BigQuery优化查询咨询：合并products与product_properties表数据

可以通过PIVOT操作将product_properties中的键值对数据转成结构化列，再和products表的初始记录合并，相比两次自连接的方式更简洁高效，减少表扫描次数。

具体SQL实现 #

WITH pivoted_properties AS (
  SELECT
    product_id,
    material,
    material_color
  FROM
    `your-project.your-dataset.product_properties`
  PIVOT (
    MAX(field_value) FOR field_name IN ('material' AS material, 'material_color' AS material_color)
  )
)
SELECT product_id, material, material_color FROM `your-project.your-dataset.products`
UNION ALL
SELECT product_id, material, material_color FROM pivoted_properties
ORDER BY product_id;

方案说明 #

• PIVOT操作会自动以product_id和sequence作为分组键，将field_name对应的field_value转成对应列，自动匹配同sequence下的material和material_color。
• 相比两次自连接，该方法仅需扫描product_properties表一次，避免了重复扫描和连接操作的性能开销，代码结构也更清晰，便于后续维护。

内容的提问来源于stack exchange，提问作者maxime.b28