计算每行之间的最近天数

debugcn 发表于 Dev

奥托·沙瓦泽（Oto Shavadze）

给定这样的表（dt列是唯一的）：

id | dt          | days_count
------------------------------
1  | 2015-06-01  |  NULL
2  | 2015-06-03  |  NULL
4  | 2015-06-09  |  NULL

我需要计算最接近的日期之间的间隔天数，并更新列中的检索结果days_count，并且计算必须从最新日期开始。也就是说，需要的结果是：

id | dt          | days_count
------------------------------
1  | 2015-06-01  |  NULL
2  | 2015-06-03  |  2
4  | 2015-06-09  |  6

我对此有2个变体：

变体1

使用PL/SQL FOR (SELECT ..) LOOP，获取按行排序的每一行，dt desc并计算当前行日期和上一行日期之间的间隔天数。

变体2

MERGE INTO mytable
USING(
  WITH
  t1 as (SELECT id, dt,  row_number() over(order by dt) as rn from mytable),
  t2 as (SELECT  id, dt,  row_number() over(order by dt) + 1 as rn from mytable)  
  SELECT  t1.id, t1.rn,  t1.dt - t2.dt as days_count  from   t1
  left JOIN  t2
  on
  t1.rn = t2.rn
) day_interval
ON (mytable.id = day_interval.id)
WHEN MATCHED THEN UPDATE SET
mytable.days_count= day_interval.days_count

这些变体有效，但问题是：也许有更有效的方法吗？

拉利特·库马尔（Lalit Kumar B）

当您可以使用纯SQL进行操作时，切勿在PL / SQL中进行操作。

您可以使用LAG（）OVER（）分析函数轻松完成此操作。

例如，

设置

SQL> CREATE TABLE t
  2      (id int, dt date, days_count varchar2(4));

Table created.

SQL>
SQL> INSERT ALL
  2      INTO t (id, dt, days_count)
  3           VALUES (1, to_date('2015-06-01','YYYY-MM-DD'), NULL)
  4      INTO t (id, dt, days_count)
  5           VALUES (2, to_date('2015-06-03','YYYY-MM-DD'), NULL)
  6      INTO t (ID, dt, days_count)
  7           VALUES (4, to_date('2015-06-09','YYYY-MM-DD'), NULL)
  8  SELECT * FROM dual;

3 rows created.

SQL>

询问

SQL> SELECT id, dt, dt - lag(dt) over(order by dt) days_count FROM t;

        ID DT        DAYS_COUNT
---------- --------- ----------
         1 01-JUN-15
         2 03-JUN-15          2
         4 09-JUN-15          6

SQL>

合并声明

SQL> MERGE INTO t
  2      USING(
  3        SELECT id, dt, dt - lag(dt) over(order by dt) days_count FROM t
  4      ) day_interval
  5      ON (t.id = day_interval.id)
  6      WHEN MATCHED THEN UPDATE SET
  7      t.days_count= day_interval.days_count;

3 rows merged.

SQL> SELECT * FROM t;

        ID DT        DAYS
---------- --------- ----
         1 01-JUN-15
         2 03-JUN-15 2
         4 09-JUN-15 6

SQL>