获取过去30天每周至少有一次内容交互的用户ID（MySQL/HIVE）

解决方案 #

MySQL 实现

WITH date_range AS (
    -- 定义过去30天的时间区间
    SELECT DATE_SUB(CURDATE(), INTERVAL 30 DAY) AS start_date, CURDATE() AS end_date
),
user_weekly_interaction AS (
    -- 按用户+年+周分组，标记该周是否有交互
    SELECT 
        user_id,
        YEAR(date) AS year,
        WEEK(date, 1) AS week_num, -- 参数1指定周一为每周起始
        MAX(CASE WHEN Video_day > 0 OR Shares_day > 0 THEN 1 ELSE 0 END) AS has_interaction
    FROM your_table
    CROSS JOIN date_range
    WHERE date BETWEEN start_date AND end_date
    GROUP BY user_id, YEAR(date), WEEK(date, 1)
),
total_weeks_in_range AS (
    -- 计算过去30天覆盖的总周数
    SELECT COUNT(DISTINCT CONCAT(YEAR(date), '-', WEEK(date, 1))) AS total_week_count
    FROM your_table
    CROSS JOIN date_range
    WHERE date BETWEEN start_date AND end_date
)
-- 筛选出所有周都有交互的用户
SELECT DISTINCT ui.user_id
FROM user_weekly_interaction ui
CROSS JOIN total_weeks_in_range tw
WHERE ui.has_interaction = 1
GROUP BY ui.user_id
HAVING COUNT(ui.week_num) = tw.total_week_count;

Hive 实现

Hive默认周起始为周日，这里通过datediff基于1970-01-05（周一）计算周编号，确保周一为周起始：

WITH date_range AS (
    SELECT date_sub(current_date(), 30) AS start_date, current_date() AS end_date
),
user_weekly_interaction AS (
    SELECT 
        user_id,
        -- 生成以周一为起始的周编号
        floor(datediff(date, '1970-01-05') / 7) AS week_num,
        MAX(CASE WHEN Video_day > 0 OR Shares_day > 0 THEN 1 ELSE 0 END) AS has_interaction
    FROM your_table
    CROSS JOIN date_range
    WHERE date BETWEEN start_date AND end_date
    GROUP BY user_id, floor(datediff(date, '1970-01-05') / 7)
),
total_weeks_in_range AS (
    SELECT COUNT(DISTINCT floor(datediff(date, '1970-01-05') / 7)) AS total_week_count
    FROM your_table
    CROSS JOIN date_range
    WHERE date BETWEEN start_date AND end_date
)
SELECT DISTINCT ui.user_id
FROM user_weekly_interaction ui
CROSS JOIN total_weeks_in_range tw
WHERE ui.has_interaction = 1
GROUP BY ui.user_id
HAVING COUNT(ui.week_num) = tw.total_week_count;

逻辑说明 #

1. date_range：固定过去30天的时间范围，避免重复计算。
2. user_weekly_interaction：按用户和周分组，判断该用户在对应周是否有有效交互（只要单日视频/分享量大于0，即标记该周为有交互）。
3. total_weeks_in_range：统计时间范围内覆盖的总周数。
4. 最终通过分组统计，筛选出有交互的周数等于总周数的用户，即满足“过去30天内每周都有交互”的要求。

注：请将SQL中的your_table替换为实际表名。

内容的提问来源于stack exchange，提问作者Tino Uchiha