使用窗口函数的sql查询-插入和更新输出的超前和滞后

nwlls2ji 于 2021-06-25 发布在 Mysql

关注(0)|答案(1)|浏览(340)

我需要一个sql查询来使用窗口函数lag和lead执行以下操作。
对于每个键，我需要在最终输出中执行下面的插入和更新
插入条件：
1.默认情况下，layer_no=0需要写入输出。
2.如果col1、col2、col3的值与它的珍贵记录相对应有任何变化，则需要在输出中写入该记录。
例如：按键1，图层为2，列3中的值从400变为600
更新条件：
1.如果col1、col2、col3的值与其前一个记录的值没有变化，但是“depart column”中有变化，则需要在输出中更新该值。
2.在插入层号为0的记录后，甚至层号也应按顺序更新
示例：第1层为第3层时，col1、col2、col3中没有更改，但DEVITE列中的值更改为“xyz”，因此需要在输出中更新。

select * from input_table;   
+-----+--------+----+----+----+------+
|  KEY|LAYER_NO|COL1|COL2|COL3|DEPART|
+-----+--------+----+----+----+------+
|key_1|       0| 200| 300| 400|   abc|->default write
|key_1|       1| 200| 300| 400|   abc|
|key_1|       2| 200| 300| 600|   abc|--->change in col3,so write
|key_1|       2| 200| 300| 600|   abc|
|key_1|       3| 200| 300| 600|   xyz|--->change in col4,so update
|key_2|       0| 500| 700| 900|   prq|->default write
|key_2|       1| 888| 555| 900|   prq|--->change in col1 & col 2,so write
|key_3|       0| 111| 222| 333|   lgh|->default write
|key_3|       1| 084| 222| 333|   lgh|--->change in col1,so write
|key_3|       2| 084| 222| 333|   rrr|--->change in col4,so update
+-----+--------+----+----+----+------+

从input\ u表生成以下输出的sql查询是什么？
预期产量：

+-----+--------+----+----+----+------+
|  KEY|LAYER_NO|COl1|COl2|COl3|DEPART|
+-----+--------+----+----+----+------+
|key_1|       0| 200| 300| 400|   abc|
|key_1|       1| 200| 300| 600|   xyz|
|key_2|       0| 500| 700| 900|   prq|
|key_2|       1| 888| 555| 900|   prq|
|key_3|       0| 111| 222| 333|   lgh|
|key_3|       1| 084| 222| 333|   rrr|
+-----+--------+----+----+----+------+

sql mysql sql-server oracle oracle-sqldeveloper

来源：https://stackoverflow.com/questions/48207352/sql-query-using-windowing-functions-lead-and-lag-for-insert-update-output

1条答案

按热度按时间

mqkwyuun1#

这可以通过首先计算出更改了col1、col2或col3列的行，然后为后续行查找最新的depart值来实现，如下所示：

WITH your_table AS (SELECT 'key_1' KEY, 0 layer_no, 200 col1, 300 col2, 400 col3, 'abc' depart FROM dual UNION ALL
                    SELECT 'key_1' KEY, 1 layer_no, 200 col1, 300 col2, 400 col3, 'abc' depart FROM dual UNION ALL
                    SELECT 'key_1' KEY, 2 layer_no, 200 col1, 300 col2, 600 col3, 'abc' depart FROM dual UNION ALL
                    SELECT 'key_1' KEY, 2 layer_no, 200 col1, 300 col2, 600 col3, 'abc' depart FROM dual UNION ALL
                    SELECT 'key_1' KEY, 3 layer_no, 200 col1, 300 col2, 600 col3, 'xyz' depart FROM dual UNION ALL
                    SELECT 'key_2' KEY, 0 layer_no, 500 col1, 700 col2, 900 col3, 'prq' depart FROM dual UNION ALL
                    SELECT 'key_2' KEY, 1 layer_no, 888 col1, 555 col2, 900 col3, 'prq' depart FROM dual UNION ALL
                    SELECT 'key_3' KEY, 0 layer_no, 111 col1, 222 col2, 333 col3, 'lgh' depart FROM dual UNION ALL
                    SELECT 'key_3' KEY, 1 layer_no, 084 col1, 222 col2, 333 col3, 'lgh' depart FROM dual UNION ALL
                    SELECT 'key_3' KEY, 2 layer_no, 084 col1, 222 col2, 333 col3, 'rrr' depart FROM dual),
   changed_rows AS (SELECT KEY,
                           layer_no,
                           col1,
                           col2,
                           col3,
                           depart,
                           CASE WHEN LAG(col1) OVER (PARTITION BY KEY ORDER BY layer_no) = col1
                                     AND LAG(col2) OVER (PARTITION BY KEY ORDER BY layer_no) = col2
                                     AND LAG(col3) OVER (PARTITION BY KEY ORDER BY layer_no) = col3
                                     THEN 0
                                ELSE 1
                           END changed_cols1_to_3
                    FROM   your_table),
    define_grps AS (SELECT KEY,
                           layer_no,
                           col1,
                           col2,
                           col3,
                           depart,
                           changed_cols1_to_3,
                           SUM(changed_cols1_to_3) OVER (PARTITION BY KEY ORDER BY layer_no) grp
                    FROM   changed_rows)
SELECT KEY,
       grp -1 layer_no,
       col2,
       col2,
       col3,
       MAX(depart) KEEP (dense_rank LAST ORDER BY layer_no) depart
FROM   define_grps
GROUP BY KEY,
         col1,
         col2,
         col3,
         grp;

KEY     LAYER_NO       COL2       COL2       COL3 DEPART
----- ---------- ---------- ---------- ---------- ------
key_1          0        300        300        400 abc
key_1          1        300        300        600 xyz
key_2          0        700        700        900 prq
key_2          1        555        555        900 prq
key_3          1        222        222        333 rrr
key_3          0        222        222        333 lgh

这个 changed_rows 子查询检查col1、col2和col3，查看它是否与前一行的值相同（无更改）或不相同（更改）。我们为更改的行指定值1，为未更改的行指定值0。
这个 define_grps 子查询计算 changed_cols1_to_3 每个键跨所有行的列。这可以对列1、2和3相同的每组连续行进行分组。
最后，我们可以为每组选择最后一行。新的图层是grp数减去1。

赞(0）回复(0）举报 2021-06-25

我来回答

使用窗口函数的sql查询-插入和更新输出的超前和滞后

1条答案

相关问题

热门标签

最新问答