Netezza SQL实现分组内rank对应var4的差值与百分比计算

数据表与需求 #

现有数据表my_table数据如下：

var1 var2 var3 rank var4
1    a    b    c    1   20
2    a    b    c    3   10
3    d    e    f    2   50
4    d    e    f    5   30

需求：针对var1、var2、var3的每组组合：

• 计算rank值较小的行的var4减去rank值较大的行的var4（对应示例中的20-10、50-30）
• 计算该差值的百分比（示例对应50%、60%）

期望结果：

var1 var2 var3 var4diff var4percent
---------------------------------------
1    a    b    c      10        50%
2    d    e    f      20        60%

已编写的部分代码 #

WITH ranked AS 
(
    SELECT 
        var1, var2, var3, rank, var4,
        ROW_NUMBER() OVER (PARTITION BY var1, var2, var3 ORDER BY rank) AS rn
    FROM 
        my_table
)
SELECT 
    a.var1, a.var2, a.var3,
    a.var4 - b.var4 AS var4diff,
    (a.var4 - b.var4) / b.var4 * 100 || '%' AS var4percent
FROM 
    ranked a

代码验证与修正 #

你尝试的代码思路是正确的，但存在两处需要调整的地方：

1. 关联逻辑偏差 #

你用ORDER BY rank生成的rn中，rn=1对应rank最小的行，rn=2对应rank最大的行。原代码的连接条件a.rn = b.rn +1会让a指向rank最大的行，b指向rank最小的行，此时a.var4 - b.var4得到的是负差值（如10-20=-10），和期望结果相反。

2. 整数除法精度问题 #

Netezza中整数除法会截断小数，直接计算会丢失精度，需要先转为浮点运算。

修正后的正确代码 #

WITH ranked AS (
  SELECT var1, var2, var3, rank, var4,
    ROW_NUMBER() OVER(PARTITION BY var1, var2, var3 ORDER BY rank) AS rn
  FROM my_table
)
SELECT a.var1, a.var2, a.var3,
  a.var4 - b.var4 AS var4diff,
  CASE 
    WHEN b.var4 = 0 THEN '0%'
    -- 乘以100.0转为浮点运算，避免整数截断
    ELSE CAST(((a.var4 - b.var4) * 100.0 / b.var4) AS INTEGER) || '%' 
  END AS var4percent
FROM ranked a
-- 调整关联条件，让a指向rank最小的行，b指向rank最大的行
JOIN ranked b ON a.var1 = b.var1 AND a.var2 = b.var2 AND a.var3 = b.var3 AND b.rn = a.rn + 1;

逻辑说明 #

• 用ROW_NUMBER()按组内rank升序生成行号，确保每组的两行被正确标记
• 通过自连接关联同组的两行，调整关联条件让a对应rank最小的行、b对应rank最大的行，保证差值为正
• 加入CASE处理除零异常，用100.0触发浮点运算，避免精度丢失

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