行转列的应用合并多行综合定位

Love The Way You Lie 2023-07-13 12:24 12阅读 0赞

行转列的应用：

可以利用行转列把一个事件的多行属性放在一行，和事件关联，综合定位事件，不必像之前一样放在一个综合属性，|分隔然后用数组切分函数

原sql

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70][]

可以利用行转列把一个事件的多行属性放在一行，和事件关联，综合定位事件

SELECT   
event\_id,  
"1",  
"2"  
FROM  
(  
SELECT event\_id,  
max( (case when (  event\_property\_name='指标名称')  
 then  
  event\_property\_value  
  else  
  null  
  end  
   )) as "1",  
   max((case when ( event\_property\_name='所属库')  
 then  
  event\_property\_value  
  else  
  null  
  end  
   )) AS "2"

FROM daily\_new\_clientrpt\_slave GROUP BY event\_id )

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 1][]

\------------------------------有中文不用下面写法，不起作用-----------------------------

SELECT event\_code,  
(case event\_property\_name when '指标名称'  
 then  
  event\_property\_value  
  else  
  null  
  end  
   ) as "1",  
   (case event\_property\_name when '所属库'  
 then  
  event\_property\_value  
  else  
  null  
  end  
   ) AS "2"

FROM daily\_new\_clientrpt\_slave

================  
最终合并sql  
SELECT   
m.uid,T."1",T."2"  
FROM  
daily\_new\_clientrpt\_master m  
left JOIN  
(  
SELECT   
event\_id,  
"1",  
"2"  
FROM  
(  
SELECT event\_id,  
max( (case when (  event\_property\_name='指标名称')  
 then  
  event\_property\_value  
  else  
  null  
  end  
   )) as "1",  
   max((case when ( event\_property\_name='所属库')  
 then  
  event\_property\_value  
  else  
  null  
  end  
   )) AS "2"

FROM daily\_new\_clientrpt\_slave GROUP BY event\_id ))T  
on m.event\_id=T.event\_id

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70]: /images/20230529/f94764383d3346ddbb2ff80d7c91b91c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 1]: /images/20230529/8ddcae03b83940308a100dcf0ffef494.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 2]: /images/20230529/fabf46c895074038bb66b22b18ee9909.png