row_number()影响SQL查询输出？简化查询后结果差异解析

开发者阿泽

2026-7-22

问题描述

原SQL查询通过row_number() over(partition by ARN order by FDDATE desc)生成rnk，筛选rnk=1且FINAL_DECISION in ('APPROVE')的数据，原查询包含多余字段；移除不必要字段后，单独运行UNION ALL子查询结果与原查询一致，但整合为完整查询后输出数量差异极大（原查询结果394918条，修改后159050条）。

原SQL查询：

SELECT ARN,FDDATE as carded_date,PRODUCT_CODE as variant
from (
             Select *,row_number() over(partition by ARN order by FDDATE desc) as rnk 
      FROM (
              SELECT
                  *,
                  case when FINAL_DECISION_DATE != 'nan' then cast(to_date(FINAL_DECISION_DATE,"yyyy-MM-dd") as DATE) 
                  end as FDDATE
          FROM
              hdfc_dre
          WHERE FINAL_DECISION_DATE != 'nan' and cast(to_date(FINAL_DECISION_DATE,"yyyy-MM-dd") as DATE) <= "2022-10-31"
          UNION ALL
        SELECT
              Curable_or_Non_Curable,FINAL_DECISION,
              APPLICATION_REFERENCE_NUMBER AS ARN,CAST(WORKITEMID as int),PRODUCT_CODE,DROPOFF_REASON, cast(cast(to_date(FINAL_DECISION_DATE_TIME,"yyyy-MM-dd") as date) as String),
              CUSTOMER_TYPE,APPLICATION_TYPE,CAST(CREATION_DATE_TIME as STRING),SOURCING_CHANNEL,
              case
              when substr(FINAL_DECISION_DATE_TIME, 3, 1) = '-' then cast(to_date(substr(FINAL_DECISION_DATE_TIME, 0, 9), "dd-MMM-yy") as DATE) 
              when substr(FINAL_DECISION_DATE_TIME, 3, 1) = '/' then cast(to_date(substr(FINAL_DECISION_DATE_TIME, 0, 9), "MM/dd/yy") as Date) 

              end as FDDATE
          FROM
              HDFC_TABLE
)
)
WHERE rnk = 1
AND FINAL_DECISION in ('APPROVE')
ORDER BY 1 DESC

修改后的SQL查询：

SELECT ARN,FDDATE as carded_date,PRODUCT_CODE as variant
from (
             Select *,row_number() over(partition by ARN order by FDDATE desc) as rnk 
      FROM (
              SELECT
                 FINAL_DECISION,
                 ARN,
                 PRODUCT_CODE,
                  case when FINAL_DECISION_DATE != 'nan' then cast(to_date(FINAL_DECISION_DATE,"yyyy-MM-dd") as DATE)
                  end as FDDATE
          FROM
              hdfc_dre
          WHERE FINAL_DECISION_DATE != 'nan' and cast(to_date(FINAL_DECISION_DATE,"yyyy-MM-dd") as DATE) <= "2022-10-31"
          UNION ALL
        SELECT
              FINAL_DECISION,
              APPLICATION_REFERENCE_NUMBER AS ARN,PRODUCT_CODE,
              case
              when substr(FINAL_DECISION_DATE_TIME, 3, 1) = '-' then cast(to_date(substr(FINAL_DECISION_DATE_TIME, 0, 9), "dd-MMM-yy") as DATE) 
              when substr(FINAL_DECISION_DATE_TIME, 3, 1) = '/' then cast(to_date(substr(FINAL_DECISION_DATE_TIME, 0, 9), "MM/dd/yy") as Date) 

              end as FDDATE
          FROM
              HDFC_TABLE
                )
   )                 
WHERE rnk = 1
AND FINAL_DECISION in ('APPROVE')
ORDER BY 1 DESC

Spark统计结果：

bookings_op.where(col("FDDATE").isNotNull).select(count(col("ARN"))).show()
bookings_or.where(col("FDDATE").isNotNull).select(count(col("ARN"))).show()
+----------+
|count(ARN)|
+----------+
|    394918|
+----------+

+----------+
|count(ARN)|
+----------+
|    159050|
+----------+

差异原因分析

核心问题是Spark SQL的UNION ALL按字段位置而非字段名匹配列，原查询的字段顺序不匹配导致数据合并错误，进而影响过滤逻辑：

原查询字段顺序不匹配
原查询第一个子查询用SELECT *获取hdfc_dre的所有字段，再追加FDDATE列；第二个子查询手动指定了12个字段的顺序。由于hdfc_dre的原生字段顺序与第二个子查询的字段顺序不一致，UNION ALL时会按列的位置强行合并，导致列的含义完全混乱——比如原查询中UNION ALL后的FINAL_DECISION列，实际可能混合了hdfc_dre的其他字段值，而非真正的审批决策值。
过滤条件失效
原查询外层的AND FINAL_DECISION in ('APPROVE')过滤条件，实际作用在错误的列上，无法正确筛选出审批通过的记录，导致大量非APPROVE的行被保留，最终结果数量远高于预期。
修改后的查询修复了字段匹配问题
修改后的查询明确指定了两个子查询的字段顺序（FINAL_DECISION, ARN, PRODUCT_CODE, FDDATE），UNION ALL时列的含义完全对应，FINAL_DECISION列确实存储的是审批决策值，过滤条件能正确筛选出APPROVE的记录，因此结果数量符合实际情况。
row_number计算的间接影响
原查询中错误的列合并还会导致row_number()的分区排序逻辑基于错误的数据，但最直接的影响还是过滤条件失效，这是结果数量差异的主要原因。

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