sql

窗口函数

有三种排序方式
rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

group by的常见搭配：常和以下聚合函数搭配

avg（）-- 求平均值
count（）-- 计数
sum（）-- 求和
max（） – 最大值
min（）-- 最小值

例题

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者

SELECT tag, uid, ranking
FROM (
    SELECT ei.tag, er.uid, 
    ROW_NUMBER()OVER(PARTITION BY tag ORDER BY max(er.score) DESC, min(er.score) DESC, er.uid DESC) ranking
    FROM exam_record er
    JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    GROUP BY ei.tag, er.uid) t
WHERE ranking <= 3;

或者

WITH t AS (
    SELECT ei.tag, er.uid, 
    ROW_NUMBER()OVER(PARTITION BY tag ORDER BY max(er.score) DESC, min(er.score) DESC, er.uid DESC) ranking
    FROM exam_record er
    JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    GROUP BY ei.tag, er.uid
)

SELECT tag, uid, ranking
FROM t
WHERE ranking <= 3;

快手服务端暑期 250318 手撕真题

手撕：SQL题，统计 2023 年 9 月每个内容类别（content_category）中被举报次数最多的前 5 个上传者（uploader_id）

SELECT category, id
FROM (
    SELECT 
        t2.content_category AS category, 
        t2.uploader_id AS id, 
        ROW_NUMBER() OVER (
            PARTITION BY t2.content_category 
            ORDER BY COUNT(*) DESC
        ) AS idx
    FROM report_log t1
    INNER JOIN content_info t2  -- 改用 INNER JOIN 过滤无效内容
        ON t1.content_id = t2.content_id
    WHERE 
        YEAR(t1.report_date) = 2023 
        AND MONTH(t1.report_date) = 9
    GROUP BY 
        t2.content_category, 
        t2.uploader_id
) AS ranked
WHERE idx <= 5;

1	ROW_NUMBER() OVER (PARTITION BY t2.content_category ORDER BY COUNT(*))

功能：按 content_category 分区，在每个分区内（例如所有“科技”类别的上传者），根据被举报次数（COUNT(*)）生成行号。

1	GROUP BY t2.content_category, t2.uploader_id

功能：将数据按 content_category（内容类别）和 uploader_id（上传者ID）分组，统计每个上传者在每个类别中的被举报次数（COUNT(*)）。
**GROUP BY*：用于聚合数据，生成分组统计结果（行数减少）
**PARTITION BY*：用于窗口函数，定义数据分区的计算范围（行数不变）

先group，后partition！

两者结合使用，相当于group分组后，用partition里的聚合函数来聚合

深度分页优化

手撕：sql 深度分页优化，update_time字段是bigint类型，给update_time 建立了索引

1
2
3

select * from table_name
where update_time > 123456
limit 100000, 10

说一下这个 SQL 的执行过程吗？执行器里边执行这里边先执行什么，后执行什么？然后他有没有用到索引？如果用了，他用了哪个索引？然后他返回的结果集是什么？然后第二步他又执行了什么？然后他返回什么？有没有索引？大概这样。

如果让你改造的话，你会怎么改？

select *
from table_name
where update_time > (
    select update_time
    from table_name
    where update_time > 123456
    limit 100000, 1
)
limit 10

优化点：避免深度分页扫描：子查询仅需扫描 100001 条索引记录，外层查询扫描 10 条。回表次数减少：外层查询仅需回表 10 次（原SQL需回表 100000+10 次）。

现在执行的过程肯定是先执行子查询，对吧？子查询相当于返回的是一个数字，那外层SQL的执行顺序是什么？它会用到索引吗？然后它返回的是什么？它的执行顺序是什么？外层SQL是先执行 limit 还是先执行select？

建议：回头你看一下这个的执行过程，然后也可以建一个表，然后试一试他们真正的执行过程是啥样的。

验证执行计划

实际执行时间分别是：0.8s和0.03s

1 2	EXPLAIN SELECT * FROM table_name WHERE update_time > 123456 LIMIT 100000, 10; EXPLAIN SELECT * FROM table_name WHERE update_time > (...) LIMIT 10;

1 2	id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE table_name range idx_update_time idx_update_time 8 498590 100.00 Using index condition

type: range表示使用索引范围扫描（idx_update_time），仅扫描 update_time > 123456 的数据。
rows: 498,590优化器预估需要扫描近50万行数据才能找到满足条件的记录。

Extra: Using index condition表示使用了“索引条件下推”（Index Condition Pushdown, ICP），直接在存储引擎层过滤数据，减少回表次数。

1
2
3

select_type table partitions type possible_keys key key_len ref rows filtered Extra
PRIMARY table_name  range idx_update_time idx_update_time 8  498590 100.00 Using where
SUBQUERY table_name  range idx_update_time idx_update_time 8  498590 100.00 Using where; Using index

子查询部分（SUBQUERY）
- type: range使用索引范围扫描，找到第100000行的 update_time 值。
- Extra: Using index表示子查询仅访问索引，无需回表（覆盖索引），显著减少IO开销。
主查询部分（PRIMARY）
- type: range基于子查询的结果（update_time > 子查询值），直接通过索引定位到目标数据。
- rows: 498,590优化器预估的行数与实际执行不符，此处为估算误差（实际只需扫描10行）。
ds给的sql，差不多

SELECT * FROM table_name 
WHERE update_time > 123456 
  AND update_time > ( -- 传入上一页的最后一个 update_time 值
    SELECT update_time 
    FROM table_name 
    WHERE update_time > 123456 
    ORDER BY update_time 
    LIMIT 100000, 1
  )
ORDER BY update_time 
LIMIT 10;

note-sql

sql

窗口函数

例题

深度分页优化