@Anaisdg, if I understand the proposal @Jay_Clifford was linking to - it’s basically experimantal.join
?
I’m posting the real code and data her, minus the actual serial numbers. Every SERIAL is uniq pr 5G and 4G. The 4G and 5G data get the timestamp set by the same process so they are guaranteed to match across 4G and 5G. Timestap differences shown here is due to the time it takes me to edit - run - copy the files and outputs.
I have tried multiple variants of the band_xG
. Using map, group, neither. But the result always end up the same. I have not yet tried the fieldsAsCol()
suggestion.
import "experimental"
band_4G =from(bucket:"fmbb/autogen")
|> range(start: -1h, stop: -0h)
|> filter(fn: (r) => r._measurement == "cellular" and
r._field == "band" and
r.serial_number =~ /SERIALS/ and
r.carrier == "4g"
)
|> last()
|> group(columns: ["serial_number"])
// |> map(fn:(r) => ({serial_number:r.serial_number,_value: r._value}))
band_5G =from(bucket:"fmbb/autogen")
|> range(start: -1h, stop: -0h)
|> filter(fn: (r) => r._measurement == "cellular" and
r._field == "band" and
r.serial_number =~ /SERIALS/ and
r.carrier == "5g"
)
|> last()
|> group(columns: ["serial_number"])
// |> map(fn:(r) => ({serial_number:r.serial_number,_value: r._value}))
band_join_experimental = experimental.join(
left: band_5G,
right: band_4G,
fn: (left, right) => ({
left with
lv: left._value,
rv: right._value,
})
)
band_join = join(
tables:{b4g: band_4G,b5g: band_5G},
on: ["_time"]
)
// band_join_experimental
band_join
// band_4G
//band_5G
This here is the result from band_4G
--- 1
_start _stop _time _value _field _measurement carrier event_type host serial_number
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:59.680798307Z 7 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:07.951028618Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:12.65139133Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:52.2506839Z 7 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:09.450994627Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:18.080115384Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:16.380093804Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:31.051109215Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:49.580320951Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:33.150752235Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:11.88083486Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:26.980719112Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:43.951497901Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:33.950908606Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:31.551357797Z 28 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:35.780340747Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:13.280945134Z 7 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:40:48.75125927Z 7 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:39.251149174Z 7 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:53.880066019Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:15.180555959Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:42:00.280151373Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:38.68014069Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:28.751131459Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:57.480119039Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:08.850851654Z 7 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:50.650852376Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:36.650830672Z 7 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:54.080062187Z 7 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:13.580028059Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:21.180114438Z 7 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:58.450624515Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:17.080321766Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:33.880406058Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:44.750583122Z 7 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:50.380113762Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:03.080244902Z 7 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:41.25059569Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:39.151341346Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:15.380308241Z 7 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:36.980747631Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:04.180180129Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:52.980224702Z 7 band cellular 4g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:41.25059569Z 7 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:39.350996327Z 7 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:38.350569001Z 3 band cellular 4g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:00.393115167Z 2021-11-17T06:42:00.393115167Z 2021-11-17T06:41:47.08065926Z 3 band cellular 4g cellular cmp-swarm8 SERIAL
Output from band_5G
--- 1
_start _stop _time _value _field _measurement carrier event_type host serial_number
2021-11-17T05:42:37.675327224Z 2021-11-17T06:42:37.675327224Z 2021-11-17T06:41:52.2506839Z 78 band cellular 5g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:37.675327224Z 2021-11-17T06:42:37.675327224Z 2021-11-17T06:42:19.880614892Z 78 band cellular 5g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:37.675327224Z 2021-11-17T06:42:37.675327224Z 2021-11-17T06:42:14.850732429Z 78 band cellular 5g cellular cmp-swarm7 SERIAL
2021-11-17T05:42:37.675327224Z 2021-11-17T06:42:37.675327224Z 2021-11-17T06:42:23.580216037Z 78 band cellular 5g cellular cmp-swarm8 SERIAL
2021-11-17T05:42:37.675327224Z 2021-11-17T06:42:37.675327224Z 2021-11-17T06:41:39.350996327Z 78 band cellular 5g cellular cmp-swarm7 SERIAL
Output from band_join
--- 1
_field_b4g _field_b5g _measurement_b4g _measurement_b5g _start_b4g _start_b5g _stop_b4g _stop_b5g _time _value_b4g _value_b5g carrier_b4g carrier_b5g event_type_b4g event_type_b5g host_b4g host_b5g serial_number_b4g serial_number_b5g
band band cellular cellular 2021-11-17T05:43:26.208481765Z 2021-11-17T05:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:42:54.351129493Z 7 78 4g 5g cellular cellular cmp-swarm7 cmp-swarm7 SERIAL SERIAL
band band cellular cellular 2021-11-17T05:43:26.208481765Z 2021-11-17T05:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:25.880028651Z 3 78 4g 5g cellular cellular cmp-swarm8 cmp-swarm8 SERIAL SERIAL
band band cellular cellular 2021-11-17T05:43:26.208481765Z 2021-11-17T05:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:25.880028651Z 3 78 4g 5g cellular cellular cmp-swarm8 cmp-swarm8 SERIAL SERIAL
band band cellular cellular 2021-11-17T05:43:26.208481765Z 2021-11-17T05:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:42:14.850732429Z 7 78 4g 5g cellular cellular cmp-swarm7 cmp-swarm7 SERIAL SERIAL
band band cellular cellular 2021-11-17T05:43:26.208481765Z 2021-11-17T05:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:25.880028651Z 7 78 4g 5g cellular cellular cmp-swarm8 cmp-swarm8 SERIAL SERIAL
band band cellular cellular 2021-11-17T05:43:26.208481765Z 2021-11-17T05:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:25.880028651Z 7 78 4g 5g cellular cellular cmp-swarm8 cmp-swarm8 SERIAL SERIAL
band band cellular cellular 2021-11-17T05:43:26.208481765Z 2021-11-17T05:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:43:26.208481765Z 2021-11-17T06:42:41.250671663Z 7 78 4g 5g cellular cellular cmp-swarm7 cmp-swarm7 SERIAL SERIAL
Output from band_join_experimental
--- 1
_field _measurement _start _stop _time _value carrier event_type host lv rv serial_number
band cellular 2021-11-17T05:44:01.80917651Z 2021-11-17T06:44:01.80917651Z 2021-11-17T06:43:56.350752262Z 78 5g cellular cmp-swarm7 78 7 SERIAL
band cellular 2021-11-17T05:44:01.80917651Z 2021-11-17T06:44:01.80917651Z 2021-11-17T06:43:25.880028651Z 78 5g cellular cmp-swarm8 78 3 SERIAL
band cellular 2021-11-17T05:44:01.80917651Z 2021-11-17T06:44:01.80917651Z 2021-11-17T06:43:41.050646745Z 78 5g cellular cmp-swarm7 78 7 SERIAL
band cellular 2021-11-17T05:44:01.80917651Z 2021-11-17T06:44:01.80917651Z 2021-11-17T06:43:25.880028651Z 78 5g cellular cmp-swarm8 78 7 SERIAL
band cellular 2021-11-17T05:44:01.80917651Z 2021-11-17T06:44:01.80917651Z 2021-11-17T06:43:43.350979446Z 78 5g cellular cmp-swarm7 78 7 SERIAL