动态行转列的巧妙实现

需求描述

行转列展示，比如原来3个颜色的3行，展示为3列，实际更复杂，是3行的复杂表头，第一行是小类，第二行是颜色，第三行是3个不同的数量。

给个更直观的例子：

需求分析

实际上这里3个颜色只是举例，实际要按真实的数据来，也就是需要动态的转，即使强大如oracle的pivot也搞不定。何况还有多列表头。

而且不同的区域同一个小类的颜色可能不一样，而表格只有1个表头，这需要合并所有的颜色。

一般这种情况都需要写动态sql，取出所有颜色，再case when对应的颜色汇总出数量，很复杂，性能也不会太好。

先以简单的入手行转列，如上图的3行，最终展示为左边2固定列，右边9动态列，这动态的9列是1个大组（类别）、3个小组（颜色）、每小组下3个数值，这不相当于json中的嵌套结构吗？

而pg对json的支持很好，问题迎刃而解！

实现

select org_name, in_store_city,json_agg(row_to_json(row(tiny_class,row(color_name,row(allot_in_org_suit_total_sales_qty,in_org_fill_rate,allot_in_org_all_last_7_14days_sales_qty)))))from tmp_rst_ra_skc_org_detailwhere org_name='上海区域' and in_store_city='上海市' and tiny_class='10K'group by org_name, in_store_city

1. 3个row函数对应3级嵌套

2.原本颜色有3行，变1行需要group by汇总

3.json的汇总需要先将row转json，再json_agg

结果：

org_namein_store_cityjson_agg

上海区域

上海市

[{“f1”:”10K”,”f2”:{“f1”:”灰”,”f2”:{“f1”:980,”f2”:null,”f3”:0}}}, {“f1”:”10K”,”f2”:{“f1”:”棕”,”f2”:{“f1”:980,”f2”:null,”f3”:0}}}, {“f1”:”10K”,”f2”:{“f1”:”黑”,”f2”:{“f1”:980,”f2”:null,”f3”:0}}}]

如果前端嫌左边的2列不是json，可以也加上：

select row_to_json(row(org_name, in_store_city,json_agg(row_to_json(row(tiny_class,row(color_name,row(allot_in_org_suit_total_sales_qty,in_org_fill_rate,allot_in_org_all_last_7_14days_sales_qty)))))))from tmp_rst_ra_skc_org_detailwhere org_name='上海区域' and in_store_city='上海市' and tiny_class='10K'group by org_name, in_store_city

增加需求

完成这个后，需求方又说，颜色的后面，小类的后面还要有汇总列：

看图分析，这里合计相当于也是1个颜色，假设有了这个颜色，那直接应用上面的sql即可。由于这里的合计也是按照动态列动态进行的，因此可以利用grouping sets先算出汇总值。

create temp table tmp_sum asselect org_name, in_store_city,tiny_class,color_name,sum(allot_in_org_suit_total_sales_qty) allot_in_org_suit_total_sales_qty,sum(in_org_fill_rate) in_org_fill_rate,sum(allot_in_org_all_last_7_14days_sales_qty) allot_in_org_all_last_7_14days_sales_qtyfrom tmp_rst_ra_skc_org_detail--where org_name='上海区域' and in_store_city='上海市' and tiny_class='10K'group by GROUPING SETS (        (org_name, in_store_city,tiny_class,color_name),        (org_name, in_store_city,tiny_class),        (org_name, in_store_city)    )

select * from tmp_sumwhere org_name='上海区域' and in_store_city='上海市' and tiny_class='10K'order by org_name,in_store_city,tiny_class,color_name

结果如下，color_name为null的为汇总。

select * from tmp_sum

where org_name=’上海区域’ and in_store_city=’上海市’ and tiny_class=’10K’

order by org_name,in_store_city,tiny_class,color_name

org_namein_store_citytiny_classcolor_nameallot_in_org_suit_total_sales_qtyin_org_fill_rateallot_in_org_all_last_7_14days_sales_qty

上海区域

上海市

10K

棕

980

上海区域

上海市

10K

灰

980

上海区域

上海市

10K

黑

980

上海区域

上海市

10K

2,940

汇总后再套用前面的json包装：

create temp table tmp_pivot aswith t as(--create temp table tmp_sum asselect org_name, in_store_city,tiny_class,color_name,sum(allot_in_org_suit_total_sales_qty) allot_in_org_suit_total_sales_qty,sum(in_org_fill_rate) in_org_fill_rate,sum(allot_in_org_all_last_7_14days_sales_qty) allot_in_org_all_last_7_14days_sales_qtyfrom tmp_rst_ra_skc_org_detail--where org_name='上海区域' and in_store_city='上海市' and tiny_class='10K'group by GROUPING SETS (        (org_name, in_store_city,tiny_class,color_name),        (org_name, in_store_city,tiny_class),        (org_name, in_store_city)    ))select org_name, in_store_city,json_agg(row_to_json(row(tiny_class,row(color_name,row(allot_in_org_suit_total_sales_qty,in_org_fill_rate,allot_in_org_all_last_7_14days_sales_qty)))))from t--mp_rst_ra_skc_org_detailgroup by org_name, in_store_city

查看结果：

select * from tmp_pivot order by org_name limit 3

从截图可见，类别和颜色都没有排序行转列，个数（也就是表头）是不是一致也未知，先排序也方便比较。

