SQL計算寶寶吃奶的時間間隔（二）

  • 2020 年 3 月 11 日
  • 筆記

導讀：之前，在「數據和雲」公眾號發表過一篇文章《如何使用SQL計算寶寶每次吃奶的時間間隔（分析函數的使用）》，本文為續篇，如何提升程序的易用性。

因為我工作繁忙，時常不能及時幫助媳婦兒記錄，為了讓不懂數據庫的媳婦兒也可以自己用手機熟練操作。我繼續做了一些修正和封裝：

1.給媳婦兒的手機下載一個terminal終端軟件termius，實現只需打開app就可以連接到環境； 2.跟媳婦兒進一步溝通需求，按她能夠接受的使用習慣來修正並封裝程序，原則是儘可能的簡化操作。

先來看下改進後最終的使用效果： –1.手機上打開termius軟件，點擊進入到終端，輸入 h 可以看到所有命令的說明： 

[oracle@jystdrac1 ~]$ h**Command for Baby's feed_time:**Usage: v                         - View Today's Result. vv <'mmdd'>            - View one day's Result. i                         - Insert a row using current time. ii <'mmdd hh24:mi'>     - Insert a row using input time. d <id>                    - Delete a mistake row by id. u <label> <id>            - Update a mistake row by id. h                         - Help

–2.輸入 v 可以看到今天的餵奶時間和間隔： 

[oracle@jystdrac1 ~]$ vView Today's Result:          ID FEED_TIME   L   LAG(min)     LAG(h)---------- ----------- - ---------- ----------        53 12-15 01:00 N        140       2.33        54 12-15 04:08 N        188       3.13        55 12-15 07:35 N        207       3.45        56 12-15 10:40 N        185       3.08        69 12-15 13:16 N        156        2.6        70 12-15 14:32 N         76       1.27        82 12-15 17:14 N        163       2.71        83 12-15 19:15 N        121       2.01  8 rows selected.

–3.輸入 i 可以直接以當前系統時間插入一條數據，並顯示插入後的結果： 

[oracle@jystdrac1 ~]$ iInsert a row using current time:1 row created.Commit complete.View Today's Result:        ID FEED_TIME   L   LAG(min)     LAG(h)---------- ----------- - ---------- ----------        53 12-15 01:00 N        140       2.33        54 12-15 04:08 N        188       3.13        55 12-15 07:35 N        207       3.45        56 12-15 10:40 N        185       3.08        69 12-15 13:16 N        156        2.6        70 12-15 14:32 N         76       1.27        82 12-15 17:14 N        163       2.71        83 12-15 19:15 N        121       2.01        94 12-15 23:02 N        227       3.789 rows selected.

–4.輸入 d <id> 可以刪除id對應的那行記錄，並顯示刪除後的結果：

[oracle@jystdrac1 ~]$ d 94Delete a mistake row by id:Enter value for id: old   1: delete from t_baby where id = &idnew   1: delete from t_baby where id = &idnew   1: delete from t_baby where id = 941 row deleted.Commit complete.View Today's Result:        ID FEED_TIME   L   LAG(min)     LAG(h)---------- ----------- - ---------- ----------        53 12-15 01:00 N        140       2.33        54 12-15 04:08 N        188       3.13        55 12-15 07:35 N        207       3.45        56 12-15 10:40 N        185       3.08        69 12-15 13:16 N        156        2.6        70 12-15 14:32 N         76       1.27        82 12-15 17:14 N        163       2.71        83 12-15 19:15 N        121       2.018 rows selected.

–5.輸入 u <label> <id> 可以更新指定id的label值，比如將id=55的記錄label值修改為'L'，代表這次餵奶量很少，不參與計算 

[oracle@jystdrac1 ~]$ u l 55Update a mistake row by id:Enter value for label: Enter value for id: old   1: update t_baby set label = upper('&label') where id = &idnew   1: update t_baby set label = upper('l') where id = 551 row updated.Commit complete.View Today's Result:        ID FEED_TIME   L   LAG(min)     LAG(h)---------- ----------- - ---------- ----------        53 12-15 01:00 N        140       2.33        54 12-15 04:08 N        188       3.13        56 12-15 10:40 N        392       6.53        69 12-15 13:16 N        156        2.6        70 12-15 14:32 N         76       1.27        82 12-15 17:14 N        163       2.71        83 12-15 19:15 N        121       2.017 rows selected.

