需求: 媳妇儿最近担心宝宝的吃奶时间不够规律, 网上说是正常平均 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 N
- 6 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:00
- 6 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 86
- 6 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.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>
最后总结下, 实际以后每次记录喂奶时间直接 @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 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. 确认无误提交事物:
- test@DEMO> commit;
- Commit complete.
- test@DEMO>
可以清楚看到最新的一次喂奶间隔是 194 分钟, 也是正常的 ^_^
来源: https://www.cnblogs.com/jyzhao/p/12036186.html