order by的执行时机

最新推荐文章于 2022-10-17 22:57:13 发布

yians

最新推荐文章于 2022-10-17 22:57:13 发布

阅读量522

点赞数

分类专栏：基础文章标签： sql

本文链接：https://blog.csdn.net/yians/article/details/120418090

版权

基础专栏收录该内容

8 篇文章 0 订阅

订阅专栏

假设有这么一个需求，要根据用户点击表，统计每个用户从多到少的点击链接、点击链接次数和依据次数的排序序号。

表数据如下：

id   user_id uri
1    1       /
2    1       /index
3    2       /
4    1       /
5    2       /
6    2       /index
7    1       /index
8    3       /index
9    2       /get
10   1       /get

结果应该如下：

rank user_id count uri
1    1       2     /
2    1       2     /index
3    1       1     /get
1    2       2     /
2    2       1     /get
3    2       1     /index
1    3       1     /index

使用mysql5.7来做统计，sql的思路肯定是按照user_id和uri分组，然后count(uri)，再依据user_id和count排序，不过这里，我们还要展示序号。mysql5.7没有窗口函数，可以通过变量的方式来计算，这样我们有下面的代码：

set @rank := 0, @userId := 0, @ids = '0';
select @rank := IF(@userId = user_id, @rank + 1, 1) as r, @userId := user_id as p, @ids := concat(@ids, '-', user_id) as ids, count(1) as cnt, uri 
from click
group by user_id, uri order by user_id, cnt desc

但是执行这段代码时，会发现错误，我们对于rank的计算需要是有序的，但是（重点来了）在包含order by的select语句里拿到的值并不是排序之后的，通过ids变量可以看出：

r    p    ids    cnt  uri
1    1    0-1    2    /
2    1    0-1-1    2    /index
1    1    0-1-1-2-2-3-2-1    1    /get
1    2    0-1-1-2    2    /
1    2    0-1-1-2-2-3-2    1    /get
2    2    0-1-1-2-2    1    /index
1    3    0-1-1-2-2-3    1    /index

ids路径是select时读取user_id的顺序，对比结果（cnt是有序的）可以看出在获取完之后重新进行了排序，这样对于ids而言成了乱序。

这说明如果对select的数据有保证有序的依赖，只有在order by的外层来进行。所以我们应该通过子查询保证有序后再进行rank的计算。

正确的sql如下：

set @rank := 0, @userId := 0, @ids = '0';
select @rank := IF(@userId = user_id, @rank + 1, 1) as r, @userId := user_id as p, @ids := concat(@ids, '-', user_id) as ids, cnt, uri from (
	select user_id, count(1) as cnt, uri 
	from click
	group by user_id, uri order by user_id, cnt desc
) t

此时我们的结果如下：

r   p   ids cnt uri
1	1	0-1	2	/
2	1	0-1-1	2	/index
3	1	0-1-1-1	1	/get
1	2	0-1-1-1-2	2	/
2	2	0-1-1-1-2-2	1	/get
3	2	0-1-1-1-2-2-2	1	/index
1	3	0-1-1-1-2-2-2-3	1	/index

我们看到ids是有序的，这样保证rank的逻辑是正确的！