Greenplum 动态行转列实现汇总报表

• 万家灯火，每一盏灯都代表着一个温暖的家，每一个家都有属于自己的故事。在这喧嚣的城市里，愿我们都能找到属于自己的那盏明灯

背景

  自从上了Greenplum 数据仓库项目真是一个头两个大，各需求应接不暇，日报、周报、月报都可以理解。难点都在各种报表格式显示，下面的示例是我啃了1周的成果。

客户需求

  本来可以按行显示各商户数据，看着也方便，但客户非要求行转列。本着客户是上帝的份上，客户有需求咱就满足，需求如下：
  按日期，以商户为列展示报表，增加商户报表动态增加相对应的列，效果如下：

  交易日期  | 交易汇总 | 商户A  | 商户B | 商户C  | 商户D 
------------+----------+--------+-------+--------+-------
 2024-07-01 |   213.89 |  20.00 | 90.00 |  25.00 | 78.89
 2024-07-02 |    53.50 |  21.10 | 17.20 |  15.20 |     0
 2024-07-03 |   385.00 | 181.00 | 87.00 | 117.00 |     0

配置表及交易订单表

• 配置表：mer_conf

 mer_id | mer_name 
--------+----------
 1      | 商户A
 2      | 商户B
 3      | 商户C
 4      | 商户D

• 订单表：trans_order

 trans_date | mer_id | trans_amt 
------------+--------+-----------
 2024-07-01 | 2      |     90.00
 2024-07-01 | 3      |     25.00
 2024-07-01 | 4      |     78.89
 2024-07-01 | 1      |     20.00
 2024-07-02 | 3      |     15.20
 2024-07-02 | 2      |     17.20
 2024-07-02 | 1      |     21.10
 2024-07-03 | 1      |     91.00
 2024-07-03 | 2      |     87.00
 2024-07-03 | 3      |     67.00
 2024-07-03 | 3      |     50.00
 2024-07-03 | 1      |     90.00

实现逻辑

  还是基于case when … then …end 来实现行转列的，如果固定case when后再有商户数据还需要再修改存储过程，维护比较麻烦，于是想到了动态拼接SQL，将case when 语句通过商户配置表（mer_conf） 来动态控制列。每次调用存储过程时根据动态列重建新表，展示报表时根据需求写SQL就ok。

实现过程代码

CREATE OR REPLACE FUNCTION Sp_row_to_column(v_CurrentDate varchar(8) default to_char(current_date-1,'yyyymmdd'))
    RETURNS text
    LANGUAGE plpgsql
    VOLATILE
AS $$

DECLARE 
    -- 建表拼接SQL：
    v_tab_ddl text default '';
    -- 影响行数
    v_count int; 
BEGIN
  /************************************************************
      name:            Sp_row_to_column
      purpose:         行转列实现
      create user:     布衣
      create time:     2024/08/01
      input:           v_CurrentDate : '20230605'
      target table :   trans_order_rpt : 按商户月汇总表，仅存当月汇总数据
      source table :   trans_order：交易订单表；
                       mer_conf   ： 商户配置表；
      comments:        按商户月汇总表，仅存当月汇总数据
  ************************************************************/  
  
raise notice 'DROP  [trans_order_rpt]';
-- 删除表trans_order_rpt
drop table if exists trans_order_rpt;

