What is the bigquery sql inner join equivalent for rows?-CodePudding

I have the following 2 tables:

I would like to create a table where

all rows of table 1 are included
if the timestamp of any row of table 2 falls in between the timestamp and endTime of any row of table 1, then include the row.

The resultant table would like:

There are columns/fields that are common to both tables, but I haven't included them for brevity. Basically, I am looking for the equivalent of an inner join operation but then instead of adding the rows of table 2 as columns, add them as rows. I have written a sample code whilst experimenting with inner join as below:

WITH table_a AS (
  SELECT 'x' AS event, 1 AS timestamp, 5 AS endtime, 'a' AS field1
  UNION ALL SELECT 'x', 100, 200, 'b'
),
table_b AS (
  SELECT 'y' AS event, 2 AS timestamp, 'm' AS field2
  UNION ALL SELECT 'y', 25, 'n'
  UNION ALL SELECT 'y', 150, 'o'
)

SELECT
  table_a.*,
  table_b.*
FROM table_a JOIN table_b

Any thoughts what bigquery sql functions I can use?

CodePudding user response：

Use below

select *, null field2 from table_a  union all
select distinct b.event, b.timestamp, null, cast(null as string), field2 
from table_b b
join table_a a 
on b.timestamp between a.timestamp and a.endtime

if applied to sample data in your question - output is