OLAP SQL 之美

发表于 2025-10-15 更新于 2025-10-20 分类于翻译阅读次数：本文字数： 4k 阅读时长 ≈ 14 分钟

原文：The beauty of OLAP SQL

我的日常工作是构建分析仪表板的后端系统。这些仪表板通过数据可视化，帮助用户深入洞察其业务数据。今天，我将分享一个利用 SQL 编写强大 OLAP 查询的真实案例。

请设想您正在运营一系列披萨品牌，目标是评估您的产品货架份额与主要竞争对手的差距。您最关心的业务问题可能包括：

您在披萨品类中的铺货份额（Share of Assortment, SOA）是多少？在沃尔玛分销的披萨产品中，有 50% 是您的吗？还是只有 5%？
平均而言，您的披萨产品有多少在 Kroger 进行了分销？您有 5 种不同的产品，但平均只有 2 种有售？是更多还是更少？
您的铺货份额随时间推移是如何变化的？与去年/上个月相比，是增加 10% 还是减少 5%？
哪些竞争对手占据了大部分货架份额？您与他们的比较如何？
谁正在投放新产品？谁在减少投放？
您的哪个品牌是成功的？哪个需要撤市或通过广告/更优惠的价格等进行改进？

数据结构如下所示（已匿名化）：

p_id	manufacturer	category	brand	retailer	period	distributed_days
i64	i64	i64	i64	i64	str	i64
6581	61	20	273	1	2023_52	31407
8563	34	4	274	1	2023_52	27515
8411	11	6	276	1	2023_52	30693
…	…	…	…	…	…	…

数据代表了从零售商网站（如 Walmart、Kroger 等）抓取的产品数据的一个子集。在这个具体的案例中，我们对分析每个品牌制造商的产品组合份额相对于不同变量的情况感兴趣。

每行包含有关产品的数据：

p_id: 产品的唯一 ID。
manufacturer: 制造商 ID。
category: 产品类别，例如：牙膏、除臭剂、酱料等。
brand: 产品品牌，例如：Dove、Pepsi、KitKat 等。
retailer 和 period: 零售商 ID 和数据爬取的周（这里的数据代表 2023 年的最后一周和 2024 年的第一周）。
distributed_days: 在此期间内，产品在该零售商网站上的所有商店中被看到的天数总和。例如，如果沃尔玛在美国有 1000 家商店，并且产品在一整周内在所有商店都有售，那么分销天数将是 1000 * 7 = 7000 天。我们稍后会看到如何使用它来计算铺货份额。

在接下来的内容中，我将使用 DuckDB，一个令人惊叹且强大的嵌入式查询引擎。

1) 这两周内所有零售商中，每个制造商分销了多少种独特产品？

这是一个简单的查询，我们只需获取不同的产品并按制造商分组：

SELECT
    manufacturer,
    COUNT(DISTINCT p_id) AS nb_unique_products -- 独特产品的数量
FROM
    product_details
GROUP BY
    ALL -- DuckDB 提供的强大结构
ORDER BY
    nb_unique_products DESC
LIMIT
    10;

-- ┌──────────────┬────────────────────┐
-- │ manufacturer │ nb_unique_products │
-- │    int64     │       int64        │
-- ├──────────────┼────────────────────┤
-- │            7 │               3463 │ <-- The big players
-- │           34 │               2688 │
-- │            2 │               2368 │
-- │           26 │               1227 │
-- │           39 │                978 │
-- │            6 │                783 │
-- │           58 │                727 │
-- │           21 │                499 │
-- │           57 │                372 │ <-- Let's say this is me
-- │            3 │                333 │
-- ├──────────────┴────────────────────┤
-- │ 10 rows                 2 columns │
-- └───────────────────────────────────┘

结果示例： 制造商 7、34、2 是主要的参与者。假设制造商 57 是“我”（有 372 种独特产品）。

2) 我的产品在不同类别中的分布情况如何？

只筛选我的产品，然后按产品类别分组并计算独特产品数量。

查看每个类别所占的百分比是很重要的。窗口函数是一个很棒的工具。它可以在一个行窗口上应用类似聚合的计算。

