MariaDB10.2.X-新特性1-支持分析函数
前言：前段时间看到MariaDB10.2出测试版本了，心想有什么新特性玩玩，大家都知道MySQL不支持分析函数，但是MariaDB10.2.X支持分析函数了，
1.表结构
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` varchar(30) NOT NULL COMMENT '用户ID',
`orderId` varchar(60) NOT NULL DEFAULT '' COMMENT '订单ID',
`channerId` varchar(60) NOT NULL DEFAULT '' COMMENT '充值渠道',
`amount` int(11) DEFAULT NULL COMMENT '充值金额',
PRIMARY KEY (`id`),
KEY `idx_userid` (`userId`),
KEY `idx_channerId` (`channerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2.插入测试数据
insert into t1 (userId, orderId, channerId, amount) values('张3','2016060101','支付宝',100);
insert into t1 (userId, orderId, channerId, amount) values('李4','2016060102','支付宝',98);
insert into t1 (userId, orderId, channerId, amount) values('张3','2016060103','易票联',60);
insert into t1 (userId, orderId, channerId, amount) values('张3','2016060104','银联',120);
insert into t1 (userId, orderId, channerId, amount) values('王5','2016060105','微信',170);
insert into t1 (userId, orderId, channerId, amount) values('张3','2016060106','微信',40);
insert into t1 (userId, orderId, channerId, amount) values('张3','2016060107','支付宝',200);
insert into t1 (userId, orderId, channerId, amount) values('张3','2016060108','易支付',10);
insert into t1 (userId, orderId, channerId, amount) values('张3','2016060109','微信','80);
insert into t1 (userId, orderId, channerId, amount) values('李4','2016060110','支付宝',300);
insert into t1 (userId, orderId, channerId, amount) values('李4','2016060111','银联',40);
insert into t1 (userId, orderId, channerId, amount) values('李4','2016060112','钱包',72);
insert into t1 (userId, orderId, channerId, amount) values('赵6','2016060113','闪付',20);
insert into t1 (userId, orderId, channerId, amount) values('田7','2016060114','钱包',80);
insert into t1 (userId, orderId, channerId, amount) values('刘1','2016060115','微信',60);
insert into t1 (userId, orderId, channerId, amount) values('哈2','2016060116','易支付',82);
3.查询每个充值渠道的总金额
MariaDB [test11]> select channerId,sum(amount) over(partition by channerId) from t1;
+-----------+------------------------------------------+
| channerId | sum(amount) over(partition by channerId) |
+-----------+------------------------------------------+
| 支付宝 | 698 |
| 支付宝 | 698 |
| 易票联 | 60 |
| 银联 | 160 |
| 微信 | 350 |
| 微信 | 350 |
| 支付宝 | 698 |
| 易支付 | 92 |
| 微信 | 350 |
| 支付宝 | 698 |
| 银联 | 160 |
| 钱包 | 152 |
| 闪付 | 20 |
| 钱包 | 152 |
| 微信 | 350 |
| 易支付 | 92 |
+-----------+------------------------------------------+
16 rows in set (0.02 sec)
MariaDB [test11]> select * from (
-> select channerId,sum(amount) over(partition by channerId) from t1) a
-> GROUP BY channerId;
+-----------+------------------------------------------+
| channerId | sum(amount) over(partition by channerId) |
+-----------+------------------------------------------+
| 微信 | 350 |
| 支付宝 | 698 |
| 易支付 | 92 |
| 易票联 | 60 |
| 钱包 | 152 |
| 银联 | 160 |
| 闪付 | 20 |
+-----------+------------------------------------------+
7 rows in set (0.02 sec)
4.查询每个渠道平均充值金额
MariaDB [test11]> select channerId,avg(amount) over(partition by channerId) from t1
-> ;
+-----------+------------------------------------------+
| channerId | avg(amount) over(partition by channerId) |
+-----------+------------------------------------------+
| 支付宝 | 174.5000 |
| 支付宝 | 174.5000 |
| 易票联 | 60.0000 |
| 银联 | 80.0000 |
| 微信 | 87.5000 |
| 微信 | 87.5000 |
| 支付宝 | 174.5000 |
| 易支付 | 46.0000 |
| 微信 | 87.5000 |
| 支付宝 | 174.5000 |
| 银联 | 80.0000 |
| 钱包 | 76.0000 |
| 闪付 | 20.0000 |
| 钱包 | 76.0000 |
| 微信 | 87.5000 |
| 易支付 | 46.0000 |
+-----------+------------------------------------------+
16 rows in set (0.01 sec)
MariaDB [test11]> select * from (
-> select channerId,avg(amount) over(partition by channerId) from t1) a
-> GROUP BY channerId;
+-----------+------------------------------------------+
| channerId | avg(amount) over(partition by channerId) |
+-----------+------------------------------------------+
| 微信 | 87.5000 |
| 支付宝 | 174.5000 |
| 易支付 | 46.0000 |
| 易票联 | 60.0000 |
| 钱包 | 76.0000 |
| 银联 | 80.0000 |
| 闪付 | 20.0000 |
+-----------+------------------------------------------+
7 rows in set (0.00 sec)
5.查询每个渠道最小充值金额
MariaDB [test11]> select * from ( select channerId,min(amount) over(partition by channerId) from t1) a GROUP BY channerId;
ERROR 1235 (42000): This version of MariaDB doesn't yet support 'This aggregate as window function'
可惜不支持
6.查询每个驱动最大充值金额
MariaDB [test11]> select * from ( select channerId,max(amount) over(partition by channerId) from t1) a GROUP BY channerId;
ERROR 1235 (42000): This version of MariaDB doesn't yet support 'This aggregate as window function'
MariaDB [test11]>
聪明的你，一下子猜测到不支持
#7.根据每个充值渠道，根据充值金额排名
MariaDB [test11]> SELECT RANK() OVER (PARTITION BY channerId ORDER BY amount desc ) AS rank, DENSE_RANK() OVER (PARTITION BY channerId ORDER BY amount desc ) AS dense_rank, ROW_NUMBER() OVER (PARTITION BY channerId ORDER BY amount desc) AS row_num, userId,channerId,amount from t1 ;
+------+------------+---------+--------+-----------+--------+
| rank | dense_rank | row_num | userId | channerId | amount |
+------+------------+---------+--------+-----------+--------+
| 3 | 3 | 3 | 张3 | 支付宝 | 100 |
| 4 | 4 | 4 | 李4 | 支付宝 | 98 |
| 1 | 1 | 1 | 张3 | 易票联 | 60 |
| 1 | 1 | 1 | 张3 | 银联 | 120 |
| 1 | 1 | 1 | 王5 | 微信 | 170 |
| 4 | 4 | 4 | 张3 | 微信 | 40 |
| 2 | 2 | 2 | 张3 | 支付宝 | 200 |
| 2 | 2 | 2 | 张3 | 易支付 | 10 |
| 2 | 2 | 2 | 张3 | 微信 | 80 |
| 1 | 1 | 1 | 李4 | 支付宝 | 300 |
| 2 | 2 | 2 | 李4 | 银联 | 40 |
| 2 | 2 | 2 | 李4 | 钱包 | 72 |
| 1 | 1 | 1 | 赵6 | 闪付 | 20 |
| 1 | 1 | 1 | 田7 | 钱包 | 80 |
| 3 | 3 | 3 | 刘1 | 微信 | 60 |
| 1 | 1 | 1 | 哈2 | 易支付 | 82 |
+------+------------+---------+--------+-----------+--------+
16 rows in set (0.00 sec)
#排个序，这样就好看多了，每个渠道充值排名
MariaDB [test11]> SELECT RANK() OVER (PARTITION BY channerId ORDER BY amount desc ) AS rank, DENSE_RANK() OVER (PARTITION BY channerId ORDER BY amount desc ) AS dense_rank, ROW_NUMBER() OVER (PARTITION BY channerId ORDER BY amount desc) AS row_num, userId,channerId,amount from t1 order by 5,1;
+------+------------+---------+--------+-----------+--------+
| rank | dense_rank | row_num | userId | channerId | amount |
+------+------------+---------+--------+-----------+--------+
| 1 | 1 | 1 | 王5 | 微信 | 170 |
| 2 | 2 | 2 | 张3 | 微信 | 80 |
| 3 | 3 | 3 | 刘1 | 微信 | 60 |
| 4 | 4 | 4 | 张3 | 微信 | 40 |
| 1 | 1 | 1 | 李4 | 支付宝 | 300 |
| 2 | 2 | 2 | 张3 | 支付宝 | 200 |
| 3 | 3 | 3 | 张3 | 支付宝 | 100 |
| 4 | 4 | 4 | 李4 | 支付宝 | 98 |
| 1 | 1 | 1 | 哈2 | 易支付 | 82 |
| 2 | 2 | 2 | 张3 | 易支付 | 10 |
| 1 | 1 | 1 | 张3 | 易票联 | 60 |
| 1 | 1 | 1 | 田7 | 钱包 | 80 |
| 2 | 2 | 2 | 李4 | 钱包 | 72 |
| 1 | 1 | 1 | 张3 | 银联 | 120 |
| 2 | 2 | 2 | 李4 | 银联 | 40 |
| 1 | 1 | 1 | 赵6 | 闪付 | 20 |
+------+------------+---------+--------+-----------+--------+
16 rows in set (0.01 sec)
MariaDB [test11]>
小结
跟Oracle,PostgreSQL, SQL SERVER还是有差距，不过已经很不错了。希望官方版本也引进这个新特性
