CODEWITHSUNDEEP
snowflake-cloud-data-platform
Programming-Lover
Programming-Lover

Reputation: 1265

How to get average value per minute from information_schema.warehouse_load_history()

snoflake's information_schema.warehouse_load_history() provides data for every 5 seconds, is there any way to get the average value per minute?

Query

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

Result

+-------------------------------+-------------------------------+----------------+-------------+-----------------+-------------------------+-------------+
| 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 |

Upvotes: 0

Views: 55

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

You can use aggregate functions:

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;

Don't forget that if the selected period is less than 8 hours, load is shown in 5-second intervals; otherwise, 5-minute intervals are used.

Upvotes: 1

Related Questions