Os procedimentos armazenados impedem a injeção de SQL?

Question

joanolo

Asked: 2017-01-09 15:21:03 +0800 CST2017-01-09 15:21:03 +0800 CST 2017-01-09 15:21:03 +0800 CST

Como gerar uma série 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... no padrão SQL ou T-SQL?

772

Dados dois números ne m, quero gerar uma série da forma

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

e repita várias mvezes.

Por exemplo, para n = 3e m = 4, quero uma sequência dos seguintes 24 números:

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

Eu sei como obter esse resultado no PostgreSQL por um dos dois métodos:

Usando a seguinte consulta, que usa a generate_seriesfunção, e alguns macetes para garantir que a ordem seja a correta:

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 ;

... ou use uma função para o mesmo propósito, com loops adjuntos e aninhados:

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 ;

Como eu poderia fazer o equivalente no SQL padrão ou no Transact-SQL/SQL Server?

12 respostas

Voted

Erwin Brandstetter · Answer 1 · 2017-01-09T19:04:28+08:00

Postgres

Você pode fazê-lo funcionar com uma matemática simples generate_series() e básica (ver funções matemáticas ).

Embrulhado em uma função SQL simples:

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$;

Ligar:

SELECT * FROM generate_up_down_series(3, 4);

Gera o resultado desejado. n e m podem ser qualquer inteiro onde n*2*m não estoura int4.

Como?

Na subconsulta:

Gere o número total desejado de linhas ( n*2*m ), com um número crescente simples. Eu o nomeio n2m. 0 a N-1 (não 1 a N ) para simplificar a seguinte operação de módulo .
Considere % n*2 ( %é o operador de módulo) para obter uma série de n números crescentes, m vezes. Eu o nomeio n2.

Na consulta externa:

Adicione 1 à metade inferior ( n2 < n ).
Para a metade superior ( n2 >= n ) espelho da metade inferior com n*2 - n2 .
Acrescentei ORDER BYpara garantir o pedido solicitado. Com as versões atuais do Postgres também funciona sem ORDER BYa consulta simples - mas não necessariamente em consultas mais complexas! Isso é um detalhe de implementação (e não vai mudar), mas não obrigatório pelo padrão SQL.

Infelizmente, generate_series()é específico do Postgres e não SQL padrão, como foi comentado. Mas podemos reutilizar a mesma lógica:

SQL padrão

Você pode gerar os números de série com um CTE recursivo em vez de generate_series(), ou, de forma mais eficiente para uso repetido, criar uma tabela com números inteiros de série uma vez. Qualquer um pode ler, ninguém pode escrever nele!

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;

Então, o acima SELECTse torna ainda mais simples:

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;

Vérace · Answer 2 · 2021-10-27T05:07:54+08:00

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):

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).

ypercubeᵀᴹ · Answer 3 · 2017-01-09T15:44:44+08:00

No Postgres, é fácil usar a generate_series()função:

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 ;

No SQL padrão - e supondo que haja um limite razoável para o tamanho dos parâmetros n, m, ou seja, menos de um milhão - você pode usar uma Numberstabela:

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

preencha-o com o método preferido do seu DBMS:

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

e, em seguida, usá-lo, em vez de 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 ;

Abelisto · Answer 4 · 2017-01-09T16:03:32+08:00

Se você precisar de SQL simples. Teoricamente deveria funcionar na maioria dos DBMSs (testados em PostgreSQL e 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;

Explicação

Gerar série 1..n

Assumindo quen=3
```
with recursive s(n) as (
  select 1
  union all
  select n+1 from s where n<3
)
select * from s;
```
É bastante simples e pode ser encontrado em quase todos os documentos sobre CTEs recursivos. No entanto, precisamos de duas instâncias de cada valor, então
Gerar série 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;
```
Aqui estamos apenas dobrando o valor inicial, que tem duas linhas, mas o segundo grupo precisamos na ordem inversa, então vamos introduzir a ordem daqui a pouco.
Antes de introduzirmos a ordem, observe que isso também é uma coisa. Podemos ter duas linhas na condição inicial com três colunas cada, nossa n<3ainda é uma condição de coluna única. E ainda estamos apenas aumentando o valor de 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;
```

Da mesma forma, podemos misturá-los um pouco, observar nossa mudança de condição inicial aqui : aqui temos um (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;

Gerar série 1..n,n..1

O truque aqui é gerar a série, (1..n) duas vezes, e simplesmente mudar a ordem no segundo conjunto.
```
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;
```
Aqui iestá a ordem e zo número da sequência (ou metade da sequência, se você quiser). Assim, para a sequência 1 estamos aumentando a ordem de 1 para 3 e para a sequência 2 estamos diminuindo a ordem de 6 para 4. E finalmente
Multiplique a série param

(veja a primeira consulta na resposta)

Evan Carroll · Answer 5 · 2017-01-09T15:29:08+08:00

Evan Carroll

2017-01-09T15:29:08+08:002017-01-09T15:29:08+08:00

No PostgreSQL, isso é fácil,

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;

4

Twinkles · Answer 6 · 2017-01-10T07:01:32+08:00

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.)

McNets · Answer 7 · 2017-01-09T15:55:31+08:00

Uma função básica usando iteradores.

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;

Jules · Answer 8 · 2017-01-09T21:52:09+08:00

Jules

2017-01-09T21:52:09+08:002017-01-09T21:52:09+08:00

Isso funciona no MS-SQL e acho que pode ser modificado para qualquer tipo de SQL.

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

vkp · Answer 9 · 2017-01-10T05:21:18+08:00

Uma maneira de fazer isso no SQL Server usando um cte recursivo.

Gere o número necessário de membros na série (para n = 3 e m = 4, seria 24, que é 2 n m)
Depois disso, usando a lógica em uma caseexpressão, você pode gerar a série necessária.

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)

Conforme sugerido por @AndriyM .. a caseexpressão pode ser simplificada para

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

Julien Vavasseur · Answer 10 · 2017-01-10T07:07:51+08:00

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:

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.

Como gerar uma série 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... no padrão SQL ou T-SQL?

Postgres

Como?

SQL padrão

TL;DR

</TL;DR>

Introduction:

Preparation step:

Method 1 - GENERATE_SERIES (fiddle):

Step 1:

Step 2:

Step 3:

Method 2 - Recursive CTE (fiddle):

Method 3 - Pure SQL (fiddle):

Final step:

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

5th solution (Honourable mention) (fiddle):

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

Benchmarking:

Explicação

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Como gerar uma série 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... no padrão SQL ou T-SQL?

12 respostas

Postgres

Como?

SQL padrão

TL;DR

</TL;DR>

Introduction:

Preparation step:

Method 1 - GENERATE_SERIES (fiddle):

Step 1:

Step 2:

Step 3:

Method 2 - Recursive CTE (fiddle):

Method 3 - Pure SQL (fiddle):

Final step:

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

5th solution (Honourable mention) (fiddle):

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

Benchmarking:

Explicação

relate perguntas

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