Sql-Server

如何在標準 SQL 或 T-SQL 中生成 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, … 系列？

  • November 20, 2021

給定兩個數字n和m，我想生成一系列表格

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

並重複m幾次。

例如，對於n = 3and m = 4，我想要以下 24 個數字的序列：

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

我知道如何通過以下兩種方法之一在 PostgreSQL 中實現此結果：

使用以下查詢，它使用該generate_series函式，以及一些技巧來保證順序是正確的：

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 ;

…或使用帶有伴隨和嵌套循環的函式用於相同目的：

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 ;

我怎麼可能在標準 SQL 或 Transact-SQL / SQL Server 中做同樣的事情？

TL; 博士

這是一個很長的方法，所以我先把最好的（即我最快的）方法放在這裡。它使用INTARRAY擴展 - 對於 340 和 570 的參數，它需要 21ms *。第二好的（22 ms - 相同的參數）只使用標準的 PostgreSQL 結構。如果其他人提出（a）更快的方法，我會將它們放在這裡並“退休”我的！

  • 8 GB RAM，Intel i5 第 11 代 CPU，四核 - 8 執行緒，NVMe 256 GB 驅動器

</TL;DR>

介紹：

這個問題引起了我的興趣（+1），我思考了

  • a) 如何回答和
  • b) 答案如何概括？

下面的所有程式碼都可以在各種小提琴中找到 - 每個方法/​​貢獻者。

有趣的是，到目前為止，沒有人回答過（10 個答案 - 以及一些質量非常好的 SQL/程式啟動！）使用ARRAYs （教程），我相信這在這種情況下非常有幫助。

一般來說，我的方法是將第一個系列生成為一個 ARRAY ( {1, 2,.. n, n,..2, 1})，然後生成m這些系列以完成整個任務。

我採取了三五種方法：

  • 第一個是 PostgreSQL 特定的，使用GENERATE_SERIES()( tutorial ) 和ARRAYs。還有一個函式呼叫可以替換為RECURSIVE CTE( "RCTE"- 參見教程)。
  • 第二個（也是 PostgreSQL 特定的）將 an與對和sRCTE的呼叫結合在一起。可以替換為-請參閱解決方案 3。GENERATE_SERIES()``ARRAY``GENERATE_SERIES()``RCTE
  • 第三種解決方案是"Pure SQL"並且應該適用於任何支持 s 的 RDBMS （例如RCTESQL Server ） - 也可以使用表（即序列） 在 dba 聊天室討論之後，我已經刪除了對s 用於構造序列的要求.numbers``RCTE
  • UNNEST()那麼有兩個“快速”的解決方案依賴於保持秩序的事實。

準備步驟：

根據問題，我使用了一個名為param儲存值（3& 5）的表 - 這些顯然可以更改。此外，對於基準測試步驟（見下文），我將這個參數表用於所有測試的查詢，以便有一個公平的競爭環境。如上所述，numbers還允許使用序列表。

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

第一種方法如下：

方法 1 - GENERATE_SERIES（小提琴）：

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

結果：

the_first_series
              1
              2
              3
              3
              2
              1

第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 &lt; (SELECT n * 2 FROM param)
)
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param);

結果（相同）：

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

第 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 &lt; (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;

結果：

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}

最後：

--
--  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 &lt; (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;

結果：

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

方法 2 - 遞歸 CTE（小提琴）：

在這裡，我通過在同一個 RCTE 中建構所需的序列及其編號方案，設法“用一塊石頭殺死兩隻鳥”，如下所示：

--
-- 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 &lt; (SELECT n FROM param) THEN (i + 1)
         WHEN cnt = (SELECT n FROM param) THEN cnt
         WHEN cnt &gt; (SELECT n FROM param) THEN 6 - i
       END
   )
 ),
 ARRAY_APPEND(cnt_arr, cnt + 1)
 FROM cte_fill_array
 WHERE cnt &lt; 2 * (SELECT n FROM param)
)
SELECT i, cnt, val_arr, cnt_arr FROM cte_fill_array;

結果：

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}

我們只想要最後一條記錄，因此我們使用CARDINALITY()函式選擇它 - 其中等於 ( n * 2) 是最後一條記錄（步驟未單獨顯示）。

最後一步 -有關更多詳細資訊，請參閱小提琴

