如何在Netezza SQL中实现嵌套百分位数计算并修正查询

问题背景 #

已在R中掌握嵌套百分位数计算逻辑，需迁移至Netezza SQL。当前查询使用CTE和NTILE函数，但存在两个问题：

• 无法为Height、Weight、Hospital_Visits分箱变量添加对应的min/max范围列
• 不确定是否存在患者重复分组、分箱范围重叠情况

附原参考代码 #

R数据生成代码 #

set.seed(123)
library(dplyr)

Patient_ID = 1:5000
gender <- c("Male","Female")
gender <- sample(gender, 5000, replace=TRUE, prob=c(0.45, 0.55))
Gender <- as.factor(gender)

status <- c("Immigrant","Citizen")
status <- sample(status, 5000, replace=TRUE, prob=c(0.3, 0.7))
Status  <- as.factor(status )

Height = rnorm(5000, 150, 10)
Weight = rnorm(5000, 90, 10)
Hospital_Visits = sample.int(20,  5000, replace = TRUE)

disease <- c("Yes","No")
disease <- sample(disease, 5000, replace=TRUE, prob=c(0.4, 0.6))
Disease <- as.factor(disease)

my_data = data.frame(Patient_ID, Gender, Status, Height, Weight, Hospital_Visits, Disease)

R嵌套百分位数计算代码 #

final = my_data %>%
    group_by(Gender, Status) %>%
    mutate(
        Height_ntile = ntile(Height, 3),
        Height_range = paste(sprintf("%.2f", min(Height)), sprintf("%.2f", max(Height)), sep = "-")
    ) %>%
    group_by(Height_ntile, Height_range, .add = TRUE) %>%
    mutate(
        Weight_ntile = ntile(Weight, 3),
        Weight_range = paste(sprintf("%.2f", min(Weight)), sprintf("%.2f", max(Weight)), sep = "-")
    ) %>%
    group_by(Weight_ntile, Weight_range, .add = TRUE) %>%
    mutate(
        Hospital_Visits_ntile = ntile(Hospital_Visits, 3),
        Hospital_range = paste(min(Hospital_Visits), max(Hospital_Visits), sep = "-")
    ) %>%
    group_by(Hospital_Visits_ntile, Hospital_range, .add = TRUE) %>%
    summarize(
        percent_disease = mean(Disease == "Yes"),
        count = n(),
        .groups = "drop"
    )

原Netezza SQL查询 #

WITH height_groups AS (
  SELECT Patient_ID,
         Gender,
         Status,
         Height,
         Weight,
         Hospital_Visits,
         Disease,
         NTILE(3) OVER (PARTITION BY Gender, Status ORDER BY Height) AS Height_ntile
  FROM my_data
),
weight_groups AS (
  SELECT Patient_ID,
         Gender,
         Status,
         Height,
         Weight,
         Hospital_Visits,
         Disease,
         Height_ntile,
         NTILE(3) OVER (PARTITION BY Gender, Status, Height_ntile ORDER BY Weight) AS Weight_ntile
  FROM height_groups
),
hospital_visits_groups AS (
  SELECT Patient_ID,
         Gender,
         Status,
         Height,
         Weight,
         Hospital_Visits,
         Disease,
         Height_ntile,
         Weight_ntile,
         NTILE(3) OVER (PARTITION BY Gender, Status, Height_ntile, Weight_ntile ORDER BY Hospital_Visits) AS Hospital_Visits_ntile
  FROM weight_groups
)
SELECT Gender, 
       Status, 
       Height_ntile, 
       Weight_ntile, 
       Hospital_Visits_ntile, 
       AVG(CASE WHEN Disease = 'Yes' THEN 1.0 ELSE 0.0 END) AS percent_disease, 
       COUNT(*) AS count
FROM hospital_visits_groups
GROUP BY Gender, Status, Height_ntile, Weight_ntile, Hospital_Visits_ntile;

修正后的Netezza SQL查询 #

