我的书签
添加书签
移除书签Window and aggregate data with Flux
A common operation performed with time series data is grouping data into windows of time, or “windowing” data, then aggregating windowed values into a new value. This guide walks through windowing and aggregating data with Flux and demonstrates how data is shaped in the process.
If you’re just getting started with Flux queries, check out the following:
- Get started with Flux for a conceptual overview of Flux and parts of a Flux query.
- Execute queries to discover a variety of ways to run your queries.
The following example is an in-depth walk-through of the steps required to window and aggregate data. The aggregateWindow() function performs these operations for you, but understanding how data is shaped in the process helps to successfully create your desired output.
Data set
For the purposes of this guide, define a variable that represents your base data set. The following example queries the memory usage of the host machine.
1. dataSet = from(bucket: "example-bucket")
2. |> range(start: -5m)
3. |> filter(fn: (r) => r._measurement == "mem" and r._field == "used_percent")
4. |> drop(columns: ["host"])
This example drops the host column from the returned data since the memory data is only tracked for a single host and it simplifies the output tables. Dropping the host column is optional and not recommended if monitoring memory on multiple hosts.
dataSet can now be used to represent your base data, which will look similar to the following:
1. Table: keys: [_start, _stop, _field, _measurement]
2. _start:time _stop:time _field:string _measurement:string _time:time _value:float
3. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
4. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:50:00.000000000Z 71.11611366271973
5. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:50:10.000000000Z 67.39630699157715
6. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:50:20.000000000Z 64.16666507720947
7. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:50:30.000000000Z 64.19951915740967
8. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:50:40.000000000Z 64.2122745513916
9. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:50:50.000000000Z 64.22209739685059
10. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:51:00.000000000Z 64.6336555480957
11. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:51:10.000000000Z 64.16516304016113
12. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:51:20.000000000Z 64.18349742889404
13. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:51:30.000000000Z 64.20474052429199
14. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:51:40.000000000Z 68.65062713623047
15. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:51:50.000000000Z 67.20139980316162
16. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:52:00.000000000Z 70.9143877029419
17. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:52:10.000000000Z 64.14549350738525
18. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:52:20.000000000Z 64.15379047393799
19. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:52:30.000000000Z 64.1592264175415
20. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:52:40.000000000Z 64.18190002441406
21. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:52:50.000000000Z 64.28837776184082
22. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:53:00.000000000Z 64.29731845855713
23. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:53:10.000000000Z 64.36963081359863
24. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:53:20.000000000Z 64.37397003173828
25. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:53:30.000000000Z 64.44413661956787
26. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:53:40.000000000Z 64.42906856536865
27. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:53:50.000000000Z 64.44573402404785
28. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:00.000000000Z 64.48912620544434
29. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:10.000000000Z 64.49522972106934
30. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:20.000000000Z 64.48652744293213
31. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:30.000000000Z 64.49949741363525
32. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:40.000000000Z 64.4949197769165
33. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:50.000000000Z 64.49787616729736
34. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:55:00.000000000Z 64.49816226959229
Windowing data
Use the window() function to group your data based on time bounds. The most common parameter passed with the window() is every which defines the duration of time between windows. Other parameters are available, but for this example, window the base data set into one minute windows.
1. dataSet
2. |> window(every: 1m)
The every parameter supports all valid duration units, including calendar months (1mo) and years (1y).
Each window of time is output in its own table containing all records that fall within the window.
window() output tables
1. Table: keys: [_start, _stop, _field, _measurement]
2. _start:time _stop:time _field:string _measurement:string _time:time _value:float
3. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
4. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:51:00.000000000Z used_percent mem 2018-11-03T17:50:00.000000000Z 71.11611366271973
5. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:51:00.000000000Z used_percent mem 2018-11-03T17:50:10.000000000Z 67.39630699157715
6. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:51:00.000000000Z used_percent mem 2018-11-03T17:50:20.000000000Z 64.16666507720947
7. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:51:00.000000000Z used_percent mem 2018-11-03T17:50:30.000000000Z 64.19951915740967
8. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:51:00.000000000Z used_percent mem 2018-11-03T17:50:40.000000000Z 64.2122745513916
9. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:51:00.000000000Z used_percent mem 2018-11-03T17:50:50.000000000Z 64.22209739685059
12. Table: keys: [_start, _stop, _field, _measurement]
13. _start:time _stop:time _field:string _measurement:string _time:time _value:float
14. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
15. 2018-11-03T17:51:00.000000000Z 2018-11-03T17:52:00.000000000Z used_percent mem 2018-11-03T17:51:00.000000000Z 64.6336555480957
16. 2018-11-03T17:51:00.000000000Z 2018-11-03T17:52:00.000000000Z used_percent mem 2018-11-03T17:51:10.000000000Z 64.16516304016113
17. 2018-11-03T17:51:00.000000000Z 2018-11-03T17:52:00.000000000Z used_percent mem 2018-11-03T17:51:20.000000000Z 64.18349742889404
18. 2018-11-03T17:51:00.000000000Z 2018-11-03T17:52:00.000000000Z used_percent mem 2018-11-03T17:51:30.000000000Z 64.20474052429199
19. 2018-11-03T17:51:00.000000000Z 2018-11-03T17:52:00.000000000Z used_percent mem 2018-11-03T17:51:40.000000000Z 68.65062713623047
20. 2018-11-03T17:51:00.000000000Z 2018-11-03T17:52:00.000000000Z used_percent mem 2018-11-03T17:51:50.000000000Z 67.20139980316162
23. Table: keys: [_start, _stop, _field, _measurement]
24. _start:time _stop:time _field:string _measurement:string _time:time _value:float
25. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
26. 2018-11-03T17:52:00.000000000Z 2018-11-03T17:53:00.000000000Z used_percent mem 2018-11-03T17:52:00.000000000Z 70.9143877029419
27. 2018-11-03T17:52:00.000000000Z 2018-11-03T17:53:00.000000000Z used_percent mem 2018-11-03T17:52:10.000000000Z 64.14549350738525
28. 2018-11-03T17:52:00.000000000Z 2018-11-03T17:53:00.000000000Z used_percent mem 2018-11-03T17:52:20.000000000Z 64.15379047393799
29. 2018-11-03T17:52:00.000000000Z 2018-11-03T17:53:00.000000000Z used_percent mem 2018-11-03T17:52:30.000000000Z 64.1592264175415
30. 2018-11-03T17:52:00.000000000Z 2018-11-03T17:53:00.000000000Z used_percent mem 2018-11-03T17:52:40.000000000Z 64.18190002441406
31. 2018-11-03T17:52:00.000000000Z 2018-11-03T17:53:00.000000000Z used_percent mem 2018-11-03T17:52:50.000000000Z 64.28837776184082
34. Table: keys: [_start, _stop, _field, _measurement]
35. _start:time _stop:time _field:string _measurement:string _time:time _value:float
36. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
37. 2018-11-03T17:53:00.000000000Z 2018-11-03T17:54:00.000000000Z used_percent mem 2018-11-03T17:53:00.000000000Z 64.29731845855713
38. 2018-11-03T17:53:00.000000000Z 2018-11-03T17:54:00.000000000Z used_percent mem 2018-11-03T17:53:10.000000000Z 64.36963081359863
39. 2018-11-03T17:53:00.000000000Z 2018-11-03T17:54:00.000000000Z used_percent mem 2018-11-03T17:53:20.000000000Z 64.37397003173828
40. 2018-11-03T17:53:00.000000000Z 2018-11-03T17:54:00.000000000Z used_percent mem 2018-11-03T17:53:30.000000000Z 64.44413661956787
41. 2018-11-03T17:53:00.000000000Z 2018-11-03T17:54:00.000000000Z used_percent mem 2018-11-03T17:53:40.000000000Z 64.42906856536865
42. 2018-11-03T17:53:00.000000000Z 2018-11-03T17:54:00.000000000Z used_percent mem 2018-11-03T17:53:50.000000000Z 64.44573402404785
45. Table: keys: [_start, _stop, _field, _measurement]
46. _start:time _stop:time _field:string _measurement:string _time:time _value:float
47. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
48. 2018-11-03T17:54:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:00.000000000Z 64.48912620544434
49. 2018-11-03T17:54:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:10.000000000Z 64.49522972106934
50. 2018-11-03T17:54:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:20.000000000Z 64.48652744293213
51. 2018-11-03T17:54:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:30.000000000Z 64.49949741363525
52. 2018-11-03T17:54:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:40.000000000Z 64.4949197769165
53. 2018-11-03T17:54:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:50.000000000Z 64.49787616729736
56. Table: keys: [_start, _stop, _field, _measurement]
57. _start:time _stop:time _field:string _measurement:string _time:time _value:float
58. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
59. 2018-11-03T17:55:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:55:00.000000000Z 64.49816226959229
When visualized in the InfluxDB UI, each window table is displayed in a different color.
Aggregate data
Aggregate functions take the values of all rows in a table and use them to perform an aggregate operation. The result is output as a new value in a single-row table.
Since windowed data is split into separate tables, aggregate operations run against each table separately and output new tables containing only the aggregated value.
For this example, use the mean() function to output the average of each window:
1. dataSet
2. |> window(every: 1m)
3. |> mean()
mean() output tables
1. Table: keys: [_start, _stop, _field, _measurement]
2. _start:time _stop:time _field:string _measurement:string _value:float
3. ------------------------------ ------------------------------ ---------------------- ---------------------- ----------------------------
4. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:51:00.000000000Z used_percent mem 65.88549613952637
7. Table: keys: [_start, _stop, _field, _measurement]
8. _start:time _stop:time _field:string _measurement:string _value:float
9. ------------------------------ ------------------------------ ---------------------- ---------------------- ----------------------------
10. 2018-11-03T17:51:00.000000000Z 2018-11-03T17:52:00.000000000Z used_percent mem 65.50651391347249
13. Table: keys: [_start, _stop, _field, _measurement]
14. _start:time _stop:time _field:string _measurement:string _value:float
15. ------------------------------ ------------------------------ ---------------------- ---------------------- ----------------------------
16. 2018-11-03T17:52:00.000000000Z 2018-11-03T17:53:00.000000000Z used_percent mem 65.30719598134358
19. Table: keys: [_start, _stop, _field, _measurement]
20. _start:time _stop:time _field:string _measurement:string _value:float
21. ------------------------------ ------------------------------ ---------------------- ---------------------- ----------------------------
22. 2018-11-03T17:53:00.000000000Z 2018-11-03T17:54:00.000000000Z used_percent mem 64.39330975214641
25. Table: keys: [_start, _stop, _field, _measurement]
26. _start:time _stop:time _field:string _measurement:string _value:float
27. ------------------------------ ------------------------------ ---------------------- ---------------------- ----------------------------
28. 2018-11-03T17:54:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 64.49386278788249
31. Table: keys: [_start, _stop, _field, _measurement]
32. _start:time _stop:time _field:string _measurement:string _value:float
33. ------------------------------ ------------------------------ ---------------------- ---------------------- ----------------------------
34. 2018-11-03T17:55:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 64.49816226959229
Because each data point is contained in its own table, when visualized, they appear as single, unconnected points.
Recreate the time column
Notice the _time column is not in the aggregated output tables. Because records in each table are aggregated together, their timestamps no longer apply and the column is removed from the group key and table.
Also notice the _start and _stop columns still exist. These represent the lower and upper bounds of the time window.
Many Flux functions rely on the _time column. To further process your data after an aggregate function, you need to re-add _time. Use the duplicate() function to duplicate either the _start or _stop column as a new _time column.
1. dataSet
2. |> window(every: 1m)
3. |> mean()
4. |> duplicate(column: "_stop", as: "_time")
duplicate() output tables
1. Table: keys: [_start, _stop, _field, _measurement]
2. _start:time _stop:time _field:string _measurement:string _time:time _value:float
3. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
4. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:51:00.000000000Z used_percent mem 2018-11-03T17:51:00.000000000Z 65.88549613952637
7. Table: keys: [_start, _stop, _field, _measurement]
8. _start:time _stop:time _field:string _measurement:string _time:time _value:float
9. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
10. 2018-11-03T17:51:00.000000000Z 2018-11-03T17:52:00.000000000Z used_percent mem 2018-11-03T17:52:00.000000000Z 65.50651391347249
13. Table: keys: [_start, _stop, _field, _measurement]
14. _start:time _stop:time _field:string _measurement:string _time:time _value:float
15. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
16. 2018-11-03T17:52:00.000000000Z 2018-11-03T17:53:00.000000000Z used_percent mem 2018-11-03T17:53:00.000000000Z 65.30719598134358
19. Table: keys: [_start, _stop, _field, _measurement]
20. _start:time _stop:time _field:string _measurement:string _time:time _value:float
21. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
22. 2018-11-03T17:53:00.000000000Z 2018-11-03T17:54:00.000000000Z used_percent mem 2018-11-03T17:54:00.000000000Z 64.39330975214641
25. Table: keys: [_start, _stop, _field, _measurement]
26. _start:time _stop:time _field:string _measurement:string _time:time _value:float
27. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
28. 2018-11-03T17:54:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:55:00.000000000Z 64.49386278788249
31. Table: keys: [_start, _stop, _field, _measurement]
32. _start:time _stop:time _field:string _measurement:string _time:time _value:float
33. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
34. 2018-11-03T17:55:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:55:00.000000000Z 64.49816226959229
“Unwindow” aggregate tables
Keeping aggregate values in separate tables generally isn’t the format in which you want your data. Use the window() function to “unwindow” your data into a single infinite (inf) window.
1. dataSet
2. |> window(every: 1m)
3. |> mean()
4. |> duplicate(column: "_stop", as: "_time")
5. |> window(every: inf)
Windowing requires a _time column which is why it’s necessary to recreate the _time column after an aggregation.
Unwindowed output table
1. Table: keys: [_start, _stop, _field, _measurement]
2. _start:time _stop:time _field:string _measurement:string _time:time _value:float
3. ------------------------------ ------------------------------ ---------------------- ---------------------- ------------------------------ ----------------------------
4. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:51:00.000000000Z 65.88549613952637
5. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:52:00.000000000Z 65.50651391347249
6. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:53:00.000000000Z 65.30719598134358
7. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:54:00.000000000Z 64.39330975214641
8. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:55:00.000000000Z 64.49386278788249
9. 2018-11-03T17:50:00.000000000Z 2018-11-03T17:55:00.000000000Z used_percent mem 2018-11-03T17:55:00.000000000Z 64.49816226959229
With the aggregate values in a single table, data points in the visualization are connected.
Summing up
You have now created a Flux query that windows and aggregates data. The data transformation process outlined in this guide should be used for all aggregation operations.
Flux also provides the aggregateWindow() function which performs all these separate functions for you.
The following Flux query will return the same results:
aggregateWindow function
1. dataSet
2. |> aggregateWindow(every: 1m, fn: mean)
