跳至正文

【读书】Learning_SQL by Alan_Beaulieu 第8章 分组聚合

标签:

SQL学习系列，最早于2023年春节期间，发布于微信公众号【格物致知】系列，一年后，整理到博客：

2024年 ㊗️新年慢乐·喜乐平安

喜乐君按：数据分析的关键是理解聚合，我最近总结了几句关键语句，尝试从业务角度理解这一切：聚合即分析、分析即聚合；维度组合是聚合的依据。理解聚合的依据，及其层次关系，是通常高级分析的必由之路。

在Learning S QL的第八章，作者介绍了关于聚合的关键内容，比如group概念，group 的字段，aggregate函数，聚合后的filter conditiond等内容。笔记记录如下，分享读者。

在近期的书稿修订中，我正在整理如何更好地理解计算，理解Excel、SQL和tableau背后的“同中之异”与“异中之同”，从而更好地普及tableau。

Chapter 8 Grouping and aggregates 

Feb 2, 2022 @喜乐君 

深刻地理解group和aggregate的关键，在于理解数据表和聚合表的业务差异、数据差异，两个视角的有机结合，就是顿悟的开始。

本章开篇的介绍最为重要，说明了数据的两个功能（存储业务数据和提供业务分析），并进一步使用了level of granularity的词汇介绍。

我先借用Kimball 和 Ross在“the data warehouse toolkit”书中的话，数据一方面要记录业务运营过程（operation recording keeping），另一方面要回答业务问题（ analytical decision making）。前者，数据总是以明细的方式存储（one transaction record at a time),我们称之为 the lowest level of granularity；后者分析却截然不同 DW/BI users almost NEVER deal with one transaction at a time. these systems are optimized for high-perfomance queries as users’ question often require than hundreds of hundreds of thousands of transaction be searched and compressed into an answer（kimball P2）,也就是说 业务决策需要的成百上千的数据明细的汇总，而非明细，也就是Higher level of granularity.

data is genrally stored as the lowest level of granularity needed by any of a database’s users; if anyone needs to look at individual customer transactions then there needs to be a table in the databases that stores individual transactions. That doesn’t mean, however, that all users must deal with the data as it is stored in the database. the Focus of this chapter is on how data can be grouped and aggregated to allow uers to interact with it as some higher level of granularity than what is stored in the database.

近期我在重新修订第一本书的计算部分，尝试更加全面、深入地理解计算背后的逻辑框架，其背后的基础，是如何理解“数据表”和“数据分析结果”的二元结构，或者说physical–logical 结构。

1、Grouping Concepts 分组概念

区分查询和分析，select profit会把明细表的每个利润值查出来，而select sum(profit)则会查询后得出所有利润的和，这才是分析。对于大数据而言，分析必聚合，聚合必分析。

聚合和分组相对而生，如同日与月、男与女。我近期正在强调的酝酿的一句话是“维度是聚合的依据”，这里的维度构成问题的层次，构成high level of granularity.

考虑到聚合后的筛选和，聚合前的筛选，还可以增加having和where条件，因此一个完整的实例如下：

-- 查询 华东地区，高利润交易（单笔大于100），查看各类别mysql> select 地区,类别,sum(利润) -> from tableau.superstore -> where 地区='华东' and 利润 >100 -> group by 地区,类别 -> having SUM(利润)>1000;+--------+--------------+-------------------+| 地区 | 类别 | sum(利润) |+--------+--------------+-------------------+| 华东 | 技术 | 633381.3359999995 || 华东 | 办公用品 | 508693.36 || 华东 | 家具 | 627252.9059999995 |+--------+--------------+-------------------+3 rows in set (0.01 sec)mysql>

这个案例和我书中的案例类型，where 利润 >100是行级别的判断筛选，having  SUM(利润)>1000是聚合之后判断筛选。where在分组聚合之前，having在分组聚合之后。

2、Aggregation Functions 聚合函数

函数是特定功能的模型化，聚合函数针对每个分组中的多行数据，完成特定的计算。常见的函数有：

Aggregate functions perform a specific operation over all rows in a group. 

  • max
  • min
  • avg _average value across a set
  • sum _ sum of values
  • count_ number of values

count distinc values

注意，在SQL并没有countd不重复计数函数，“不重复”distinct 是count的一种特殊形式，因此使用如下完成：

count (distinc [profit] ) 

implicit versus explicit groups

