Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle Analytic Enhancements

By Saurabh Gupta | on June 19, 2012 |
Oracle 11g Tutorials

Since the release of Oracle 8.1.6, analytic functions have emerged as a major extended  strength of SQL language. Analytic functions address the problems like moving averages, cumulative sums, ranking, percentile and many other problems which are crucial for OLAP applications. Such data operations are required for business applications which involve strategic decision making and predictive forecasts.

Topics

  • The LISTAGG function
  • Aggregate behavior of LISTAGG
  • Analytic behavior of LISTAGG
  • The NTH_VALUE function
  • Analytic behavior of NTH_VALUE function
  • Conclusion

Analytic functions work with ordered grouped data sets or ‘windows’ which are created by the analytic clause. Each window constitutes a defined set of rows which satisfy defined conditions relative to the current row. Analytic function logic is operative upon this data set under the window. Some of the key accomplishments of SQL analytic functions are drawn in the below figure.

Analytic operations are processed as the last one in an unsorted query (without ORDER  BY clause) execution process. Until Oracle 10g, the major and frequently used analytic functions are RANK, DENSE_RANK, LEAD, LAG, ROW_NUMBER, and RANGE. In addition to the analytic functions, several aggregate functions also reveal their analytic behavior in SELECT queries. Aggregate functions like MIN, MAX, and SUM can inherit analytic behavior when operated in a window.

In this article, we shall focus on the analytic enhancements in Oracle 11g.  Oracle 11g introduced two new functions inSQL namely, LISTAGG and NTH_VALUE.

The LISTAGG function

Oracle 11g Release 2 included a new function in the aggregate cum analytic
functions category. The built in function is known as LISTAGG which can be used to aggregate string with a delimiter. The LISTAGG function not only aggregates
the strings but also puts them in a specified order.

Before digging into the details of LISTAGG function, let us understand the
string aggregation. We shall see the string aggregation in a group and in a row.
The below figure shows the sample order data for each month has been aggregated
month wise.

Prior to release of Oracle 11g, there were several workaround solutions to
achieve string aggregations as listed below

• STRAGG (currently it exists in SYS schema in unusable state)
• WM_CONCAT (exists in WMSYS user)
• User defined stored function or an aggregate function
• Workarounds using ROW_NUMBER and SYS_CONNECT_BY_PATH
• COLLECT function using object type table

In comparison to the above solutions, LISTAGG function is efficient in terms of performance and usage convenience.

The LISTAGG function follows the below syntax

LISTAGG( [ ]) 
WITHIN GROUP (ORDER BY ) 
[OVER (PARTITION BY )]

Note that the LISTAGG has mandatory WITHIN GROUP (ORDER BY) clause. Oracle raises exception if any of the clause is missing while using LISTAGG in queries.

The aggregate behavior of LISTAGG can be extended by adding the windowing clause OVER (PARTITION BY).

Aggregate behavior of LISTAGG

The aggregate behavior of the LISTAGG function is quite straightforward and goes exactly as the purpose. The function simply aggregates the column values which fall under the group. Sorting of the aggregated string depends upon the ORDER BY specification. If no sorting criterion has to be applied, NULL can be specified. Let us check both the cases and observe the difference.

We shall demonstrate the illustration on the EMPLOYEES table. The SELECT query below aggregates the employee names which have the same job category.

SELECT job, LISTAGG( ename, ‘,’) WITHIN GROUP (ORDER BY NULL) names
FROM employees
GROUP BY job
/

Now let us check the difference by setting the order of the above output. We will sort the names in ascending order of their hire date.

SELECT job, LISTAGG( ename, ‘,’) WITHIN GROUP (ORDER BY hiredate) names
FROM employees
GROUP BY job
/

Thus, we see the effect of ORDER BY clause in the LISTAGG function. The aggregate behavior of the function can be easily and frequently employed to resolve string  aggregation problems.

Analytic behavior of LISTAGG

Till now, we examined the aggregate side of the LISTAGG function. The LISTAGG function shows analytic behavior when used with OVER() clause. Let us illustrate a small  example

The query below demonstrates the analytic behavior of LISTAGG function. The window is created by the JOB column. Note that the output of LISTAGG function is same for the complete window. This deduces the fact that analytic function output moves for each row of its window, which is not the case in aggregate functions.

SELECT job,
ename,
sal,
LISTAGG (ename, ‘,’) WITHIN GROUP (ORDER BY sal) 
OVER (PARTITION BY job) names
FROM employees
/

The NTH_VALUE function

Since Oracle 9i Release 2, we have been using the FIRST_VALUE and LAST_VALUE
analytic functions in SQL. While the FIRST_VALUE retrieves the first value of a column in a window which contains partitioned data in ordered way. Similarly, the LAST_VALUE
function gives the last value of the window. For random selection in a window, programmers used a workaround solution.

Oracle 11g R2 introduces a new analytic function with similar genre. The NTH_VALUE function can pick a random record from the window. Not only first or last, the function can pull out fifth, tenth or any specific value from the data set.

The NTH_VALUE function has the below syntax

NTH_VALUE (, ) 
[FROM FIRST | FROM LAST]
[RESPECT NULLS | IGNORE NULLS]
OVER (PARTITION BY  ORDER BY )

In the above syntax, [FROM FIRST | FROM LAST] is an optional clause which determines whether the operation has to start from first row or last row. By default, it is FROM FIRST.

[RESPECT NULLS| IGNORE NULLS] clause determines whether NULL values are to be included or excluded in the calculation. By default, it is RESPECT NULLS.

The OVER()clause creates the window for the analytic operation.

Analytic behavior of NTH_VALUE function

We shall illustrate the analytic applications of NTH_VALUE function in the below examples.

Very often we come across the scenarios where we need to retrieve the second highest salary drawer in each job category. Using sub queries and multiple analytic functions, it  used to be a cumbersome workaround solution. Let us observe how NTH_VALUE function simplifies the solution.

SELECT job, ename,sal,
NTH_VALUE (sal,2) 
OVER (PARTITION BY job ORDER BY sal DESC) SECOND
FROM employees
ORDER BY job, sal DESC
/

In the above output, note the second highest salary in each job category; Adams is the second highest paid clerk while Blake is the second highest paid manager.

Comparing NTH_VALUE against its predecessors FIRST_VALUE and LAST_VALUE, the
function can well derive its predecessors too. NTH_VALUE (column, 1) FROM FIRST is equivalent to FIRST_VALUE while NTH_VALUE (column, 1) FROM LAST is equivalent to LAST_VALUE.


Conclusion

The article describes the aggregate and analytic enhancements made in Oracle 11g. With  the new inductions, previously practiced workaround solutions would surely be relieved off. I hope my efforts to describe the usage and applications of the functions would be fair and considerable.

« « Cross-Cultural Diversity
Oracle XML Storage » »

Author Description

Avatar

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • Oracle 11g Virtual Columns

    May 30, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • Oracle 11g Invisible Index

    May 27, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle 11g Collections

    June 2, 2011 - 0 Comment
  • Compound Triggers in Oracle 11g

    May 9, 2011 - 0 Comment
  • Oracle 11g Result Cache

    April 28, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g Read Only Tables

    June 16, 2011 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle XML Storage

    June 19, 2012 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment
  • Oracle 11g Subprogram Overloading

    June 17, 2011 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle XML Storage
  • Oracle Partitioning Overview
  • PL/Scope and PL/SQL Hierarchical Profiler
  • Oracle VPD implementation
  • Oracle 11g SecureFiles

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT