Hive中按条件获取双周/月度最新记录的查询方法

我来帮你搞定这个需求，核心思路是先筛选出目标状态的记录，再通过窗口函数定位到每个id下最新的那条记录，同时计算对应的双周和月份维度值。

完整Hive SQL代码 #

WITH low_status_records AS (
    SELECT 
        id,
        updated_date,
        -- 计算年度双周编号：将周数除以2后向上取整
        CEIL(WEEKOFYEAR(updated_date) / 2) AS biweek,
        -- 提取月份
        MONTH(updated_date) AS month
    FROM your_table_name  -- 替换成你的实际表名
    WHERE status = 'LOW'
),
ranked_records AS (
    SELECT 
        id,
        biweek,
        month,
        -- 按id分组，按更新时间倒序排名，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_date DESC) AS rn
    FROM low_status_records
)
SELECT 
    id,
    biweek,
    month
FROM ranked_records
WHERE rn = 1;

代码解释 #

1. 筛选目标记录：第一个CTE low_status_records 先过滤出所有status='LOW'的行，同时用WEEKOFYEAR函数获取更新日期的年度周数，再通过CEIL向上取整得到双周编号（比如样本里2020-03-14是第11周，对应第6个双周，和预期结果匹配），用MONTH函数直接提取月份。
2. 定位最新记录：第二个CTE ranked_records 使用ROW_NUMBER()窗口函数，按id分组后，将同id的记录按updated_date降序排列，最新的那条记录会被标记为rn=1。
3. 输出结果：最后筛选出rn=1的行，就是每个id下最新出现LOW状态的双周和月份信息，和你给出的预期输出完全一致。

补充说明 #

如果你的双周定义不是基于年度周数（比如每月1号开始算第一个双周），可以调整双周的计算逻辑，但从你的样本数据和预期结果来看，用年度周数计算的方式是完全匹配的。

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