时间戳

首页 > 解决方案 > Presto SQL - 在选择性分组中不同

presto - Presto SQL - 在选择性分组中不同

问题描述

ID  Name     Gender  Country 
1   Arun     Male    India
2   Akshay   Male    England
3   Chethna  Female  India
4   Priya    Female  China
5   Piyush   Male    India
6   Arun     Male    England
7   Tisha    Female  England
8   Chethna  Female  China

我想先将它们分组为男性/女性，然后是相关的国家/地区。

Query1 : select Gender, count(distinct name) from Table group by Gender

Output: 
Gender   count(distinct name)
Male     3
Female   3

像这样以 JSON 格式复制结果，结果：{male : {count : 3}, female : {count : 3} }

Query2 : select Gender, Country, count(distinct name) from Table group by Gender, Country

Output:
Gender  Country    count(distinct name)
Male    India      2
Male    England    2
Female  India      1
Female  China      2
Female  England    1

在上面的 Json 中添加这个结果，

结果：{男:{count:3,印度:{count:2},英格兰:{count:2}},女:{count:3,印度:{count:1},中国:{count:2},英格兰：{count:1}}}

那么我可以在单个查询中实现这一点吗？

标签： presto

解决方案

您可以使用以下命令在单个查询中按性别和按性别+国家/地区计算计数GROUPING SETS：

WITH data(id, name, gender, country) AS (
   VALUES
   (1, 'Arun',    'Male'  , 'India'),
   (2, 'Akshay',  'Male'  , 'England'),
   (3, 'Chethna', 'Female', 'India'),
   (4, 'Priya',   'Female', 'China'),
   (5, 'Piyush',  'Male'  , 'India'),
   (6, 'Arun',    'Male'  , 'England'),
   (7, 'Tisha',   'Female', 'England'),
   (8, 'Chethna', 'Female', 'China'))

SELECT gender, country, count(distinct name)
FROM data
GROUP BY GROUPING SETS ((gender), (gender, country))

产生：

 gender | country | _col2
--------+---------+-------
 Male   | England |     2
 Female | China   |     2
 Male   | NULL    |     3
 Female | NULL    |     3
 Female | India   |     1
 Male   | India   |     2
 Female | England |     1
(7 rows)

推荐阅读