增量计算标准差：利用dbt在SQL中实现高效统计更新
作者供图
SQL 聚合函数在应用于大型数据集时可能会非常耗费计算资源。随着数据集的增长，反复对整个数据集进行重新计算会变得低效。为了应对这一挑战，通常会采用 增量聚合 方法 — 这种方法涉及维护先前的状态，并使用新数据进行更新。虽然增量聚合对于 COUNT 或 SUM 这样的聚合来说相对简单，但对于像标准差这样更复杂的指标，如何应用增量聚合就成了一个问题。
标准差（https://en.wikipedia.org/wiki/Standard_deviation）是一种统计指标，用于衡量变量值的变化程度或离散程度。
它是通过取方差（https://en.wikipedia.org/wiki/Variance）的平方根来计算的。
样本方差的计算公式如下：
样本的方差公式（计算样本方差的方法）
计算标准差可能有点复杂，因为它需要同时更新均值和每个数据点的平方差之和。然而，通过代数操作，我们可以推导出一个用于增量计算的公式——这使得可以在现有数据集的基础上轻松地加入新数据。这种方法避免每次添加新数据时都需要重新计算，从而大大提高了效率，让过程更加高效。更多细节请参考我的GitHub。
推导的样本方差公式
公式基本上被分成了三个部分：
- 已有的加权方差
- 新的加权方差
- 组间均值差异的方差，用来考虑组间的差异。
此方法通过保留已有数据集的COUNT（k）、AVG（µk）和VAR（Sk），并将其与新数据集的COUNT（n）、AVG（µn）和VAR（Sn）相结合，逐步计算方差增量。因此，能轻松高效地算出更新后的标准差，无需再扫描全部数据。
现在我们已经对增量标准差的数学有了大致的理解（至少有了基本的理解），接下来我们就来探讨一下如何在dbt中用SQL实现这个功能。在接下来的例子中，我们将逐步介绍如何搭建增量模型来计算并更新用户的交易数据统计。
考虑一个名为 stg__transactions 的交易表，该表记录用户的交易和相关事件。我们的目标是创建一个不随时间变化的静态表 int__user_tx_state ，该表聚合用户的交易状态。如下的图片显示了两个表的列详细信息。
作者的图片
为了提高效率，我们旨在通过将新的交易数据与现有的聚合状态（即当前用户状态）相结合，逐步更新状态记录。这种方法使我们能够在不查看所有历史数据的情况下计算出更新后的用户状态。
作者的图片
以下代码假定你对一些 dbt 概念有一定了解，如果你不熟悉这些概念，你仍然可以理解代码，但建议你阅读 dbt 的增量指南 或这篇很棒的文章 dbt 增量模型的正确方式。
我们将一步一步地构建一个完整的dbt SQL步骤，旨在高效地计算增量计算，而无需反复扫描整个表。我们首先在dbt中将模型定义为增量，并使用unique_key来更新现有行，而不是插入新行。
-- 依赖于: {{ ref('stg_transactions') }}
{{ config(materialized='增量', unique_key=['USER_ID'], incremental_strategy='增量策略') }}接下来，我们从 _stg_transactions 表中获取记录。
is_incremental 这个部分过滤出时间戳晚于最新用户更新时间的交易记录，从而只包含“新交易记录”。
WITH 交易数据 AS (
SELECT
用户ID（USER_ID）,
交易ID,
交易时间戳,
金额
FROM {{ ref('stg__transactions') }}
{% if is_incremental() %}
WHERE 时间戳 > COALESCE((select max(更新时间戳) from {{ this }}), TIMESTAMP '0')
{% endif %}
)在获取新的交易记录之后，我们将它们按用户进行汇总，然后在后续的CTE中逐步更新每个用户的账户状态。
INCREMENTAL_USER_TX_DATA AS ( -- 增量用户交易数据
SELECT
USER_ID,
MAX(TX_TIMESTAMP) AS 最近更新时间, -- 最近更新时间
COUNT(TX_VALUE) AS 增量计数, -- 增量计数
AVG(TX_VALUE) AS 增量平均值, -- 增量平均值
SUM(TX_VALUE) AS 增量总和, -- 增量总和
COALESCE(STDDEV(TX_VALUE), 0) AS 增量标准差 -- 增量标准差
FROM
NEW_USER_TX_DATA -- 新用户交易数据
GROUP BY
USER_ID
)我们现在来到了需要真正计算聚合结果的重要部分。如果我们还没有启用增量模式，或者还没有任何“状态”行，我们只需要计算新的聚合值。
NEW_USER_累积数据 AS (
SELECT
NEW_DATA.USER_ID,
{% if not is_incremental() %} 不是增量更新时,
NEW_DATA.更新时间 AS 更新时间,
NEW_DATA.增量计数 AS COUNT_TX,
NEW_DATA.增量平均 AS AVG_TX,
NEW_DATA.增量总和 AS SUM_TX,
NEW_DATA.增量标准差 AS STDDEV_TX
{% else %} 是增量更新时,
...但当我们处于增量模式下时，我们需要将过去的数据与根据上述公式在 INCREMENTAL_USER_TX_DATA CTE（公共表表达式）中生成的新数据结合起来。我们首先计算新的总和（SUM）、计数（COUNT）和平均值（AVG）。
...
{% else %}
COALESCE(EXISTING_USER_DATA.COUNT_TX, 0) AS _n, -- 这是 _n
NEW_DATA.INCREMENTAL_COUNT AS _k, -- 这是 _k
COALESCE(EXISTING_USER_DATA.SUM_TX, 0) + NEW_DATA.INCREMENTAL_SUM AS NEW_SUM_TX, -- 新总和
COALESCE(EXISTING_USER_DATA.COUNT_TX, 0) + NEW_DATA.INCREMENTAL_COUNT AS NEW_COUNT_TX, -- 新计数
NEW_SUM_TX / NEW_COUNT_TX AS AVG_TX, -- 新平均值
COALESCE (如果存在则返回第一个非空值)
...我们接着计算方差公式里的三个部分
1. 如果之前的样本集包含一个或更少的项目，现有的加权方差将被截断至0：
...
CASE
WHEN _n > 1 THEN (((_n - 1) / (NEW_COUNT_TX - 1)) * POWER(COALESCE(EXISTING_USER_DATA.STDDEV_TX, 0), 2))
ELSE 0
END AS 现加权方差, -- 现有加权方差
...2. 同样地，增量加权方差也是等等。
...
CASE
WHEN _k > 1 THEN (((_k - 1) / (NEW_COUNT_TX - 1)) * POWER(NEW_DATA.INCREMENTAL_STDDEV, 2))
ELSE 0
END AS INCREMENTAL_WEIGHTED_VARIANCE, -- 增量加权方差
...3. 如前所述的，均值差的方差和用于连接过去数据的SQL术语包括在内。
...
POWER((COALESCE(EXISTING_USER_DATA.AVG_TX, 0) - NEW_DATA.INCREMENTAL_AVG), 2) AS 均值差平方,
CASE
WHEN NEW_COUNT_TX = 1 THEN 0
ELSE (_n * _k) / (NEW_COUNT_TX * (NEW_COUNT_TX - 1))
END AS 组间权重, -- 组间权重
组间权重 * 均值差平方 AS 均值差方差, -- 组间方差
现有加权方差 + 增量加权方差 + 均值差方差 AS 总体方差,
CASE
WHEN _n = 0 THEN NEW_DATA.INCREMENTAL_STDDEV -- 无历史数据
WHEN _k = 0 THEN EXISTING_USER_DATA.STDDEV_TX -- 无新数据
ELSE SQRT(总体方差) -- 总体标准差（方差的平方根）
END AS 总体标准差,
NEW_DATA.UPDATED_AT AS 新数据更新时间, -- 更新时间
NEW_SUM_TX AS 新数据总和, -- 总和
NEW_COUNT_TX AS 新数据计数 -- 计数
{% endif %}
FROM
INCREMENTAL_USER_TX_DATA new_data -- 增量用户交易数据 new_data
{% if is_incremental() %}
左连接
{{ this }} EXISTING_USER_DATA
ON
NEW_DATA.USER_ID = EXISTING_USER_DATA.USER_ID
{% endif %}
)最后，我们要选择表中的列，同时考虑到更新的数据和不变的数据。
SELECT
USER_ID,
UPDATED_AT,
COUNT_TX,
SUM_TX,
AVG_TX,
STDDEV_TX
FROM 新用户累计数据通过把这些步骤结合起来，我们就得到了最终的SQL模型。
-- depends_on: {{ ref('stg__initial_table') }}
{{ config(materialized='incremental', unique_key=['USER_ID'], incremental_strategy='merge') }}
WITH NEW_USER_TX_DATA AS (
SELECT
USER_ID,
TX_ID,
TX_TIMESTAMP,
TX_VALUE
FROM {{ ref('stg__initial_table') }}
{% if is_incremental() %}
WHERE TX_TIMESTAMP > COALESCE((select max(UPDATED_AT) from {{ this }}), 0::TIMESTAMP_NTZ)
{% endif %}
),
INCREMENTAL_USER_TX_DATA AS (
SELECT
USER_ID,
MAX(TX_TIMESTAMP) AS UPDATED_AT,
COUNT(TX_VALUE) AS INCREMENTAL_COUNT,
AVG(TX_VALUE) AS INCREMENTAL_AVG,
SUM(TX_VALUE) AS INCREMENTAL_SUM,
COALESCE(STDDEV(TX_VALUE), 0) AS INCREMENTAL_STDDEV
FROM
NEW_USER_TX_DATA
GROUP BY
USER_ID
),
NEW_USER_CUMULATIVE_DATA AS (
SELECT
NEW_DATA.USER_ID,
{% if not is_incremental() %}
NEW_DATA.UPDATED_AT AS UPDATED_AT,
NEW_DATA.INCREMENTAL_COUNT AS COUNT_TX,
NEW_DATA.INCREMENTAL_AVG AS AVG_TX,
NEW_DATA.INCREMENTAL_SUM AS SUM_TX,
NEW_DATA.INCREMENTAL_STDDEV AS STDDEV_TX
{% else %}
COALESCE(EXISTING_USER_DATA.COUNT_TX, 0) AS _n, -- 这是增量标准差
NEW_DATA.INCREMENTAL_COUNT AS _k, -- 这是k
COALESCE(EXISTING_USER_DATA.SUM_TX, 0) + NEW_DATA.INCREMENTAL_SUM AS NEW_SUM_TX, -- 新总和
COALESCE(EXISTING_USER_DATA.COUNT_TX, 0) + NEW_DATA.INCREMENTAL_COUNT AS NEW_COUNT_TX, -- 新计数
NEW_SUM_TX / NEW_COUNT_TX AS AVG_TX, -- 新平均值
CASE
WHEN _n > 1 THEN (((_n - 1) / (NEW_COUNT_TX - 1)) * POWER(COALESCE(EXISTING_USER_DATA.STDDEV_TX, 0), 2))
ELSE 0
END AS EXISTING_WEIGHTED_VARIANCE, -- 已有的加权方差
CASE
WHEN _k > 1 THEN (((_k - 1) / (NEW_COUNT_TX - 1)) * POWER(NEW_DATA.INCREMENTAL_STDDEV, 2))
ELSE 0
END AS INCREMENTAL_WEIGHTED_VARIANCE, -- 新增的加权方差
POWER((COALESCE(EXISTING_USER_DATA.AVG_TX, 0) - NEW_DATA.INCREMENTAL_AVG), 2) AS MEAN_DIFF_SQUARED,
CASE
WHEN NEW_COUNT_TX = 1 THEN 0
ELSE (_n * _k) / (NEW_COUNT_TX * (NEW_COUNT_TX - 1))
END AS BETWEEN_GROUP_WEIGHT, -- 组间加权系数
BETWEEN_GROUP_WEIGHT * MEAN_DIFF_SQUARED AS MEAN_DIFF_VARIANCE,
EXISTING_WEIGHTED_VARIANCE + INCREMENTAL_WEIGHTED_VARIANCE + MEAN_DIFF_VARIANCE AS VARIANCE_TX,
CASE
WHEN _n = 0 THEN NEW_DATA.INCREMENTAL_STDDEV -- 无历史数据
WHEN _k = 0 THEN EXISTING_USER_DATA.STDDEV_TX -- 无新增数据
ELSE SQRT(VARIANCE_TX)
END AS STDDEV_TX,
NEW_DATA.UPDATED_AT AS UPDATED_AT,
NEW_SUM_TX AS SUM_TX,
NEW_COUNT_TX AS COUNT_TX
{% endif %}
FROM
INCREMENTAL_USER_TX_DATA new_data
{% if is_incremental() %}
LEFT JOIN
{{ this }} EXISTING_USER_DATA
ON
NEW_DATA.USER_ID = EXISTING_USER_DATA.USER_ID
{% endif %}
)
SELECT
USER_ID,
UPDATED_AT,
COUNT_TX,
SUM_TX,
AVG_TX,
STDDEV_TX
FROM NEW_USER_CUMULATIVE_DATA在整个过程中，我们展示了如何有效地处理非增量模式和增量模式，使用数学方法有效地更新方差和标准差等指标。通过无缝地将历史数据与新数据结合，我们实现了实时数据聚合的优化和扩展方法。
在这篇文章中，我们探讨了逐步计算标准差的数学技术以及如何使用 dbt 的增量模型来实现它。这种方法被证明非常高效，可以在不重新扫描整个数据集的情况下高效处理大规模数据集。实践中，这导致了更快、更可扩展的系统，能够高效处理实时更新。如果您有任何想法或想要进一步讨论，请随时联系我，我很乐意听取您的意见。
共同学习，写下你的评论
评论加载中...
作者其他优质文章
