Difference Between Analytic Functions and Aggregate Functions
I have already posted a Blog to show
the difference between AGGREGATE and ANALYTICAL Function. This blog is just to
show some more examples to demonstrate the difference.
To read about earlier
blog - Click here.
Consider below table:
SQL> CREATE TABLE SALES
2 (
3 SALE_ID INTEGER,
4 PRODUCT_ID INTEGER,
5 YEAR INTEGER,
6 Quantity INTEGER,
7 PRICE INTEGER
8 );
Table created.
SQL> INSERT INTO SALES VALUES ( 1, 100, 2008, 10, 5000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 2, 100, 2009, 12, 5000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 3, 100, 2010, 25, 5000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 4, 100, 2011, 16, 5000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 5, 100, 2012, 8, 5000);
1 row created.
SQL>
SQL> INSERT INTO SALES VALUES ( 6, 200, 2010, 10, 9000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 7, 200, 2011, 15, 9000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 8, 200, 2012, 20, 9000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 9, 200, 2008, 13, 9000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 10,200, 2009, 14, 9000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 11, 300, 2010, 20, 7000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 12, 300, 2011, 18, 7000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 13, 300, 2012, 20, 7000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 14, 300, 2008, 17, 7000);
1 row created.
SQL> INSERT INTO SALES VALUES ( 15, 300, 2009, 19, 7000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * From SALES;
SALE_ID PRODUCT_ID YEAR QUANTITY PRICE
---------- ---------- ---------- ---------- ----------
1 100 2008 10 5000
2 100 2009 12 5000
3 100 2010 25 5000
4 100 2011 16 5000
5 100 2012 8 5000
6 200 2010 10 9000
7 200 2011 15 9000
8 200 2012 20 9000
9 200 2008 13 9000
10 200 2009 14 9000
11 300 2010 20 7000
12 300 2011 18 7000
13 300 2012 20 7000
14 300 2008 17 7000
15 300 2009 19 7000
15 rows selected.
Q. Write a query to find the number of products sold in each year?
The SQL query Using Aggregate functions is
SQL> SELECT Year, COUNT(1) CNT FROM SALES
2 GROUP BY YEAR;
YEAR CNT
---------- ----------
2009 3
2010 3
2011 3
2008 3
2012 3
The SQL query Using Analytical functions is
SQL> SELECT SALE_ID, PRODUCT_ID, Year, QUANTITY, PRICE,
2 COUNT(1) OVER (PARTITION BY YEAR) CNT
3 FROM SALES;
SALE_ID PRODUCT_ID YEAR QUANTITY PRICE CNT
---------- ---------- ---------- ---------- ---------- ----------
9 200 2008 13 9000 3
1 100 2008 10 5000 3
14 300 2008 17 7000 3
15 300 2009 19 7000 3
2 100 2009 12 5000 3
10 200 2009 14 9000 3
11 300 2010 20 7000 3
6 200 2010 10 9000 3
3 100 2010 25 5000 3
12 300 2011 18 7000 3
4 100 2011 16 5000 3
7 200 2011 15 9000 3
13 300 2012 20 7000 3
5 100 2012 8 5000 3
8 200 2012 20 9000 3
15 rows selected.
From the outputs, you can observe that the aggregate functions return only one row per group whereas analytic functions keeps all the rows in the group. Using the aggregate functions, the select clause contains only the columns specified in group by clause and aggregate functions whereas in analytic functions you can specify all the columns in the table.
The PARTITION BY clause is similar to GROUP By clause, it specifies the window of rows that the analytic function should operate on.
I hope you got some basic idea about aggregate and analytic functions. Now let’s start with solving the Interview Questions on Oracle Analytic Functions.
Q. Write a SQL query using the analytic function to find the total sales(QUANTITY) of each product?
Solution:
SUM analytic function can be used to find the total sales. The SQL query is
SQL> SELECT PRODUCT_ID, QUANTITY,
2 SUM(QUANTITY) OVER( PARTITION BY PRODUCT_ID ) TOT_SALES
3 FROM SALES;
PRODUCT_ID QUANTITY TOT_SALES
---------- ---------- ----------
100 12 71
100 10 71
100 25 71
100 16 71
100 8 71
200 15 72
200 10 72
200 20 72
200 14 72
200 13 72
300 20 94
300 18 94
300 17 94
300 20 94
300 19 94
15 rows selected.
Q. Write a SQL query to find the cumulative sum of sales(QUANTITY) of each product? Here first sort the QUANTITY in ascending order for each product and then accumulate the QUANTITY.
Cumulative sum of QUANTITY for a product = QUANTITY of current row + sum of QUANTITIES all previous rows in that product.
Solution:
We have to use the option "ROWS UNBOUNDED PRECEDING" in the SUM analytic function to get the cumulative sum. The SQL query to get the output is
SQL> SELECT PRODUCT_ID, QUANTITY, SUM(QUANTITY) OVER( PARTITION BY PRODUCT_ID
2 ORDER BY QUANTITY ASC ROWS UNBOUNDED PRECEDING) CUM_SALES
3 FROM SALES;
PRODUCT_ID QUANTITY CUM_SALES
---------- ---------- ----------
100 8 8
100 10 18
100 12 30
100 16 46
100 25 71
200 10 10
200 13 23
200 14 37
200 15 52
200 20 72
300 17 17
300 18 35
300 19 54
300 20 74
300 20 94
15 rows selected.
The ORDER BY clause is used to sort the data. Here the ROWS UNBOUNDED PRECEDING option specifies that the SUM analytic function should operate on the current row and the pervious rows processed.
Q. Write a SQL query to find the sum of sales of current row and previous 2 rows in a product group? Sort the data on sales and then find the sum.
Solution:
The SQL query for the required output is
SQL> SELECT PRODUCT_ID, QUANTITY,
2 SUM(QUANTITY) OVER(PARTITION BY PRODUCT_ID ORDER BY QUANTITY DESC
3 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) CALC_SALES
4 FROM SALES;
PRODUCT_ID QUANTITY CALC_SALES
---------- ---------- ----------
100 25 25
100 16 41
100 12 53
100 10 38
100 8 30
200 20 20
200 15 35
200 14 49
200 13 42
200 10 37
300 20 20
300 20 40
300 19 59
300 18 57
300 17 54
15 rows selected.
The ROWS BETWEEN clause specifies the range of rows to consider for calculating the SUM.
Q. Write a SQL query to find the Median of sales of a product?
Solution:
The SQL query for calculating the median is
SQL> SELECT PRODUCT_ID, QUANTITY,
2 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY QUANTITY ASC)
3 OVER (PARTITION BY PRODUCT_ID) MEDIAN
4 FROM SALES;
PRODUCT_ID QUANTITY MEDIAN
---------- ---------- ----------
100 8 12
100 10 12
100 12 12
100 16 12
100 25 12
200 10 14
200 13 14
200 14 14
200 15 14
200 20 14
300 17 19
300 18 19
300 19 19
300 20 19
300 20 19
15 rows selected.
Q. Write a SQL query to find the minimum sales of a product without using the group by clause.
Solution:
The SQL query is
SQL> SELECT PRODUCT_ID, YEAR, QUANTITY
2 FROM
3 (
4 SELECT PRODUCT_ID, YEAR, QUANTITY,
5 ROW_NUMBER() OVER(PARTITION BY PRODUCT_ID
6 ORDER BY QUANTITY ASC) MIN_SALE_RANK
7 FROM SALES
8 ) WHERE MIN_SALE_RANK = 1;
PRODUCT_ID YEAR QUANTITY
---------- ---------- ----------
100 2012 8
200 2010 10
300 2008 17
this one looked bit complex to me with very less explanation as compared to other topics
ReplyDeleteI'd like to thank you for taking the time and effort it took to create this blog. Normally, I don't leave comments on blogs, but your article is so compelling that I can't help but leave my opinion on this one. IAPPC, a leading Netsuite Partner in India for ERP installation and solution provider, provides end-to-end implementation services and support to meet the organization's demands.
ReplyDeleteIf you are looking for contact information for QuickBooks, then be sure to give them a call at Quickbooks Customer Service +1 855-675-3194.
ReplyDelete