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