Hive查询：如何通过GROUP BY与WHERE子句过滤含超5分钟会话的分组

Got it, let's iron out your query to hit the goal: only keeping sessions where every single participant has a session duration longer than 5 minutes. First, let's recap what your original query does—it's targeting sessions that include specific Gmail users (active "Person" roles as of 2018-04-12) but doesn't handle the duration filter we need. Let's fix that.

Approach 1: Use Window Functions to Validate Session-Wide Duration #

This method calculates the shortest participant duration in each session. If the shortest duration is over 5 minutes, that means all participants in the session meet the requirement. We'll fold in your existing filters too:

WITH session_duration_validation AS (
    SELECT 
        U.session_id,
        U.session_date,
        U.participant_duration,
        U.email,
        -- Get the smallest duration across all participants in the session
        MIN(U.participant_duration) OVER (PARTITION BY U.session_id) AS min_session_duration
    FROM data.usage U
    JOIN (
        SELECT distinct M.session_id 
        FROM data.usage M 
        WHERE email like '%gmail.com%' 
          AND data_date >= '20180101' 
          AND name IN (
              SELECT lower(name) 
              FROM data.users 
              WHERE role like 'Person%' 
                AND isactive = TRUE 
                AND data_date = '20180412'
          )
    ) M ON U.session_id = M.session_id
)
SELECT 
    session_id,
    session_date,
    participant_duration,
    email
FROM session_duration_validation
-- Filter sessions where all participants meet the 5-minute threshold
-- Note: Assumes duration is in seconds—swap 300 for 5 if it's in minutes
WHERE min_session_duration > 300;

Approach 2: Exclude Sessions with Any Short-Duration Participant #

If window functions aren't your vibe, you can first flag sessions that have at least one participant with a duration ≤5 minutes, then exclude those from your results:

-- First, identify sessions that don't meet our criteria (have any short participant)
WITH invalid_sessions AS (
    SELECT DISTINCT session_id
    FROM data.usage
    WHERE participant_duration <= 300
)
SELECT 
    U.session_id,
    U.session_date,
    U.participant_duration,
    U.email
FROM data.usage U
JOIN (
    SELECT distinct M.session_id 
    FROM data.usage M 
    WHERE email like '%gmail.com%' 
      AND data_date >= '20180101' 
      AND name IN (
          SELECT lower(name) 
          FROM data.users 
          WHERE role like 'Person%' 
            AND isactive = TRUE 
            AND data_date = '20180412'
      )
) M ON U.session_id = M.session_id
-- Only keep sessions where no participant has a short duration
WHERE U.session_id NOT IN (SELECT session_id FROM invalid_sessions);

Quick Notes: #

• I assumed participant_duration is measured in seconds—if it's in minutes, just replace 300 with 5 in the WHERE clauses.
• I swapped your original LEFT OUTER JOIN for a regular JOIN because it looks like you only care about sessions that match your Gmail/Person role filter. If you need to retain non-matching sessions too, just remove the WHERE M.session_id IS NOT NULL (or switch back to LEFT JOIN and adjust the logic accordingly).

内容的提问来源于stack exchange，提问作者Matt W.