14

Given two numbers n and m, I want to generate a series of the form

1, 2, ..., (n-1), n, n, (n-1), ... 2, 1

and repeat it m times.

For instance, for n = 3 and m = 4, I want a sequence of the following 24 numbers:

1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1
----------------  ----------------  ----------------  ----------------

I know how to achieve this result in PostgreSQL by either of two methods:

Using the following query, which uses the generate_series function, and a few tricks to guarantee that the order is the right one:

WITH parameters (n, m) AS
(
    VALUES (3, 5)
)
SELECT 
    xi
FROM
(
    SELECT
        i, i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
    UNION ALL
    SELECT
        i + parameters.n, parameters.n + 1 - i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
) AS s0 
CROSS JOIN 
    generate_series (1, (SELECT m FROM parameters)) AS x(j)
ORDER BY
    j, i ;

... or use a function for the same purpose, with adjoint and nested loops:

CREATE FUNCTION generate_up_down_series(
    _elements    /* n */ integer,
    _repetitions /* m */ integer)
RETURNS SETOF integer AS
$BODY$
declare
    j INTEGER ;
    i INTEGER ;
begin
    for j in 1 .. _repetitions loop
        for i in         1 .. _elements loop
              return next i ;
        end loop ;
        for i in reverse _elements .. 1 loop
              return next i ;
        end loop ;
    end loop ;
end ;
$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT ;

How could I possibly do the equivalent in either standard SQL or in Transact-SQL / SQL Server?

0

13 Answers 13

11
+250

TL;DR

This is a long one, so I'm putting the best (i.e. fastest of my) methods here first. It makes use of the INTARRAY extension - for parameters of 340 and 570, it takes 21ms*. The second best (22 ms - same parameters) only uses standard PostgreSQL constructs. If anyone else comes up with (a) faster method(s), I will put them here and "retire" mine!

* 8 GB RAM, Intel i5 11th gen CPU, Quad core - 8 threads, NVMe 256 GB drive

</TL;DR>

Introduction:

This question intrigued me (+1) and I pondered

  • a) how to answer it and

  • b) how could the answer be generalised?

All of the code below can be found in the various fiddles - per method/contributor.

Interestingly, nobody who's answered so far (10 answers - and some very good quality SQL/programming to boot!) has made use of ARRAYs (tutorial), which I believe are very helpful in this case.

In a general sense, my approach has been to generate the first series as an ARRAY ({1, 2,.. n, n,..2, 1}) and then generate m of these for the complete task.

I took three five approaches:

  • the first is PostgreSQL specific, making use of GENERATE_SERIES() (tutorial) and ARRAYs. There's also a function call that can be replaced with a RECURSIVE CTE ("RCTE" - see tutorial).

  • the second (also PostgreSQL specific) combines an RCTE with a call to GENERATE_SERIES() and ARRAYs. The GENERATE_SERIES() can be replaced with an RCTE - see solution 3.

  • the third solution is "Pure SQL" and should work on any RDBMS that supports RCTEs (SQL Server for example) - can also use a numbers table (i.e. a sequence) Following discussions on the dba chatroom, I have removed the requirement for RCTEs to be used to construct sequences.

  • then there are two "speedy" solutions which rely on the fact that UNNEST() preserves order.

Preparation step:

I used a table called param to store the values (3 & 5) as per the question - these can obviously be changed. Also, for the benchmarking steps (see below), I used this param table for all queries tested so that there would be an even playing field. As mentioned above, a numbers sequence table is also permitted.

--
-- Setup of parameters...
--

CREATE TABLE param(n INT, m INT);
INSERT INTO param (VALUES(3, 5));
SELECT * FROM param;


--
-- Setup of numbers
--

CREATE TABLE numbers (n INT);
INSERT INTO numbers
SELECT GENERATE_SERIES(1, ((SELECT m FROM param))); 
SELECT * FROM numbers LIMIT 5;

The first method is as follows:

Method 1 - GENERATE_SERIES (fiddle):

Step 1:

