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

最近学习了SQL 的新技能：CTE，全称 Common Table Expressions（通用表表达式）。有助于理解 Tableau LOD 表达式，也有助于理解 DAX 中的表函数。当然，都不如 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

而后在主查询中，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 被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

这个过程，如果在 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 喜乐君