最佳实践

通过以下最佳实践，您可以了解开发注重隐私保护且高效的查询的技巧。

隐私权和数据准确性

基于沙盒数据开发查询

最佳实践：仅在生产环境中查询生产数据。

在开发查询期间尽可能使用沙盒数据。使用沙盒数据的作业不会增加因差异检查而滤除查询结果的可能性。此外，由于没有隐私权检查，沙盒查询的运行速度更快，让您可在开发查询期间更快地进行迭代。

如果需要基于实际数据开发查询（例如使用匹配表时），为降低行重叠的几率，请为查询的每次迭代选择不太可能重叠的日期范围及其他参数。最后，针对所需的数据范围运行查询。

深入研究以前的结果

最佳实践：降低邻近查询的结果集重叠的可能性。

请注意，查询结果之间的变化率会影响之后结果因隐私权检查而被忽略的可能性。与邻近的返回结果集近似的结果集可能会被丢弃。

您应修改查询中的关键参数（例如日期范围或广告系列 ID），以降低出现明显重叠的可能性。

不查询当天的数据

最佳实践：不运行结束日期为当天的多个查询。

运行结束日期为当天的多个查询通常会导致行被滤除。在午夜过后不久针对前一天的数据运行查询也会如此。

如无必要，不针对相同的数据进行多次查询

最佳实践：

  • 选择密切相关的开始日期和结束日期。
  • 不应为查询设置重叠的时间范围，应针对无交集的数据集运行查询，然后在 BigQuery 中汇总结果。
  • 使用已保存的结果，而不是重新运行查询。
  • 为您要查询的每个日期范围创建临时表。

广告数据中心会限制针对相同数据进行查询的总次数。因此，您应尽量控制对特定数据的访问次数。

不在同一查询中使用不必要的汇总

最佳实践：

  • 尽可能减少查询中的汇总数量
  • 尽可能重写查询以组合汇总

广告数据中心将可在子查询中使用的跨用户汇总数量限制为 100。因此，总体来讲，我们建议编写的查询使用有针对性的分组键和简单汇总输出更多行，而不是使用宽泛分组键和复杂汇总输出更多列。应避免使用以下模式：

SELECT
  COUNTIF(field_1 = a_1 AND field_2 = b_1) AS cnt_1,
  COUNTIF(field_1 = a_2 AND field_2 = b_2) AS cnt_2
FROM
  table

应使用 GROUP BY 语句重写那些根据同一组字段来统计事件的查询。

SELECT
  field_1,
  field_2,
  COUNT(1) AS cnt
FROM
  table
GROUP BY
  1, 2

可以采用与 BigQuery 中同样的方式汇总结果。

如果查询从数组创建列，然后对其进行汇总，则应重写这些查询，将这些步骤合并。

SELECT
  COUNTIF(a_1) AS cnt_1,
  COUNTIF(a_2) AS cnt_2
FROM
  (SELECT
     1 IN UNNEST(field) AS a_1,
     2 IN UNNEST(field) AS a_2,
   FROM
     table)

上一个查询可重写为：

SELECT f, COUNT(1) FROM table, UNNEST(field) AS f GROUP BY 1

对于在不同汇总中使用不同字段组合的查询，可以重写为多个更有针对性的查询。

SELECT
  COUNTIF(field_1 = a_1) AS cnt_a_1,
  COUNTIF(field_1 = b_1) AS cnt_b_1,
  COUNTIF(field_2 = a_2) AS cnt_a_2,
  COUNTIF(field_2 = b_2) AS cnt_b_2,
FROM table

上一个查询可拆分为：

SELECT
  field_1, COUNT(*) AS cnt
FROM table
GROUP BY 1

和

SELECT
  field_2, COUNT(*) AS cnt
FROM table
GROUP BY 1

您可以将这些结果拆分为单独的查询，通过单个查询创建和联接多个表，或使用 UNION 将其合并（如果架构兼容）。

优化并了解联接

最佳实践：使用 LEFT JOIN（而非 INNER JOIN）将点击次数或转化次数与展示次数相联接。