–6.輸入 u <label> <id> 可以更新指定id的label值，比如將id=55的記錄label值修改回'N',代表這次餵奶量正常，參與計算 

[oracle@jystdrac1 ~]$ u n 55Update a mistake row by id:Enter value for label: Enter value for id: old   1: update t_baby set label = upper('&label') where id = &idnew   1: update t_baby set label = upper('n') where id = 551 row updated.Commit complete.View Today's Result:        ID FEED_TIME   L   LAG(min)     LAG(h)---------- ----------- - ---------- ----------        53 12-15 01:00 N        140       2.33        54 12-15 04:08 N        188       3.13        55 12-15 07:35 N        207       3.45        56 12-15 10:40 N        185       3.08        69 12-15 13:16 N        156        2.6        70 12-15 14:32 N         76       1.27        82 12-15 17:14 N        163       2.71        83 12-15 19:15 N        121       2.018 rows selected.

–7.輸入 ii <'mmdd hh24:mi'> 可以插入指定日期和時間 

[oracle@jystdrac1 ~]$ ii '1215 23:00'Insert a row using input time(mmdd mi:ss) eg: 1215 10:00Enter value for feed_time: 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('1215 23:00','mmdd hh24:mi'))1 row created.Commit complete.View Today's Result:        ID FEED_TIME   L   LAG(min)     LAG(h)---------- ----------- - ---------- ----------        53 12-15 01:00 N        140       2.33        54 12-15 04:08 N        188       3.13        55 12-15 07:35 N        207       3.45        56 12-15 10:40 N        185       3.08        69 12-15 13:16 N        156        2.6        70 12-15 14:32 N         76       1.27        82 12-15 17:14 N        163       2.71        83 12-15 19:15 N        121       2.01        95 12-15 23:00 N        225       3.749 rows selected.

–8.刪除id=95的這條測試記錄 

[oracle@jystdrac1 ~]$ d 95Delete a mistake row by id:Enter value for id: old   1: delete from t_baby where id = &idnew   1: delete from t_baby where id = 95  1 row deleted.    Commit complete.  View Today's Result:          ID FEED_TIME   L   LAG(min)     LAG(h)---------- ----------- - ---------- ----------        53 12-15 01:00 N        140       2.33        54 12-15 04:08 N        188       3.13        55 12-15 07:35 N        207       3.45        56 12-15 10:40 N        185       3.08        69 12-15 13:16 N        156        2.6        70 12-15 14:32 N         76       1.27        82 12-15 17:14 N        163       2.71        83 12-15 19:15 N        121       2.01  8 rows selected.

–9.使用 vv 'mmdd' 可以顯示指定日期的餵奶間隔情況： 

[oracle@jystdrac1 ~]$ vv 1214View Today's Result:Enter value for feed_time: Enter value for feed_time: old   2: from (select t.*, lag(feed_time) over(order by id) l_time from t_baby t where label <> 'L') where feed_time >= to_date('&feed_time','mmdd') and feed_time < to_date('&feed_time','mmdd') + 1new   2: from (select t.*, lag(feed_time) over(order by id) l_time from t_baby t where label <> 'L') where feed_time >= to_date('1214','mmdd') and feed_time < to_date('1214','mmdd') + 1          ID FEED_TIME   L   LAG(min)     LAG(h)---------- ----------- - ---------- ----------        41 12-14 01:55 N        265       4.42        42 12-14 04:58 N        183       3.05        43 12-14 08:25 N        207       3.45        44 12-14 11:23 N        178       2.97        45 12-14 12:23 N         60          1        46 12-14 15:00 N        157       2.62        47 12-14 17:22 N        142       2.37        48 12-14 18:45 N         83       1.38        49 12-14 22:40 N        235       3.92  9 rows selected.

經過一番演示，媳婦兒的反饋是非常滿意的，實際她最常用的還是v和i，非常方便，其他命令偶爾使用，其實只需記住h可以獲取到幫助即可。下面將本次的修正和封裝過程記錄一下：

  • 1.環境變量配置別名
  • 2.系統shell腳本
  • 3.底層SQL文本

1. 環境變量配置別名

為了簡化操作，我將環境變量設置一些別名。

[oracle@jystdrac1 ~]$ cat ~/.bash_profile # 這裡省略原有環境變量內容,下面為在源文件基礎上追加的內容：# Alias for baby's feed_time:alias h=/home/oracle/baby_readme.shalias v=/home/oracle/baby_view.shalias i=/home/oracle/baby_insert.shalias d=/home/oracle/baby_delete.shalias u=/home/oracle/baby_update.shalias ii=/home/oracle/baby_insert_diy.shalias vv=/home/oracle/baby_view_diy.sh

