Looker 中的对称聚合是一项非常强大的功能。但是,由于对称聚合看起来有点令人望而却步,并且大多发生在后台,因此遇到它们可能会让人感到有点困惑。本页面提供了有关对称聚合的以下信息:
为什么需要对称聚合
SQL 是一种数据分析语言,它的功能极其强大。但能力越大,责任也越大,分析师有责任避免意外计算错误的汇总,例如总和、平均值和计数。
这些计算不正确执行起来轻而易举,这些类型的计算不正确可能会给分析人员带来极大困扰。以下示例说明了出错的原因。
假设您有两个表:orders
和 order_items
。order_items
表会按顺序为每件商品记录一行,因此表之间的关系是一对多的。这种关系是一对多的,因为一个订单可以包含多个商品,但每件商品只能属于一个订单。如需获得有关如何确定正确联接关系的指导,请参阅正确处理关系参数页面。
orders
表如下所示:
order_id |
user_id |
total |
order_date |
---|---|---|---|
1 | 100 | $ 50.36 | 2017-12-01 |
2 | 101 | 24.12 美元 | 2017-12-02 |
3 | 137 | $ 50.36 | 2017-12-02 |
在此 orders
表中,total
列 (SUM(total)
) 中的值总和等于 124.84
。
假设 order_items
表包含六行:
order_id |
item_id |
quantity |
unit_price |
---|---|---|---|
1 | 50 | 1 | 23.00 美元 |
1 | 63 | 2 | 13.68 |
2 | 63 | 1 | 13.68 |
2 | 72 | 1 | $ 5.08 |
2 | 79 | 1 | 5.36 美元 |
3 | 78 | 1 | $ 50.36 |
获取已订商品的数量很简单。quantity
列中的值总和 (SUM(quantity)
) 为 7
。
现在,假设您使用共享列 order_id
联接 orders
表和 order_items
表。生成下表:
order_id |
user_id |
total |
order_date |
item_id |
quantity |
unit_price |
---|---|---|---|---|---|---|
1 | 100 | $ 50.36 | 2017-12-01 | 50 | 1 | 23.00 美元 |
1 | 100 | $ 50.36 | 2017-12-01 | 63 | 2 | 13.68 |
2 | 101 | 24.12 美元 | 2017-12-02 | 63 | 1 | 13.68 |
2 | 101 | 24.12 美元 | 2017-12-02 | 72 | 1 | $ 5.08 |
2 | 101 | 24.12 美元 | 2017-12-02 | 79 | 1 | 5.36 美元 |
3 | 137 | $ 50.36 | 2017-12-02 | 78 | 1 | $ 50.36 |
上表提供了新信息,例如两件商品在 12 月 1 日订购了(order_date
列中的 2017-12-01
),四件商品在 12 月 2 日(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 |
total |
---|---|
1 | $ 50.36 |
1 | $ 50.36 |
2 | 24.12 美元 |
2 | 24.12 美元 |
2 | 24.12 美元 |
3 | 50.26 |
对称聚合会接受主键(在本例中为 order_id
),并为每个键创建一个非常大的数字,确保该数字具有唯一性,并且始终针对同一输入提供相同的输出。(通常使用哈希函数执行此操作,其详细信息不在本页面的讨论范围内)。结果将如下所示:
big_unique_number |
total |
---|---|
802959190063912 | $ 50.36 |
802959190063912 | $ 50.36 |
917651724816292 | 24.12 美元 |
917651724816292 | 24.12 美元 |
917651724816292 | 24.12 美元 |
110506994770727 | $ 50.36 |
然后,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,使其尽可能高效,同时仍然保证正确答案。