如何实现无重复行的双查询关联并适配Looker Studio动态工人筛选

需求说明 #

需要在同一表格中展示两类数据：

• 按门店+工人资质聚合的全局统计字段（总支付、总报价等）
• 指定工人在对应门店的明细数据（无工作记录则明细字段为null）
  同时要支持Looker Studio动态选择工人，自动适配工人姓名变更或离职的情况。

修改后的BigQuery查询 #

WITH agg AS (
  SELECT *
  FROM quotes
  INNER JOIN budgets USING(id, store, month)
  INNER JOIN sales USING(id, store, month)
  INNER JOIN payments USING(id, store, month)
  INNER JOIN specs USING(id)
),
t2 AS (
  -- 按门店+工人资质计算全局聚合统计
  SELECT 
    worker_spec,
    store,
    SUM(payment) AS all_payments,
    SUM(quotes) AS all_quotes,
    SUM(sales) AS all_sales,
    SUM(total_budget) AS all_total_budget
  FROM agg
  GROUP BY worker_spec, store
),
all_workers AS (
  -- 获取所有唯一工人姓名，用于参数自动填充
  SELECT DISTINCT worker_name
  FROM agg
)
-- 关联全局聚合与工人明细，确保每个门店-资质-工人组合仅一行
SELECT
  t2.store AS `门店（store）`,
  t2.worker_spec AS `工人资质（worker_spec）`,
  t2.all_payments AS `总支付（all_payments）`,
  t2.all_quotes AS `总报价（all_quotes）`,
  t2.all_sales AS `总销售额（all_sales）`,
  t2.all_total_budget AS `总预算（all_total_budget）`,
  t1.worker_name AS `工人姓名（worker_name）`,
  t1.payment AS `支付额（payment）`,
  t1.quotes AS `报价（quotes）`,
  t1.sales AS `销售额（sales）`,
  t1.total_budget AS `预算（total_budget）`
FROM t2
CROSS JOIN all_workers
LEFT JOIN agg t1 
  ON t2.store = t1.store 
  AND t2.worker_spec = t1.worker_spec
  AND t1.worker_name = all_workers.worker_name

Looker Studio配置步骤 #

1. 创建工人参数：在数据源中新建下拉参数，关联查询结果中的工人姓名（worker_name）字段，设置为单选模式。参数会自动同步数据源中的所有工人姓名，工人变动时无需手动更新。
2. 添加过滤器：在报表中添加过滤器，设置条件为工人姓名（worker_name）等于上述参数值。
3. 构建可视化表格：将查询中的字段拖入表格组件，即可实现选择工人后，自动展示对应各门店的明细+全局聚合数据，无记录的门店明细字段自动显示null。

方案优势 #

• 避免了硬编码工人姓名的问题，完全支持动态选择。
• 通过CROSS JOIN+LEFT JOIN的逻辑，确保每个门店-资质-工人组合仅返回一行数据，解决了之前关联导致的重复行问题。
• all_workers CTE自动获取所有工人，参数选项随数据源实时更新，适配人员变动场景。

内容的提问来源于stack exchange，提问作者xerac