--
-- Step 1
--
SELECT
  GENERATE_SERIES(1, (SELECT n FROM param)) AS the_first_series
UNION ALL
SELECT 
  GENERATE_SERIES((SELECT n FROM param), 1, -1);

Result:

the_first_series
               1
               2
               3
               3
               2
               1

Step 2:

--
--  Two possible Step 2s using a PL/pgSQL function or an RCTE
--
CREATE OR REPLACE FUNCTION fill_array_with_seq(the_array anyarray, seq_num INT)
RETURNS ANYARRAY LANGUAGE PLpgSQL AS $$
DECLARE
BEGIN
  FOR i IN 1..seq_num LOOP
    the_array[i] := i;
    i = i + 1;
  END LOOP;
  RETURN the_array;
end $$;

SELECT fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param));

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param);

Results (same):

fill_array_with_seq
      {1,2,3,4,5,6}
              f_arr
      {1,2,3,4,5,6}

Step 3:

--
-- Step 3
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT 
  ROW_NUMBER() OVER () AS rn,
  (
    SELECT f_arr FROM cte_fill_arr
    WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
  ),
  
  -- could use
  --
  -- fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param))
  --
  
  ARRAY
  (
    SELECT 
      GENERATE_SERIES(1, (SELECT n FROM param))
    UNION ALL
    SELECT 
      GENERATE_SERIES((SELECT n FROM param), 1, -1)
  ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x;

Result:

rn         f_arr             arr
 1  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 2  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 3  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 4  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 5  {1,2,3,4,5,6}   {1,2,3,3,2,1}

Finally:

--
--  Steps 4 & 5 - separate subquery not shown
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT the_series FROM
(
  SELECT 
    ROW_NUMBER() OVER () AS rn,
    UNNEST
    (
      (
        SELECT f_arr FROM cte_fill_arr
        WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
      )
    ) AS seq,  
    UNNEST
    (
      ARRAY
      (
        SELECT 
          GENERATE_SERIES(1, (SELECT n FROM param))
        UNION ALL
        SELECT 
          GENERATE_SERIES((SELECT n FROM param), 1, -1)
      )
    ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x
  ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;

Result:

the_series
         1
         2
         3
         3
         2
         1
         1
         2
...
... snipped for brevity
...

Method 2 - Recursive CTE (fiddle):

Here, I managed to "kill two birds with one stone" by constructing both the desired sequence and its numbering scheme in the same RCTE as follows:

--
-- Step 1
--
WITH RECURSIVE cte_fill_array AS -- (cnt, val_arr, cnt_arr) AS
(
  SELECT 1 AS i, 1 AS cnt, ARRAY[1] AS val_arr, ARRAY[1] AS cnt_arr
  UNION ALL
  SELECT i + 1, cnt + 1, 
  ARRAY_APPEND
  (
    val_arr,
    (
      SELECT 
        CASE
          WHEN cnt < (SELECT n FROM param) THEN (i + 1)
          WHEN cnt = (SELECT n FROM param) THEN cnt
          WHEN cnt > (SELECT n FROM param) THEN 6 - i
        END
    )
  ),
  ARRAY_APPEND(cnt_arr, cnt + 1)
  FROM cte_fill_array
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT i, cnt, val_arr, cnt_arr FROM cte_fill_array;

Result:

i   cnt val_arr cnt_arr
1   1   {1} {1}
2   2   {1,2}   {1,2}
3   3   {1,2,3} {1,2,3}
4   4   {1,2,3,3}   {1,2,3,4}
5   5   {1,2,3,3,2} {1,2,3,4,5}
6   6   {1,2,3,3,2,1}   {1,2,3,4,5,6}

We only want the last record, so we select this by using the CARDINALITY() function - where that is equal to (n * 2) is the last record (step not shown individually).

Final step - see fiddle for more detail

--
--  Steps 2 - end
--
WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT arr AS the_series FROM
(
  SELECT 
    ROW_NUMBER() OVER () AS rn,
    UNNEST
    (
      (
        SELECT f_arr FROM cte_fill_arr
        WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
      )
    ) AS seq,  
    UNNEST
    (
      ARRAY
      (
        SELECT 
          GENERATE_SERIES(1, (SELECT n FROM param))
        UNION ALL
        SELECT 
          GENERATE_SERIES((SELECT n FROM param), 1, -1)
      )
    ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x
  ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;

Result (same as others):

the_series
         1
         2
         3
         3
...
... snipped for brevity
...

A simpler, (and IMHO) more elegant solution exists - using the GENERATE_SUBSCRIPTS() function (explanation) as follows:

WITH RECURSIVE cte (i) AS
(
  SELECT 1 AS i, 1 AS cnt
  UNION ALL
  SELECT 
    CASE
      WHEN cnt < (SELECT n FROM param) THEN (i + 1)
      WHEN cnt = (SELECT n FROM param) THEN cnt
      ELSE i - 1
    END AS i,
    cnt + 1
  FROM cte
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT the_arr
FROM
(
  SELECT 
    x, 
    UNNEST(ARRAY(SELECT i FROM cte))    AS the_arr, 
    GENERATE_SUBSCRIPTS(ARRAY(SELECT i FROM cte), 1) AS ss

  FROM GENERATE_SERIES(1, (SELECT m FROM param)) AS t(x)
) AS s
ORDER BY x, ss;

Result (same):

the_series
1
2
3
3
...
... snipped for brevity
...

Method 3 - Pure SQL (fiddle):

Final step:

Since all of the code below has been seen above in one form or another, I'm just including the final step. No PostgreSQL specific functionality has been used and it also works under SQL Server 2019 (Linux fiddle) - also works back to 2016 - all versions.

WITH RECURSIVE cte (i, cnt) AS
(
  SELECT 1 AS i, 1 AS cnt
  UNION ALL
  SELECT 
    CASE
      WHEN cnt < (SELECT n FROM param) THEN (i + 1)
      WHEN cnt = (SELECT n FROM param) THEN cnt
      ELSE i - 1
    END AS i,
    cnt + 1
  FROM cte
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT 
  n.n, c.i, c.cnt
FROM 
  cte c
CROSS JOIN numbers n
ORDER BY n.n, c.cnt;

Result (same):

i
1
2
3
3

4th solution (and clubhouse leader!) (fiddle):

SELECT UNNEST(arr)
FROM
(
  SELECT arr, GENERATE_SERIES(1, (SELECT m FROM param)) AS gs
  FROM
  (
    SELECT 
    ARRAY
    (
      SELECT x 
      FROM GENERATE_SERIES(1, (SELECT n FROM param)) x
      UNION ALL
      SELECT x
      FROM GENERATE_SERIES((SELECT n FROM param), 1, -1) x
    ) AS arr
  ) AS t
) AS s;

Same result as for all the others.

5th solution (Honourable mention) (fiddle):

SELECT
  UNNEST
  (
    ARRAY_CAT
    (
      ARRAY
      (
        SELECT GENERATE_SERIES(1, (SELECT n FROM param))::INT
      ), 
      ARRAY
      (
        SELECT GENERATE_SERIES((SELECT n FROM param), 1, -1)::INT
      )
    )
  ) FROM GENERATE_SERIES(1, (SELECT m FROM param));

Same result as for all the others.

6th Solution (another scorcher! - uses the INTARRAY extension fiddle):

WITH cte AS
(
  SELECT
    ARRAY(SELECT GENERATE_SERIES(1, (SELECT n FROM param))) AS arr
)
SELECT UNNEST 
(
  (
    SELECT ARRAY_CAT(c.arr, SORT(c.arr, 'DESC'))
    FROM cte c
  )
) FROM GENERATE_SERIES(1, (SELECT m FROM param));

Same result!

Benchmarking:

I benchmarked all of the PostgreSQL solutions.

I have done my utmost to be fair in these benchmarks - I used a param table (3, 5) (also (34, 57) and (340, 570) at (home)) in my SQL. For those queries requiring a number table (i.e. a sequence), after discussion, I have included it for those queries requiring it. I'm not entirely sure about this, since consultants are frequently forbidden from creating separate tables, no matter how trivial, but this appears to have been the consensus!

Please let me know if you are unhappy with any of the tests and I'll gladly rerun them!

I used db<>fiddle for the tests and the usual caveats apply - I don't know what else is running on that server at any given moment in time - I took an average of several runs for each solution (the vast bulk of the results were within ~ 10% of each other - discarded obvious outliers (longer, not shorter times).

It was pointed out to me (knew anyway) that 3 & 5 aren't very large numbers - I did try with low 100's for each parameter, but the runs kept failing on db<>fiddle.uk, but I would just say that the all of the runs were remarkably consistent with each other, only varying by ~ +- 10%.

The second reading runs with a are for values of 34 & 57 - feel free to try yourselves.

With the (home) tests, I used params of (340, 570) on an 8GB machine (i5 - 10th gen, NVMe 256GB) - nothing else running - variance v. low ~ 1/2%!

  • Vérace's (Another scorcher using INTARRAY!) 6th solution (fiddle) (0.110ms)/0.630 ms/21.5 ms (home) - new leader!

  • Vérace's (ex-Clubhouse leader) 4th solution (fiddle) 0.120 ms/0.625 ms/22.5 ms (home)

  • Vérace's (Honourable mention) 5th solution (fiddle) (0.95ms/0.615 (goes down!)/26 ms (home)

  • Vérace GENERATE_SERIES SQL method (fiddle): 0.195 ms/3.1 ms/140ms (home)

  • Vérace RECURSIVE CTE SQL method (fiddle): 0.200 ms/2.9 ms/145m (home)

  • Vérace GENERATE_SUBSCRIPTS() SQL method (fiddle): 0.110 ms/2.75 ms/130ms (home)

  • Vérace "Pure SQL" method (fiddle): 0.134 ms/2.85ms/190ms (home)

  • OP's SQL method (fiddle): 12.50 ms/18.5ms/190ms (home)

  • OP's PL/pgSQL function method (fiddle): 0.60 ms/0.075ms/86ms (home)

  • ypercube's SQL method (fiddle): 0.175 ms, /4.3 ms /240 ms (home)

  • ypercube's alternative method (fiddle): 0.090 ms/0.95 ms/36ms (home)

  • Erwin Brandtstetter's SQL method (fiddle): 2.15 ms /3.65 ms/160ms (home) (160 drops to ~ 100 without the ORDER BY - home)

  • Erwin Brandtstetter's function method (fiddle): 0.169 ms/2.3 ms/180 ms (home)

  • Abelisto's SQL method (fiddle) 0.145/fails if params changed?

  • Evan Carroll's SQL method (fiddle) 0.125 ms/1.1ms/45ms (home)

  • McNet's PL/pgSQL method (fiddle) 0.075 ms/ 0.075 ms/125ms (home)

Again, I reiterate (at the risk of repeating myself (multiple times! :-) )), if you are unhappy with your benchmark, please let me know and I will include any amendments here - I would just stress that I am genuinely interested in fairness and actually learning from this process - unicorn points are all very well, but my priority is on increasing my (our) knowledge-base!

The source code of PostgreSQL is a bit above my pay grade, but I believe that operations with GENERATE_SERIES and ARRAYs preserve order - the WITH ORDINALITY implies this (again, I think) - the correct answers come up even without paying attention to ordering (although this not a guarantee)! @ErwinBrandstetter says that:

  • I added ORDER BY to guarantee the requested order. With current versions or Postgres it also works without ORDER BY for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not mandated by the SQL standard.

My understanding is that ARRAYs are fast in PostgreSQL because much of the backend code is implemented through them - but as I said, I'm not really a C expert.

Current standings (as of 2021-10-27 13:50 UTC) are:

  • Vérace 1st, 2nd & 3rd,
  • ypercube 4th,
  • Evan Carroll 5th
  • the rest of the field...

I found these posts on ARRAYs from Erwin Brandstetter (1) & a_horse_with_no_name (2) very helpful! Other's that I found helpful were as follows (1, 2).

1
  • EXPLAIN (ANALYZE, BUFFERS, VERBOSE) is a good way to get details for the query plan and execution. But it adds considerable overhead, which matters most for cheap operations. To measure raw performance you'd rather use EXPLAIN (ANALYZE, TIMING OFF) or some other method as listed here: stackoverflow.com/a/9065976/939860 I'd consider "4. Precise manual measurement with clock_timestamp()". Won't change the ranking much, just produce times closer to reality. Oct 27, 2021 at 20:18
12

Postgres

You can make it work with a single generate_series() and basic math (see mathematical functions).

Wrapped into a simple SQL function:

CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
  RETURNS SETOF int
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END
FROM  (
   SELECT n2m, n2m % (n*2) AS n2
   FROM   generate_series(0, n*2*m - 1) n2m
   ) sub
ORDER  BY n2m
$func$;

Call:

SELECT * FROM generate_up_down_series(3, 4);

Generates the desired result. n and m can be any integer where n*2*m does not overflow int4.

How?

In the subquery:

  • Generate the desired total number of rows (n*2*m), with a simple ascending number. I name it n2m. 0 to N-1 (not 1 to N) to simplify the following modulo operation.

  • Take it % n*2 (% is the modulo operator) to get a series of n ascending numbers, m times. I name it n2.

In the outer query:

  • Add 1 to lower half (n2 < n).

  • For the upper half (n2 >= n) mirror of the lower half with n*2 - n2.

  • I added ORDER BY to guarantee the requested order. With current versions of Postgres it also works without ORDER BY for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not mandated by the SQL standard.

Unfortunately, generate_series() is Postgres specific and not standard SQL, as has been commented. But we can reuse the same logic:

Standard SQL

You can generate the serial numbers with a recursive CTE instead of generate_series(), or, more efficiently for repeated use, create a table with serial integer numbers once. Anyone can read, noone can write to it!

CREATE TABLE int_seq (i integer);

WITH RECURSIVE cte(i) AS (
   SELECT 0
   UNION ALL
   SELECT i+1 FROM cte
   WHERE  i < 20000  -- or as many you might need!
   )
INSERT INTO int_seq
SELECT i FROM cte;

Then, the above SELECT becomes even simpler:

SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END AS x
FROM  (
   SELECT i, i % (n*2) AS n2
   FROM   int_seq
   WHERE  i < n*2*m  -- remember: 0 to N-1
   ) sub
ORDER  BY i;
0
7

In Postgres, it's easy using the generate_series() function:

WITH 
  parameters (n, m) AS
  ( VALUES (3, 5) )
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p, 
    generate_series(1, p.n) AS gn (i),
    generate_series(1, 2)   AS g2 (i),
    generate_series(1, p.m) AS gm (i)
ORDER BY
    gm.i, g2.i, gn.i ;

In standard SQL - and assuming that there is a reasonable limit on the size of the parameters n, m, i.e. less than a million - you can use a Numbers table:

CREATE TABLE numbers 
( n int not null primary key ) ;

fill it with the preferred method of your DBMS:

INSERT INTO numbers (n)
VALUES (1), (2), .., (1000000) ;  -- some mildly complex SQL here
                                  -- no need to type a million numbers

and then use it, instead of generate_series():

WITH 
  parameters (n, m) AS
  ( VALUES (3, 5) )
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p
  JOIN numbers AS gn (i) ON gn.i <= p.n
  JOIN numbers AS g2 (i) ON g2.i <= 2
  JOIN numbers AS gm (i) ON gm.i <= p.m 
ORDER BY
    gm.i, g2.i, gn.i ;
0
6

If you need plain SQL. Theoretically it should to work on the most DBMSs (tested on PostgreSQL and SQLite):

with recursive 
  s(i,n,z) as (
    select * from (values(1,1,1),(3*2,1,2)) as v  -- Here 3 is n
    union all
    select
      case z when 1 then i+1 when 2 then i-1 end, 
      n+1,
      z 
    from s 
    where n < 3), -- And here 3 is n
  m(m) as (select 1 union all select m+1 from m where m < 2) -- Here 2 is m

select n from s, m order by m, i;

Explanation

  1. Generate series 1..n

    Assuming that n=3

    with recursive s(n) as (
      select 1
      union all
      select n+1 from s where n<3
    )
    select * from s;
    

    It is quite simple and could be found in the almost any docs about recursive CTEs. However wee need two instances of each values so

  2. Generate series 1,1,..,n,n

    with recursive s(n) as (
      select * from (values(1),(1)) as v
      union all
      select n+1 from s where n<3
    )
    select * from s;
    

    Here we just doubling the initial value, which has two rows, but the second bunch we need in the reverse order, so we'll introduce the order in a bit.

  3. Before we introduce the order observe that this is also a thing. We can have two rows in the starting condition with three columns each, our n<3 is still a single column conditional. And, we're still just increasing the value of n.

    with recursive s(i,n,z) as (
      select * from (values(1,1,1),(1,1,1)) as v
      union all
      select
        i,
        n+1,
        z 
      from s where n<3
    )
    select * from s;
    
  4. Likewise, we can mix them up a bit, watch our starting condition change here: here we have a (6,2), (1,1)

    with recursive s(i,n,z) as (
      select * from (values(1,1,1),(6,1,2)) as v
      union all
      select
        i,
        n+1,
        z 
      from s where n<3
    )
    select * from s;
    
  5. Generate series 1..n,n..1

    The trick here is to generate the series, (1..n) twice, and then simply change the ordering on the second set.

    with recursive s(i,n,z) as (
      select * from (values(1,1,1),(3*2,1,2)) as v
      union all
      select
        case z when 1 then i+1 when 2 then i-1 end, 
        n+1,
        z 
      from s where n<3
    )
    select * from s order by i;
    

    Here i is order and z is number of the sequence (or half of sequence if you want). So for sequence 1 we are increasing order from 1 to 3 and for sequence 2 we are decreasing the order from 6 to 4. And finally

  6. Multiply the series to m

    (see the first query in the answer)

0
4

In PostgreSQL, this is easy,

CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
RETURNS setof int AS $$
SELECT x FROM (
  SELECT 1, ordinality AS o, x FROM generate_series(1,n) WITH ORDINALITY AS t(x)
  UNION ALL
  SELECT 2, ordinality AS o, x FROM generate_series(n,1,-1) WITH ORDINALITY AS t(x)
) AS t(o1,o2,x)
CROSS JOIN (
  SELECT * FROM generate_series(1,m)
) AS g(y)
ORDER BY y,o1,o2
$$ LANGUAGE SQL;
0
4

If you want a portable solution you need to realize that this is basically a mathematical problem.

Given @n as the highest number of the sequence and @x as the position of the number in that sequence (starting with zero), the following function would work in SQL Server:

CREATE FUNCTION UpDownSequence
(
    @n int, -- Highest number of the sequence
    @x int  -- Position of the number we need
)
RETURNS int
AS
BEGIN
    RETURN  @n - 0.5 * (ABS((2*((@x % (@n+@n))-@n)) +1) -1)
END
GO

You can check it with this CTE:

DECLARE @n int=3;--change the value as needed
DECLARE @m int=4;--change the value as needed

WITH numbers(num) AS (SELECT 0 
                      UNION ALL
                      SELECT num+1 FROM numbers WHERE num+1<2*@n*@m) 
SELECT num AS Position, 
       dbo.UpDownSequence(@n,num) AS number
FROM numbers
OPTION(MAXRECURSION 0)

(Quick explanation: the function uses MODULO() to create a sequence of repeating numbers and ABS() to turn it into a zig-zag wave. The other operations transform that wave to match the desired result.)

0
2

This works in MS-SQL and I think can be modified for any SQL flavor.

declare @max int, @repeat int, @rid int

select @max = 3, @repeat = 4

-- create a temporary table
create table #temp (row int)

--create seed rows
while (select count(*) from #temp) < @max * @repeat * 2
begin
    insert into #temp
    select 0
    from (values ('a'),('a'),('a'),('a'),('a')) as a(col1)
    cross join (values ('a'),('a'),('a'),('a'),('a')) as b(col2)
end

-- set row number can also use identity
set @rid = -1

update #temp
set     @rid = row = @rid + 1

-- if the (row/max) is odd, reverse the order
select  case when (row/@max) % 2 = 1 then @max - (row%@max) else (row%@max) + 1 end
from    #temp
where   row < @max * @repeat * 2
order by row
2

Using only basic Math + - * / and Modulo:

SELECT x
    , s = x % (2*@n) +
         (1-2*(x % @n)) * ( ((x-1) / @n) % 2)
FROM (SELECT TOP(2*@n*@m) x FROM numbers) v(x)
ORDER BY x;

This doesn't require a specific RDBMS.

With numbers being a number table:

...; 
WITH numbers(x) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n0(x)
    CROSS JOIN (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n1(x)
    CROSS JOIN (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n2(x)
)
...

This generate a number table (1-1000) without using a recursive CTE. See Sample. 2nm must be smaller than the number of row in numbers.

Output with n=3 and m=4:

x   s
1   1
2   2
3   3
4   3
5   2
6   1
7   1
8   2
... ...

This version requires a smaller number table (v >= n and v >= m):

WITH numbers(v) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM (VALUES(1), (2), (3), (4), (5), (6), ...) AS n(x)
)
SELECT ord = @n*(v+2*m) + n
    , n*(1-v) + ABS(-@n-1+n)*v
FROM (SELECT TOP(@n) v FROM numbers ORDER BY v ASC) n(n)
CROSS JOIN (VALUES(0), (1)) AS s(v)
CROSS JOIN (SELECT TOP(@m) v-1 FROM numbers ORDER BY v ASC) m(m)
ORDER BY ord;

See Sample.

2

A basic function using iterators.

T-SQL

create function generate_up_down_series(@max int, @rep int)
returns @serie table
(
    num int
)
as
begin

    DECLARE @X INT, @Y INT;
    SET @Y = 0;

    WHILE @Y < @REP
    BEGIN
    
        SET @X = 1;
        WHILE (@X <= @MAX)
        BEGIN
            INSERT @SERIE
            SELECT @X;
            SET @X = @X + 1;
        END
        
        SET @X = @MAX;
        WHILE (@X > 0)
        BEGIN
            INSERT @SERIE
            SELECT @X;
            SET @X = @X -1;
        END
        
        SET @Y = @Y + 1;
    END
    
    RETURN;
end
GO

Postgres

create or replace function generate_up_down_series(maxNum int, rep int)
returns table (serie int) as
$body$
declare
    x int;
    y int;
    z int;
BEGIN

    x := 0;
    while x < rep loop
    
        y := 1;
        while y <= maxNum loop
            serie := y;
            return next;
            y := y + 1;
        end loop;
    
        z := maxNum;
        while z > 0 loop
            serie := z;
            return next;
            z := z - 1;
        end loop;
        
        x := x + 1;
    end loop;

END;
$body$ LANGUAGE plpgsql IMMUTABLE STRICT;
0
2

A way to do it in SQL Server using a recursive cte.

  1. Generate the required number of members in the series (for n=3 and m=4 it would be 24 which is 2nm)

  2. After that using logic in a case expression, you can generate the required series.

Sample Demo

declare @n int=3;--change the value as needed
declare @m int=4;--change the value as needed

with numbers(num) as (select 1 
                      union all
                      select num+1 from numbers where num<2*@n*@m) 
select case when (num/@n)%2=0 and num%@n<>0 then num%@n 
            when (num/@n)%2=0 and num%@n=0 then 1  
            when (num/@n)%2=1 and num%@n<>0 then @n+1-(num%@n)  
            when (num/@n)%2=1 and num%@n=0 then @n
       end as num
from numbers
OPTION(MAXRECURSION 0)

As suggested by @AndriyM .. the case expression can be simplified to

with numbers(num) as (select 0
                      union all
                      select num+1 from numbers where num<2*@n*@m-1) 
select case when (num/@n)%2=0 then num%@n + 1
            when (num/@n)%2=1 then @n - num%@n
       end as num
from numbers
OPTION(MAXRECURSION 0)

Demo

0
1
declare @n int = 5;
declare @m int = 3;
declare @t table (i int, pk int identity);
WITH  cte1 (i) 
AS ( SELECT 1
     UNION ALL
     SELECT i+1 FROM cte1
     WHERE  i < 100  -- or as many you might need!
   )
insert into @t(i) select i from cte1 where i <= @m  order by i
insert into @t(i) select i from @t order by i desc
select t.i --, t.pk, r.pk 
from @t as t 
cross join (select pk from @t where pk <= @n) as r
order by r.pk, t.pk
1
+250

C Extension: pg-generate-up-down-series

To beat the benchmark I decided to resubmit with a C Extension. I published it under the name of pg-generate-up-down-series. It's only slightly faster than Verace's query, and slightly slower than generate_series().

Code

You can see the FULL code for the function in question here, this is a breakdown of the logic/flow,

Datum generate_up_down_series_4(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(generate_up_down_series_4);

typedef struct
{
  # used in ValuePerCallMode for state between calls
} fctx_4;

Datum
generate_up_down_series_4(PG_FUNCTION_ARGS)
{
  
    if (SRF_IS_FIRSTCALL()) {
        # get arguments
    
        ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
    
        # if in the `FROM` clause use Materialized convention
        if ( rsinfo->allowedModes & SFRM_Materialize_Preferred ) {
        }
        # if in the `SELECT` clause do initialize for ValuePerCall convention
        else {
            FuncCallContext *funcctx = SRF_FIRSTCALL_INIT();
            MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
            
            # pre-generate one series and place it in memory

            MemoryContextSwitchTo(oldcontext);
        }

    }

    # On every call do this stuff.
    FuncCallContext *funcctx = SRF_PERCALL_SETUP();

    if ( fctx->current <= fctx->n2 ) {
        # Get data and return a tuple/row       
        SRF_RETURN_NEXT(funcctx, result);
    }
    else {
        # Say we're done.
        SRF_RETURN_DONE(funcctx);
    }

    # this will never be reached, but it's a good convention
    PG_RETURN_NULL();
}

Benchmark

VERACE_QUERY [number to beat]
 count  
--------
 387600
(1 row)

Time: 17.732 ms


XXXXX VALUEPERCALL XXXXX
SELECT count(*) FROM (SELECT generate_series(1,2*340*570)) AS t;
 count  
--------
 387600
(1 row)

Time: 15.784 ms
SELECT count(*) FROM (SELECT generate_up_down_series_evan(340,570)) AS t;
 count  
--------
 387600
(1 row)

Time: 16.612 ms


XXXXX MATERIALIZED XXXXX
SELECT count(*) FROM (SELECT * FROM generate_series(1,2*340*570)) AS t;
 count  
--------
 387600
(1 row)

Time: 45.261 ms
SELECT count(*) FROM (SELECT * FROM generate_up_down_series_evan(340,570)) AS t;
 count  
--------
 387600
(1 row)
0

ORACLE macro:

CREATE OR REPLACE FUNCTION genupdownseq(p_num IN NUMBER, p_cycle IN NUMBER, p_up IN NUMBER DEFAULT 1)
RETURN VARCHAR2
SQL_MACRO
IS 
BEGIN
    RETURN q'{select 
    case when mod(trunc((n-1)/p_cycle)+1 ,2) = p_up
        then 
            mod(n-1,p_cycle)+1
        else
            p_cycle - mod(n-1,p_cycle)
        end as n 
from (
    select level as n from dual connect by level <= p_num
)
}';
END ;
/

select * from genupdownseq(200,10) ;


    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    10
    9
    8
    7
    6
...


select * from genupdownseq(20,5,0) ;

5
4
3
2
1
1
2
3
4
5
5
4
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.