阶段三模块三.sql

本文主要是介绍阶段三模块三.sql，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

会话概念：用户的一次会话含义是指用户进入系统开始到用户离开算作一次会话，离开或者重新开始一次会话的概念是指用户的两次行为事件差值大于30分钟，

select 
     user_id
    ,click_time
    ,last_time
    ,diff_time
    ,flag
    ,row_number() over(partition by user_id,rn order by click_time asc)
from 
(
    select 
            user_id
            ,click_time
            ,last_time
            ,diff_time
            ,flag
            ,case when num1 = 0 or num1 is null then 0 else 1 end as rn
    from 
    (
        select 
            user_id
            ,click_time
            ,last_time
            ,diff_time
            ,flag
            ,(rn-flag) as num1

        from
        (
            select 
                user_id
                ,click_time
                ,last_time
                ,diff_time
                ,flag
                ,lag(flag) over(partition by user_id order by click_time) as rn

            from 
            (
                select 
                    t1.user_id
                    ,t1.click_time
                    ,t1.last_time
                    ,t1.diff_time
                    ,case when t1.diff_time > 30*60 then 1 else 0 end as flag
                from
                (
                    select 
                        t.user_id
                        ,t.click_time
                        ,t.last_time
                        ,unix_timestamp(t.click_time) -unix_timestamp(t.last_time) as diff_time
                    from 
                    (
                        select 
                             user_id
                            ,click_time
                            ,lag(click_time) over(partition by user_id order by click_time) as last_time
                        from user_clicklog
                    ) t
                ) t1
            ) t2
        ) t3
    ) t4
) t5

这篇关于阶段三模块三.sql的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！