对于主要使用数据工作的 Python 开发人员来说，很难不发现自己经常深入研究 SQL 和 Python 的开源数据库 pandas。尽管这些工具使操作和转换数据变得如此容易——有时只需一行代码即可完成——但分析师仍然必须始终理解他们的数据以及代码的含义。即使计算像汇总统计这样简单的东西也可能容易出现严重的错误。
在本文中，我们将研究算术平均值。尽管传统上它是针对一维数据进行教学的，但为多维数据计算它需要一个根本不同的过程。事实上，如果将算术平均值视为一维数据进行计算，则会产生严重不正确的数字，有时与预期结果相差几个数量级。对我来说，这是一次令人谦卑的经历：即使是算术平均值也与任何其他计算一样值得仔细检查。
很少有统计计算能与最基本的计算相媲美，例如：百分比、总和，尤其是平均值，它们具有简洁性和解释力。因此，它们无处不在，从探索性数据分析到数据仪表板和管理报告。但其中之一，算术平均值，却异常地有问题。尽管传统上它是针对一维数据进行教学的，但为多维数据计算它需要一个根本不同的过程。事实上，如果将算术平均值视为一维数据进行计算，则会产生严重不正确的数字，有时与预期结果相差几个数量级。对我来说，这是一次令人谦卑的经历：即使是算术平均值也与任何其他计算一样值得仔细检查。
回归基础
算术平均值的定义是
opensource.com
或
SUM(all observations) / COUNT(number of observations)我们可以通过一个简单的摘苹果例子来理解这一点
苹果
| 姓名 | 苹果数量 |
|---|---|
| 凯蒂 | 4 |
| 艾伦 | 8 |
| 约翰 | 10 |
| 苔丝 | 8 |
| 杰西卡 | 5 |
这里的观察对象是什么？一个个体，由单个列（姓名）定义，也称为维度或属性。
使用上面的公式，我们可以计算算术平均值
SUM(4 + 8 + 10 + 8 + 5) / 5 = 7在 SQL 中，我们会这样写
SELECT AVG(num_apples) FROM apples我们刚刚计算了什么？“每人平均摘苹果的数量”（其中每个人代表一个观察对象）。
增加复杂性：二维数据
苹果
| 日期 | 姓名 | 苹果数量 |
|---|---|---|
| 2017-09-24 | 凯蒂 | 4 |
| 2017-09-24 | 艾伦 | 8 |
| 2017-09-24 | 约翰 | 10 |
| 2017-09-24 | 苔丝 | 8 |
| 2017-09-26 | 凯蒂 | 5 |
在这个例子中，我们将杰西卡替换为凯蒂，但在不同的日期。
现在，表中的每个观察对象不仅仅是（姓名）。凯蒂出现了两次，但在不同的观察对象中，因为凯蒂在不同的日子摘了苹果。相反，每个观察对象由两个维度组成：（日期，姓名）。
我们可以问和之前相同的问题：“每人平均摘苹果的数量是多少？”
我们应该期望得到一个数字，就像之前一样。我们应该期望平均值等于 7，就像我们之前得到的那样吗？
回到我们的公式
opensource.com
或
SUM(4 + 8 + 10 + 8 + 5) / 4 = 8.75
因此，尽管分子（摘苹果的数量）保持不变，但分母（人数）从 5 变为 4。凯蒂摘了两次苹果，在不同的日子，所以我们不会重复计算她。
这里发生了什么？在表级别定义的观察单位与我们分析的观察单位不同。
对于我们的分析问题，我们不是在询问每个人摘苹果的天数。我们只是在询问每个人平均摘苹果的数量，我们应该得到像“平均摘了 7 个苹果”或“平均摘了 10 个苹果”这样的答案。如果凯蒂碰巧比其他人摘苹果的天数更多，那应该真正提高平均值。在任何苹果采摘者的随机样本中，我们可能会遇到像凯蒂这样的人，他们比其他任何人都更频繁地摘苹果，这会推高每人平均摘苹果的数量。
那么我们如何在 SQL 中编写这个呢？这样做不行
SELECT AVG(num_apples) FROM apples这将给我们和之前相同的答案：7。
我们必须做的是折叠数据到我们关心的分析级别。我们不是在询问日期-人平均摘苹果的数量，这正是之前的查询会给我们的结果。我们询问的是普通人平均摘苹果的数量。我们分析的观察级别是一个人（姓名），而不是一个日期-人（日期，姓名）。
所以我们的查询看起来像这样
SELECT AVG(num_apples) FROM (
SELECT name, SUM(num_apples) AS num_apples
FROM apples
GROUP BY name
) AS t可怕。
内部查询给了我们这个结果集
苹果
| 姓名 | 苹果数量 |
|---|---|
| 凯蒂 | 9 |
| 艾伦 | 8 |
| 约翰 | 10 |
| 苔丝 | 8 |
现在，这才是我们想要取平均值的！外部查询然后执行此操作
SUM(4 + 8 + 10 + 8 + 5) / 4 = 8.75那么我们在这里学到了什么？我们的分析问题要求我们将数据的维度降低到低于表定义的维度。表定义了两个维度（日期，姓名）的观察对象，但我们的分析问题要求一个维度（姓名）的观察对象。
通过折叠进行的这种维度变化导致了分母中观察对象数量的变化，这改变了我们的平均值。
再重申一下显而易见的事实：如果我们没有对原始数据执行此折叠操作，我们计算的第一个平均值将是错误的。
为什么会更普遍地发生这种情况？
当数据存储在数据库中时，必须指定粒度级别。换句话说，“什么构成单个观察对象？”
你可以想象一个表存储这样的数据
销售额
| 日期 | 售出产品 |
|---|---|
| 2017-09-21 | 21 |
| 2017-09-22 | 28 |
| 2017-09-24 | 19 |
| 2017-09-25 | 21 |
| 2017-09-26 | 19 |
| 2017-09-27 | 18 |
但你也可以想象一个表存储相同的数据，但只是具有更高的粒度，像这样
销售额
| 日期 | 产品类别 | 售出产品 |
|---|---|---|
| 2017-09-21 | T 恤 | 16 |
| 2017-09-21 | 夹克 | 2 |
| 2017-09-21 | 帽子 | 3 |
| 2017-09-22 | T 恤 | 23 |
| 2017-09-22 | 帽子 | 5 |
| 2017-09-24 | T 恤 | 10 |
| 2017-09-24 | 夹克 | 3 |
| 2017-09-24 | 帽子 | 6 |
| 2017-09-25 | T 恤 | 21 |
| 2017-09-26 | T 恤 | 14 |
| 2017-09-26 | 帽子 | 5 |
| 2017-09-27 | T 恤 | 14 |
| 2017-09-27 | 夹克 | 4 |
在表级别定义的观察单位称为主键。主键在所有数据库表中都是必需的，并应用一个约束，即每个观察对象必须是唯一的。毕竟，如果一个观察对象出现两次但不是唯一的，它应该只是一个观察对象。
它通常遵循这样的语法
CREATE TABLE sales (
date DATE NOT NULL default '0000-00-00',
product_category VARCHAR(40) NOT NULL default '',
products_sold INT
PRIMARY KEY (date, product_category) <------
)请注意，我们选择记录数据的粒度级别实际上是我们表定义的一部分。主键定义了我们数据中的“单个观察对象”。并且在我们开始存储任何数据之前，它是必需的。
现在，仅仅因为我们记录了那个粒度级别的数据，并不意味着我们需要在那个粒度级别分析它。我们需要分析数据的粒度级别将始终是我们试图回答的问题类型的功能。
这里的关键要点是，主键在表级别定义了一个观察对象，这可能包含一个或两个或 20 个维度。但我们的分析可能不会如此精细地定义观察对象（例如，我们可能只关心每天的销售额），因此我们必须折叠数据并为我们的分析重新定义观察对象。
形式化模式
因此，我们知道，对于我们提出的任何分析问题，我们需要重新定义什么是单个观察对象，独立于主键碰巧是什么。如果我们只是在不折叠数据的情况下取平均值，我们最终将在分母中得到过多的观察对象（即，主键定义的数量），因此平均值过低。
回顾一下，使用与上面相同的数据
销售额
| 日期 | 产品类别 | 售出产品 |
|---|---|---|
| 2017-09-21 | T 恤 | 16 |
| 2017-09-21 | 夹克 | 2 |
| 2017-09-21 | 帽子 | 3 |
| 2017-09-22 | T 恤 | 23 |
| 2017-09-22 | 帽子 | 5 |
| 2017-09-24 | T 恤 | 10 |
| 2017-09-24 | 夹克 | 3 |
| 2017-09-24 | 帽子 | 6 |
| 2017-09-25 | T 恤 | 21 |
| 2017-09-26 | T 恤 | 14 |
| 2017-09-26 | 帽子 | 5 |
| 2017-09-27 | T 恤 | 14 |
| 2017-09-27 | 夹克 | 4 |
“每天平均售出多少产品？”
好吧，在这个数据集中有六天，总共售出了 126 件产品。 平均每天售出 21 件产品。
不是 9.7，这是你从这个查询中得到的结果
SELECT AVG(products_sold) FROM sales我们需要像这样折叠数据
SELECT AVG(quantity) FROM (
SELECT date, SUM(products_sold) AS quantity
FROM sales
GROUP BY date
) AS t得到 21。我们可以感受到这里的量级：9.7 与 21 完全不接近。
注释上面的查询
SELECT AVG(quantity) FROM (
SELECT date, SUM(products_sold) AS quantity
FROM sales
GROUP BY date // [COLLAPSING KEY]
) AS t在这里，我将折叠键定义为“与我们的分析相关的观察单位”。它与主键无关——它忽略了我们不关心的任何列，例如（产品类别）。折叠键表示：“我们只想在这个粒度级别工作，所以通过将所有低于它的粒度级别加起来来汇总它。”
在这种情况下，我们明确地为我们的分析定义了一个观察单位（日期），这将构成我们分母中的行数。如果我们不这样做，谁知道有多少观察对象（行）会滑入分母？（答案：我们在主键级别看到的数量。）
不幸的是，折叠键并不是故事的结局。
如果我们想要分组的平均值呢？例如，“按类别划分的平均售出产品数量是多少？”
使用分组
“按类别划分的平均售出产品数量是多少？”
看起来是一个无害的问题。会出什么问题呢？
SELECT product_category, AVG(products_sold)
FROM sales
GROUP BY product_category没什么。实际上，这行得通。这是正确的答案。我们得到
销售额
| 产品类别 | AVG(products_sold) |
|---|---|
| T 恤 | 12.83 |
| 夹克 | 3 |
| 帽子 | 4.75 |
夹克的健全性检查：我们有三天销售夹克，总共销售 4 + 3 + 2 = 9 件，所以平均值为 3。
我立即想到：“三个什么？” 答案：“平均售出三件夹克。” 问题：“平均什么？” 答案：“平均每天，我们售出三件夹克。”
好的，现在我们看到我们最初的问题不够精确——它没有提及天数！
这是我们真正回答的问题：“对于每个产品类别，每天平均售出多少产品？”
英语平均问题剖析
由于任何 SQL 查询的最终目标都是对用简单英语提出的问题进行直接的、声明式的翻译，我们首先需要理解英语问题中的各个部分。
让我们分解一下：“对于每个产品类别，每天平均售出多少产品？”
有三个部分
- 分组： 我们想要每个产品类别的平均值（product_category）
- 观察： 我们的分母应该是天数（date）
- 度量： 分子是我们正在求和的度量变量（products_sold）
对于每个组，我们想要一个平均值，这将是每天售出的产品总数除以该组中的天数。
我们的目标是将这些英语组件直接翻译成 SQL。
从英语到 SQL
这里有一些交易数据
交易
| 日期 | 产品 | 州 | 购买者 | 数量 |
|---|---|---|---|---|
| 2016-12-23 | 吸尘器 | 纽约州 | Brian King | 1 |
| 2016-12-23 | 订书机 | 纽约州 | Brian King | 3 |
| 2016-12-23 | 打印机墨水 | 纽约州 | Brian King | 2 |
| 2016-12-23 | 订书机 | 纽约州 | Trevor Campbell | 1 |
| 2016-12-23 | 吸尘器 | 马萨诸塞州 | Lauren Mills | 1 |
| 2016-12-23 | 打印机墨水 | 马萨诸塞州 | John Smith | 5 |
| 2016-12-24 | 吸尘器 | 马萨诸塞州 | Lauren Mills | 1 |
| 2016-12-24 | 键盘 | 纽约州 | Brian King | 2 |
| 2016-12-25 | 键盘 | 马萨诸塞州 | Tom Lewis | 4 |
| 2016-12-26 | 订书机 | 纽约州 | John Doe | 1 |
“对于每个州和产品，每天平均售出多少产品？”
SELECT state, product, AVG(quantity)
FROM transactions
GROUP BY state, product这给了我们
交易
| 州 | 产品 | AVG(quantity) |
|---|---|---|
| 纽约州 | 吸尘器 | 1 |
| 纽约州 | 订书机 | 1.66 |
| 纽约州 | 打印机墨水 | 2 |
| 纽约州 | 键盘 | 2 |
| 马萨诸塞州 | 吸尘器 | 1 |
| 马萨诸塞州 | 打印机墨水 | 5 |
| 马萨诸塞州 | 键盘 | 4 |
（纽约州，订书机）的健全性检查，我们应该得到总共 3 + 1 + 1 = 5，在 2 天内（2017-12-23 和 2017-12-26），得到 2.5...
哎，SQL 结果给了我们 1.66。查询一定是错误的。
这是正确的查询
SELECT state, product, AVG(quantity) FROM (
SELECT state, product, date, SUM(quantity) as quantity
FROM transactions
GROUP BY state, product, date
) AS t
GROUP BY state, product给了我们
交易
| 州 | 产品 | AVG(quantity) |
|---|---|---|
| 纽约州 | 吸尘器 | 1 |
| 纽约州 | 订书机 | 2.5 |
| 纽约州 | 打印机墨水 | 2 |
| 纽约州 | 键盘 | 2 |
| 马萨诸塞州 | 吸尘器 | 1 |
| 马萨诸塞州 | 打印机墨水 | 5 |
| 马萨诸塞州 | 键盘 | 4 |
SQL 平均问题剖析
我们确定英语平均问题有三个部分，如果我们不尊重这一点，我们就会错误地计算平均值。我们也知道英语中的组件应该翻译成 SQL 中的组件。
它们在这里
SELECT state, product,
AVG(quantity) // [MEASUREMENT VARIABLE]
FROM (
SELECT state, product, date, SUM(quantity) as quantity
FROM transactions
GROUP BY state, product, date // [COLLAPSING KEY]
) AS t
GROUP BY state, product // [GROUPING KEY]
-- [OBSERVATION KEY] = [COLLAPSING KEY] - [GROUPING KEY]
-- (date) = (state, product, date) - (state, product)这与上面的查询相同，只是带有注释。
请注意，折叠键不在我们的英语问题中——它就像伪造一个主键，但用于我们的分析，而不是使用表中定义的主键。
另请注意，在 SQL 翻译中，观察键是隐式的，而不是显式的。观察键等于折叠键（即，我们分析所需的维度，仅此而已）减去分组键（我们分组的维度）。剩下的任何东西——就是观察键，或者定义我们分析的观察对象的东西。
我第一个承认我们平均问题中最重要的一部分——也就是，定义观察对象的部分——在 SQL 中甚至不是显式的，这有多么令人困惑。它是隐式的。我称之为取多维平均值的陷阱。
要点如下
- 折叠键定义了我们将在分析中使用的维度。来自表主键的所有其他内容都将被“汇总”。我们在内部查询的 GROUP BY 中定义折叠键。
- 分组键是我们想要对其数据进行分组的维度（即，“对于每个组”）。这在外部查询的 GROUP BY 中定义。
- 折叠键 − 分组键 = 观察键.
- 如果你没有定义折叠键，你将隐式地使用表的主键作为你的折叠键。
- 如果你没有进行任何分组，则折叠键等于观察键
举例来说，如果你的表的主键是（日期、产品、州、购买者），并且你想按购买者取平均值（观察对象：购买者），对于每个州（组：州），你必须求解折叠键（即，内部 SQL 查询中的内容）。
我们不想隐式地使用主键，所以我们将使用折叠键。什么折叠键？折叠键将是（观察键：购买者）+（分组键：州）=（购买者，州）。这进入我们内部查询的 GROUP BY 中，（州）单独进入外部查询的 GROUP BY 中，隐式地，观察键是（购买者）。
最后，请注意如果我们不使用折叠键会发生什么。主键是（日期、产品、州、购买者），我们的分组键是（州）。如果我们根本不使用任何子查询，我们将得到一个答案，该答案将观察对象定义为（日期、产品、州、购买者）−（州）=（日期、产品、购买者）。这将决定我们在每个组中看到的观察对象数量，这会影响我们平均值的分母。这是错误的。
总结
我从这一切中学到的一件事是，从分析的角度来看，永远不要相信主键。它定义了粒度——即，什么是观察对象——用于记录数据，但这可能不是你分析所需要的。如果你没有明确意识到这种差异将如何影响你的计算，你的计算很可能是不正确的。因为无论你是否意识到，主键都会影响你的分母。
所以，如果你不能相信主键，最安全的方法是始终折叠数据。如果你没有进行任何分组，那么你的折叠键明确地等于你的观察键。如果你正在进行分组，那么你的折叠键是你观察键和分组键的总和。但有一点是肯定的：如果你不折叠你的数据，你就是在隐式地信任主键。
我学到的第二件事是，完全与 SQL 无关，提出关于平均值的问题并不总是直观的。“按证券按日计算的平均股价是多少？” 即使用简单的英语来说，也是一个模棱两可的问题！ 那是每种证券每天的平均股价，还是每天每种证券的平均份额？
业务问题不是以数据库逻辑或程序代码的形式出现的。相反，它们是使用自然语言制定的，必须翻译成数据语言。作为数据分析师，你必须澄清：“我们究竟在取什么平均值？” 在这里，从折叠、分组和观察键的角度思考是有帮助的，尤其是在概念化有多少观察对象进入你的分母时。
这个问题不仅限于 SQL，而是任何关系数据存储，例如 pandas.DataFrames 或 R 数据表。如果你和我一样，你可能会仔细研究你的旧代码，grep 查找平均值，并想，“我在这里平均计算的究竟是什么？”
这篇文章最初发表在 alexpetralia.com 上，并经许可转载。
要了解更多信息，请参加 Alex Petralia 的演讲，分析数据：pandas 和 SQL 教会了我关于取平均值的知识，在 PyCon 克利夫兰 2018 上。
1 条评论