sql 一个字段里出现多个类别映射到多个字段

1、假如查询

SELECT userid AS userid ,

ordersource,

ROUND(AVG(ticket_num),0) AS ticket_num,

ROUND(AVG(totalamont),0) AS totalamont

FROM dwd_store_trade_ordersource_di

WHERE pt > 20200913 AND pt < 20201020

GROUP BY userid , ordersource

ORDER BY userid

 

得到

 

 

希望把每个不同类别的ticket 和totalamount当作字段，也就是希望得到2*类别数个字段的结果怎么做？

 

因为有两个不同字段ticket和totalamount，不好处理，我们可以使用concat，先合并成一个字段，也就是最终的结果是1*类别数个字段

 

然后使用case ... when ... then ...end语句

 

然后需要进行聚合。如果是只有一个字段，字段是数字，我们可以用sum， 而我们这里已经concat转化为字符串了，所以使用wm_concat

最终代码为

SELECT a.userid,

wm_concat(' ',case a.ordersource when 'ELEBE_WAIMAI' then CONCAT(a.ticket_num, ',',a.totalamont) when null then 0 end) as ELEBE_WAIMAI,

wm_concat(' ',case a.ordersource when 'ELEME_WAIMAI' then CONCAT(a.ticket_num, ',',a.totalamont) when null then 0 end) as ELEME_WAIMAI,

wm_concat(' ',case a.ordersource when 'KOUBEI_WAIMAI' then CONCAT(a.ticket_num, ',',a.totalamont) when null then 0 end) as KOUBEI_WAIMAI,

wm_concat(' ',case a.ordersource when 'MEITUAN_WAIMAI' then CONCAT(a.ticket_num, ',',a.totalamont) when null then 0 end) as MEITUAN_WAIMAI,

wm_concat(' ',case a.ordersource when 'WX_SHOP' then CONCAT(a.ticket_num, ',',a.totalamont) when null then 0 end) as WX_SHOP,

wm_concat(' ',case a.ordersource when 'ZIYING_MINIAPP' then CONCAT(a.ticket_num, ',',a.totalamont) when null then 0 end) as ZIYING_MINIAPP,

wm_concat(' ',case a.ordersource when 'other' then CONCAT(a.ticket_num, ',',a.totalamont) when null then 0 end) as other,

wm_concat(' ',case a.ordersource when 'store' then CONCAT(a.ticket_num, ',',a.totalamont) when null then 0 end) as store

FROM

(

SELECT userid AS userid ,

ordersource,

ROUND(AVG(ticket_num),0) AS ticket_num,

ROUND(AVG(totalamont),0) AS totalamont

FROM dwd_store_trade_ordersource_di

WHERE pt > 20200913 AND pt < 20201020

GROUP BY userid , ordersource

ORDER BY userid

) a

group by a.userid

 

最终效果为