Oracle/SQL生成带分组数据的策略-所有者汇总表方法咨询

针对需求中需要将Owner作为行、Strategy作为列，单元格显示POSITION_FLAG总和/符合条件行数的需求，提供两种Oracle SQL实现方案：

1. 静态列实现（适合Strategy固定的场景） #

如果Strategy种类固定（比如已知30种），可以直接用PIVOT结合预聚合完成，同时处理空值为0/0：

WITH all_combos AS (
    -- 生成所有Owner和Strategy的全组合，确保无遗漏
    SELECT DISTINCT o.Owner, s.Strategy
    FROM (SELECT DISTINCT Owner FROM your_table) o
    CROSS JOIN (SELECT DISTINCT Strategy FROM your_table) s
),
aggregated AS (
    SELECT
        ac.Owner,
        ac.Strategy,
        -- 拼接成"总和/行数"的字符串，空数据用0填充
        CONCAT(NVL(SUM(t.POSITION_FLAG), 0), '/', NVL(COUNT(t.Account), 0)) AS ratio
    FROM all_combos ac
    LEFT JOIN your_table t
        ON ac.Owner = t.Owner AND ac.Strategy = t.Strategy
    GROUP BY ac.Owner, ac.Strategy
)
SELECT
    Owner AS X,
    Equity,
    "Fixed Income"
    -- 这里依次列出所有30种Strategy的列名
FROM aggregated
PIVOT (
    MAX(ratio)
    FOR Strategy IN (
        'Equity' AS Equity,
        'Fixed Income' AS "Fixed Income"
        -- 补充剩余28种Strategy的映射，格式为 '策略名' AS 列别名
    )
)
ORDER BY Owner;

2. 动态列实现（适合Strategy可变或数量较多的场景） #

当Strategy有30种且可能变化时，手动写静态列效率低，可通过动态SQL自动生成列：

DECLARE
    v_strategy_cols VARCHAR2(4000);
    v_pivot_in_clause VARCHAR2(4000);
    v_full_sql VARCHAR2(32767);
BEGIN
    -- 生成PIVOT需要的IN子句内容
    SELECT LISTAGG('''' || Strategy || ''' AS "' || Strategy || '"', ', ') WITHIN GROUP (ORDER BY Strategy)
    INTO v_pivot_in_clause
    FROM (SELECT DISTINCT Strategy FROM your_table);

    -- 生成最终查询的列列表（含空值处理）
    SELECT LISTAGG('NVL("' || Strategy || '", ''0/0'') AS "' || Strategy || '"', ', ') WITHIN GROUP (ORDER BY Strategy)
    INTO v_strategy_cols
    FROM (SELECT DISTINCT Strategy FROM your_table);

    -- 拼接完整动态SQL
    v_full_sql := '
        WITH all_combos AS (
            SELECT DISTINCT o.Owner, s.Strategy
            FROM (SELECT DISTINCT Owner FROM your_table) o
            CROSS JOIN (SELECT DISTINCT Strategy FROM your_table) s
        ),
        aggregated AS (
            SELECT
                ac.Owner,
                ac.Strategy,
                CONCAT(NVL(SUM(t.POSITION_FLAG), 0), ''/'', NVL(COUNT(t.Account), 0)) AS ratio
            FROM all_combos ac
            LEFT JOIN your_table t
                ON ac.Owner = t.Owner AND ac.Strategy = t.Strategy
            GROUP BY ac.Owner, ac.Strategy
        )
        SELECT
            Owner AS X,
            ' || v_strategy_cols || '
        FROM aggregated
        PIVOT (
            MAX(ratio)
            FOR Strategy IN (' || v_pivot_in_clause || ')
        )
        ORDER BY Owner';

    -- 输出SQL语句（可直接复制执行，或用EXECUTE IMMEDIATE直接执行）
    DBMS_OUTPUT.PUT_LINE(v_full_sql);
END;
/

关键说明 #

• all_combos CTE通过CROSS JOIN生成所有Owner和Strategy的组合，确保每个单元格都有对应数据，避免出现NULL；
• 聚合时用NVL处理空数据，保证没有匹配记录时显示0/0；
• 动态SQL方案会自动读取所有Strategy并生成对应的列，无需手动维护30种策略的列映射。

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