重要提示： 窗口函数考虑的行是经过 WHERE、GROUP BY 和 HAVING 子句（如果有的话）处理后产生的行。你可以认为窗口函数是最后运行的。

nb_unique_products / SUM(nb_unique_products) OVER () 的含义是：

取当前行的 nb_unique_products，并将其除以窗口中所有行 nb_unique_products 的总和。这里的窗口是所有类别。

SELECT
    category,
    -- 1. 计算每个类别中的独特产品数量
    COUNT(DISTINCT p_id) AS nb_unique_products,
    -- 2. 计算该类别产品数量占总产品数量的百分比
    ROUND(
        ---OVER ()：这是定义 “窗口” 的部分。一个空的 () 括号意味着窗口包含 GROUP BY 之后的所有行。
        100 * nb_unique_products / SUM(nb_unique_products) OVER (),
        2
    ) AS "% of products"
FROM
    product_details
WHERE
    manufacturer = 57 -- 筛选我的产品
GROUP BY
    category
ORDER BY
    nb_unique_products DESC

-- ┌──────────┬────────────────────┬───────────────┐
-- │ category │ nb_unique_products │ % of products │
-- │  int64   │       int64        │    double     │
-- ├──────────┼────────────────────┼───────────────┤
-- │        4 │                203 │         54.57 │< products concentrated on
-- │        3 │                 50 │         13.44 │< these two categories.
-- │       18 │                 23 │          6.18 │
-- │       16 │                 22 │          5.91 │
-- │       22 │                 22 │          5.91 │
-- │        2 │                 22 │          5.91 │
-- │        1 │                 18 │          4.84 │
-- │       11 │                 10 │          2.69 │
-- │        5 │                  2 │          0.54 │
-- └──────────┴────────────────────┴───────────────┘

问题： 假设您只负责类别 1、2、3 和 4。您不希望看到其他类别。您可能会尝试添加 AND category IN (1, 2, 3, 4) 到 WHERE 子句中。

SELECT
    category,
    COUNT(DISTINCT p_id) AS nb_unique_products,
    ROUND(
        100 * nb_unique_products / SUM(nb_unique_products) OVER (),
        2
    ) AS "% of products"
FROM
    product_details
WHERE
    manufacturer = 57
    AND category IN (1, 2, 3, 4) -- 你添加了这行
GROUP BY
    category
ORDER BY
    nb_unique_products DESC;

-- which gives:
-- ┌──────────┬────────────────────┬───────────────┐
-- │ category │ nb_unique_products │ % of products │
-- │  int64   │       int64        │    double     │
-- ├──────────┼────────────────────┼───────────────┤
-- │        4 │                203 │         69.28 │
-- │        3 │                 50 │         17.06 │
-- │        2 │                 22 │          7.51 │
-- │        1 │                 18 │          6.14 │
-- └──────────┴────────────────────┴───────────────┘

这样做会导致百分比（铺货份额）从 54.57% 变为 69.28%。

原因： 如前所述，窗口函数在 WHERE 和 GROUP BY 子句执行后对行进行评估。在这种情况下，意味着您仅针对（1、2、3 和 4）这些类别计算份额。

有两种方法可以解决这个问题：

使用子查询： 在外部查询中筛选类别。

SELECT
  *
FROM
 (<PREVIOUS QUERY>)
WHERE
  category IN (1, 2, 3, 4);

使用 DuckDB 提供的 QUALIFY： 在计算窗口函数后进行筛选。

SELECT
  category,
  COUNT(DISTINCT p_id) AS nb_unique_products,
  ROUND(
      100 * nb_unique_products / SUM(nb_unique_products) OVER (),
      2
  ) AS "% of products"
FROM
  product_details
WHERE
  manufacturer = 57
GROUP BY
  category
QUALIFY -- At last, filter on window function result using the following:
  category IN (1, 2, 3, 4)
ORDER BY
  nb_unique_products DESC
