如何通过SQL将账户资产分配百分比从行展示转为列展示？

问题说明 #

现有portfolio表结构及数据如下：

需要计算每个账户的股票、现金、债券资产占比，要求输出为列形式（每种资产对应一列），但原SQL返回的是行形式结果，不符合预期：

错误输出 #

预期输出 #

解决方案 #

可以通过条件聚合（CASE WHEN配合聚合函数）实现行转列，同时正确计算各资产类型的占比。以下是优化后的SQL：

WITH account_total AS (
    -- 计算每个账户的总权益金额：所有资产类型的实际金额之和
    SELECT 
        account_no,
        SUM((port_percent * position_amt) / 100) AS total_asset
    FROM portfolio
    GROUP BY account_no
)
SELECT 
    p.account_no,
    -- 计算股票占比，无数据则显示0
    ROUND(COALESCE(SUM(CASE WHEN p.portfolio_type = 'Stock' THEN (p.port_percent * p.position_amt)/100 END) / at.total_asset * 100, 0), 0) AS stock_percent,
    -- 计算现金占比
    ROUND(COALESCE(SUM(CASE WHEN p.portfolio_type = 'Cash' THEN (p.port_percent * p.position_amt)/100 END) / at.total_asset * 100, 0), 0) AS cash_percent,
    -- 计算债券占比
    ROUND(COALESCE(SUM(CASE WHEN p.portfolio_type = 'Bond' THEN (p.port_percent * p.position_amt)/100 END) / at.total_asset * 100, 0), 0) AS bond_percent
FROM portfolio p
JOIN account_total at ON p.account_no = at.account_no
GROUP BY p.account_no, at.total_asset
ORDER BY p.account_no;

关键说明 #

1. 总权益计算：通过CTE account_total 统计每个账户的总实际资产金额，公式为 (port_percent * position_amt)/100，即单条记录的实际资产值，再求和得到账户总权益。
2. 行转列逻辑：使用CASE WHEN对不同portfolio_type的资产进行分组聚合，将行数据转为列，同时计算该类型资产占总权益的百分比。
3. 空值处理：用COALESCE将无对应资产类型的账户占比设为0，ROUND保证百分比为整数格式。
4. 原SQL问题：原SQL的GROUP BY包含了position_amt，导致分组错误，且未做行转列处理，因此返回行形式结果。

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