Redshift中同一SQL同时按周与自定义28日起始月分组的实现

Absolutely! You can pull both weekly and custom 28th-to-28th monthly violation totals in a single query using UNION ALL to merge two aggregated result sets. Here's how to do it:

Step-by-Step Breakdown #

We’ll split the query into two complementary parts, then merge them:

1. Weekly aggregation: Reuse your existing logic to group by SNAPSHOT_WEEK
2. Custom monthly aggregation: Calculate the 28th-to-28th period for each date, then aggregate by that period
3. Merge both sets with UNION ALL to get your combined output

Full Query Code #

-- Weekly aggregation section
SELECT 
    CASE 
        WHEN video_code = 'A' THEN 'Seller' 
        WHEN video_code = 'B' THEN 'Vendor' 
        WHEN video_code = 'C' THEN 'Others' 
    END AS CATEGORY,
    TO_CHAR(snapshot_time - DATE_PART('dow', snapshot_time)::int + 4, 'IW') AS "WEEK OR MONTH",
    SUM(VIOLATION_COUNT) AS SUM_VIOLATION_COUNT
FROM my_table
WHERE snapshot_time BETWEEN '20180505'::date - '41 days'::interval AND '20180505'::date
GROUP BY CATEGORY, "WEEK OR MONTH"

UNION ALL

-- Custom monthly aggregation section
SELECT 
    CASE 
        WHEN video_code = 'A' THEN 'Seller' 
        WHEN video_code = 'B' THEN 'Vendor' 
        WHEN video_code = 'C' THEN 'Others' 
    END AS CATEGORY,
    -- Generate "28 March" / "28 April" style period labels
    TO_CHAR(
        CASE 
            WHEN EXTRACT(DAY FROM snapshot_time) >= 28 
            THEN DATE_TRUNC('month', snapshot_time) + INTERVAL '1 month'
            ELSE DATE_TRUNC('month', snapshot_time)
        END,
        '"28" FMMonth' -- FMMonth removes extra spaces from month names
    ) AS "WEEK OR MONTH",
    SUM(VIOLATION_COUNT) AS SUM_VIOLATION_COUNT
FROM my_table
WHERE snapshot_time BETWEEN '20180505'::date - '41 days'::interval AND '20180505'::date
GROUP BY CATEGORY, "WEEK OR MONTH"

-- Optional: Add ordering to organize results
ORDER BY CATEGORY, "WEEK OR MONTH";

How the Custom Monthly Logic Works #

For any snapshot_time:

• If the day is 28 or later, the date belongs to the period ending on the next month's 28th (e.g., 2018-03-28 → "28 April")
• If the day is before 28, it belongs to the period ending on the current month's 28th (e.g., 2018-03-27 → "28 March")
• The TO_CHAR function formats the period's end month into your desired "28 [Month Name]" string.

Key Notes #

• Ensure column names and data types match exactly between the two UNION ALL sections (we use "WEEK OR MONTH" as the shared column name here)
• The FMMonth format specifier ensures month names like "March" don’t have unnecessary leading/trailing spaces
• Adjust the ORDER BY clause at the end to sort results by category and period if needed

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