题解 | 未下单用户登陆渠道统计

/*where和having都不支持使用窗口函数语句或别名，因为SQL执行顺序是from/join--where--group by--having--select--order by,因此要使用窗口函数别名只能创建子查询或者同cte分布查询
*/
/*子查询
select tt.channel 
,tt.cnt
from (
    select t.channel
    ,t.cnt
    ,dense_rank() over(order by t.cnt desc ,channel) as rk
    from (
        select distinct uid
        ,channel
        ,count(uid) over(partition by channel) as cnt
        from user_info
            where uid not in (
            select uid
            from order_log
            )
        ) t
) tt
where tt.rk = 1
order by cnt desc,channel
*/

/*cte分布查询*/
with details1 as (
    select t.channel
    ,t.cnt
    ,dense_rank() over(order by t.cnt desc ,channel) as rk
    from (
        select distinct uid
        ,channel
        ,count(uid) over(partition by channel) as cnt
        from user_info
            where uid not in (
            select uid
            from order_log
            )
        ) t
    ) 

select details1.channel
,details1.cnt
from details1
where details1.rk = 1
order by cnt desc,channel