-- ┌──────────┬────────────────────┬───────────────┐
-- │ category │ nb_unique_products │ % of products │
-- │  int64   │       int64        │    double     │
-- ├──────────┼────────────────────┼───────────────┤
-- │        4 │                203 │         54.57 │
-- │        3 │                 50 │         13.44 │
-- │        2 │                 22 │          5.91 │
-- │        1 │                 18 │          4.84 │
-- └──────────┴────────────────────┴───────────────┘

使用 QUALIFY 后，结果将显示在所有类别总数中的份额（例如 54.57%），同时只返回您感兴趣的类别行。

3) 在类别 3 和 4 中，我们的竞争对手是谁？

我们主要在两个类别中分销产品（假设是 牙膏 和 漱口水）。我们应该更关注这些类别的竞争对手。

我们先来看一个简单的例子：

SELECT
    category,
    manufacturer,
    COUNT( DISTINCT p_id) as nb_unique_products
FROM product_details
WHERE category IN (3, 4)
GROUP BY ALL
ORDER BY category, nb_unique_products DESC
LIMIT 5;
-- ┌──────────┬──────────────┬────────────────────┐
-- │ category │ manufacturer │ nb_unique_products │
-- │  int64   │    int64     │       int64        │
-- ├──────────┼──────────────┼────────────────────┤
-- │        3 │            2 │                196 │
-- │        3 │           39 │                185 │
-- │        3 │           41 │                124 │
-- │        3 │           67 │                119 │
-- │        3 │           34 │                 92 │
-- └──────────┴──────────────┴────────────────────┘

这样我们就得到了所有制造商在类别 3 和 4 中分销的独特产品数量。
接下来，我们尝试只筛选出每个类别中排名前三的制造商，因为这些才是我们最需要重点关注的竞争对手。

SELECT
    category,
    manufacturer,
    COUNT(DISTINCT p_id) as nb_unique_products, -- over the category+manufacturer,
    row_number() OVER (
        PARTITION BY category -- rank inside the category
        ORDER BY -- more products = better position
            nb_unique_products DESC
    ) as position,
    ROUND(
        100 * nb_unique_products / SUM(nb_unique_products) OVER (
            PARTITION BY category
        ),
        2
    ) as "% of the market"
FROM
    product_details
WHERE
    category IN (3, 4)
GROUP BY
    category,
    manufacturer
QUALIFY
    position <= 3 -- top 3 competitors
    OR manufacturer = 57 -- and me please
ORDER BY
    category,
    position
-- ┌──────────┬──────────────┬────────────────────┬──────────┬─────────────────┐
-- │ category │ manufacturer │ nb_unique_products │ position │ % of the market │
-- │  int64   │    int64     │       int64        │  int64   │     double      │
-- ├──────────┼──────────────┼────────────────────┼──────────┼─────────────────┤
-- │        3 │            2 │                196 │        1 │           18.23 │
-- │        3 │           39 │                185 │        2 │           17.21 │
-- │        3 │           41 │                124 │        3 │           11.53 │
-- │        3 │           57 │                 50 │        7 │            4.65 │< this is me
-- │        4 │           34 │                452 │        1 │           23.12 │
-- │        4 │            7 │                303 │        2 │            15.5 │
-- │        4 │            2 │                278 │        3 │           14.22 │
-- │        4 │           57 │                203 │        5 │           10.38 │< this is me
-- └──────────┴──────────────┴────────────────────┴──────────┴─────────────────┘

这样一来，你就能看到（例如）你在类别 4 中排名第五，并且还能知道是谁占据了最高的份额。我们来拆解一下这个查询：

WHERE: 筛选出类别 3 和 4 的数据。
GROUP BY: 获取每个制造商在每个类别中的独特产品数量。
row_number(): 对每个制造商在所属类别内进行排名。这是在一个窗口内完成的，该窗口按类别进行分区（partitioned by category），并按我们在第 1 步中计算的独特产品数量进行排序（ordered by…）。我们必须按类别分区，这是为了确保两个类别分开计算。因此，每个制造商将会有 2 个排名：一个在类别 3，另一个在类别 4。（注：原文提到类别 2 和 3，根据上下文应为类别 3 和 4）。
SUM() OVER (PARTITION BY category): 计算每个制造商在给定类别中的产品份额。
QUALIFY: 仅返回前 3 名竞争对手和制造商 57。

