1 Answer
- Newest
- Most votes
- Most comments
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.
answered 2 years ago
Relevant content
- asked 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated a year ago
Thanks Rajesh! I was thinking of doing this via introducing a calculated fields on quicksight? Is this use case supported?