個人中心
退出
PostgreSQL 如何生成連續(xù)的數(shù)字/字符/時間序列
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
使用 generate_series 函數(shù)生成序列
生成一個連接的數(shù)字序列
生成一個間隔的數(shù)字序列
生成一個連續(xù)的字符序列
生成一個間隔的時間序列
使用通用表表達式生成序列
生成一個等差數(shù)字序列
生成一個等比數(shù)字序列
生成斐波那契數(shù)列
生成一個連續(xù)的字符序列
生成一個間隔的時間序列
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天我們介紹一下如何在 PostgreSQL 中生成連續(xù)的數(shù)字、字符以及時間序列值。
??如果你使用的是 MySQL 數(shù)據(jù)庫，實現(xiàn)相同的功能可以參考這篇文章。
??如果你使用的是 Oracle 數(shù)據(jù)庫，實現(xiàn)相同的功能可以參考這篇文章。
??如果你使用的是 SQL Server，實現(xiàn)相同的功能可以參考這篇文章。
使用 generate_series 函數(shù)生成序列
PostgreSQL 中的 generate_series 函數(shù)可以用于生成一個數(shù)字或者時間序列集合。
生成一個連接的數(shù)字序列
generate_series 函數(shù)有 3 種形式，其中最簡單的 generate_series(start, stop) 可以生成一個增量為 1 的等差數(shù)列。例如：
select * from generate_series(1, 5);
generate_series|
---------------|
1|
2|
3|
4|
5|
查詢返回一個表，數(shù)據(jù)從 1 遞增到 5。
生成一個間隔的數(shù)字序列
第二種形式的 generate_series(start, stop, step) 函數(shù)允許指定一個增量。例如：
select * from generate_series(1, 10, 2);
generate_series|
---------------|
1|
3|
5|
7|
9|
select * from generate_series(10, 0, -2.5);
generate_series|
---------------|
10|
7.5|
5.0|
2.5|
0.0|
select * from generate_series(1, 10, -2);
generate_series|
---------------|
第一個函數(shù)返回了一個增量為 2 的數(shù)字序列；第二個函數(shù)返回了一個增量為 -2.5 的數(shù)字序列；第三個函數(shù)沒有返回任何數(shù)據(jù)行，因為沒有從 1 到 10 并且增量為 -2 的序列。
生成一個連續(xù)的字符序列
基于以上 generate_series 函數(shù)和 CHR(n) 函數(shù)可以生成連續(xù)的字符序列。例如：
select chr(v) from generate_series(65, 70) v;
chr|
---|
A |
B |
C |
D |
E |
F |
以上函數(shù)生成了字符 A 到 F 的序列，chr(v) 函數(shù)用于將 ASCII 或者 Unicode 編碼轉(zhuǎn)化為相應(yīng)的字符。
生成一個間隔的時間序列
第三種形式的 generate_series(start_timestamp, stop_timestamp, step_interval) 函數(shù)用于生成一個時間序列值。例如：
select * from generate_series('2020-01-01 00:00:00'::timestamp, '2020-01-01 12:00:00'::timestamp, interval '1 hour');
generate_series |
-------------------|
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|
以上查詢返回了一個表，數(shù)據(jù)為 2020-01-01 00:00:00 到 2020-01-01 12:00:00，間隔為 1 小時的時間點。
如果生成的時間序列只包含日期部分，也可以使用之前的 generate_series 函數(shù)形式。例如：
select current_date + dates.val as val
from generate_series(0, 28, 7) dates(val);
val |
----------|
2020-07-14|
2020-07-21|
2020-07-28|
2020-08-04|
2020-08-11|
以上查詢返回當(dāng)前日期以及接下來四周對應(yīng)的日期。
使用通用表表達式生成序列
生成一個等差數(shù)字序列
PostgreSQL 中的通用表表達式（Common Table Expression）支持遞歸調(diào)用，可以用于生成各種數(shù)列。例如：
with recursive t(n) as (
values (1)
union all
select n+2 from t where n < 9
)
select n from t;
n|
-|
1|
3|
5|
7|
9|
以上語句生成了一個從 1 遞增到 9、增量為 2 的數(shù)列，執(zhí)行過程如下：
首先，執(zhí)行 CTE 中的初始化查詢，生成一行數(shù)據(jù)（1）；
然后，第一次執(zhí)行遞歸查詢，判斷 n < 9，生成一行數(shù)據(jù) 3（n+2）；
接著，重復(fù)執(zhí)行遞歸查詢，生成更多的數(shù)據(jù)；直到 n = 9 時不滿足條件終止遞歸；此時臨時表 t 中包含 5 條數(shù)據(jù)；
最后，執(zhí)行主查詢，返回所有的數(shù)據(jù)。
生成一個等比數(shù)字序列
generate_series 函數(shù)只能生成等差數(shù)列，通用表表達式則可以生成更復(fù)雜的數(shù)列，例如等比數(shù)列：
with recursive t(n) as (
values (1)
union all
select n * 3 from t where n < 100
)
select n from t;
n |
---|
1|
3|
9|
27|
81|
243|
從第二行開始，每個數(shù)字都是上一行的 3 倍。
生成斐波那契數(shù)列
斐波那契數(shù)列（Fibonacci series）是指從數(shù)字 0 和 1（或者從 1 和 1）開始，后面的每個數(shù)字等于它前面兩個數(shù)字之和（0、1、1、2、3、5、8、13、21、…）。使用通用表表達式可以很容易地生成斐波那契數(shù)列：
with recursive fibonacci (n, fib_n, next_fib_n) as
(
select 1, 0, 1
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 個斐波那契數(shù)列值；字段 fib_n 表示斐波那契數(shù)列值；字段 next_fib_n 表示下一個斐波那契數(shù)列值。
生成一個連續(xù)的字符序列
基于通用表表達式和 CHR(n) 函數(shù)同樣可以生成連續(xù)的字符序列，例如：
with recursive t(n) as (
values (65)
union all
select n+1 from t where n < 70
)
select chr(n) from t;
chr|
---|
A |
B |
C |
D |
E |
F |
生成一個間隔的時間序列
以下語句使用遞歸通用表表達式生成一個時間序列：
with recursive ts(v) as (
values ('2020-01-01 00:00:00'::timestamp)
union all
select v + interval '1 hour' from ts where v < '2020-01-01 12:00:00'::timestamp
)
select * 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|
以上查詢返回了一個表，數(shù)據(jù)為 2020-01-01 00:00:00 到 2020-01-01 12:00:00，間隔為 1 小時的時間點。
如果生成的時間序列只包含日期部分，可以使用以下語句：
with recursive ts(v) as (
values (current_date)
union all
select v + 7 from ts where v < current_date + 28
)
select * from ts;
v |
----------|
2020-07-14|
2020-07-21|
2020-07-28|
2020-08-04|
2020-08-11|
以上查詢返回當(dāng)前日期以及接下來四周對應(yīng)的日期。
分類導(dǎo)航