Impala技术问题：如何准确获取指定列的下一条有效记录

问题背景 #

现有Impala查询在小时连续时可正常输出，但当delivery_slot_from_hour列的小时数据不连续时，无法正确区分当前、下一个delivery_slot及剩余归为“Upcoming”的结果。

原查询 #

WITH x AS(
SELECT
    delivery_slot_from_hour,
    tour_id
FROM my_table
WHERE substr(delivery_slot_from,1,10) = from_timestamp(now(), 'yyyy-MM-dd')
)
SELECT
    'Current' AS tour_status,
    delivery_slot_from_hour AS delivery_slot,
    COUNT(DISTINCT tour_id) AS tour_count
FROM x
WHERE CAST(substr(delivery_slot_from_hour,1,2) AS INT) = hour(date_trunc('hour',from_utc_timestamp(now(), 'MYRegion')))
GROUP BY delivery_slot
UNION ALL
SELECT
    'Current' AS tour_status,
    delivery_slot_from_hour AS delivery_slot,
    COUNT(DISTINCT tour_id) AS tour_count
FROM x
WHERE CAST(substr(delivery_slot_from_hour,1,2) AS INT) = hour(date_trunc('hour',from_utc_timestamp(now(), 'MYRegion')))+1
GROUP BY delivery_slot
UNION ALL
SELECT
    'Upcoming' AS tour_status,
    '-' AS delivery_slot,
    COUNT(DISTINCT tour_id) AS tour_count
FROM x
WHERE CAST(substr(delivery_slot_from_hour,1,2) AS INT) > hour(date_trunc('hour',from_utc_timestamp(now(), 'MYRegion')))+1
GROUP BY tour_status;

数据说明 #

delivery_slot_from_hour为字符串类型，格式示例：

该列数据可能不连续，例如：

需求 #

输出当前delivery_slot、下一个delivery_slot（指当前时间之后的第一个存在的slot），其余所有slot归为“Upcoming”，期望输出示例：

改写后的查询 #

WITH x AS (
    SELECT
        delivery_slot_from_hour,
        tour_id,
        -- 提取小时数字并转换为整数
        CAST(substr(delivery_slot_from_hour, 1, 2) AS INT) AS slot_hour,
        -- 获取当前时区的小时
        hour(date_trunc('hour', from_utc_timestamp(now(), 'MYRegion'))) AS current_hour
    FROM my_table
    WHERE substr(delivery_slot_from, 1, 10) = from_timestamp(now(), 'yyyy-MM-dd')
),
-- 对所有slot按小时排序，标记当前及下一个有效slot
slot_ranks AS (
    SELECT
        delivery_slot_from_hour,
        slot_hour,
        current_hour,
        -- 按小时升序排序，生成排名
        ROW_NUMBER() OVER (ORDER BY slot_hour) AS rn
    FROM (
        SELECT DISTINCT delivery_slot_from_hour, slot_hour, current_hour
        FROM x
    ) t
),
-- 确定当前和下一个需要展示的slot
target_slots AS (
    SELECT delivery_slot_from_hour
    FROM slot_ranks
    WHERE rn IN (
        -- 当前时间之后的第一个slot
        SELECT rn FROM slot_ranks WHERE slot_hour >= current_hour ORDER BY rn LIMIT 1,
        -- 第一个slot之后的下一个slot
        SELECT rn + 1 FROM slot_ranks WHERE slot_hour >= current_hour ORDER BY rn LIMIT 1
    )
)
-- 输出当前和下一个slot的统计
SELECT
    'Current' AS tour_status,
    x.delivery_slot_from_hour AS delivery_slot,
    COUNT(DISTINCT x.tour_id) AS tour_count
FROM x
JOIN target_slots ts ON x.delivery_slot_from_hour = ts.delivery_slot_from_hour
GROUP BY x.delivery_slot_from_hour
UNION ALL
-- 输出剩余归为Upcoming的统计
SELECT
    'Upcoming' AS tour_status,
    '-' AS delivery_slot,
    COUNT(DISTINCT x.tour_id) AS tour_count
FROM x
WHERE x.delivery_slot_from_hour NOT IN (SELECT delivery_slot_from_hour FROM target_slots)
GROUP BY tour_status;

改写思路 #

1. 在CTE x中统一提取slot小时数和当前时区的小时，避免重复计算；
2. 通过slot_ranks对所有唯一的slot按小时排序，生成排名；
3. 利用排名找到当前时间之后的第一个slot及其下一个slot，作为需要标记为Current的目标slot；
4. 分别统计目标slot的tour数量，以及剩余slot的总数量归为Upcoming。

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