时间戳

首页 > 解决方案 > BigQuery：根据命中和产品范围维度过滤掉命中

google-bigquery - BigQuery：根据命中和产品范围维度过滤掉命中

问题描述

在 BigQuery 中，有如下所述的基于 Google Analytics 的查询，并且可以正常工作。

#standard sql   
SELECT
      Date,
      SUM(totals.visits) AS Sessions,
      SUM(totals.transactions) AS Transactions
    FROM
      `[projectID].[DatasetID].ga_sessions_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20181217'
      AND '20181217'
      AND totals.visits > 0
    GROUP BY
      Date

在此查询中，我需要排除命中内的所有命中...

注意：不是打算在会话级别（如在此屏幕截图中）而是在命中级别应用上述 3 个条件，因为我想从另一个 GA 视图而不是从中获取数据的视图中重现数字已加载到 BigQuery。在这个其他 GA 视图中，上述 3 个条件被设置为视图过滤器。

到目前为止，“最佳”查询是下面的查询（基于下面 Martin Weitzmann 的帖子）。但是，此查询中未过滤数据集（换句话说，条件不起作用）。

SELECT Date, 
-- hits,
SUM(totals.transactions), 
SUM(totals.visits) 

FROM (

(
  SELECT date, totals,
    -- create own hits array
    ARRAY(
      SELECT AS STRUCT 
        hitnumber, 
        page,
        -- create own product array
        ARRAY(
          SELECT AS STRUCT productSku, productQuantity 
          FROM h.product AS p
          WHERE (SELECT COUNT(1)=0 FROM p.customDimensions WHERE index=6 AND value like '63%') 
        ) AS product
      FROM t.hits as h
      WHERE 
        NOT REGEXP_CONTAINS(page.pagePath,r'gebak|cake')
        AND
        (SELECT COUNT(1)=0 FROM h.customDimensions WHERE index=23 AND value like '%editor%')
    ) AS hits
  FROM
    `[projectID].[DatasetID].ga_sessions_*` t
  WHERE 
  _TABLE_SUFFIX BETWEEN '20181217'
  AND '20181217'
  AND totals.visits > 0
  ))
  GROUP BY Date

有谁知道如何实现所需的输出？

提前非常感谢！

注意：由于隐私问题，projectID 和 datasetID 在两个查询中都被屏蔽了。

标签： google-bigquery

解决方案

自有数组方法

您可以通过在原始数据上使用子查询并将其输出反馈给数组函数来创建自己的匹配和产品数组。在这些子查询中，您可以过滤掉您的匹配和产品：

#standardsql
SELECT
  date,
  hits
  --SUM(totals.visits) AS Sessions,
  --SUM(totals.transactions) AS Transactions
FROM
  (
  SELECT 
    date, totals,
    -- create own hits array
    ARRAY(
      SELECT AS STRUCT 
        hitnumber, 
        page,
        -- create own product array
        ARRAY(
          SELECT AS STRUCT productSku, productQuantity 
          FROM h.product AS p
          WHERE (SELECT COUNT(1)=0 FROM p.customDimensions WHERE index=6 AND value like '63%') 
        ) AS product
      FROM t.hits as h
      WHERE 
        NOT REGEXP_CONTAINS(page.pagePath,r'gebak|cake')
        AND
        (SELECT COUNT(1)=0 FROM h.customDimensions WHERE index=23 AND value like '%editor%')
    ) AS hits
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_20161104` t
  )
--GROUP BY 1
LIMIT 100

我将此示例置于未分组状态，但您可以通过相应地注释掉hits和组来轻松调整它...

细分方法

我认为您只需要在语句中使用正确的子查询WHERE：

#standardsql
SELECT
  date,
  SUM(totals.visits) AS Sessions,
  SUM(totals.transactions) AS Transactions
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*` t
WHERE
  (SELECT COUNT(1)=0 FROM t.hits h
    WHERE 
      (SELECT count(1)>0 FROM h.customDimensions WHERE index=23 AND value like '%editor%')
      OR
      (SELECT count(1)>0 from h.product p, p.customdimensions cd WHERE index=6 AND value like '63%')
      OR
      REGEXP_CONTAINS(page.pagePath,r'gebak|cake')
  )
GROUP BY date

由于您的所有组都在会话级别，因此您不需要在主表上进行任何展平（分别与数组交叉连接），这很昂贵。在最外层WHERE，您输入hits带有子查询的数组 - 这就像对行的 for-each 一样。在这里，您已经可以数数REGEXP_CONTAINS(page.pagePath,r'gebak|cake')。

对于其他情况，您再次编写子查询以输入相应的数组 - 在第一种情况下，customDimensions在hits. 这就像一个嵌套在另一个中的 for-each（子查询中的子查询）。

在第二种情况下，我只是扁平化 - 但仅在子查询中：product使用它的customDimensions. 所以这也是一次嵌套的 for-each，因为我很懒惰并且交叉加入。我本可以编写另一个子查询而不是交叉连接，所以基本上是一个三重嵌套的 for-each（子查询中的子查询）。

由于我正在计算要排除的情况，因此我的外部条件是COUNT(1)=0.

我只能用 ga 样本数据对其进行测试......所以它有点未经测试。但我想你明白了。

推荐阅读