大数据SQL题38 连续签到领金币数

原题链接：http://practice.atguigu.cn/#/question/38/desc?qType=SQL

题目需求

用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。

每连续签到7天重新累积签到天数。

从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序

期望结果如下：

user_id（用户id）	sum_coin_cn（金币总数)
101	7
109	3
107	3
102	3
106	2
104	2
103	2
1010	2
108	1
105	1

需要用到的表：

用户登录明细表：user_login_detail

user_id(用户id)	ip_address(ip地址)	login_ts(登录时间)	logout_ts(登出时间)
101	180.149.130.161	2021-09-21 08:00:00	2021-09-27 08:30:00
102	120.245.11.2	2021-09-22 09:00:00	2021-09-27 09:30:00
103	27.184.97.3	2021-09-23 10:00:00	2021-09-27 10:30:00

解题思路

1. 连续登录天数映射法
   1. 本题难点主要在于连续登录额外金币数量的计算，因此首先需要找出每个用户的所有连续登录情况，在这里可以使用ROW_NUMBER() 打标然后分组获取
   2. 获取到连续登录的分组后，首先计算分组连续登录天数x，这是用户登录的基础金币来源
   3. 由于连续登录的金币奖励是周期循环的（如下表所示），因此我们需要找到x到3天、7天奖励的映射公式
   4. 可以推断出，获取3天奖励的次数n = (x+4)/7，获取7天奖励的次数n = x/7 ，n向下取整，即可计算出额外金币的总数量(x+4)/7*2 + x/7*6

n（连续登录奖励轮次）	获取3天奖励时x对应天数	获取7天奖励时x对应天数
1	3	7
2	10	14
3	17	21
…	…	…
n	3n+4(n-1) = 7n-4	7n

SELECT  user_id,
        SUM(login_days + FLOOR(login_days/7)*6 + FLOOR((login_days+4)/7)*2) AS sum_coin_cn
FROM
(
	SELECT  user_id,
	        DATE_SUB(login_date,rn) AS flag,
	        COUNT(1)                AS login_days
	FROM
	(
		SELECT  user_id,
		        date(login_ts)                                                    AS login_date,
		        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY  date(login_ts)) AS rn
		FROM user_login_detail
		GROUP BY  user_id,
		          date(login_ts)
	) t1
	GROUP BY  user_id,
	          DATE_SUB(login_date,rn)
) t2
GROUP BY  user_id
ORDER BY  sum_coin_cn DESC

1. 按天计算法

首先同样找出每个用户的所有连续登录区间，随后对区间内的每一天，求出当天可以获得的金币数量，最后求和。

SELECT  user_id,
        SUM(coins) AS sum_coin_cn
FROM
(
	SELECT  user_id,
	        CASE WHEN rn % 7 = 3 THEN 3
	             WHEN rn % 7 = 0 THEN 7  ELSE 1 END AS coins
	FROM
	(
		SELECT  user_id,
		        ROW_NUMBER() OVER (PARTITION BY (user_id,DATE_SUB(login_date,rn)) ORDER BY  login_date) AS rn
		FROM
		(
			SELECT  user_id,
			        date(login_ts)                                                    AS login_date,
			        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY  date(login_ts)) AS rn
			FROM user_login_detail
			GROUP BY  user_id,
			          date(login_ts)
		) t1
	) t2
) t3
GROUP BY  user_id
ORDER BY  sum_coin_cn DESC