这个查询是否适用于计算每个用户活动日期之间的最大间隔?

Question

我有以下任务:编写一个查询来获取下表中每个用户活动日期之间的最大间隔:(例如，用户1将拥有max。3天的间隔，用户2将有30天，用户3将为空)

CREATE TABLE IF NOT EXISTS `gamers` (
`id` int(6) not null,
`user_id` int(3) not null,
`active_date` DATE Not null,
PRIMARY KEY (`id`)
);
INSERT INTO `gamers` (`id`,`user_id`,`active_date`) VALUES
('1','1','2019-01-01'),
('2','1','2019-01-02'),
('3','1','2019-01-05'),
('4','2','2019-03-01'),
('5','2','2019-03-31'),
('6','3','2019-04-01');

我的解决方案如下:

SELECT g.id as id, g.user_id, MAX(total_amount_spent) OVER(PARTITION BY g.country), g.country
FROM gamers2 as g
INNER JOIN cte as c
ON c.country = g.country
WHERE install_source = 'ua' AND g.id NOT IN (SELECT id FROM cte)
GROUP BY g.country
ORDER BY g.user_id, total_amount_spent DESC
),
cte3 AS(
SELECT g.id, g.user_id, MAX(total_amount_spent) OVER(PARTITION BY g.country), g.country
FROM gamers2 as g
INNER JOIN cte2 as c
ON c.country = g.country
WHERE install_source = 'ua' AND g.id NOT IN (SELECT id FROM cte2)
GROUP BY g.country
ORDER BY g.user_id, total_amount_spent DESC
)
SELECT * FROM cte
UNION
SELECT * FROM cte2
UNION
SELECT * FROM cte3

Answer 1

我们可以这样使用LAG()和ROW_NUMBER():

WITH cte AS (
SELECT *, LAG(active_date, 1, active_date) OVER
(PARTITION BY user_id ORDER BY active_date) lag_active_date
FROM gamers2
),
cte2 AS (
SELECT *, DATEDIFF(active_date, lag_active_date) AS diff,
ROW_NUMBER() OVER (PARTITION BY user_id
ORDER BY DATEDIFF(active_date, lag_active_date) DESC) AS rn
FROM cte
)
SELECT user_id, diff
FROM cte2
WHERE rn = 1;

Answer 2

在我看来更容易读:

WITH
w_lag_val AS (
SELECT
id
, user_id
, active_date
, active_date 
- LAG(active_date) OVER(
PARTITION BY user_id ORDER BY active_date
) 
AS lag_val
FROM gamers
)
SELECT
user_id
, MAX(lag_val) AS max_gap
FROM w_lag_val
GROUP BY user_id
ORDER BY user_id
;
-- out  user_id | max_gap 
-- out ---------+---------                                                                                                                                                                                 
-- out  1       |       3
-- out  2       |      30
-- out  3       |  (null)       

Answer 3

select *, max(diff) from (
select *, DATEDIFF(  f,active_date)as diff from (
SELECT gamers.id , gamers.user_id, gamers.active_date, 
(select active_date from gamers as g1 where g1.active_date > gamers.active_date and gamers.user_id = g1.user_id  order by g1.active_date limit 1) as f
from gamers
order by gamers.id) as vv) as gg
group by user_id

有另一个解决方案