PostgreSQL 窗口函数

随着数据库的发展迭代，有一些被人们常用的功能已经在数据库中被做成窗口函数来实现了，窗口函数的引入带来了极大的方便。下面对几个常见的实现做下简单的介绍。

PostgreSQL 数据库通用的窗口函数
函数            描述
row_number      当前行在其分区中的行号，从 1 开始计
rank            带间隙的当前行排名。与该行的第一个同等行的 row_number 相同
dense_rank      不带间隙的当前行排名
percent_rank    当前行的相对排名 rank-1/总行数 - 1
first_value     返回在窗口帧中第一行上计算的 VALUE
last_value      返回在窗口帧中最后一行上计算的 VALUE
nth_value       返回在窗口帧中第 N 行上计算的 VALUE ，没有这样的行则返回空值

构造和模拟测试数据
CREATE TABLE "public"."windows_functions" (
  "vaf01" int8 NOT NULL,
  "bbx01" int4 NOT NULL,
  "vaa01" int8 NOT NULL,
  "vaf06" int8,
  "vaf21" numeric(18,4),
  "vaf36" timestamp(6),
  "bce02a" varchar(20),
  PRIMARY KEY ("vaf01"));

ALTER TABLE "public"."windows_functions" OWNER TO "postgres";

INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (658728616326209540, 135, 658239514644455424, 658239514644455425, '1.0000', '2021-12-23 17:52:02', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (658731204983529490, 135, 658239514644455424, 658239514644455425, '1.0000', '2021-12-23 17:57:59', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (658731645389643794, 135, 658239514644455424, 658239514644455425, '1.0000', '2021-12-23 18:04:16', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (551054176646471682, 141, 551053257468944384, 551053257468944385, '1.0000', '2021-03-01 14:52:21', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (551701005100785664, 141, 551698907898462208, 551698908091400192, '1.0000', '2021-03-03 09:42:23', '2909');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (627165126042198016, 141, 538649333814403072, 627162666070319105, '1.0000', '2021-09-27 15:29:23', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (570926872293154816, 144, 552536561183506432, 570925895129374721, '1.0000', '2021-04-25 10:59:29', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (658728616326209536, 144, 658239514644455424, 658239514644455425, '1.0000', '2021-12-23 17:52:02', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (658731204983529486, 144, 658239514644455424, 658239514644455425, '1.0000', '2021-12-23 17:57:59', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (658731645389643790, 144, 658239514644455424, 658239514644455425, '1.0000', '2021-12-23 18:04:16', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (535825623831158785, 158, 535762576789880832, 535762576844406784, '1.0000', '2021-01-18 14:18:47', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (535825623831158786, 158, 535762576789880832, 535762576844406784, '1.0000', '2021-01-18 14:18:47', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (535825623831158787, 158, 535762576789880832, 535762576844406784, '1.0000', '2021-01-18 14:18:47', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (535825623831158788, 158, 535762576789880832, 535762576844406784, '1.0000', '2021-01-18 14:18:47', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (726737386096173058, 158, 549156263519461376, 726737120902914049, '1.0000', '2022-06-29 09:54:29', '1327');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (724594107497979906, 161, 724593621424283648, 724593621424283649, '1.0000', '2022-06-23 11:58:17', '1417');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (729302667771387909, 161, 729300393015779328, 729300393015779329, '1.0000', '2022-07-06 11:48:24', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (863466615524708353, 161, 615189645499703296, 863461644267835393, '1.0000', '2023-07-11 17:08:02', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (516210446089334785, 165, 516195929204858880, 516195929204858881, '1.0000', '2020-11-25 11:15:59', '1317');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (516213731345051649, 165, 516195929204858880, 516195929204858881, '1.0000', '2020-11-25 11:29:03', '1317');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (569892501071208449, 165, 569887714174967808, 569887714439208960, '1.0000', '2021-04-22 14:29:18', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (727567424773300225, 165, 722409122246565888, 727488339111976961, '1.0000', '2022-07-01 16:53:11', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (881202964285509632, 165, 535843758638514177, 880871334060642305, '1.0000', '2023-08-29 15:45:40', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (513818311230763009, 168, 507967861428469760, 513721626081247232, '1.0000', '2020-11-18 20:50:18', '1317');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (531507780406878209, 168, 531505335798743040, 531505336008458240, '1.0000', '2021-01-06 16:22:09', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (805118984532926464, 168, 805004845214998528, 805004846649450496, '3.0000', '2023-01-31 16:55:05', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (805118984532926465, 168, 805004845214998528, 805004846649450496, '3.0000', '2023-01-31 16:55:05', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (514028077471248408, 181, 507967861428469760, 513721626081247232, '1.0000', '2020-11-19 10:43:24', '1317');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (518893461668241417, 181, 518456253970784256, 518456253970784257, '1.0000', '2020-12-02 20:57:07', '1317');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (612680959363522561, 181, 612668560447250432, 612668807080714241, '1.0000', '2021-08-18 16:15:23', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (743773524774232065, 181, 743117897265717248, 743752713933824001, '1.0000', '2022-08-15 10:10:25', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (870970629162160128, 181, 552452918809337856, 870958825786724353, '1.0000', '2023-08-01 10:06:17', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (535821302968229911, 182, 535753930509008896, 535753930626449408, '1.0000', '2021-01-18 14:02:33', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (628272603169169408, 182, 628271142662184960, 628271142662184961, '1.0000', '2021-09-30 16:48:19', '1417');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (738759730964602883, 182, 738415678138228736, 738415678138228737, '1.0000', '2022-08-01 14:07:23', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (809714627037503489, 182, 809710848791683072, 809710850574262272, '1.0000', '2023-02-13 09:16:50', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (732183536827768833, 189, 732162271870066688, 732162271966535680, '1.0000', '2022-07-14 10:35:58', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (734773025429397505, 189, 734771344595623936, 734771344750813184, '1.0000', '2022-07-21 14:05:40', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (755842628033060877, 189, 523788638480375808, 755806701562765313, '1.0000', '2022-09-17 17:28:43', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (771773392977272836, 189, 771740975218434048, 771740975411372032, '1.0000', '2022-10-31 16:31:54', '0724');
INSERT INTO "public"."windows_functions" ("vaf01", "bbx01", "vaa01", "vaf06", "vaf21", "vaf36", "bce02a") VALUES (871354775944450049, 189, 871333633741451264, 871333635742134272, '1.0000', '2023-08-02 11:32:47', '0724');

窗口函数 row_number
SELECT vaf01,bbx01,vaa01,vaf06,vaf21,vaf36,bce02a,row_number() OVER (PARTITION BY bbx01 ORDER BY vaf36 DESC)
FROM windows_functions;
说明：在 bbx01 相同的情况下，按照 vaf36 进行倒序排序，选择时间最大的排在第一。可以看到 row_number 列是没有重复值的，即使 bbx01 相等并且 vaf36 也相等的情况下 row_number 的值也是不相等的。

窗口函数 rank
SELECT vaf01,bbx01,vaa01,vaf06,vaf21,vaf36,bce02a,rank() OVER (PARTITION BY bbx01 ORDER BY vaf36 DESC)
FROM windows_functions;
说明：在 bbx01 相同的情况下，按照 vaf36 进行倒序排序，选择时间最大的排在第一，可以看到 rank 列是有重复值的，当 bbx01 相等并且 vaf36 也相等的情况下 rank 的值是相当的。但是 rank 的值是存在跳跃的比如 1 2 2 4 会存在没有 3 的情况。

窗口函数 dense_rank
SELECT vaf01,bbx01,vaa01,vaf06,vaf21,vaf36,bce02a,dense_rank() OVER (PARTITION BY bbx01 ORDER BY vaf36 DESC)
FROM windows_functions;
说明：其与 rank 的值是类似的，但是最后的列 dense_rank 不存在跳跃。

窗口函数 first_value
SELECT vaf01,bbx01,vaa01,vaf06,vaf21,vaf36,bce02a,first_value(vaf36) OVER (PARTITION BY bbx01 ORDER BY vaf36 DESC)
FROM windows_functions;
说明：获取当 bbx01 相等的情况下，vaf36 倒序排序的第一个值也就是最大值。

窗口函数 last_value
说明：窗口函数 first_value 与窗口函数 last_value 是类似的。