Oracle 如何生成連續(xù)的數(shù)字/字符/時(shí)間序列

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        使用層次查詢生成序列
            生成一個(gè)連接的數(shù)字序列
            生成一個(gè)間隔的數(shù)字序列
            生成一個(gè)連續(xù)的字符序列
            生成一個(gè)間隔的時(shí)間序列
        使用表函數(shù)生成序列
            創(chuàng)建模擬的 generate_series 函數(shù)
            使用 generate_series 函數(shù)生成序列
        使用通用表表達(dá)式生成序列
            生成一個(gè)等差數(shù)字序列
            生成一個(gè)等比數(shù)字序列
            生成斐波那契數(shù)列
            生成一個(gè)連續(xù)的字符序列
            生成一個(gè)間隔的時(shí)間序列

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天我們繼續(xù)介紹如何在 Oracle 數(shù)據(jù)庫(kù)中生成連續(xù)的數(shù)字/字符/時(shí)間序列。

    ??如果你使用的是 MySQL 數(shù)據(jù)庫(kù)，實(shí)現(xiàn)相同的功能可以參考這篇文章。
    ??如果你使用的是 PostgreSQL，實(shí)現(xiàn)相同的功能可以參考這篇文章。
    ??如果你使用的是 SQL Server，實(shí)現(xiàn)相同的功能可以參考這篇文章。

在 Oracle 中實(shí)現(xiàn)數(shù)據(jù)行生成的方法有很多，本文只介紹幾種常用的語(yǔ)法。
使用層次查詢生成序列

Oracle 提供了 CONNECT BY 層次查詢，可以用于生成數(shù)字序列。
生成一個(gè)連接的數(shù)字序列

以下語(yǔ)句使用 CONNECT BY 和 LEVEL 偽列生成了一個(gè)連續(xù)的數(shù)字序列：

SELECT LEVEL AS n
FROM dual
WHERE LEVEL >= 11
CONNECT BY LEVEL <= 15;
N |
--|
11|
12|
13|
14|
15|



通過(guò)修改查詢條件中的 LEVEL 范圍，可以返回不同的數(shù)字序列。

除了使用 LEVEL 偽列之外，也可以使用 ROWNUM 偽列實(shí)現(xiàn)相同的功能：

SELECT rownum AS n
FROM dual
CONNECT BY LEVEL <= 5;
N|
-|
1|
2|
3|
4|
5|


生成一個(gè)間隔的數(shù)字序列

以下查詢利用 mod 函數(shù)返回了一個(gè)從 2 到 15 之間、增量為 3 的數(shù)字序列：

SELECT LEVEL AS n
FROM dual
WHERE LEVEL >= 2 AND MOD(LEVEL-2, 3)=0
CONNECT BY LEVEL <= 15;
N |
--|
 2|
 5|
 8|
11|
14|



以下查詢返回了一個(gè)增量為 -2.5、范圍從 15 到 1.4 之間的降序數(shù)字序列：

SELECT (LEVEL-1) * -2.5 + 15 AS n
FROM dual
WHERE LEVEL >= 1
CONNECT BY (LEVEL-1) * -2.5 + 15 >= 1.4;
N   |
----|
  15|
12.5|
  10|
 7.5|
   5|
 2.5|



生成一個(gè)連續(xù)的字符序列

基于上面的層次查詢和 chr(n) 函數(shù)可以生成連續(xù)的字符序列。例如：

SELECT CHR(LEVEL-1+65) AS letter
FROM dual
CONNECT BY LEVEL-1 <= 70-65;
LETTER|
------|
A     |
B     |
C     |
D     |
E     |
F     |



該查詢返回了字符 A 到 F 的序列，chr(n) 函數(shù)用于將 ASCII 編碼轉(zhuǎn)化為相應(yīng)的字符。
生成一個(gè)間隔的時(shí)間序列

同樣基于以上層次查詢和時(shí)間加減法可以生成間隔的時(shí)間序列。例如：

