1 Antwort
- Neueste
- Die meisten Stimmen
- Die meisten Kommentare
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.
beantwortet vor 2 Jahren
Relevanter Inhalt
- AWS OFFICIALAktualisiert vor 3 Jahren
- AWS OFFICIALAktualisiert vor einem Jahr
- AWS OFFICIALAktualisiert vor einem Jahr
- AWS OFFICIALAktualisiert vor 2 Jahren
Thanks Rajesh! I was thinking of doing this via introducing a calculated fields on quicksight? Is this use case supported?