Spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring

View All Scripts Login to Run Script

Script Name Spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring
Description The challenge in this sample 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. The challenges I will discuss 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. See for the original blog article on which this sample is based: https://technology.amis.nl/2013/02/28/oracle-sql-spotting-combinations-that-occur-and-those-that-do-not-demonstrating-analytical-functions-outer-join-and-subquery-factoring/
Area SQL General / SQL Query
Contributor Lucas Jellema
Created Monday October 19, 2015

Statement 1

create table records
( seq  number(5)
, payload varchar2(200)
)

Table created.

Statement 2

insert into records values (1, 'red')

1 row(s) inserted.

Statement 3

insert into records values (2, 'blue')

1 row(s) inserted.

Statement 4

insert into records values (3, 'yellow')

1 row(s) inserted.

Statement 5

insert into records values (4, 'red')

1 row(s) inserted.

Statement 6

insert into records values (5, 'white')

1 row(s) inserted.

Statement 7

insert into records values (6, 'blue')

1 row(s) inserted.

Statement 8

insert into records values (7, 'red')

1 row(s) inserted.

Statement 9

insert into records values (8, 'yellow')

1 row(s) inserted.

Statement 10

insert into records values (9, 'blue')

1 row(s) inserted.

Statement 11

insert into records values (29, 'red')

1 row(s) inserted.

Statement 12

insert into records values (11, 'white')

1 row(s) inserted.

Statement 13

insert into records values (12, 'red')

1 row(s) inserted.

Statement 14

insert into records values (13, 'blue')

1 row(s) inserted.

Statement 15

insert into records values (14, 'blue')

1 row(s) inserted.

Statement 16

insert into records values (15, 'green')

1 row(s) inserted.

Statement 17

insert into records values (16, 'blue')

1 row(s) inserted.

Statement 18

insert into records values (17, 'yellow')

1 row(s) inserted.

Statement 19

insert into records values (18, 'red')

1 row(s) inserted.

Statement 20

insert into records values (19, 'yellow')

1 row(s) inserted.

Statement 21

insert into records values (20, 'red')

1 row(s) inserted.

Statement 22

insert into records values (10, 'blue')

1 row(s) inserted.

Statement 23

insert into records values (119, 'white')

1 row(s) inserted.

Statement 24

insert into records values (120, 'blue')

1 row(s) inserted.

Statement 25

insert into records values (121, 'red')

1 row(s) inserted.

Statement 26

First, let’s look at the sequences of three colors that have been observed and recorded. In order to construct the sequences, we need to combine each record with the subsequent two records. And creating a row result using other rows in the result set is almost the definition of Analytical Functions. So that is what we will use. In fact, I will use the LEAD function to take for each record the next two records into account for deriving the sequence. Note that records that do not have at least two subsequent records will not produce useful sequence results so they will be excluded. The query becomes:

The observed color sequences

with sequences as
( select seq
  ,      payload
         ||'|'||lead(payload)   over (order by seq)
		 ||'|'||lead(payload,2) over (order by seq) color_sequence
  ,      row_number()   over (order by seq desc) rows_to_come		 
  from   records
)
select distinct color_sequence
from   sequences
where  rows_to_come > 2

 
COLOR_SEQUENCE
red|yellow|red
red|blue|yellow
blue|blue|green
blue|white|red
yellow|red|white
red|red|white
red|yellow|blue
red|white|blue
blue|green|blue
blue|blue|white
yellow|blue|blue
yellow|red|yellow
blue|yellow|red
green|blue|yellow
blue|red|yellow
red|blue|blue
white|red|blue
white|blue|red
yellow|red|red

19 rows selected.

COLOR_SEQUENCE
red\|yellow\|red
red\|blue\|yellow
blue\|blue\|green
blue\|white\|red
yellow\|red\|white
red\|red\|white
red\|yellow\|blue
red\|white\|blue
blue\|green\|blue
blue\|blue\|white
yellow\|blue\|blue
yellow\|red\|yellow
blue\|yellow\|red
green\|blue\|yellow
blue\|red\|yellow
red\|blue\|blue
white\|red\|blue
white\|blue\|red
yellow\|red\|red

Statement 27

find out how often each color_sequence occurs. We get the query for addressing this challenge easily by adding the count(seq) and the group by color_sequence:

Count occurrences per color sequence

with sequences as
( select seq
  ,      payload
         ||'|'||lead(payload)   over (order by seq)
		 ||'|'||lead(payload,2) over (order by seq) color_sequence
  ,      row_number()   over (order by seq desc) rows_to_come		 
  from   records
)
select color_sequence
,      count(seq) occurrence_count
from   sequences
where  rows_to_come > 2
group
by     color_sequence
order
by     occurrence_count desc

 
COLOR_SEQUENCE OCCURRENCE_COUNT
white|blue|red 2
blue|yellow|red 2
red|white|blue 2
blue|white|red 1
yellow|red|white 1
red|red|white 1
red|yellow|blue 1
blue|green|blue 1
blue|blue|white 1
yellow|blue|blue 1
yellow|red|yellow 1
green|blue|yellow 1
blue|red|yellow 1
red|blue|blue 1
white|red|blue 1
blue|blue|green 1
yellow|red|red 1
red|yellow|red 1
red|blue|yellow 1

