复杂查询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
```
2：场景（mysql CPU飚高问题排查：排查某些慢sql所在的接口和sql执行语句）
```
WITH per_minute_stats AS (
SELECT
request_uri,
DATE_FORMAT(log_date, '%Y-%m-%d %H:%i') AS MINUTE,
MAX(request_time) AS max_request_time,
AVG(request_time) AS avg_request_time
FROM
api_novelbar_nginx_log
WHERE
log_date >= '2025-07-24 00:00:00'
AND log_date < '2025-07-25 00:00:00'
AND request_uri IS NOT NULL
GROUP BY
request_uri,
DATE_FORMAT(log_date, '%Y-%m-%d %H:%i')
)
SELECT
p1.request_uri,
MAX(p1.avg_request_time) AS max_avg_request_time,
MAX(p1.max_request_time) AS max_request_time,
MAX(p1.max_request_time) / NULLIF(MAX(p1.avg_request_time), 0) AS max_to_avg_ratio,
-- 记录最大平均耗时发生时间
MAX_BY (p1.MINUTE, p1.avg_request_time) AS max_avg_time_minute,
-- 记录最大耗时发生时间
MAX_BY (p1.MINUTE, p1.max_request_time) AS max_time_minute
FROM
per_minute_stats p1
WHERE
1 = 1
AND request_uri IS NOT NULL
-- AND request_uri = '/get_module_recommend'
GROUP BY
p1.request_uri
HAVING
max_to_avg_ratio >= 5
ORDER BY
max_to_avg_ratio DESC;
```
