如何在无crosstab的Amazon RDS Aurora PostgreSQL中转置数据集生成直方图

嘿，我来帮你搞定这个转置生成直方图的需求！既然Amazon Aurora没法用crosstab()函数，咱们换个思路，用通用的SQL语法就能实现你要的效果，不用依赖特定的扩展函数。

核心思路 #

我们可以分成三步来实现：

1. 生成覆盖所有可能操作计数的数字序列（从0到数据中最大的操作数）
2. 分别统计每个操作（action1、action2）在各个计数上的用户数量
3. 将数字序列和统计结果关联，格式化输出空值为空白字符串

PostgreSQL兼容版Aurora的SQL代码 #

WITH action_counts AS (
    -- 生成从0到最大操作数的所有计数区间
    SELECT generate_series(0, max_val) AS count_num
    FROM (
        SELECT GREATEST(MAX(action1), MAX(action2)) AS max_val
        FROM your_table
    ) AS max_vals
),
action1_stats AS (
    -- 统计每个计数下action1的用户数
    SELECT action1 AS count_num, COUNT(*) AS cnt
    FROM your_table
    GROUP BY action1
),
action2_stats AS (
    -- 统计每个计数下action2的用户数
    SELECT action2 AS count_num, COUNT(*) AS cnt
    FROM your_table
    GROUP BY action2
)
-- 关联结果并格式化输出
SELECT 
    ac.count_num AS "#",
    COALESCE(CAST(a1.cnt AS TEXT), '') AS "Action1",
    COALESCE(CAST(a2.cnt AS TEXT), '') AS "Action2"
FROM action_counts ac
LEFT JOIN action1_stats a1 ON ac.count_num = a1.count_num
LEFT JOIN action2_stats a2 ON ac.count_num = a2.count_num
ORDER BY ac.count_num;

MySQL兼容版Aurora的SQL代码 #

如果你的Aurora是MySQL兼容引擎，需要用递归CTE来生成数字序列：

WITH RECURSIVE action_counts AS (
    SELECT 0 AS count_num
    UNION ALL
    SELECT count_num + 1
    FROM action_counts
    WHERE count_num < (
        SELECT GREATEST(MAX(action1), MAX(action2))
        FROM your_table
    )
),
action1_stats AS (
    SELECT action1 AS count_num, COUNT(*) AS cnt
    FROM your_table
    GROUP BY action1
),
action2_stats AS (
    SELECT action2 AS count_num, COUNT(*) AS cnt
    FROM your_table
    GROUP BY action2
)
SELECT 
    ac.count_num AS `#`,
    IFNULL(CAST(a1.cnt AS CHAR), '') AS `Action1`,
    IFNULL(CAST(a2.cnt AS CHAR), '') AS `Action2`
FROM action_counts ac
LEFT JOIN action1_stats a1 ON ac.count_num = a1.count_num
LEFT JOIN action2_stats a2 ON ac.count_num = a2.count_num
ORDER BY ac.count_num;

代码说明 #

• action_counts CTE负责生成完整的计数范围，确保不会漏掉任何可能的操作次数（比如你例子里的0到5，后续数据如果出现更大的数值也能自动适配）
• action1_stats和action2_stats分别统计每个操作在不同计数下的用户数量
• 最后通过左关联将计数序列和统计结果结合，用COALESCE（PostgreSQL）或IFNULL（MySQL）把空值转换成空白字符串，完全匹配你要的输出格式

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