行列轉換
https://help.aliyun.com/zh/maxcompute/use-cases/transpose-rows-to-columns-or-columns-to-rows?spm=a2c4g.11186623.0.i29
補充，使用laterval view：
@geea3_query := select query,table_read
,case when locate('WHLSPDCIRCUMLRELEQF',toupper(query))>0 then 1 else 0 end as WHLSPDCIRCUMLRELEQF
,case when locate('VEHSPDINDCDVEHSPDINDCD',toupper(query))>0 then 1 else 0 end as VEHSPDINDCDVEHSPDINDCD
,case when locate('WHLSPDCIRCUMLRERIQF',toupper(query))>0 then 1 else 0 end as WHLSPDCIRCUMLRERIQF
from ods_meta.ods_holo_query_log_di where dt='20240814' and table_read like '%geea3%';
@geea3_query_col2row := SELECT
query
,table_read
,KEY AS field_name
,IF(value IS NULL,0,value) AS check_result
FROM @geea3_query
LATERAL VIEW EXPLODE(MAP('WHLSPDCIRCUMLRELEQF',WHLSPDCIRCUMLRELEQF,'VEHSPDINDCDVEHSPDINDCD',VEHSPDINDCDVEHSPDINDCD,'WHLSPDCIRCUMLRERIQF',WHLSPDCIRCUMLRERIQF)) t2 AS KEY ,value
;
SELECT
field_name
,sum(check_result)
FROM @geea3_query_col2row
GROUP BY field_name
;
sql調優：
https://developer.aliyun.com/ebook/3764/read?spm=a2c6h.26392459.ebook-detail.4.35494291R1gyIV