2020-04-23 16:03 已编辑 北京邮电大学 大数据开发工程师
关注
全部评论
快意人生💪
南京邮电大学 数据分析师
select user_id,dt,row_number() over (partition by user_id ,rt order by active_dt ) as result from ( select user_id,dt,row_number() over (partition by user_id order by active_dt ) as rnt, cast(active_dt as int)-row_number() over (partition by user_id order by active_dt ) as rt from table1 ) a
dongchuan
电子科技大学 Java
select user_id, active_dt, if(count = 3,rank,1) active_days from ( select user_id,active_dt,rank, count(*)over(partition by user_id,sub_date) count from ( select user_id,active_dt,rank, (cast(active_dt as bigint) - rank) sub_date from (select user_id,active_dt, rank() over (partition by user_id order by active_dt) rank from table1) t1 ) t2 ) t3
红雪白枫
南昌航空大学科技学院 C工程师
SELECT user_id, active_dt, row_number() OVER(PARTITION BY active_days ORDER BY active_dt) active_days FROM ( SELECT user_id, active_dt,TO_DATE(active_dt,'YYYYMMDD') - row_number() OVER(order by active_dt) active_days FROM table1 )
几分神勇似从前
集美大学 数据分析师
select user_id, active_at, row_number()over(partition by t1 order by active_at) from (select user_id, active_at, date_sub(activer_at,row_number()over(partition by user_id order by active_at)) as t1, count(1) from table1 group by user_id, active_at, date_sub(activer_at,row_number()over(partition by user_id order by active_at)) )
李贵鑫_求职数据分析
东北大学 数据分析师
个人感觉这道题应该表达的是：连续三日活跃的，以1/2/3这样标出来。如果间断了活跃，就重新从1计数。
牛客694615854号
深圳信息职业技术学院 大数据开发工程师
窗口函数增加新的排序一列 之后case when select a.user_id,a.active_dt,a.active_days from (select *, (case when active_day<=3 then active_day else 1 end)active_days from( select user_id, active_dt, row_number() over (partition by user_id order by active_dt )as active_day from table1) t)a ;
京龙鱼
字节跳动_数据开发工程师
第一感觉窗口函数可以做 就是麻烦些，要好几步。简单的要想一想
相关推荐
查看11道真题和解析