SELECT TIMESTAMP '2020-01-01 00:00:00' + (LEVEL-1)/24 AS ts
FROM dual
CONNECT BY LEVEL <= 12;
TS                 |
-------------------|
2020-01-01 00:00:00|
2020-01-01 01:00:00|
2020-01-01 02:00:00|
2020-01-01 03:00:00|
2020-01-01 04:00:00|
2020-01-01 05:00:00|
2020-01-01 06:00:00|
2020-01-01 07:00:00|
2020-01-01 08:00:00|
2020-01-01 09:00:00|
2020-01-01 10:00:00|
2020-01-01 11:00:00|


該查詢返回了 2020-01-01 00:00:00 到 2020-01-01 12:00:00、間隔為 1 小時(shí)的所有時(shí)間點(diǎn)。
使用表函數(shù)生成序列

Oracle 支持表函數(shù)（table function），也就是返回結(jié)果為集合（表）的函數(shù)，可以用于模擬 PostgreSQL 中的 generate_series 函數(shù)。
創(chuàng)建模擬的 generate_series 函數(shù)

我們創(chuàng)建一個(gè) PL/SQL 函數(shù) generate_series：

CREATE OR REPLACE FUNCTION generate_series (pstart IN NUMBER, pstop IN NUMBER, pstep IN NUMBER DEFAULT 1)
RETURN sys.odcinumberlist DETERMINISTIC PIPELINED
AS
BEGIN
  IF (pstep = 0) THEN
    raise_application_error(-20001, 'step size cannot equal zero!');
  END IF;
 
  IF (pstart > pstop AND pstep > 0) OR (pstart < pstop AND pstep < 0) THEN
    RETURN;
  END IF;
 
  FOR i IN 0 .. floor(abs((pstop-pstart)/pstep)) LOOP
    PIPE ROW (pstart + i * pstep);
  END LOOP;
    
  RETURN;
END generate_series;



其中，sys.odcinumberlist 是 Oracle 預(yù)定義的變長(zhǎng)數(shù)組類型；PIPELINED 表示定義管道表函數(shù)；pstart 表示數(shù)據(jù)序列的起點(diǎn)，pstop 表示數(shù)據(jù)序列的終點(diǎn)，pstep 表示每次的增量，不允許為 0，默認(rèn)為 1。
使用 generate_series 函數(shù)生成序列

創(chuàng)建了 generate_series 函數(shù)之后，我們就可以用它來(lái)生成各種序列值。例如：

SELECT * FROM TABLE(generate_series(11, 15));
COLUMN_VALUE|
------------|
          11|
          12|
          13|
          14|
          15|

SELECT * FROM TABLE(generate_series(15, 1.4, -2.5));
COLUMN_VALUE|
------------|
          15|
        12.5|
          10|
         7.5|
           5|
         2.5|


其中，TABLE 函數(shù)用于將數(shù)組轉(zhuǎn)換為表；第一個(gè)函數(shù)返回了 11 到 15 的連續(xù)整數(shù)；第二個(gè)函數(shù)返回了 15 到 1.4 之間增量為 -2.5 的降序序列。

我們同樣可以使用 generate_series 函數(shù)生成字符序列和時(shí)間序列：

SELECT chr(column_value) FROM TABLE(generate_series(65, 70));
CHR(COLUMN_VALUE)|
-----------------|
A                |
B                |
C                |
D                |
E                |
F                |

SELECT TIMESTAMP '2020-01-01 00:00:00' + (column_value-1)/24 AS ts
FROM TABLE(generate_series(1, 12));
TS                 |
-------------------|
2020-01-01 00:00:00|
2020-01-01 01:00:00|
2020-01-01 02:00:00|
2020-01-01 03:00:00|
2020-01-01 04:00:00|
2020-01-01 05:00:00|
2020-01-01 06:00:00|
2020-01-01 07:00:00|
2020-01-01 08:00:00|
2020-01-01 09:00:00|
2020-01-01 10:00:00|
2020-01-01 11:00:00|



使用通用表表達(dá)式生成序列
生成一個(gè)等差數(shù)字序列

通用表表達(dá)式（Common Table Expression）的遞歸調(diào)用可以用于生成各種數(shù)列。例如：

WITH t(n) AS (
  SELECT 1 FROM dual
  UNION ALL
  SELECT n+2 FROM t WHERE n < 9
)
SELECT n FROM t;
N|
-|
1|
3|
5|
7|
9|



