CREATE TABLE doc_sales (
region STRING,
category STRING,
amount INT
);
INSERT INTO doc_sales VALUES
('East', 'Electronics', 100),
('East', 'Clothing', 80),
('West', 'Electronics', 120),
('West', 'Clothing', 60);
使用
GROUPING SETS
GROUPING SETS
查询各维度小计与总计，并用
GROUPING
GROUPING
标记汇总行：
SELECT
CASE WHEN GROUPING(region) = 1 THEN '全部地区' ELSE region END AS region,
CASE WHEN GROUPING(category) = 1 THEN '全部品类' ELSE category END AS category,
SUM(amount) AS total_amount,
GROUPING(region) AS g_region,
GROUPING(category) AS g_category
FROM doc_sales
GROUP BY GROUPING SETS ((region, category), (region), (category), ())
ORDER BY g_region, g_category, region, category;
