4秒内连续算一个会话

row value1 value2 res
1 - 3 -
2 3 4 ×
2 4 5 ×
2 5 10 ×
2 10 15 10
2 20 25 20
2 25 30 25
2 30 31 ×
2 31 - - 31
row value1 value2 res
1 - 3 -
2 3 4 ×
2 4 5 ×
2 5 10 ×
2 10 15 10
2 20 25 20
2 25 30 25
2 30 35 30
2 35 - - 35
  1. select a.room_id,a.audience_id,a.gift_id,
  2. a.gift_num a_gift_num,b.gift_num b_gift_num,
  3. a.data_generate_time a_data_generate_time,b.data_generate_time b_data_generate_time,
  4. case
  5. when b.data_generate_time-a.data_generate_time<${interval}
  6. and a.gift_num<b.gift_num
  7. or b.data_generate_time=a.data_generate_time
  8. then 0
  9. else 1
  10. end tag
  11. from
  12. (select
  13. room_id,audience_id,gift_id,gift_num,data_generate_time,
  14. row_number() over(partition by room_id,audience_id,gift_id order by data_generate_time asc) row_id
  15. from bigdata.bili_danmu_gift_info_daily_tmp1) a
  16. join
  17. (select
  18. room_id,audience_id,gift_id,gift_num,data_generate_time,
  19. row_number() over(partition by room_id,audience_id,gift_id order by data_generate_time asc) row_id
  20. from bigdata.bili_danmu_gift_info_daily_tmp1) b
  21. on a.row_id=b.row_id-1 and a.room_id=b.room_id and a.audience_id=b.audience_id and a.gift_id=b.gift_id;
文档更新时间: 2019-07-25 11:53   作者:admin