Breaking News: Grepper is joining You.com. Read the official announcement!

BigQuery: join 2 tables but only choosing rows based on date column

Sleepy Squirrel answered on June 23, 2022 Popularity 1/10 Helpfulness 1/10

answer BigQuery: join 2 tables but only choosing rows based on date column

BigQuery: join 2 tables but only choosing rows based on date column

Comment

--  ---------------------------------------------------------------------------------
--  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

Popularity 1/10 Helpfulness 1/10 Language sql

Source: stackoverflow.com

Tags: date join rows sql

Link to this answer
Share Copy Link

Contributed on Jun 23 2022

Sleepy Squirrel

0 Answers Avg Quality 2/10

BigQuery: join 2 tables but only choosing rows based on date column

Contents

More Related Answers

BigQuery: join 2 tables but only choosing rows based on date column

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.