4) 多层次分销产品分析

假设您想获取您的独特产品数量，并从以下多个维度进行汇总：

按零售商和类别
按零售商（涵盖所有类别）
按类别（涵盖所有零售商）
按所有零售商和所有类别（总计）

您可以独立计算每一项，然后将所有结果进行合并（UNION），但其实有一个更简单的方法。

SELECT
    COALESCE(retailer, 0) AS retailer, -- 将空值转换为可读的 ID
    COALESCE(category, 0) AS category, -- 将空值转换为可读的 ID
    COUNT(DISTINCT p_id) AS nb_unique_products
FROM
    product_details
GROUP BY
    GROUPING SETS (
        -- 按零售商和类别（进行分组） (即，计算每个零售商在每个类别中的总数)
        (category, retailer),
        -- 按类别（涵盖所有零售商） (即，计算每个类别在所有零售商中的总数)
        (category),
        -- 按零售商（涵盖所有类别） (即，计算每个零售商在所有类别中的总数)
        (retailer),
        -- 按所有类别和所有零售商（ (即，计算总计总数)
        ()
    )
ORDER BY
    ALL

-- ┌──────────┬──────────┬────────────────────┐
-- │ retailer │ category │ nb_unique_products │
-- │  int64   │  int64   │       int64        │
-- ├──────────┼──────────┼────────────────────┤
-- │        0 │        0 │              17428 │
-- │        0 │        1 │                136 │
-- │        0 │        2 │               2105 │
-- │        0 │        3 │               1075 │
-- │        0 │        4 │               1955 │
-- │        · │        · │                 ·  │
-- │        · │        · │                 ·  │
-- │        · │        · │                 ·  │
-- │        2 │       11 │                346 │
-- ├──────────┴──────────┴────────────────────┤
-- │ x rows (xx shown)              3 columns │
-- └──────────────────────────────────────────┘

GROUPING SETS 等同于将多个不同的 GROUP BY 查询的结果进行合并（UNION）。

对于未包含在分组集中的维度，结果中将显示 NULL。因此，针对分组集 (category)（仅按类别汇总）产生的结果行，其 retailer 字段的值将被设置为 NULL。

如果你需要使用所有可能的分组集（即所有维度及其组合，包括总计），那么你可以使用 CUBE 关键字来简化语法：

SELECT
    COALESCE(retailer, 0) AS retailer,
    COALESCE(category, 0) AS category,
    COUNT(DISTINCT p_id) AS nb_unique_products
FROM
    product_details
GROUP BY
    -- 这等同于所有可能的分组集:
    -- (category, retailer), (category), (retailer), (category), ()
    CUBE(retailer, category)
ORDER BY
    ALL

ROLLUP 是另一种分组结构，但它与 CUBE 的工作方式不同，因为它会限制可能的分组集。

ROLLUP(x, y) 的定义如下：

(x, y)
(x)
()

(y) 这一分组集会被 CUBE 包含，但不会被 ROLLUP 包含。

当不同维度之间存在层次关系时，ROLLUP 更加有用，例如：

1	SUM(population) GROUP BY ROLLUP(county, region, city) -- (国家, 地区, 城市)

在这种情况下，分组集 (region)（仅按地区汇总）的意义就不大，因为它与 (country, region)（按国家和地区汇总）是等价的，因为一个地区（region）总是隶属于一个国家（county）。

5) 既然我的主战场是类别 3 和 4，我的铺货份额 (SOA)是多少？

在第三问中，我们看到了用来统计类别 3 和 4 中独特的分销产品数量。

但是，拥有更多的独特分销产品数量，与你的铺货份额（Share of Assortment, SOA）并非完全正相关。

想象一下，你拥有 15 种独特的冷冻食品，而你的竞争对手只有 3 种，并且市场上只有你们两家。

如果在某个零售商（比如沃尔玛），他们总是上架竞争对手的 2 种产品和你的 1 种产品。在这种情况下，你的 SOA 只有 1/3 = 33%，而你的竞争对手将占据冷冻食品货架 2/3 = 77% 的份额。尽管你在独特产品数量上拥有巨大的份额 15/18 = 83%，但你在实际货架占比上却被竞争对手超越了。

