SQL 案例
案例一:近60天 ,任意7天内有5天活跃的用户明细
版本:mysql 8.0
示例数据:
ftime | uid |
---|---|
20220326 | 134****0923 |
20220325 | 139****3215 |
20220325 | 191****2589 |
20220325 | 191****2589 |
20220325 | 191****2589 |
20220325 | 191****2589 |
*** |
解析
第一步:表自关联,求出日期差
注意:笛卡尔积
SELECT t1.ftime t1,t1.uid,t2.ftime t2,DATEDIFF(t1.ftime,t2.ftime) 日期差
FROM(
SELECT ftime,uid
FROM temp_table
where ftime>= date_sub(now(),interval 60 day)
and ftime<= date_format(DATE_SUB(now(),interval 1 day),"%Y%m%d")
GROUP BY ftime,uid
)t1 left join (
SELECT ftime,uid
FROM temp_table
where ftime>= date_sub(now(),interval 60 day)
and ftime<= date_format(DATE_SUB