在BigQuery中循环数据-Java 学习之路

我们一直在努力在（标准sql）BigQuery中循环数据而没有成功 .

我不确定它是否是sql的支持功能，我们未解决的问题或我们想要这样做的方式，因为我们想在BigQuery中做到这一点 .

无论如何，假设我们有一个事件表，其中每个事件都由用户ID和日期描述（同一用户ID可能在同一日期有很多事件）

id  STRING
dt  DATE

我们想知道的一件事是有多少不同的用户在给定的时间段内生成了事件 . 这是相当简单的，只是表上的COUNT，其中句点作为WHERE子句中的约束 . 例如，如果我们有四个月的时间：

SELECT
  COUNT(DISTINCT id) AS total
FROM
  `events`
WHERE
  dt BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -4 MONTH)
  AND CURRENT_DATE()

但是，如果我们想要在相同的给定时间段内递归地在其他日子（或几周）使用历史记录，我们的问题就出现了 . 例如，对于昨天，前天等等......直到...例如，3个月前 . 所以这里的变量将是CURRENT_DATE（），它可以追溯到一天或任何一个因素，但间隔保持不变（在我们的例子中，为4个月） . 我们期待这样的事情（一天因素）：

2017-07-14 2017-03-14 1760333
2017-07-13 2017-03-13 1856333
2017-07-12 2017-03-12 2031993
...
2017-04-14 2017-01-14 1999352

这只是同一个表上每天，每周等的循环，然后是在该时间段内发生的不同事件的COUNT . 但我们不能在BigQuery中做'循环' .

我们想到的一种方式是JOIN，然后是GROUP BY间隔的COUNT（利用HAVING子句来模拟从给定日期到4个月的时间段），但这是非常低效的，它只是没有完成考虑表的大小（其中有大约2.54亿条记录，截至今天为173 GB，并且每天都在不断增长） .

我们想到的另一种方法是使用UDF，我们认为我们将一个日期间隔列表提供给函数，然后我们的函数将为返回间隔的每个间隔和该间隔的计数应用朴素查询（用于计数） . 但是...... BigQuery中的UDF不支持访问UDF中的表，因此我们必须将整个表提供给UDF，我们还没有尝试过，但似乎并不合理 .

因此，我们没有任何解决方案可以基本上迭代相同的数据并在BigQuery中对部分数据（如您所见的重叠部分）进行计算，我们唯一的解决方案就是在BigQuery之外（最后是循环功能） .

有没有办法或有人可以想办法在BigQuery中做到这一切？我们的目标是将此作为BigQuery内部的视图提供，以便它不依赖于需要以我们设置的频率（天/周/等等）触发的外部系统 .

2 回答

对你起作用吗？

WITH dates AS(
  SELECT GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 4 MONTH), CURRENT_DATE()) arr_dates
),
data AS(
  SELECT 1 id, '2017-03-14' dt UNION ALL
  SELECT 1 id, '2017-03-14' dt UNION ALL
  SELECT 1, '2017-04-20' UNION ALL
  SELECT 2, '2017-04-20' UNION ALL
  SELECT 3, '2017-03-15' UNION ALL
  SELECT 4, '2017-04-20' UNION ALL
  SELECT 5, '2017-07-14'
)

SELECT
  i_date date,
  DATE_ADD(i_date, INTERVAL 4 MONTH) next_date,
  (SELECT COUNT(DISTINCT id) FROM data WHERE PARSE_DATE("%Y-%m-%d", data.dt) BETWEEN i_date AND DATE_ADD(i_date, INTERVAL 4 MONTH)) total
FROM dates,
UNNEST(arr_dates) i_date
ORDER BY i_date

data 是 events 表的模拟 .

回复于 2024-04-16T17:39:47+08:00

下面是BigQuery Standard SQL的这种技术示例

#standardSQL
SELECT 
  DAY,
  COUNT(CASE WHEN period = 7  THEN id END) AS days_07,
  COUNT(CASE WHEN period = 14 THEN id END) AS days_14,
  COUNT(CASE WHEN period = 30 THEN id END) AS days_30
FROM (
  SELECT
    dates.day AS DAY,
    periods.period AS period,
    id
  FROM yourTable AS activity
  CROSS JOIN (SELECT DAY FROM yourTable GROUP BY DAY) AS dates
  CROSS JOIN (SELECT period FROM (SELECT 7 AS period UNION ALL 
                SELECT 14 AS period UNION ALL SELECT 30 AS period)) AS periods
  WHERE dates.day >= activity.day 
  AND CAST(DATE_DIFF(dates.day, activity.day, DAY) / periods.period AS INT64) = 0
  GROUP BY 1,2,3
)
GROUP BY DAY
-- ORDER BY DAY

您可以使用如下虚拟数据来演示/测试此示例

#standardSQL
WITH data AS (
  SELECT 
    DAY, CAST(10 * RAND() AS INT64) AS id
  FROM UNNEST(GENERATE_DATE_ARRAY('2017-01-01', '2017-07-13')) AS DAY
)
SELECT 
  DAY,
  COUNT(DISTINCT CASE WHEN period = 7  THEN id END) AS days_07,
  COUNT(DISTINCT CASE WHEN period = 14 THEN id END) AS days_14,
  COUNT(DISTINCT CASE WHEN period = 30 THEN id END) AS days_30
FROM (
  SELECT
    dates.day AS DAY,
    periods.period AS period,
    id
  FROM data AS activity
  CROSS JOIN (SELECT DAY FROM data GROUP BY DAY) AS dates
  CROSS JOIN (SELECT period FROM (SELECT 7 AS period UNION ALL 
                SELECT 14 AS period UNION ALL SELECT 30 AS period)) AS periods
  WHERE dates.day >= activity.day 
  AND CAST(DATE_DIFF(dates.day, activity.day, DAY) / periods.period AS INT64) = 0
  GROUP BY 1,2,3
)
GROUP BY DAY
ORDER BY DAY

回复于 2024-04-16T17:39:47+08:00

在BigQuery中循环数据

2 回答

相关问题