铺货份额（SOA）可以定义为：

$$\text {SOA} = \frac {\text {SUM}(\text {我的产品的分销天数})}{\text {SUM}(\text {我的产品} + \text {竞争对手产品的分销天数})}$$

（注：这里的“分销天数”（distributed days）是衡量产品实际占有货架空间和时间的关键指标。）

以下是针对类别 3 和 4，按 SOA 排名获取前 3 名竞争对手的查询：

WITH soa_per_category_per_manufacturer AS (
    SELECT
        category,
        manufacturer,
        SUM(distributed_days) AS _total_ddays, -- 每个制造商的总分销天数
        ROUND(
            100 * _total_ddays / SUM(_total_ddays) OVER (PARTITION BY category),
            2
        ) AS soa -- 这正是 SOA 公式
    FROM
        product_details
    WHERE
        category IN (3, 4)
    GROUP BY
        category,
        manufacturer
)
SELECT
    * EXCLUDE(_total_ddays), -- 感谢 DuckDB 的 EXCLUDE :)
    row_number() OVER (
        PARTITION BY category
        ORDER BY
            soa DESC
    ) AS position -- 然后按类别上的 soa 排序
FROM
    soa_per_category_per_manufacturer
QUALIFY
    position <= 3 -- 前 3 名竞争对手
    OR manufacturer = 57 -- 加上我

-- ┌──────────┬──────────────┬────────┬──────────┐
-- │ category │ manufacturer │  soa   │ position │
-- │  int64   │    int64     │ double │  int64   │
-- ├──────────┼──────────────┼────────┼──────────┤
-- │        3 │           39 │  22.12 │        1 │
-- │        3 │            2 │  16.04 │        2 │
-- │        3 │           67 │  14.36 │        3 │
-- │        3 │           57 │   3.95 │        8 │< I am no longer 7th on the market
-- │        4 │           34 │   24.6 │        1 │
-- │        4 │            7 │  17.27 │        2 │
-- │        4 │            2 │   10.9 │        3 │
-- │        4 │           57 │  10.36 │        5 │< Still 5th
-- └──────────┴──────────────┴────────┴──────────┘

该查询使用了公共表表达式（CTEs, Common Table Expressions），这是一种组织查询的方式，可以使其更具可读性。在某些情况下，如果 CTE 被物化并在多个地方使用，还能避免一遍又一遍地重复计算相同的结果（请查阅物化 CTEs）。

6) 时间演变！

现在，假设你想比较你的产品类别在当前选定的周2024_1与前一周2023_52之间的铺货份额（SOA）变化。

WITH soa_per_period_category_per_manufacturer AS (
    SELECT
        period,
        category,
        manufacturer,
        SUM(distributed_days) as _total_ddays,
        ROUND(
            100 * _total_ddays / SUM(_total_ddays) OVER (
                PARTITION BY
                period, -- 注意这里加入了 period :)
                category
            ),
            2
        ) as soa,
    FROM
        product_details
    GROUP BY
        period, -- 这里也是 :)
        category,
        manufacturer),
soa_variation AS (
    SELECT
        * EXCLUDE (_total_ddays),
        -- 注意使用已定义的窗口来简化查询，
        -- 特别是当窗口在多个地方使用时。
        soa - lag(soa, 1, 0) OVER period_window as variation_soa,
    FROM
        soa_per_period_category_per_manufacturer
        -- 定义窗口并为其指定名称
        WINDOW period_window AS (
            PARTITION BY category,
            -- 注意，这里需要制造商（manufacturer），
            -- 因为变化是按制造商和类别计算的
            manufacturer
            ORDER BY
                -- 这非常重要，因为变化是：当前 soa - 之前的 soa。
                -- 窗口中的值必须按此顺序排列，才能使用 soa - lag(soa, 1, 0)。
                -- 另外请注意 '2023_52' < '2024_1'
                period
        )
        -- 在窗口函数计算后，只取本周数据，
        -- 因为变化量是与本周挂钩的。
        QUALIFY period = '2024_1'
    ORDER BY
        period)
