7 Replies Latest reply: Jan 27, 2011 5:54 AM by Hoek RSS

    analytic function and aggregate function

    833901
      What are analytic function and aggregate function. What is difference between them?
        • 1. Re: analytic function and aggregate function
          Efficientoracle
          Hi,
          Try This. This tells you the basic differences with samples.

          http://www.orafaq.com/node/55
          http://www.oraclepassport.com/Oracle_Analytic_and_Aggregate_Functions.html

          Regards,
          Simma.....
          • 2. Re: analytic function and aggregate function
            Hoek
            You can find answers in the online Oracle documentation:
            http://www.oracle.com/pls/db102/homepage (Database version 10.2)
            http://www.oracle.com/pls/db112/homepage (Database version 11.2)

            Just simply do a quick search and you'll find topics like:
            http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174
            http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#sthref962
            • 3. Re: analytic function and aggregate function
              varun4dba
              hi,

              Analytic Functions :----------

              Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
              Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
              Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.


              Aggregate Functions :----------

              Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
              If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.




              let me know if you are feeling any problem in understanding.
              thanks.

              Edited by: varun4dba on Jan 27, 2011 3:32 PM
              • 4. Re: analytic function and aggregate function
                Efficientoracle
                HI,


                aggregates take "many rows, collapse them into one"

                analytics "do not, there is no row collapsing"


                the two are orthogonal concepts really.


                Regards,
                Simma......
                • 5. Re: analytic function and aggregate function
                  833901
                  If a particular problem can be solved by both, then which one to prefer. I am speaking in general, if there are two solutions, then what?
                  • 6. Re: analytic function and aggregate function
                    BluShadow
                    830898 wrote:
                    If a particular problem can be solved by both, then which one to prefer. I am speaking in general, if there are two solutions, then what?
                    You'd be hard pushed to find a problem that would be solved by both directly. Aggregate functions require the data to be grouped together whereas Analytical functions don't, they just calculate the values for each row based on a window of the data (essentially looking at a group of data without actually grouping it in the result).

                    You could take a query with an analytical query and then group the data down afterwards, but then why would you use the analytical function in the first place.
                    • 7. Re: analytic function and aggregate function
                      Hoek
                      If a particular problem can be solved by both, then which one to prefer. I am speaking in general, if there are two solutions, then what?
                      Here's a thread on how Tom Kyte looks at it:
                      http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:74525921631614