I am using FluxQL with influxdb 1.8 and I am attempting to correlate/join records from 2 different tables. The challenge in this case seems to be that these 2 tables have no columns with values in common. So far the closest I have gotten to what I’m looking for is using the map function to add a new column (let’s call it ‘join_column’) with a value of zero (0) for every row in both tables and then I was able to join on that column. However, if table_1 and table_2 each have 10 rows, then the join of these tables ends up with 100 rows. I think this is because ‘join_column’ contains all zeroes.

I am thinking that if there was a way I could use the values 1 to 10 in the join_column field (1 to 10 in table_1 and 1 to 10 in table_2) then when I joined the tables I would get exactly 10 rows of joined data. I have tried to find a way to do this and so far have been unsuccessful.

Ideas:

write a function that increments a global variable and returns increasing values every time it is called. The trouble is, I couldn’t find a way to increment a global variable and my function returned the same number every time.

get the row index somehow and use that as my values for join_column.

So far I haven’t been able to figure out a way to code either one of those in FluxQL. Any help would be greatly appreciated!

Each table in the script above has two rows and when I join on the join_column there are 2 rows total.
If table_1 and table_2 have 10 rows each then I expect the join to have 10 rows. If you join on multiple columns where there are duplicate values.

The example join that you provided above made me realize what I think is one important difference from my situation: the time values themselves in my 2 initial result tables are different because they are coming from 2 different machines. So, if I include “_time” in my join statement as you have done above, I get no results (or I might get a few results if the times happen to match up).

What I am trying to accomplish is to build a result table showing a graph of the time differences (in seconds) between the records in the original 2 tables.

Something like this (where uint_time1 and uint_time_2 are the epoch times in nanoseconds from the original result tables converted to uints):
time1_time2_join = join(tables: {time1: time1, time2:time2}, on: [“join_column”])
|> map(fn: ® => ({
_time: r._time_time1,
time_diff: if r.uint_time_1 >= r.uint_time_2 then (r.uint_time_1 - r.uint_time_2) / uint(v: 1000000000) else uint(v: 0)
}))
|> yield(name: “joined_times”)

Unfortunately, this only works well if I limit my results from the original tables to 1 record each (again, because when join on just join_column I end up with the number of result rows being the product of the number of rows in the original 2 tables because the times themselves don’t match up).

I still think one solution in special case would be to find a way to add a column with an incrementing number to join on but I haven’t been able to find a way to create such a column.