Shulou(Shulou.com)06/01 Report--

Editor's note: a good SQL can do a lot of things, for example, it can solve the problem of pirate payment, you can use SQL to put elephants in the refrigerator, and you can also use SQL to solve interlocking criminal investigation reasoning problems. Recently, a reader friend submitted a manuscript, "use SQL to calculate the time interval for a baby to breast-feed." you can learn from it.

Demand

Recently, my wife is worried that the baby's feeding time is not regular enough. It is said on the Internet that it is normal to breast-feed every 3 hours on average. Let me record the feeding time of each time, analyze whether the actual deviation is very big, and give feedback to the doctor when I go to the hospital for review next time.

In addition, we should also pay attention to sometimes crying for breast-feeding, but actually stop eating after only two bites. This situation should be specially marked, and if this situation is not taken into account, it should be analyzed to see if it is normal.

Environment

Oracle 11.2.0.4

1. Record the time of each breast-feeding

two。 Calculate the breastfeeding interval

1. Record the time of each breast-feeding

I created a table, t_baby, in my Oracle test environment to record the baby's breast-feeding time each time:

Test@DEMO > desc t_baby

Name Null? Type

ID NOT NULL NUMBER

FEED_TIME NOT NULL DATE

LABEL VARCHAR2 (1)

Note: the LABEL field here is used to indicate the amount of milk eaten.

The default value is set to 'milk intake, which means that the milk intake is normal; if the milk intake is small, the LABEL field value of the corresponding record can be manually updated to' milk'; if the milk intake is very large and exceeds the normal value, it can be updated to'M'.

Since you have to insert a piece of data artificially every time you feed, to simplify the OPS operation, save the insert statement to the file i.sql, as follows:

Test@DEMO > get I

1 PROMPT Please input your feed_time (mmdd hh34:mi). Eg:1213 08:00

