终极行列转换party（hive、DB2、mysql）

两种形式：不涉及分组拆开的（union all + sum、case when）；涉及分组拆开的（collect_list、lateral view+explode）

1、不涉及分组拆开的（union all + sum、case when）

1）几个显示值的字段要变成单独的一列来枚举——行转列

思路：要把这几个维度制造成表中的一列内容：

而select '枚举值’可以让它成为一列，或者使用寄存器？（如DB2）

实践：DB2

使用values寄存器：

只需要结合table 和values

原：

SELECT INNER_ABBR,MEAS_TYPE,MEAS_VALUE  FROM TKZJ.V_BBKQ_DM_TKI_RESULT_OFFICE_INFO

,LATERAL(VALUES('牙医数',YAYI),('医生数量',YISHENG))AS Q(MEAS_TYPE,MEAS_VALUE)

注：想看看DB2这个LATERAL是个什么函数，在DB2的官方文档中也没查到，相关的Lateral View是hive的UDTF表生成函数，在CSDN上https://blog.csdn.net/qq_36297093/article/details/118668257这个文章提到了DB2的LATERAL，但替换成table更好，更容易理解，下面的以TABLE举例

第一，简单复习下DB2 VALUES，寄存器

第二，把下面日月年的值行转列

--原结果

SELECT * FROM TKZJ.A_TKHL_YTD_SNAP_ZZB

WHERE SNAP_DATE =  CURRENT_DATE - 1 DAY

AND GROUP_CAT_NAME = '仙林'

--行转列

SELECT GROUP_CAT_NAME,MEAS_NAME,MEAS_type,MEAS_VALUE FROM TKZJ.A_TKHL_YTD_SNAP_ZZB

,TABLE(VALUES('日',VALUE_D),('月',VALUE_M),('年',VALUE_YR)) AS q(MEAS_type,MEAS_VALUE)  --将要转换的东西，用VALUES寄存起来，装到一个表里，相当于两个表的连接

其中table可以看成是和hive中的lateral join横向连接一样，首先生成一张VALUES('日',VALUE_D),('月',VALUE_M),('年',VALUE_YR)的这张q的临时表，然后把这张表与原表做横向连接

实践2：传统union

--不适合多字段

SELECT

SNAP_DATE,

GROUP_CAT_NAME,

MEAS_NAME,

'日',

value_D

FROM

TKZJ.A_TKHL_YTD_SNAP_ZZB

WHERE SNAP_DATE =  CURRENT_DATE - 1 DAY

AND GROUP_CAT_NAME = '仙林'

UNION ALL

SELECT

SNAP_DATE,

GROUP_CAT_NAME,

MEAS_NAME,

'月',

VALUE_M

FROM

TKZJ.A_TKHL_YTD_SNAP_ZZB

WHERE SNAP_DATE =  CURRENT_DATE - 1 DAY

AND GROUP_CAT_NAME = '仙林'

2）将单独一列的枚举值变成几个值字段——列转行

思路：即让这一列消失，要看这几个枚举字段下的值，相当于按照这几个枚举的角度来分类，可以用group by

实践1：

表名：t1

表结构：

a -- 年份

b -- 部门

c -- 绩效得分

表内容：

a   b  c

2014  B  9

2015  A  8

2014  A  10

2015  B  7

"

"问题一：

将上述表内容转为如下输出结果所示：

a  col_A col_B

2014  10   9

2015  8    7"

select

a

,sum(case when b = A then c else null end ) as col_A

,sum(case when b = B then c else null end ) as col_B

from t1

group by a

2、涉及分组拆开的

1）将一个字段的几个值拆开

hive：lateral view+explode

mysql：SUBSTRING_INDEX + 第一种情况的行列转换

2）将多个字段分组组成一个字段

hive：Collect_list、collect_set

mysql：Group_concat

DB2：listagg