mysql實現開窗函式

學習過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。