Generate a sequence of records from a base record

Question

Is there a way to expand table 1 into table 2? It is to output each integer between start_no and end_no as a seq_no field, and take the other fields of the original table to form a new table (table 2).

Table 1:

date       source market channel_no start_no end_no err_type
---------- ------ ------ ---------- -------- ------ --------
2022.06.01 src55  SZ     2011       565663   565665 1       
2022.06.01 src55  SZ     2011       565918   565920 1       
2022.06.01 src55  SZ     2011       566010   566012 1       
2022.06.01 src55  SZ     2011       566363   566365 1       
2022.06.01 src55  SZ     2011       566512   566513 1

Table 2:

date       source market channel_no err_type seq_no
---------- ------ ------ ---------- -------- ------
2022.06.01 src55  SZ     2011       1        565663
2022.06.01 src55  SZ     2011       1        565664
2022.06.01 src55  SZ     2011       1        565665
2022.06.01 src55  SZ     2011       1        565918
2022.06.01 src55  SZ     2011       1        565919
2022.06.01 src55  SZ     2011       1        565920
2022.06.01 src55  SZ     2011       1        566010
2022.06.01 src55  SZ     2011       1        566011
2022.06.01 src55  SZ     2011       1        566012
2022.06.01 src55  SZ     2011       1        566363
2022.06.01 src55  SZ     2011       1        566364
2022.06.01 src55  SZ     2011       1        566365
2022.06.01 src55  SZ     2011       1        566512
2022.06.01 src55  SZ     2011       1        566513

score 0 · Answer 1 · answered Sep 20 '22 at 06:51

You can use function each and cj(cross join) to loop through each row of original table and conduct cross join with the sequence data table generated by start_no and end_no, where the cross join returns the Cartesian product of rows from the tables in the join. In the end, you use function unionAll to combine all the intermediate tables into the final output table.

The simulated data, the algorithm, and the results are listed as follows:

Simulated data:

start_no=[565663,565918,566010,566363,566512]
end_no=[565665,565920,566012,566365,566513]
tb=table(take(2022.06.01,5) as date,take(`src55,5) as source,take(`SZ,5) as market,take(2011,5) as channel_no,start_no  , end_no,take(1,5) as err_type);

tb;
date       source market channel_no start_no end_no err_type
---------- ------ ------ ---------- -------- ------ --------
2022.06.01 src55  SZ     2011       565663   565665 1
2022.06.01 src55  SZ     2011       565918   565920 1
2022.06.01 src55  SZ     2011       566010   566012 1
2022.06.01 src55  SZ     2011       566363   566365 1
2022.06.01 src55  SZ     2011       566512   566513 1

The algorithm:

def f(tb,i){
        tt=select date,source,market,channel_no,err_type from tb where rowNo(date)=i
        start_no=tb[i]['start_no']
        end_no=tb[i]['end_no']
        return cj(tt,table(start_no..end_no as seq_no))
        }
tb=unionAll(each(f{tb},1..size(tb)-1),false)

Results：

date       source market channel_no err_type seq_no
---------- ------ ------ ---------- -------- ------
2022.06.01 src55  SZ     2011       1        565663
2022.06.01 src55  SZ     2011       1        565664
2022.06.01 src55  SZ     2011       1        565665
2022.06.01 src55  SZ     2011       1        565918
2022.06.01 src55  SZ     2011       1        565919
2022.06.01 src55  SZ     2011       1        565920
2022.06.01 src55  SZ     2011       1        566010
2022.06.01 src55  SZ     2011       1        566011
2022.06.01 src55  SZ     2011       1        566012
2022.06.01 src55  SZ     2011       1        566363
2022.06.01 src55  SZ     2011       1        566364
2022.06.01 src55  SZ     2011       1        566365
2022.06.01 src55  SZ     2011       1        566512
2022.06.01 src55  SZ     2011       1        566513

Generate a sequence of records from a base record

1 Answers1