Posts tagged analytical function
One of the very nice new features in SQL in Oracle Database 12c is the MATCH_RECOGNIZE operator. MATCH_RECOGNIZE adds a facility for spotting patterns in records. This allows us to locate records that are part of some kind of pattern relative to other records. It does sound similar to what Analytic Functions – most notably LAG and LEAD can do – but it is different. LAG and LEAD allow you to calculate the result of a record based on other records in the result set – but you are very limited in the ways in which you can indicate which other records in the result set are to be referenced by LAG and LEAD. Typically, it will be a fixed number of records before or after the record itself.
MATCH_RECOGNIZE allows us to have the database find a pattern – a regular expression expressed in terms of row conditions – in a far more flexible, dynamic and almost fuzzy way.
In this article, I will use this functionality to find the most valuable player in a football team (US readers: I mean the sports that you may refer to as Soccer). My definition of the MVP is the player who is most frequently part of a period of uninterrupted ball possession ending with a goal. Whether the play scores the goal, More >
Out of the box usage of ADF DVT Scheduling Gantt Chart to report Database Query Results using stacked bar charts per time period0
Gantt Charts in ADF are interesting components to visualize data that is organized according to time. The Gantt Charts have a horizontal time axis. In rows along the vertical axis, resources or tasks are displayed. The cells in this time/resource matrix represent information about the resource or the task at some point in time or more specifically: in some time period.
In this recent blog-article, I explained how we can use the Schedule Gantt chart to present results per resource per period using something closely resembling horizontal bar charts. The key thought is that when we present data associated with a standard period, we can use the Gantt Chart’s capability to set the length of the bar to express the size of the value in a specific period. More specifically: we can use the end date property to manipulate the length of the bar. This article we will take this one step further and create a Stacked Bar Chart for each resource for each period. In this case, I will present the number of Employees hired per Department per Year and do so using Stacked Bar Charts with segments per Job. The result – driven directly from SQL query in a read only ViewObject – looks like this:
We can More >
Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring0
The challenge in this article is a simple one. I have a table with records (duh!) and each record describes the occurrence of a certain payload. In this example the payload will be a color. Every record also has a sequence number to indicate well, when it appeared relative to the other observations. A subset of the data would look like this:
The challenges I will discuss in this article are simple:
- which sequences of three color observations occur in the data set
- how often do these sequences of three occur (give me the top 3 sequences)
- given the colors that have been observed, which combinations of three can be created
- which possible combinations (or color sequences of three colors) have not been observed at all
Using Oracle SQL features such as Analytical Functions, Outer Join and Subquery Factoring – these questions become very easy to answer.
AMIS Masterclass Advanced SQL – scherp je SQL skills in een pittige en praktijkgerichte dag – maandag 10 december0
Of je nu ‘klassieke’ Oracle applicaties ontwikkelt, met APEX bezig bent, een SOA landschap inricht of een Java/JEE (bijvoorbeeld ADF) applicatie bouwt: het fundament van veel van wat je doet is de database. En de crux van optimaal gebruik van de database is en blijft SQL. Dit bepaalt performance en schaalbaarheid en ook ontwikkel-productiviteit en zelfs pure functionaliteit. SQL kan veel meer in de Oracle Database dan de meeste – ook heel ervaren ontwikkelaars – weten. Tussen Oracle7 en Oracle 11gR2 is er met eigenlijk iedere release enorm veel kracht bijgekomen in de handen van “SQL componisten”. En je doet jezelf tekort als je niet regelmatig je SQL vaardigheden aanscherpt tot de nieuwste technische mogelijkheden en vooral de praktijkbewezen evolutie.
Op maandag 10 december verzorgt AMIS – Oracle specialist in Nieuwegein, sinds 1991 – een Masterclass Advanced SQL. Waarin voor ervaren SQL ontwikkelaars een overzicht wordt gegeven van de evolutie van de database – tot en met 11g Release 2 en met hier en daar al een vooruitblik naar 12c.
Oracle 11gR2 – alternative for CONNECT_BY_ISLEAF function for Recursive Subquery Factoring (dedicated to Anton)
On our blog, we have been discussing the new hierarchical query functionality in Oracle Database 11g Release 2, using Recursive Suquery Factoring. Instead of using CONNECT BY and its close associates such as START WITH, PRIOR, LEVEL and more exotic comrades like SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT and NOCYCLE this release gave us a new, less proprietary and eventually probably more intuitive and functionally rich approach. We have also written how though we have no straightforward alternatives for LEVEL, SYS_CONNECT_BY_PATH and CONNECT_BY_ROOT – in the new recursive approach they are fantastically easy to emulate.
Until recently I have been quite happy with the new hierarchical querying and telling the world how I felt. Then an esteemed colleague – a far more experienced SQL programmer than I am – came up to me and remined me how the recursive sub query syntax at the present does not have a replacement for the CONNECT_BY_ISLEAF function – the SQL function that tells us whether a node produced in an hierarchical query has any children or is at the bottom of the chain – i.e. a leaf node. For leaf nodes (child-less), the function returns a value of one and for parent nodes the More >