row_number()函数随机排序

目录

• 数据准备
• row_number()中分组字段不是最细粒度
• 结果解析
• 如何判断row_number()是随机排序
• 解决方案

数据准备

-- 建表
CREATE TABLE IF NOT EXISTS TEST01.A
(
     ID                   VARCHAR(50)           COMMENT '序号'           -- 01
    ,XID                  VARCHAR(50)           COMMENT '信贷编号'       -- 02
    ,AMT                  INT                   COMMENT '数字'           -- 03
    ,DT                   VARCHAR(50)           COMMENT '日期'           -- 04
)
COMMENT 'A表'
STORED AS PARQUET
;

-- 插数
INSERT INTO TEST01.A(ID,XID,AMT,DT) VALUES ('1','d0m2e6',462,'20220716');
INSERT INTO TEST01.A(ID,XID,AMT,DT) VALUES ('1','e8a5m0',519,'20220716');
INSERT INTO TEST01.A(ID,XID,AMT,DT) VALUES ('1','g0s7h9',323,'20220715');

-- 查数
SELECT T.ID, T.AMT FROM (
    SELECT
           ID,XID,AMT,DT
          ,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY DT DESC) AS RN
    FROM TEST01.A
)T
WHERE T.RN = 1
;

row_number()中分组字段不是最细粒度

SELECT
       ID,XID,AMT,DT
      ,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY DT DESC) AS RN
FROM TEST01.A

查询结果

执行查询语句第1次：

执行查询语句第2次：

结果解析

ID与XID是联合主键，row_number()里面只按ID分组，存在同一ID同一DT有多条数据，按DT降序排序会失效，此时函数会对这多条数据随机排出123名，每执行一次脚本，结果会有差异。

如果外层查询是通过限制WHERE RN=1来取值，那取值结果就会存在随机性。

如何判断row_number()是随机排序

可以用下列方式进行测试：

-- 测试代码
SELECT ID,DT,COUNT(1) FROM TEST01.A GROUP BY 1,2 ORDER BY COUNT(1) DESC;

将分组字段与排序字段，分组查询，检查是否有重复值，如果有重复COUNT(1)>1，那每次跑数就是随机取值的。

解决方案

三方面思考：

1. 替换或增加分组字段
2. 替换或增加排序字段
3. 修改加工逻辑，对于多个取值的情况，如果是数值型，可以采用取最大值或者汇总求和的办法