Teradata中如何按组对计算列升序后求累计和

我在Teradata中有如下表：

对应的表创建与数据插入语句：

CREATE VOLATILE TABLE student(group1 varchar(10),group2 varchar(10))
NO PRIMARY INDEX
ON COMMIT PRESERVE ROWS;
INSERT INTO student values ('A','A');
INSERT INTO student values ('A','B');
INSERT INTO student values ('A','B');
INSERT INTO student values ('A','C');
INSERT INTO student values ('A','C');
INSERT INTO student values ('A','C');
INSERT INTO student values ('A','C');
INSERT INTO student values ('B','A');
INSERT INTO student values ('B','A');
INSERT INTO student values ('B','A');
INSERT INTO student values ('B','A');
INSERT INTO student values ('B','D');

想要得到如下结果表（percentage列先升序排序再计算累计和cumsum）：

我用以下SQL能得到除cumsum外的所有列：

SELECT 
    group1,
    group2,
    COUNT (*) count_both_groups,
    SUM (count_both_groups) OVER (PARTITION BY group1) AS group1_count,
    (0.000+count_both_groups)/group1_count as percentage
FROM student
GROUP BY group1,group2
order BY group1,percentage;

但尝试添加累计和列时用下面的SQL报错：

SELECT 
    group1,
    group2,
    COUNT (*) count_both_groups,
    SUM (count_both_groups) OVER (PARTITION BY group1) AS group1_count,
    (0.000+count_both_groups)/group1_count as percentage,
    sum (percentage) over (partition by group1 order by count_both_groups asc ROWS UNBOUNDED PRECEDING) AS cumsum
FROM student
GROUP BY group1,group2;

报错信息：

Ordered Analytical Functions can not be nested.

需要解决这个问题，实现需求。

Teradata不允许在同一SELECT层级嵌套使用分析函数，所以需要先通过子查询或CTE封装前置计算结果，再在外部查询中计算累计和。

方法1：使用子查询 #

SELECT 
    group1,
    group2,
    count_both_groups,
    group1_count,
    ROUND(percentage, 3) AS percentage,
    ROUND(SUM(percentage) OVER (PARTITION BY group1 ORDER BY percentage ASC ROWS UNBOUNDED PRECEDING), 3) AS cumsum
FROM (
    SELECT 
        group1,
        group2,
        COUNT(*) AS count_both_groups,
        SUM(COUNT(*)) OVER (PARTITION BY group1) AS group1_count,
        CAST(COUNT(*) AS FLOAT) / SUM(COUNT(*)) OVER (PARTITION BY group1) AS percentage
    FROM student
    GROUP BY group1, group2
) AS sub
ORDER BY group1, percentage;

方法2：使用CTE（可读性更强） #

WITH group_summary AS (
    SELECT 
        group1,
        group2,
        COUNT(*) AS count_both_groups,
        SUM(COUNT(*)) OVER (PARTITION BY group1) AS group1_count,
        CAST(COUNT(*) AS FLOAT) / SUM(COUNT(*)) OVER (PARTITION BY group1) AS percentage
    FROM student
    GROUP BY group1, group2
)
SELECT 
    group1,
    group2,
    count_both_groups,
    group1_count,
    ROUND(percentage, 3) AS percentage,
    ROUND(SUM(percentage) OVER (PARTITION BY group1 ORDER BY percentage ASC ROWS UNBOUNDED PRECEDING), 3) AS cumsum
FROM group_summary
ORDER BY group1, percentage;

关键说明 #

1. 先在子查询/CTE中完成分组计数、组内总计数、百分比的计算，规避嵌套分析函数的限制。
2. 外部查询基于已计算好的percentage列，按group1分区、percentage升序排序后计算累计和。
3. 使用CAST(COUNT(*) AS FLOAT)确保除法得到浮点数结果，ROUND函数用于格式化结果为三位小数，匹配需求中的数值格式。

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