inplicit group 情形只有一种，那就是highest level of aggregation ，我通常用 (总公司的）利润总和来表达。

mysql> select sum(利润), avg(利润),max(利润) -> from tableau.superstore;+-------------------+--------------------+-------------+| sum(利润) | avg(利润) | max(利润) |+-------------------+--------------------+-------------+| 4295077.849999958 | 215.63800833416798 | 10108.28 |+-------------------+--------------------+-------------+1 row in set (0.02 sec)

如果在tableau中，我常常写一个自定义字符串来代替最高聚合所对应的维度“公司”，当然在SQL中，也可以写一个这样的常量。 

using expression在聚合中使用计算

along with using columns as arguments to aggregate funcions, you can build expressions to use as agruments. 

这里的expression，当然只能是行级别的计算（也就是第七章中的部分），书中用expression作为聚合的部分，其实也可以作为group by的部分，如下所示，substring是行级别字符串函数，而销售额*折扣这是算术计算 expression。

mysql> select substring(`订单 Id`,1,2) as region, -> sum(销售额*折扣) AS "折扣额expression", avg(利润) -> from tableau.superstore -> group by substring(`订单 Id`,1,2);+--------+---------------------+--------------------+| region | 折扣额expression | avg(利润) |+--------+---------------------+--------------------+| US | 645327.6928000005 | -74.04749145728651 || CN | 825726.4550500017 | 287.977592295142 |+--------+---------------------+--------------------+2 rows in set (0.03 sec)

how Nulls are handled 

这里我就只写结论了：Null空值不影响sum、avg、min、max等计算，只影响count计算。也就是空行也算一行。

Even with the addition of the null value to the table, the sum(), max(), and avg() functions all return the same values

3、Generating Groups 生成分组

People are ralely interested in looking at raw data; instead, people engaging in data analysis will want to manipulate the raw data to better suit their needs. 

To answer theses types of queries, you will need to ask the dabase server to group rows together by one or more columuns or expressions.

在这一部分，作者分为了三个小节：

single-column grouping 

Multicolumns grouping

grouping via expression

-- expression as groupmysql> select year(订单日期) as Year, sum(利润) -> from tableau.superstore -> group by year(订单日期);+------+--------------------+| Year | sum(利润) |+------+--------------------+| 2020 | 1362228.8540000026 || 2019 | 1243885.9999999993 || 2018 | 942940.4459999987 || 2017 | 746022.5499999992 |+------+--------------------+4 rows in set (0.03 sec)

维度字段是一个还是多个组合，其实并不影响分析的过程，聚合的过程是完全相同的。相比single column还是multi-columns，我更倾向使用维度dimension来理解group by字段。

不管是计算expression，还是隐形的维度（比如默认不出现的“总公司”），都是dimension，都是行级别计算或者已有的字段。

No dimension——implicit group——highest level of aggregation.

one dimension——explicit group——

multi- dimensions——explicit groups 

虽然少数的fields既可以作为dimension，也可以作为Measure，但是作为group by的argument，作为聚合aggregate的依据，必然称之为dimension，比如年龄age。

！书中没有从业务的角度，区分行级别计算和聚合计算的差异。

4、Group Filter Condition 分组筛选条件

group filter condition 和 where filter condition的区别在于执行的优先级，where优先于group。不过，我们必须从本质上理解，它们的差异在于聚合基于明细，而where是明细的筛选，group是聚合的筛选，聚合是从row level data到 aggregation数据的桥梁。

在Tableau分享中，我经常提到一个经典的案例：2018年、利润亏损的交易，查看各个子类别的销售额和利润，之后筛选销售额大于2万的子类别（即关注销售额高而利润亏损严重的子类别）。

mysql> select year(订单日期) as Year,子类别, round(sum(销售额)) 销售额agg, round(sum(利润)) 利润agg -> from tableau.superstore -> where year(订单日期)=2018 and 利润<=0 -- row level -> group by year(订单日期), 子类别 -- basis of aggregation -> having 销售额agg>20000; -- group filter condition +------+-----------+--------------+-----------+| Year | 子类别 | 销售额agg | 利润agg |+------+-----------+--------------+-----------+| 2018 | 复印机 | 103341 | -51627 || 2018 | 设备 | 28339 | -18268 || 2018 | 电话 | 91096 | -71852 || 2018 | 器具 | 54346 | -21915 || 2018 | 配件 | 30622 | -14951 || 2018 | 椅子 | 82249 | -47929 || 2018 | 书架 | 60251 | -35949 || 2018 | 桌子 | 79009 | -82888 |+------+-----------+--------------+-----------+8 rows in set (0.02 sec)
mysql> 

在tableau中我们可以轻松的实现上述的功能，这个过程更加简单，而且可以随着问题调整。

5、with rollup 通常高级分析的桥梁

generating rollups 本来属于第3小节，鉴于它的特殊性，我这里单独介绍。

虽然在汇总表中计算小计和合计不常见，不过从功能上，rollups是通常高级分析的桥梁，它可以在分析结果后生成不同层次的结果。

with rollup

with rollup 将指定的group字段上卷到更高层次，也就是作为direction合计，其他的字段就是scope分组了。这里做一个简单示例，不同年度、不同类别 的利润合计。

mysql> select year(订单日期) as Year,类别, round(sum(利润)) -> from tableau.superstore -> where year(订单日期)>2018 -> group by year(订单日期), 类别 with rollup;+------+--------------+--------------------+| Year | 类别 | round(sum(利润)) |+------+--------------+--------------------+| 2019 | 办公用品 | 349129 | -- year*类别 层次 | 2019 | 家具 | 385628 || 2019 | 技术 | 509129 || 2019 | NULL | 1243886 | -- year层次| 2020 | 办公用品 | 495578 || 2020 | 家具 | 387970 || 2020 | 技术 | 478681 || 2020 | NULL | 1362229 || NULL | NULL | 2606115 | -- 最高层次聚合+------+--------------+--------------------+9 rows in set (0.05 sec)

注意，这里不仅仅有小计（类别为null的部分），还有合计（year和类别都是null的最后一行）

分析中，最难的就是一个问题中包含了多个层次，我们可以把上面的明细数据分为三个：

  • Year *类别的层次 聚合
  • Year 的层次 聚合 
  • 最高层次聚合 ——两个都是null的部分

with cube 

with rollup 只是指定层次的聚合，而with cube则是所有层次的聚合。只是这个函数目前不被mysql支持，因此书中引用了oracle的计算结果，这里直接引用如下：

在分析过程中，可视化的合计当然比这个更加直观，特别是和颜色结合时，而更重要的不是这样的rollup，而是作为单独一列的rollup，我们称之为不同层次的计算，也是后续window窗口计算的基础。

tableau中，完成更高层次聚合需要表计算table calculation，它们和SQL中的 窗口计算window calculation对应，其背后的本质都是一致的。

在这个过程中，你会享受到「一通百通」的学习乐趣。

了解 喜乐君 的更多信息

订阅后即可通过电子邮件收到最新文章。