Consider a table with below columns and sample data:
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
There are multiple id and key combination with value of ‘n’ prepopulated. But for each unique key and id combination and weekend in ascending order, values of remaining column needs to be filled.
For every row, n-1 indicates the value of ‘n’ for previous week , n-2 indicate the value of ‘n’ two weeks before and so on. Only previous 11 values of n needs to be considered for each row and the record having no values needs to be filled with null. Following is the required output
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
If you have no gap in your source data, you can obtain the required result with the LAG analytic function:
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
However, if there is a gap (a missing weekend for instance) this will return the wrong result. In this case, you'll have to be more specific with your windowing clause, for instance:
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