2 * insert into t_baby (id,feed_time) values (s. 1. NextVal & feed_time','mmdd hh34:mi')

Test@DEMO >

In this way, each time you perform the insert, you can easily call the insert directly. For example, if my wife just told me that the lactation time is 13:16, then you can directly call the insert:

Test@DEMO > @ I

Please input your feed_time (mmdd hh34:mi). Eg:1213 08:00

Enter value for feed_time: 1213 13:16

Old 1: insert into t_baby (id,feed_time) values (s. 1. NextvalRecoment date ('& feed_time','mmdd hh34:mi'))

New 1: insert into t_baby (id,feed_time) values (s. 1. NextVal hh34:mi' toast date ('1213 08Vol 00century hh34:mi')

1 row created.

Test@DEMO > commit

Commit complete.

After confirming that the data you just inserted is correct, be sure to submit something. The reason why I did not write commit into the script is to facilitate direct rollback rollback when I find that the data is entered incorrectly.

Now let's take a look at the available data and record in detail the time of each breast-feeding:

Test@DEMO > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Session altered.

Test@DEMO > select * from t_baby

ID FEED_TIME LABEL

6 2019-12-13 02:49:00 N

7 2019-12-13 04:58:00 N

8 2019-12-13 09:01:00 N

9 2019-12-13 10:40:00 L

15 2019-12-13 11:50:00 N

16 2019-12-13 13:16:00 N

6 rows selected.

You can see the LABEL='L', recorded by ID=9, that is to say, the amount of milk eaten this time is very small.

two。 Calculate the breastfeeding interval

Some people can't help but ask that you have such a simple requirement that you put it into the Oracle database and implement it in SQL computing. What? You said you were going to use the Oracle analysis function?

In fact, there is no need to be too serious, suitable for their own is the best, I just like to knock sqlplus do not like to use Excel and other tools, but also can review the analysis function, why not?

Needless to say, let's take a look at how to use the analysis function to display the last feeding time L_TIME:

Select T. feed_time, lag (feed_time) over (order by id) l_time from t_baby t

Test@DEMO > select t.questions, lag (feed_time) over (order by id) l_time from t_baby t

ID FEED_TIME L L_TIME

--

6 2019-12-13 02:49:00 N

7 2019-12-13 04:58:00 N 2019-12-13 02:49:00

8 2019-12-13 09:01:00 N 2019-12-13 04:58:00

9 2019-12-13 10:40:00 L 2019-12-13 09:01:00

15 2019-12-13 11:50:00 N 2019-12-13 10:40:00

16 2019-12-13 13:16:00 N 2019-12-13 11:50:00

6 rows selected.

Then directly query and calculate the feeding interval, in minutes:

Select id, feed_time, label, round ((feed_time-l_time) * 24 * 60,2) "LAG (min)"

From (select t. Feed_time, lag (feed_time) over (order by id) l_time from t_baby t)

Test@DEMO > select id, feed_time, label, round ((feed_time-l_time) * 24 * 60,2) "LAG (min)" from (select T. min, lag (feed_time) over (order by id) l_time from t_baby t)

ID FEED_TIME L LAG (min)

--

6 2019-12-13 02:49:00 N

7 2019-12-13 04:58:00 N 129

8 2019-12-13 09:01:00 N

9 2019-12-13 10:40:00 L 99

15 2019-12-13 11:50:00 N 70

16 2019-12-13 13:16:00 N 86

6 rows selected.

The wife is mainly worried about whether the feeding interval is too short and will hold up to the baby. If we assume that the interval is more than 2 hours is normal, then the corresponding LAG (min) > 120 minutes is normal.

Judging from the available data, there are indeed a large number of anomalies.

And we mentioned earlier that there is a very small amount of breast-feeding, what if this situation is excluded? Then recalculate:

Select id, feed_time, label, round ((feed_time-l_time) * 24 * 60,2) "LAG (min)"

From (select t. Feed_time, lag (feed_time) over (order by id) l_time from t_baby t where label'L')

Test@DEMO > select id, feed_time, label, round ((feed_time-l_time) * 24 * 60,2) "LAG (min)" from (select T. min, lag (feed_time) over (order by id) l_time from t_baby t where label'L')

ID FEED_TIME L LAG (min)

--

6 2019-12-13 02:49:00 N

7 2019-12-13 04:58:00 N 129

8 2019-12-13 09:01:00 N

15 2019-12-13 11:50:00 N 169

16 2019-12-13 13:16:00 N 86

Now you can see that the data tend to be normal, and only one feeding interval is less than 120 minutes. Of course, there is less data at present, and more follow-up data can more accurately reflect the abnormal proportion.

Because this interval is often queried. Save the two statements as v1.sql and v2.sql respectively for later use.

Test@DEMO > l

1 * select id, feed_time, label, round ((feed_time-l_time) * 24 * 60,2) "LAG (min)" from (select T. from, lag (feed_time) over (order by id) l_time from t_baby t)

Test@DEMO > save v1 rep

Wrote file v1.sql

Test@DEMO > l

1 * select id, feed_time, label, round ((feed_time-l_time) * 24 * 60,2) "LAG (min)" from (select T. from, lag (feed_time) over (order by id) l_time from t_baby t where label'L')

Test@DEMO > save v2 rep

Wrote file v2.sql

Test@DEMO > @ v1

ID FEED_TIME L LAG (min)

--

6 2019-12-13 02:49:00 N

7 2019-12-13 04:58:00 N 129

8 2019-12-13 09:01:00 N

9 2019-12-13 10:40:00 L 99

15 2019-12-13 11:50:00 N 70

16 2019-12-13 13:16:00 N 86

6 rows selected.

Test@DEMO > @ v2

ID FEED_TIME L LAG (min)

--

6 2019-12-13 02:49:00 N

7 2019-12-13 04:58:00 N 129

8 2019-12-13 09:01:00 N

15 2019-12-13 11:50:00 N 169

16 2019-12-13 13:16:00 N 86

Test@DEMO >

In conclusion, you can enter the specific time directly @ I for each feeding time recorded in the actual future, and then check @ v1 or @ v2 for ok according to the actual needs, then submit the changes after confirmation, and then be familiar with the whole process:

-1. Insert feeding time:

Test@DEMO > @ I

Please input your feed_time (mmdd hh34:mi). Eg:1213 08:00

Enter value for feed_time: 1213 16:30

Old 1: insert into t_baby (id,feed_time) values (s. 1. NextvalRecoment date ('& feed_time','mmdd hh34:mi'))

New 1: insert into t_baby (id,feed_time) values (s. 1. NextVal hh34:mi' toast date ('1213 16 rime 30 minutes hh34:mi'))

1 row created.

-- 2. Check the feeding interval:

Test@DEMO > @ v1

ID FEED_TIME L LAG (min)

--

6 2019-12-13 02:49:00 N

7 2019-12-13 04:58:00 N 129

8 2019-12-13 09:01:00 N

9 2019-12-13 10:40:00 L 99

15 2019-12-13 11:50:00 N 70

16 2019-12-13 13:16:00 N 86

19 2019-12-13 16:30:00 N 194

7 rows selected.

-3. Check the feeding interval (excluding Label='L'):

Test@DEMO > @ v2

ID FEED_TIME L LAG (min)

--

6 2019-12-13 02:49:00 N

7 2019-12-13 04:58:00 N 129

8 2019-12-13 09:01:00 N

15 2019-12-13 11:50:00 N 169

16 2019-12-13 13:16:00 N 86

19 2019-12-13 16:30:00 N 194

6 rows selected.

-- 4. Confirm that things are submitted correctly:

Test@DEMO > commit

Commit complete.

Test@DEMO >

You can clearly see that the latest feeding interval is 194 minutes, which is also normal ^ _ ^.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.