BigQuery:根据最接近的时间戳加入 [英] BigQuery: Join based on closest timestamp
本文介绍了BigQuery:根据最接近的时间戳加入的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在基于BigQuery中最接近的时间戳联接两个表,并收到此错误.连接谓词中不支持带有表的子查询.
I am joining two tables based on closest timestamp in BigQuery and getting this error. Unsupported subquery with table in join predicate.
SELECT gs.user_session_id
,dtc._date
,dtc.hit_timestamp _timestamp
,dtc.user_id
FROM ga.2_deduped_twice_click_data dtc LEFT JOIN ga.sessions gs ON dtc.user_id = gs.user_id
and dtc.hit_timestamp = ( SELECT dtc2.hit_timestamp FROM ga.2_deduped_twice_click_data dtc2 order by ABS(TIMESTAMP_DIFF(dtc.hit_timestamp, gs._timestamp, MILLISECOND)) LIMIT 1 )
推荐答案
您可以尝试混合使用ARRAY_AGG
和ORDER BY diff LIMIT 1
:
You could try a mix of ARRAY_AGG
and ORDER BY diff LIMIT 1
:
WITH a AS (
SELECT * FROM UNNEST(
[STRUCT(TIMESTAMP('2018-01-02 20:01:00') AS time, 'monkey' AS animal)
,STRUCT('2018-03-04 10:10:10', 'lion')
,STRUCT('2018-07-04 10:10:10', 'donkey')
])
),
b AS (
SELECT * FROM UNNEST(
[STRUCT(TIMESTAMP('2017-01-02 10:01:00') AS time, 'one' AS festival)
,STRUCT('2019-03-04 10:10:10', 'two')
,STRUCT('2018-07-04 10:10:10', 'three')
,STRUCT('2018-03-05 10:10:10', 'four')
])
)
SELECT b,
(SELECT AS STRUCT *
FROM a
ORDER BY ABS(TIMESTAMP_DIFF(b.time, a.time, SECOND))
LIMIT 1) closest
FROM b
这篇关于BigQuery:根据最接近的时间戳加入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文