Fill in Gaps in series (NOT time series)

[Dlivingston]

I have a table that looks like:

id | low_esn | high_esn
------+---------+----------
101 | 15900 | 15999
102 | 15900 | 15999
103 | 15900 | 15999


what I need to do is convert this to :

id | values
-----+--------
101 | 15900
101 | 15901
101 | 15902
...
101 | 15998
101 | 15999
102 | 15900
102 | 15901
102 | 15902
...
102 | 15998
102 | 15999


I was able to get to the first step of at least getting everything in two columns - but without something like Oracle's CONNECT BY, Postgres's generate_series(), etc. I cannot figure out how to "fill in" the gaps:

Code: Select all

select id, c as values
  from (select id,
			   low_esn c
          from udr_range
		union all
		select id,
			   high_esn c
          from udr_range) subq
order by 1;

Does anyone know how to do this in the SQL environment of Vertica?

[NorbertKrupa]

Do you have a numbers table you could join to?

[scutter]

There’s a CONNECT BY extension for Vertica:

https://github.com/vertica/Vertica-Exte ... _functions

and also in the HP Marketplace somewhere.

Besides using a numbers table as Norbert suggested, you could also create a UDTF to do this. Very each to write in Java and might perform better than a join to the numbers table since it’s just generating numbers.

—Sharon

[Dlivingston]

I think I could get a series of numbers using another table in the DB as something like:

Code: Select all

select row_number() over () as rownum
  from esn
 limit 15999

however, how would you fill in the values?

I *think* something like:

Code: Select all

select id, c as values
  from (select id, low_esn c
          from udr_range
		union all
		select id, high_esn c
          from udr_range) subq
  right outer join
     ....

[JimKnicely]

Maybe something like the following?

Code: Select all

dbadmin=> SELECT * FROM udr_range ORDER BY id;
 id  | low_esn | high_esn
-----+---------+----------
 101 |   15900 |    15999
 102 |   15900 |    15999
 103 |   15900 |    15999
(3 rows)

Code: Select all

