如何根据同一表中的日期计算不同行中两个值的差异

Question

我有一个Oracle表，结构如下：

Serial Some_value  Date(Date Field type)

1      100         4-10-2019 8:00

1      106         4-17-2019 7:00

2      100         4-10-2019 8:00

2      101         4-17-2019 6:33

3      100         4-10-2019 9:00

3      110         4-17-2019 6:00

我希望有一个查询，返回序号，其差异大于5，目前的读数和7天前。上面的示例表的输出如下：

Serial Difference

1      6

3      10

目前，我有这个SQL来获得当前值和值七天前，但我正在努力拼凑这一切。

  (select quantity from Parts where date > sysdate-1) -
  (select quantity from Parts where to_char(sysdate-7,'DDD') = 
   to_char(date,'DDD') and 
   to_char(sysdate-7,'YYYY') = to_char(date,'YYYY') and
   to_char(sysdate-7,'MM') = to_char(date,'MM'))

谢谢!

Answer 1

我们可以在这里尝试使用LAG函数：

WITH cte AS (
    SELECT Serial, Some_value, "Date",
        LAG(Some_value, 1) OVER (PARTITION BY Serial ORDER BY "Date") Some_value_lag
    FROM Parts
)

SELECT
    Serial,
    "Date",
    ABS(Some_value - Some_value_lag) AS difference
FROM cte
WHERE
    ABS(Some_value - Some_value_lag) > 5
ORDER BY
    Serial,
    "Date";

​

​

​

演示

​

上面的CTE首先在每组串行记录中按日期生成Some_value列的滞后。然后，我们查询只包含某些值之间绝对差超过5的记录。注意，每个序列组中最早的记录将自动不包括在结果集中，因为在这种情况下，LAG将返回NULL。我继续并将日期包含在输出中，因为它使它更容易阅读。