GROUP BY
GROUP BY
子句用于对查询结果进行分组,在GROUP BY
中使用GROUPING SETS
、CUBE
、ROLLUP
可以以不同的形式展示分组结果。
语法
GROUP BY expression [, ...]
GROUPING SETS
GROUPING SETS
用于在同一结果集中指定多个GROUP BY
选项,作用相当于多个GROUP BY
查询的UNION
组合形式。
SELECT origin_state, origin_zip, destination_state, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
(origin_state),
(origin_state, origin_zip),
(destination_state));
上述示例等同于:
SELECT origin_state, NULL, NULL, sum(package_weight)
FROM shipping GROUP BY origin_state
UNION ALL
SELECT origin_state, origin_zip, NULL, sum(package_weight)
FROM shipping GROUP BY origin_state, origin_zip
UNION ALL
SELECT NULL, NULL, destination_state, sum(package_weight)
FROM shipping GROUP BY destination_state;
CUBE
CUBE
用于列出所有可能的分组集。
SELECT origin_state, destination_state, sum(package_weight)
FROM shipping
GROUP BY origin_state, destination_state WITH CUBE
上述示例等同于:
SELECT origin_state, destination_state, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
(origin_state, destination_state),
(origin_state),
(destination_state),
())
ROLLUP
ROLLUP
可以以层级的方式列出分组集。
SELECT origin_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY ROLLUP (origin_state, origin_zip)
上述示例等同于:
SELECT origin_state, origin_zip, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS ((origin_state, origin_zip), (origin_state), ())
注意事项
查询中必须使用标准聚合函数(
SUM
、AVG
或COUNT
)声明非分组列,否则无法使用GROUP BY
子句。GROUP BY
中的列或表达式列表必须与查询列表中的非聚合表达式的列相同。
示例
例如,以下查询列表中包含两个聚合表达式,第一个聚合表达式使用SUM
函数,第二个聚合表达式使用COUNT
函数,其余两列(LISTID
、EVENTID
)声明为分组列。
select listid, eventid, sum(pricepaid) as revenue,
count(qtysold) as numtix
from sales
group by listid, eventid
order by 3, 4, 2, 1
limit 5;
listid | eventid | revenue | numtix
-------+---------+---------+--------
89397| 47 | 20.00 | 1
106590 | 76 | 20.00 | 1
124683 | 393 | 20.00 | 1
103037 | 403 | 20.00 | 1
147685 | 429 | 20.00 | 1
(5 rows)
GROUP BY
子句中的表达式也可以使用序号来引用所需的列。
例如,上述示例可改写为以下形式。
select listid, eventid, sum(pricepaid) as revenue,
count(qtysold) as numtix
from sales
group by 1,2
order by 3, 4, 2, 1
limit 5;
listid | eventid | revenue | numtix
-------+---------+---------+--------
89397 | 47 | 20.00 | 1
106590 | 76 | 20.00 | 1
124683 | 393 | 20.00 | 1
103037 | 403 | 20.00 | 1
147685 | 429 | 20.00 | 1
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论