全部产品
云市场

GROUP BY

更新时间:2019-07-10 09:24:59

GROUP BY子句用于对查询结果进行分组,在GROUP BY中使用GROUPING SETSCUBEROLLUP可以以不同的形式展示分组结果。

语法

  1. GROUP BY expression [, ...]

GROUPING SETS

GROUPING SETS用于在同一结果集中指定多个GROUP BY选项,作用相当于多个GROUP BY查询的UNION组合形式。

  1. SELECT origin_state, origin_zip, destination_state, sum(package_weight)
  2. FROM shipping
  3. GROUP BY GROUPING SETS (
  4. (origin_state),
  5. (origin_state, origin_zip),
  6. (destination_state));

上述示例等同于:

  1. SELECT origin_state, NULL, NULL, sum(package_weight)
  2. FROM shipping GROUP BY origin_state
  3. UNION ALL
  4. SELECT origin_state, origin_zip, NULL, sum(package_weight)
  5. FROM shipping GROUP BY origin_state, origin_zip
  6. UNION ALL
  7. SELECT NULL, NULL, destination_state, sum(package_weight)
  8. FROM shipping GROUP BY destination_state;

CUBE

CUBE用于列出所有可能的分组集。

  1. SELECT origin_state, destination_state, sum(package_weight)
  2. FROM shipping
  3. GROUP BY origin_state, destination_state WITH CUBE

上述示例等同于:

  1. SELECT origin_state, destination_state, sum(package_weight)
  2. FROM shipping
  3. GROUP BY GROUPING SETS (
  4. (origin_state, destination_state),
  5. (origin_state),
  6. (destination_state),
  7. ())

ROLLUP

ROLLUP可以以层级的方式列出分组集。

  1. SELECT origin_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY ROLLUP (origin_state, origin_zip)

上述示例等同于:

  1. SELECT origin_state, origin_zip, sum(package_weight)
  2. FROM shipping
  3. GROUP BY GROUPING SETS ((origin_state, origin_zip), (origin_state), ())

注意事项

  • 查询中必须使用标准聚合函数(SUMAVGCOUNT)声明非分组列,否则无法使用GROUP BY子句。

  • GROUP BY中的列或表达式列表必须与查询列表中的非聚合表达式的列相同。

示例

例如,以下查询列表中包含两个聚合表达式,第一个聚合表达式使用SUM函数,第二个聚合表达式使用COUNT函数,其余两列(LISTIDEVENTID)声明为分组列。

  1. select listid, eventid, sum(pricepaid) as revenue,
  2. count(qtysold) as numtix
  3. from sales
  4. group by listid, eventid
  5. order by 3, 4, 2, 1
  6. limit 5;
  7. listid | eventid | revenue | numtix
  8. -------+---------+---------+--------
  9. 89397| 47 | 20.00 | 1
  10. 106590 | 76 | 20.00 | 1
  11. 124683 | 393 | 20.00 | 1
  12. 103037 | 403 | 20.00 | 1
  13. 147685 | 429 | 20.00 | 1
  14. (5 rows)

GROUP BY子句中的表达式也可以使用序号来引用所需的列。

例如,上述示例可改写为以下形式。

  1. select listid, eventid, sum(pricepaid) as revenue,
  2. count(qtysold) as numtix
  3. from sales
  4. group by 1,2
  5. order by 3, 4, 2, 1
  6. limit 5;
  7. listid | eventid | revenue | numtix
  8. -------+---------+---------+--------
  9. 89397 | 47 | 20.00 | 1
  10. 106590 | 76 | 20.00 | 1
  11. 124683 | 393 | 20.00 | 1
  12. 103037 | 403 | 20.00 | 1
  13. 147685 | 429 | 20.00 | 1