函数

collect_list

DROP TABLE IF EXISTS tmp_prgdb.tmp_dwa_user_attribute_day_007;
CREATE TABLE tmp_prgdb.tmp_dwa_user_attribute_day_007 STORED AS ORC AS
select
          user_id
         ,collect_list(visit_time) as visit_page_top5       --近30天浏览页面名称
    from (
            select
                     user_id
                    ,visit_time
                    ,row_number() over(PARTITION BY user_id ORDER BY visit_time desc) rn_desc
               from dwm_db.dwm_cp_ubh_visit_day
              where day_id >= DATE_SUB('${DAY_ID}',30)
                and day_id <= DATE_ADD('${DAY_ID}',1)
         ) B4
   where B4.rn_desc <= 5
group by user_id

SQL实例

炸裂函数（一行变多行）

效果上类似于笛卡尔积（join不加条件）

select user_id,kv 
from dws_md_event_info_day 
lateral view explode(split(`event_property`,',')) tmp as kv 
where etl_date='2020-11-04';

HSQL执行计划

博客 (opens new window)