presto 不支持窗口函数 count(distinct ) 替换方案

思路:

1. array_agg(“role_uid”) over( ORDER BY sort asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) array_role ;通过

   sort 升序排列获取当前列之前的所有 role_uid ,通过 array_agg聚合，在通过 cardinality(array_distinct( array_role )) ,array_distinct 进行角色的去重，cardinality统计角色个数，在通过max() 获取分组数据

 
SELECT "role_uid","task_sid" ,"task_sid@sort" sort,cardinality(array_distinct(dk)) "累积" from  (
SELECT  "role_uid","task_sid","task_sid@task_name","task_sid@sort" 
,LEAD("task_sid",1,1 ) over(partition by  "role_uid" ORDER BY  "task_sid@sort" asc ) lead
,  array_agg("role_uid") over(  ORDER BY  cast("task_sid@sort" as bigint ) asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) dk
FROM v_event_119 a,ta_dim.dim_119_0_124593 b
WHERE "$part_event"='role_task'AND  b."task_sid@task_id"=a."task_sid" AND  ${PartDate:date1} 
)k