gpt4 book ai didi

google-analytics - BigQuery:根据最接近的时间戳加入

转载 作者:行者123 更新时间:2023-12-03 17:24:23 26 4
gpt4 key购买 nike

我正在基于BigQuery中最接近的时间戳加入两个表,并收到此错误。连接谓词中带有表的不支持的子查询。

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


enter image description here

关于google-analytics - BigQuery:根据最接近的时间戳加入,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58170852/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com