使用SQL计算宝宝每次吃奶的时间间隔

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

需求：媳妇儿最近担心宝宝的吃奶时间不够规律，网上说是正常平均3小时喂奶一次，让我记录下每次的吃奶时间，分析下实际是否偏差很大，好在下次去医院复查时反馈给医生。

此外，还要注意有时候哭闹要吃奶，而实际只吃了两口便不吃了。这种情况要特殊标记下，如果不算这种情况的话，分析看是否正常。

环境：Oracle 11.2.0.4

1.记录每次吃奶时间
2.计算吃奶时间间隔

1.记录每次吃奶时间

我在自己的Oracle测试环境中创建了一张表t_baby，用于实现记录宝宝每次的吃奶时间：

test@DEMO> desc t_baby Name Null? Type ----------------------------------------- -------- ------------ ID NOT NULL NUMBER FEED_TIME NOT NULL DATE LABEL VARCHAR2(1)

注意:这里的LABEL字段就是用于标志吃奶量的。

默认值设置为'N',代表吃奶量正常；如果吃奶量很少，则可将对应记录的LABEL字段值手工更新为'L'；如果吃奶量非常多，超过正常值，则更新为'M'。

由于每次吃奶都要人为的插入一条数据，为了简化这个运维操作，将插入语句保存到文件i.sql中，内容如下：

test@DEMO> get i 1 PROMPT Please input your feed_time(mmdd hh24:mi). eg:1213 08:00 2* insert into t_baby(id,feed_time) values(s1.nextval,to_date('&feed_time','mmdd hh24:mi')) test@DEMO>

这样每次执行插入就可以方便的直接@i调用插入，比如刚刚媳妇儿告知我本次吃奶时间是13:16，那直接调用插入即可：

test@DEMO> @i Please input your feed_time(mmdd hh24: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 hh24:mi')) new 1: insert into t_baby(id,feed_time) values(s1.nextval,to_date('1213 08:00','mmdd hh24:mi'))1 row created.test@DEMO> commit;Commit complete.

确认刚刚插入的数据无误后，一定要记得提交事物。我之所以没有将commit写到脚本中，就是为了当发现数据输入有误，方便直接rollback回滚。

现在我们来看一下现有的数据，详细记录了每一次的吃奶时间：

test@DEMO> alter session set nls_date_format='yyyy-mm-dd hh24: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 N6 rows selected.

可以看到ID=9这条记录的LABEL='L',也就是说这次吃奶量非常少。

2.计算吃奶时间间隔

也许有人禁不住会问，你这么简单的需求还把它弄到Oracle数据库里，还用SQL计算实现。什么？你说你还要用到Oracle分析函数？
其实不必太较真了，适合自己的才是最好的，我就是喜欢敲sqlplus不喜欢用Excel等工具，还能复习下分析函数，何乐而不为呢。
废话不多说，来看如何用分析函数显示上次喂奶时间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:006 rows selected.

然后直接查询计算下喂奶间隔即可，以分钟为单位：

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 866 rows selected.

媳妇儿主要担心是喂奶间隔太短，会不会撑到宝宝，那如果说我们假定间隔在2h以上都是正常的话，那么对应的也就是LAG(min)>120分钟以上的为正常。

从目前已有的数据来看，的确异常次数比较多。

而我们之前说到存在喂奶量极少的情况，如果将这种情况排除在外呢？再重新计算：

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

这下可以看到数据趋于正常，只有一次喂奶间隔在120分钟以下了。当然目前数据还比较少，后续数据多了才可以更准确的反映出异常的比例。

因为会经常查询到这个间隔时间。将这个两个语句分别保存为v1.sql和v2.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) test@DEMO> save v1 rep Wrote file v1.sqltest@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.sqltest@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 866 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 86test@DEMO>

最后总结下，实际以后每次记录喂奶时间直接@i输入具体时间，每次查喂奶间隔就根据实际需求看@v1或者@v2就ok，确认无误后提交更改，再实际熟悉下整个流程：

--1.插入喂奶时间： test@DEMO> @i Please input your feed_time(mmdd hh24: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 hh24:mi')) new 1: insert into t_baby(id,feed_time) values(s1.nextval,to_date('1213 16:30','mmdd hh24: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 1947 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 1946 rows selected.--4.确认无误提交事物： test@DEMO> commit;Commit complete.test@DEMO>