{"id":73148,"date":"2015-09-17T13:47:30","date_gmt":"2015-09-17T13:47:30","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/introduction-to-analytic-functions-part-2\/"},"modified":"2021-07-14T13:07:20","modified_gmt":"2021-07-14T13:07:20","slug":"introduction-to-analytic-functions-part-2","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/introduction-to-analytic-functions-part-2\/","title":{"rendered":"Introduction to Analytic Functions (Part 2)"},"content":{"rendered":"

In the first part of this series<\/a> I introduced you to the analytic functions family, outlined its close relationship to aggregate functions, and illustrated my points with a few examples. I demonstrated how, by clever use of the analytic function clauses \u2013 partition by, order by, and the windowing clause \u2013 you could tune your functions to wring even more information from your data.<\/p>\n

That’s great and all that, but there’s more. In this second article we’ll look at the various types of analytic functions that are available to us, and we’ll get our hands dirty with a few more examples.\u00a0 After all, no one ever became a great footballer just by talking<\/em> about football; you’ve got to go out there and kick a few balls.<\/p>\n

So let’s kick balls.<\/p>\n

Types of Analytic Functions<\/h5>\n

You probably don’t need me to tell you this, but I’ll tell you anyway; analytic functions have the following syntax:<\/p>\n

FUNCTION (params) OVER ([PARTITION BY <\u2026>] [ORDER BY <\u2026>][window_clause])\r\n<\/pre>\n
The clauses \u2013 partition by, order by, and the window clause \u2013 are optional; or, to put it more accurately, might<\/em> be optional. It depends on the function. There are a few dozen different analytic functions in Oracle, and you could split them into two camps \u2013 those for which the ORDER BY clause is mandatory and those where it is optional.<\/p>\n
Alternatively, you could split them into those that have equivalent aggregate functions, and those that are purely analytic functions with no aggregate little brother.<\/p>\n
Or, perhaps more sensibly, you can group analytic functions by what they do.<\/p>\n
    \n
  • There are the Arithmetic analytic functions, such as MAX, MIN, COUNT, AVG, MEDIAN and SUM.<\/li>\n
  • There are the Statistical analytic functions: STDVAR, CORR, COVAR_POP, VARIANCE, CUME_DIST and a few others.<\/li>\n
  • There are the Ranking \u2013 or Positional \u2013 analytic functions: DENSE_RANK, FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, PERCENT_RANK, RANK, ROW_NUMBER and a few others.<\/li>\n
  • There are the Relative analytic functions: LEAD, LAG.<\/li>\n
  • Since 12c, there are the data mining analytic functions: CLUSTER_DETAILS, FEATURE_DETAILS, PREDICTION_DETAILS and a few others.<\/li>\n
  • And finally, there are others that do not fit neatly into a larger group, such as RATIO_TO_REPORT and LISTAGG.<\/li>\n<\/ul>\n
    \"Windowing
    \nObviously, we can’t discuss every single different analytic function; it’d take too long \u2013 and, if I’m being honest, I haven’t used every one of them in my career. But if we pick a representative few, it’ll give you a good sense of how and when to use analytic functions.<\/p>\n
    MAX, MIN, AVG<\/h5>\n
    These functions are pretty straightforward, and don’t need much introduction, so let’s jump straight into an example.\u00a0 Say you wanted to produce a report of every employee, their salary, and a comparison of that salary to others within their departments.<\/p>\n
    SELECT ename, deptno, sal,\r\nAVG(sal) OVER (PARTITION BY deptno) \"Average Dept Salary\",\r\nMIN(sal) OVER (PARTITION BY deptno) \"Minimum Dept Salary\",\r\nMAX(sal) OVER (PARTITION BY deptno) \"Maximum Dept Salary\"\r\nFROM emp\r\nORDER BY ename;\r\n<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    ENAME<\/strong><\/td>\nDEPTNO<\/strong><\/td>\nSAL<\/strong><\/td>\nAverage Dept Salary<\/strong><\/td>\nMinimum Dept Salary<\/strong><\/td>\nMaximum Dept Salary<\/strong><\/td>\n<\/tr>\n
    ADAMS<\/td>\n20<\/td>\n1100<\/td>\n2175<\/td>\n800<\/td>\n3000<\/td>\n<\/tr>\n
    ALLEN<\/td>\n30<\/td>\n1600<\/td>\n1566.67<\/td>\n950<\/td>\n2850<\/td>\n<\/tr>\n
    BLAKE<\/td>\n30<\/td>\n2850<\/td>\n1566.67<\/td>\n950<\/td>\n2850<\/td>\n<\/tr>\n
    CLARK<\/td>\n10<\/td>\n2450<\/td>\n2916.67<\/td>\n1300<\/td>\n5000<\/td>\n<\/tr>\n
    FORD<\/td>\n20<\/td>\n3000<\/td>\n2175<\/td>\n800<\/td>\n3000<\/td>\n<\/tr>\n
    JAMES<\/td>\n30<\/td>\n950<\/td>\n1566.67<\/td>\n950<\/td>\n2850<\/td>\n<\/tr>\n
    JONES<\/td>\n20<\/td>\n2975<\/td>\n2175<\/td>\n800<\/td>\n3000<\/td>\n<\/tr>\n
    KING<\/td>\n10<\/td>\n5000<\/td>\n2916.67<\/td>\n1300<\/td>\n5000<\/td>\n<\/tr>\n
    MARTIN<\/td>\n30<\/td>\n1250<\/td>\n1566.67<\/td>\n950<\/td>\n2850<\/td>\n<\/tr>\n
    MILLER<\/td>\n10<\/td>\n1300<\/td>\n2916.67<\/td>\n1300<\/td>\n5000<\/td>\n<\/tr>\n
    SCOTT<\/td>\n20<\/td>\n3000<\/td>\n2175<\/td>\n800<\/td>\n3000<\/td>\n<\/tr>\n
    SMITH<\/td>\n20<\/td>\n800<\/td>\n2175<\/td>\n800<\/td>\n3000<\/td>\n<\/tr>\n
    TURNER<\/td>\n30<\/td>\n1500<\/td>\n1566.67<\/td>\n950<\/td>\n2850<\/td>\n<\/tr>\n
    WARD<\/td>\n30<\/td>\n1250<\/td>\n1566.67<\/td>\n950<\/td>\n2850<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     <\/p>\n
    LISTAGG<\/h5>\n
    Outside of the set of arithmetic analytic functions, this is probably the one that I find myself using the most.\u00a0 LISTAGG concatenates the contents of a column into a VARCHAR2 string; basically, it takes vertical data and allows you display it horizontally.<\/p>\n
    The LISTAGG syntax is worth a look, as it is a little different.<\/p>\n
    LISTAGG (expr [, delimiter]) WITHIN GROUP (ORDER BY expr) [OVER (PARTITION BY)]\r\n<\/pre>\n
    Here’s an example.\u00a0 Say we wanted to obtain a list of all our employees and all the colleagues that share a department with them, we’d turn to LISTAGG.<\/p>\n
    SELECT ename, deptno,\r\nLISTAGG (ename, ',') WITHIN GROUP (ORDER BY ename) OVER (PARTITION BY deptno) AS \"Colleagues\"\r\nFROM emp\r\nORDER BY ename;\r\n<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    ENAME<\/strong><\/td>\nDEPTNO<\/strong><\/td>\nColleagues<\/strong><\/td>\n<\/tr>\n
    ADAMS<\/td>\n20<\/td>\nADAMS,FORD,JONES,SCOTT,SMITH<\/td>\n<\/tr>\n
    ALLEN<\/td>\n30<\/td>\nALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD<\/td>\n<\/tr>\n
    BLAKE<\/td>\n30<\/td>\nALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD<\/td>\n<\/tr>\n
    CLARK<\/td>\n10<\/td>\nCLARK,KING,MILLER<\/td>\n<\/tr>\n
    FORD<\/td>\n20<\/td>\nADAMS,FORD,JONES,SCOTT,SMITH<\/td>\n<\/tr>\n
    JAMES<\/td>\n30<\/td>\nALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD<\/td>\n<\/tr>\n
    JONES<\/td>\n20<\/td>\nADAMS,FORD,JONES,SCOTT,SMITH<\/td>\n<\/tr>\n
    KING<\/td>\n10<\/td>\nCLARK,KING,MILLER<\/td>\n<\/tr>\n
    MARTIN<\/td>\n30<\/td>\nALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD<\/td>\n<\/tr>\n
    MILLER<\/td>\n10<\/td>\nCLARK,KING,MILLER<\/td>\n<\/tr>\n
    SCOTT<\/td>\n20<\/td>\nADAMS,FORD,JONES,SCOTT,SMITH<\/td>\n<\/tr>\n
    SMITH<\/td>\n20<\/td>\nADAMS,FORD,JONES,SCOTT,SMITH<\/td>\n<\/tr>\n
    TURNER<\/td>\n30<\/td>\nALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD<\/td>\n<\/tr>\n
    WARD<\/td>\n30<\/td>\nALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     <\/p>\n
    RANK, DENSE_RANK<\/h5>\n
    RANK and DENSE_RANK are very closely related; they do pretty much the same thing, with one small \u2013 significant \u2013 difference. They both return the position \u2013 the rank<\/em> \u2013 of the row in an ordered group. The only difference is that DENSE_RANK determines that position based on a count of the unique<\/em> values in the list; RANK, on the other hand, gives rows with an equal value an equal rank, which can result in gaps in the ranking. If two rows have an equal value and rank as Position 1, the next available position using RANK will be Position 3; using DENSE_RANK, it will be Position 2.<\/p>\n
    Here’s the syntax:<\/p>\n
    DENSE_RANK|RANK () OVER ([PARTITION BY <\u2026>] ORDER BY expr)\r\n<\/pre>\n
     <\/p>\n
    SELECT ename, sal,\r\n  RANK () OVER (ORDER BY sal) AS \"Rank\",\r\n  DENSE_RANK () OVER (ORDER BY sal) AS \"Dense Rank\"\r\nFROM emp\r\nORDER BY sal;\r\n<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    ENAME<\/strong><\/td>\nSAL<\/strong><\/td>\nRank<\/strong><\/td>\nDense_Rank<\/strong><\/td>\n<\/tr>\n
    SMITH<\/td>\n800<\/td>\n1<\/td>\n1<\/td>\n<\/tr>\n
    JAMES<\/td>\n950<\/td>\n2<\/td>\n2<\/td>\n<\/tr>\n
    ADAMS<\/td>\n1100<\/td>\n3<\/td>\n3<\/td>\n<\/tr>\n
    MARTIN<\/td>\n1250<\/td>\n4<\/td>\n4<\/td>\n<\/tr>\n
    WARD<\/td>\n1250<\/td>\n4<\/td>\n4<\/td>\n<\/tr>\n
    MILLER<\/td>\n1300<\/td>\n6<\/td>\n5<\/td>\n<\/tr>\n
    TURNER<\/td>\n1500<\/td>\n7<\/td>\n6<\/td>\n<\/tr>\n
    ALLEN<\/td>\n1600<\/td>\n8<\/td>\n7<\/td>\n<\/tr>\n
    CLARK<\/td>\n2450<\/td>\n9<\/td>\n8<\/td>\n<\/tr>\n
    BLAKE<\/td>\n2850<\/td>\n10<\/td>\n9<\/td>\n<\/tr>\n
    JONES<\/td>\n2975<\/td>\n11<\/td>\n10<\/td>\n<\/tr>\n
    FORD<\/td>\n3000<\/td>\n12<\/td>\n11<\/td>\n<\/tr>\n
    SCOTT<\/td>\n3000<\/td>\n12<\/td>\n11<\/td>\n<\/tr>\n
    KING<\/td>\n5000<\/td>\n14<\/td>\n12<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     <\/p>\n
    The way that the RANK and the DENSE_RANK values fork at the Miller record demonstrates their subtle difference.<\/p>\n
    Finally, even though this is an article on analytic functions, I think it’s worth spending a little time looking at the aggregate function equivalents for RANK and DENSE_RANK. They return a (dense) rank for a hypothetical <\/em>value in the ordered list.\u00a0 Imagine you were planning to take on a new member of staff at a salary of $2650, and you wondered where that would place her within the company hierarchy.<\/p>\n
    The syntax for the RANK and DENSE_RANK aggregate functions are a little different from that of their analytic cousins.<\/p>\n
    DENSE_RANK|RANK (expr) WITHIN GROUP (ORDER BY expr)\r\n<\/pre>\n
    SELECT DENSE_RANK (2650) WITHIN GROUP (ORDER BY sal) AS \"Dense_rank\",\r\n  RANK (2650) WITHIN GROUP (ORDER BY sal) AS \"Rank\"\r\nFROM emp;\r\n<\/pre>\n\n\n\n\n
    Dense_rank<\/strong><\/td>\nRank<\/strong><\/td>\n<\/tr>\n
    9<\/td>\n10<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     <\/p>\n
    ROW_NUMBER<\/h5>\n
    A good corollary following on from RANK and DENSE_RANK is ROW_NUMBER. Unlike the other two, ROW_NUMBER returns a sequential ranking of a row within an ordered list, making no special allowance for rows of equal value.<\/p>\n
    And if you’re wondering why we need ROW_NUMBER when we’ve got the perfectly good rownum<\/em> pseudocolumn, then you’re forgetting the importance of the analytic function clauses. Firstly, using PARTITION BY, we can get the row number within <\/em>a group.<\/p>\n
    SELECT ename, deptno, sal,\r\nROW_NUMBER() OVER (PARTITION BY deptno ORDER BY deptno, sal desc) AS \"Position in Dept\"\r\nFROM emp\r\nORDER BY deptno, sal desc;\r\n<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    ENAME<\/strong><\/td>\nDEPTNO<\/strong><\/td>\nSAL<\/strong><\/td>\nPosition in Dept<\/strong><\/td>\n<\/tr>\n
    KING<\/td>\n10<\/td>\n5000<\/td>\n1<\/td>\n<\/tr>\n
    CLARK<\/td>\n10<\/td>\n2450<\/td>\n2<\/td>\n<\/tr>\n
    MILLER<\/td>\n10<\/td>\n1300<\/td>\n3<\/td>\n<\/tr>\n
    SCOTT<\/td>\n20<\/td>\n3000<\/td>\n1<\/td>\n<\/tr>\n
    FORD<\/td>\n20<\/td>\n3000<\/td>\n2<\/td>\n<\/tr>\n
    JONES<\/td>\n20<\/td>\n2975<\/td>\n3<\/td>\n<\/tr>\n
    ADAMS<\/td>\n20<\/td>\n1100<\/td>\n4<\/td>\n<\/tr>\n
    SMITH<\/td>\n20<\/td>\n800<\/td>\n5<\/td>\n<\/tr>\n
    BLAKE<\/td>\n30<\/td>\n2850<\/td>\n1<\/td>\n<\/tr>\n
    ALLEN<\/td>\n30<\/td>\n1600<\/td>\n2<\/td>\n<\/tr>\n
    TURNER<\/td>\n30<\/td>\n1500<\/td>\n3<\/td>\n<\/tr>\n
    WARD<\/td>\n30<\/td>\n1250<\/td>\n4<\/td>\n<\/tr>\n
    MARTIN<\/td>\n30<\/td>\n1250<\/td>\n5<\/td>\n<\/tr>\n
    JAMES<\/td>\n30<\/td>\n950<\/td>\n6<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Secondly, if you’ve ever got weird results using rownum <\/em>in a query that also has an order by statement, you’ll know that rownum is determined before<\/em> the order is applied to the resultset. However, using row_number along with the analytic order by clause, you can ensure that your results respect your imposed order.<\/p>\n
    SELECT ename, sal, rownum,\r\n  ROW_NUMBER() OVER (ORDER BY sal) AS \"Row_number\"\r\nFROM emp\r\nORDER BY sal;\r\n<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    ENAME<\/strong><\/td>\nSAL<\/strong><\/td>\nROWNUM<\/strong><\/td>\nRow_number<\/strong><\/td>\n<\/tr>\n
    SMITH<\/td>\n800<\/td>\n7<\/td>\n1<\/td>\n<\/tr>\n
    JAMES<\/td>\n950<\/td>\n13<\/td>\n2<\/td>\n<\/tr>\n
    ADAMS<\/td>\n1100<\/td>\n12<\/td>\n3<\/td>\n<\/tr>\n
    MARTIN<\/td>\n1250<\/td>\n10<\/td>\n4<\/td>\n<\/tr>\n
    WARD<\/td>\n1250<\/td>\n9<\/td>\n5<\/td>\n<\/tr>\n
    MILLER<\/td>\n1300<\/td>\n14<\/td>\n6<\/td>\n<\/tr>\n
    TURNER<\/td>\n1500<\/td>\n11<\/td>\n7<\/td>\n<\/tr>\n
    ALLEN<\/td>\n1600<\/td>\n8<\/td>\n8<\/td>\n<\/tr>\n
    CLARK<\/td>\n2450<\/td>\n3<\/td>\n9<\/td>\n<\/tr>\n
    BLAKE<\/td>\n2850<\/td>\n2<\/td>\n10<\/td>\n<\/tr>\n
    JONES<\/td>\n2975<\/td>\n4<\/td>\n11<\/td>\n<\/tr>\n
    FORD<\/td>\n3000<\/td>\n6<\/td>\n12<\/td>\n<\/tr>\n
    SCOTT<\/td>\n3000<\/td>\n5<\/td>\n13<\/td>\n<\/tr>\n
    KING<\/td>\n5000<\/td>\n1<\/td>\n14<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     <\/p>\n
    Conclusion<\/h5>\n
    There are, as I said, dozens of analytic functions and I’ve been pleased to watch Oracle add to their number with each major release.\u00a0 These articles were never going to fully dissect every possible function, but I hope that they’ve demonstrated the strengths of the functionality and its amazing flexibility.<\/p>\n","protected":false},"excerpt":{"rendered":"
    In the first part of this series I introduced you to the analytic functions family, outlined its close relationship to aggregate functions, and illustrated my points with a few examples. I demonstrated how, by clever use of the analytic function clauses \u2013 partition by, order by, and the windowing clause \u2013 you could tune your functions to wring even mor…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48352,4459,4150],"coauthors":[],"class_list":["post-73148","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-analytic-functions","tag-oracle","tag-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73148","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73148"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73148\/revisions"}],"predecessor-version":[{"id":91644,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73148\/revisions\/91644"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73148"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}