Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
partitioning row-num and aggregation Grouping Set results

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Auryn

New User


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

PostPosted: Wed Nov 06, 2019 2:46 am    Post subject: partitioning row-num and aggregation Grouping Set results
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to summarize data by grouping on ... santhosh5983 DFSORT/ICETOOL 6 Tue Sep 04, 2018 9:07 am
No new posts How to extract the difference from Su... pkmurali TSO/ISPF 3 Mon Jul 02, 2018 8:44 pm
No new posts Weirdo results with JOINKEYS Balaryan DFSORT/ICETOOL 4 Wed Oct 18, 2017 9:04 pm
No new posts Vision results (also known as DYL-280) CaptBill CA Products 1 Tue Dec 20, 2016 3:35 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us