SELECT
t1.song_encode AS song_encode -- 歌曲编码
,t1.song_name_clean AS song_name_clean -- 清洗后的歌曲名称
,t1.song_subname AS song_subname -- 歌曲副名称
,t1.artist_codelist AS artist_codelist -- 艺术家编码列表
,t1.artist_name_list AS artist_name_list -- 艺术家名称列表
,t2.mv_list AS song_mv_list -- 严格同歌组相关MV
FROM
(
SELECT
song_encode --歌曲编码(mv 或 歌曲)
,TRIM(REGEXP_REPLACE(REGEXP_REPLACE(song_name,'(','('),')',')')) AS song_name -- mv或歌曲名称
,if(song_name REGEXP '《|》',
if(song_name REGEXP '《',
if(split(song_name,'《|》')[1] REGEXP '#',
split(trim(split(song_name,'《|》')[1]),'#')[1],
split(song_name,'《|》')[1]),
substr(split(song_name,'》')[1],2)),
if(song_name REGEXP '【|】',
if(split(song_name,'【|】')[1] REGEXP '#',
split(trim(split(song_name,'【|】')[1]),'#')[1],
split(song_name,'【|】')[1]),
song_name)) AS song_name_clean -- 清洗后的歌曲名称(mv 或 歌曲)
,if(song_type = '普通', 'song', 'video') AS song_type
,finger_print -- 指纹KEY
,song_mv_encode -- 歌曲关联的mv编码
,video_song_encode -- 短视频关联的歌曲编码(mv关联的歌曲编码)
,similar_song_encode_list -- 相似歌曲列表(或 相似mv列表)
FROM bicoredata.dim_music_song_ds
WHERE pt_d= '20251201'
AND song_type IN ('普通', 'Video')
AND song_substatus <> '可显示 可搜索 可播放 不可推荐'
AND song_substatus <> '只灰显 可搜索 不可播放 不可推荐'
AND song_status IN ('隐藏','商用')
AND video_review_quality_type!='劣质'
AND !bicoredata.isEmpty(song_encode)
) t1
LEFT JOIN
(
SELECT
t1.song_encode AS song_encode --歌曲 id
,t1.song_name AS song_name -- 歌曲名称
,t2.mv_list AS mv_list -- mv编码列表
,t2.mv_name_list AS mv_name_list -- mv名称列表
FROM
(
SELECT
song_encode --歌曲 id
,song_name -- 歌曲名称
,finger_print
FROM
(
SELECT
song_encode --歌曲编码(mv 或 歌曲)
,TRIM(REGEXP_REPLACE(REGEXP_REPLACE(song_name,'(','('),')',')')) AS song_name -- mv或歌曲名称
,if(song_name REGEXP '《|》',
if(song_name REGEXP '《',
if(split(song_name,'《|》')[1] REGEXP '#',
split(trim(split(song_name,'《|》')[1]),'#')[1],
split(song_name,'《|》')[1]),
substr(split(song_name,'》')[1],2)),
if(song_name REGEXP '【|】',
if(split(song_name,'【|】')[1] REGEXP '#',
split(trim(split(song_name,'【|】')[1]),'#')[1],
split(song_name,'【|】')[1]),
song_name)) AS song_name_clean -- 清洗后的歌曲名称(mv 或 歌曲)
,if(song_type = '普通', 'song', 'video') AS song_type
,finger_print -- 指纹KEY
,song_mv_encode -- 歌曲关联的mv编码
,video_song_encode -- 短视频关联的歌曲编码(mv关联的歌曲编码)
,similar_song_encode_list -- 相似歌曲列表(或 相似mv列表)
FROM bicoredata.dim_music_song_ds
WHERE pt_d= '20251201'
AND song_type IN ('普通', 'Video')
AND song_substatus <> '可显示 可搜索 可播放 不可推荐'
AND song_substatus <> '只灰显 可搜索 不可播放 不可推荐'
AND song_status IN ('隐藏','商用')
AND video_review_quality_type!='劣质'
AND !bicoredata.isEmpty(song_encode)
) tt1
WHERE song_type =='song'
) t1
INNER JOIN
(
SELECT
finger_print
-- fixme 此处存在一个问题:mv编码有数据,但是名称缺失。如 M_1BF5OFScruHgh6d/MCeR5r1F3upJtqtkc 长流不息
,CONCAT_WS('/',COLLECT_SET(song_mv_encode)) AS mv_list -- mv编码列表
,CONCAT_WS('/',COLLECT_SET(song_mv_name)) AS mv_name_list -- mv名称列表
FROM
(
-- 此处得到的是 mv指纹 mv编码 mv名称
SELECT
t1.finger_print AS finger_print
,t1.song_mv_encode AS song_mv_encode
,t2.song_name AS song_mv_name -- 歌曲名称(mv名称)
FROM
(
-- 歌曲记录
SELECT
finger_print
,song_mv_encode -- 歌曲关联的mv编码
FROM
(
SELECT
song_encode --歌曲编码(mv 或 歌曲)
,TRIM(REGEXP_REPLACE(REGEXP_REPLACE(song_name,'(','('),')',')')) AS song_name -- mv或歌曲名称
,if(song_name REGEXP '《|》',
if(song_name REGEXP '《',
if(split(song_name,'《|》')[1] REGEXP '#',
split(trim(split(song_name,'《|》')[1]),'#')[1],
split(song_name,'《|》')[1]),
substr(split(song_name,'》')[1],2)),
if(song_name REGEXP '【|】',
if(split(song_name,'【|】')[1] REGEXP '#',
split(trim(split(song_name,'【|】')[1]),'#')[1],
split(song_name,'【|】')[1]),
song_name)) AS song_name_clean -- 清洗后的歌曲名称(mv 或 歌曲)
,if(song_type = '普通', 'song', 'video') AS song_type
,finger_print -- 指纹KEY
,song_mv_encode -- 歌曲关联的mv编码
,video_song_encode -- 短视频关联的歌曲编码(mv关联的歌曲编码)
,similar_song_encode_list -- 相似歌曲列表(或 相似mv列表)
FROM bicoredata.dim_music_song_ds
WHERE pt_d= '20251201'
AND song_type IN ('普通', 'Video')
AND song_substatus <> '可显示 可搜索 可播放 不可推荐'
AND song_substatus <> '只灰显 可搜索 不可播放 不可推荐'
AND song_status IN ('隐藏','商用')
AND video_review_quality_type!='劣质'
AND !bicoredata.isEmpty(song_encode)
) tt2
WHERE song_type =='song' -- (song_type为歌曲)
-- 确保左外 驱动表字段非空
AND song_mv_encode != '未定义' AND song_mv_encode != '-1' AND !bicoredata.isEmpty(song_mv_encode)
AND finger_print != '未定义' AND finger_print != '-1' AND !bicoredata.isEmpty(finger_print)
)t1
LEFT JOIN
(
SELECT
song_encode, -- todo 此处可能是 mv编码 或 歌曲编码
song_name_clean AS song_name
FROM
(
SELECT
song_encode --歌曲编码(mv 或 歌曲)
,TRIM(REGEXP_REPLACE(REGEXP_REPLACE(song_name,'(','('),')',')')) AS song_name -- mv或歌曲名称
,if(song_name REGEXP '《|》',
if(song_name REGEXP '《',
if(split(song_name,'《|》')[1] REGEXP '#',
split(trim(split(song_name,'《|》')[1]),'#')[1],
split(song_name,'《|》')[1]),
substr(split(song_name,'》')[1],2)),
if(song_name REGEXP '【|】',
if(split(song_name,'【|】')[1] REGEXP '#',
split(trim(split(song_name,'【|】')[1]),'#')[1],
split(song_name,'【|】')[1]),
song_name)) AS song_name_clean -- 清洗后的歌曲名称(mv 或 歌曲)
,if(song_type = '普通', 'song', 'video') AS song_type
,finger_print -- 指纹KEY
,song_mv_encode -- 歌曲关联的mv编码
,video_song_encode -- 短视频关联的歌曲编码(mv关联的歌曲编码)
,similar_song_encode_list -- 相似歌曲列表(或 相似mv列表)
FROM bicoredata.dim_music_song_ds
WHERE pt_d= '20251201'
AND song_type IN ('普通', 'Video')
AND song_substatus <> '可显示 可搜索 可播放 不可推荐'
AND song_substatus <> '只灰显 可搜索 不可播放 不可推荐'
AND song_status IN ('隐藏','商用')
AND video_review_quality_type!='劣质'
AND !bicoredata.isEmpty(song_encode)
) tt3
) t2
ON t1.song_mv_encode=t2.song_encode -- t1的歌曲关联的mv编码 join t2的编码 (得到的一定都是mv的编码)
) k
GROUP BY finger_print
) t2
ON t1.finger_print = t2.finger_print
WHERE mv_list != '未定义' AND !bicoredata.isEmpty(mv_list) -- fixme 上面 mv_list应该补充缺失值处理
) t2
ON t1.song_encode = t2.song_encode
WHERE pt_d= '20251201'
;报错operate failure
AdHoc stage for parsing sql:line 9:0 cannot recognize input near '<EOF>' '<EOF>' '<EOF>',请帮忙解决