.net函数查询_SQL窗口函数

知识点总结

窗口函数 - 爱莫脑图​mind.airmore.cn

---

基本语法：

<窗口函数>over (partition by <用于分组的列名> order by <用于排序的列名>)

1. 3种专用窗口函数的区别：

rank：并列名次会占用下个名次的位置（1,1,1,4）

dense_rank：并列名次不会占用下一个名次的位置（1,1,1,2）

row_number：不考虑并列名次的情况（1,2,3,4）

select a.soldto,a.PONumber,
rank() over(PARTITION by b.group order BY net desc),
dense_rank() over(PARTITION by b.group order BY net desc),
row_number() over(PARTITION by b.group order BY net desc) 
from 订单数据 as a join md as b on a.soldto=b.soldto

TOPN问题：

每个省订单金额最高的3张订单

select *
from(select b.province,a.soldto,a.PONumber,a.net,
row_number() over(PARTITION by b.province order BY net desc)as ranking 
from 订单数据 as a join md as b on a.soldto=b.soldto)as c
where ranking<=3

注意：嵌套查询的时候子查询出来的的结果是作为一个派生表来进行上一级的查询的，所以子查询的结果必须要有一个别名

2. 聚合函数作为窗口函数

可以计算截止到本行数据的统计数据

累计求和问题

SELECT *,
sum(总计金额)over(order by soldto)as SUM,
avg(总计金额)over(order by soldto)as avg,
max(总计金额)over(order by soldto)as max,
min(总计金额)over(order by soldto)as min
from(
select soldto,sum(net)as 总计金额
from 订单数据
GROUP BY soldto)as a
-- 将订单数据by soldto进行汇总后再计算

在每个组里比较问题

总计订单金额高于平均订单金额的客户

select*
from(
SELECT *,
avg(总计金额)over(order by province)as avg
from(
select a.soldto,a.总计金额,b.province
from
(select soldto,sum(net)as 总计金额
from 订单数据
GROUP BY soldto)as a join md as b on a.soldto=b.Soldto)as c)as d
where 总计金额>avg
-- 将订单数据by soldto进行汇总后再与md的省份做联结，计算出每个省份的平均订单金额

移动平均问题

select*
from(
SELECT *,
avg(总计金额)over(order by province rows 2 preceding)as avg
from(
select a.soldto,a.总计金额,b.province
from
(select soldto,sum(net)as 总计金额
from 订单数据
GROUP BY soldto)as a join md as b on a.soldto=b.Soldto)as c)as d
where 总计金额>avg
-- 将订单数据by soldto进行汇总后再与md的省份做联结，计算出每个省份的平均订单金额

---

存储过程

把重复要做的事情整理成业务步骤，写成SQL语句，再写进存储过程中即可以按照步骤来读取业务数据。

无参数存储过程

有参数存储过程

in输入参数

	create procedure in1(in num int) 
	begin select num;
	set num=1;
	select num; 
	end;

	set @num=0;
	call in1(@num);
	select num;

out输出参数

create procedure out2(out num int) 
	begin select num;
	set num=1;
	select num; 
	end;

	set @num=0;
	call out2(@num);
	select num;

inout输入输入参数

	create procedure inout2(inout num int) 
	begin select num;
	set num=1;
	select num; 
	end;

	set @num=0;
	call inout2(@num);
	select num;