了解对称汇总

Looker 中的对称汇总是一项非常强大的功能。不过，由于对称汇总看起来有点吓人，并且大多在后台进行，因此遇到对称汇总时可能会感到困惑。本页介绍了有关对称汇总的以下信息：

• 为何需要对称汇总
• 对称汇总的工作原理
• 对称汇总为什么看起来很复杂

为什么需要对称汇总

SQL 是数据分析的语言，功能非常强大。但是，能力越大，责任也就越大，分析师有责任避免意外计算出不正确的汇总值（例如总和、平均值和计数）。

令人惊讶的是，很容易出错地执行这些计算，而这类计算错误可能会让分析师感到非常沮丧。以下示例说明了可能会出现的问题。

假设您有两个表，即 orders 和 order_items。order_items 表会为订单中的每项记录一行，因此这两个表之间的关系是一对多关系。这种关系是一对多关系，因为一个订单可以包含多个商品，但每个商品只能属于一个订单。如需有关确定正确联接关系的指导，请参阅正确设置关系参数最佳实践页面。

在此 orders 表中，total 列 (SUM(total)) 中的值的总和等于 124.84。

假设 order_items 表包含六行：

获取订购商品数量非常简单。quantity 列 (SUM(quantity)) 中的值总和为 7。

现在，假设您使用 orders 表和 order_items 表的共享列 order_id 进行联接。这会生成以下表格：

上表提供了新信息，例如 12 月 1 日订购了 2 件商品（order_date 列中的 2017-12-01），12 月 2 日订购了 4 件商品（2017-12-02）。之前的一些计算（例如 SUM(quantity) 计算）仍然有效。不过，如果您尝试计算总支出，就会遇到问题。

如果您使用之前的计算方式 SUM(total)，则新表中 order_id 值为 1 的行对应的总值 50.36 将被统计两次，因为该订单包含两个不同的商品（item_id 值分别为 50 和 63）。order_id 值为 2 的行对应的 24.12 总和将被统计三次，因为该订单包含三个不同的商品。因此，此表的计算结果 SUM(total) 为 223.44，而不是正确答案 124.84。

虽然在处理两个小示例表时很容易避免此类错误，但在现实生活中，由于表格和数据很多，解决此问题会更加复杂。这正是用户可能会犯的错误，而且可能根本没有意识到。对称汇总就是用来解决此问题的。

对称汇总的运作方式

对称汇总可防止分析师和任何其他使用 Looker 的用户意外错误计算总和、平均值和计数等汇总值。对称汇总有助于减轻分析师的负担，因为分析师可以信任用户不会使用不正确的数据。对称汇总通过确保正确统计计算中的每个事实的次数，以及跟踪您正在计算的内容，来实现这一点。

在前面的示例中，对称汇总函数会识别出 total 是 orders（而非 order_items）的属性，因此它只需统计每个订单的总金额一次，即可得出正确答案。该函数通过使用分析师在 Looker 中定义的唯一主键来实现此目的。这意味着，当 Looker 对联接表进行计算时，它会发现，即使有两行 order_id 的值为 1，也不应将总计数算两次，因为该总计数已包含在计算中，并且对于 order_id 的值为 2 的三行，也应只将总计数算一次。

值得注意的是，对称汇总取决于模型中指定的唯一 主键和正确的联接关系。因此，如果您获得的结果看起来不正确，请与分析师联系，确保所有设置均正确无误。

为什么对称汇总看起来很复杂

对称汇总的显示方式可能有点神秘。如果不使用对称汇总，Looker 通常会编写良好的 SQL，例如以下示例所示：

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price"
FROM order_items AS order_items

GROUP BY 1,2
ORDER BY 1
LIMIT 500

使用对称汇总时，SQL Looker 写入可能如下所示：

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price",
  (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(users.age ,0)
  *(1000000*1.0)) AS DECIMAL(38,0))) +
  CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0)) )
  - SUM(DISTINCT CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))) ) AS DOUBLE PRECISION)
  / CAST((1000000*1.0) AS DOUBLE PRECISION), 0)
  / NULLIF(COUNT(DISTINCT CASE WHEN users.age IS NOT NULL THEN users.id
  ELSE NULL END), 0)) AS "users.average_age
FROM order_items AS order_items
LEFT JOIN users AS users ON order_items.user_id = users.id

GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

对称汇总采用的确切格式取决于 Looker 正在编写的 SQL 方言，但所有格式都执行相同的基本操作：如果多行具有相同的主键，对称汇总函数只会统计一次。它通过使用 SQL 标准中鲜为人知的 SUM DISTINCT 和 AVG DISTINCT 函数来实现这一点。

如需了解具体原因，您可以使用对称汇总来完成之前的计算。在联接表中的七个列中，您只需要两个：要汇总的列 (total) 和订单的唯一主键 (order_id)。

对称汇总会采用主键（在本例中为 order_id），并为每个主键创建一个非常大的数字，该数字保证是唯一的，并且对于相同的输入，始终会给出相同的输出。（通常，它会使用哈希函数来实现此目的，但具体细节超出了本页的讨论范围。）结果应如下所示：

然后，对于每行，Looker 会执行以下操作：

SUM(DISTINCT big_unique_number + total) - SUM(DISTINCT big_unique_number)

这样，您就可以获得准确的汇总总数，每个总数的统计次数完全正确。Looker 对称汇总函数不会被重复行或总金额相同的多个订单所迷惑。您可以尝试自行计算，以便更好地了解对称汇总的运作方式。

执行此操作所需的 SQL 看起来并不美观：对于 CAST()、md5()、SUM(DISTINCT) 和 STRTOL()，您肯定不想手动编写 SQL。不过，幸运的是，您无需这样做，Looker 可以为您编写 SQL。

如果某个汇总无需使用对称汇总即可正常运行，Looker 会自动检测到这一点，并不会使用该函数。由于对称汇总会带来一些性能开销，因此 Looker 能够区分何时使用对称汇总，何时不使用对称汇总，这进一步优化了 Looker 生成的 SQL，使其尽可能高效，同时仍能保证正确的答案。