dbadmin=> SELECT id, low_esn + rn AS values
dbadmin->   FROM (SELECT id, low_esn, high_esn, (row_number() OVER (PARTITION BY id) - 1) rn
dbadmin(>           FROM udr_range
dbadmin(>          CROSS JOIN system_columns) foo
dbadmin->  WHERE low_esn + rn <= high_esn
dbadmin->  ORDER BY id, low_esn + rn;
 id  | values
-----+--------
 101 |  15900
 101 |  15901
 101 |  15902
 101 |  15903
 101 |  15904
 101 |  15905
 101 |  15906
 101 |  15907
 101 |  15908
 101 |  15909
 101 |  15910
 101 |  15911
 101 |  15912
 101 |  15913
 101 |  15914
 101 |  15915
 101 |  15916
 101 |  15917
 101 |  15918
 101 |  15919
 101 |  15920
 101 |  15921
 101 |  15922
 101 |  15923
 101 |  15924
 101 |  15925
 101 |  15926
 101 |  15927
 101 |  15928
 101 |  15929
 101 |  15930
 101 |  15931
 101 |  15932
 101 |  15933
 101 |  15934
 101 |  15935
 101 |  15936
 101 |  15937
 101 |  15938
 101 |  15939
 101 |  15940
 101 |  15941
 101 |  15942
 101 |  15943
 101 |  15944
 101 |  15945
 101 |  15946
 101 |  15947
 101 |  15948
 101 |  15949
 101 |  15950
 101 |  15951
 101 |  15952
 101 |  15953
 101 |  15954
 101 |  15955
 101 |  15956
 101 |  15957
 101 |  15958
 101 |  15959
 101 |  15960
 101 |  15961
 101 |  15962
 101 |  15963
 101 |  15964
 101 |  15965
 101 |  15966
 101 |  15967
 101 |  15968
 101 |  15969
 101 |  15970
 101 |  15971
 101 |  15972
 101 |  15973
 101 |  15974
 101 |  15975
 101 |  15976
 101 |  15977
 101 |  15978
 101 |  15979
 101 |  15980
 101 |  15981
 101 |  15982
 101 |  15983
 101 |  15984
 101 |  15985
 101 |  15986
 101 |  15987
 101 |  15988
 101 |  15989
 101 |  15990
 101 |  15991
 101 |  15992
 101 |  15993
 101 |  15994
 101 |  15995
 101 |  15996
 101 |  15997
 101 |  15998
 101 |  15999
 102 |  15900
 102 |  15901
 102 |  15902
 102 |  15903
 102 |  15904
 102 |  15905
 102 |  15906
 102 |  15907
 102 |  15908
 102 |  15909
 102 |  15910
 102 |  15911
 102 |  15912
 102 |  15913
 102 |  15914
 102 |  15915
 102 |  15916
 102 |  15917
 102 |  15918
 102 |  15919
 102 |  15920
 102 |  15921
 102 |  15922
 102 |  15923
 102 |  15924
 102 |  15925
 102 |  15926
 102 |  15927
 102 |  15928
 102 |  15929
 102 |  15930
 102 |  15931
 102 |  15932
 102 |  15933
 102 |  15934
 102 |  15935
 102 |  15936
 102 |  15937
 102 |  15938
 102 |  15939
 102 |  15940
 102 |  15941
 102 |  15942
 102 |  15943
 102 |  15944
 102 |  15945
 102 |  15946
 102 |  15947
 102 |  15948
 102 |  15949
 102 |  15950
 102 |  15951
 102 |  15952
 102 |  15953
 102 |  15954
 102 |  15955
 102 |  15956
 102 |  15957
 102 |  15958
 102 |  15959
 102 |  15960
 102 |  15961
 102 |  15962
 102 |  15963
 102 |  15964
 102 |  15965
 102 |  15966
 102 |  15967
 102 |  15968
 102 |  15969
 102 |  15970
 102 |  15971
 102 |  15972
 102 |  15973
 102 |  15974
 102 |  15975
 102 |  15976
 102 |  15977
 102 |  15978
 102 |  15979
 102 |  15980
 102 |  15981
 102 |  15982
 102 |  15983
 102 |  15984
 102 |  15985
 102 |  15986
 102 |  15987
 102 |  15988
 102 |  15989
 102 |  15990
 102 |  15991
 102 |  15992
 102 |  15993
 102 |  15994
 102 |  15995
 102 |  15996
 102 |  15997
 102 |  15998
 102 |  15999
 103 |  15900
 103 |  15901
 103 |  15902
 103 |  15903
 103 |  15904
 103 |  15905
 103 |  15906
 103 |  15907
 103 |  15908
 103 |  15909
 103 |  15910
 103 |  15911
 103 |  15912
 103 |  15913
 103 |  15914
 103 |  15915
 103 |  15916
 103 |  15917
 103 |  15918
 103 |  15919
 103 |  15920
 103 |  15921
 103 |  15922
 103 |  15923
 103 |  15924
 103 |  15925
 103 |  15926
 103 |  15927
 103 |  15928
 103 |  15929
 103 |  15930
 103 |  15931
 103 |  15932
 103 |  15933
 103 |  15934
 103 |  15935
 103 |  15936
 103 |  15937
 103 |  15938
 103 |  15939
 103 |  15940
 103 |  15941
 103 |  15942
 103 |  15943
 103 |  15944
 103 |  15945
 103 |  15946
 103 |  15947
 103 |  15948
 103 |  15949
 103 |  15950
 103 |  15951
 103 |  15952
 103 |  15953
 103 |  15954
 103 |  15955
 103 |  15956
 103 |  15957
 103 |  15958
 103 |  15959
 103 |  15960
 103 |  15961
 103 |  15962
 103 |  15963
 103 |  15964
 103 |  15965
 103 |  15966
 103 |  15967
 103 |  15968
 103 |  15969
 103 |  15970
 103 |  15971
 103 |  15972
 103 |  15973
 103 |  15974
 103 |  15975
 103 |  15976
 103 |  15977
 103 |  15978
 103 |  15979
 103 |  15980
 103 |  15981
 103 |  15982
 103 |  15983
 103 |  15984
 103 |  15985
 103 |  15986
 103 |  15987
 103 |  15988
 103 |  15989
 103 |  15990
 103 |  15991
 103 |  15992
 103 |  15993
 103 |  15994
 103 |  15995
 103 |  15996
 103 |  15997
 103 |  15998
 103 |  15999
(300 rows)

[Dlivingston]

Wow, that is ... SO ... much simpler than the direction I was going.

That will work quite well ... Thank you !

[JimKnicely]

Cool.

FYI, I randomly chose the SYSTEM_COLUMNS system table to help generate the row numbers. Not sure how many rows you may need to generate per ID (in your example it was only 100), but if it's more than the number of rows in your SYSTEM_COLUMNS system table, you'll have to find a bigger table or cross join it to itself.

Something like this:

Code: Select all

SELECT id, low_esn + rn AS values
  FROM (SELECT id, low_esn, high_esn, (row_number() OVER (PARTITION BY id) - 1) rn
          FROM udr_range
         CROSS JOIN system_columns c1
         CROSS JOIN system_columns c2) foo
 WHERE low_esn + rn <= high_esn
 ORDER BY id, low_esn + rn;