使用Netezza SQL按20个收入分位数计算患病率的逻辑验证

场景与测试数据 #

我正在使用Netezza SQL，通过R模拟本地Netezza连接上传了如下测试表：

set.seed(123)
num_rows <- 100
gender <- sample(c("m", "f"), num_rows, replace = TRUE)
disease <- sample(c("y", "n"), num_rows, replace = TRUE)
income <- runif(num_rows, min = 1000, max = 10000)
my_table <- data.frame(gender, disease, income)

library(DBI)
con <- DBI::dbConnect(duckdb::duckdb(), dbdir = ":memory:")
copy_to(con, my_table)

需求与尝试 #

我的需求是：使用Netezza SQL将收入划分为20个分位数区间（用NTILE实现），并计算每个区间内的患病率。

以下是我的尝试代码（可在R中运行）：

WITH ntiles AS (
    SELECT 
        income,
        disease, 
        NTILE(10) OVER (ORDER BY income) as income_ntile
    FROM my_table
)
SELECT 
    income_ntile, 
    MIN(income) as min_income, 
    MAX(income) as max_income,
    COUNT(*) as count, 
    COUNT(CASE WHEN disease = 'y' THEN 1 END) as disease_count,
    COUNT(CASE WHEN disease = 'y' THEN 1 END)*100.0/COUNT(*) as disease_rate
FROM ntiles
GROUP BY income_ntile;

运行后输出格式符合预期：

income_ntile min_income max_income count disease_count disease_rate
1             1   1056.707   1648.514    10             5           50
2             2   1669.461   2545.286    10             5           50
3             3   2549.546   3347.712    10             4           40
4             4   3407.693   4556.981    10             5           50
5             5   4563.034   5244.186    10             4           40
6             6   5300.608   6188.671    10             5           50
7             7   6337.411   7177.376    10             4           40
8             8   7284.354   8067.625    10             5           50
9             9   8189.485   8937.798    10             4           40
10           10   9241.152   9839.263    10             5           50

但我不确定编写的逻辑是否正确，希望专业人员帮忙验证。

验证结果 #

你的核心逻辑是正确的，具体说明如下：

1. 分位数分组逻辑：使用NTILE(N) OVER (ORDER BY income)将收入按升序划分为N个等数量的区间，这是Netezza中实现分位数分组的标准方式，逻辑没问题。
2. 聚合计算逻辑：
   • 通过MIN(income)和MAX(income)获取每个区间的收入范围，合理清晰；
   • COUNT(*)统计区间总人数，COUNT(CASE WHEN disease = 'y' THEN 1 END)统计患病人数，这种条件计数方式在SQL中是正确的（未满足条件的行会返回NULL，COUNT会忽略NULL）；
   • 患病率计算COUNT(...) * 100.0 / COUNT(*)通过乘以100.0确保结果为浮点型百分比，避免整数除法导致的精度丢失，逻辑正确。

注意事项 #

你当前代码中NTILE(10)是划分为10个区间，而需求是20个，只需将参数改为NTILE(20)即可。另外补充两点细节：

• 如果disease字段存在NULL值，当前逻辑会自动排除这些记录（因为CASE WHEN未匹配时返回NULL，COUNT不统计），若需要将NULL视为未患病，可调整为COUNT(CASE WHEN disease = 'y' THEN 1 ELSE 0 END)或者用SUM(CASE WHEN disease = 'y' THEN 1 ELSE 0 END)统计患病数，结果一致；
• Netezza中NTILE在处理总记录数无法被N整除时，会将多余的记录均匀分配到前面的区间，比如100条数据分20组，每组5条；如果是101条，前1组会有6条，其余19组5条，这是符合SQL标准的行为。

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