19 rows selected.

COLOR_SEQUENCE	OCCURRENCE_COUNT
white\|blue\|red	2
blue\|yellow\|red	2
red\|white\|blue	2
blue\|white\|red	1
yellow\|red\|white	1
red\|red\|white	1
red\|yellow\|blue	1
blue\|green\|blue	1
blue\|blue\|white	1
yellow\|blue\|blue	1
yellow\|red\|yellow	1
green\|blue\|yellow	1
blue\|red\|yellow	1
red\|blue\|blue	1
white\|red\|blue	1
blue\|blue\|green	1
yellow\|red\|red	1
red\|yellow\|red	1
red\|blue\|yellow	1

Statement 28

To only get the top 3 we can use – until 12c is upon us – the inline view construction.

Find top 3 occurring sequences (pre 12c approach)

with sequences as
( select seq
  ,      payload
         ||'|'||lead(payload)   over (order by seq)
		 ||'|'||lead(payload,2) over (order by seq) color_sequence
  ,      row_number()   over (order by seq desc) rows_to_come		 
  from   records
)
select *
from   ( select color_sequence
         ,      count(seq) occurrence_count
         from   sequences
         where  rows_to_come > 2
         group
         by     color_sequence
         order
         by     occurrence_count desc
	   )
where  rownum < 4

 
COLOR_SEQUENCE OCCURRENCE_COUNT
red|white|blue 2
white|blue|red 2
blue|yellow|red 2

3 rows selected.

COLOR_SEQUENCE	OCCURRENCE_COUNT
red\|white\|blue	2
white\|blue\|red	2
blue\|yellow\|red	2

Statement 29

All the possible combinations of three colors using the different colors that have been observed is an easy enough challenge using the cross join (to get the cartesian product)

All possible combinations of three colors

with colors as
(select distinct payload color
 from   records
)
select c1.color||'|'||c2.color||'|'||c3.color
from   colors c1
       cross join
       colors c2
       cross join
       colors c3

 
C1.COLOR||'|'||C2.COLOR||'|'||C3.COLOR
green|green|green
green|green|red
green|green|blue
green|green|white
green|green|yellow
green|red|green
green|red|red
green|red|blue
green|red|white
green|red|yellow
green|blue|green
green|blue|red
green|blue|blue
green|blue|white
green|blue|yellow
green|white|green
green|white|red
green|white|blue
green|white|white
green|white|yellow
green|yellow|green
green|yellow|red
green|yellow|blue
green|yellow|white
green|yellow|yellow
red|green|green
red|green|red
red|green|blue
red|green|white
red|green|yellow
red|red|green
red|red|red
red|red|blue
red|red|white
red|red|yellow
red|blue|green
red|blue|red
red|blue|blue
red|blue|white
red|blue|yellow
red|white|green
red|white|red
red|white|blue
red|white|white
red|white|yellow
red|yellow|green
red|yellow|red
red|yellow|blue
red|yellow|white
red|yellow|yellow
blue|green|green
blue|green|red
blue|green|blue
blue|green|white
blue|green|yellow
blue|red|green
blue|red|red
blue|red|blue
blue|red|white
blue|red|yellow
blue|blue|green
blue|blue|red
blue|blue|blue
blue|blue|white
blue|blue|yellow
blue|white|green
blue|white|red
blue|white|blue
blue|white|white
blue|white|yellow
blue|yellow|green
blue|yellow|red
blue|yellow|blue
blue|yellow|white
blue|yellow|yellow
white|green|green
white|green|red
white|green|blue
white|green|white
white|green|yellow
white|red|green
white|red|red
white|red|blue
white|red|white
white|red|yellow
white|blue|green
white|blue|red
white|blue|blue
white|blue|white
white|blue|yellow
white|white|green
white|white|red
white|white|blue
white|white|white
white|white|yellow
white|yellow|green
white|yellow|red
white|yellow|blue
white|yellow|white
white|yellow|yellow
yellow|green|green
yellow|green|red
yellow|green|blue
yellow|green|white
yellow|green|yellow
yellow|red|green
yellow|red|red
yellow|red|blue
yellow|red|white
yellow|red|yellow
yellow|blue|green
yellow|blue|red
yellow|blue|blue
yellow|blue|white
yellow|blue|yellow
yellow|white|green
yellow|white|red
yellow|white|blue
yellow|white|white
yellow|white|yellow
yellow|yellow|green
yellow|yellow|red
yellow|yellow|blue
yellow|yellow|white
yellow|yellow|yellow

