假设数据是这样的- MySQL/HIVE -假设一周是Mon-Sun
Video_day and Share_day =当天的视频/分享数,不累计
<表类>
user_id
日期
Video_day
Shares_day
tbody><<tr>23672 2023-04-26 20 7 15021 2023-04-26 2 0 15021 2023-04-26 1 0 15021 2023-04-26 1 1 15021 2023-04-26 17 4 6241 2023-04-26 12 6 168 2023-04-26 5 2 表类>
根据用户和周数对数据进行分组,以获得每周符合条件的用户
然后再按用户分组,以获得哪些用户符合至少4周的资格。
请注意,30天覆盖超过4周,并且一些用户可以在30天间隔的开始或结束的部分周中获得资格。
SELECT user_id
FROM (
SELECT user_id, WEEK(date,1) as week_num, SUM(video_day), SUM(shares_day)
FROM table
WHERE date >= DATE_ADD(NOW(), INTERVAL -30 DAY)
GROUP BY WEEK(date,1)
HAVING SUM(video_day) > 0 or SUM(shares_day) > 0
) week_report
GROUP BY user_id
HAVING COUNT(week_num) >= 4