热门标签
热门文章
- 1string操作_string操作符
- 2计算机设备安全隐患排查,安全安全隐患排查实施方案
- 3MySQL教程——基础篇
- 4java计算机毕业设计学生考勤系统小程序【附源码+远程部署+程序+mysql】
- 5刘未鹏：为什么你应该(从现在开始就)写博客_刘未鹏博客
- 6Transformer详解讲解_transformer全连接前馈网络公式解释
- 7[ctf.show.reverse] 36D杯 神光_ctfshow神光
- 8Visual Studio编译优化选项_visual studio 循环优化设置
- 9「聊设计模式」之职责链模式（Chain of Responsibility）
- 10SpringBoot 面试题（五）_springbooot事务的面试题
当前位置: article > 正文
SQL面试题练习 —— 连续签到领金币
作者：黑客灵魂 | 2024-07-10 01:48:33
赞
踩
SQL面试题练习 —— 连续签到领金币
题目来源：百度。
1 题目
有用户签到记录表，t_coin_signin，记录用户当天是否完成签到，请计算出每个用户的每个月获得的金币数量；
签到领金币规则如下：
- 用户签到获得1金币；
- 如果用户连续签到3天则第三天获得2金币，如果用户连续签到7天则第7天获得5金币；
- 连续签到7天后连续天数重置，每月签到天数重置；
样例数据
+----------+--------------+----------+ | user_id | signin_date | is_sign | +----------+--------------+----------+ | 001 | 2024-01-01 | 1 | | 001 | 2024-01-02 | 1 | | 001 | 2024-01-03 | 1 | | 001 | 2024-01-04 | 0 | | 001 | 2024-01-05 | 1 | | 001 | 2024-01-06 | 1 | | 001 | 2024-01-07 | 1 | | 001 | 2024-01-08 | 1 | | 001 | 2024-01-09 | 1 | | 001 | 2024-01-10 | 1 | | 001 | 2024-01-11 | 1 | | 001 | 2024-01-12 | 1 | | 001 | 2024-01-13 | 1 | | 001 | 2024-01-14 | 1 | | 001 | 2024-01-15 | 1 | | 001 | 2024-01-16 | 1 | | 001 | 2024-01-17 | 1 | | 001 | 2024-01-18 | 1 | | 001 | 2024-01-19 | 1 | | 001 | 2024-01-20 | 0 | | 001 | 2024-01-21 | 1 | | 001 | 2024-01-22 | 1 | | 001 | 2024-01-23 | 1 | | 001 | 2024-01-24 | 0 | | 001 | 2024-01-25 | 1 | | 001 | 2024-01-26 | 1 | | 001 | 2024-01-27 | 1 | | 001 | 2024-01-28 | 1 | | 001 | 2024-01-29 | 0 | | 001 | 2024-01-30 | 1 | | 001 | 2024-01-31 | 1 | | 001 | 2024-02-01 | 1 | | 001 | 2024-02-02 | 1 | | 001 | 2024-02-03 | 1 | | 001 | 2024-02-04 | 1 | | 001 | 2024-02-05 | 1 | | 001 | 2024-02-06 | 1 | | 001 | 2024-02-07 | 1 | | 001 | 2024-02-08 | 1 | | 001 | 2024-02-09 | 1 | | 001 | 2024-02-10 | 1 | +----------+--------------+----------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
2 建表语句
--建表语句 CREATE TABLE t_coin_signin ( user_id string COMMENT '用户ID', signin_date string COMMENT '日期', is_sign bigint COMMENT '是否签到 1-签到，0-未签到' ) COMMENT '签到领金币记录表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ; -- 插入数据 insert into t_coin_signin(user_id, signin_date, is_sign) values ('001', '2024-01-01', 1), ('001', '2024-01-02', 1), ('001', '2024-01-03', 1), ('001', '2024-01-04', 0), ('001', '2024-01-05', 1), ('001', '2024-01-06', 1), ('001', '2024-01-07', 1), ('001', '2024-01-08', 1), ('001', '2024-01-09', 1), ('001', '2024-01-10', 1), ('001', '2024-01-11', 1), ('001', '2024-01-12', 1), ('001', '2024-01-13', 1), ('001', '2024-01-14', 1), ('001', '2024-01-15', 1), ('001', '2024-01-16', 1), ('001', '2024-01-17', 1), ('001', '2024-01-18', 1), ('001', '2024-01-19', 1), ('001', '2024-01-20', 0), ('001', '2024-01-21', 1), ('001', '2024-01-22', 1), ('001', '2024-01-23', 1), ('001', '2024-01-24', 0), ('001', '2024-01-25', 1), ('001', '2024-01-26', 1), ('001', '2024-01-27', 1), ('001', '2024-01-28', 1), ('001', '2024-01-29', 0), ('001', '2024-01-30', 1), ('001', '2024-01-31', 1), ('001', '2024-02-01', 1), ('001', '2024-02-02', 1), ('001', '2024-02-03', 1), ('001', '2024-02-04', 1), ('001', '2024-02-05', 1), ('001', '2024-02-06', 1), ('001', '2024-02-07', 1), ('001', '2024-02-08', 1), ('001', '2024-02-09', 1), ('001', '2024-02-10', 1);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
3 题解
- 根据用户是否签到，判断用户是否连续签到
本题每个用户的日期记录是连续的，给出了当天用户是否签到。我们把签到日期记录为0，未签到日期记录为1。根据用户、月份进行分组,按照日期排序，得到一个用户连续签到的分组 signin_group。为方便后续处理，增加sign_month 字段。
注意： 这里面的分组数据中包含了用户未签到的日期数据，所以不是标准的连续结果，我们稍后再进行处理。
select user_id,
signin_date,
is_sign,
substr(signin_date, 1, 7) as sign_month,
sum(if(is_sign = 1, 0, 1))
over (partition by user_id,substr(signin_date, 1, 7) order by signin_date asc) as signin_group
from t_coin_signin
- 1
- 2
- 3
- 4
- 5
- 6
- 7
执行结果
+----------+--------------+----------+-------------+---------------+ | user_id | signin_date | is_sign | sign_month | signin_group | +----------+--------------+----------+-------------+---------------+ | 001 | 2024-01-01 | 1 | 2024-01 | 0 | | 001 | 2024-01-02 | 1 | 2024-01 | 0 | | 001 | 2024-01-03 | 1 | 2024-01 | 0 | | 001 | 2024-01-04 | 0 | 2024-01 | 1 | | 001 | 2024-01-05 | 1 | 2024-01 | 1 | | 001 | 2024-01-06 | 1 | 2024-01 | 1 | | 001 | 2024-01-07 | 1 | 2024-01 | 1 | | 001 | 2024-01-08 | 1 | 2024-01 | 1 | | 001 | 2024-01-09 | 1 | 2024-01 | 1 | | 001 | 2024-01-10 | 1 | 2024-01 | 1 | | 001 | 2024-01-11 | 1 | 2024-01 | 1 | | 001 | 2024-01-12 | 1 | 2024-01 | 1 | | 001 | 2024-01-13 | 1 | 2024-01 | 1 | | 001 | 2024-01-14 | 1 | 2024-01 | 1 | | 001 | 2024-01-15 | 1 | 2024-01 | 1 | | 001 | 2024-01-16 | 1 | 2024-01 | 1 | | 001 | 2024-01-17 | 1 | 2024-01 | 1 | | 001 | 2024-01-18 | 1 | 2024-01 | 1 | | 001 | 2024-01-19 | 1 | 2024-01 | 1 | | 001 | 2024-01-20 | 0 | 2024-01 | 2 | | 001 | 2024-01-21 | 1 | 2024-01 | 2 | | 001 | 2024-01-22 | 1 | 2024-01 | 2 | | 001 | 2024-01-23 | 1 | 2024-01 | 2 | | 001 | 2024-01-24 | 0 | 2024-01 | 3 | | 001 | 2024-01-25 | 1 | 2024-01 | 3 | | 001 | 2024-01-26 | 1 | 2024-01 | 3 | | 001 | 2024-01-27 | 1 | 2024-01 | 3 | | 001 | 2024-01-28 | 1 | 2024-01 | 3 | | 001 | 2024-01-29 | 0 | 2024-01 | 4 | | 001 | 2024-01-30 | 1 | 2024-01 | 4 | | 001 | 2024-01-31 | 1 | 2024-01 | 4 | | 001 | 2024-02-01 | 1 | 2024-02 | 0 | | 001 | 2024-02-02 | 1 | 2024-02 | 0 | | 001 | 2024-02-03 | 1 | 2024-02 | 0 | | 001 | 2024-02-04 | 1 | 2024-02 | 0 | | 001 | 2024-02-05 | 1 | 2024-02 | 0 | | 001 | 2024-02-06 | 1 | 2024-02 | 0 | | 001 | 2024-02-07 | 1 | 2024-02 | 0 | | 001 | 2024-02-08 | 1 | 2024-02 | 0 | | 001 | 2024-02-09 | 1 | 2024-02 | 0 | | 001 | 2024-02-10 | 1 | 2024-02 | 0 | +----------+--------------+----------+-------------+---------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 计算用户签到日期，是第几天连续
先计算出用户当月实际是第几天连续签到，增加限制用户签到状态。
select user_id,
signin_date,
is_sign,
sign_month,
signin_group,
count(signin_date)over(partition by user_id,sign_month,signin_group order by signin_date asc) as conn_sign_days
from (select user_id,
signin_date,
is_sign,
substr(signin_date, 1, 7) as sign_month,
sum(if(is_sign = 1, 0, 1))
over (partition by user_id,substr(signin_date, 1, 7) order by signin_date asc) as signin_group
from t_coin_signin) t
where is_sign = 1
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
执行结果
+----------+--------------+----------+-------------+---------------+-----------------+ | user_id | signin_date | is_sign | sign_month | signin_group | conn_sign_days | +----------+--------------+----------+-------------+---------------+-----------------+ | 001 | 2024-01-01 | 1 | 2024-01 | 0 | 1 | | 001 | 2024-01-02 | 1 | 2024-01 | 0 | 2 | | 001 | 2024-01-03 | 1 | 2024-01 | 0 | 3 | | 001 | 2024-01-05 | 1 | 2024-01 | 1 | 1 | | 001 | 2024-01-06 | 1 | 2024-01 | 1 | 2 | | 001 | 2024-01-07 | 1 | 2024-01 | 1 | 3 | | 001 | 2024-01-08 | 1 | 2024-01 | 1 | 4 | | 001 | 2024-01-09 | 1 | 2024-01 | 1 | 5 | | 001 | 2024-01-10 | 1 | 2024-01 | 1 | 6 | | 001 | 2024-01-11 | 1 | 2024-01 | 1 | 7 | | 001 | 2024-01-12 | 1 | 2024-01 | 1 | 8 | | 001 | 2024-01-13 | 1 | 2024-01 | 1 | 9 | | 001 | 2024-01-14 | 1 | 2024-01 | 1 | 10 | | 001 | 2024-01-15 | 1 | 2024-01 | 1 | 11 | | 001 | 2024-01-16 | 1 | 2024-01 | 1 | 12 | | 001 | 2024-01-17 | 1 | 2024-01 | 1 | 13 | | 001 | 2024-01-18 | 1 | 2024-01 | 1 | 14 | | 001 | 2024-01-19 | 1 | 2024-01 | 1 | 15 | | 001 | 2024-01-21 | 1 | 2024-01 | 2 | 1 | | 001 | 2024-01-22 | 1 | 2024-01 | 2 | 2 | | 001 | 2024-01-23 | 1 | 2024-01 | 2 | 3 | | 001 | 2024-01-25 | 1 | 2024-01 | 3 | 1 | | 001 | 2024-01-26 | 1 | 2024-01 | 3 | 2 | | 001 | 2024-01-27 | 1 | 2024-01 | 3 | 3 | | 001 | 2024-01-28 | 1 | 2024-01 | 3 | 4 | | 001 | 2024-01-30 | 1 | 2024-01 | 4 | 1 | | 001 | 2024-01-31 | 1 | 2024-01 | 4 | 2 | | 001 | 2024-02-01 | 1 | 2024-02 | 0 | 1 | | 001 | 2024-02-02 | 1 | 2024-02 | 0 | 2 | | 001 | 2024-02-03 | 1 | 2024-02 | 0 | 3 | | 001 | 2024-02-04 | 1 | 2024-02 | 0 | 4 | | 001 | 2024-02-05 | 1 | 2024-02 | 0 | 5 | | 001 | 2024-02-06 | 1 | 2024-02 | 0 | 6 | | 001 | 2024-02-07 | 1 | 2024-02 | 0 | 7 | | 001 | 2024-02-08 | 1 | 2024-02 | 0 | 8 | | 001 | 2024-02-09 | 1 | 2024-02 | 0 | 9 | | 001 | 2024-02-10 | 1 | 2024-02 | 0 | 10 | +----------+--------------+----------+-------------+---------------+-----------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 处理签到天数
使用 mod 函数，对 conn_sign_days 进行处理，每7天重置，这里会得到 0~6 的结果，其中 1~6 为准确连续天数，0 代表第7天，需要特殊处理一下。
select user_id, signin_date, is_sign, sign_month, signin_group, if(mod(count(signin_date) over (partition by user_id,sign_month,signin_group order by signin_date), 7) = 0, 7, mod(count(signin_date) over (partition by user_id,sign_month,signin_group order by signin_date), 7)) as conn_sign_days from (select user_id, signin_date, is_sign, substr(signin_date, 1, 7) as sign_month, sum(if(is_sign = 1, 0, 1)) over (partition by user_id,substr(signin_date, 1, 7) order by signin_date asc) as signin_group from t_coin_signin) t where is_sign = 1
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
执行结果
+----------+--------------+----------+-------------+---------------+-----------------+ | user_id | signin_date | is_sign | sign_month | signin_group | conn_sign_days | +----------+--------------+----------+-------------+---------------+-----------------+ | 001 | 2024-01-01 | 1 | 2024-01 | 0 | 1 | | 001 | 2024-01-02 | 1 | 2024-01 | 0 | 2 | | 001 | 2024-01-03 | 1 | 2024-01 | 0 | 3 | | 001 | 2024-01-05 | 1 | 2024-01 | 1 | 1 | | 001 | 2024-01-06 | 1 | 2024-01 | 1 | 2 | | 001 | 2024-01-07 | 1 | 2024-01 | 1 | 3 | | 001 | 2024-01-08 | 1 | 2024-01 | 1 | 4 | | 001 | 2024-01-09 | 1 | 2024-01 | 1 | 5 | | 001 | 2024-01-10 | 1 | 2024-01 | 1 | 6 | | 001 | 2024-01-11 | 1 | 2024-01 | 1 | 7 | | 001 | 2024-01-12 | 1 | 2024-01 | 1 | 1 | | 001 | 2024-01-13 | 1 | 2024-01 | 1 | 2 | | 001 | 2024-01-14 | 1 | 2024-01 | 1 | 3 | | 001 | 2024-01-15 | 1 | 2024-01 | 1 | 4 | | 001 | 2024-01-16 | 1 | 2024-01 | 1 | 5 | | 001 | 2024-01-17 | 1 | 2024-01 | 1 | 6 | | 001 | 2024-01-18 | 1 | 2024-01 | 1 | 7 | | 001 | 2024-01-19 | 1 | 2024-01 | 1 | 1 | | 001 | 2024-01-21 | 1 | 2024-01 | 2 | 1 | | 001 | 2024-01-22 | 1 | 2024-01 | 2 | 2 | | 001 | 2024-01-23 | 1 | 2024-01 | 2 | 3 | | 001 | 2024-01-25 | 1 | 2024-01 | 3 | 1 | | 001 | 2024-01-26 | 1 | 2024-01 | 3 | 2 | | 001 | 2024-01-27 | 1 | 2024-01 | 3 | 3 | | 001 | 2024-01-28 | 1 | 2024-01 | 3 | 4 | | 001 | 2024-01-30 | 1 | 2024-01 | 4 | 1 | | 001 | 2024-01-31 | 1 | 2024-01 | 4 | 2 | | 001 | 2024-02-01 | 1 | 2024-02 | 0 | 1 | | 001 | 2024-02-02 | 1 | 2024-02 | 0 | 2 | | 001 | 2024-02-03 | 1 | 2024-02 | 0 | 3 | | 001 | 2024-02-04 | 1 | 2024-02 | 0 | 4 | | 001 | 2024-02-05 | 1 | 2024-02 | 0 | 5 | | 001 | 2024-02-06 | 1 | 2024-02 | 0 | 6 | | 001 | 2024-02-07 | 1 | 2024-02 | 0 | 7 | | 001 | 2024-02-08 | 1 | 2024-02 | 0 | 1 | | 001 | 2024-02-09 | 1 | 2024-02 | 0 | 2 | | 001 | 2024-02-10 | 1 | 2024-02 | 0 | 3 | +----------+--------------+----------+-------------+---------------+-----------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 计算每天得到的金币数
根据conn_sign_days 中签到第几天，得出每天应该得到多少金币。
select user_id, signin_date, is_sign, sign_month, signin_group, conn_sign_days, case when conn_sign_days = 3 then 2 when conn_sign_days = 7 then 5 else 1 end as coin_num from (select user_id, signin_date, is_sign, sign_month, signin_group, if(mod(count(signin_date) over (partition by user_id,sign_month,signin_group order by signin_date asc), 7) = 0, 7, mod(count(signin_date) over (partition by user_id,sign_month,signin_group order by signin_date asc), 7)) as conn_sign_days from (select user_id, signin_date, is_sign, substr(signin_date, 1, 7) as sign_month, sum(if(is_sign = 1, 0, 1)) over (partition by user_id,substr(signin_date, 1, 7) order by signin_date asc) as signin_group from t_coin_signin) t where is_sign = 1) tt
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
执行结果
+----------+--------------+----------+-------------+---------------+-----------------+-----------+ | user_id | signin_date | is_sign | sign_month | signin_group | conn_sign_days | coin_num | +----------+--------------+----------+-------------+---------------+-----------------+-----------+ | 001 | 2024-01-01 | 1 | 2024-01 | 0 | 1 | 1 | | 001 | 2024-01-02 | 1 | 2024-01 | 0 | 2 | 1 | | 001 | 2024-01-03 | 1 | 2024-01 | 0 | 3 | 2 | | 001 | 2024-01-05 | 1 | 2024-01 | 1 | 1 | 1 | | 001 | 2024-01-06 | 1 | 2024-01 | 1 | 2 | 1 | | 001 | 2024-01-07 | 1 | 2024-01 | 1 | 3 | 2 | | 001 | 2024-01-08 | 1 | 2024-01 | 1 | 4 | 1 | | 001 | 2024-01-09 | 1 | 2024-01 | 1 | 5 | 1 | | 001 | 2024-01-10 | 1 | 2024-01 | 1 | 6 | 1 | | 001 | 2024-01-11 | 1 | 2024-01 | 1 | 7 | 5 | | 001 | 2024-01-12 | 1 | 2024-01 | 1 | 1 | 1 | | 001 | 2024-01-13 | 1 | 2024-01 | 1 | 2 | 1 | | 001 | 2024-01-14 | 1 | 2024-01 | 1 | 3 | 2 | | 001 | 2024-01-15 | 1 | 2024-01 | 1 | 4 | 1 | | 001 | 2024-01-16 | 1 | 2024-01 | 1 | 5 | 1 | | 001 | 2024-01-17 | 1 | 2024-01 | 1 | 6 | 1 | | 001 | 2024-01-18 | 1 | 2024-01 | 1 | 7 | 5 | | 001 | 2024-01-19 | 1 | 2024-01 | 1 | 1 | 1 | | 001 | 2024-01-21 | 1 | 2024-01 | 2 | 1 | 1 | | 001 | 2024-01-22 | 1 | 2024-01 | 2 | 2 | 1 | | 001 | 2024-01-23 | 1 | 2024-01 | 2 | 3 | 2 | | 001 | 2024-01-25 | 1 | 2024-01 | 3 | 1 | 1 | | 001 | 2024-01-26 | 1 | 2024-01 | 3 | 2 | 1 | | 001 | 2024-01-27 | 1 | 2024-01 | 3 | 3 | 2 | | 001 | 2024-01-28 | 1 | 2024-01 | 3 | 4 | 1 | | 001 | 2024-01-30 | 1 | 2024-01 | 4 | 1 | 1 | | 001 | 2024-01-31 | 1 | 2024-01 | 4 | 2 | 1 | | 001 | 2024-02-01 | 1 | 2024-02 | 0 | 1 | 1 | | 001 | 2024-02-02 | 1 | 2024-02 | 0 | 2 | 1 | | 001 | 2024-02-03 | 1 | 2024-02 | 0 | 3 | 2 | | 001 | 2024-02-04 | 1 | 2024-02 | 0 | 4 | 1 | | 001 | 2024-02-05 | 1 | 2024-02 | 0 | 5 | 1 | | 001 | 2024-02-06 | 1 | 2024-02 | 0 | 6 | 1 | | 001 | 2024-02-07 | 1 | 2024-02 | 0 | 7 | 5 | | 001 | 2024-02-08 | 1 | 2024-02 | 0 | 1 | 1 | | 001 | 2024-02-09 | 1 | 2024-02 | 0 | 2 | 1 | | 001 | 2024-02-10 | 1 | 2024-02 | 0 | 3 | 2 | +----------+--------------+----------+-------------+---------------+-----------------+-----------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 计算没人每月得到的金币数
select user_id, sign_month, sum(coin_num) as month_coin_num from (select user_id, signin_date, is_sign, sign_month, signin_group, conn_sign_days, case when conn_sign_days = 3 then 2 when conn_sign_days = 7 then 5 else 1 end as coin_num from (select user_id, signin_date, is_sign, sign_month, signin_group, if(mod(count(signin_date) over (partition by user_id,sign_month,signin_group order by signin_date asc), 7) = 0, 7, mod(count(signin_date) over (partition by user_id,sign_month,signin_group order by signin_date asc), 7)) as conn_sign_days from (select user_id, signin_date, is_sign, substr(signin_date, 1, 7) as sign_month, sum(if(is_sign = 1, 0, 1)) over (partition by user_id,substr(signin_date, 1, 7) order by signin_date asc) as signin_group from t_coin_signin) t where is_sign = 1) tt) ttt group by user_id, sign_month
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
执行结果
+----------+-------------+-----------------+
| user_id | sign_month | month_coin_num |
+----------+-------------+-----------------+
| 001 | 2024-01 | 40 |
| 001 | 2024-02 | 16 |
+----------+-------------+-----------------+
- 1
- 2
- 3
- 4
- 5
- 6
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/黑客灵魂/article/detail/804395
推荐阅读
相关标签
