行转列的应用:
可以利用行转列把一个事件的多行属性放在一行,和事件关联,综合定位事件,不必像之前一样放在一个综合属性,|分隔然后用数组切分函数
原sql
可以利用行转列把一个事件的多行属性放在一行,和事件关联,综合定位事件
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 )
------------------------------有中文不用下面写法,不起作用-----------------------------
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