I have a use case where I plot hourly power usage values in a table. The table is created by joining a table of hourly power usage with a table of a summation of a days total power usage.
The timeslots are named 0-23 and are columns in the pivot table. When i observe the pivoted power table, the top row (0-23) is ordered numerically, like I want, but once I join the two tables
the sorting instead uses alphabetical sorting. This does not happen if i run the same query in InfluxDB.
Why is this happening, and what is the best way to keep the numerical ordering?
As a reference, this is how the data looks in the pivot table:
And this is how it looks after the join operation:
Here is the full query:
import "join"
import "date"
import "timezone"
option location = timezone.location(name: "Europe/Stockholm")
start_ = date.truncate(t: today(), unit: 1mo)
end_ = date.truncate(t: date.add(d: 1mo, to:start_), unit:1mo)
base = from(bucket: "Powerdata")
|> range(start: start_, stop: end_)
|> filter(fn: (r) => r["_measurement"] == "power_usage" and
r["_field"] == "value" and
r["topic"] == "powerusage")
|> aggregateWindow(every: 1h, fn: count, createEmpty: false)
|> toFloat()
|> timeShift(columns: ["_time"], duration: -1h)
|> map(fn: (r) => ({r with _value: r._value*4.0/1000.0,
day: date.monthDay(t: r._time),
timeslot: date.hour(t: r._time)}))
p = base
|> group()
|> pivot(rowKey: ["day"],
columnKey: ["timeslot"],
valueColumn: "_value")
s = base
|> group(columns: ["day"])
|> sum()
|> group()
join.inner(left: p,
right: s,
on: (l,r) => l.day == r.day,
as: (l,r) => ({l with total : r._value}) )