WITH height_groups AS (
  SELECT 
    Patient_ID,
    Gender,
    Status,
    Height,
    Weight,
    Hospital_Visits,
    Disease,
    NTILE(3) OVER (PARTITION BY Gender, Status ORDER BY Height) AS Height_ntile,
    -- 计算当前分箱的Height范围
    MIN(Height) OVER (PARTITION BY Gender, Status, NTILE(3) OVER (PARTITION BY Gender, Status ORDER BY Height)) AS Height_min,
    MAX(Height) OVER (PARTITION BY Gender, Status, NTILE(3) OVER (PARTITION BY Gender, Status ORDER BY Height)) AS Height_max
  FROM my_data
),
weight_groups AS (
  SELECT 
    Patient_ID,
    Gender,
    Status,
    Height,
    Weight,
    Hospital_Visits,
    Disease,
    Height_ntile,
    Height_min,
    Height_max,
    NTILE(3) OVER (PARTITION BY Gender, Status, Height_ntile ORDER BY Weight) AS Weight_ntile,
    -- 计算当前分箱的Weight范围
    MIN(Weight) OVER (PARTITION BY Gender, Status, Height_ntile, NTILE(3) OVER (PARTITION BY Gender, Status, Height_ntile ORDER BY Weight)) AS Weight_min,
    MAX(Weight) OVER (PARTITION BY Gender, Status, Height_ntile, NTILE(3) OVER (PARTITION BY Gender, Status, Height_ntile ORDER BY Weight)) AS Weight_max
  FROM height_groups
),
hospital_visits_groups AS (
  SELECT 
    Patient_ID,
    Gender,
    Status,
    Height,
    Weight,
    Hospital_Visits,
    Disease,
    Height_ntile,
    Height_min,
    Height_max,
    Weight_ntile,
    Weight_min,
    Weight_max,
    NTILE(3) OVER (PARTITION BY Gender, Status, Height_ntile, Weight_ntile ORDER BY Hospital_Visits) AS Hospital_Visits_ntile,
    -- 计算当前分箱的Hospital_Visits范围
    MIN(Hospital_Visits) OVER (PARTITION BY Gender, Status, Height_ntile, Weight_ntile, NTILE(3) OVER (PARTITION BY Gender, Status, Height_ntile, Weight_ntile ORDER BY Hospital_Visits)) AS Hospital_min,
    MAX(Hospital_Visits) OVER (PARTITION BY Gender, Status, Height_ntile, Weight_ntile, NTILE(3) OVER (PARTITION BY Gender, Status, Height_ntile, Weight_ntile ORDER BY Hospital_Visits)) AS Hospital_max
  FROM weight_groups
)
SELECT 
  Gender, 
  Status, 
  Height_ntile,
  -- 拼接Height范围字符串
  TO_CHAR(Height_min, 'FM999.99') || '-' || TO_CHAR(Height_max, 'FM999.99') AS Height_range,
  Weight_ntile,
  -- 拼接Weight范围字符串
  TO_CHAR(Weight_min, 'FM999.99') || '-' || TO_CHAR(Weight_max, 'FM999.99') AS Weight_range,
  Hospital_Visits_ntile,
  -- 拼接Hospital_Visits范围字符串
  Hospital_min || '-' || Hospital_max AS Hospital_range,
  AVG(CASE WHEN Disease = 'Yes' THEN 1.0 ELSE 0.0 END) AS percent_disease, 
  COUNT(*) AS count
FROM hospital_visits_groups
GROUP BY 
  Gender, 
  Status, 
  Height_ntile,
  Height_min,
  Height_max,
  Weight_ntile,
  Weight_min,
  Weight_max,
  Hospital_Visits_ntile,
  Hospital_min,
  Hospital_max;

问题说明与解决 #

1. 分箱范围列添加：
   在每个CTE中，通过嵌套窗口函数，基于当前分箱的分区（如Gender, Status, Height_ntile）计算对应变量的min和max值，最终在聚合时拼接成范围字符串。使用TO_CHAR格式化数值，保证和R代码中的sprintf("%.2f")输出格式一致。

2. 重复分组与范围重叠问题：
   Netezza的NTILE函数会将有序分区内的数据均匀分配到指定数量的桶中，只要PARTITION BY和ORDER BY的逻辑正确，每个患者只会被分配到一个分箱，且分箱范围是连续不重叠的。本查询中每个步骤的分区逻辑和R代码完全对齐，不会出现重复分组或范围重叠的情况。

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