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

时间:2021-10-06 06:43:40

本文是《使用SQL计算宝宝每次吃奶的时间间隔》的续篇,因为我工作繁忙,时常不能及时帮助媳妇儿记录,为了让不懂数据库的媳妇儿也可以自己用手机熟练操作。我继续做了一些修正和封装:

  • 1.给媳妇儿的手机下载一个terminal终端软件termius,实现只需打开app就可以连接到环境;
  • 2.跟媳妇儿进一步沟通需求,按她能够接受的使用习惯来修正并封装程序,原则是尽可能的简化操作。

先来看下改进后最终的使用效果:

--1.手机上打开termius软件,点击进入到终端,输入 h 可以看到所有命令的说明:
[[email protected] ~]$ 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 可以看到今天的喂奶时间和间隔:
[[email protected] ~]$ v
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.

--3.输入 i 可以直接以当前系统时间插入一条数据,并显示插入后的结果:
[[email protected] ~]$ i
Insert 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.78

9 rows selected.

--4.输入 d <id> 可以删除id对应的那行记录,并显示删除后的结果:
[[email protected] ~]$ d 94
Delete a mistake row by id:
Enter value for id: old   1: delete from t_baby where id = &id
new   1: delete from t_baby where id = 94

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.

--5.输入 u <label> <id> 可以更新指定id的label值,比如将id=55的记录label值修改为'L',代表这次喂奶量很少,不参与计算
[[email protected] ~]$ u l 55
Update a mistake row by id:
Enter value for label: Enter value for id: old   1: update t_baby set label = upper('&label') where id = &id
new   1: update t_baby set label = upper('l') where id = 55

1 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.01

7 rows selected.

--6.输入 u <label> <id> 可以更新指定id的label值,比如将id=55的记录label值修改回'N',代表这次喂奶量正常,参与计算
[[email protected] ~]$ u n 55
Update a mistake row by id:
Enter value for label: Enter value for id: old   1: update t_baby set label = upper('&label') where id = &id
new   1: update t_baby set label = upper('n') where id = 55

1 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.01

8 rows selected.

--7.输入 ii <'mmdd hh24:mi'> 可以插入指定日期和时间
[[email protected] ~]$ ii '1215 23:00'
Insert a row using input time(mmdd mi:ss) eg: 1215 10:00
Enter 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.74

9 rows selected.

--8.删除id=95的这条测试记录
[[email protected] ~]$ d 95
Delete a mistake row by id:
Enter value for id: old   1: delete from t_baby where id = &id
new   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' 可以显示指定日期的喂奶间隔情况:
[[email protected] ~]$ vv 1214
View 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')   1
new   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.环境变量配置别名

为了简化操作,我将环境变量设置一些别名。

[[email protected] ~]$ cat ~/.bash_profile 
# 这里省略原有环境变量内容,下面为在源文件基础上追加的内容:
# Alias for baby's feed_time:
alias h=/home/oracle/baby_readme.sh
alias v=/home/oracle/baby_view.sh
alias i=/home/oracle/baby_insert.sh
alias d=/home/oracle/baby_delete.sh
alias u=/home/oracle/baby_update.sh
alias ii=/home/oracle/baby_insert_diy.sh
alias vv=/home/oracle/baby_view_diy.sh

2.系统shell脚本

baby_readme.sh

vi 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.sh

vi baby_view.sh

#!/bin/bash
# script:baby_view.sh
# version:1.01
#-------------------------------------------------------------
sqlplus -S test/test <<EOF
PROMPT View Today's Result:
@v3

EOF
#-------------------------------------------------------------

baby_insert.sh

vi baby_insert.sh

#!/bin/bash
# script:baby_insert.sh
# version:1.01
#-------------------------------------------------------------
sqlplus -S test/test <<EOF
PROMPT Insert a row using current time:
@i1

PROMPT View Today's Result:
@v3

EOF
#-------------------------------------------------------------

baby_delete.sh

vi baby_delete.sh

#!/bin/bash
# script:baby_delete.sh
# version:1.01
#-------------------------------------------------------------
sqlplus -S test/test <<EOF
PROMPT Delete a mistake row by id:
@d1
$1
commit;

PROMPT View Today's Result:
@v3

EOF
#-------------------------------------------------------------

baby_insert_diy.sh

vi baby_insert_diy.sh

#!/bin/bash
# script:baby_insert_diy.sh
# version:1.01
#-------------------------------------------------------------
sqlplus -S test/test <<EOF
PROMPT Insert a row using input time(mmdd mi:ss) eg: 1215 10:00
@i2
$1
commit;

PROMPT View Today's Result:
@v3

EOF
#-------------------------------------------------------------

baby_update.sh

vi baby_update.sh

#!/bin/bash
# script:baby_update.sh
# version:1.01
#-------------------------------------------------------------
sqlplus -S test/test <<EOF
PROMPT Update a mistake row by id:
@u1
$1
$2
commit;

PROMPT View Today's Result:
@v3

EOF
#-------------------------------------------------------------

baby_view_diy.sh

vi baby_view_diy.sh

#!/bin/bash
# script:baby_view.sh
# version:1.01
#-------------------------------------------------------------
sqlplus -S test/test <<EOF
PROMPT View one day's Result:
@v4
$1
$1

EOF
#-------------------------------------------------------------

3.底层SQL文本

--echo "v - View Today's Result."
[[email protected] ~]$ cat v3.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 >= trunc(sysdate)
/

-- echo " vv <'mmdd'>       - View one day's Result."
[[email protected] ~]$ 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."
[[email protected] ~]$ cat i1.sql 
insert into t_baby(id,feed_time) values(s1.nextval,sysdate)
/
commit
/

--echo "ii - Insert a row using input time."
[[email protected] ~]$ 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. "
[[email protected] ~]$ cat d1.sql 
delete from t_baby where id = &id
/

--echo "u - Update a mistake row by id."
[[email protected] ~]$ cat u1.sql 
update t_baby set label = upper('&label') where id = &id
/

SQL文本独立出来也方便后续需求有变化时快速更改。