如何用HIVE获取同一会话下特定ID的首次IN和末次OUT记录

核心思路 #

通过会话分组将每个IN及其后续的OUT归为同一组，再分别提取每组的首次IN和末次OUT记录，最后关联并处理无OUT的填充逻辑。

实现代码 #

WITH punch_with_session AS (
    SELECT 
        ID,
        punchdate,
        punchtype,
        punchtime,
        punchdatetime,
        uuu,
        feed_date,
        -- 按ID分组、时间排序，遇到IN则开启新会话
        SUM(CASE WHEN punchtype = 'IN' THEN 1 ELSE 0 END) OVER (
            PARTITION BY ID 
            ORDER BY punchdatetime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session_id
    FROM your_table
    WHERE ID = '目标ID' -- 替换为需要查询的特定ID
),
-- 提取每个会话的首次IN记录
first_in AS (
    SELECT 
        session_id,
        ID,
        punchdate AS in_punchdate,
        punchtime AS in_punchtime,
        punchdatetime AS in_punchdatetime,
        uuu AS in_uuu,
        feed_date AS in_feed_date
    FROM punch_with_session
    WHERE punchtype = 'IN'
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY session_id, ID 
        ORDER BY punchdatetime ASC
    ) = 1
),
-- 提取每个会话的末次OUT记录
last_out AS (
    SELECT 
        session_id,
        ID,
        punchdate AS out_punchdate,
        punchtime AS out_punchtime,
        punchdatetime AS out_punchdatetime,
        uuu AS out_uuu,
        feed_date AS out_feed_date
    FROM punch_with_session
    WHERE punchtype = 'OUT'
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY session_id, ID 
        ORDER BY punchdatetime DESC
    ) = 1
)
-- 关联IN/OUT记录，无对应OUT时用current_timestamp填充
SELECT 
    fi.ID,
    fi.session_id,
    fi.in_punchdate,
    fi.in_punchtime,
    fi.in_punchdatetime,
    fi.in_uuu,
    fi.in_feed_date,
    COALESCE(lo.out_punchdate, DATE(current_timestamp())) AS out_punchdate,
    COALESCE(lo.out_punchtime, DATE_FORMAT(current_timestamp(), 'HH:mm:ss')) AS out_punchtime,
    COALESCE(lo.out_punchdatetime, current_timestamp()) AS out_punchdatetime,
    COALESCE(lo.out_uuu, fi.in_uuu) AS out_uuu, -- 若无OUT的uuu，复用IN的uuu，可按需调整
    COALESCE(lo.out_feed_date, fi.in_feed_date) AS out_feed_date -- 若无OUT的feed_date，复用IN的，可按需调整
FROM first_in fi
LEFT JOIN last_out lo 
    ON fi.ID = lo.ID 
    AND fi.session_id = lo.session_id
ORDER BY fi.in_punchdatetime;

关键说明 #

1. 会话分组逻辑：通过SUM(CASE...) OVER()窗口函数，对每个ID的打卡记录按时间排序，每遇到一条IN记录就累加1，生成唯一的session_id，确保同一个会话（从IN开始到下一个IN之前的所有记录）共享同一个ID。
2. 首次IN/末次OUT提取：使用ROW_NUMBER()窗口函数分别对每个会话的IN记录正序、OUT记录倒序排序，取第一条即为目标记录。
3. 无OUT的填充处理：用COALESCE()函数判断，若会话无对应OUT记录，则用current_timestamp()及其格式化值填充日期、时间字段；uuu和feed_date可根据业务需求选择复用IN的字段或其他默认值。

兼容低版本Hive（<2.3） #

若你的Hive版本不支持QUALIFY语法，可将first_in和last_out替换为子查询过滤的方式：

-- 替换first_in
first_in AS (
    SELECT *
    FROM (
        SELECT 
            session_id,
            ID,
            punchdate AS in_punchdate,
            punchtime AS in_punchtime,
            punchdatetime AS in_punchdatetime,
            uuu AS in_uuu,
            feed_date AS in_feed_date,
            ROW_NUMBER() OVER (
                PARTITION BY session_id, ID 
                ORDER BY punchdatetime ASC
            ) AS rn
        FROM punch_with_session
        WHERE punchtype = 'IN'
    ) t
    WHERE rn = 1
),
-- 替换last_out
last_out AS (
    SELECT *
    FROM (
        SELECT 
            session_id,
            ID,
            punchdate AS out_punchdate,
            punchtime AS out_punchtime,
            punchdatetime AS out_punchdatetime,
            uuu AS out_uuu,
            feed_date AS out_feed_date,
            ROW_NUMBER() OVER (
                PARTITION BY session_id, ID 
                ORDER BY punchdatetime DESC
            ) AS rn
        FROM punch_with_session
        WHERE punchtype = 'OUT'
    ) t
    WHERE rn = 1
)

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