Redshift多列分组聚合及获取用户最高概率国家方法咨询

Hey there! Let's tackle your two Redshift questions one by one—since Redshift uses PostgreSQL-based syntax, it's a bit different from MySQL, so I get why you might have hit a snag even though the problems seem straightforward.

1. Group by Multiple Columns and Aggregate the "Last" Column #

Let’s assume you have a table (say activity_log) with columns like category, subcategory, event_time, metric_value, and you want to group by category + subcategory to get the most recent metric_value (based on event_time) for each group. Here are two reliable methods:

Method 1: Use ROW_NUMBER() (Most Common & Clear) #

This approach ranks rows within each group, then picks the top-ranked (most recent) row:

WITH ranked_groups AS (
    SELECT
        category,
        subcategory,
        metric_value,
        event_time,
        -- Rank rows in each group by event_time (newest first)
        ROW_NUMBER() OVER (PARTITION BY category, subcategory ORDER BY event_time DESC) AS row_rank
    FROM activity_log
)
SELECT
    category,
    subcategory,
    metric_value AS last_metric_value
FROM ranked_groups
WHERE row_rank = 1;

• PARTITION BY: Defines your grouping columns (replace with your actual columns).
• ORDER BY event_time DESC: Ensures the newest entry in each group gets rank 1.
• If you need to handle ties (e.g., multiple rows with the same latest event_time), swap ROW_NUMBER() with RANK() to keep all tied rows.

Method 2: Use LAST_VALUE() #

If you prefer a single query without a CTE, use LAST_VALUE() with an adjusted window frame (Redshift’s default window only includes rows up to the current one, so we need to expand it):

SELECT DISTINCT
    category,
    subcategory,
    LAST_VALUE(metric_value) OVER (
        PARTITION BY category, subcategory
        ORDER BY event_time DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_metric_value
FROM activity_log;

The DISTINCT ensures we only get one row per group, since LAST_VALUE() repeats the same value for all rows in the group.

2. Get Unique user_id with Highest Probability Country & Its Value #

Suppose you have a table user_country_scores with user_id, country, probability, and you need each user_id paired with the country that has the highest probability (plus that probability value). Again, window functions are your friend here:

WITH ranked_countries AS (
    SELECT
        user_id,
        country,
        probability,
        -- Rank countries per user by probability (highest first)
        RANK() OVER (PARTITION BY user_id ORDER BY probability DESC) AS prob_rank
    FROM user_country_scores
)
SELECT
    user_id,
    country AS top_prob_country,
    probability AS top_probability
FROM ranked_countries
WHERE prob_rank = 1;

• Use RANK() if you want to keep all countries tied for the highest probability (you’ll get multiple rows per user in that case).
• Use ROW_NUMBER() instead if you only want one row per user (Redshift will randomly pick one tied country if there’s a tie—you can add an extra ORDER BY column like country to make it deterministic, e.g., ORDER BY probability DESC, country ASC).

Quick Note on Redshift vs MySQL Differences #

MySQL allows some non-standard GROUP BY behavior (like selecting columns not in the GROUP BY clause without aggregates), but Redshift follows strict SQL standards. Window functions are the standard, scalable way to solve these kinds of problems in Redshift—they’re more flexible and easier to maintain than messy subqueries or hacks.

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