oracle 比较LAG值并在有差异时用数据填充子列的查询？

mitkmikd 于 2022-12-03 发布在 Oracle

关注(0)|答案(2)|浏览(148)

A query that compares the LAG value and fills the sub column with data if there is a difference?

WITH A AS (
    SELECT 'GOLD' AS Title, 1 AS RNUM, 555.4 AS VALUE1, null AS DIFF, null AS LAG FROM DUAL UNION ALL
    SELECT 'GOLD' AS Title, 2 AS RNUM, 555.4 AS VALUE1, 0 AS DIFF, 555.4 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 3 AS RNUM, 555.4 AS VALUE1, 0 AS DIFF, 555.4 AS LAG FROM DUAL UNION ALL
 SELECT 'GOLD' AS Title, 4 AS RNUM, 556 AS VALUE1, 0.6 AS DIFF, 555.4 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 5 AS RNUM, 556 AS VALUE1, 0 AS DIFF, 556 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 6 AS RNUM, 556 AS VALUE1, 0 AS DIFF, 556 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 7 AS RNUM, 556.7 AS VALUE1, 0.7 AS DIFF, 556 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 8 AS RNUM, 556.7 AS VALUE1, 0 AS DIFF,556.7 AS LAG FROM DUAL UNION ALL
SELECT 'GOLD' AS Title, 9 AS RNUM, 557.3 AS VALUE1, 0.6 AS DIFF, 556.7 AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 1 AS RNUM, 400.3 AS VALUE1, null AS DIFF, null AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 2 AS RNUM, 401.3 AS VALUE1, 1.0 AS DIFF, 400.3 AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 3 AS RNUM, 401.3 AS VALUE1, 0 AS DIFF, 401.3 AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 4 AS RNUM, 401.3 AS VALUE1, 0 AS DIFF, 401.3 AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 5 AS RNUM, 402.2 AS VALUE1, 0.9 AS DIFF, 401.3 AS LAG FROM DUAL UNION ALL
SELECT 'SILVER' AS Title, 6 AS RNUM, 403.2 AS VALUE1, 1.0 AS DIFF, 402.2 AS LAG FROM DUAL
)

Using A, I want to get the same result as B.
If the data in the DIFF column is greater than 0 (or according to a condition), I want to fill the value in the AccMaxNo column with the RNUM value in the DIFF column.

Title	RNUM	VALUE1	DIFF	LAG
GOLD	1	555.4	null	null
GOLD	2	555.4	0	555.4
GOLD	3	555.4	0	555.4
GOLD	4	556	0.6	555.4
GOLD	5	556	0	556
GOLD	6	556	0	556
GOLD	7	556.7	0.7	556
GOLD	8	556.7	0	556.7
GOLD	9	557.3	0.6	556.7
SILVER	1	400.3	null	null
SILVER	2	401.3	1.0	400.3
SILVER	3	401.3	0	401.3
SILVER	4	401.3	0	401.3
SILVER	5	402.2	0.9	401.3
SILVER	6	403.2	1.0	402.2

QUERY B

Title	RNUM	VALUE1	DIFF	LAG	AccMaxNo
GOLD	1	555.4	null	null	4
GOLD	2	555.4	0	555.4	4
GOLD	3	555.4	0	555.4	4
GOLD	4	556	0.6	555.4	4
GOLD	5	556	0	556	7
GOLD	6	556	0	556	7
GOLD	7	556.7	0.7	556	7
GOLD	8	556.7	0	556.7	9
GOLD	9	557.3	0.6	556.7	9
SILVER	1	400.3	null	null	2
SILVER	2	401.3	1.0	400.3	2
SILVER	3	401.3	0	401.3	5
SILVER	4	401.3	0	401.3	5
SILVER	5	402.2	0.9	401.3	5
SILVER	6	403.2	1.0	402.2	6

oracle

来源：https://stackoverflow.com/questions/74651693/a-query-that-compares-the-lag-value-and-fills-the-sub-column-with-data-if-there

2条答案

按热度按时间

