英文:
How to set the first column value of a GROUPING SETS
问题
我有以下请求：
SELECT column1, column2, SUM(column3) as total
FROM my_table
GROUP BY GROUPING SETS ((column1, column2), ())
这返回：
|Name1|Name2|QTT|
|-|-|-|
|AB|CD|15|
|ZE|EF|15|
|None|None|30
所以 `|None|None|30` 是从 `GROUPING BY SETS` 得到的输出
但我想知道如何将第一个 "None" 定义为其他值：
|SubTotal|None|30
例如。
最终输出将是：
|Name1|Name2|QTT|
|-|-|-|
|AB|CD|15|
|ZE|EF|15|
|SubTotal|None|30
英文:
I have the following request :
SELECT column1, column2, SUM(column3) as total
FROM my_table
GROUP BY GROUPING SETS ((column1, column2), ())
Which returns :
| Name1 | Name2 | QTT |
|---|---|---|
| AB | CD | 15 |
| ZE | EF | 15 |
| None | None | 30 |
So |None|None|30 is the output from the GROUPING BY SETS
But I am wondering how to define the first None to be something else :
|SubTotal|None|30
For example.
The final output would be :
| Name1 | Name2 | QTT |
|---|---|---|
| AB | CD | 15 |
| ZE | EF | 15 |
| SubTotal | None | 30 |
答案1
得分: 1
方言未提供，但与GROUPING SETS相对应的部分是GROUPING函数：
> 描述了在由GROUP BY查询生成的行中，一组表达式中的哪些是分组的。
>
> GROUPING_ID不是聚合函数，而是一个实用函数，可以与聚合一起使用，用于确定为哪个聚合级别生成了一行
CREATE TABLE my_table(Name1 TEXT, Name2 TEXT, QTT INT)
AS SELECT 'AB','CD',15
UNION SELECT 'ZE','EF',15;
SELECT CASE WHEN GROUPING_ID(Name1,Name2)=3 THEN '小计' ELSE Name1 END AS Name1
,Name2, SUM(QTT) as total
FROM my_table
GROUP BY GROUPING SETS ((Name1, Name2), ());
输出：
相关链接：
英文:
The dialect is not provided, but the corresponding part to GROUPING SETS is GROUPING function:
> Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
>
> GROUPING_ID is not an aggregate function, but rather a utility function that can be used alongside aggregation, to determine the level of aggregation a row was generated for
CREATE TABLE my_table(Name1 TEXT, Name2 TEXT, QTT INT)
AS SELECT 'AB','CD',15
UNION SELECT 'ZE','EF',15;
SELECT CASE WHEN GROUPING_ID(Name1,Name2)=3THEN 'Subtotal' ELSE Name1 END AS Name1
,Name2, SUM(QTT) as total
FROM my_table
GROUP BY GROUPING SETS ((Name1, Name2), ());
Output:
Related:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论