SELECT
    -- 感谢 DuckDB 的 EXCLUDE/REPLACE :)
    * EXCLUDE (period) REPLACE (ROUND(variation_soa, 2) as variation_soa),
    row_number() OVER (
        PARTITION BY category
        ORDER BY
            variation_soa DESC
    ) as position -- 然后按类别上的 soa 变化量排序
FROM
    -- 筛选出每个类别中排名第一的制造商
    soa_variation QUALIFY position = 1
ORDER BY
    category
LIMIT
    10;

-- ┌──────────┬──────────────┬────────┬───────────────┬──────────┐
-- │ category │ manufacturer │  soa   │ variation_soa │ position │
-- ├──────────┼──────────────┼────────┼───────────────┼──────────┤
-- │        1 │           39 │  34.19 │          3.11 │        1 │< 39 正在变好
-- │        2 │           39 │  11.68 │          1.37 │        1 │< 跨多个类别
-- │        3 │           39 │   24.8 │          5.64 │        1 │
-- │        4 │            6 │  11.84 │          2.41 │        1 │
-- │        5 │           39 │  40.07 │          6.52 │        1 │
-- │        6 │           39 │  42.08 │         10.42 │        1 │< 这是一个很好的提升
-- │        7 │           34 │   6.29 │          3.65 │        1 │
-- │        8 │            6 │  51.29 │          0.86 │        1 │
-- │        9 │            3 │  52.69 │          9.33 │        1 │
-- │       10 │            3 │  42.24 │          6.81 │        1 │
-- └──────────┴──────────────┴────────┴───────────────┴──────────┘

这个查询获取了在每个类别中 SOA 变化量最大的制造商。请注意，这里使用了多个 CTE 来组织查询。 LAG(n) 是一个窗口函数，它允许你使用窗口内前第 n 行的值。

7) 并非所有零售商都是平等的！

假设你拥有按零售商计算的 SOA，现在你想计算一个平均值，但不是一个简单的普通平均值。因为零售商的重要性（商店数量、区域、合同等）并不相同，你需要计算一个加权平均值（Weighted Average）。

假设你拥有以下权重（权重数据在 retailer_w 表中）：

select * from retailer_w;
-- ┌──────────┬────────┐
-- │ retailer │   w    │
-- ├──────────┼────────┤
-- │       2  │    1.0 │
-- │       1  │    3.0 │< 重要性是零售商 2 的 3 倍
-- └──────────┴────────┘

那么，加权平均 SOA 可定义为：

$$\text{avg_soa} = \frac{\sum (\text{零售商}_{i} \times \text{权重}_{i})}{\sum (\text{权重}_{i})} \quad \text{对于所有} (\text{零售商}_{i}, \text{权重}_{i})$$

WITH soa_per_retailer_per_manufacturer AS (
    SELECT
        retailer,
        manufacturer,
        SUM(distributed_days) as _total_ddays,
        -- 每个制造商的总分销天数
        ROUND(
            100 * _total_ddays / SUM(_total_ddays) OVER (PARTITION BY retailer),
            2
        ) as soa -- 这正是 SOA 公式
    FROM
        product_details
    GROUP BY
        retailer,
        manufacturer)
SELECT
    manufacturer,
    ROUND(
        -- 我们按制造商分组，因此可以直接进行以下计算
        -- 来获取加权平均值
        SUM(soa * w) / SUM(w),
        2
    ) as avg_soa
FROM
    soa_per_retailer_per_manufacturer
    JOIN retailer_w USING(retailer) -- 将 SOA 结果与权重表连接
GROUP BY
    manufacturer
ORDER BY avg_soa DESC
LIMIT 5;

-- ┌──────────────┬─────────┐
-- │ manufacturer │ avg_soa │
-- ├──────────────┼─────────┤
-- │            7 │   22.52 │
-- │           34 │   16.03 │
-- │            2 │    12.8 │
-- │           26 │    7.56 │
-- │           39 │    6.72 │
-- └──────────────┴─────────┘

全文完！希望你阅读愉快 :)