如何协助MySQL实现窗口函数

参考文献：

《SQL难点解决：记录的引用》

《SQL 难点解决：集合及行号》

《SQL 难点解决：直观分组》

《SQL 难点解决：序列生成》

《MySQL难点解决：实现Oracle高级分析函数》

《MySQL难点解决：窗口函数》

窗口函数是SQL2003标准才开始有的一系列SQL函数，用于应付一些复杂运算是比较方便。但是普遍使用的MySQL数据库对窗口函数支持得却很不好，直到最近的版本才开始有部分支持，这当然就让MySQL程序员很郁闷了。

实际操作中，我们可以在MySQL里用SQL拼出窗口函数功能，但是需要使用用户变量以及多个SELECT表达式从左到右依次计算的隐含规则。下面我们来看两个例子（为调试方便，我们直接用集算器作为测试环境）。

 

1. 2016年1月销售额排名

	A
1	set @i1=0, @i2=0, @d1=null;
2	select @i1:=@i1+1 `row_number`, province, curr_sales, prev_sales,       @i2:=if(prev_sales=curr_sales,@i2,@i1) `rank` from (select province,  cast(@d1 as decimal(15,2)) as prev_sales,           @d1:=sales as curr_sales      from detail    where yearmonth=201601    order by sales desc    ) t1;
3	=connect("mysql")
4	>A3.execute(A1)
5	=A3.query@x(A2)

1. A1中语句用于初始化用户变量；
2. A2中语句先对销售额排倒序，然后每一行销售额与上一行销售额比较，若相等则排名不变，否则排名等于行号；
3. A3连接数据库；
4. A4执行初始化语句；
5. A5执行查询语句并关闭数据库连接，返回结果。

执行后A5为需要的结果。

 

2. 2016年1月和2月销售额按月分组百分比排名

	A
1	set @i1=null, @i2=0, @i3=0, @d1=null;
2	select curr_month, t1.province, curr_sales, sale_rank, if(count>1, (sale_rank-1)/(count-1), 0) as `percent_rank` from (select prev_month, curr_month, province, @i2:=if(prev_month=curr_month,@i2+1,1) as `row_number`, @i3:=if(prev_month<>curr_month, 1, if(prev_sales=curr_sales,  @i3, @i2)) as 'sale_rank', prev_sales, curr_sales from (select @i1 as prev_month, @i1:=yearmonth as curr_month,        province, @d1 as prev_sales, @d1:=sales as curr_sales from (select * from detail where yearmonth in (201601,201602) order by yearmonth, sales desc ) t111 ) t11      ) t1      join      (select yearmonth, count(*) count      from detail      where yearmonth in (201601, 201602)      group by yearmonth      ) t2      on t1.curr_month=t2.yearmonth;
3	=connect("mysql")
4	>A3.execute(A1)
5	=A3.query@x(A2)

1. A1中语句用于初始化用户变量；
2. A2中语句子查询t11求出上一行的月份和销售额，t1再求出本月行号与排名，t2算出每月的行数，最后t1与t2连接再利用公式[if(本月行数>1,(当前行的本月排名-1)/(本组行数-1),0)]求出百分比排号。

执行后A5为需要的结果。

   

 

通过上述两个例子，我们可以看到，为了实现窗口函数相应功能，SQL语句冗长、复杂而且可读性较差。另外，这里还使用了SELECT表达式从左到右依次计算的隐含规则，而这在MySQL参考手册是不推荐使用的，如果今后不能使用这一规则，那么写出来的SQL语句会更加复杂。譬如不使用这条隐含规则如何能取上一行的字段值呢？各位读者可以自行脑补。

 

值得庆幸的是，有了集算器及其特有的SPL语言，我们就大可不必这么麻烦了，MySQL只要使用最基本的SQL就行了，剩下的事由集算器来完成。

下面我们就来看看集算器的SPL语法是如何实现相应窗口函数的功能的。

1. SUM()、COUNT()、AVG()、MAX()、MIN()、VARIANCE
   1. select province, sales, sum(sales) over() `sum`,

avg(sales) over() `avg`, max(sales) over() `max`,

min(sales) over() `min`, count(*) over() `count`

from detail

where yearmonth=201601

order by sales;

	A
1