Biorąc pod uwagę dwie liczby ni mchcę wygenerować serię formularza

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

i powtórz to mrazy.

Na przykład dla n = 3i m = 4chcę ciąg następujących 24 liczb:

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

Wiem, jak osiągnąć ten wynik w PostgreSQL za pomocą jednej z dwóch metod:

Za pomocą następującego zapytania, które korzysta z generate_seriesfunkcji, i kilku sztuczek, aby zagwarantować, że kolejność jest właściwa:

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 ;

... lub użyj funkcji w tym samym celu, z pętlami przylegającymi i zagnieżdżonymi:

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 ;

Jak mogę zrobić ekwiwalent w standardowym SQL lub w Transact-SQL / SQL Server?

W Postgres korzystanie z generate_series()funkcji jest łatwe :

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 ;

W standardowym języku SQL - i przy założeniu, że istnieje rozsądny limit wielkości parametrów n, m, tj. Mniej niż milion - możesz użyć Numberstabeli:

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

wypełnij go preferowaną metodą swojego DBMS:

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

a następnie użyj go zamiast 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 ;

Postgres

Możesz sprawić, by działał z pojedynczą generate_series() i podstawową matematyką (patrz funkcje matematyczne ).

Zawinięte w prostą funkcję SQL:

CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
  RETURNS SETOF int 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$  LANGUAGE sql IMMUTABLE;

Połączenie:

SELECT * FROM generate_up_down_series(3, 4);

Generuje pożądany wynik. n i m może być dowolną liczbą całkowitą, w której n * 2 * m nie przepełnia się int4.

W jaki sposób?

W podzapytaniu:

• Wygeneruj żądaną całkowitą liczbę rzędów ( n * 2 * m ), za pomocą prostej liczby rosnącej. Nazywam to n2m. 0 do N-1 (nie 1 do N ) w celu uproszczenia następującej operacji modulo .

• Weź to % n * 2 ( %jest operatorem modulo), aby uzyskać serię n rosnących liczb, m razy. Nazywam to n2.

W zewnętrznym zapytaniu:

• Dodaj 1 do dolnej połowy ( n2 <n ).

• Dla górnej połowy ( n2> = n ) lustro dolnej połowy z n * 2 - n2 .

• Dodałem, ORDER BYaby zagwarantować żądane zamówienie. W przypadku bieżących wersji lub Postgres działa również bez ORDER BYprostych zapytań - ale niekoniecznie w bardziej złożonych zapytaniach! Jest to szczegół implementacji (i to się nie zmieni), ale nie jest uzasadniony przez standard SQL.

Niestety, generate_series()jak to zostało skomentowane , jest specyficzny dla Postgresa i niestandardowy SQL. Ale możemy ponownie użyć tej samej logiki:

Standardowy SQL

Możesz wygenerować numery seryjne za pomocą rekurencyjnego CTE zamiast generate_series()lub, bardziej efektywnie do wielokrotnego użytku, utworzyć tabelę z numerami seryjnymi liczb całkowitych jeden raz. Każdy może czytać, nikt nie może do niego pisać!

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;

Następnie powyższe SELECTstaje się jeszcze prostsze:

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;

Jeśli potrzebujesz zwykłego SQL. Teoretycznie powinien działać na większości DBMS (testowanych na PostgreSQL i 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;

Wyjaśnienie

1. Wygeneruj serie 1..n

   Przy założeniu, że n=3

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

   Jest to dość proste i można je znaleźć w prawie wszystkich dokumentach na temat rekurencyjnych CTE. Potrzebujemy jednak dwóch wystąpień każdej wartości

2. Wygeneruj serie 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;
   

   Tutaj po prostu podwajamy wartość początkową, która ma dwa wiersze, ale potrzebna jest druga wiązka w odwrotnej kolejności, więc wprowadzimy kolejność za chwilę.

3. Zanim wprowadzimy zamówienie, zwróć uwagę, że to także jest rzecz. Możemy mieć dwa wiersze w stanie początkowym z trzema kolumnami, n<3nadal mamy warunek jednej kolumny. I wciąż tylko zwiększamy wartość 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. Podobnie możemy je trochę pomieszać, obserwuj tutaj zmianę warunków początkowych : tutaj mamy (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. Wygeneruj serie 1..n, n..1

   Sztuczka polega na dwukrotnym wygenerowaniu szeregu (1..n), a następnie zmianie kolejności w drugim zestawie.

   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;
   

   Oto ikolejność i znumer sekwencji (lub połowa sekwencji, jeśli chcesz). Tak więc dla sekwencji 1 zwiększamy porządek z 1 do 3, a dla sekwencji 2 zmniejszamy porządek z 6 do 4. I na koniec

6. Pomnóż serię do m

   (patrz pierwsze zapytanie w odpowiedzi)

Jeśli chcesz przenośnego rozwiązania, musisz zdać sobie sprawę, że jest to w zasadzie problem matematyczny .

Biorąc pod uwagę @n jako najwyższą liczbę sekwencji i @x jako pozycję liczby w tej sekwencji (zaczynając od zera), następująca funkcja będzie działać w 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

Możesz to sprawdzić za pomocą tego 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)

(Szybkie wyjaśnienie: funkcja używa MODULO () do utworzenia sekwencji powtarzających się liczb, a ABS () zamienia ją w falę zygzakowatą. Inne operacje przekształcają tę falę, aby pasowała do pożądanego wyniku.)

W PostgreSQL jest to łatwe,

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;

Działa to w MS-SQL i myślę, że można je modyfikować dla dowolnego smaku 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

Sposób na wykonanie tego w SQL Server przy użyciu rekurencyjnego cte.

1) Wygeneruj wymaganą liczbę elementów w szeregu (dla n = 3 i m = 4 będzie to 24, co oznacza 2 * n * m)

2) Następnie za pomocą logiki w casewyrażeniu można wygenerować wymaganą serię.

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)

Zgodnie z sugestią @AndriyM .. casewyrażenie można uprościć

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

Używając tylko podstawowej matematyki + - * /i 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;

To nie wymaga określonego SGBD.

Z numbersbycia tabeli numer:

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

Generuje to tabelę liczb (1-1000) bez użycia rekurencyjnego CTE. Zobacz próbkę . 2 * n * m musi być mniejsza niż liczba wierszy w liczbach.

Wyjście dla n = 3 i m = 4:

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

Ta wersja wymaga mniejszej tabeli liczb (v> = n oraz 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;

Zobacz próbkę .

Podstawowa funkcja wykorzystująca iteratory.

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;

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