1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SELECT
region,
city,
grouping(region, city) AS grp_idx,
count(DISTINCT id) AS num_total,
count(DISTINCT id) FILTER (WHERE is_poi) AS num_poi,
count(DISTINCT id) FILTER (WHERE is_gov) AS num_gov
FROM location_data
GROUP BY GROUPING SETS ( (region), (city), (region, city), () );
SELECT
region,
city,
grouping(region, city) AS grp_idx,
count(DISTINCT id) AS num_total,
count(DISTINCT id) FILTER (WHERE is_poi) AS num_poi,
count(DISTINCT id) FILTER (WHERE is_gov) AS num_gov
FROM location_data
GROUP BY ROLLUP ( (region), (city) );
SELECT
region,
city,
grouping(region, city) AS grp_idx,
count(DISTINCT id) AS num_total,
count(DISTINCT id) FILTER (WHERE is_poi) AS num_poi,
count(DISTINCT id) FILTER (WHERE is_gov) AS num_gov
FROM location_data
GROUP BY CUBE ( (region), (city) );
|
