google-bigquery - 访问 STRUCT 中的第 10 到第 70 个元素

Question

我有 3 个字段：用户名、tracking_id、时间戳。对于他在我的网站上执行的每项操作，一个用户将有多个行（有些行更多，有些行更少）具有不同的跟踪 ID 和时间戳。我想按用户名分组并获取该用户第 10 次到第 70 次操作的跟踪 ID。我在 BigQuery 上使用标准 SQL。

第一个问题是，我找不到访问 STRUCT 中范围的语法（例如，只有一行或使用限制来获取前/后 70 行）。然后，我可以在设法访问一个范围后进行映像，索引可能存在超出范围的问题，因为某些用户可能没有 70 或更多的操作。

SELECT
  username,
  ARRAY_AGG(STRUCT(tracking_id,
      timestamp)
  ORDER BY
    timestamp
    )[OFFSET (9 to 69)] #??????
FROM
  table

结果应该是一个具有相同 3 个字段的表：用户名、tracking_id、时间戳，但它不应该包含所有用户的行，而应该只包含每个用户的第 10 到第 70 行。

标签： google-bigquery

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT username, 
  ARRAY_AGG(STRUCT(tracking_id, `timestamp`) ORDER BY `timestamp`) AS selected_actions
FROM (
  SELECT * EXCEPT(pos) FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY username ORDER BY `timestamp`) pos
    FROM `project.dataset.table`
  ) 
  WHERE pos BETWEEN 10 AND 70
)
GROUP BY username