BigQuery:连接2个表，但只根据日期列选择行

Question

我有两张桌子

第一个表table_new_data如下

date    type  data
2022-01 t1    0
2022-03 t2    1
2021-08 t1    1

第二个表table_old_data如下

date    type  data
2021-10 t1    2
2022-04 t2    3
2021-07 t1    4
2021-06 t1    5

我想要一个table_new_data LEFT JOIN table_old_data的sql代码片段，并产生以下结果。

new_date type new_data old_date  old_data
2022-01  t1   0        2021-10   2
2022-03  t2   1        null      null
2021-08  t1   1        2021-07   4

请注意，

• 只使用相同的table_new_data中的每一行加入行，只与table_old_data中具有最接近以前的 date的行连接。例如，对于table_old_data.

中的2021-08 t1 1，我们只想在table_new_data中加入2021-07 t1 4。

date在YYYY.

Answer 1

见下文..。想想边缘案件。

--  ---------------------------------------------------------------------------------
--  create dummy NEW table 
--  ---------------------------------------------------------------------------------
WITH
  table_new_data AS (
  SELECT
    '2022-01' AS date,
    't1' AS type,
    0 AS DATA
  UNION ALL
  SELECT
    '2022-03',
    't2' ,
    1 
  UNION ALL
  SELECT
    '2021-08' ,
    't1' ,
    1  ),
--  ---------------------------------------------------------------------------------
--  create dummy OLD table 
--  ---------------------------------------------------------------------------------
  table_old_data AS (
  SELECT
    '2021-10' AS date,
    't1' AS type,
    2 AS DATA
  UNION ALL
  SELECT
    '2022-04',
    't2',
    3 
  UNION ALL
  SELECT
    '2021-07',
    't1',
    4
  UNION ALL
  SELECT
    '2021-06',
    't1',
    5),
--  ---------------------------------------------------------------------------------
--  create joined tables based on dates from old table being LOWER (may need <=??)
--  create order = ROW_NUMBER() function to see which date is closest from old table
--  make sure to test on edge cases where dates are the same or equal to
--  ---------------------------------------------------------------------------------
  ordered AS (
  SELECT
    nd.date AS new_date,
    nd.type,
    nd.DATA AS new_data,
    od.date AS old_date,
    od.DATA AS old_data,
    ROW_NUMBER() OVER(PARTITION BY nd.type, nd.date ORDER BY nd.date ) AS rn
  FROM
    table_new_data nd
  LEFT JOIN
    table_old_data od
  ON
    nd.type = od.type
    AND od.date < nd.date )
--  ---------------------------------------------------------------------------------
--  final table to reproduce desired output in question
--  ---------------------------------------------------------------------------------
SELECT
  * EXCEPT(rn)
FROM
  ordered
WHERE
  rn = 1

输出：

​

​