PostgreSQL计算连续签到天数SQL，with RECURSIVE as递归查询

原创章芋文 2020-05-21

3619

需求

在postgresql中计算某用户从签到的今天历史连续签到的天数。
比如今天签到的日期是5月21日，某用户20号、19号、18号、16号、15号都签到了，那么该用户今天的累积签到天数应该为4。

创建测试表

create table check_history
(
	user_id int,
	check_time timestamp(0)
);

\d check_history
    Column    |              Type              | Collation | Nullable | Default 
--------------+--------------------------------+-----------+----------+---------
 check_time   | timestamp(0) without time zone |           |          | 
 user_id      | integer                        |           |          |

插入测试数据

INSERT INTO public.check_history (user_id, check_time) VALUES (111, '2020-05-20 10:15:36');
INSERT INTO public.check_history (user_id, check_time) VALUES (111, '2020-05-19 16:12:58');
INSERT INTO public.check_history (user_id, check_time) VALUES (111, '2020-05-18 09:40:38');
INSERT INTO public.check_history (user_id, check_time) VALUES (111, '2020-05-14 12:15:47');
INSERT INTO public.check_history (user_id, check_time) VALUES (1073, '2020-05-19 16:43:10');
INSERT INTO public.check_history (user_id, check_time) VALUES (1073, '2020-05-18 15:08:04');
INSERT INTO public.check_history (user_id, check_time) VALUES (1093, '2020-05-21 14:46:21');
INSERT INTO public.check_history (user_id, check_time) VALUES (1093, '2020-05-20 09:24:40');
INSERT INTO public.check_history (user_id, check_time) VALUES (1093, '2020-05-19 16:18:59');
INSERT INTO public.check_history (user_id, check_time) VALUES (1093, '2020-05-18 14:32:11');
INSERT INTO public.check_history (user_id, check_time) VALUES (1093, '2020-05-15 20:16:12');
INSERT INTO public.check_history (user_id, check_time) VALUES (1093, '2020-05-14 12:26:55');
INSERT INTO public.check_history (user_id, check_time) VALUES (1093, '2020-04-21 09:53:36');
INSERT INTO public.check_history (user_id, check_time) VALUES (1093, '2020-04-09 22:36:04');
INSERT INTO public.check_history (user_id, check_time) VALUES (1093, '2020-04-07 18:19:06');
INSERT INTO public.check_history (user_id, check_time) VALUES (1094, '2020-05-20 10:27:28');
INSERT INTO public.check_history (user_id, check_time) VALUES (1094, '2020-05-19 20:39:25');
INSERT INTO public.check_history (user_id, check_time) VALUES (1094, '2020-05-18 18:25:48');
INSERT INTO public.check_history (user_id, check_time) VALUES (1094, '2020-05-14 13:58:48');
INSERT INTO public.check_history (user_id, check_time) VALUES (1094, '2020-04-16 18:29:31');
INSERT INTO public.check_history (user_id, check_time) VALUES (1337, '2020-05-19 20:52:10');
INSERT INTO public.check_history (user_id, check_time) VALUES (6205, '2020-05-19 16:41:16');
INSERT INTO public.check_history (user_id, check_time) VALUES (6205, '2020-05-14 13:21:37');
INSERT INTO public.check_history (user_id, check_time) VALUES (6205, '2020-05-09 10:11:46');
INSERT INTO public.check_history (user_id, check_time) VALUES (11111, '2020-05-21 14:45:00');
INSERT INTO public.check_history (user_id, check_time) VALUES (11111, '2020-05-20 11:17:38');
INSERT INTO public.check_history (user_id, check_time) VALUES (11111, '2020-05-19 11:24:27');
INSERT INTO public.check_history (user_id, check_time) VALUES (11111, '2020-05-15 21:14:19');
INSERT INTO public.check_history (user_id, check_time) VALUES (11111, '2020-05-14 11:53:53');
INSERT INTO public.check_history (user_id, check_time) VALUES (11111, '2020-05-12 14:13:36');
INSERT INTO public.check_history (user_id, check_time) VALUES (11111, '2020-05-09 15:34:38');
INSERT INTO public.check_history (user_id, check_time) VALUES (11111, '2020-04-28 09:45:41');
INSERT INTO public.check_history (user_id, check_time) VALUES (11111, '2020-04-20 10:23:40');
INSERT INTO public.check_history (user_id, check_time) VALUES (11112, '2020-05-20 11:14:11');
INSERT INTO public.check_history (user_id, check_time) VALUES (11112, '2020-05-19 18:46:04');
INSERT INTO public.check_history (user_id, check_time) VALUES (11112, '2020-05-18 14:27:28');
INSERT INTO public.check_history (user_id, check_time) VALUES (11113, '2020-05-21 15:20:59');
INSERT INTO public.check_history (user_id, check_time) VALUES (11113, '2020-05-19 15:43:21');
INSERT INTO public.check_history (user_id, check_time) VALUES (11113, '2020-05-18 14:27:47');
INSERT INTO public.check_history (user_id, check_time) VALUES (11113, '2020-05-14 15:21:37');
INSERT INTO public.check_history (user_id, check_time) VALUES (11114, '2020-05-19 17:52:03');
INSERT INTO public.check_history (user_id, check_time) VALUES (11114, '2020-05-14 15:53:26');
INSERT INTO public.check_history (user_id, check_time) VALUES (11115, '2020-05-19 17:52:42');
INSERT INTO public.check_history (user_id, check_time) VALUES (11115, '2020-05-14 12:16:45');
INSERT INTO public.check_history (user_id, check_time) VALUES (11116, '2020-05-19 18:23:30');
INSERT INTO public.check_history (user_id, check_time) VALUES (11116, '2020-05-14 15:41:44');
INSERT INTO public.check_history (user_id, check_time) VALUES (11117, '2020-05-14 15:54:51');
INSERT INTO public.check_history (user_id, check_time) VALUES (11119, '2020-05-20 13:30:04');
INSERT INTO public.check_history (user_id, check_time) VALUES (11119, '2020-05-19 16:19:15');
INSERT INTO public.check_history (user_id, check_time) VALUES (11119, '2020-05-14 18:54:33');
INSERT INTO public.check_history (user_id, check_time) VALUES (11121, '2020-05-14 17:28:59');
INSERT INTO public.check_history (user_id, check_time) VALUES (11121, '2020-05-09 18:34:28');
INSERT INTO public.check_history (user_id, check_time) VALUES (11124, '2020-05-14 19:13:25');
INSERT INTO public.check_history (user_id, check_time) VALUES (11143, '2020-05-14 17:27:25');
INSERT INTO public.check_history (user_id, check_time) VALUES (11144, '2020-05-14 15:47:53');
INSERT INTO public.check_history (user_id, check_time) VALUES (11188, '2020-05-20 09:21:52');
INSERT INTO public.check_history (user_id, check_time) VALUES (11188, '2020-05-19 20:30:42');
INSERT INTO public.check_history (user_id, check_time) VALUES (11188, '2020-05-18 20:28:04');

