TimeStream stores data with a key:value approach.
Is there any simple way to pivot the data based on the dimensions to get for instance the max of all available measures in a column that is named as the measure name ?
Let take an example, with following dataset :
| time | instance_id | measure_name | measure_value::double | measure_value::bigint |
|------------------------------- |------------- |-------------------- |----------------------- |----------------------- |
| 2019-12-04 19:00:00.000000000 | A | cpu_utilization | 35 | null |
| 2019-12-04 19:00:01.000000000 | A | cpu_utilization | 38.2 | null |
| 2019-12-04 19:00:02.000000000 | B | cpu_utilization | 45.3 | null |
| 2019-12-04 19:00:00.000000000 | A | memory_utilization | 54.9 | null |
| 2019-12-04 19:00:01.000000000 | A | memory_utilization | 42.6 | null |
| 2019-12-04 19:00:02.000000000 | B | memory_utilization | 33.3 | null |
We want to construct a generic request that would provide the following result without needing any code transformation :
| instance_id | cpu_utilization (max) | memory_utilization (max) |
|------------- |----------------------- |-------------------------- |
| A | 38.2 | 54.9 |
| B | 45.3 | 33.3 |
Of course, I know that by getting the following (see next table), with few code in my favorite language it is very easy to make the pivot, but I was wondering if this is possible on a native way.
| instance_id | measure_name | max(measure_value) |
|------------- |-------------------- |-------------------- |
| A | cpu_utilization | 38.2 |
| B | cpu_utilization | 45.3 |
| A | memory_utilization | 54.9 |
| B | memory_utilization | 33.3 |
Thank you
Do a
GROUP BY
. Usecase
expressions to do conditional aggregation: