如何合并BigQuery中的两个SQL查询以省去多个视图的使用

合并后的SQL实现 #

方案1：与原有逻辑完全对齐的CTE写法

直接将生成id的逻辑封装为CTE替换原查询中的表即可，逻辑和你现有两个视图的计算规则完全一致：

WITH add_id_table AS (
  SELECT 
    *,  
    CONCAT(
      CAST(ga_sourcemedium AS string),   
      CAST(ga_campaign AS string),   
      CAST(ga_adcontent AS string),   
      CAST(ga_country AS string),   
      CAST(ga_region AS string), 
      CAST(ga_devicecategory AS string),   
      CAST(ga_date AS string)
    ) AS id
  FROM `table_name`
)
SELECT DISTINCT orders.*
FROM add_id_table orders
INNER JOIN (
  SELECT 
    id,
    MAX(_sdc_sequence) AS sequence
  FROM add_id_table
  GROUP BY id
) latest_orders
ON orders.id = latest_orders.id
AND orders._sdc_sequence = latest_orders.sequence

方案2：更简洁的窗口函数写法（BigQuery原生支持，性能更优）

用ROW_NUMBER窗口函数配合QUALIFY语法可以直接省略JOIN步骤，代码更简洁执行效率更高：

SELECT * EXCEPT(rn)
FROM (
  SELECT 
    *,  
    CONCAT(
      CAST(ga_sourcemedium AS string),   
      CAST(ga_campaign AS string),   
      CAST(ga_adcontent AS string),   
      CAST(ga_country AS string),   
      CAST(ga_region AS string), 
      CAST(ga_devicecategory AS string),   
      CAST(ga_date AS string)
    ) AS id,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY _sdc_sequence DESC) AS rn
  FROM `table_name`
)
WHERE rn = 1

如果是BigQuery最新版本还可以进一步简化为：

SELECT 
  *,  
  CONCAT(
    CAST(ga_sourcemedium AS string),   
    CAST(ga_campaign AS string),   
    CAST(ga_adcontent AS string),   
    CAST(ga_country AS string),   
    CAST(ga_region AS string), 
    CAST(ga_devicecategory AS string),   
    CAST(ga_date AS string)
  ) AS id
FROM `table_name`
QUALIFY ROW_NUMBER() OVER(PARTITION BY id ORDER BY _sdc_sequence DESC) = 1

注意事项 #

原生CONCAT函数只要拼接的任意一个字段为NULL，最终返回的id就会为NULL，如果你有字段可能为空的场景，建议替换为CONCAT_WS函数，用指定分隔符拼接同时自动跳过NULL值，示例：

CONCAT_WS('|', ga_sourcemedium, ga_campaign, ga_adcontent, ga_country, ga_region, ga_devicecategory, ga_date) AS id

不需要额外加CAST，CONCAT_WS会自动将字段转为字符串类型。

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