使用SQL查询

with RECURSIVE with_check_his as
  (SELECT  user_id, date(check_time) check_time, date_part('day', current_date::timestamp-date(check_time)) day_num 
  from check_history
  UNION ALL 
  select h.user_id, date(h.check_time) check_time, date_part('day', current_date::timestamp-date(h.check_time)) day_num
  FROM with_check_his w, check_history h WHERE h.user_id=w.user_id AND date(h.check_time)=date(w.check_time)-1) 
SELECT  user_id, max(day_num)+1
FROM 
  ( SELECT  user_id, check_time, day_num, count(*)
  FROM with_check_his
  GROUP BY user_id, check_time, day_num
  HAVING count(*)=day_num+1) a_w
GROUP BY user_id;

 user_id    | check_num 
------------+----------
       1093 |        4
      11111 |        3
      11113 |        1

postgresql中with RECURSIVE as类似于Oracle的start with connect by递归查询，date_part计算签到天数距离今天的天数，然后递归去查前一天有没有签到date(h.check_time)=date(w.check_time)-1，如果用户是连续签到，当天的签到记录就会+1，递归查询之后判断day_num+1的值与某天的签到记录条数是否相等即可。

拿其中一个用户举例：

emcs=# select * from check_history where user_id=11111 order by 2 desc;
 user_id |     check_time      
---------+---------------------
   11111 | 2020-05-21 14:45:00
   11111 | 2020-05-20 11:17:38
   11111 | 2020-05-19 11:24:27
   11111 | 2020-05-15 21:14:19
   11111 | 2020-05-14 11:53:53
   11111 | 2020-05-12 14:13:36
   11111 | 2020-05-09 15:34:38
   11111 | 2020-04-28 09:45:41
   11111 | 2020-04-20 10:23:40


emcs=# with RECURSIVE with_check_his as
emcs-#                    (SELECT  user_id, date(check_time) check_time, date_part('day', current_date::timestamp-date(check_time)) day_num
emcs(#                     from check_history
emcs(#                     UNION ALL
emcs(#                     select h.user_id, date(h.check_time) check_time, date_part('day', current_date::timestamp-date(h.check_time)) day_num
emcs(#                     FROM with_check_his w, check_history h WHERE h.user_id=w.user_id AND date(h.check_time)=date(w.check_time)-1)
emcs-# select * from with_check_his where user_id=11111 order by 1,2 desc;
 user_id | check_time | day_num 
---------+------------+---------
   11111 | 2020-05-21 |       0
   11111 | 2020-05-20 |       1
   11111 | 2020-05-20 |       1
   11111 | 2020-05-19 |       2
   11111 | 2020-05-19 |       2
   11111 | 2020-05-19 |       2
   11111 | 2020-05-15 |       6
   11111 | 2020-05-14 |       7
   11111 | 2020-05-14 |       7
   11111 | 2020-05-12 |       9
   11111 | 2020-05-09 |      12
   11111 | 2020-04-28 |      23
   11111 | 2020-04-20 |      31
(13 rows)

从上面可以看出11111用户连续签到天数为3天。

postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

PostgreSQL计算连续签到天数SQL，with RECURSIVE as递归查询

需求

创建测试表

插入测试数据

使用SQL查询

评论