--
--  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 &lt; (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;

結果（與其他相同）：

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

存在一個更簡單（和恕我直言）更優雅的解決方案 - 使用如下GENERATE_SUBSCRIPTS()函式（解釋）：

WITH RECURSIVE cte (i) AS
(
 SELECT 1 AS i, 1 AS cnt
 UNION ALL
 SELECT 
   CASE
     WHEN cnt &lt; (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 &lt; 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;

結果（相同）：

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

方法 3 - 純 SQL（小提琴）：

最後一步：

由於上面的所有程式碼都以一種或另一種形式出現在上面，所以我只包括最後一步。沒有使用 PostgreSQL 特定功能，它也適用於 SQL Server 2019 (Linux fiddle ) - 也適用於 2016 - 所有版本。

WITH RECURSIVE cte (i, cnt) AS
(
 SELECT 1 AS i, 1 AS cnt
 UNION ALL
 SELECT 
   CASE
     WHEN cnt &lt; (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 &lt; 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;

結果（相同）：

i
1
2
3
3

第四種解決方案（和clubhouse leader!）（小提琴）：

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;

與所有其他結果相同。

第五個解決方案（榮譽獎）（小提琴）：

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

與所有其他結果相同。

第 6 個解決方案（另一個 scorcher！-使用 INTARRAY 擴展小提琴）：

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

結果一樣！

基準測試：

我對所有 PostgreSQL 解決方案進行了基準測試。

我已盡最大努力在這些基準測試中保持公平——我在我的 SQL 中使用了一個參數表(3, 5)（也(34, 57)和(340, 570)at ）。(home)對於那些需要一個number表（即一個序列）的查詢，經過討論，我已經將它包含在那些需要它的查詢中。我對此並不完全確定，因為顧問經常被禁止創建單獨的表格，無論多麼微不足道，但這似乎已成為共識！

如果您對任何測試不滿意，請告訴我，我很樂意重新執行它們！

我用於db&lt;&gt;fiddle測試並且通常的警告適用 - 我不知道在任何給定時刻該伺服器上正在執行什麼 - 我為每個解決方案平均執行了幾次（大部分結果都在〜彼此的 10% - 丟棄明顯的異常值（更長，而不是更短的時間）。

有人向我指出（無論如何都知道）3 和 5 不是很大的數字 - 我確實嘗試為每個參數使用低 100，但執行在 db<>fiddle.uk 上一直失敗，但我只想說所有的執行都非常一致，只有 ~ +- 10% 的變化。

帶有 a 的第二次讀數適用於 34 和 57 的值 - 請隨意嘗試。

通過(home)測試，我(340, 570)在 8GB 機器（i5 - 第 10 代，NVMe 256GB）上使用了參數 - 沒有其他執行 - 變異數 v. 低 ~ 1/2%！

  • Vérace's (Another scorcher using INTARRAY!)第六解決方案（小提琴）（0.110ms）/ 0.630 ms /21.5 ms（家庭）- new leader！
  • Vérace（前俱樂部會所負責人）第 4 解決方案（小提琴）0.120 ms/ 0.625 ms /22.5 ms（家庭）
  • Vérace 的（榮譽獎）第 5 個解決方案（小提琴）（0.95 毫秒/0.615 （下降！） /26 毫秒（家庭）
  • Vérace GENERATE_SERIES SQL 方法（小提琴）：0.195 ms/ 3.1 ms /140ms（家庭）
  • Vérace RECURSIVE CTE SQL 方法（小提琴）：0.200 ms/ 2.9 ms /145m (home)
  • Vérace GENERATE_SUBSCRIPTS() SQL 方法（小提琴）：0.110 ms/ 2.75 ms /130ms (home)
  • Vérace“純 SQL”方法（小提琴）：0.134 ms/ 2.85ms /190ms（家庭）
  • OP的SQL方法（小提琴）：12.50 ms/ 18.5ms /190ms (home)
  • OP的PL/pgSQL函式方法（小提琴）：0.60 ms/ 0.075ms /86ms (home)
  • ypercube 的 SQL 方法 ( fiddle ): 0.175 ms, / 4.3 ms /240 ms (home)
  • ypercube 的替代方法（小提琴）：0.090 ms/ 0.95 ms /36ms（家庭）
  • Erwin Brandtstetter 的 SQL 方法（小提琴）：2.15 ms / 3.65 ms /160ms（home）（沒有 ORDER BY - home的情況下， 160 下降到 ~ 100 ）
  • Erwin Brandtstetter 的函式法（小提琴）：0.169 ms/ 2.3 ms /180 ms (home)
  • 如果參數更改， Abelisto 的 SQL 方法（小提琴）0.145/失敗？
  • Evan Carroll 的 SQL 方法（小提琴） 0.125 ms/ 1.1ms /45ms (home)
  • McNet的PL/pgSQL方法（fiddle）0.075 ms/ 0.075 ms /125ms（家）

再一次，我重申（冒著重複自己的風險（多次！:-)）），如果你對你的基準不滿意，請告訴我，我會在這裡包括任何修改——我只是強調我真的很感興趣公平地說，實際上從這個過程中學習 - 獨角獸點都很好，但我的首要任務是增加我的（我們的）知識庫！

PostgreSQL 的原始碼略高於我的工資等級，但我相信 使用GENERATE_SERIES和ARRAYs 的操作可以保持順序——這WITH ORDINALITY意味著（我認為）——即使不注意排序也能得出正確的答案（儘管這不是保證）！@ErwinBrandstetter說：

  • 我添加了 ORDER BY 以保證請求的訂單。對於目前版本或 Postgres，它也可以在沒有 ORDER BY 的情況下用於簡單查詢 - 但不一定在更複雜的查詢中！這是一個實現細節（它不會改變），但不是 SQL 標準規定的。

我的理解是ARRAYs 在 PostgreSQL 中很快，因為大部分後端程式碼都是通過它們實現的——但正如我所說，我並不是真正的C專家。

目前排名（截至 2021 年 10 月 27 日 13:50 UTC）是：

  • Vérace 第一，第二和第三，
  • 超立方體 4 號，
  • 埃文卡羅爾 5
  • 其他領域…

我發現Erwin Brandstetter ( 1 ) 和a_horse_with_no_name (2) 在 ARRAY 上的這些文章非常有幫助！我發現有幫助的其他內容如下（1、2）。

引用自：https://dba.stackexchange.com/questions/160354

相關問答

Sql-Server

MS SQL Server 是否支持“功能索引”？

  • December 21, 2021
檢視問答
Sql-Server

如何從 SQL Server 查詢中的 start_date 和 duration 計算 end_date

  • August 25, 2021
檢視問答
Sql-Server

在沒有函式的情況下以相同的順序拆分兩個分隔的字元串

  • November 23, 2020
檢視問答
Sql-Server

接收字元輸入並返回日期格式的函式（輸入不正確）

  • June 28, 2020
檢視問答
Sql-Server

儘管定義了 RETURN VARCHAR(10)，但使用者定義的函式僅返回第一個字元？

  • June 17, 2020
檢視問答
Sql-Server

“表值函式”是否強制計劃進入記憶體？

  • May 11, 2020
檢視問答