create temp table tmp_pivot aswith t as(--create temp table tmp_sum asselect org_name, in_store_city,tiny_class,color_name,sum(allot_in_org_suit_total_sales_qty) allot_in_org_suit_total_sales_qty,sum(in_org_fill_rate) in_org_fill_rate,sum(allot_in_org_all_last_7_14days_sales_qty) allot_in_org_all_last_7_14days_sales_qtyfrom tmp_rst_ra_skc_org_detail--where org_name='上海区域' and in_store_city='上海市' and tiny_class='10K'group by GROUPING SETS (        (org_name, in_store_city,tiny_class,color_name),        (org_name, in_store_city,tiny_class),        (org_name, in_store_city)    ))select org_name, in_store_city,json_agg(row_to_json(row(tiny_class,row(color_name,row(allot_in_org_suit_total_sales_qty,in_org_fill_rate,allot_in_org_all_last_7_14days_sales_qty))))order by tiny_class,color_name) --排序from t--mp_rst_ra_skc_org_detailgroup by org_name, in_store_city

初看，汇总行不一样，前2行的顺序看似一样，全部取出做文本对比，发现并不一样，有些类别或颜色1有2没有，或2有1没有。

实际应该需要类别和颜色取并集，因为表格只能有1个表头，缺失的类别或颜色无值不显示。太多数据不容易看问题，先取2个类别的：

select * from tmp_sumwhere org_name='上海区域'  and tiny_class in('10K','3/4裤')order by org_name, in_store_city,tiny_class,color_name

看汇总数据没有问题：

若要所有的json列一致，需要先求出列，其实就是表头。

create temp table tmp_header asselect distinct tiny_class,color_namefrom tmp_sum;

3/4紧身裤  黑色3/4紧身裤  null3/4裤  传奇墨水蓝3/4裤  学院藏青蓝3/4裤  橙黄3/4裤  灰3/4裤  蓝色3/4裤  黑3/4裤  黑色3/4裤  null

比如这个3/4裤有些7色，有些6色（没有这个3/4裤灰），最终都要展示成8列（含null的合计列）

但实际的数据有些颜色不够也要根据这个解析出一个没有数量的缺失列，从json中解析3/4裤灰，解析没有值。

但之前row_to_json的json格式是f1、f2形式，实际是要按value解析。

{“f1″:”短袖T恤”,”f2″:{“f1″:”白色”,”f2″:{“f1″:null,”f2″:null,”f3”:null}}}

这样row_to_json包装的json格式不对，应该要形式如下：

{“短袖T恤”:{“白色”,{“f1″:null,”f2″:null,”f3”:null}}}

select org_name, in_store_city,json_agg(json_build_object(tiny_class,json_build_object(coalesce(color_name,'总计'), row(allot_in_org_suit_total_sales_qty,in_org_fill_rate,allot_in_org_all_last_7_14days_sales_qty))))from tmp_sum2group by org_name, in_store_city

然后用表头动态解析刚生成的json结果，比如解析下面这个蓝红白3个颜色：

[{"3/4裤" : {"蓝色" : {"f1":6682,"f2":null,"f3":0}}}, {"3/4裤" : {"红" : {"f1":106912,"f2":null,"f3":0}}}]

进一步发现，即使这种结构，json数组要解开json数组，再json提取元素，再合并，AI给的sql调试有点问题，步骤这多一定快不了，就不调试了：

SELECT     outer_key AS product,     inner_key AS color,     inner_value->>'f1' AS f1,     inner_value->>'f2' AS f2,     inner_value->>'f3' AS f3 FROM (    SELECT         json_array_elements('[            {"3/4裤" : {"蓝色" : {"f1":6682,"f2":null,"f3":0}}},             {"3/4裤" : {"红" : {"f1":106912,"f2":null,"f3":0}}}        ]') AS data    ) t,    LATERAL (        SELECT             key AS outer_key,             json_each(value) AS inner_data         FROM             json_each(data)    ) t2,    LATERAL (        SELECT             key AS inner_key,             value AS inner_value         FROM             json_each(inner_data)    ) t3;

换个思路：

如果不考虑表头，之前已经得到了正确的结果，只是表头不一致，页面没法展示。

第一性原理，如果所有行的颜色都有，那表头自然一致了，那何不直接把缺的数据直接补上，这样连json都不需要了。

补充数据，以这个小类为例，原本有233行，补完有272行：

select count(*) from tmp_sum2;--233with a as(select distinct org_name, in_store_city from tmp_sum2--34)select * from a cross join tmp_header2 b--272 全部行

with a as(select distinct org_name, in_store_city from tmp_sum2--34)select org_name, in_store_city,tiny_class,color_name from a cross join tmp_header2 b--272 全部行except select org_name, in_store_city,tiny_class,color_name from tmp_sum2 --39

差了39行，原数据加上这39行，列就一致了，可以简单行转列了。

总结

这样，颠覆了原来的思路：

1. 求出小类、颜色的全部组合（distinct)

2. 求出区域、城市与小类、颜色的全部组合(cross join)

3. 追加缺失的组合数据，值默认为null

4. grouping sets求出各级的汇总值

5. 不用row_to_json,用json_build_object做出转列的嵌套json

6. 实际前端只要嵌套json的最低一级，直接平铺（看5、6如何合并？）

先以简单数据做个测试，已经补充了缺失数据，如下：

namesubjectscore

lisi

语文

lisi

英语

lisi

数学

zhang3

语文

zhang3

数学

zhang3

英语

不带汇总行：

select name,array_agg(score order by subject)

,array_agg(subject order by subject)–不用给前端

from gradesgroup by name

namearray_aggarray_agg

lisi

{NULL,90,80}

{数学,英语,语文}

zhang3

{70,NULL,60}

{数学,英语,语文}

若带汇总行：

select name,array_agg(score order by subject) ,array_agg(subject order by subject)from (select name,subject,sum(score) score from gradesgroup by grouping sets((name),(name,subject)))t group by name