python-3.x - 如何按分钟获取数据的最大值？

Question

我正在使用这个查询

Select
    distinct
    creation_time,
    max(total_bytes_processed) as total,
    query
    FROM `project-id.region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
    where creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)AND job_type = "QUERY" 
    group by  creation_time, query

我想获取最后一小时的数据并获得基于分钟的最大值。例如，假设我有这个示例数据：

  10 ,2020-10-19 15:50:58.108000+00:00"
  20 ,2020-10-19 15:51:25.718000+00:00"
  45, 2020-10-19 15:51:55.356000+00:00"
  50, 2020-10-19 15:52:50.269000+00:00"
  5,  2020-10-19 15:50:40.527000+00:00"
  15, 2020-10-19 15:51:08.883000+00:00"
  25, 2020-10-19 15:51:39.082000+00:00"
  47, 2020-10-19 15:52:16.587000+00:00"
  60, 2020-10-19 15:53:02.901000+00:00"

对于这些数据，我将如何按分钟获得最大值？

我想要的输出是：因为10 ,2020-10-19 15:50:58.108000+00:00值高于 5, 2020-10-19 15:50:40.527000+00:00 输出最大值。

10, 2020-10-19 15:50 

45, 2020-10-19 15:51 

50 , 2020-10-19 15:52

60, 2020-10-19 15:53

标签： python-3.xdatetimegoogle-bigquery

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
select max(value) value, timestamp_trunc(ts, minute) ts
from data
group by timestamp_trunc(ts, minute)

您可以使用您问题中的示例数据进行测试，使用上面的示例数据，如下例所示

#standardSQL
with data AS (
  select 10 value, timestamp '2020-10-19 15:50:58.108000+00:00' ts union all
  select 20, '2020-10-19 15:51:25.718000+00:00' union all
  select 45, '2020-10-19 15:51:55.356000+00:00' union all
  select 50, '2020-10-19 15:52:50.269000+00:00' union all
  select 5, '2020-10-19 15:50:40.527000+00:00' union all
  select 15, '2020-10-19 15:51:08.883000+00:00' union all
  select 25, '2020-10-19 15:51:39.082000+00:00' union all
  select 47, '2020-10-19 15:52:16.587000+00:00' union all
  select 60, '2020-10-19 15:53:02.901000+00:00' 
)
select max(value) value, timestamp_trunc(ts, minute) ts
from data
group by timestamp_trunc(ts, minute)  

带输出