根据前一行中的值更新行
根据前一行中的值更新行
提问于 2014-02-25 14:02:34
考虑具有以下列和示例数据的表：
id key Week-end n-11 n-10 n-9 n-8 n-7 n-6 n-5 n-4 n-3 n-2 n-1 n
255 696 10/31/2013 7
255 696 11/14/2013 6
255 696 11/28/2013 2
255 696 12/12/2013 5
255 696 12/26/2013
255 696 1/9/2014 6
255 696 1/23/2014
255 696 1/30/2014 8
255 696 1/16/2014 9
255 696 1/2/2014 5
255 696 12/19/2013
255 696 12/5/2013 3
255 696 11/21/2013
255 696 11/7/2013有多个id和键组合，值为‘n’预填充。但是对于每个唯一的key和id组合以及按升序排列的周末，需要填充剩余列的值。对于每一行，n-1表示前一周的‘n’值，n-2表示前两周的‘n’值，依此类推。每一行只需要考虑n的前11个值，没有值的记录需要用null填充。以下是所需的输出
id key Week-end n-11 n-10 n-9 n-8 n-7 n-6 n-5 n-4 n-3 n-2 n-1 n
255 696 10/31/2013 7
255 696 11/7/2013 7
255 696 11/14/2013 7 6
255 696 11/21/2013 7 6
255 696 11/28/2013 7 6 2
255 696 12/5/2013 7 6 2 3
255 696 12/12/2013 7 6 2 3 5
255 696 12/19/2013 7 6 2 3 5
255 696 12/26/2013 7 6 2 3 5
255 696 1/2/2014 7 6 2 3 5 5
255 696 1/9/2014 7 6 2 3 5 5 6
255 696 1/16/2014 7 6 2 3 5 5 6 9
255 696 1/23/2014 6 2 3 5 5 6 9
255 696 1/30/2014 6 2 3 5 5 6 9 8回答 1
Stack Overflow用户
回答已采纳
发布于 2014-02-25 14:25:02
如果源数据中没有空白，则可以使用LAG解析函数获得所需的结果：
SQL> SELECT ID, k, weekend,
2 --
3 LAG(n, 11) OVER(PARTITION BY ID, k ORDER BY weekend) "n-11",
4 LAG(n, 10) OVER(PARTITION BY ID, k ORDER BY weekend) "n-10",
5 LAG(n, 9) OVER(PARTITION BY ID, k ORDER BY weekend) "n-9",
6 LAG(n, 8) OVER(PARTITION BY ID, k ORDER BY weekend) "n-8",
7 LAG(n, 7) OVER(PARTITION BY ID, k ORDER BY weekend) "n-7",
8 LAG(n, 6) OVER(PARTITION BY ID, k ORDER BY weekend) "n-6",
9 LAG(n, 5) OVER(PARTITION BY ID, k ORDER BY weekend) "n-5",
10 LAG(n, 4) OVER(PARTITION BY ID, k ORDER BY weekend) "n-4",
11 LAG(n, 3) OVER(PARTITION BY ID, k ORDER BY weekend) "n-3",
12 LAG(n, 2) OVER(PARTITION BY ID, k ORDER BY weekend) "n-2",
13 LAG(n, 1) OVER(PARTITION BY ID, k ORDER BY weekend) "n-1",
14 --
15 n
16 FROM TEST;
ID K WEEKEND n-11 n-10 n-9 n-8 n-7 n-6 n-5 n-4 n-3 n-2 n-1 N
---- ---- ----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
255 696 31/10/2013 7
255 696 07/11/2013 7
255 696 14/11/2013 7 6
255 696 21/11/2013 7 6
255 696 28/11/2013 7 6 2
255 696 05/12/2013 7 6 2 3
255 696 12/12/2013 7 6 2 3 5
255 696 19/12/2013 7 6 2 3 5
255 696 26/12/2013 7 6 2 3 5
255 696 02/01/2014 7 6 2 3 5 5
255 696 09/01/2014 7 6 2 3 5 5 6
255 696 16/01/2014 7 6 2 3 5 5 6 9
255 696 23/01/2014 6 2 3 5 5 6 9
255 696 30/01/2014 6 2 3 5 5 6 9 8然而，如果有一个缺口(例如，一个缺少的周末)，这将返回错误的结果。在这种情况下，您必须更具体地使用窗口子句，例如：
SQL> SELECT ID, k, weekend,
2 --
3 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*11 PRECEDING AND 7*11 PRECEDING) "n-11",
4 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*10 PRECEDING AND 7*10 PRECEDING) "n-10",
5 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*9 PRECEDING AND 7*9 PRECEDING) "n-9",
6 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*8 PRECEDING AND 7*8 PRECEDING) "n-8",
7 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*7 PRECEDING AND 7*7 PRECEDING) "n-7",
8 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*6 PRECEDING AND 7*6 PRECEDING) "n-6",
9 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*5 PRECEDING AND 7*5 PRECEDING) "n-5",
10 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*4 PRECEDING AND 7*4 PRECEDING) "n-4",
11 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*3 PRECEDING AND 7*3 PRECEDING) "n-3",
12 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*2 PRECEDING AND 7*2 PRECEDING) "n-2",
13 first_value(n) OVER(PARTITION BY ID, k ORDER BY weekend RANGE BETWEEN 7*1 PRECEDING AND 7*1 PRECEDING) "n-1",
14 --
15 n
16 FROM TEST;
ID K WEEKEND n-11 n-10 n-9 n-8 n-7 n-6 n-5 n-4 n-3 n-2 n-1 N
---- ---- ----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
255 696 31/10/2013 7
255 696 07/11/2013 7
255 696 14/11/2013 7 6
255 696 21/11/2013 7 6
255 696 28/11/2013 7 6 2
255 696 05/12/2013 7 6 2 3
255 696 12/12/2013 7 6 2 3 5
255 696 19/12/2013 7 6 2 3 5
255 696 26/12/2013 7 6 2 3 5
255 696 02/01/2014 7 6 2 3 5 5
255 696 09/01/2014 7 6 2 3 5 5 6
255 696 16/01/2014 7 6 2 3 5 5 6 9
255 696 23/01/2014 6 2 3 5 5 6 9
255 696 30/01/2014 6 2 3 5 5 6 9 8页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22016478
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号