11 Replies Latest reply: Mar 18, 2013 8:36 AM by UW (Germany) RSS

    Is there a shorter (better) way with analytical functions?

    UW (Germany)
      Here is a small test case:
      create table t 
      ( id   number,
        pos  number,
        typ  number,
        m    number);
        
      insert into t values (1,1,1,100);
      insert into t values (1,2,1,100);
      insert into t values (1,3,2, 50);
      insert into t values (2,1,3, 30);
      insert into t values (2,2,4, 70);
      insert into t values (3,1,1,100);
      insert into t values (3,2,2, 50);
      insert into t values (4,1,3, 30);
      insert into t values (4,2,5, 80);
      insert into t values (4,3,3, 30);
      insert into t values (5,1,3, 30);
      insert into t values (5,2,6, 30);
      insert into t values (6,1,2, 50);
      insert into t values (6,2,7, 50);
      insert into t values (6,3,2, 50);
      insert into t values (7,1,4, 70);
      insert into t values (7,2,4, 70);
      insert into t values (7,3,4, 70);
      For each id I would like to sum all m values only when they have a different type. This would be the long way:
      with t1 as
        (select 
           id, 
           typ, 
           min(m) m1 
         from t
         group by id, typ)
      select
        id,
        sum(m1) f
      from t1
      group by id
      order by 1;
      
              ID          F
      ---------- ----------
               1        150 
               2        100 
               3        150 
               4        110 
               5         60 
               6        100 
               7         70 
      but I wonder, whether is it possible to get this result with a single select using analytical functions, something like
      select 
        id, 
        sum(m) over (partition by distinct typ) F    -- this does not work. It's only an idea how it might look like
      from t
      group by id;
        • 1. Re: Is there a shorter (better) way with analytical functions?
          Manik
          Try this...
            SELECT DISTINCT id, SUM (DISTINCT m) OVER (PARTITION BY id)
              FROM t
          ORDER BY 1;
          Cheers,
          Manik.
          • 2. Re: Is there a shorter (better) way with analytical functions?
            chris227
            This does first a grouping over id, type with calculating the min for each id, type combination.
            Afterwards for each id the sum of the mins (of each combination of id, type for this particular id) is summed up.
            select distinct
             id, sum(min(m)) over (partition by id)
            from data
            group by id, typ
            order by id
            Edited by: chris227 on 15.03.2013 07:39
            • 3. Re: Is there a shorter (better) way with analytical functions?
              €$ħ₪
              Using Analytical...
                SELECT ID, SUM (SM)
                  FROM (  SELECT ID, SUM (MIN (M)) OVER (PARTITION BY ID, TYP) SM
                            FROM TAB_MIN
                        GROUP BY ID, TYP)
              GROUP BY ID
              ORDER BY 1
              • 4. Re: Is there a shorter (better) way with analytical functions?
                UW (Germany)
                This looks for distinct m-values, but I need different typ-values.
                • 5. Re: Is there a shorter (better) way with analytical functions?
                  UW (Germany)
                  Your solution has also a nested select. I think Chris227's solution is the better one.
                  • 6. Re: Is there a shorter (better) way with analytical functions?
                    Manik
                    ok I missed it :)

                    Cheers,
                    Manik.
                    • 7. Re: Is there a shorter (better) way with analytical functions?
                      Frank Kulash
                      Hi,
                      UW (Germany) wrote:
                      Here is a small test case:
                      Thanks or posting the CREATE TABLE and INSERT statements; that's very helpful!
                      ... but I wonder, whether is it possible to get this result with a single select using analytical functions, something like
                      select 
                      id, 
                      sum(m) over (partition by distinct typ) F    -- this does not work. It's only an idea how it might look like
                      from t
                      group by id;
                      You weren't very far off. That's kind of what Chris's solution does look like.
                      I think Chris gave the best solution, but you may be interested in why that is necessary, and why other approaches won't work.

                      The problem here is that you need some kind of nesting: you want to get the SUM of a bunch of MINs. How can you nest analytic and/or aggregate functions within one another? In this problem, you have your pick of aggregate or analtytic functions, since both of the functions you want, MIN and SUM, have aggregate and ananlytic versions that can produce the same results.

                      You can nest an aggregate function within another aggregate function. When you do that, the GROUP BY clause applies to the inner function, and the result set consists of one row, as if the outer function was being done without a GROUP BY clause. That doesn't help in this problem, snce you need separate output rows for each id.

                      You can not nest analytic functions, or have one analytic function depend on another in any way. You can always compute one function (either analytic or aggregate) in a sub-query, and have another function (analytic or aggregate) operate on those results in a super-query. That's what you did in your original message: you couldn't acutally nest the MIN inside the SUM, but you got the same effect by using sub-queries.

                      Analytic functions are computed after the GROUP BY clause has been applied and aggregate functions have been computed, so you can't nest an analytic function within an aggregate function. You can, however, nest an aggregate function inside an analytic function, and that's what Chirs and Ekh both did. They differed only in how they removed the duplicate rows from the result set.
                      • 8. Re: Is there a shorter (better) way with analytical functions?
                        Manik
                        Another way of trying.... (almost similar approach as of Chris but removed min function inside the sum)
                          SELECT DISTINCT id, SUM (m) OVER (PARTITION BY id) val
                            FROM t
                        GROUP BY id, m, typ
                        ORDER BY 1;
                        This time I will print output :) just to match the requirement.
                        ID     VAL
                        ------------------
                        1     150
                        2     100
                        3     150
                        4     110
                        5     60
                        6     100
                        7     70
                        Cheers,
                        Manik.
                        • 9. Re: Is there a shorter (better) way with analytical functions?
                          chris227
                          Manik wrote:
                          Another way of trying.... (almost similar approach as of Chris but removed min function inside the sum)
                          SELECT DISTINCT id, SUM (m) OVER (PARTITION BY id) val
                          FROM t
                          GROUP BY id, m, typ
                          ORDER BY 1;
                          This time I will print output :) just to match the requirement.
                          If you read the first post of OP carefully, you will notice the min(m).
                          In my understanding this shows that there may be different m for same type, and then min(m) should be choosen.

                          Therefor a group by id, m, typ will be correct if m and typ always correspond to each other, but not if they dont.
                          • 10. Re: Is there a shorter (better) way with analytical functions?
                            Manik
                            Hey thanks for clarifying Chris.

                            Cheers,
                            Manik.
                            • 11. Re: Is there a shorter (better) way with analytical functions?
                              UW (Germany)
                              After the weekend and after some more urgent work that I had today, I come back to this thread. I’m not sure whether you will read this, as it is marked as answered already. First: thank you all for your answers. And just to clarify this: In my specific problem same types can’t have different m-values. So the min() is not necessary. In my long example
                              with t1 as
                                (select 
                                   id, 
                                   typ, 
                                   min(m) m1 
                                 from t
                                 group by id, typ)
                              …
                              the min(m) could also have been max(m) or avg(m) and it just did not came to my mind to put "m" in the "group by"-clause like "id" and "typ" because this is the numeric value that is used for calculations. And if the min() would be important, I would have used it also in my "not working example"
                              ... sum(m) over (partition by distinct typ) ...
                              Just for a better understanding of the real world problem behind my question here is a rough simplification: There are several requests, represented by the id. Such a request can be fulfilled by different articles (typ), so for every request there are several positions (pos), one for each article that is suitable for this request (but as there is more information in the position, it might be possible that several positions of one request point to the same article). On the other hand there is a stock with an amount (m) for each article and the query has to show for every request the total number of all articles on stock that can be used for each request.