如何在Netezza SQL中实现分位数分组及自行车拥有占比统计？

一、用NTILE创建等规模分组 #

Netezza的NTILE()函数可直接将数据集按指定数量划分为等规模分组，核心是基于目标字段排序生成分组。如果需要按country和gender内部独立分组（比如每个国家/性别下的身高单独分3组），可添加PARTITION BY子句。

基础分组（全局等规模） #

WITH grouped_data AS (
    SELECT
        country,
        gender,
        age,
        height,
        owns_bicycle,
        -- 身高分为3个等规模组
        NTILE(3) OVER (ORDER BY height) AS height_group,
        -- 年龄分为5个等规模组
        NTILE(5) OVER (ORDER BY age) AS age_group
    FROM MY_TABLE
)

组内独立分组（按country+gender） #

如果要求每个国家和性别内部各自进行等规模分组，调整OVER子句：

WITH grouped_data AS (
    SELECT
        country,
        gender,
        age,
        height,
        owns_bicycle,
        NTILE(3) OVER (PARTITION BY country, gender ORDER BY height) AS height_group,
        NTILE(5) OVER (PARTITION BY country, gender ORDER BY age) AS age_group
    FROM MY_TABLE
)

二、按维度聚合统计 #

基于分组后的数据集，按country、gender、height_group、age_group的唯一组合，统计分组总人数及自行车拥有率：

SELECT
    country,
    gender,
    height_group,
    age_group,
    -- 分组总人数
    COUNT(*) AS group_total_count,
    -- 自行车拥有率（保留两位小数，假设owns_bicycle为0/1字段）
    ROUND(
        SUM(CASE WHEN owns_bicycle = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
        2
    ) AS bicycle_ownership_rate
FROM grouped_data
GROUP BY country, gender, height_group, age_group
ORDER BY country, gender, height_group, age_group;

可选：给分组添加可读性标签 #

如果需要将数字分组转换为百分比区间标签，用CASE语句映射：

SELECT
    country,
    gender,
    CASE height_group
        WHEN 1 THEN '0%-33%'
        WHEN 2 THEN '33%-66%'
        WHEN 3 THEN '66%-100%'
    END AS height_group_label,
    CASE age_group
        WHEN 1 THEN '0%-20%'
        WHEN 2 THEN '20%-40%'
        WHEN 3 THEN '40%-60%'
        WHEN 4 THEN '60%-80%'
        WHEN 5 THEN '80%-100%'
    END AS age_group_label,
    COUNT(*) AS group_total_count,
    ROUND(
        SUM(CASE WHEN owns_bicycle = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
        2
    ) AS bicycle_ownership_rate
FROM grouped_data
GROUP BY country, gender, height_group, age_group
ORDER BY country, gender, height_group, age_group;

注意事项 #

• 若owns_bicycle是字符串类型（如'Y'/'N'），需调整CASE语句为CASE WHEN owns_bicycle = 'Y' THEN 1 ELSE 0 END
• NTILE处理无法完全均分的数据集时，会将多余行分配到前序组，确保组规模差异不超过1，符合等规模要求

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