【SQL】窗口函数及其他进阶用法

一个支持SQL online的在线编译器😄
https://www.jdoodle.com/execute-sql-online/

基本用法

执行顺序：哪里分组有订单 Where, Group By, Having, Order by

窗口函数

定义

窗口函数：SQL语句中OVER子句，OVER（）括号里的内容就是窗口函数的作用域，窗口函数中的元素，常见的有三类：分区、排序、框架

1）分区
PARTITION BY COL1 [ORDER BY COL2]
2）排序
3）框架：是在一个分区内进行进一步限制的筛选器
FIRST_VALUE
LAST_VALUE

支持窗口函数的查询元素：SELECT、ORDER BY

类别
1）排名：ROW_NUMBER( )、RANK( )、DENSE_RANK( )

2）分布函数
PERCENT_RANK(百分位排名)、CUME_DIST(累计分布)

3）偏移函数
FIRST_VALUE、LAST_VALUE

案例

表格如下：

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , 'mat' , 80);
insert into SC values('01' , 'pai' , 90);
insert into SC values('01' , 'eng' , 99);
insert into SC values('02' , 'mat' , 70);
insert into SC values('02' , 'pai' , 60);
insert into SC values('02' , 'eng' , 80);
insert into SC values('03' , 'mat' , 80);
insert into SC values('03' , 'pai' , 80);
insert into SC values('03' , 'eng' , 80);
insert into SC values('04' , 'mat' , 50);
insert into SC values('04' , 'pai' , 30);
insert into SC values('04' , 'eng' , 20);
insert into SC values('05' , 'mat' , 76);
insert into SC values('05' , 'pai' , 87);
insert into SC values('06' , 'mat' , 31);
insert into SC values('06' , 'eng' , 34);
insert into SC values('07' , 'pai' , 89);
insert into SC values('07' , 'eng' , 98);

Output

1. 组内排序

• RANK( )
  用法：最常用的方式，如果有两个并列第一，那么只有第三名，无第二名
  语法：rank() over(partition by 分类组别 order by 排序列 desc) rank
  可以把这个语句视为新生成一个变量

select SId,CId,rank() 
	over(partition by CId order by score desc) rank 
	from SC;

Output

• DENSE_RANK( )
  用法：如果有两个并列第一，仍然有第二名
  语法：dense_rank() over(partition by 分类组别 order by 排序列 desc) rank
  可以把这个语句视为新生成一个变量

select SId,CId,dense_rank() 
	over(partition by CId order by score desc) rank 
	from SC;

Output

• ROW_NUMBER( )
  用法：不允许并列，如果有两个并列第一，那么其中一个是第一，另一个是第二
  语法：row_number() over(partition by 分类组别 order by 排序列 desc) rank
  可以把这个语句视为新生成一个变量

select SId,CId,score,ROW_NUMBER() 
	over(partition by CId order by score desc) rank 
	from SC;

Output

2. 组内Top n, Bottom n

思路：就是组内排序外面再加一层查询，easy！

• 每组的top3

select new_SC.*,new_SC.rn 
	from (select SId,CId,score,row_number() 
		over(partition by CId order by score desc) rn 
		from SC) as new_SC 
	where new_SC.rn < 4 ;

Output

• 每组的bottom3

select new_SC.*,new_SC.rn 
	from (select SId,CId,score,row_number() 
		over(partition by CId order by score asc) rn 
		from SC) as new_SC 
	where new_SC.rn < 4 ;

Output

3. 把组内不同行转列

案例1:

select SId, 
	max(case CId when 'eng' then score else 0 end) eng, 
	max(case CId when 'mat' then score else 0 end) mat,
	max(case CId when 'pai' then score else 0 end) pai 
	from SC 
	group by SId;

案例2和3的数据集

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , 'mat' , 80);
insert into SC values('01' , 'pai' , 90);
insert into SC values('01' , 'eng' , 99);
insert into SC values('02' , 'mat' , 70);
insert into SC values('02' , 'pai' , 60);
insert into SC values('02' , 'eng' , 80);
insert into SC values('03' , 'mat' , 80);
insert into SC values('03' , 'pai' , 80);
insert into SC values('03' , 'eng' , 80);
insert into SC values('04' , 'mat' , 50);
insert into SC values('04' , 'pai' , 30);
insert into SC values('04' , 'eng' , 20);
insert into SC values('05' , 'mat' , 76);
insert into SC values('05' , 'pai' , 87);
insert into SC values('06' , 'mat' , 31);
insert into SC values('06' , 'eng' , 34);
insert into SC values('07' , 'pai' , 89);
insert into SC values('07' , 'eng' , 98);
insert into SC values('08' , 'mat' , 99);
insert into SC values('08' , 'eng' , 100);
insert into SC values('09' , 'eng' , 100);

Output

案例2
查询只选了英语课的学生

select * from SC 
	group by SId
	having count(CId) = sum(case when CId = 'eng' then 1 else 0 end);

案例3
查询选课内容和06号学生一样的其他学生信息

select t1.SId,t1.allCId from 

    (select SId, eng||mat||pai as allCId from 
        (select SId,
            max(case when CId = 'eng' then 1 else 0 end) eng,
            max(case when CId = 'mat' then 1 else 0 end) mat,
            max(case when CId = 'pai' then 1  else 0 end) pai
            from SC
        group by SId) 
    ) t1,  --第一张表用于生产新字段
    
    (select allCId from
        (select SId, eng||mat||pai as allCId from 
            (select SId,
                max(case when CId = 'eng' then 1 else 0 end) eng,
                max(case when CId = 'mat' then 1 else 0 end) mat,
                max(case when CId = 'pai' then 1  else 0 end) pai
                from SC
                group by SId) 
            ) where SId = '06'
    )as t2  --第二张表用于找到学号06学生的值
    
where t1.allCId = t2.allCId;

Output

4. 随机取数

从SC表格中随机取出5个数

select * from SC order by Random() limit 5;

https://bbs.csdn.net/topics/392012376

随机抽取n%的数据

5. 求众数、中位数（组内）