IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

partitioning row-num and aggregation Grouping Set results


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Auryn

New User


Joined: 11 Jan 2006
Posts: 67
Location: Lower Saxony (DE)

PostPosted: Wed Nov 06, 2019 2:46 am
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts COBOL NOADVANCING strange results in ... COBOL Programming 4
No new posts Complex counting and grouping DFSORT/ICETOOL 8
No new posts How to summarize data by grouping on ... DFSORT/ICETOOL 6
No new posts How to extract the difference from Su... TSO/ISPF 3
No new posts Weirdo results with JOINKEYS DFSORT/ICETOOL 4
Search our Forums:

Back to Top