Spark SQL如何获取分组内首行与末行的row_number值

修改后的Spark SQL代码 #

SELECT 
  rn,
  max_rn AS rn1,
  doc_num
FROM (
  SELECT 
    *,
    row_number() OVER (PARTITION BY car_id ORDER BY SNAPSHOT_DATE, CAR_ID) rn,
    max(row_number() OVER (PARTITION BY car_id ORDER BY SNAPSHOT_DATE, CAR_ID)) OVER (PARTITION BY car_id) max_rn
  FROM MY_TABLE
  -- 原表存在重复数据时保留DISTINCT，否则可移除
  -- SELECT DISTINCT *, ...
) t
WHERE rn = 1
-- 按需添加单doc_num过滤：AND doc_num = '73927243'

代码说明 #

• 用同一个子查询同时生成car_id分组的行号rn，以及该分组的最大行号max_rn，避免原查询表自连导致的多行结果。
• 筛选rn=1的行，直接得到每个car_id分组首行的rn和分组最大行号rn1，自然每条doc_num对应唯一记录。
• 扩展到所有doc_num时，只需去掉WHERE子句中的doc_num过滤条件即可，无需依赖LIMIT 1。

原查询问题分析 #

原查询中snap1取rn=1的单条记录后，与包含同一car_id所有行的snap2关联，形成笛卡尔积导致返回多行。即使使用max(rn1)，若未对snap1字段做分组聚合，依然会保留所有关联行。

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