PostgreSQL按周窗口计数：统计近30天获3+评论的去重影片

Alright, let's figure out how to add that 4th column you're stuck on. The core challenge is counting films that got 3+ reviews in the same 30-day window you're using for column 3. Here's how to weave that into your existing PostgreSQL query:

WITH week_dates AS (
    -- Generate all unique week start dates (Monday as the first day of the week) from your reviews
    SELECT date(date_trunc('isoweek', updated_at::date)) AS week_start
    FROM reviews
    GROUP BY week_start
),
week_time_windows AS (
    -- Define consistent date ranges: 30-day lookback and the full week period
    -- Adjust these if your original 3rd column used a different window (e.g., 30 days before week end)
    SELECT
        week_start,
        week_start - INTERVAL '29 days' AS last30d_start,
        week_start + INTERVAL '6 days' AS week_end  -- Sunday, end of the current week
    FROM week_dates
),
film_review_counts AS (
    -- Calculate total reviews per film in the 30-day window for each week
    SELECT
        w.week_start,
        r.film_id,
        COUNT(r.review_id) AS total_reviews
    FROM week_time_windows w
    JOIN reviews r 
        ON r.updated_at BETWEEN w.last30d_start AND w.week_end
    GROUP BY w.week_start, r.film_id
),
weekly_base_stats AS (
    -- Your existing first 3 columns, aligned to the same time windows
    SELECT
        w.week_start,
        COUNT(DISTINCT r.film_id) AS weekly_reviewed_films,
        COUNT(DISTINCT CASE 
            WHEN r.updated_at BETWEEN w.last30d_start AND w.week_end 
            THEN r.film_id 
        END) AS last30d_unique_films
    FROM week_time_windows w
    LEFT JOIN reviews r 
        ON r.updated_at BETWEEN w.week_start AND w.week_end
    GROUP BY w.week_start, w.last30d_start, w.week_end
)
-- Combine all stats and add the 4th column
SELECT
    bs.week_start,
    bs.weekly_reviewed_films,
    bs.last30d_unique_films,
    -- Count distinct films with 3+ reviews in the 30-day window
    COUNT(DISTINCT frc.film_id) AS last30d_films_with_3plus_reviews
FROM weekly_base_stats bs
LEFT JOIN film_review_counts frc 
    ON bs.week_start = frc.week_start 
    AND frc.total_reviews >= 3
GROUP BY bs.week_start, bs.weekly_reviewed_films, bs.last30d_unique_films
ORDER BY bs.week_start;

Quick Explanations: #

• Monday-start weeks: We use date_trunc('isoweek', ...) because PostgreSQL's default week truncation starts on Sunday—isoweek ensures your week starts on Monday as required.
• Consistent windows: The week_time_windows CTE makes sure your 30-day lookback matches exactly between columns 3 and 4, so your stats stay aligned.
• Left joins: This preserves weeks where no films hit the 3+ review threshold (they'll show 0 for the 4th column instead of being dropped entirely).

If your original query used a different method to calculate week starts or the 30-day window, just tweak the week_time_windows CTE to match your existing logic—this structure is flexible to fit what you already have working for columns 1-3.

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