Hive SQL实现：获取每个用户首个文本值的前一列数值

这个需求我懂，咱们可以借助Hive的窗口函数和CTE（公共表表达式）来实现，下面一步步拆解思路：

先明确数据结构和需求 #

原表整理后的数据更清晰：

user | ts         | col   | isnumber
1    | 1473811200 | 5     | y
1    | 1473811205 | 10    | y
1    | 1473811207 | 15    | y
1    | 1473811212 | text1 | n
1    | 1473811215 | text2 | n
1    | 1473811225 | 30    | y
2    | 1473811201 | 10    | y
2    | 1473811205 | text3 | n
2    | 1473811207 | 20    | y
2    | 1473811210 | 30    | y

我们要拿到每个用户第一条isnumber='n'记录之前，最后一条isnumber='y'的col值，期望输出：

user | col
1    | 15
2    | 10

方法一：先定位第一条非数值记录的时间，再筛选关联 #

这个思路很直接：先找出每个用户第一条非数值记录的最早时间，再筛选该时间之前的所有数值记录，取其中最新那条的col值。

完整SQL #

WITH first_non_num AS (
    -- 第一步：获取每个用户第一条非数值记录的最小时间戳
    SELECT 
        user,
        MIN(ts) AS first_non_ts
    FROM your_table
    WHERE isnumber = 'n'
    GROUP BY user
)
-- 第二步：关联原表，筛选目标记录并取最新数值
SELECT 
    t.user,
    MAX(t.col) KEEP (DENSE_RANK LAST ORDER BY t.ts) AS col
FROM your_table t
JOIN first_non_num fnn ON t.user = fnn.user
WHERE t.isnumber = 'y' AND t.ts < fnn.first_non_ts
GROUP BY t.user;

代码解释 #

• first_non_num CTE：用MIN(ts)拿到每个用户第一条非数值记录的时间（因为记录按时间顺序，最小ts就是第一条出现的时间）。
• 主查询通过JOIN关联原表，筛选出isnumber='y'且时间早于第一条非数值记录的条目，再用MAX(col) KEEP (DENSE_RANK LAST ORDER BY ts)获取每个用户最新那条数值记录的col值——这是Hive里取排序后最后一条记录指定字段的常用写法。

方法二：用窗口函数标记记录位置 #

如果不想用JOIN，也可以用窗口函数标记每条记录是否在第一条非数值记录之前，再筛选取值。

完整SQL #

WITH ranked_records AS (
    SELECT 
        user,
        col,
        ts,
        isnumber,
        -- 累加非数值记录标记：遇到'n'就加1，之前的记录都是0
        SUM(CASE WHEN isnumber = 'n' THEN 1 ELSE 0 END) OVER (
            PARTITION BY user 
            ORDER BY ts 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS non_num_flag
    FROM your_table
)
SELECT 
    user,
    MAX(col) KEEP (DENSE_RANK LAST ORDER BY ts) AS col
FROM ranked_records
-- 只保留还没遇到非数值记录的数值型条目
WHERE non_num_flag = 0 AND isnumber = 'y'
GROUP BY user;

代码解释 #

• ranked_records CTE里的窗口函数SUM(...) OVER(...)会按用户分组、时间排序，从第一条记录到当前记录累加非数值的计数。第一条非数值记录的non_num_flag会变成1，之后的记录都是1，而之前的记录都是0。
• 主查询筛选non_num_flag=0的数值记录，再取每个用户最新那条的col值。

拓展：处理无n记录的用户 #

如果有些用户没有任何非数值记录，上面的SQL会过滤掉他们。如果需要保留这些用户并取他们最后一条数值记录的col，可以修改方法一为左连接：

WITH first_non_num AS (
    SELECT 
        user,
        MIN(ts) AS first_non_ts
    FROM your_table
    WHERE isnumber = 'n'
    GROUP BY user
)
SELECT 
    t.user,
    MAX(t.col) KEEP (DENSE_RANK LAST ORDER BY t.ts) AS col
FROM your_table t
LEFT JOIN first_non_num fnn ON t.user = fnn.user
WHERE t.isnumber = 'y' 
  AND (fnn.first_non_ts IS NULL OR t.ts < fnn.first_non_ts)
GROUP BY t.user;

这样没有非数值记录的用户，会取他们所有数值记录里最新的那条col值。

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