GROUP BY

GROUP BY group_expression [ , ... ] [ WITH { ROLLUP | CUBE } ]
GROUP BY { group_expression | { ROLLUP | CUBE | GROUPING SETS } (grouping_set [ , ...]) } [ , ... ]

-- 创建测试表
CREATE EXTERNAL TABLE gb_dealer (id INT, city STRING, car_model STRING, quantity INT)
    USING PARQUET LOCATION 'cosn://<your_cos_bucket>/test_group_by/gb_dealer';
INSERT INTO gb_dealer VALUES
    (100,'Fremont','Honda Civic',10),(100,'Fremont','Honda Accord',15),
    (200,'Dublin','Honda Civic',20),(300,'San Jose','Honda Accord',8);

-- GROUP BY id
SELECT id, sum(quantity) FROM gb_dealer GROUP BY id ORDER BY id;

-- GROUP BY 列位置
SELECT id, sum(quantity) FROM gb_dealer GROUP BY 1 ORDER BY 1;

-- 多个聚合
SELECT id, sum(quantity) AS s, max(quantity) AS m FROM gb_dealer GROUP BY id ORDER BY id;

-- GROUP BY + DISTINCT
SELECT car_model, count(DISTINCT city) AS cnt FROM gb_dealer GROUP BY car_model;

-- GROUPING SETS
SELECT city, car_model, sum(quantity) FROM gb_dealer
GROUP BY GROUPING SETS ((city, car_model), (city), ());

-- ROLLUP
SELECT city, car_model, sum(quantity) FROM gb_dealer
GROUP BY city, car_model WITH ROLLUP ORDER BY city;

-- CUBE
SELECT city, car_model, sum(quantity) FROM gb_dealer
GROUP BY city, car_model WITH CUBE ORDER BY city;

-- FILTER
SELECT id, sum(quantity) FILTER (WHERE car_model = 'Honda Civic') FROM gb_dealer GROUP BY id ORDER BY id;