mysql - 根据另一个表中的 ID 和日期过滤 bigquery 表

Question

我是大查询的新手，我正在尝试使用两个大查询表获取一些数据。第一个表包含 ID 和购买日期，第二个日期包含所有用户事件以及事件当天的日期列。

我想要做的是，对于第一个表中的所有 ID，从第二个表中获取所有这些 ID 的所有事件，以便事件日期介于购买表中的日期和该日期之前 30 周之间购买表。

WITH
  purchasers AS (
  SELECT
    *
  FROM
    `table1`),

  activity AS(
  SELECT
    *
  FROM
    `table2`
  WHERE
    date BETWEEN (DATE_SUB(purchasers.four_week_back,INTERVAL 30 week))
    AND ((purchasers.four_week_back)) )

SELECT
  *
FROM
  purchasers
JOIN
  activity
USING
  (id)

Table 1- 

Id      product_type_id     created_at      four_week_back
594157      72               2018-09-27     2018-03-01
10532522    72               2018-09-27     2018-03-01
535061      72               2018-09-27     2018-03-01
10740216    72               2018-09-27     2018-03-01
6093925     72               2018-09-27     2018-03-01
1712485     72               2018-09-28     2018-03-02
3381129     72               2018-09-28     2018-03-02
4290069     72               2018-09-28     2018-03-02
647413      72               2018-09-28     2018-03-02
10482204    72               2018-09-28     2018-03-02
6585925     72               2018-09-28     2018-03-02
1409073     72               2018-09-28     2018-03-02
10749573    72               2018-09-28     2018-03-02

Table 2- 
id  date        varA    var2 
594157      2018-02-19  5637    TRUE
594157      2018-02-04  43160   FALSE
594157      2018-01-17  43160   TRUE
10740216    2018-02-06  43160   FALSE
10740216    2018-02-19  43160   TRUE
10740216    2018-02-09  43161   FALSE
10740216    2017-12-27  43161   TRUE
4290069     2018-02-18  43161   FALSE
4290069     2018-01-05  43161   TRUE
4290069     2018-02-07  43161   FALSE
4290069     2018-02-18  43161   TRUE
4290069     2018-02-19  43161   FALSE
4290069     2018-01-27  43162   TRUE

但是我一直无法这样做。所需的输出是对于表 1 中的所有 id，从表中获取日期范围(table1.date 和 table1.date，30 周间隔)之间的事件。

Answer 1

The desired output is that for all the id from the table 1, get the activities from table between the date range (table1.date and table1.date, 30 week interval)

以下适用于 BigQuery 标准 SQL

#standardSQL
SELECT *
FROM `project.dataset.table1` t1
LEFT JOIN `project.dataset.table2` t2
USING (id) 
WHERE date BETWEEN DATE_SUB(created_at, INTERVAL 30 WEEK) AND created_at  

注意:您可以在上面使用 SELECT * 假设没有其他不明确的列而不是 id

下面是供您测试的示例

#standardSQL
WITH `project.dataset.table1` AS (
  SELECT 594157 id, 72 product_type_id, DATE '2018-09-27' created_at, DATE '2018-03-01' four_week_back UNION ALL
  SELECT 10532522, 72, '2018-09-27', '2018-03-01' UNION ALL
  SELECT 535061, 72, '2018-09-27', '2018-03-01' UNION ALL
  SELECT 10740216, 72, '2018-09-27', '2018-03-01' UNION ALL
  SELECT 6093925, 72, '2018-09-27', '2018-03-01' UNION ALL
  SELECT 1712485, 72, '2018-09-28', '2018-03-02' UNION ALL
  SELECT 3381129, 72, '2018-09-28', '2018-03-02' UNION ALL
  SELECT 4290069, 72, '2018-09-28', '2018-03-02' UNION ALL
  SELECT 647413, 72, '2018-09-28', '2018-03-02' UNION ALL
  SELECT 10482204, 72, '2018-09-28', '2018-03-02' UNION ALL
  SELECT 6585925, 72, '2018-09-28', '2018-03-02' UNION ALL
  SELECT 1409073, 72, '2018-09-28', '2018-03-02' UNION ALL
  SELECT 10749573, 72, '2018-09-28', '2018-03-02' 
), `project.dataset.table2` AS (
  SELECT 594157 id, DATE '2018-03-19' day, 5637 varA, TRUE var2 UNION ALL
  SELECT 594157, '2018-03-04', 43160, FALSE UNION ALL
  SELECT 594157, '2018-01-17', 43160, TRUE UNION ALL
  SELECT 10740216, '2018-03-06', 43160, FALSE UNION ALL
  SELECT 10740216, '2018-03-19', 43160, TRUE UNION ALL
  SELECT 10740216, '2018-02-09', 43161, FALSE UNION ALL
  SELECT 10740216, '2017-12-27', 43161, TRUE UNION ALL
  SELECT 4290069, '2018-02-18', 43161, FALSE UNION ALL
  SELECT 4290069, '2018-01-05', 43161, TRUE UNION ALL
  SELECT 4290069, '2018-02-07', 43161, FALSE UNION ALL
  SELECT 4290069, '2018-02-18', 43161, TRUE UNION ALL
  SELECT 4290069, '2018-02-19', 43161, FALSE UNION ALL
  SELECT 4290069, '2018-01-27', 43162, TRUE 
)
SELECT *
FROM `project.dataset.table1` t1
LEFT JOIN `project.dataset.table2` t2
USING (id) 
WHERE day BETWEEN DATE_SUB(created_at, INTERVAL 30 WEEK) AND created_at   

结果

Row id          product_type_id created_at  four_week_back  day         varA    var2     
1   594157      72              2018-09-27  2018-03-01      2018-03-19  5637    true     
2   594157      72              2018-09-27  2018-03-01      2018-03-04  43160   false    
3   10740216    72              2018-09-27  2018-03-01      2018-03-06  43160   false    
4   10740216    72              2018-09-27  2018-03-01      2018-03-19  43160   true