并非所有展示次数均与点击次数或转化次数相关联。因此，如果您使用 INNER JOIN 将点击次数或转化次数与展示次数相联接，与点击次数或转化次数无关的展示次数会从结果中滤除。

图片中以维恩图展示了多种联接类型

在 BigQuery 中联接部分最终结果

最佳实践：避免在广告数据中心通过查询联接汇总结果。请改为编写 2 个单独的查询，然后在 BigQuery 中联接各自的结果。

系统会从结果中滤除不符合汇总要求的行。因此，如果您通过查询将未充分汇总的行与充分汇总的行相联接，所生成的行将会被滤除。此外，包含多项汇总的查询在广告数据中心的性能不佳。

您可以在 BigQuery 中联接多个汇总查询的结果（来自广告数据中心）。使用常见查询计算出的结果所用的最终架构均相同。

以下查询会获取广告数据中心的各个结果（campaign_data_123 和 campaign_data_456）并在 BigQuery 中联接这些结果：

SELECT t1.campaign_id, t1.city, t1.X, t2.Y
FROM `campaign_data_123` AS t1
FULL JOIN `campaign_data_456` AS t2
USING (campaign_id, city)

使用被滤除的行摘要

最佳实践：在查询中添加被滤除的行摘要。

被滤除的行摘要会统计因隐私权检查而被滤除的数据。系统会对被滤除的行中的数据求和，并将结果添加到一个汇总行中。虽然无法对被滤除的数据进行进一步分析，但可以大致了解从结果中滤除了多少数据。

设想用户 ID 为零的情况

最佳实践：设想结果中用户 ID 为零的情况。

最终用户的 ID 被设为 0 的原因有多种，例如广告个性化功能被停用、监管原因等。因此，多位用户的数据对应的 user_id 为 0。

如果您想了解数据总值（例如总展示次数或总点击次数），则应添加相应事件。不过，这些数据对于发掘有关客户的数据洞见没有帮助，如果您要获得此类数据洞见，应将其滤除。

您可以在查询中添加 WHERE user_id != "0"，以从结果中排除相应数据。

效果

避免重新汇总

最佳实践：避免跨用户进行多层汇总。

如果查询会合并已汇总的结果（例如，如果查询包含多个 GROUP BY 或嵌套汇总），则需要通过更多资源进行处理。

通常情况下，可以对包含多层汇总的查询进行分解，从而提升性能。这样做时，您应尝试使行保留在事件级别或用户级别，然后通过单级汇总进行合并。

应避免使用以下模式：

SELECT SUM(count)
FROM
  (SELECT campaign_id, COUNT(0) AS count FROM ... GROUP BY 1)

应将使用多层汇总的查询重写为使用单层汇总。

(SELECT ... GROUP BY ... )
JOIN USING (...)
(SELECT ... GROUP BY ... )

应分解易于分解的查询。您可以在 BigQuery 中将结果进行联接。

针对 BigQuery 进行优化

一般而言，工作量较少的查询性能更佳。评估查询性能时，所需的工作量取决于以下因素：

如果查询的执行方式不符合服务等级协议，或者由于资源耗尽或超时而出错，请参考以下建议：

  • 使用以前查询的结果，而不是重新计算。例如，每周总值可以通过在 BigQuery 中对 7 个单日汇总查询求和获得。
  • 将查询分解为逻辑子查询（例如将多个联接拆分为多个查询），或以其他方式限制所处理的数据集。您可以在 BigQuery 中将各个作业的结果合并到单个数据集中。这样做可能有助于解决资源耗尽的问题，但可能会降低查询速度。
  • 如果您在 BigQuery 中遇到超出资源上限的错误，请尝试使用临时表将您的查询拆分为多个 BigQuery 查询。
  • 减少在单个查询中引用的表的数量，否则会占用大量内存，且可能会导致查询失败。
  • 重写查询，减少用户表的联接次数。
  • 重写查询，避免将表与它本身联接。

查询顾问

如果您的 SQL 有效，但可能会触发过度过滤，查询顾问会在查询形成过程中显示切实可行的建议，帮助您避免不理想的结果。

触发器包括以下模式：

可以通过以下方式使用查询顾问：