Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
Hi there,
again I'm trying to examine one of our tables with some similar coloumns and their correlations.
After counting with grouping sets in a sub query I try to number the result rows ("lfd_nr") and to cumulate the counted numbers ("anzahl" / "anz_kum") by partitioning the results (one window for each grouping set).
| Code: |
SELECT ROW_NUMBER()
OVER (PARTITION BY b.cols_flag
ORDER BY b.cols_flag ASC
, b.col_alpha
, b.col_bravo
, b.col_charlie )
ldf_nr
, b.cols_flag
, b.col_alpha
, b.col_bravo
, b.col_charlie
, b.anzahl
, SUM(b.anzahl)
OVER (PARTITION BY b.cols_flag
ORDER BY b.cols_flag ASC
, b.col_alpha
, b.col_bravo
, b.col_charlie
ROWS UNBOUNDED PRECEDING )
anz_kum
FROM (SELECT a.col_alpha
, a.col_bravo
, a.col_charlie
, a.anzahl
, DECODE(a.col_alpha , NULLIF('{}', '{}'), 'X', ' ')
||DECODE(a.col_bravo , NULLIF('{}', '{}'), 'X', ' ')
||DECODE(a.col_charlie, NULLIF('{}', '{}'), 'X', ' ')
cols_flag
FROM (SELECT col_alpha
, col_bravo
, col_charlie
, COUNT(*) anzahl
FROM my_table
GROUP BY GROUPING SETS
( ( col_alpha )
, ( col_bravo )
, ( col_charlie))
) a
) b
WITH UR
; |
I did several trys with one and two sub queries but none of them were successfull. Whatever i tried, the windows partition clause did not work.
So, the results I got and what I still desire to get I show you in the following table.
| Code: |
LDF_NR LDF_NR COLS_FLAG COL_ALPHA COL_BRAVO COL_CHARLIE ANZAHL ANZ_KUM ANZ_KUM
(result) (desired) (result) (desired)
-------- --------- --------- --------- --------- ----------- ------ -------- ---------
1 1 XX {} {} 70 70 70
2 2 XX 111 {} {} 7552 7622 7622
3 3 XX AAA {} {} 245 7867 7867
4 4 XX ___ {} {} 92 7959 7959
5 1 X X {} 0009 {} 5722 13681 5722
6 2 X X {} 0010 {} 246 13927 5968
7 3 X X {} 0024 {} 115 14042 6083
8 4 X X {} 0059 {} 91 14133 6174
9 5 X X {} 0072 {} 67 14200 6241
10 6 X X {} 0155 {} 29 14229 6270
11 7 X X {} 0172 {} 7 14236 6277
12 8 X X {} 0227 {} 5 14241 6282
13 9 X X {} 0230 {} 557 14798 6839
14 10 X X {} 0306 {} 216 15014 7055
15 11 X X {} 0309 {} 26 15040 7081
16 12 X X {} 0340 {} 1 15041 7082
17 13 X X {} 0384 {} 135 15176 7217
18 14 X X {} 0404 {} 3 15179 7220
19 15 X X {} 0409 {} 3 15182 7223
20 16 X X {} 0414 {} 10 15192 7233
21 17 X X {} 0439 {} 121 15313 7354
22 18 X X {} 0554 {} 35 15348 7389
23 19 X X {} 0614 {} 268 15616 7657
24 20 X X {} 0657 {} 59 15675 7716
25 21 X X {} 0724 {} 5 15680 7721
26 22 X X {} 0727 {} 5 15685 7726
27 23 X X {} 0744 {} 44 15729 7770
28 24 X X {} 0904 {} 2 15731 7772
29 25 X X {} 0907 {} 1 15732 7773
30 26 X X {} 0940 {} 184 15916 7957
31 27 X X {} 0954 {} 2 15918 7959
32 1 XX {} {} 0007 91 16009 91
33 2 XX {} {} 0009 12 16021 103
34 3 XX {} {} 0011 106 16127 209
35 4 XX {} {} 0021 2 16129 211
36 5 XX {} {} 0024 7 16136 218
37 6 XX {} {} 0031 3773 19909 3991
38 7 XX {} {} 0041 752 20661 4743
39 8 XX {} {} 0051 299 20960 5042
40 9 XX {} {} 0071 512 21472 5554
41 10 XX {} {} 0072 217 21689 5771
42 11 XX {} {} 0082 169 21858 5940
43 12 XX {} {} 0083 56 21914 5996
44 13 XX {} {} 0092 96 22010 6092
45 14 XX {} {} 0108 149 22159 6241
46 15 XX {} {} 0109 29 22188 6270
47 16 XX {} {} 0155 3 22191 6273
48 17 XX {} {} 0172 7 22198 6280
49 18 XX {} {} 0218 557 22755 6837
50 19 XX {} {} 0227 216 22971 7053
51 20 XX {} {} 0230 1 22972 7054
52 21 XX {} {} 0306 26 22998 7080
53 22 XX {} {} 0319 1 22999 7081
54 23 XX {} {} 0384 135 23134 7216
55 24 XX {} {} 0554 4 23138 7220
56 25 XX {} {} 0657 3 23141 7223
57 26 XX {} {} 0727 10 23151 7233
58 27 XX {} {} 0744 121 23272 7354
59 28 XX {} {} 0907 35 23307 7389
60 29 XX {} {} 0914 2 23309 7391
61 30 XX {} {} 0944 268 23577 7659
62 31 XX {} {} 1000 59 23636 7718
63 32 XX {} {} 1218 5 23641 7723
64 33 XX {} {} 1309 5 23646 7728
65 34 XX {} {} 1319 3 23649 7731
66 35 XX {} {} 1340 126 23775 7857
67 36 XX {} {} 1404 5 23780 7862
68 37 XX {} {} 1414 7 23787 7869
69 38 XX {} {} 1614 8 23795 7877
70 39 XX {} {} 1724 5 23800 7882
71 40 XX {} {} 2218 5 23805 7887
72 41 XX {} {} 2219 6 23811 7893
73 42 XX {} {} 2319 6 23817 7899
74 43 XX {} {} 2340 2 23819 7901
75 44 XX {} {} 3029 2 23821 7903
76 45 XX {} {} 3319 9 23830 7912
77 46 XX {} {} 3340 31 23861 7943
78 47 XX {} {} 4028 1 23862 7944
79 48 XX {} {} 5118 2 23864 7946
80 49 XX {} {} 6018 2 23866 7948
81 50 XX {} {} 6118 1 23867 7949
82 51 XX {} {} 7118 3 23870 7952
83 52 XX {} {} 8118 4 23874 7956
84 53 XX {} {} 9118 1 23875 7957
85 54 XX {} {} 9218 2 23877 7959 |
Little explanation: {} means NULL.
In the past I was successful with several other ROW_NUMBER and aggregate function usages but this time I don't have any idea why the windows partition clause does not work.
As you can see the auxiliary coloumn COLS_FLAG looks pretty good. But why is it ineffective in the partition clauses?
I guess it's a bungling mistake and a very simple thing. Like it was in my question in thread #66500...
Thank you all for your help |
|
