PostgreSQL：结合RANK()实现日期区间内3天窗口的Track最高计数排名

你之前的查询报错是因为窗口函数的ORDER BY子句指定的是count(tracks)（数值类型），此时RANGE会基于该数值进行范围计算，而非日期字段，所以无法识别'2 days'这样的时间间隔。要实现连续3天的滑动窗口统计，需要先按日期拆分每日数据，再基于日期计算滚动聚合，最后提取每个用户的最大3天计数并排名。

以下是符合需求的查询，尽量保留了你原有的关联结构：

WITH daily_track_counts AS (
    -- 第一步：统计每个user_resort_registration每日的track数量
    SELECT
        u.*,
        urr.id AS user_resort_registration_id,
        urr.*,
        rd.date AS resort_day_date,
        COUNT(t.id) AS daily_track_count
    FROM users u
    INNER JOIN user_resort_registrations urr ON urr.user_id = u.id
    INNER JOIN resort_days rd ON urr.id = rd.user_resort_registration_id
    INNER JOIN resorts r ON urr.resort_id = r.id
    INNER JOIN tracks t ON rd.id = t.resort_day_id
    WHERE rd.date >= '2023-03-01T07:00:00' 
      AND rd.date <= '2023-04-01T05:59:59' 
      AND r.identifier = 'SOME_IDENTIFIER'
    GROUP BY u.id, urr.id, rd.date
),
rolling_3day_totals AS (
    -- 第二步：计算每个user_resort_registration连续3天的track累计数
    SELECT
        *,
        SUM(daily_track_count) OVER (
            PARTITION BY user_resort_registration_id
            ORDER BY resort_day_date
            RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
        ) AS three_day_total
    FROM daily_track_counts
),
max_3day_per_registration AS (
    -- 第三步：找出每个user_resort_registration的最大3天累计数及对应日期范围
    SELECT
        *,
        -- 记录该最大累计数对应的3天窗口起始日期
        resort_day_date - INTERVAL '2 days' AS window_start,
        resort_day_date AS window_end,
        -- 标记当前记录是否为该用户的最大3天计数
        RANK() OVER (
            PARTITION BY user_resort_registration_id
            ORDER BY three_day_total DESC, resort_day_date DESC
        ) AS rank_in_registration
    FROM rolling_3day_totals
)
-- 第四步：筛选每个用户的最大3天记录，并进行全局排名
SELECT
    u.*,
    urr.*,
    three_day_total AS max_3day_track_count,
    window_start,
    window_end,
    RANK() OVER (ORDER BY three_day_total DESC) AS global_position
FROM max_3day_per_registration
WHERE rank_in_registration = 1
LIMIT 25;

查询逻辑说明 #

1. 每日统计层：先拆分到user_resort_registration+日期维度，获取每日的track计数，这是滑动窗口计算的基础。
2. 滚动聚合层：通过PARTITION BY user_resort_registration_id确保每个用户独立计算，ORDER BY resort_day_date让窗口按日期排序，RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW定义了“当前日期及往前2天”的连续3天窗口，累计该窗口内的track总数。
3. 最大值提取层：对每个用户的所有3天窗口结果排序，标记出累计数最大的记录（如果有多个日期窗口累计数相同，取最晚的窗口）。
4. 全局排名层：筛选出每个用户的最大3天记录，并按累计数进行全局排名，符合你原查询的ORDER BY count(tracks) DESC排名逻辑。

关键注意点 #

• PostgreSQL 14支持基于时间间隔的RANGE窗口，这是实现连续日期滚动聚合的核心特性。
• 保留了原查询中所有表的关联关系，确保结果包含users和user_resort_registrations的全部字段。

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