Oracle 如何生成连续的数字/字符/时间序列
作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
文章目录
使用层次查询生成序列
生成一个连接的数字序列
生成一个间隔的数字序列
生成一个连续的字符序列
生成一个间隔的时间序列
使用表函数生成序列
创建模拟的 generate_series 函数
使用 generate_series 函数生成序列
使用通用表表达式生成序列
生成一个等差数字序列
生成一个等比数字序列
生成斐波那契数列
生成一个连续的字符序列
生成一个间隔的时间序列
大家好,我是只谈技术不剪发的 Tony 老师。今天我们继续介绍如何在 Oracle 数据库中生成连续的数字/字符/时间序列。
📝如果你使用的是 MySQL 数据库,实现相同的功能可以参考这篇文章。
📝如果你使用的是 PostgreSQL,实现相同的功能可以参考这篇文章。
📝如果你使用的是 SQL Server,实现相同的功能可以参考这篇文章。
在 Oracle 中实现数据行生成的方法有很多,本文只介绍几种常用的语法。
使用层次查询生成序列
Oracle 提供了 CONNECT BY 层次查询,可以用于生成数字序列。
生成一个连接的数字序列
以下语句使用 CONNECT BY 和 LEVEL 伪列生成了一个连续的数字序列:
SELECT LEVEL AS n
FROM dual
WHERE LEVEL >= 11
CONNECT BY LEVEL <= 15;
N |
--|
11|
12|
13|
14|
15|
通过修改查询条件中的 LEVEL 范围,可以返回不同的数字序列。
除了使用 LEVEL 伪列之外,也可以使用 ROWNUM 伪列实现相同的功能:
SELECT rownum AS n
FROM dual
CONNECT BY LEVEL <= 5;
N|
-|
1|
2|
3|
4|
5|
生成一个间隔的数字序列
以下查询利用 mod 函数返回了一个从 2 到 15 之间、增量为 3 的数字序列:
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|
以下查询返回了一个增量为 -2.5、范围从 15 到 1.4 之间的降序数字序列:
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|
生成一个连续的字符序列
基于上面的层次查询和 chr(n) 函数可以生成连续的字符序列。例如:
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) 函数用于将 ASCII 编码转化为相应的字符。
生成一个间隔的时间序列
同样基于以上层次查询和时间加减法可以生成间隔的时间序列。例如:
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 小时的所有时间点。
使用表函数生成序列
Oracle 支持表函数(table function),也就是返回结果为集合(表)的函数,可以用于模拟 PostgreSQL 中的 generate_series 函数。
创建模拟的 generate_series 函数
我们创建一个 PL/SQL 函数 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 预定义的变长数组类型;PIPELINED 表示定义管道表函数;pstart 表示数据序列的起点,pstop 表示数据序列的终点,pstep 表示每次的增量,不允许为 0,默认为 1。
使用 generate_series 函数生成序列
创建了 generate_series 函数之后,我们就可以用它来生成各种序列值。例如:
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 函数用于将数组转换为表;第一个函数返回了 11 到 15 的连续整数;第二个函数返回了 15 到 1.4 之间增量为 -2.5 的降序序列。
我们同样可以使用 generate_series 函数生成字符序列和时间序列:
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|
使用通用表表达式生成序列
生成一个等差数字序列
通用表表达式(Common Table Expression)的递归调用可以用于生成各种数列。例如:
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|
以上语句生成了一个从 1 递增到 9、增量为 2 的数列,执行过程如下:
首先,执行 CTE 中的初始化查询,生成一行数据(1);
然后,第一次执行递归查询,判断 n < 9,生成一行数据 3(n+2);
接着,重复执行递归查询,生成更多的数据;直到 n = 9 时不满足条件终止递归;此时临时表 t 中包含 5 条数据;
最后,执行主查询,返回所有的数据。
生成一个等比数字序列
上文模拟的 generate_series 函数只能生成等差数列,通用表表达式则可以生成更复杂的数列,例如等比数列:
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|
从第二行开始,每个数字都是上一行的 3 倍。
生成斐波那契数列
斐波那契数列(Fibonacci series)是指从数字 0 和 1(或者从 1 和 1)开始,后面的每个数字等于它前面两个数字之和(0、1、1、2、3、5、8、13、21、…)。使用通用表表达式可以很容易地生成斐波那契数列:
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 个斐波那契数列值;字段 fib_n 表示斐波那契数列值;字段 next_fib_n 表示下一个斐波那契数列值。
生成一个连续的字符序列
基于通用表表达式和 CHR(n) 函数同样可以生成连续的字符序列,例如:
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 |
生成一个间隔的时间序列
以下语句使用递归通用表表达式生成一个时间序列:
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|
以上查询返回了一个表,数据为 2020-01-01 00:00:00 到 2020-01-01 12:00:00,间隔为 1 小时的时间点。
如果觉得文章对你有用,欢迎关注❤️、评论📝、点赞赞👍