如何获取每个指标BigQuery的最后一行? [英] How to get the last row per metric BigQuery?

问题描述

数据:

+-------+------------+-----------------------+
| value |   metric   |  timestamp            |
+-------+------------+-----------------------+
| 30    | tempA      |  2019-01-12T01:00:00  |
| 28    | tempA      |  2019-02-12T02:00:00  |
| 25    | tempB      |  2019-03-12T03:00:00  |
| 60    | humidityA  |  2019-04-12T04:00:00  |
| 15    | tempC      |  2019-05-15T01:00:00  |
+-------+------------+-----------------------+

我想获取某些温度指标的最新已知值.我想出了:

I want to get the last known value for certain temp metrics. I came up with:

SELECT
  metric,
  value,
  timestamp,
  seqnum
FROM (
  SELECT
    metric,
    value,
    timestamp,
    ROW_NUMBER() OVER (PARTITION BY metric ORDER BY timestamp DESC) AS seqnum
  FROM
    `project.dataset.table`
  WHERE
    metric IN ('tempA', 'tempB') )
WHERE
  seqnum = 1

这会对整个表进行分区，这可能需要一些时间.是否有更高效/高效的方法来获取每个指标的最后一行?

This partitions the whole table, which may take some time. Is there a more performant/efficient way to get the last row per metric?

推荐答案

数组聚合可以更快，因为由于LIMIT 1，它只需要将第一行保留在内存中即可.

Array aggregation can be faster since it only needs to keep the top row in memory due to the LIMIT 1:

SELECT
  metric,
  ARRAY_AGG(
    STRUCT(value, timestamp)
    ORDER BY timestamp DESC LIMIT 1
  )[OFFSET(0)].*
FROM
  `project.dataset.table`
WHERE
  metric IN ('tempA', 'tempB')
GROUP BY metric

这篇关于如何获取每个指标BigQuery的最后一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！