sql - 选择命中序列的查询消耗大量数据

Question

我正在尝试通过网站上的替代 channel 来衡量转化率。我的查询旨在输出查看相关起始 URL 的 session 计数和严格按该顺序点击确认页面的 session 计数。它通过比较点击次数来做到这一点。

我的查询似乎返回了准确的数字，但这样做会选择大量数据，对于我试图限制为一天一小时的数据，不到 23GB。我似乎没有以一种特别有效的方式编写我的查询，并且如果我继续使用它，我会很快用完我公司的所有数据配额。

这是完整的有问题的查询:

WITH 
s1 AS (
  SELECT
      fullVisitorId,
      visitId,    
      LOWER(h.page.pagePath),
      device.deviceCategory AS platform,
      MIN(h.time) AS s1_time
  FROM
    `project.dataset.ga_sessions_*`, UNNEST(hits) AS h 
  WHERE
    _TABLE_SUFFIX BETWEEN '20170107' AND '20170107'
  AND
    LOWER(h.page.pagePath) LIKE '{funnel-start-url-1}%' OR LOWER(h.page.pagePath) LIKE '{funnel-start-url-2}%'
  AND
    totals.visits = 1
  AND
    h.hour  < 21
  AND
    h.hour >= 20      
  AND
    h.type = "PAGE"
  GROUP BY
    path,
    platform,
    fullVisitorId,
    visitId
  ORDER BY
    fullVisitorId ASC, visitId ASC
),

confirmations AS (
  SELECT
      fullVisitorId,
      visitId,
      MIN(h.time) AS confirmation_time
  FROM
    `project.dataset.ga_sessions_*`, UNNEST(hits) AS h 
  WHERE
    _TABLE_SUFFIX BETWEEN '20170107' AND '20170107'
  AND
    h.type = "PAGE"      
  AND
    LOWER(h.page.pagePath) LIKE '{confirmation-url-1}%' OR LOWER(h.page.pagePath) LIKE '{confirmations-url-2}%'
  AND
    totals.visits = 1
  AND
    h.hour  < 21
  AND
    h.hour >= 20
  GROUP BY
    fullVisitorId,
    visitId
)

SELECT
  platform,
  path,
  COUNT(path) AS Views,
  SUM(
    CASE 
      WHEN s1.s1_time < confirmations.confirmation_time 
      THEN 1 
      ELSE 0 
    END
  ) AS SubsequentPurchases
FROM
  s1
LEFT JOIN
  confirmations
ON
  s1.fullVisitorId = confirmations.fullVisitorId
AND
  s1.visitId = confirmations.visitId
GROUP BY
  platform,
  path

这个查询是什么意味着它必须处理这么多数据？有没有更好的方法来获得这些数字。理想情况下，任何方法都应该能够测量多条不同的路线，但此时我会满足于可持续性。

Answer 1

可能有几种方法可以优化您的查询，但似乎不能完全解决您的问题(我将进一步尝试解释)。

至于查询，这个也是一样的，但是避免了重新选择数据和LEFT JOIN。手术:

SELECT
    path,
    platform,
    COUNT(path) views,
    COUNT(CASE WHEN last_hn > first_hn THEN 1 END) SubsequentPurchases
from(
SELECT
    fv,
    v,
    platform,
    path,
    first_hn,
    MAX(last_hn) OVER(PARTITION BY fv, v) last_hn
from(
SELECT 
    fullvisitorid fv,
    visitid v,
    device.devicecategory platform,
    LOWER(hits.page.pagepath) path,
    MIN(CASE WHEN REGEXP_CONTAINS(hits.page.pagepath, r'/catalog/|product') THEN hits.hitnumber ELSE null END) first_hn,
    MAX(CASE WHEN REGEXP_CONTAINS(hits.page.pagepath, r'success') then hits.hitnumber ELSE null END) last_hn
FROM `project_id.data_set.ga_sessions_20170112`,
UNNEST(hits) hits
WHERE 
    REGEXP_CONTAINS(hits.page.pagepath, r'/catalog/|product|success')
    AND totals.visits = 1
    AND hits.type = 'PAGE'
GROUP BY 
    fv, v, path, platform
)
)
GROUP BY
    path, platform
HAVING NOT REGEXP_CONTAINS(path, r'success')

first_hn追踪 funnel-start-url (其中我使用了术语“目录”和“产品”)和 last_hn跟踪确认 URL(我使用了“成功”一词，但可以在 regex 选择器中添加更多值)。此外，通过使用 MIN和 MAX操作和 analytical functions您可以在查询中进行一些优化。

不过这里有几点需要说明:

如果插入 WHERE hits.hithour = 20 ，BigQuery 仍然需要扫描整个表才能找到 20 和非 20。这意味着您观察到的 23Gbs 仍然占一整天。

为了比较，我针对我们的 ga_sessions 测试了您的查询，大约花了 31 天达到 23Gb 的数据。由于您没有选择那么多字段，因此达到这个数量应该不是那么容易，除非您的数据源有相当高的流量。

给定电流 pricing对于 BigQuery，处理 23Gbs 大约需要 0.11 美元，这非常划算。

我可以想象的另一件事是，您每天运行此查询数次并且没有 cache或一些适合这些操作的架构。

话虽如此，您可以优化您的查询，但我怀疑它最终不会有太大变化，因为您似乎拥有大量数据。处理 23Gbs 几次应该不是问题，但如果您担心它会达到您的配额，那么您似乎每天要运行几次此查询。

既然如此，看看有没有使用一些 cache标记或将结果保存到另一个表中，然后查询它会有所帮助。此外，您可以开始只保存您感兴趣的 session (具有您正在寻找的 URL 模式)的每日表格，然后在这些新创建的表格中运行最终查询，这将允许您在更大的天数范围内进行查询为此花费更少。