Amazon Redshift SQL实现：按两类分组计算2021年1月月度数据的总计平均值

Alright, let's work through how to write the right Redshift SQL for your scenario. From what you described, you have monthly data filtered to January 2021, and need to calculate averages of totals grouped by two categories. I'll cover common variations based on typical use cases matching your "green box" result.

First, Define Your Base Query (Grouped Totals) #

First, let's start by getting the total value for each combination of your two categories in January 2021. I'll assume your table has:

• A date column (e.g., transaction_date) to filter the month
• Two category columns (e.g., region and product_type)
• A numeric value column (e.g., sales_amount) to aggregate

SELECT
    region AS category1,  -- Replace with your first category column
    product_type AS category2,  -- Replace with your second category column
    SUM(sales_amount) AS total_value
FROM
    your_monthly_data_table  -- Replace with your actual table name
WHERE
    -- Filter for January 2021 using Redshift's date_trunc function
    date_trunc('month', transaction_date) = '2021-01-01'
GROUP BY
    region,
    product_type;

This query gives you the total for each (category1, category2) pair—this is the foundation for calculating your averages.

Calculate Averages of These Totals #

Now, depending on what exactly you need in that green box, here are two common scenarios:

Scenario 1: Global Average of All Grouped Totals #

If you want the average of every (category1, category2) total across the entire dataset, use a window function with no partitioning:

SELECT
    category1,
    category2,
    total_value,
    -- Calculate the average of all total_value rows
    AVG(total_value) OVER () AS overall_average_of_totals
FROM (
    -- Inner query: Get grouped totals as before
    SELECT
        region AS category1,
        product_type AS category2,
        SUM(sales_amount) AS total_value
    FROM
        your_monthly_data_table
    WHERE
        date_trunc('month', transaction_date) = '2021-01-01'
    GROUP BY
        region,
        product_type
) AS grouped_totals;

Scenario 2: Average by One Category (e.g., Average per Category1) #

If you want the average of totals grouped by just the first category (e.g., average product type total per region), add a PARTITION BY clause to the window function:

SELECT
    category1,
    category2,
    total_value,
    -- Calculate average total_value per category1
    AVG(total_value) OVER (PARTITION BY category1) AS average_by_category1
FROM (
    SELECT
        region AS category1,
        product_type AS category2,
        SUM(sales_amount) AS total_value
    FROM
        your_monthly_data_table
    WHERE
        date_trunc('month', transaction_date) = '2021-01-01'
    GROUP BY
        region,
        product_type
) AS grouped_totals;

Scenario 3: Just the Overall Average (No Group Details) #

If you only need the single average value of all grouped totals, simplify to:

SELECT
    AVG(total_value) AS overall_average_of_totals
FROM (
    SELECT
        SUM(sales_amount) AS total_value
    FROM
        your_monthly_data_table
    WHERE
        date_trunc('month', transaction_date) = '2021-01-01'
    GROUP BY
        region,
        product_type
) AS grouped_totals;

Key Notes for Redshift #

• Date Filtering: date_trunc('month', date_column) converts any date in January 2021 to 2021-01-01, making it easy to filter the entire month. If your date is stored as a string, cast it first: date_trunc('month', CAST(date_string AS DATE)).
• Replace Placeholders: Swap out your_monthly_data_table, category1, category2, sales_amount, and transaction_date with your actual table/column names.
• Window Functions: Redshift fully supports these window functions, so they'll perform efficiently even on large datasets.

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