125 rows selected.

Statement 30
The final challenge is little bit harder. Here are looking for all the color combinations that could have been – as is demonstrated by the previous query producing 125 records – but were never actually witnessed. So any color combination that is possible but that was never recorded should be produced by this query. It is a query to look for the things that do not exist in our data. And that call for the anti-join pattern. It goes like this: The query reuses query from the previous step that produces all possible combinations. Next, the subquery sequences is the one used at the beginning of this article. Only when we look at the main query see we something new. The possible color combinations are left outer joined with the color sequences that were recorded. The left outer join means that we will at least keep all color combinations and try to join them with the recorded sequences whenever possible. Any combination that did not actually occur – or at least was not recorded – will not be joined with a real sequence record but will instead be outer joined. Well, these combinations are the ones we are looking for: the color combinations that could not be joined for real because they have not been recorded for real. All we need to do to complete the solution is filter away all the color combinations that were joined, leaving the ones that were outer joined. Some of the 106 results produced:
Finding possible sequences that never actually occurred
```
with colors as
(select distinct payload color
 from   records
)
,   combinations as
( select c1.color||'|'||c2.color||'|'||c3.color combination
  from   colors c1
         cross join
         colors c2
         cross join
         colors c3
)
, sequences as
( select seq
  ,      payload
         ||'|'||lead(payload) over (order by seq)
         ||'|'||lead(payload,2) over (order by seq) color_sequence
  ,      row_number()  over (order by seq desc)  rows_to_come
  from   records
)
select c.combination
from   combinations c
       left outer join
       sequences s
       on (c.combination = s.color_sequence)
where  s.rowid is null
```

C1.COLOR\|\|'\|'\|\|C2.COLOR\|\|'\|'\|\|C3.COLOR
green\|green\|green
green\|green\|red
green\|green\|blue
green\|green\|white
green\|green\|yellow
green\|red\|green
green\|red\|red
green\|red\|blue
green\|red\|white
green\|red\|yellow
green\|blue\|green
green\|blue\|red
green\|blue\|blue
green\|blue\|white
green\|blue\|yellow
green\|white\|green
green\|white\|red
green\|white\|blue
green\|white\|white
green\|white\|yellow
green\|yellow\|green
green\|yellow\|red
green\|yellow\|blue
green\|yellow\|white
green\|yellow\|yellow
red\|green\|green
red\|green\|red
red\|green\|blue
red\|green\|white
red\|green\|yellow
red\|red\|green
red\|red\|red
red\|red\|blue
red\|red\|white
red\|red\|yellow
red\|blue\|green
red\|blue\|red
red\|blue\|blue
red\|blue\|white
red\|blue\|yellow
red\|white\|green
red\|white\|red
red\|white\|blue
red\|white\|white
red\|white\|yellow
red\|yellow\|green
red\|yellow\|red
red\|yellow\|blue
red\|yellow\|white
red\|yellow\|yellow
blue\|green\|green
blue\|green\|red
blue\|green\|blue
blue\|green\|white
blue\|green\|yellow
blue\|red\|green
blue\|red\|red
blue\|red\|blue
blue\|red\|white
blue\|red\|yellow
blue\|blue\|green
blue\|blue\|red
blue\|blue\|blue
blue\|blue\|white
blue\|blue\|yellow
blue\|white\|green
blue\|white\|red
blue\|white\|blue
blue\|white\|white
blue\|white\|yellow
blue\|yellow\|green
blue\|yellow\|red
blue\|yellow\|blue
blue\|yellow\|white
blue\|yellow\|yellow
white\|green\|green
white\|green\|red
white\|green\|blue
white\|green\|white
white\|green\|yellow
white\|red\|green
white\|red\|red
white\|red\|blue
white\|red\|white
white\|red\|yellow
white\|blue\|green
white\|blue\|red
white\|blue\|blue
white\|blue\|white
white\|blue\|yellow
white\|white\|green
white\|white\|red
white\|white\|blue
white\|white\|white
white\|white\|yellow
white\|yellow\|green
white\|yellow\|red
white\|yellow\|blue
white\|yellow\|white
white\|yellow\|yellow
yellow\|green\|green
yellow\|green\|red
yellow\|green\|blue
yellow\|green\|white
yellow\|green\|yellow
yellow\|red\|green
yellow\|red\|red
yellow\|red\|blue
yellow\|red\|white
yellow\|red\|yellow
yellow\|blue\|green
yellow\|blue\|red
yellow\|blue\|blue
yellow\|blue\|white
yellow\|blue\|yellow
yellow\|white\|green
yellow\|white\|red
yellow\|white\|blue
yellow\|white\|white
yellow\|white\|yellow
yellow\|yellow\|green
yellow\|yellow\|red
yellow\|yellow\|blue
yellow\|yellow\|white
yellow\|yellow\|yellow

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database