Hive列值转换为列名处理方案

1.背景   

    最近有个朋友问了一个问题，如何把列值转换为列名进行展示，比如一个表有销售名称，销售日期，销售金额，他想统计这个销售每个月的销售额，然后每个月作为列字段。

 

2.实施

方法1使用case when:

select
t1.sale_name
,sum(case when t1.month_id='2023-01' then t1.money else 0 end) as `2023-01`
,sum(case when t1.month_id='2023-02' then t1.money else 0 end) as `2023-02`
,sum(case when t1.month_id='2023-03' then t1.money else 0 end) as `2023-03`
,sum(case when t1.month_id='2023-04' then t1.money else 0 end) as `2023-04`
,sum(case when t1.month_id='2023-05' then t1.money else 0 end) as `2023-05`
,sum(case when t1.month_id='2023-06' then t1.money else 0 end) as `2023-06`
,sum(case when t1.month_id='2023-07' then t1.money else 0 end) as `2023-07`
from 
(select sale_name,substring(data_date,0,7) as month_id,sum(money) as money from xc_tmp.tmp_11_22_row_colu group by sale_name,substring(data_date,0,7))t1
group by t1.sale_name

　　

 

SELECT 
t1.sale_name
,sum(date_money['2023-01']) as `2023-01`
,sum(date_money['2023-02']) as `2023-02`
,sum(date_money['2023-03']) as `2023-03`
,sum(date_money['2023-04']) as `2023-04`
,sum(date_money['2023-05']) as `2023-05`
,sum(date_money['2023-06']) as `2023-06`
,sum(date_money['2023-07']) as `2023-07`
FROM 
(
SELECT sale_name,map(substring(data_date,0,7), money) AS date_money
FROM xc_tmp.tmp_11_22_row_colu
)t1
GROUP BY t1.sale_name

　　

 

SELECT a.值1 as 别名1,a.值2 as 别名2,a.值3 as 别名3,a.值... as 别名...,
FROM
    table_name  ---就是数据源
---开始使用函数
PIVOT
(
---这里可用聚合函数来统计
sum(要统计的列名)
FOR
--列名1就是要行转列的列名
列名1
IN ([值1],[值2],[值3],[...])---值1等来自列名1的值
 
) AS a  ---起个名字
--这里可接分组或排序
group by 列名

　　

3.总结

    其实理想的状况估计是根据列值，自动扩展列名，大致思考了一下，这种应该是实现不了的，毕竟列名是属于表的元数据信息，肯定是需要事先声明的，除非写脚本根据数据拼凑SQL,但这也是指定了列名的，只是减少了人工拼凑的工作。