SQLite 3.3x如何正确计算样本数据集的累积分布占比

基础信息 #

测试表结构与数据 #

CREATE TABLE MY_TABLE(
  "Group" TEXT,
  Callees INTEGER,
  Callers INTEGER
);
  
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 1, 505);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 2, 172);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 3, 33);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 4, 20);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 5, 5);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 6, 5);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 7, 3);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 8, 4);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 9, 3);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 10, 2);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 11, 1);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 13, 1);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 14, 1);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 16, 1);
INSERT INTO MY_TABLE("Group", Callees, Callers) VALUES ('Group1', 22, 2);

需求说明 #

按Group字段分组，按照Callees从小到大排序，逐行计算当前行及之前所有行的Callers值之和，占本组总Callers值的累积百分比。
预期输出格式参考：

Group1  1 505 0.6662
Group1  2 172 0.8931
Group1  3  33 0.9366
...
Group1 22   2 1.0000

计算规则：第一行占比 = 505 / 本组Callers总和758 ≈ 0.6662，后续逐行累加Callers后计算占比，最后一行占比为1.0000。

原有语句问题 #

原SQL使用cume_dist()窗口函数无法实现需求：该函数的逻辑是统计当前行在排序结果中的行数位置占总行数的比例，是按行数计数的累积分布，不是按Callers字段值加权的累计和占比，计算逻辑和需求不匹配。

正确实现方案 #

SQLite 3.3x版本已支持标准窗口函数，使用SUM() OVER()分别计算分组累计和与分组总和，相除即可得到目标累积占比，SQL语句如下：

SELECT
  "Group",
  Callees,
  Callers,
  ROUND(
    SUM(Callers) OVER (PARTITION BY "Group" ORDER BY Callees ASC)
    * 1.0 / SUM(Callers) OVER (PARTITION BY "Group"),
    4
  ) AS CumulativeP
FROM MY_TABLE
ORDER BY "Group", Callees;

逻辑解释 #

• PARTITION BY "Group"：按分组独立计算，支持多组数据同时统计
• SUM(Callers) OVER (PARTITION BY "Group" ORDER BY Callees ASC)：按Callees升序排序，计算从分组第一行到当前行的Callers累计值
• SUM(Callers) OVER (PARTITION BY "Group")：计算每个分组下的Callers总数值
• *1.0：避免整数除法截断结果，保证计算精度
• ROUND(...,4)：将结果保留4位小数，匹配示例输出格式

执行结果 #

Group1|1|505|0.6662
Group1|2|172|0.8931
Group1|3|33|0.9367
Group1|4|20|0.9631
Group1|5|5|0.9697
Group1|6|5|0.9763
Group1|7|3|0.9802
Group1|8|4|0.9855
Group1|9|3|0.9894
Group1|10|2|0.9921
Group1|11|1|0.9934
Group1|13|1|0.9947
Group1|14|1|0.9960
Group1|16|1|0.9974
Group1|22|2|1.0

注：第三行结果0.9367与示例标注的0.9366为四舍五入精度差异，实际计算值710/758≈0.936675，保留四位小数四舍五入后为0.9367，示例值为直接截断四位小数的结果。如果需要截断而非四舍五入，可以将ROUND函数替换为CAST(xxx * 10000 AS INTEGER)/10000.0实现。

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