作为 SQL 中的一般规则(乃至 Looker),您不能按聚合函数(在 Looker 中表示为测量)的结果对查询进行分组。 您只能按未汇总的字段(在 Looker 中表示为维度)进行分组。 如果您尝试在 Looker 中汇总某项测量,则会看到以下错误:
Measures with Looker aggregations (sum, average, min, max, list types) may not reference other measures.
如果您需要一种方法来将测量值(COUNT、SUM、AVG、MAX 等)更改为维度,以便按该维度进行分组以对其进行聚合(例如 COUNT 的总和或 SUM 的 AVG),在“探索”中按该测量进行过滤(在 WHERE 子句中,而不是在 HAVING 子句中)或对它的 pivot,该怎么办?
使用派生表指定测量的维度
在 Looker 内部,该解决方案称为维度化。这是因为您将测量重新定义为一个 维度。这是通过创建一个派生表(其中包含您要在其 SQL 定义中维度化的测量)而实现的。
流程
以下示例基于一个示例电子商务数据集。本示例的目标是在现有总收入 type: sum
衡量的基础上创建 type: average
衡量。
以下步骤概述了如何生成基于 SQL 的派生表。您可以选择创建基于 LookML 的派生表(也称为原生派生表 (NDT))作为 SQL 的替代方案。
- 首先,设置探索查询。 选择相应的字段,包括要维度化的测量。 在示例用例中,“探索数据”表格包含按用户状态和用户 ID 分组的总收入:
- 从数据表的 SQL 标签页中选择在 SQL Runner 中打开,以在 SQL Runner 中打开查询:
-
在 SQL Runner 中运行查询(通过点击运行按钮)并确认结果后,从 SQL Runner 齿轮菜单中选择添加到项目 (Add to Project) 选项,以打开添加到项目 (Add to Project) 弹出式窗口。此时,您需要移除派生表 SQL 中的任何行数上限子句,以确保查询中包含所有需要的结果。
您还可以从菜单中选择 Get Derived Table LookML,以手动将生成的 LookML 复制并粘贴到项目中。
- 从 Add to Project 弹出式窗口中,从 Project 下拉列表中选择项目名称,为派生表视图文件输入名称,然后选择 Add。
-
现在,派生表位于视图文件中,接下来您可以创建汇总维度测量的测量。例如,您现在可以为新的总收入维度
order_items_total_revenue
创建type: average
衡量。dimension: order_items_total_revenue { type: number sql: ${TABLE}.order_items.total_revenue ;; value_format_name: usd } measure: average_revenue { type: average sql: ${order_items_total_revenue} ;; value_format_name: usd }
- 仔细检查派生表中是否定义了主键。
- 加入新视图并加入原始探索(或创建新的探索),以便使用新字段创建查询和内容。
总结
使用 Looker 派生的表对衡量指标进行维度化分析可释放新功能潜能,并让您能够通过数据深入分析。通过按维度化测量分组 ,在 WHERE 子句(而非 HAVING)中按该测量值进行过滤、对其进行透视以及基于该测量创建其他维度,您就可以将探索查询和内容转到下一个维度。
如需详细了解如何创建和使用派生表,以及注意事项和效果优化提示,请访问派生表文档页面。