LAG [analytic]

在窗口内当前行之前按照给定的偏移量返回该输入表达式的值。此函数允许您同时访问表中的多行。这对于在可以可靠地知道行的相对位置时比较值很有用。借助它还可以避免成本较高的自联接，从而加快查询处理速度。

有关获取后续行的信息，请参阅 LEAD。

行为类型

语法

LAG ( expression[, offset ] [, default ] ) OVER (
    [ window-partition-clause ]
    window-order-clause )

参数

表达式: 要进行求值的表达式，例如常数、列、非统计函数、函数表达式或任何涉及以上内容的表达式。
offset: 表示 lag 有多大。默认值为 1（前一行）。此参数的计算结果必须为常数正整数。
default: offset 超出表或分区的边界时返回的值。此值必须是一个常数值或者可以解析为常数的表达式；数据类型强制转换为第一个实参的类型。

示例

这个例子计算了表中按照日期所剩下的余额的总和，同时计算了最后一天的先前余额的总和。依照接下来的输入，数据满足以下条件：

对于每个 some_id，每个由 month_date 表示的日期都有 1 个行。
对于每个 some_id，日期都是连续的；也就是说，如果 2 月 24 号有一行，2 月 26 号有一行，则 2 月 25 号也有一行。

每个 some_id 都有相同的一组日期。

=> CREATE TABLE balances (
       month_date DATE,
       current_bal INT,
       some_id INT);

=> INSERT INTO balances values ('2009-02-24', 10, 1);
=> INSERT INTO balances values ('2009-02-25', 10, 1);
=> INSERT INTO balances values ('2009-02-26', 10, 1);
=> INSERT INTO balances values ('2009-02-24', 20, 2);
=> INSERT INTO balances values ('2009-02-25', 20, 2);
=> INSERT INTO balances values ('2009-02-26', 20, 2);
=> INSERT INTO balances values ('2009-02-24', 30, 3);
=> INSERT INTO balances values ('2009-02-25', 20, 3);
=> INSERT INTO balances values ('2009-02-26', 30, 3);

现在使用 LAG 计算当前每一个日期的余额的总和，以及最后一天的先前余额的总和：

=> SELECT month_date,
     SUM(current_bal) as current_bal_sum,
     SUM(previous_bal) as previous_bal_sum FROM
       (SELECT month_date, current_bal,
     LAG(current_bal, 1, 0) OVER
       (PARTITION BY some_id ORDER BY month_date)
     AS previous_bal FROM balances) AS subQ
     GROUP BY month_date ORDER BY month_date;
month_date  | current_bal_sum | previous_bal_sum
------------+-----------------+------------------
 2009-02-24 |              60 |                0
 2009-02-25 |              50 |               60
 2009-02-26 |              60 |               50
(3 rows)

对于相同的示例数据来说，不可使用接下来的查询，原因是 LAG 嵌套在一个聚合函数中：

=> SELECT month_date,
    SUM(current_bal) as current_bal_sum,
   SUM(LAG(current_bal, 1, 0) OVER
      (PARTITION BY some_id ORDER BY month_date)) AS previous_bal_sum
   FROM some_table GROUP BY month_date ORDER BY month_date;

下面的示例使用了 VMart 数据库。LAG 首先返回前一行的年收入值，然后计算当前行收入与前一行收入的差：

=> SELECT occupation, customer_key, customer_name, annual_income,
   LAG(annual_income, 1, 0) OVER (PARTITION BY occupation
   ORDER BY annual_income) AS prev_income, annual_income -
   LAG(annual_income, 1, 0) OVER (PARTITION BY occupation
   ORDER BY annual_income) AS difference
   FROM customer_dimension ORDER BY occupation, customer_key LIMIT 20;
 occupation | customer_key |    customer_name     | annual_income | prev_income | difference
------------+--------------+----------------------+---------------+-------------+------------
 Accountant |           15 | Midori V. Peterson   |        692610 |      692535 |         75
 Accountant |           43 | Midori S. Rodriguez  |        282359 |      280976 |       1383
 Accountant |           93 | Robert P. Campbell   |        471722 |      471355 |        367
 Accountant |          102 | Sam T. McNulty       |        901636 |      901561 |         75
 Accountant |          134 | Martha B. Overstreet |        705146 |      704335 |        811
 Accountant |          165 | James C. Kramer      |        376841 |      376474 |        367
 Accountant |          225 | Ben W. Farmer        |         70574 |       70449 |        125
 Accountant |          270 | Jessica S. Lang      |        684204 |      682274 |       1930
 Accountant |          273 | Mark X. Lampert      |        723294 |      722737 |        557
 Accountant |          295 | Sharon K. Gauthier   |         29033 |       28412 |        621
 Accountant |          338 | Anna S. Jackson      |        816858 |      815557 |       1301
 Accountant |          377 | William I. Jones     |        915149 |      914872 |        277
 Accountant |          438 | Joanna A. McCabe     |        147396 |      144482 |       2914
 Accountant |          452 | Kim P. Brown         |        126023 |      124797 |       1226
 Accountant |          467 | Meghan K. Carcetti   |        810528 |      810284 |        244
 Accountant |          478 | Tanya E. Greenwood   |        639649 |      639029 |        620
 Accountant |          511 | Midori P. Vogel      |        187246 |      185539 |       1707
 Accountant |          525 | Alexander K. Moore   |        677433 |      677050 |        383
 Accountant |          550 | Sam P. Reyes         |        735691 |      735355 |        336
 Accountant |          577 | Robert U. Vu         |        616101 |      615439 |        662
(20 rows)

接下来的示例使用了 LEAD 和 LAG 返回了当前行工资后的第三行以及当前行工资之前的第五行：

=> SELECT hire_date, employee_key, employee_last_name,
   LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "next_hired" ,
   LAG(hire_date, 1) OVER (ORDER BY hire_date) AS "last_hired"
   FROM employee_dimension ORDER BY hire_date, employee_key;
 hire_date  | employee_key | employee_last_name | next_hired | last_hired
------------+--------------+--------------------+------------+------------
 1956-04-11 |         2694 | Farmer             | 1956-05-12 |
 1956-05-12 |         5486 | Winkler            | 1956-09-18 | 1956-04-11
 1956-09-18 |         5525 | McCabe             | 1957-01-15 | 1956-05-12
 1957-01-15 |          560 | Greenwood          | 1957-02-06 | 1956-09-18
 1957-02-06 |         9781 | Bauer              | 1957-05-25 | 1957-01-15
 1957-05-25 |         9506 | Webber             | 1957-07-04 | 1957-02-06
 1957-07-04 |         6723 | Kramer             | 1957-07-07 | 1957-05-25
 1957-07-07 |         5827 | Garnett            | 1957-11-11 | 1957-07-04
 1957-11-11 |          373 | Reyes              | 1957-11-21 | 1957-07-07
 1957-11-21 |         3874 | Martin             | 1958-02-06 | 1957-11-11
(10 rows)

LAG [analytic]

行为类型

语法

参数

示例

另请参阅