Partition - Smoothing
Query - Unbound Window
SELECT datetime, value,
COUNT(value) AS "Count",
AVG(value) AS "SimpleAvg",
WAVG(value) AS "WeightedAvg",
EMA(0.01, value) AS "ExpAvg-0.01",
EMA(0.10, value) AS "ExpAvg-0.10",
EMA(0.25, value) AS "ExpAvg-0.25",
EMA(0.90, value) AS "ExpAvg-0.90",
EMA(0.99, value) AS "ExpAvg-0.99"
FROM timer_1m
WHERE datetime BETWEEN '2019-02-09T00:30:00Z' AND '2019-02-09T02:30:00Z'
WITH INTERPOLATE(5 minute, linear)
WITH ROW_NUMBER(entity, tags ORDER BY time) > 0
View ChartLab example.
Results
| datetime | value | Count | SimpleAvg | WeightedAvg | ExpAvg-0.01 | ExpAvg-0.10 | ExpAvg-0.25 | ExpAvg-0.90 | ExpAvg-0.99 |
|---------------------|-------|-------|-----------|-------------|-------------|-------------|-------------|-------------|-------------|
| 2019-02-09 00:30:00 | 30.00 | 1 | 30.00 | 30.00 | 30.00 | 30.00 | 30.00 | 30.00 | 30.00 |
| 2019-02-09 00:35:00 | 35.00 | 2 | 32.50 | 33.33 | 30.05 | 30.50 | 31.25 | 34.50 | 34.95 |
| 2019-02-09 00:40:00 | 40.00 | 3 | 35.00 | 36.67 | 30.15 | 31.45 | 33.44 | 39.45 | 39.95 |
| 2019-02-09 00:45:00 | 45.00 | 4 | 37.50 | 40.00 | 30.30 | 32.80 | 36.33 | 44.44 | 44.95 |
| 2019-02-09 00:50:00 | 50.00 | 5 | 40.00 | 43.33 | 30.50 | 34.52 | 39.75 | 49.44 | 49.95 |
| 2019-02-09 00:55:00 | 55.00 | 6 | 42.50 | 46.67 | 30.74 | 36.57 | 43.56 | 54.44 | 54.95 |
| 2019-02-09 01:00:00 | 0.00 | 7 | 36.43 | 35.00 | 30.43 | 32.91 | 32.67 | 5.44 | 0.55 |
| 2019-02-09 01:05:00 | 5.00 | 8 | 32.50 | 28.33 | 30.18 | 30.12 | 25.75 | 5.04 | 4.96 |
| 2019-02-09 01:10:00 | 10.00 | 9 | 30.00 | 24.67 | 29.98 | 28.11 | 21.81 | 9.50 | 9.95 |
| 2019-02-09 01:15:00 | 15.00 | 10 | 28.50 | 22.91 | 29.83 | 26.80 | 20.11 | 14.45 | 14.95 |
| 2019-02-09 01:20:00 | 20.00 | 11 | 27.73 | 22.42 | 29.73 | 26.12 | 20.08 | 19.45 | 19.95 |
| 2019-02-09 01:25:00 | 25.00 | 12 | 27.50 | 22.82 | 29.68 | 26.01 | 21.31 | 24.44 | 24.95 |
| 2019-02-09 01:30:00 | 30.00 | 13 | 27.69 | 23.85 | 29.68 | 26.41 | 23.48 | 29.44 | 29.95 |
| 2019-02-09 01:35:00 | 35.00 | 14 | 28.21 | 25.33 | 29.74 | 27.27 | 26.36 | 34.44 | 34.95 |
| 2019-02-09 01:40:00 | 40.00 | 15 | 29.00 | 27.17 | 29.84 | 28.54 | 29.77 | 39.44 | 39.95 |
| 2019-02-09 01:45:00 | 45.00 | 16 | 30.00 | 29.26 | 29.99 | 30.19 | 33.58 | 44.44 | 44.95 |
| 2019-02-09 01:50:00 | 50.00 | 17 | 31.18 | 31.57 | 30.19 | 32.17 | 37.68 | 49.44 | 49.95 |
| 2019-02-09 01:55:00 | 55.00 | 18 | 32.50 | 34.04 | 30.44 | 34.45 | 42.01 | 54.44 | 54.95 |
| 2019-02-09 02:00:00 | 0.00 | 19 | 30.79 | 30.63 | 30.14 | 31.01 | 31.51 | 5.44 | 0.55 |
| 2019-02-09 02:05:00 | 5.00 | 20 | 29.50 | 28.19 | 29.88 | 28.40 | 24.88 | 5.04 | 4.96 |
| 2019-02-09 02:10:00 | 10.00 | 21 | 28.57 | 26.54 | 29.69 | 26.56 | 21.16 | 9.50 | 9.95 |
| 2019-02-09 02:15:00 | 15.00 | 22 | 27.95 | 25.53 | 29.54 | 25.41 | 19.62 | 14.45 | 14.95 |
| 2019-02-09 02:20:00 | 20.00 | 23 | 27.61 | 25.07 | 29.44 | 24.87 | 19.72 | 19.45 | 19.95 |
| 2019-02-09 02:25:00 | 25.00 | 24 | 27.50 | 25.07 | 29.40 | 24.88 | 21.04 | 24.44 | 24.95 |
| 2019-02-09 02:30:00 | 30.00 | 25 | 27.60 | 25.45 | 29.40 | 25.39 | 23.28 | 29.44 | 29.95 |
...
Query - Sliding Window
SELECT datetime, value,
COUNT(value) AS "Count",
AVG(value) AS "SMA",
WAVG(value) AS "WMA",
WTAVG(value) AS "WTMA",
EMA(0.10, value) AS "EMA-0.10"
FROM timer_1m
WHERE datetime BETWEEN '2019-02-09T00:30:00Z' AND '2019-02-09T02:30:00Z'
WITH INTERPOLATE(5 minute, linear)
WITH ROW_NUMBER(entity, tags ORDER BY time) BETWEEN 10 PRECEDING AND CURRENT ROW
View ChartLab example.
Results
| datetime | value | Count | SMA | WMA | WTMA | EMA-0.10 |
|---------------------|-------|-------|-------|-------|-------|----------|
| 2019-02-09 00:30:00 | 30.00 | 1 | 30.00 | 30.00 | 30.00 | 30.00 |
| 2019-02-09 00:35:00 | 35.00 | 2 | 32.50 | 33.33 | 34.98 | 30.50 |
| 2019-02-09 00:40:00 | 40.00 | 3 | 35.00 | 36.67 | 38.32 | 31.45 |
| 2019-02-09 00:45:00 | 45.00 | 4 | 37.50 | 40.00 | 41.66 | 32.80 |
| 2019-02-09 00:50:00 | 50.00 | 5 | 40.00 | 43.33 | 44.99 | 34.52 |
| 2019-02-09 00:55:00 | 55.00 | 6 | 42.50 | 46.67 | 48.33 | 36.57 |
| 2019-02-09 01:00:00 | 0.00 | 7 | 36.43 | 35.00 | 34.53 | 32.91 |
| 2019-02-09 01:05:00 | 5.00 | 8 | 32.50 | 28.33 | 27.15 | 30.12 |
| 2019-02-09 01:10:00 | 10.00 | 9 | 30.00 | 24.67 | 23.34 | 28.11 |
| 2019-02-09 01:15:00 | 15.00 | 10 | 28.50 | 22.91 | 21.67 | 26.80 |
| 2019-02-09 01:20:00 | 20.00 | 10 | 27.50 | 21.36 | 20.01 | 27.86 |
| 2019-02-09 01:25:00 | 25.00 | 10 | 26.50 | 20.91 | 19.67 | 29.32 |
| 2019-02-09 01:30:00 | 30.00 | 10 | 25.50 | 21.55 | 20.67 | 31.13 |
| 2019-02-09 01:35:00 | 35.00 | 10 | 24.50 | 23.27 | 23.00 | 33.26 |
| 2019-02-09 01:40:00 | 40.00 | 10 | 23.50 | 26.09 | 26.66 | 35.68 |
| 2019-02-09 01:45:00 | 45.00 | 10 | 22.50 | 30.00 | 31.66 | 17.43 |
| 2019-02-09 01:50:00 | 50.00 | 10 | 27.50 | 35.00 | 36.66 | 22.43 |
| 2019-02-09 01:55:00 | 55.00 | 10 | 32.50 | 40.00 | 41.66 | 27.43 |
| 2019-02-09 02:00:00 | 0.00 | 10 | 31.50 | 34.09 | 34.66 | 26.43 |
| 2019-02-09 02:05:00 | 5.00 | 10 | 30.50 | 29.27 | 29.00 | 26.03 |
| 2019-02-09 02:10:00 | 10.00 | 10 | 29.50 | 25.55 | 24.67 | 26.17 |
| 2019-02-09 02:15:00 | 15.00 | 10 | 28.50 | 22.91 | 21.67 | 26.80 |
| 2019-02-09 02:20:00 | 20.00 | 10 | 27.50 | 21.36 | 20.01 | 27.86 |
| 2019-02-09 02:25:00 | 25.00 | 10 | 26.50 | 20.91 | 19.67 | 29.32 |
| 2019-02-09 02:30:00 | 30.00 | 10 | 25.50 | 21.55 | 20.67 | 31.13 |