-
Notifications
You must be signed in to change notification settings - Fork 13
/
SCRATCH.flux
192 lines (152 loc) · 6.77 KB
/
SCRATCH.flux
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
// schema exploration
import "influxdata/influxdb/schema"
schema.tagValues(bucket: "racing", tag: "CarClass")
import "influxdata/influxdb/schema"
class = schema.tagValues(bucket: "racing", tag: "CarClass")
model = schema.tagValues(bucket: "racing", tag: "CarModel")
// A single metric
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "laps")
|> filter(fn: (r) => r._field == "TrackPositionPercent")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "mean")
// join
brake = from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => r["_field"] == "Brake")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
// list Sessions
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => r["_field"] == "Gear")
|> keep(columns: ["GameName", "user", "CarModel", "TrackCode", "SessionId", "_time", "_value"])
|> sort(columns: ["_time"], desc: false)
|> first()
|> group(columns: [])
|> drop(columns: ["_value"])
// annotations
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
// |> range(start: -2d, stop: now())
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => r["_field"] == "CurrentLap")
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> sort(columns: ["_time"])
|> keep(columns: ["_time", "_value"])
|> yield(name: "mean")
// get SessionId
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => r["_field"] == "Gear")
|> keep(columns: ["SessionId"])
|> distinct(column: "SessionId")
// variable filtering
// https://community.grafana.com/t/grafana-influxdb-flux-query-for-displaying-multi-select-variable-inputs/35536/14
x = from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => r["_field"] == "world_x")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
join(tables: {brake: brake, x: x}, on: ["_time"] )
|> keep(columns: ["_value_brake", "_value_x"])
|> sort(columns: ["_value_x"], desc: false)
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> keyValues(keyColumns: ["CarClass"])
|> group()
|> keep(columns: ["CarClass"])
|> distinct(columns: ["CarClass"])
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> group(columns: ["CarClass"], mode:"by")
|> count()
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> group(columns: ["CarClass"], mode:"by")
|> count()
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> group(columns: ["CarClass"], mode:"by")
|> count()
|> map(fn: (r) => ({r with _value: float(v: r._value)}))
|> histogram(
bins: linearBins(start: 0.0, width: 5000.0, count: 10, infinity: true),
normalize: true
)
// CarModels
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "laps")
|> keyValues(keyColumns: ["CarModel"])
|> group()
|> keep(columns: ["CarModel"])
|> distinct(column: "CarModel")
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => r["_field"] == "Brake" or r["_field"] == "Clutch" or r["_field"] == "Throttle"
or contains(value: r._CarModel, set: ${CarModel:json})
or contains(value: r._CarClass, set: ${CarClass:json})
)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "mean")
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => (r["_field"] == "Brake" or r["_field"] == "Clutch" or r["_field"] == "Throttle"))
|> group(columns: ["CarModel", "_field"])
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "mean")
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => (r["_field"] == "Brake" or r["_field"] == "Clutch" or r["_field"] == "Throttle"))
|> group(columns: ["CarModel", "_field"])
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "mean")
from(bucket: "racing")
|> range(start: 2022-04-29T07:28:32.729Z, stop:2022-04-29T07:30:51.993Z)
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => r["_field"] == "Brake" or r["_field"] == "Clutch" or r["_field"] == "Throttle"
and( contains(value: r.CarModel, set: ["Mazda MX-5 Cup"]) and contains(value: r.CarClass, set: ["MX5 Cup 2016","Rallycross","SBRS"]))
)
|> aggregateWindow(every: 200ms, fn: mean, createEmpty: false)
|> yield(name: "mean")
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => (
(r["_field"] == "Brake" or r["_field"] == "Clutch" or r["_field"] == "Throttle")
and (
contains(value: r.CarModel, set: ${CarModel:json})
)
))
|> group(columns: ["CarModel", "_field"])
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "mean")
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "laps")
|> filter(fn: (r) => r["_field"] == "CurrentLap")
|> group(columns: ["SessionId", "UserId", "GameName", "TrackCode", "CarModel", "user"])
|> count()
|> yield(name: "mean")
https://grafana.com/grafana/dashboards/15356
https://www.sqlpac.com/en/documents/influxdb-v2-flux-language-quick-reference-guide-cheat-sheet.html
games = [${GameName:doublequote}]
session_types = [${SessionTypeName:doublequote}]
from(bucket: "racing")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "laps_cc")
|> filter(fn: (r) => r["user"] == "${user}")
|> keep(columns: ["GameName", "user", "CarModel", "TrackCode", "SessionId", "SessionTypeName", "_time", "_value"])
|> last()
|> group()
|> filter(fn: (r) => contains(value: r.GameName, set: games) )
|> filter(fn: (r) => contains(value: r.SessionTypeName, set: session_types) )
|> group(columns: [])
|> sort(columns: ["End"], desc: true)