如何使用SQL計(jì)算寶寶每次吃奶的時(shí)間間隔（文末含PPT）

編者的話：搞好SQL可以做很多事情，比如說可以解決海盜分金的問題，可以用SQL把大象裝進(jìn)冰箱，還可以用SQL解決環(huán)環(huán)相扣的刑偵推理問題，近期，有位讀者朋友投稿了“使用SQL計(jì)算寶寶每次吃奶的時(shí)間間隔”，大家可以一起借鑒下~

需求

媳婦兒最近擔(dān)心寶寶的吃奶時(shí)間不夠規(guī)律，網(wǎng)上說是正常平均3小時(shí)喂奶一次，讓我記錄下每次的吃奶時(shí)間，分析下實(shí)際是否偏差很大，好在下次去醫(yī)院復(fù)查時(shí)反饋給醫(yī)生。

此外，還要注意有時(shí)候哭鬧要吃奶，而實(shí)際只吃了兩口便不吃了。這種情況要特殊標(biāo)記下，如果不算這種情況的話，分析看是否正常。

環(huán)境

Oracle 11.2.0.4

1.記錄每次吃奶時(shí)間

2.計(jì)算吃奶時(shí)間間隔

1. 記錄每次吃奶時(shí)間

我在自己的Oracle測(cè)試環(huán)境中創(chuàng)建了一張表t_baby，用于實(shí)現(xiàn)記錄寶寶每次的吃奶時(shí)間：

test@DEMO> desc t_baby

 Name                                      Null?    Type

 ----------------------------------------- -------- ------------

 ID                                        NOT NULL NUMBER

 FEED_TIME                                 NOT NULL DATE

 LABEL                                              VARCHAR2(1)

注意：這里的LABEL字段就是用于標(biāo)志吃奶量的。

默認(rèn)值設(shè)置為'N',代表吃奶量正常；如果吃奶量很少，則可將對(duì)應(yīng)記錄的LABEL字段值手工更新為'L'；如果吃奶量非常多，超過正常值，則更新為'M'。

由于每次吃奶都要人為的插入一條數(shù)據(jù)，為了簡(jiǎn)化這個(gè)運(yùn)維操作，將插入語句保存到文件i.sql中，內(nèi)容如下：

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(s1.nextval,to_date('&feed_time','mmdd hh34:mi'))

test@DEMO>

這樣每次執(zhí)行插入就可以方便的直接@i調(diào)用插入，比如剛剛媳婦兒告知我本次吃奶時(shí)間是13:16，那直接調(diào)用插入即可：

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(s1.nextval,to_date('&feed_time','mmdd hh34:mi'))

new   1: insert into t_baby(id,feed_time) values(s1.nextval,to_date('1213 08:00','mmdd hh34:mi'))

1 row created.

test@DEMO> commit;

Commit complete.

確認(rèn)剛剛插入的數(shù)據(jù)無誤后，一定要記得提交事物。我之所以沒有將commit寫到腳本中，就是為了當(dāng)發(fā)現(xiàn)數(shù)據(jù)輸入有誤，方便直接rollback回滾。

現(xiàn)在我們來看一下現(xiàn)有的數(shù)據(jù)，詳細(xì)記錄了每一次的吃奶時(shí)間：

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.

可以看到ID=9這條記錄的LABEL='L',也就是說這次吃奶量非常少。

2.計(jì)算吃奶時(shí)間間隔

也許有人禁不住會(huì)問，你這么簡(jiǎn)單的需求還把它弄到Oracle數(shù)據(jù)庫里，還用SQL計(jì)算實(shí)現(xiàn)。什么？你說你還要用到Oracle分析函數(shù)？

其實(shí)不必太較真了，適合自己的才是最好的，我就是喜歡敲sqlplus不喜歡用Excel等工具，還能復(fù)習(xí)下分析函數(shù)，何樂而不為呢。

廢話不多說，來看如何用分析函數(shù)顯示上次喂奶時(shí)間L_TIME：

select t.*, lag(feed_time) over(order by id) l_time from t_baby t;

test@DEMO> select t.*, 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.

然后直接查詢計(jì)算下喂奶間隔即可，以分鐘為單位：

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

from (select t.*, 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.*, 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        243

         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.

媳婦兒主要擔(dān)心是喂奶間隔太短，會(huì)不會(huì)撐到寶寶，那如果說我們假定間隔在2h以上都是正常的話，那么對(duì)應(yīng)的也就是LAG(min)>120分鐘以上的為正常。

從目前已有的數(shù)據(jù)來看，的確異常次數(shù)比較多。

而我們之前說到存在喂奶量極少的情況，如果將這種情況排除在外呢？再重新計(jì)算：

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

from (select t.*, 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.*, 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        243

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

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

這下可以看到數(shù)據(jù)趨于正常，只有一次喂奶間隔在120分鐘以下了。當(dāng)然目前數(shù)據(jù)還比較少，后續(xù)數(shù)據(jù)多了才可以更準(zhǔn)確的反映出異常的比例。

因?yàn)闀?huì)經(jīng)常查詢到這個(gè)間隔時(shí)間。將這個(gè)兩個(gè)語句分別保存為v1.sql和v2.sql，方便后續(xù)使用。

test@DEMO> l

  1* select id, feed_time, label, round((feed_time - l_time) * 24 * 60, 2) "LAG(min)" from (select t.*, 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.*, 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        243

         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        243

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

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

test@DEMO>

最后總結(jié)下，實(shí)際以后每次記錄喂奶時(shí)間直接@i輸入具體時(shí)間，每次查喂奶間隔就根據(jù)實(shí)際需求看@v1或者@v2就ok，確認(rèn)無誤后提交更改，再實(shí)際熟悉下整個(gè)流程：

--1.插入喂奶時(shí)間：

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(s1.nextval,to_date('&feed_time','mmdd hh34:mi'))

new   1: insert into t_baby(id,feed_time) values(s1.nextval,to_date('1213 16:30','mmdd hh34:mi'))

1 row created.

--2.查看喂奶間隔：

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        243

         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.查看喂奶間隔（排除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        243

        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.確認(rèn)無誤提交事物：

test@DEMO> commit;

Commit complete.

test@DEMO>

可以清楚看到最新的一次喂奶間隔是194分鐘，也是正常的^_^