您真的了解SQL的GROUP BY和HAVING子句吗？

最新推荐文章于 2022-11-20 09:50:33 发布

danpu0978

最新推荐文章于 2022-11-20 09:50:33 发布

阅读量244

点赞数

文章标签：数据库 java mysql python 大数据

在SQL中，有些事情我们只是想当然而已，没有适当考虑。

其中之一是GROUP BY和不太流行的HAVING子句。让我们看一个简单的例子。对于本示例，我们将重申在上一篇文章中看到的示例数据库，其中包含了出色的LEAD（），LAG（），FIRST_VALUE（）和LAST_VALUE（）函数：

CREATE TABLE countries (
  code CHAR(2) NOT NULL,
  year INT NOT NULL,
  gdp_per_capita DECIMAL(10, 2) NOT NULL,
  govt_debt DECIMAL(10, 2) NOT NULL
);

在没有窗口函数之前，仅使用GROUP BY进行聚合。我们可以使用SQL询问数据库的一个典型问题是：

在过去四年中，每年人均GDP超过4万美元的国家中，前三名平均政府债务占GDP的百分比是多少？

ew。一些（学术）业务要求。

在SQL（PostgreSQL方言）中，我们将编写：

select code, avg(govt_debt)
from countries
where year > 2010
group by code
having min(gdp_per_capita) >= 40000
order by 2 desc
limit 3

或者，带有内嵌评论

-- The average government debt
select code, avg(govt_debt)

-- for those countries
from countries

-- in the last four years
where year > 2010

-- yepp, for the countries
group by code

-- whose GDP p.c. was over 40'000 in every year
having min(gdp_per_capita) >= 40000

-- The top 3
order by 2 desc
limit 3

结果是：

code     avg
------------
JP    193.00
US     91.95
DE     56.00

记住完整理解SQL的10个简单步骤：

FROM生成数据集
WHERE减少生成的数据集
GROUP BY汇总简化的数据集
HAVING减少汇总的数据集
SELECT转换精简的聚合数据集
ORDER BY对转换后的数据集进行排序
LIMIT .. OFFSET构成排序后的数据集

… LIMIT .. OFFSET可能有不同的风味。

空的GROUP BY子句

GROUP BY一个非常特殊的情况是显式或隐式空GROUP BY子句。这是我们可以询问数据库的问题：

有没有人均GDP超过5万美元的国家？

在SQL中，我们将编写：

select true answer
from countries
having max(gdp_per_capita) >= 50000

结果是

answer
------
t

您当然可以改用EXISTS子句（在这种情况下，请不要使用COUNT(*) ）：

select exists(
  select 1 
  from countries 
  where gdp_per_capita >= 50000
);

我们会再次得到：

answer
------
t

…但是让我们关注普通的HAVING子句。

并不是每个人都知道HAVING可以全部单独使用，或者拥有HAVING全部意味着什么。 SQL 1992标准已经允许在不使用GROUP BY情况下使用HAVING ，但是直到SQL：1999引入GROUPING SETS时，该语法的语义才被明确定义：

7.10 <具有子句>
<having clause> ::= HAVING <search condition>
语法规则

1）设HC为<having子句>。令TE为立即包含的<表表达式>
HC。如果TE没有立即包含<group by子句>，则GROUP BY（）是隐式的。

那很有意思。如果我们忽略显式的GROUP BY子句，则存在一个隐式的GROUP BY ( ) 。如果您愿意进一步研究SQL标准，则会发现：

<group by clause> ::=
    GROUP BY <grouping specification>

<grouping specification> ::=
    <grouping column reference>
  | <rollup list>
  | <cube list>
  | <grouping sets list>
  | <grand total>
  | <concatenated grouping>

<grouping set> ::=
    <ordinary grouping set>
  | <rollup list>
  | <cube list>
  | <grand total>

<grand total> ::= <left paren> <right paren>

因此， GROUP BY ( )本质上是按“总计”分组，如果我们只想寻找有史以来最高的人均GDP，这就是直观的情况：

select max(gdp_per_capita)
from countries;

产生：

max
--------
52409.00

上面的查询也与此隐式相同（PostgreSQL不支持）：

select max(gdp_per_capita)
from countries
group by ();

很棒的分组集

