select
t.userid as userId,
count(t.trackId) as trackCount,
COLLECT_SET(s.norm_language) as lang_tag,
COLLECT_SET(s.opt_scene_tag_name_list) as scene_tag,
-- COLLECT_SET((REPLACE(s.opt_emotion_tag_name_list), '(口径)', '')) as emotion_tag,
COLLECT_SET(TRIM(tag.tag_name)) AS style_tag
from
music_db_dump.music_trackofplaylist t
join music_db_dump.music_playlist p on t.playlistid = p.id and p.userId in (
13750232744,
1826878048,
13922228932,
8623580192,
6400536510,
8120417123,
8622975358,
2113620273,
3255479948,
4887572756,
1358085986,
2126738800,
557827678,
1505278066,
8266152964,
12219975654,
410813953,
1572808515,
7852330987,
1334988468,
440041498,
1972153994,
392855198,
10055929286,
6344704267,
1293523280,
13878096588,
12499309644,
505630409,
3808993284,
9238424571,
1586057570,
367029321,
1880629723,
129052863,
3235701481,
7898779603,
7877476570,
8162269455,
3385073482,
1679835444,
1366243753,
1312155864,
1605381480,
3297162180,
3828388122,
8299071089,
558501731,
1573414791,
8106734108,
614342343,
1682701527,
310906198,
8232772313,
3880126486,
1880777017,
8665620122,
8715085756,
309195744,
555067877,
3327466883,
9968135662,
3865955170,
1977799679,
4901164319,
5190018469,
1996717385,
1976833509,
488434192,
1790812077,
8759561515,
5090830004,
1673777794,
1518711239,
4040994054,
2004483656,
6309266029,
8686925306,
3581415917,
1629890791,
3871164889,
1957442608,
9236313912,
618951421,
536999674,
1527129527,
1414483898,
1677937891,
283529639,
78648004,
4014279908,
5051096421,
301147718,
131680681,
2126491488,
258588819,
489086747,
308393587,
44272900,
390765820
)
JOIN music_new_dm.ads_itm_pgc_song_tag_dd s on CAST(t.trackId AS STRING) = s.song_id
left join music_db_backend.music_artist a on s.album_artist_id = a.id
join (
SELECT * FROM music_dimension.relation_itm_pgc_tag_resource_mapping_dd tag
WHERE tag.resource_type = '1'
and tag.lvl1_category_name='STYLE_TAG'
and tag.grand_parent_tag_id in ('1017','1015','1000','1007','1008','1012','1006','1011','1014','1022','1071','1028','1003','1010','1077','1026','1041','1043','1037','152124','1252','79088','1001','10119','1029','1263')
and tag.confidence >=5
and tag.dt = '${job.etl_date}'
) tag on tag.resource_id = s.song_id
where
p.specialtype = 5
and s.dt = '${job.etl_date}'
and t.dt = '${job.etl_date}'
and p.dt = '${job.etl_date}'
and a.day = '${job.etl_date}'
and from_unixtime(t.addTime/1000, 'yyyy-MM-dd') = '${job.etl_date}'
GROUP by
t.userId是这个sql报的错
最新发布