-- 拼接 建表语句：trans_order_rpt
select string_agg(t.sql_text,' ' order by t.id) into v_tab_ddl   from (
select 1 id,'create table trans_order_rpt  as ( select ' sql_text
union all 
select 2,
    'sum(case when m.mer_id='''|| mer_id ||''' then m.trans_amt else 0 end) as "'||mer_name||'",'
from mer_conf f  
union all
select 3,
'
    m.trans_date trans_date,
    sum(m.trans_amt) as total_trans_amt
    from trans_order m
    where m.trans_date::date>=date_trunc('''||'month'||''','''||v_CurrentDate||'''::date) and m.trans_date <='''||v_CurrentDate || '''
    group by m.trans_date
    )distributed by(trans_date)
') t ;

-- raise notice '%', v_tab_ddl;
-- 执行建表SQL
 EXECUTE v_tab_ddl;
GET DIAGNOSTICS v_count = ROW_COUNT;
raise notice  'CREATE [trans_order_rpt] Success:%',v_count;
return 'Success';
 END;
$$
EXECUTE ON ANY;

代码下载：Sp_row_to_column.zip

操作测试

• 执行过程

postgres@[local]:5432=>select Sp_row_to_column();
NOTICE:  DROP  [trans_order_rpt]
NOTICE:  CREATE [trans_order_rpt] Success:3
 sp_row_to_column 
------------------
 Success
(1 row)

• 查看结果

postgres@[local]:5432=>select t.trans_date as "交易日期",
-> t.total_trans_amt as "交易汇总",
-> t."商户A",
-> t."商户B",
-> t."商户C",
-> t."商户D" 
from trans_order_rpt t order by t.trans_date;
  交易日期  | 交易汇总 | 商户A  | 商户B | 商户C  | 商户D 
------------+----------+--------+-------+--------+-------
 2024-07-01 |   213.89 |  20.00 | 90.00 |  25.00 | 78.89
 2024-07-02 |    53.50 |  21.10 | 17.20 |  15.20 |     0
 2024-07-03 |   385.00 | 181.00 | 87.00 | 117.00 |     0

• 增加"商户E"

postgres@[local]:5432=>insert into mer_conf values(5,'商户E');
INSERT 0 1

• 再次执行过程

postgres@[local]:5432=>select Sp_row_to_column();
NOTICE:  DROP  [trans_order_rpt]
NOTICE:  CREATE [trans_order_rpt] Success:3
 sp_row_to_column 
------------------
 Success
(1 row)

• 再次查看结果，因没有交易记录，因此"商户E"交易汇总为0

postgres@[local]:5432=>select t.trans_date as "交易日期",
-> t.total_trans_amt as "交易汇总",
-> t."商户A",
-> t."商户B",
-> t."商户C",
-> t."商户D",
-> t."商户E" 
-> from trans_order_rpt t order by t.trans_date;
  交易日期  | 交易汇总 | 商户A  | 商户B | 商户C  | 商户D | 商户E 
------------+----------+--------+-------+--------+-------+-------
 2024-07-01 |   213.89 |  20.00 | 90.00 |  25.00 | 78.89 |     0
 2024-07-02 |    53.50 |  21.10 | 17.20 |  15.20 |     0 |     0
 2024-07-03 |   385.00 | 181.00 | 87.00 | 117.00 |     0 |     0
(3 rows)

• 7月4号"商户E"开始产生交易数据

postgres@[local]:5432=>INSERT INTO trans_order(  trans_date , mer_id , trans_amt ) VALUES( '2024-07-04'::DATE , 2   , 87.00  );
postgres@[local]:5432=>INSERT INTO trans_order(  trans_date , mer_id , trans_amt ) VALUES( '2024-07-04'::DATE , 3   , 67.00  );
postgres@[local]:5432=>INSERT INTO trans_order(  trans_date , mer_id , trans_amt ) VALUES( '2024-07-04'::DATE , 1   , 90.00  );
postgres@[local]:5432=>INSERT INTO trans_order(  trans_date , mer_id , trans_amt ) VALUES( '2024-07-04'::DATE , 4   , 78.89  );
postgres@[local]:5432=>INSERT INTO trans_order(  trans_date , mer_id , trans_amt ) VALUES( '2024-07-04'::DATE , 5   , 78.89  );

• 指定‘2024-07-04’日期汇总数据

postgres@[local]:5432=>select Sp_row_to_column('2024-07-04');
NOTICE:  DROP  [trans_order_rpt]
NOTICE:  CREATE [trans_order_rpt] Success:4
 sp_row_to_column 
------------------
 Success
(1 row)

• 查看汇总数据

postgres@[local]:5432=>select trans_date,total_trans_amt,"商户A","商户B","商户C","商户D","商户E" from trans_order_rpt order by trans_date;

总结

• 遇事多思考，复杂的问题也可以简单化解决；
• 问题需求需要多沟通，尽快要有纠正不合理的需求；