復雜查詢sql
1:場景(章節跳出率分日看板:需要指定范圍區間的閱讀每本書的每個章節,每個章節的閱讀uv,連續閱讀章節的uv,由于無法知道搜索的具體的哪個日期,因此只能實時查詢)
```
with op as
(SELECT DISTINCT user_id,book_id,sort_id
FROM `fq_chapter_rack_distincts`
WHERE `date` >= 20240607 AND `date` <= 20250415
and book_id = 14612)
,op1 as
(SELECT
user_id,
book_id,
sort_id,
lead(sort_id,1,null) over(partition by user_id,book_id order by sort_id) next_sort_id
from op)
select
book_id,
sort_id,
count(user_id) read_uv,
count(case when next_sort_id is not null and next_sort_id - sort_id = 1 then user_id else null end) read_next_sort_uv
from op1 group by book_id,sort_id
```