SQLite
lag()函数返回来自当前行所在的分区内当前行之前的指定行之内的值。
lag()
语法
这里是 SQLite
lag()函数的语法：
lag(expr[, offset[, default]])OVER ( [PARTITION BY partition_column_list] [ORDER BY order_column_list])
参数
expr必需的。它可以是一个列名或者表达式。
offset可选的。相对于当前行的偏移的行数。默认值为 1。
default可选的。它可以是一个列名或者表达式。
partition_column_list参与分区的列的列表。
order_column_list参与排序的列的列表。
返回值
SQLite
lag()函数返回来自当前行所在的分区内当前行之前的指定行之内的值。
lag()
示例
演示数据准备
使用下面的
CREATE TABLE语句创建一个表
tax_revenue以存储每季度的税收收益：
CREATE TABLE tax_revenue ( id INTEGER PRIMARY KEY, year CHAR(4) NOT NULL, quarter CHAR(1) NOT NULL, revenue INT NOT NULL);
这里创建了一个
tax_revenue表，它有 5 个列：
id- 行 ID，主键。
year- 年份。
quarter- 季节，1 - 4。
revenue- 税收收益。
使用下面的
INSERT语句向
tax_revenue表中插入一些行：
INSERT INTO tax_revenue (year, quarter, revenue)VALUES ('2020', '1', 3515), ('2020', '2', 3678), ('2020', '3', 4203), ('2020', '4', 3924), ('2021', '1', 3102), ('2021', '2', 3293), ('2021', '3', 3602), ('2021', '4', 2901);使用下面的
SELECT语句检索表中的数据：
SELECT * FROM tax_revenue;
id year quarter revenue-- ---- ------- -------1 2020 1 3515 2 2020 2 3678 3 2020 3 4203 4 2020 4 3924 5 2021 1 3102 6 2021 2 3293 7 2021 3 3602 8 2021 4 2901
使用 SQLite lag()
函数和下一季度的收益比较
下面的语句，在使用 SQLite
lag()函数在每行中添加
next_quarter_revenue列以比较当前季度和下一季度的收益：
SELECT *, lag(revenue, 1) OVER ( PARTITION BY year ORDER BY quarter DESC ) next_quarter_revenueFROM tax_revenue;
id year quarter revenue next_quarter_revenue-- ---- ------- ------- --------------------4 2020 4 3924 3 2020 3 4203 3924 2 2020 2 3678 4203 1 2020 1 3515 3678 8 2021 4 2901 7 2021 3 3602 2901 6 2021 2 3293 3602 5 2021 1 3102 3293
注意，上面 SQL 语句中的窗口函数：
lag(revenue, 1) OVER ( PARTITION BY year ORDER BY quarter DESC) next_quarter_revenue
在
OVER子句中，
PARTITION BY year将所有行按照年份进行分区
ORDER BY quarter DESC将每个分区内的行按照季度逆序排列。
lag(revenue, 1)返回每行在其关联的分区内之前一行(
1)中的收益(
revenue)。
那么在
next_quarter_revenue列中存放的就是当前行的下一个季度的收益。所以，每个分区的第一行中的
next_quarter_revenue列的值为 null。
当然，您可以为
next_quarter_revenue列中的 null 值指定要给默认值。下面的语句使用了
0作为默认值：
SELECT *, lag(revenue, 1, 0) OVER ( PARTITION BY year ORDER BY quarter DESC ) next_quarter_revenueFROM tax_revenue;
id year quarter revenue next_quarter_revenue-- ---- ------- ------- --------------------4 2020 4 3924 0 3 2020 3 4203 3924 2 2020 2 3678 4203 1 2020 1 3515 3678 8 2021 4 2901 0 7 2021 3 3602 2901 6 2021 2 3293 3602 5 2021 1 3102 3293
