Generate a sequence of numbers for a certain interval in vertica

Question

Generate a sequence of numbers for a certain interval. In postgresql there is a function generate_series. You need to write something similar to sql for vertica

id	low_esn	high_esn
101	10	13
102	5	7

There should be a result:

id	value
101	10
101	11
101	12
101	13
102	5
102	6
102	7

Answer 1

You can use 2 way for it:

1. You can add extension for it: https://github.com/tensquaregames/Vertica-GenerateSeries-Extension
2. You can use answer from this question. vertica generate table with numbers & select prime numbers

I hope that these links help you.

Answer 2

Vertica has the TIMESERIES clause for that, which is considerably more embedded into its SQL engine than the generate_series function of PostgreSQL.

TIMESERIES works on an already existing time series in the data - which can just consist of two rows.

We need to "verticalise" your horizontal representation of start and finish, then convert the integers to timestamps (with microseconds) - and I just do that in the OVER(... ORDER BY) sub-clause of the TIMESERIES clause - to generate a time series spaced by the interval supplied just after the TIMESERIES keyword, out of which we get back the microseconds again:

WITH
indata(           id , low_esn, high_esn) AS (
          SELECT 101 , 10     , 13
UNION ALL SELECT 102 ,  5     ,  7
)
,
-- the TIMESERIES clause needs an initial tuple and a final tuple
borders(id,val) AS (
  SELECT
    id
  , low_esn
  FROM indata
  UNION ALL
  SELECT
    id
  , high_esn
  FROM indata
)
SELECT
  id
, MICROSECOND(ts) AS val
FROM borders
TIMESERIES ts AS '1 microsecond' OVER (
  PARTITION BY id
  ORDER BY TIMESTAMPADD(MICROSECOND,val,TIMESTAMP '2000-01-01'
)
);

id	val
101	10
101	11
101	12
101	13
102	5
102	6
102	7