如何从information_schema.warehouse_load_history()中获得每分钟平均值

Question

snoflake的information_schema.warehouse_load_history()每5秒提供一次数据，有什么方法可以得到每分钟的平均值吗？

查询

select * from table(snowflake.information_schema.warehouse_load_history());

结果

+-------------------------------+-------------------------------+----------------+-------------+-----------------+-------------------------+-------------+
| START_TIME                    | END_TIME                      | WAREHOUSE_NAME | AVG_RUNNING | AVG_QUEUED_LOAD | AVG_QUEUED_PROVISIONING | AVG_BLOCKED |
|-------------------------------+-------------------------------+----------------+-------------+-----------------+-------------------------+-------------|
| 2022-11-10 00:54:00.000 -0800 | 2022-11-10 00:54:05.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:05.000 -0800 | 2022-11-10 00:54:10.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:10.000 -0800 | 2022-11-10 00:54:15.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:15.000 -0800 | 2022-11-10 00:54:20.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:20.000 -0800 | 2022-11-10 00:54:25.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:25.000 -0800 | 2022-11-10 00:54:30.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:30.000 -0800 | 2022-11-10 00:54:35.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:35.000 -0800 | 2022-11-10 00:54:40.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:40.000 -0800 | 2022-11-10 00:54:45.000 -0800 | PROD_WH   |        0.03 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:45.000 -0800 | 2022-11-10 00:54:50.000 -0800 | PROD_WH   |        0.01 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:50.000 -0800 | 2022-11-10 00:54:55.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:54:55.000 -0800 | 2022-11-10 00:55:00.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:55:00.000 -0800 | 2022-11-10 00:55:05.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:55:05.000 -0800 | 2022-11-10 00:55:10.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:55:10.000 -0800 | 2022-11-10 00:55:15.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:55:15.000 -0800 | 2022-11-10 00:55:20.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:55:20.000 -0800 | 2022-11-10 00:55:25.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:55:25.000 -0800 | 2022-11-10 00:55:30.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |
| 2022-11-10 00:55:30.000 -0800 | 2022-11-10 00:55:35.000 -0800 | PROD_WH   |        0.00 |            0.00 |                    0.00 |        0.00 |

Answer 1

您可以使用聚合函数：

select date_trunc( 'minutes', start_time ), warehouse_name, avg(avg_running), avg(AVG_QUEUED_LOAD), avg(AVG_QUEUED_PROVISIONING), avg(AVG_BLOCKED)  from table(snowflake.information_schema.warehouse_load_history())
group by date_trunc( 'minutes', start_time ), 2;

不要忘记，如果所选周期小于8小时，负载将以5秒间隔显示；否则，将使用5分钟间隔。