如何在Postgres中生成Stem and Leaf Plot？求解Leaf列实现

需求说明 #

需要生成包含累计计数（Running Count）、Stem、Leaf三列的茎叶图：

• Stem为数字的十位部分（百位、千位场景可扩展逻辑）
• Leaf为数字的剩余部分（个位），需按升序拼接成字符串
• 累计计数为从第一个Stem到当前Stem的总记录数

样本数据 #

17、31、30、22、23、24、30、33、22、16、40、38、37、36、35、34、33、32、31、48、41、35、36、37、26、36、46、35、47、35、34、36、42、43、36、56、32、46、30

期望结果 #

解决方案 #

以下SQL可直接实现需求，核心是用分组聚合生成Leaf列，窗口函数计算累计计数：

-- 构造样本数据
WITH sample_data AS (
    SELECT unnest(ARRAY[17, 31, 30, 22, 23, 24, 30, 33, 22, 16, 40, 38, 37, 36, 35, 34, 33, 32, 31, 48, 41, 35, 36, 37, 26, 36, 46, 35, 47, 35, 34, 36, 42, 43, 36, 56, 32, 46, 30]) AS num
),
-- 按Stem分组，生成排序后的Leaf字符串并统计组内数量
stem_leaf_groups AS (
    SELECT
        FLOOR(num / 10)::TEXT AS stem,
        -- 按个位升序拼接，生成Leaf列
        STRING_AGG(MOD(num, 10)::TEXT, '' ORDER BY MOD(num, 10)) AS leaf,
        COUNT(*) AS group_count
    FROM sample_data
    GROUP BY stem
    ORDER BY stem
)
-- 计算累计计数并输出最终结果
SELECT
    SUM(group_count) OVER (ORDER BY stem) AS "Running Count",
    stem AS "Stem",
    leaf AS "Leaf"
FROM stem_leaf_groups;

关键逻辑解释 #

1. Stem计算：FLOOR(num / 10)::TEXT 比LEFT(num::TEXT,1)更健壮，能正确处理个位数（返回'0'），符合茎叶图规范。
2. Leaf计算：STRING_AGG(..., ORDER BY ...) 是核心，它将每个Stem组内的个位数字按升序排序后拼接成字符串，直接得到需求中的Leaf列。
3. 累计计数：SUM(group_count) OVER (ORDER BY stem) 用窗口函数实现累计求和，自动生成从第一个组到当前组的总记录数。

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