2-5 GROUP BY和PARTITION BY

SQL语句中具有分组功能的是GROUP BY和PARTITION BY，他们都可以根据指定的列为表分组。区别仅仅在于，GROUP BY在分组之后把每个分组聚合成一行数据。
下面是存储了几个团队极其成员的信息表。
image.png

  1. DROP TABLE if EXISTS Teams;
  2. CREATE TABLE Teams
  3. (member VARCHAR(32) PRIMARY KEY,
  4.  team   CHAR(1)     NOT NULL,
  5.  age    INTEGER     NOT NULL);
  7. INSERT INTO Teams VALUES('大木',    'A',    28);
  8. INSERT INTO Teams VALUES('逸见',    'A',    19);
  9. INSERT INTO Teams VALUES('新藤',    'A',    23);
  10. INSERT INTO Teams VALUES('山田',    'B',    40);
  11. INSERT INTO Teams VALUES('久本',    'B',    29);
  12. INSERT INTO Teams VALUES('桥田',    'C',    30);
  13. INSERT INTO Teams VALUES('野野宫',      'D',    28);
  14. INSERT INTO Teams VALUES('鬼塚',    'D',    28);
  15. INSERT INTO Teams VALUES('加藤',    'D',    24);
  16. INSERT INTO Teams VALUES('新城',    'D',    22);
  1. mariadb> /* 理解PARTITION BY */
  2. SELECT member, team, age ,
  3.        RANK() OVER(PARTITION BY team ORDER BY age DESC) rn,
  4.        DENSE_RANK() OVER(PARTITION BY team ORDER BY age DESC) dense_rn,
  5.        ROW_NUMBER() OVER(PARTITION BY team ORDER BY age DESC) row_num
  6.   FROM Teams
  7.  ORDER BY team, rn;
  8. +--------+------+-----+----+----------+---------+
  9. | member | team | age | rn | dense_rn | row_num |
  10. +--------+------+-----+----+----------+---------+
  11. | 大木   | A    |  28 |  1 |        1 |       1 |
  12. | 新藤   | A    |  23 |  2 |        2 |       2 |
  13. | 逸见   | A    |  19 |  3 |        3 |       3 |
  14. | 山田   | B    |  40 |  1 |        1 |       1 |
  15. | 久本   | B    |  29 |  2 |        2 |       2 |
  16. | 桥田   | C    |  30 |  1 |        1 |       1 |
  17. | 鬼塚   | D    |  28 |  1 |        1 |       1 |
  18. | 野野宫 | D    |  28 |  1 |        1 |       2 |
  19. | 加藤   | D    |  24 |  3 |        2 |       3 |
  20. | 新城   | D    |  22 |  4 |        3 |       4 |
  21. +--------+------+-----+----+----------+---------+

image.png

划分出来的自己，有3个性质

  1. 它们都是非空集合
  2. 所有子集的并集等于划分之前的集合
  3. 任何两个子集之间没有交集。