Join.inner change sorting order to alphabetical

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}) )

I solved the problem by adding a post processing “Organize fields” transformation.

1 Like