MySQL 模拟实现 ORACLE 的聚合 row_number() over(partition by A)

统计数据时,需要先聚合再筛选,Oracle和DB2可以通过OVER PARTITION BY轻松办到,如:SELECT *,ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC)

可惜我面对的是MYSQL,于是需要模拟实现,为了更好地描述需求,也为了加强记忆,我做了如下例子:

有如下的统计表:(国家,统计年份,当年会员数)

mysql> select * from stat;
+----+---------+------+--------+
| id | state   | year | counts |
+----+---------+------+--------+
|  1 | 中国     | 1966 |     20 |
|  2 | 美国     | 1968 |      5 |
|  3 | 泰国     | 1962 |      4 |
|  4 | 英国     | 1964 |      8 |
|  5 | 加拿大   | 1988 |      2 |
|  6 | 马来西亚 | 1980 |      6 |
|  7 | 韩国     | 1980 |      8 |
|  8 | 朝鲜     | 1980 |     10 |
|  9 | 日本     | 1980 |     55 |
| 10 | 加拿大   | 1980 |      9 |
| 11 | 美国     | 1975 |     72 |
| 12 | 韩国     | 1990 |     10 |
| 13 | 日本     | 1989 |     50 |
| 14 | 美国     | 1977 |     12 |
| 15 | 中国     | 1990 |     60 |
| 16 | 英国     | 1980 |     85 |
| 17 | 美国     | 1999 |     40 |
| 18 | 韩国     | 2004 |     16 |
| 19 | 朝鲜     | 1998 |     50 |
| 20 | 美国     | 1989 |     80 |
| 21 | 中国     | 1999 |    205 |
| 22 | 马来西亚 | 2004 |     50 |
| 23 | 新加坡   | 2003 |     90 |
+----+---------+------+--------+
23 rows in set (0.07 sec)

需求:

国家按会员数排名,以最新的统计结果为准,(譬如中国最新的是1999年的数据,则其他年份的数据作废)

思路:

ORACLE或DB2的SQL:

SELECT *,ROW_NUMBER() OVER(PARTITION BY STATE ORDER BY YEAR DESC) NUM FROM TESTDB WHERE NUM=1 ORDER BY COUNTS;
//ROW_NUMBER() OVER   从聚合中取得行号作为值
//PARTITION BY STATE  以国家字段聚合
//ORDER BY YEAR DESC  按年排序
//WHERE NUM=1         筛选聚合行号为1的,即每国家最新的数据

MySQL的实现方法:

  1. 进行第一次select,通过state排序,把相同国家放在一起
  2. 进行第二次select,定义临时变量pdept,用于记录上一行的state字段值
  3. 定义临时变量rank,用当前行的state值对比变量pdept,相同则rank+1,否则为1
  4. 进行第三次select,筛选rank值为1的行,按counts排序

解决方案:

SELECT state,year,counts from (
    SELECT state,year,counts,
    if(@pdept=stat_tmp.state,@rank:=@rank+1,@rank:=1) as rank,
    @pdept:=stat_tmp.state
    from (
        SELECT state,year,counts
        FROM stat
        ORDER BY state ASC,year DESC 
    ) stat_tmp ,(select @pdept := null ,@rank:=0) a 
) result where rank=1 order by counts desc

参考:http://tech.it168.com/a2009/0512/275/000000275999.shtml

若您觉得我的博文对您有帮助,欢迎点击下方按钮对我打赏
打赏