google-bigquery - 选择 BigQuery 中具有不同 2 列的最新事件

Question

我有一个 BigQuery 表，其架构如下:

{
  {"name": "timeCreated", "type": "datetime"},
  {"name": "userid", "type": "string"},
  {"name": "textid", "type": "string"},
  {"name": "textvalue": "type": "float"}
}

我正在尝试进行查询，因此我最终得到了为每对 userid 和 textid 组合创建的最新时间行。我已经尝试过 GROUP BY 等人，但我似乎无法获得 ORDER BY timeCreated 字段，然后删除每对 userid 和 textid 列不在顶部的所有行。

Answer 1

要获取 Google BigQuery 中组的最新(最后)或最早(第一个)元素，您可以使用 ARRAY_AGG与 [OFFSET (0)] 和适当的 ORDER BY(DESC 或 ASC):

WITH test_table AS (
  SELECT DATETIME '2020-11-01 01:00:00' AS timeCreated, 'user1' AS userid, 'text1' AS textid, 1.1 AS textvalue UNION ALL
  SELECT DATETIME '2020-11-01 03:00:00' AS timeCreated, 'user1' AS userid, 'text1' AS textid, 1.2 AS textvalue UNION ALL
  SELECT DATETIME '2020-11-01 02:00:00' AS timeCreated, 'user1' AS userid, 'text1' AS textid, 1.3 AS textvalue UNION ALL
  SELECT DATETIME '2020-11-01 02:00:00' AS timeCreated, 'user1' AS userid, 'text2' AS textid, 1.4 AS textvalue UNION ALL
  SELECT DATETIME '2020-11-01 01:00:00' AS timeCreated, 'user1' AS userid, 'text2' AS textid, 1.5 AS textvalue UNION ALL
  SELECT DATETIME '2020-11-01 00:00:00' AS timeCreated, 'user2' AS userid, 'text1' AS textid, 1.6 AS textvalue
)
SELECT 
  userid,
  textid,
  ARRAY_AGG(timeCreated ORDER BY timeCreated DESC LIMIT 1)[OFFSET(0)] AS latest FROM test_table
GROUP BY userid, textid