时间戳

首页 > 解决方案 > 如何在不使用 LAG 功能的情况下访问以前的记录

sql - 如何在不使用 LAG 功能的情况下访问以前的记录

问题描述

如何在不使用 LAG() 函数的情况下访问上一条记录 - 计算 diff - 找到 %age 增加或减少

这就是我试图找到差异的方法

WITH test AS
(
    SELECT rownum rn, quantity 
    FROM test1
)
SELECT  cur.quantity - previous.quantity as diff
FROM test cur
JOIN test previous
ON cur.Rn - 1 = previous.Rn;

但无法巩固..

输入 

TEST1
s_id  quantity
11        50
21        55
31        65
41        55
51        75
61        75
71        85
81        80
91        90

预期产出

s_id    quantity       diff    percentage_increase_decrease
11        50            0
21        55            5
31        65            10
41        55           -10
51        75            20
61        75            0
71        85            10
81        80            -5
91        90            10

我正在使用 LAG() 获得所需的输出，但不使用 LAG() 如何实现相同的输出？ 如何计算预期输出中的第 4 列，即每个商店 id 的 percent_increase_decrease？

标签： sqloracle

解决方案

您的查询中的微小修改应该可以工作。

WITH test AS
(SELECT S_ID, QUANTITY, ROWNUM RN FROM
    (SELECT s_id, quantity 
    FROM test1 order by s_id) -- added order by before taking rownum
)
SELECT  S_ID, cur.quantity - COALESCE(previous.quantity,0) as diff -- used coalesce to avoid first row issue
FROM test cur
LEFT JOIN test previous -- used LEFT JOIN to avoid first row issue
ON cur.Rn - 1 = previous.Rn;

干杯！！

- 更新 - 

WITH test AS
(SELECT S_ID, QUANTITY, ROWNUM RN FROM
    (SELECT s_id, quantity 
    FROM test1 order by s_id) -- added order by before taking rownum
)
SELECT
    S_ID,
    QUANTITY,
    QUANTITY - PREV_QUANTITY AS DIFF,
    CASE WHEN PREV_QUANTITY <> 0 THEN
      ROUND((QUANTITY - PREV_QUANTITY) * 100 / PREV_QUANTITY, 2)
    ELSE
      100 -- IF PREV_QUANTITY IS 0 OR NULL THEN WE CAN TAKE 100 AS  PERCENTAGE OR 0 OR ANY OTHER VALUE YOU WANT
    END AS PERCENTAGE_INCREASE_DECREASE
FROM
    (
        SELECT
            S_ID,
            CUR.QUANTITY,
            COALESCE(PREVIOUS.QUANTITY, 0) AS PREV_QUANTITY  -- used coalesce to avoid first row issue
        FROM
            TEST CUR
            LEFT JOIN TEST PREVIOUS -- used LEFT JOIN to avoid first row issue
             ON CUR.RN - 1 = PREVIOUS.RN
    );

干杯！！

推荐阅读