SQL生成计算行的优化方案及Redshift无Pivot函数替代方法咨询

嘿，针对你提出的SQL优化和Redshift Pivot替代方案的问题，我来给你梳理下实用的解决方案～

问题背景 #

原始数据表：

Metric Value
--------------
A      100
B      200
C      300

需要衍生出包含原数据及比率计算的新表：

Metric Value
--------------
A      100
B      200
C      300
C/A    3
B/A    2

当前思路是通过转置-临时存储-选择列-逆转置的流程实现，现在想知道：

1. 是否有更优的SQL实现方式？
2. Redshift不支持Pivot函数，除Case语句外是否有其他替代方案？

一、更优的SQL实现方式 #

完全不用走转置那套复杂流程，直接用UNION ALL结合子查询获取基准值（这里是A的值）来拼接原始行和计算行，逻辑更直接，性能也更优，避免了转置带来的额外开销。

方案1：直接子查询拼接

-- 先输出原始数据行
SELECT metric, value
FROM your_table
UNION ALL
-- 计算C/A的比率行
SELECT 'C/A' AS metric, (value / (SELECT value FROM your_table WHERE metric = 'A'))::FLOAT AS value
FROM your_table
WHERE metric = 'C'
UNION ALL
-- 计算B/A的比率行
SELECT 'B/A' AS metric, (value / (SELECT value FROM your_table WHERE metric = 'A'))::FLOAT AS value
FROM your_table
WHERE metric = 'B'
ORDER BY metric;

方案2：用CTE缓存基准值（减少重复查询）

如果基准值A需要多次使用，用CTE先把它存起来，避免重复扫描表：

WITH base_a AS (
    SELECT value AS a_value
    FROM your_table
    WHERE metric = 'A'
)
SELECT metric, value
FROM your_table
UNION ALL
SELECT 'C/A' AS metric, (t.value / ba.a_value)::FLOAT AS value
FROM your_table t
CROSS JOIN base_a ba
WHERE t.metric = 'C'
UNION ALL
SELECT 'B/A' AS metric, (t.value / ba.a_value)::FLOAT AS value
FROM your_table t
CROSS JOIN base_a ba
WHERE t.metric = 'B'
ORDER BY metric;

这种方式不需要转置，直接拼接原始行和计算行，逻辑清晰，执行效率也更高。

二、Redshift中Pivot的替代方案（除Case外） #

Redshift确实没有原生的PIVOT函数，除了传统的CASE语句，还有几个实用的替代方案：

1. 使用FILTER子句（条件聚合的简化写法）

Redshift支持PostgreSQL风格的FILTER子句，比CASE语句更简洁，可读性更好。比如要把行转成列（将A、B、C作为单独列）：

SELECT
    SUM(value) FILTER (WHERE metric = 'A') AS a_value,
    SUM(value) FILTER (WHERE metric = 'B') AS b_value,
    SUM(value) FILTER (WHERE metric = 'C') AS c_value
FROM your_table;

它的底层逻辑和CASE一致，但写法更清爽，减少了冗余代码。

2. 利用JSON函数进行行转列

如果指标数量较多，用JSON聚合的方式可以避免写大量条件判断：

SELECT
    JSON_EXTRACT_PATH_TEXT(metric_json, 'A')::INT AS a_value,
    JSON_EXTRACT_PATH_TEXT(metric_json, 'B')::INT AS b_value,
    JSON_EXTRACT_PATH_TEXT(metric_json, 'C')::INT AS c_value
FROM (
    SELECT JSON_OBJECT_AGG(metric, value) AS metric_json
    FROM your_table
) t;

注意需要把JSON提取出的字符串转成对应的数据类型，这种方式适合动态指标较多的场景。

3. 临时表+交叉连接（适合少量固定指标）

如果指标数量少且固定，可以先把每个指标的值存入临时表，再通过交叉连接合并成一行：

CREATE TEMP TABLE temp_metrics AS
SELECT metric, value FROM your_table;

SELECT
    a.value AS a_value,
    b.value AS b_value,
    c.value AS c_value
FROM temp_metrics a
CROSS JOIN temp_metrics b
CROSS JOIN temp_metrics c
WHERE a.metric = 'A' AND b.metric = 'B' AND c.metric = 'C';

不过这种方式只适合指标数量少的情况，否则交叉连接会产生大量中间数据，影响性能。

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