如何在Metabase中按周统计跨日期区间的MyClasses记录数

1. 生成目标周区间 #

首先生成包含所有课程涉及日期的周范围（默认以ISO周为准，每周从周一开始）：

WITH weeks AS (
    SELECT 
        generate_series(
            date_trunc('week', MIN(startDate))::date,
            date_trunc('week', MAX(endDate))::date,
            interval '1 week'
        ) AS week_start,
        generate_series(
            date_trunc('week', MIN(startDate))::date + interval '6 days',
            date_trunc('week', MAX(endDate))::date + interval '6 days',
            interval '1 week'
        ) AS week_end
    FROM MyClasses
)

2. 关联课程统计覆盖数量 #

通过判断课程时间区间与周区间的重叠关系（课程startDate ≤ 周结束日，且课程endDate ≥ 周开始日），按周分组统计覆盖的课程数：

WITH weeks AS (
    SELECT 
        generate_series(
            date_trunc('week', MIN(startDate))::date,
            date_trunc('week', MAX(endDate))::date,
            interval '1 week'
        ) AS week_start,
        generate_series(
            date_trunc('week', MIN(startDate))::date + interval '6 days',
            date_trunc('week', MAX(endDate))::date + interval '6 days',
            interval '1 week'
        ) AS week_end
    FROM MyClasses
)
SELECT
    TO_CHAR(w.week_start, 'YYYY-"W"IW') AS week_label, -- 格式化为「2022-W31」样式的周标识
    COUNT(DISTINCT mc.id) AS course_count
FROM weeks w
LEFT JOIN MyClasses mc 
    ON mc.startDate <= w.week_end
    AND mc.endDate >= w.week_start
GROUP BY w.week_start, w.week_end
ORDER BY w.week_start;

3. 示例数据验证结果 #

针对你的示例数据：

• class1（2022-08-01至2022-08-03）：覆盖2022年第31周（2022-08-01至2022-08-07）
• class2（2022-08-01至2022-08-08）：覆盖第31周、第32周（2022-08-08至2022-08-14）
• class3（2022-08-03至2022-08-16）：覆盖第31周、第32周、第33周（2022-08-15至2022-08-21）

执行SQL后会得到符合需求的结果：

4. 自定义周起始日（可选） #

如果需要以周日作为周起始日，可调整周区间的生成逻辑：

-- 以周日为周起始的周区间生成
generate_series(
    date_trunc('week', MIN(startDate))::date - interval '1 day',
    date_trunc('week', MAX(endDate))::date - interval '1 day',
    interval '1 week'
) AS week_start

对应的周结束日为week_start + interval '6 days'。

内容的提问来源于stack exchange，提问作者Yayo Arellano