1 réponse
- Le plus récent
- Le plus de votes
- La plupart des commentaires
0
If I understand correctly, the query result is a single value which represents the aggregate power of all devices. The device values to aggregate is the latest power of each device. For e.g. if there are 3 devices with the following power values
device1: 00:00:00 val=20, 00:00:05 val=25, 00:00:10 val=20
device2: 00:00:00 val=19, 00:00:05 val=72, 00:00:10 val=81
device3: 00:00:00 val=00, 00:00:05 val=22, 00:00:10 val=100
The result will be (20 + 81 + 100) = 201
A sample query for this is below
WITH cte1 AS (
SELECT device, first_value(measure_value::bigint) OVER (PARTITION BY device ORDER BY time DESC) as value FROM <db>.<table> WHERE time > ago(1m)
), cte2 AS (
SELECT device, min(value) as value FROM cte1 GROUP BY device
) SELECT sum(value) FROM cte2
The time predicate e.g. ago(1m)
is necessary to ensure that the whole table is not scanned. It can be set to any time window that will give the latest power values of all devices.
répondu il y a 2 ans
Contenus pertinents
- demandé il y a 2 mois
- demandé il y a un an
- demandé il y a 7 mois
- AWS OFFICIELA mis à jour il y a 3 ans
Thanks Rajesh! I was thinking of doing this via introducing a calculated fields on quicksight? Is this use case supported?