w7t8yxp51#

在Oracle 12中，您可以使用MATCH_RECOGNIZE执行逐行处理：

SELECT title,
       rnum,
       value1,
       value1 - lag AS diff,
       lag,
       MAX(rnum) OVER (PARTITION BY title, mno) AS accmaxno
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY title
  ORDER BY rnum
  MEASURES
    PREV(value1) AS lag,
    MATCH_NUMBER() AS mno
  ALL ROWS PER MATCH
  PATTERN ((^ first_row | same_value)* any_row)
  DEFINE
    same_value AS PREV(value1) = value1
)

其中，对于示例数据：

CREATE TABLE table_name (Title, RNUM, VALUE1) AS
SELECT 'GOLD',   1, 555.4 FROM DUAL UNION ALL
SELECT 'GOLD',   2, 555.4 FROM DUAL UNION ALL
SELECT 'GOLD',   3, 555.4 FROM DUAL UNION ALL
SELECT 'GOLD',   4, 556 FROM DUAL UNION ALL
SELECT 'GOLD',   5, 556 FROM DUAL UNION ALL
SELECT 'GOLD',   6, 556 FROM DUAL UNION ALL
SELECT 'GOLD',   7, 556.7 FROM DUAL UNION ALL
SELECT 'GOLD',   8, 556.7 FROM DUAL UNION ALL
SELECT 'GOLD',   9, 557.3 FROM DUAL UNION ALL
SELECT 'SILVER', 1, 400.3 FROM DUAL UNION ALL
SELECT 'SILVER', 2, 401.3 FROM DUAL UNION ALL
SELECT 'SILVER', 3, 401.3 FROM DUAL UNION ALL
SELECT 'SILVER', 4, 401.3 FROM DUAL UNION ALL
SELECT 'SILVER', 5, 402.2 FROM DUAL UNION ALL
SELECT 'SILVER', 6, 403.2 FROM DUAL;

输出：
| 标题|额定值|值1|差异|滞后|ACC最大编号|
| - -|- -|- -|- -|- -|- -|
| 金色|一个|五百五十五点四| * 空值 | 空值 *| 四个|
| 金色|2个|五百五十五点四|第0页|五百五十五点四|四个|
| 金色|三个|五百五十五点四|第0页|五百五十五点四|四个|
| 金色|四个|五五六|六分|五百五十五点四|四个|
| 金色|五个|五五六|第0页|五五六|七个|
| 金色|六个|五五六|第0页|五五六|七个|
| 金色|七个|五百五十六点七|.7分|五五六|七个|
| 金色|八个|五百五十六点七|第0页|五百五十六点七|九个|
| 金色|九个|五百五十七点三|六分|五百五十六点七|九个|
| 银|一个|400.3美元| * 空值 | 空值 *| 2个|
| 银色|2个|401.3节|一个|400.3美元|2个|
| 银|三个|401.3节|第0页|401.3节|五个|
| 银|四个|401.3节|第0页|401.3节|五个|
| 银|五个|402.2节|.9分|401.3节|五个|
| 银|六个|403.2节|一个|402.2节|六个|
fiddle

赞(0）回复(0）举报 2022-12-03

u5rb5r592#

解析函数***FIRST_VALUE***对于此目的非常实用。

**NULLIF*此处的函数将所有diff值0转换为null

Then***IGNORE NULLS***子句与FIRST_VALUE一起使用时，将忽略NULLIF（diff，0）为空的所有行
最后，窗口子句***ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING***允许将函数限制为所需的相关行。

SELECT a.*
     , FIRST_VALUE( 
           CASE WHEN NULLIF(diff, 0) IS NOT NULL THEN rnum ELSE NULL END 
         ) IGNORE NULLS OVER(
                PARTITION BY title ORDER BY rnum 
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            ) AccMaxNo
FROM A
;

demo on db<>fiddle

赞(0）回复(0）举报 2022-12-03

我来回答

oracle 比较LAG值并在有差异时用数据填充子列的查询？

2条答案

相关问题

热门标签

最新问答