以上語(yǔ)句生成了一個(gè)從 1 遞增到 9、增量為 2 的數(shù)列，執(zhí)行過(guò)程如下：

    首先，執(zhí)行 CTE 中的初始化查詢，生成一行數(shù)據(jù)（1）；
    然后，第一次執(zhí)行遞歸查詢，判斷 n < 9，生成一行數(shù)據(jù) 3（n+2）；
    接著，重復(fù)執(zhí)行遞歸查詢，生成更多的數(shù)據(jù)；直到 n = 9 時(shí)不滿足條件終止遞歸；此時(shí)臨時(shí)表 t 中包含 5 條數(shù)據(jù)；
    最后，執(zhí)行主查詢，返回所有的數(shù)據(jù)。

生成一個(gè)等比數(shù)字序列

上文模擬的 generate_series 函數(shù)只能生成等差數(shù)列，通用表表達(dá)式則可以生成更復(fù)雜的數(shù)列，例如等比數(shù)列：

WITH t(n) AS (
  SELECT 1 FROM dual
  UNION ALL
  SELECT n*3 FROM t WHERE n < 100
)
SELECT n FROM t;
N  |
---|
  1|
  3|
  9|
 27|
 81|
243|



從第二行開始，每個(gè)數(shù)字都是上一行的 3 倍。
生成斐波那契數(shù)列

斐波那契數(shù)列（Fibonacci series）是指從數(shù)字 0 和 1（或者從 1 和 1）開始，后面的每個(gè)數(shù)字等于它前面兩個(gè)數(shù)字之和（0、1、1、2、3、5、8、13、21、…）。使用通用表表達(dá)式可以很容易地生成斐波那契數(shù)列：

WITH fibonacci (n, fib_n, next_fib_n) AS (
  SELECT 1, 0, 1 FROM dual
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
  FROM fibonacci
  WHERE n < 10 )
SELECT * FROM fibonacci;
N |FIB_N|NEXT_FIB_N|
--|-----|----------|
 1|    0|         1|
 2|    1|         1|
 3|    1|         2|
 4|    2|         3|
 5|    3|         5|
 6|    5|         8|
 7|    8|        13|
 8|   13|        21|
 9|   21|        34|
10|   34|        55|



其中，字段 n 表示該行包含了第 n 個(gè)斐波那契數(shù)列值；字段 fib_n 表示斐波那契數(shù)列值；字段 next_fib_n 表示下一個(gè)斐波那契數(shù)列值。
生成一個(gè)連續(xù)的字符序列

基于通用表表達(dá)式和 CHR(n) 函數(shù)同樣可以生成連續(xù)的字符序列，例如：

WITH t(n) AS (
  SELECT 65 FROM dual
  UNION ALL
  SELECT n+1 FROM t WHERE n <= 70
)
SELECT chr(n) FROM t;
CHR(N)|
------|
A     |
B     |
C     |
D     |
E     |
F     |
G     |



生成一個(gè)間隔的時(shí)間序列

以下語(yǔ)句使用遞歸通用表表達(dá)式生成一個(gè)時(shí)間序列：

WITH ts(v) AS (
  SELECT TIMESTAMP '2020-01-01 00:00:00' FROM dual
  UNION ALL
  SELECT v + 1/24 FROM ts WHERE v < TIMESTAMP '2020-01-01 12:00:00'
)
SELECT v FROM ts;
V                  |
-------------------|
2020-01-01 00:00:00|
2020-01-01 01:00:00|
2020-01-01 02:00:00|
2020-01-01 03:00:00|
2020-01-01 04:00:00|
2020-01-01 05:00:00|
2020-01-01 06:00:00|
2020-01-01 07:00:00|
2020-01-01 08:00:00|
2020-01-01 09:00:00|
2020-01-01 10:00:00|
2020-01-01 11:00:00|
2020-01-01 12:00:00|



以上查詢返回了一個(gè)表，數(shù)據(jù)為 2020-01-01 00:00:00 到 2020-01-01 12:00:00，間隔為 1 小時(shí)的時(shí)間點(diǎn)。

如果覺(jué)得文章對(duì)你有用，歡迎關(guān)注??、評(píng)論??、點(diǎn)贊贊??