我有两个表，对于表numberTwo的每一行，我需要在表numberOne中获取具有相同cod值的提示，在这些提示之间，当比较time1和time2时，提示具有最接近的时间。
为了更容易理解我需要做的是：
表号一：
| id | cod | hint | time1 |
---------------------------------------------------
| 1 | ABC | V | 2016-11-03 18:00:00 UTC |
| 2 | ABC | W | 2016-11-03 12:00:00 UTC |
| 3 | CDE | X | 2016-11-03 19:00:00 UTC |
| 4 | CDE | Y | 2016-11-03 19:30:00 UTC |
| 5 | EFG | Z | 2016-11-03 18:00:00 UTC |
表号2
| id | cod | value | time2 |
----------------------------------------------------
| 1 | ABC | xyz2 | 2016-11-03 18:20:00 UTC |
| 2 | ABC | h323 | 2016-11-03 11:30:00 UTC |
| 3 | ABC | rewq | 2016-11-03 09:00:00 UTC |
| 4 | CDE | abce | 2016-11-03 19:10:00 UTC |
因此，对于表2的第1行，我将使用编码将表1的所有行编码为：ABC
| 1 | ABC | V | 2016-11-03 18:00:00 UTC |
| 2 | ABC | W | 2016-11-03 12:00:00 UTC |
在这两者之间，我将获得与time2最接近的时间戳：
| 1 | ABC | V | 2016-11-03 18:00:00 UTC |
处理完每一行后，我将得到一个像这样的表：
所需表
| id | cod | hint | value | time2 |
--------------------------------------------------------------
| 1 | ABC | V | xyz2 | 2016-11-03 18:20:00 UTC |
| 2 | ABC | W | h323 | 2016-11-03 11:30:00 UTC |
| 3 | ABC | W | rewq | 2016-11-03 09:00:00 UTC |
| 4 | CDE | X | abce | 2016-11-03 19:10:00 UTC |
最佳答案
适用于BigQuery标准SQL-尝试以下操作
您可以取消注释带有示例数据的注释块以进行快速测试
WITH
/*
TableNumberOne AS (
SELECT 1 AS id, 'ABC' AS cod, 'V' AS hint, TIMESTAMP '2016-11-03 18:00:00 UTC' AS time1 UNION ALL
SELECT 2 AS id, 'ABC' AS cod, 'W' AS hint, TIMESTAMP '2016-11-03 12:00:00 UTC' AS time1 UNION ALL
SELECT 3 AS id, 'CDE' AS cod, 'X' AS hint, TIMESTAMP '2016-11-03 19:00:00 UTC' AS time1 UNION ALL
SELECT 4 AS id, 'CDE' AS cod, 'Y' AS hint, TIMESTAMP '2016-11-03 19:30:00 UTC' AS time1 UNION ALL
SELECT 5 AS id, 'EFG' AS cod, 'Z' AS hint, TIMESTAMP '2016-11-03 18:00:00 UTC' AS time1
),
TableNumberTwo AS (
SELECT 1 AS id, 'ABC' AS cod, 'xyz2' AS value, TIMESTAMP '2016-11-03 18:20:00 UTC' AS time2 UNION ALL
SELECT 2 AS id, 'ABC' AS cod, 'h323' AS value, TIMESTAMP '2016-11-03 11:30:00 UTC' AS time2 UNION ALL
SELECT 3 AS id, 'ABC' AS cod, 'rewq' AS value, TIMESTAMP '2016-11-03 09:00:00 UTC' AS time2 UNION ALL
SELECT 4 AS id, 'CDE' AS cod, 'abce' AS value, TIMESTAMP '2016-11-03 19:10:00 UTC' AS time2
),
*/
tempTable AS (
SELECT
t2.id, t2.cod, t2.value, t2.time2, t1.hint,
ROW_NUMBER() OVER(PARTITION BY t2.id, t2.cod, t2.value
ORDER BY ABS(TIMESTAMP_DIFF(t2.time2, t1.time1, SECOND))) AS win
FROM TableNumberTwo AS t2
JOIN TableNumberOne AS t1
ON t1.cod = t2.cod
)
SELECT id, cod, hint, value, time2
FROM tempTable
WHERE win = 1
关于mysql - BigQuery根据最接近的时间戳记和匹配值组合表格，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40413094/