SQL按客户号列转行并展示近12个月消费数据的最优方案问询

嘿，这个需求我刚好处理过类似的，给你梳理下最优的实现思路：

核心思路是动态给每个客户的消费记录按时间倒序排名，再通过条件聚合把行转成列，这样新数据录入后，排名会自动更新，始终展示最近12个月的最新数据。

步骤1：处理月度消费数据（可选） #

如果你的表中同一个客户同一个月可能有多条消费记录（比如示例里的6月两条），首先需要按月度聚合消费金额，确保每个客户每个月只有一条数据：

WITH monthly_cons AS (
    SELECT 
        cust_no,
        -- 这里根据你的数据库调整日期截断函数：
        -- PostgreSQL/Redshift: DATE_TRUNC('month', read_dt)
        -- SQL Server: EOMONTH(read_dt) 或者 DATEFROMPARTS(YEAR(read_dt), MONTH(read_dt), 1)
        -- MySQL: DATE_FORMAT(read_dt, '%Y-%m-01')
        DATE_TRUNC('month', read_dt) AS month_period,
        SUM(cons) AS monthly_total -- 按业务需求用SUM/MAX/AVG，比如单月多条取总和
    FROM your_table_name
    GROUP BY cust_no, DATE_TRUNC('month', read_dt)
)

步骤2：给月度数据动态排名 #

用窗口函数ROW_NUMBER()给每个客户的月度数据按时间倒序排号，排名1就是最新的月份，排名12就是第12新的月份：

, ranked_cons AS (
    SELECT 
        cust_no,
        monthly_total,
        ROW_NUMBER() OVER (PARTITION BY cust_no ORDER BY month_period DESC) AS rank_num
    FROM monthly_cons
)

步骤3：行转列（条件聚合） #

通过条件聚合把排名对应的消费金额转成列，自动过滤掉超过12个月的数据：

SELECT 
    cust_no,
    MAX(CASE WHEN rank_num = 1 THEN monthly_total END) AS latest_month_cons,
    MAX(CASE WHEN rank_num = 2 THEN monthly_total END) AS second_latest_cons,
    MAX(CASE WHEN rank_num = 3 THEN monthly_total END) AS third_latest_cons,
    -- 依次写到rank_num=12
    MAX(CASE WHEN rank_num = 12 THEN monthly_total END) AS twelfth_latest_cons
FROM ranked_cons
WHERE rank_num <= 12 -- 只保留最近12个月
GROUP BY cust_no;

关键优势 #

• 自动刷新：每次查询都会基于最新的read_dt重新计算排名，新数据录入后，最新月份会自动顶到rank_num=1，超过12个月的旧数据会被自动过滤。
• 性能友好：如果给cust_no和read_dt建复合索引（CREATE INDEX idx_cust_readdt ON your_table_name(cust_no, read_dt DESC);），窗口函数的排序会直接利用索引，大幅提升查询速度。
• 通用性强：条件聚合的写法几乎兼容所有主流SQL数据库（MySQL、PostgreSQL、SQL Server、Oracle等），比数据库专属的PIVOT语法更灵活。

简化版（如果单月只有一条记录） #

如果你的表中每个客户每个月只有一条消费记录，可以跳过月度聚合步骤，直接用原表排名：

WITH ranked_cons AS (
    SELECT 
        cust_no,
        cons,
        ROW_NUMBER() OVER (PARTITION BY cust_no ORDER BY read_dt DESC) AS rank_num
    FROM your_table_name
)
SELECT 
    cust_no,
    MAX(CASE WHEN rank_num = 1 THEN cons END) AS latest_cons,
    MAX(CASE WHEN rank_num = 2 THEN cons END) AS second_latest_cons,
    -- ... 到rank_num=12
    MAX(CASE WHEN rank_num = 12 THEN cons END) AS twelfth_latest_cons
FROM ranked_cons
WHERE rank_num <=12
GROUP BY cust_no;

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