2. 系統shell腳本

##baby_readme.shvi baby_readme.sh#!/bin/bash# script:baby_readme.sh# version:1.01#-------------------------------------------------------------echo "**Command for Baby's feed_time:**"echo "Usage:"echo " v       - View Today's Result."echo " vv <'mmdd'>     - View one day's Result."echo " i       - Insert a row using current time."echo " ii <'mmdd hh24:mi'>   - Insert a row using input time."echo " d <id>     - Delete a mistake row by id."echo " u <label> <id>   - Update a mistake row by id."echo " h - Help"#-------------------------------------------------------------##baby_view.shvi baby_view.sh#!/bin/bash# script:baby_view.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT View Today's Result:@v3EOF#-------------------------------------------------------------##baby_insert.shvi baby_insert.sh#!/bin/bash# script:baby_insert.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT Insert a row using current time:@i1PROMPT View Today's Result:@v3EOF#-------------------------------------------------------------##baby_delete.shvi baby_delete.sh#!/bin/bash# script:baby_delete.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT Delete a mistake row by id:@d1$1commit;PROMPT View Today's Result:@v3EOF#-------------------------------------------------------------##baby_insert_diy.shvi baby_insert_diy.sh#!/bin/bash# script:baby_insert_diy.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT Insert a row using input time(mmdd mi:ss) eg: 1215 10:00@i2$1commit;PROMPT View Today's Result:@v3EOF#-------------------------------------------------------------##baby_update.shvi baby_update.sh#!/bin/bash# script:baby_update.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT Update a mistake row by id:@u1$1$2commit;PROMPT View Today's Result:@v3EOF#-------------------------------------------------------------##baby_view_diy.shvi baby_view_diy.sh#!/bin/bash# script:baby_view.sh# version:1.01#-------------------------------------------------------------sqlplus -S test/test <<EOFPROMPT View one day's Result:@v4$1$1EOF#-------------------------------------------------------------

3. 底層SQL文本

--echo "v - View Today's Result."[oracle@jystdrac1 ~]$ cat v3.sqlSELECT    id,    to_char(feed_time, 'mm-dd hh24:mi') "FEED_TIME",    label,    round((feed_time - l_time) * 24 * 60) "LAG(min)",    round((feed_time - l_time) * 24, 2) "LAG(h)"FROM    (        SELECT            t.*,            LAG(feed_time) OVER(ORDER BY id) l_time        FROM            t_baby t        WHERE            label <> 'L'    )WHERE    feed_time >= trunc(sysdate)/  -- echo " vv <'mmdd'>     - View one day's Result."[oracle@jystdrac1 ~]$ cat v4.sql SELECT    id,    to_char(feed_time, 'mm-dd hh24:mi') "FEED_TIME",    label,    round((feed_time - l_time) * 24 * 60) "LAG(min)",    round((feed_time - l_time) * 24, 2) "LAG(h)"FROM    (        SELECT            t.*,            LAG(feed_time) OVER(ORDER BY id) l_time        FROM            t_baby t        WHERE            label <> 'L'    )WHERE    feed_time >= TO_DATE('&feed_time', 'mmdd')    AND feed_time < TO_DATE('&feed_time', 'mmdd') + 1/  --echo "i - Insert a row using current time."[oracle@jystdrac1 ~]$ cat i1.sql insert into t_baby(id,feed_time) values(s1.nextval,sysdate)/commit/  --echo "ii - Insert a row using input time."[oracle@jystdrac1 ~]$ cat i2.sql insert into t_baby(id,feed_time) values(s1.nextval,to_date('&feed_time','mmdd hh24:mi'))/  --echo "d - Delete a mistake row by id. "[oracle@jystdrac1 ~]$ cat d1.sql delete from t_baby where id = &id/  --echo "u - Update a mistake row by id."[oracle@jystdrac1 ~]$ cat u1.sql update t_baby set label = upper('&label') where id = &id/

SQL文本獨立出來也方便後續需求有變化時快速更改。

墨天輪原文鏈接：https://www.modb.pro/db/22267（複製到瀏覽器中打開或者點擊左下角的「閱讀原文」）

VirMach 便宜 VPS

QNews

QNews