mysql实现开窗函数
http://blog.itpub.net/29989552/viewspace-2123077/
学习过oracle的应该知道,oracle中的分析函数功能十分强大,包括mssql、postgresql等数据库都支持开窗函数。然而mysql至今都没有提供这样的功能,今天就来探讨下用mysql实现一些开窗功能需求。
实验数据:表sale
| month | user_id | amount |
+--------+---------+--------+
| 201601 | 1 | 500 |
| 201601 | 2 | 300 |
| 201601 | 3 | 100 |
| 201602 | 1 | 1000 |
| 201602 | 2 | 800 |
| 201603 | 2 | 1000 |
| 201603 | 3 | 500 |
| 201604 | 1 | 1000 |
需求一:求每个月的销售额及累计销售额(二月份额累计销售额是一月销售额加二月销售额)
开窗函数实现:
select month,sum(amount),sum(sum(amount)) over (ORDER BY month) from sale GROUP BY month ORDER BY month
month | sum | sum
--------+------+------
201601 | 900 | 900
201602 | 1800 | 2700
201603 | 1500 | 4200
201604 | 1000 | 5200
轻松完成需求
mysql实现:
select
a.month,a.amount,sum(b.amount)
from (select month,sum(amount) amount from sale GROUP BY month) a CROSS JOIN (select month,sum(amount) amount from sale GROUP BY month) b
where a.month>=b.month GROUP BY a.month,a.amount
+--------+--------+---------------+
| month | amount | sum(b.amount) |
+--------+--------+---------------+
| 201601 | 900 | 900 |
| 201602 | 1800 | 2700 |
| 201603 | 1500 | 4200 |
| 201604 | 1000 | 5200 |
可以看出相比较于开窗函数,mysql实现起来很麻烦,先用子查询将每个月的销售汇总,再将两个子查询做笛卡尔积,性能肯定比开窗差很多,如果子查询结果集很多,做笛卡尔积是个灾难!
需求二:每个月销售冠军及销售额
开窗函数实现:
select month,user_id,amount
from
(select
month,user_id,amount,
row_number() over (PARTITION by month ORDER BY amount desc) rn
from sale) a where a.rn=1
month | user_id | amount
--------+---------+--------
201601 | 1 | 500
201603 | 2 | 1000
201602 | 1 | 1000
201604 | 1 | 1000
mysql实现:
方法一:
select a.*
from (select t1.*,
(select count(*) + 1
from sale
where month = t1.month
and amount > t1.amount) as group_id
from sale t1) a
where a.group_id = 1;
+--------+---------+--------+----------+
| month | user_id | amount | group_id |
+--------+---------+--------+----------+
| 201601 | 1 | 500 | 1 |
| 201603 | 2 | 1000 | 1 |
| 201602 | 1 | 1000 | 1 |
| 201604 | 1 | 1000 | 1 |
+--------+---------+--------+----------+
这个样的方式同样是用笛卡尔积得出和开窗rank一样的结果列出来
方法二:
select month,user_id,max(amount) from sale GROUP BY month
| month | user_id | max(amount) |
+--------+---------+-------------+
| 201601 | 1 | 500 |
| 201602 | 1 | 1000 |
| 201603 | 2 | 1000 |
| 201604 | 1 | 1000 |
mysql实现起来简单,但是注意,这个语句是不严谨的,因为user_id没有跟在group by后面,这是其他数据库所不允许。在mysql5.6中默认的sql_mode只有NO_ENGINE_SUBSTITUTIO,这样的语法可以使用,如果sql_mode有ONLY_FULL_GROUP_BY的限制,则无法使用这样的语法。mysql5.7的sql_mode就默认含有ONLY_FULL_GROUP_BY。
f