在Amazon Redshift中将每周起始日设为周三进行数据聚合

不用硬编码日期范围，直接通过日期函数动态计算每个日期所属的周三起始周，生成对应的时间线标签，以下是主流SQL方言的实现：

MySQL 实现 #

-- 动态取数据中最早的周三作为基准周起始，也可以直接写固定值如'2023-08-16'
SET @base_wed = (SELECT MIN(event_date) - INTERVAL (WEEKDAY(MIN(event_date)) - 2) % 7 DAY FROM your_table);

SELECT
    CONCAT(
        FLOOR(DATEDIFF(event_date, @base_wed) / 7) + 1,
        '. ',
        DAYOFMONTH(DATE_SUB(event_date, INTERVAL (WEEKDAY(event_date) - 2) % 7 DAY)),
        '-',
        DAYOFMONTH(DATE_SUB(event_date, INTERVAL (WEEKDAY(event_date) - 2) % 7 DAY) + INTERVAL 6 DAY)
    ) AS time_line,
    COUNT(*) AS event_count -- 替换成你需要的聚合逻辑
FROM your_table
GROUP BY time_line
ORDER BY time_line;

说明：

• WEEKDAY(date) 返回0=周一、1=周二、2=周三，通过(WEEKDAY(event_date)-2)%7算出当前日期到本周三的偏移量，往前偏移得到周起始日。
• 基于基准周三计算周序号，保证标签里的数字连续递增。

PostgreSQL 实现 #

WITH base_wed AS (
    -- 动态取最早周三，固定基准就写SELECT '2023-08-16'::DATE AS date
    SELECT MIN(event_date) - ((EXTRACT(DOW FROM MIN(event_date)) - 3)::INT % 7) * INTERVAL '1 day' AS date FROM your_table
)
SELECT
    CONCAT(
        FLOOR(DATE_PART('day', event_date - bw.date) / 7) + 1,
        '. ',
        TO_CHAR(event_date - ((EXTRACT(DOW FROM event_date) - 3)::INT % 7) * INTERVAL '1 day', 'DD'),
        '-',
        TO_CHAR(event_date - ((EXTRACT(DOW FROM event_date) - 3)::INT % 7) * INTERVAL '1 day' + INTERVAL '6 days', 'DD')
    ) AS time_line,
    COUNT(*) AS event_count -- 替换成你需要的聚合逻辑
FROM your_table, base_wed bw
GROUP BY time_line
ORDER BY time_line;

说明：

• EXTRACT(DOW FROM date) 返回0=周日、1=周一、3=周三，通过偏移量计算得到本周三起始日。

BigQuery 实现 #

DECLARE base_wed DATE DEFAULT (
    -- 动态取最早周三，固定基准就写SELECT '2023-08-16'
    SELECT DATE_SUB(MIN(event_date), INTERVAL MOD(EXTRACT(DAYOFWEEK FROM MIN(event_date)) - 4, 7) DAY) FROM your_table
);

SELECT
    CONCAT(
        CAST(FLOOR(DATE_DIFF(event_date, base_wed, DAY) / 7) + 1 AS STRING),
        '. ',
        FORMAT_DATE('%d', DATE_SUB(event_date, INTERVAL MOD(EXTRACT(DAYOFWEEK FROM event_date) - 4, 7) DAY)),
        '-',
        FORMAT_DATE('%d', DATE_ADD(DATE_SUB(event_date, INTERVAL MOD(EXTRACT(DAYOFWEEK FROM event_date) - 4, 7) DAY), INTERVAL 6 DAY))
    ) AS time_line,
    COUNT(*) AS event_count -- 替换成你需要的聚合逻辑
FROM your_table
GROUP BY time_line
ORDER BY time_line;

说明：

• BigQuery的EXTRACT(DAYOFWEEK FROM date) 返回1=周日、2=周一、4=周三，通过偏移量计算得到本周三起始日。

通用提示 #

• 如果不需要固定基准周，用MIN(event_date)动态生成最早周三，数据新增时会自动生成新的时间线标签，无需修改代码。
• 要显示完整日期（如带月份），把格式符DD改成MM-DD即可。

内容的提问来源于stack exchange，提问作者Nitish Bannur