Hive SQL带条件抽样：按区域设备筛选当日前6用户全事件

Hey Andrew, let's break this down step by step—since you're new to RANK(), I'll explain exactly how it fits into solving your problem.

Your goal is to grab 6 specific users (one per region-device combo: US iOS, US Android, US PC, EU iOS, EU Android, EU PC) and all their events from the current day's partition. Here's a working SQL example tailored to your use case, with explanations:

Solution SQL #

WITH target_users AS (
    -- First, identify the 6 users we need: 1 per (region, device) combo
    SELECT 
        user_id,
        region,
        device
    FROM (
        -- Rank users within each region-device group by their earliest event time
        SELECT 
            user_id,
            region,
            device,
            RANK() OVER (
                PARTITION BY region, device 
                ORDER BY MIN(datetime) ASC
            ) AS user_rank
        FROM game_events
        -- Filter to only the current day's partition (adjust date logic if needed for your SQL dialect)
        WHERE DATE(datetime) = CURRENT_DATE()
        -- Group by user + region + device to handle users who might have events across multiple regions/devices
        GROUP BY user_id, region, device
    ) ranked_users
    -- Pick the first user (earliest event) in each target region-device combo
    WHERE user_rank = 1
    AND (region, device) IN (
        ('US', 'iOS'), ('US', 'Android'), ('US', 'PC'),
        ('EU', 'iOS'), ('EU', 'Android'), ('EU', 'PC')
    )
)
-- Get all current-day events for our target users
SELECT 
    ge.*
FROM game_events ge
JOIN target_users tu 
    ON ge.user_id = tu.user_id 
    AND DATE(ge.datetime) = CURRENT_DATE()
-- Optional: Order results by region, device, and event time for readability
ORDER BY tu.region, tu.device, ge.datetime;

Key Explanations #

Let's unpack the parts that use RANK() and other critical logic:

1. Window Function with RANK():

   • PARTITION BY region, device: Splits our data into separate groups for each region-device pair (e.g., all US iOS users, all EU Android users, etc.).
   • ORDER BY MIN(datetime) ASC: Sorts users within each group by the earliest time they triggered an event in that region-device combo.
   • RANK() assigns a number to each user in the group—rank 1 goes to the user with the earliest event.

2. Filtering Target Users:

   • We keep only users where user_rank = 1 to get the first user in each region-device group.
   • The IN clause explicitly limits us to the 6 exact combos you need, so we don't accidentally include other regions/devices.

3. Joining Back to Get All Events:

   • Once we have our 6 target users, we join back to the original table to fetch every event they triggered that day (including duplicates, as you requested).

Adjustments for Your SQL Dialect #

Depending on which database you're using, you might need to tweak the date logic:

• SQL Server: Replace DATE(datetime) = CURRENT_DATE() with CONVERT(date, datetime) = GETDATE()
• Oracle: Use TRUNC(datetime) = SYSDATE
• BigQuery: DATE(datetime) = CURRENT_DATE() works as-is

This approach efficiently uses your date partition (since we're filtering to the current day upfront) and ensures you get exactly the users and events you need.

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