使用 SQL CTE表表达式理解 Tableau LOD 表达式

---

最近学习了SQL 的新技能：CTE，全称 Common Table Expressions（通用表表达式）。有助于理解 Tableau LOD 表达式，也有助于理解 DAX 中的表函数。当然，都不如 tableau LOD 优雅， 哈哈

CTE 是一个临时表、虚拟表、中间表，有助于简化 SQL 过程，但没有改变 SQL 本质。

参考文章：What Is a Common Table Expression (CTE) in SQL? by Ignacio L. Bisso19th Jan 2022 

1、简单的 CTE 案例：嵌套查询

举例如下：

WITH tickets AS (
  SELECT distinct
    branch,
    date,
    unit_price * quantity AS ticket_amount
  FROM sales
)
SELECT
  branch,
  date,
  ticket_amount
FROM tickets
WHERE ticket_amount > 100

上述开头定义了一个临时表 Tickets，对应一个查询过程：从 sales 中去重查询 branch、date，并将每一行的单价和数量相乘。

而后在主查询中，select 再从上面的临时表中查询金额大于100的部分。

2、高级的 CTE 查询：替代聚合子查询

我们再看一个更高级一点的案例，在查询中包含子查询。

WITH highest AS (
  SELECT
    branch,
    date,
    MAX(unit_price) AS highest_price
  FROM sales
  GROUP BY branch, date
)
SELECT
  sales.*,
  h.highest_price
FROM sales
JOIN highest h
  ON sales.branch = h.branch
    AND sales.date = h.date

在上面的 highest 查询中，使用了 group by 分组和 max 聚合，从而获得“每个branch、date 对应的最高单价”。

而后在主程序查询中，上述子查询 highest 被join 到 sales——这个sales 同时也是 highest 的来源。也就是说，这个查询是“将底表的聚合表和底表相连”，从而获得每个 Branch、date 对应的最高单价。

这个过程，可以合并为一个聚合子查询，而在 tableau 中，只需要一个 FIXED LOD，如下：

highest_price = { FIXED branch, date : MAX( unit price ) }

我们把 LOD 称之为预先聚合，就是这样的逻辑。

3、高级 CET 和查询组合做聚合的聚合：对应 LOD 的聚合

再看一个例子，如下：

WITH daily_revenue AS (
  SELECT
    branch,
    date,
    SUM(unit_price * quantity) AS daily_revenue
  FROM   sales
  WHERE EXTRACT(YEAR FROM date) = 2021
  GROUP BY 1,2
)
SELECT
  branch,
  MAX(daily_revenue) max_daily_revenue
FROM daily_revenue
GROUP BY 1
ORDER BY 2 DESC

这个查询首先用 CTE 完成一个表查询，而后在主查询中被用于再聚合，这样从 sales 底表就连续完成了两次查询聚合。从最终 max_daily_revenue的角度看，就是来自于 sales 的聚合的聚合——二次聚合。

这个过程，如果在 tableau 中用 LOD 实现，就是：

MAX ( { FIXED branch, date : SUM( unit_price * quantity )} )

需要特别说明的是，LOD 表达式只有在特定环境中才有意义，这里的 MAX 基于问题的详细级别（branch），而 LOD 内嵌的 SUM 基于 FIXED 指定的详细级别（Branch+date）。所以说，LOD 是预先聚合，而聚合的二次聚合是在视图中完成，这里并没有再嵌套一个 LOD。

类似的案例是 各省份的客户购买力分析，详细说明参见下面博客：

• Tableau嵌套LOD：客户购买力分析的多遍聚合 2022/5 摘自《数据可视化分析》第二版

---

2024-07-24 喜乐君