数据分析面试之mysql的分组排序和变量赋值顺序

2字数 676阅读 1066

学习过mysql的同学,大都体会过mysql的一个的痛点,那就是mysql没有支持分组排序的开窗函数。但是我们能够通过使用变量来达到这个目的。

我们先来创建一个人员年龄表(如下),有个这样的需求是,根据不同性别进行分组排序他们的年龄,并得到序列号

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

我们先来得到想要的结果,然后来和大家解释下

select 
    first_name,
    gender,
    age ,
    rank
from
    (select  
        first_name,
        gender,
        age,
        @rank:=if(@gen=gender,@rank+1,1) rank,
        @gen:=gender
    from person,(select @rank:=0,@gen:=null) temp
    order by gender, age asc) b

结果如下:


Rank
一:mysql 变量解释
  • 通过set 赋值变量


    set
  • 通过select 赋值变量


    select
二:解释上面的分组排序代码
  • 第一步先赋值变量
Variables
  • 第二步使用IF条件进行分组

下面,我们不要太关注from person,(select @rank:=0,@gen:=null) temp,就是当作进行变量的初始化就好

   select  
        first_name,
        gender,
        age,
        @rank:=if(@gen=gender,@rank+1,1) rank,
        @gen:=gender
    from person,(select @rank:=0,@gen:=null) temp
    order by gender, age asc

和大家说下这个代码的流程
a.第一步:变量赋值,是先运行from 后面的内容,以及排序,排序的目的是把 FM放到各自的组中(这一点和我们原来的先select 后order 是不一样的,等下会有说明)此时@rank等于0,@gen等于null
b.第二步: 开始进行select中的内容,会先进行
第一行,运行
@rank:=if(@gen=gender,@rank+1,1) rank,此时@gen是等于null的,而gender 是第一行的值,所以IF函数将会返回1,第一行的rank就会返回1,接着运行@gen:=gender ,此时的@gen会被赋值第一行的值
第二行,
还是先运行@rank:=if(@gen=gender,@rank+1,1) rank,此时的@gen是等于gender,根据IF会返回@rank+1 然后赋值到@rank,直到遇到下一个不一样的gender,@rank 才会重新变成1

三:变量赋值顺序

我们通过看个例子来感受下

set @rownum:=0;
select 
        first_name,
        gender,
        age, @rownum as rownum
from person
where @rownum<1
order by first_name,least(0,@rownum:=@rownum+1);

在sql 语句中的执行顺序是 from 、where 、select 、order by
在这我们的疑问是先进行的order by 后进行的 select

如果是先进行的select 的话,rownum会输出0、1,而真实的结果是
rownnum是输出的1、2


所以我们可以暂认为是先进行的order by 后进行的 select,因为没有找到官方的说明。

四:注意事项

mysql 的变量赋值有 =:=,这两种形式,但是在select 后面的赋值,要用:=这种形式,如果不用就会出现这样的问题。

image.png

参考学习
mysql中变量赋值的顺序

推荐阅读更多精彩内容