使用KQL从指定日期（2023年4月1日）起每周识别新增用户

核心思路是先定位每个用户的首次出现日期，再将该日期映射到以2023-04-01为起点的统计周，最终按周筛选出仅在当周首次出现的用户（即新增用户）。

假设你的表名为user_activity，包含字段event_time（datetime类型，记录用户活动时间）和email（用户唯一标识），以下是主流数据库的实现代码：

MySQL 版本 #

-- 计算每个用户的首次出现时间
WITH user_first_seen AS (
    SELECT 
        email,
        MIN(event_time) AS first_seen_time
    FROM user_activity
    WHERE event_time >= '2023-04-01'
    GROUP BY email
),
-- 将首次时间映射到对应统计周
user_week_group AS (
    SELECT 
        email,
        first_seen_time,
        -- 生成从2023-04-01开始的周编号（0代表第一周）
        FLOOR(DATEDIFF(DATE(first_seen_time), '2023-04-01') / 7) AS week_idx,
        -- 计算周起止日期用于展示
        DATE_ADD('2023-04-01', INTERVAL FLOOR(DATEDIFF(DATE(first_seen_time), '2023-04-01') / 7) * 7 DAY) AS week_start,
        DATE_ADD('2023-04-01', INTERVAL (FLOOR(DATEDIFF(DATE(first_seen_time), '2023-04-01') / 7) + 1) * 7 DAY - 1 DAY) AS week_end
    FROM user_first_seen
)
-- 按周输出新增用户
SELECT 
    week_idx + 1 AS 周数,
    week_start AS 周起始日期,
    week_end AS 周结束日期,
    GROUP_CONCAT(DISTINCT email ORDER BY email) AS 新增用户邮箱列表
FROM user_week_group
GROUP BY week_idx, week_start, week_end
ORDER BY week_idx;

PostgreSQL 版本 #

WITH user_first_seen AS (
    SELECT 
        email,
        MIN(event_time) AS first_seen_time
    FROM user_activity
    WHERE event_time >= '2023-04-01'::DATE
    GROUP BY email
),
user_week_group AS (
    SELECT 
        email,
        first_seen_time,
        FLOOR(DATE_PART('day', DATE(first_seen_time) - '2023-04-01'::DATE) / 7) AS week_idx,
        ('2023-04-01'::DATE + FLOOR(DATE_PART('day', DATE(first_seen_time) - '2023-04-01'::DATE) / 7) * 7) AS week_start,
        ('2023-04-01'::DATE + (FLOOR(DATE_PART('day', DATE(first_seen_time) - '2023-04-01'::DATE) / 7) + 1) * 7 - 1) AS week_end
    FROM user_first_seen
)
SELECT 
    week_idx + 1 AS 周数,
    week_start AS 周起始日期,
    week_end AS 周结束日期,
    STRING_AGG(DISTINCT email, ', ' ORDER BY email) AS 新增用户邮箱列表
FROM user_week_group
GROUP BY week_idx, week_start, week_end
ORDER BY week_idx;

SQL Server 版本 #

WITH user_first_seen AS (
    SELECT 
        email,
        MIN(event_time) AS first_seen_time
    FROM user_activity
    WHERE event_time >= '2023-04-01'
    GROUP BY email
),
user_week_group AS (
    SELECT 
        email,
        first_seen_time,
        FLOOR(DATEDIFF(day, '2023-04-01', CAST(first_seen_time AS DATE)) / 7.0) AS week_idx,
        DATEADD(day, FLOOR(DATEDIFF(day, '2023-04-01', CAST(first_seen_time AS DATE)) / 7.0) * 7, '2023-04-01') AS week_start,
        DATEADD(day, (FLOOR(DATEDIFF(day, '2023-04-01', CAST(first_seen_time AS DATE)) / 7.0) + 1) * 7 - 1, '2023-04-01') AS week_end
    FROM user_first_seen
)
SELECT 
    CAST(week_idx + 1 AS INT) AS 周数,
    week_start AS 周起始日期,
    week_end AS 周结束日期,
    STRING_AGG(DISTINCT email, ', ') WITHIN GROUP (ORDER BY email) AS 新增用户邮箱列表
FROM user_week_group
GROUP BY week_idx, week_start, week_end
ORDER BY week_idx;

补充说明 #

• 上述代码以每7天为一个统计周（从2023-04-01开始），如果需要按自然周（如周一至周日）划分，可替换周编号计算逻辑为数据库内置的周函数（如MySQL的WEEK()、PostgreSQL的DATE_PART('week', ...)），注意调整周起始日参数确保与需求匹配。
• 若需单独查询某一周的新增用户，在最后一步SELECT前添加WHERE week_idx = N（N为周编号，0对应第一周，1对应第二周，以此类推）即可。
• 建议给event_time字段创建索引，提升大表下MIN(event_time)的计算效率。

内容的提问来源于stack exchange，提问作者Neeraj Malik