SQL每日一题(20211124)每个用户从第一次购买，计算其7天内的购买数量，然后再以此类推计算后面每隔7天的购买数量

time丶sand

于 2022-05-20 08:36:50 发布

阅读量585

点赞数

分类专栏：每日一练文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/weixin_44681259/article/details/124851717

版权

每日一练专栏收录该内容

119 篇文章 17 订阅

订阅专栏

SQL每日一题(20220428)
在这里插入图片描述

SELECT d.id, d.date, d.num
FROM (SELECT c.dt, c.id, sum(c.num) num, min(c.date) date
      FROM (SELECT a.id, a.da, b.date, FLOOR((b.date - a.da) / 7) dt, b.num
            FROM (SELECT id, min(date) da, sum(num) num FROM t1124 GROUP BY id) a
                     RIGHT JOIN (SELECT * FROM t1124) b ON a.id = b.id) c
      GROUP BY c.dt, c.id
      ORDER BY c.id) d

select id, aa, sum(num) num
from (select a.id,
             a.date,
             a.num,
             ifnull(if(ifnull(datediff(a.date, b.date), right(a.date, 1)) >= 7, b.num,
                       ifnull(datediff(a.date, b.date), right(a.date, 1))), a.num) aa
      from (select *, row_number() over (partition by id) rnk from t1124) a left
               join(select *, row_number() over () rnk from t1124) b on a.rnk=b.rnk+1) p
group by aa, id