在本文的这一部分中，我们将离开PostgreSQL领域，进入SQL Server领域，因为PostgreSQL可耻地没有实现以下任何条件（尚未实现）。

现在，如果不仔细看一下SQL：1999标准GROUPING SETS ，我们将无法理解总计（空GROUP BY ( )子句）。你们中有些人可能听说过CUBE()或ROLLUP()分组功能，它们只是常用GROUPING SETS语法糖。让我们尝试在一个查询中回答这个问题：

每年或每个国家的最高人均GDP值是多少

在SQL中，我们将编写：

select code, year, max(gdp_per_capita)
from countries
group by grouping sets ((code), (year))

产生两个串联的记录集：

code    year    max
------------------------
NULL    2009    46999.00 <- grouped by year
NULL    2010    48358.00
NULL    2011    51791.00
NULL    2012    52409.00

CA      NULL    52409.00 <- grouped by code
DE      NULL    44355.00
FR      NULL    42578.00
GB      NULL    38927.00
IT      NULL    36988.00
JP      NULL    46548.00
RU      NULL    14091.00
US      NULL    51755.00

很好，不是吗？这与使用UNION ALL查询本质上是一样的事情

select code, null, max(gdp_per_capita)
from countries
group by code
union all
select null, year, max(gdp_per_capita)
from countries
group by year;

实际上，这是完全一样的，因为后者显式连接了两组分组记录……即两个GROUPING SETS 。这个SQL Server文档页面也很好地解释了它。

其中最强大的就是：CUBE（）

现在，想像一下，您想要添加“总计”以及每个国家和年份的最高值，从而生成四个不同的串联集。为了限制结果，在此示例中，我们还将过滤掉少于48000的GDP：

select 
  code, year, max(gdp_per_capita), 
  grouping_id(code, year) grp
from countries
where gdp_per_capita >= 48000
group by grouping sets (
  (), 
  (code), 
  (year), 
  (code, year)
)
order by grp desc;

现在，此美观的查询将产生我们可以想象的所有可能的分组组合，包括总计，以产生：

code    year    max         grp
---------------------------------
NULL    NULL    52409.00    3 <- grand total

NULL    2012    52409.00    2 <- group by year
NULL    2010    48358.00    2
NULL    2011    51791.00    2

CA      NULL    52409.00    1 <- group by code
US      NULL    51755.00    1

US      2010    48358.00    0 <- group by code and year
CA      2012    52409.00    0
US      2012    51755.00    0
CA      2011    51791.00    0
US      2011    49855.00    0

并且由于这是报表和OLAP中非常常见的操作，因此我们可以使用CUBE()函数简单地编写相同的代码：

select 
  code, year, max(gdp_per_capita), 
  grouping_id(code, year) grp
from countries
where gdp_per_capita >= 48000
group by cube(code, year)
order by grp desc;

兼容性

尽管前几个查询也可以在PostgreSQL上运行，但是使用GROUPING SETS的查询只能在jOOQ当前支持的17个RDBMS中的4个上运行。这些是：

DB2
甲骨文
SQL服务器
Sybase SQL Anywhere

jOOQ还完全支持前面提到的语法。 GROUPING SETS变体可以这样写：

// Countries is an object generated by the jOOQ
// code generator for the COUNTRIES table.
Countries c = COUNTRIES;

ctx.select(
       c.CODE,
       c.YEAR,
       max(c.GDP_PER_CAPITA),
       groupingId(c.CODE, c.YEAR).as("grp"))
   .from(c)
   .where(c.GDP_PER_CAPITA.ge(new BigDecimal("48000")))
   .groupBy(groupingSets(new Field[][] {
       {},
       { c.CODE },
       { c.YEAR },
       { c.CODE, c.YEAR }
   }))
   .orderBy(fieldByName("grp").desc())
   .fetch();

…或CUBE()版本：

ctx.select(
       c.CODE,
       c.YEAR,
       max(c.GDP_PER_CAPITA),
       groupingId(c.CODE, c.YEAR).as("grp"))
   .from(c)
   .where(c.GDP_PER_CAPITA.ge(new BigDecimal("48000")))
   .groupBy(cube(c.CODE, c.YEAR))
   .orderBy(fieldByName("grp").desc())
   .fetch();