Comparative Window Functions: Proposed Extensions to Analytic Functions

I promise that this will be the last post on analytic functions this month; but I came across this proposal to extend analytic functions with a comparative window function on Tom Kyte’s blog yesterday (also make sure to review the actual paper). Basically this new type of analytic function would allow you to compare the current row in a window to any other row in the window. I recently had a requirement where this would have come in handy. Imagine a dataset made up of product category, product id, product referential id, e.g.:

Computers,1,2
Computers,1,3
Computers,1,4
Computers,4,9

The requirement was to find those products whose reference products were also in the dataset. In the example above this would have been the record Computers,1,4. Something that can only be easily solved with a self-join. With this new proposal you could write this as

SELECT * FROM (
	SELECT
		prod_cat.
		prod_id.
		prod_ref_id,
		MAX(CASE WHEN prod_ref_id = INDEX(prod_id,anchor_row) THEN 1 ELSE 0) OVER (PARTITION BY prod_cat ROWS BETWEEN UNBOUNDED PRECEDING UNBOUNDED FOLLOWING) ref_exists_ind
	FROM
		prod_ref
)
WHERE ref_exists_ind = 1

Pretty cool…

SQL quiz revisited: ROW_NUMBER, LAST_VALUE, FIRST_VALUE and the SQL knights value problem.

Thank you all for your entries to the recent quiz. The guys from Packt should be shortly sending out the e-books to the winners.

Today I will walk you through some of the possible solutions and also briefly touch upon performance of these.

Last week’s contest was based on a classic SQL problem. Anthony Molinaro, the author of the SQL Cookbook has named this the Knight Values problem because it is analogous to a knight’s path in the game of chess: “You determine the result the way a knight determines a new location: by jumping to a row then turning and jumping to a different column”. In our example we first have to find the highest amount sold for a customer and from there then get the corresponding time_id. Another complicating factor is that we want the query to be deterministic, i.e. we just want to get back one record per customer. So what happens if there is a tie between the amount_sold, e.g. the same amount_sold exists on 5/11/2009 and 7/11/2009. If we come across such a tie we would like to select the amount_sold with the highest time_id.

Note: You can make this query only deterministic if there is a unique key on the table. Otherwise you may end up with a tie. There is a way around this though by randomly selecting one of the values or performing a SELECT DISTINCT. I will give two examples further down.

Of course you can do this in classic SQL using a subquery:

SELECT
   cust_id,
   amount_sold,
   MAX(time_id)
FROM (
   SELECT
      a.cust_id,
      a.time_id,
      a.amount_sold
   FROM
      customer a JOIN (
         SELECT
            cust_id,
            --amount_sold,
            MAX(amount_sold) amount_sold
         FROM
            customer
         GROUP BY
            cust_id
      ) b ON (a.cust_id = b.cust_id AND
              a.amount_sold = b.amount_sold)
)
GROUP BY
   cust_id,
   amount_sold

A query such as the following would not be deterministic as it does not resolve the potential tie outlined above.

SELECT
   *
FROM
   customer
WHERE
   (cust_id, amount_sold) in (
      SELECT
         cust_id,
         max(amount_sold)
      FROM
         customer
      GROUP BY
         cust_id
);

You can clearly see this if you insert the following record into our customer table

INSERT INTO customer VALUES (2,TO_DATE('05-FEB-2001','DD-MON-YYYY'),1545.78);

An alternative to the above is the use of analytic functions. You can either use ROW_NUMBER(),LAST_VALUE/FIRST_VALUE or KEEP FIRST/LAST with DENSE_RANK.

Let’s first have a look at LAST_VALUE.

SELECT cust_id, time_id, MAX(amount_sold) FROM (
   SELECT
      cust_id,
      time_id,
      LAST_VALUE(time_id) OVER (PARTITION BY cust_id ORDER BY amount_sold, time_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) time_max_amount,
      amount_sold
   FROM
      customer
)
WHERE
   time_max_amount = time_id
GROUP BY
   cust_id, time_id

So what are we doing here? We first break up the customer table by cust_id and order the resultset by amount_sold. We then grab the last value in this ordered resultset, which will give us the time_id for the maximum amount_sold for each customer. If you want to avoid a tie between the same amount_sold to a customer on two different days you need to include a unique value in the ORDER BY clause. In our case we are including the time_id to avoid a tie. If you are using LAST_VALUE you need to include the windowing clause ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. As per documentation: “If you omit the windowing_clause of the analytic_clause, it defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This default sometimes returns an unexpected value, because the last value in the window is at the bottom of the window, which is not fixed. It keeps changing as the current row changes. For expected results, specify the windowing_clause as RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Alternatively, you can specify the windowing_clause as RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.”

We could of course use the FIRST_VALUE function where we can use the default windowing behaviour to our advantage and omit the clause altogether. Thus making it easier to read. We have to slightly change our query though:

SELECT cust_id, time_id, MAX(amount_sold) FROM (
   SELECT
      cust_id,
      time_id,
      FIRST_VALUE(time_id) OVER (PARTITION BY cust_id ORDER BY amount_sold DESC, time_id DESC) time_max_amount,
      amount_sold
   FROM
      customer
)
WHERE
   time_max_amount = time_id
GROUP BY
   cust_id, time_id

One reader sent in a query to avoid the subquery altogether. Well, in Teradata you could have done this using analytic functions in the predicate clause.

SELECT DISTINCT
   cust_id,
   FIRST_VALUE(amount_sold) OVER (PARTITION BY cust_id ORDER BY amount_sold DESC, time_id) amount_sold,
   FIRST_VALUE(time_id) OVER (PARTITION BY cust_id ORDER BY amount_sold DESC, time_id) time_id
FROM
   customer

Another way of solving this with analytic functions is to use KEEP FIRST/LAST with dense_rank

SELECT cust_id, time_id, MAX(amount_sold) FROM (
   SELECT
      cust_id,
      time_id,
      MAX(time_id) KEEP(DENSE_RANK LAST ORDER BY amount_sold)  OVER ( PARTITION BY cust_id) time_max_amount,
      amount_sold
   FROM
      customer
)
WHERE
   time_max_amount = time_id
GROUP BY
   cust_id, time_id

If you don’t have a unique key in your dataset you can make the result deterministic by randomly selecting one of the tied records. This is achieved via row_number(). The solution below is my favourite as it is the most readable of the lot and always deterministic.

SELECT
   cust_id,
   time_id
FROM (
   SELECT
      cust_id,
      time_id,
      row_number() OVER (PARTITION BY cust_id ORDER BY amount_sold DESC, time_id DESC) rn FROM customer
)
WHERE
   rn = 1

A couple of words on performance.

The pure SQL approach will perform a full scan of the customer table twice. This means that it will generate twice the amount of logical I/O. However, the analytic functions will use up a lot more memory or even worse temp disk space for sorting based on the PARTITION and the ORDER BY clause. By creating an index on the columns used in the partition clause and the order by clause (such as the one below) you can generate a WINDOW NO SORT explain plain. As a result the query will have to do no sorting at all and should generally speaking perform better than the pure SQL. However, like everything in life it all comes down to your specific situation.

CREATE INDEX idx ON customer
(cust_id,AMOUNT_SOLD DESC,time_id desc)

If you want to read up on analytic functions I recommend the following books:
SQL Cookbook. This is my number one.
The Art of SQL.
SQL Hacks
SQL Tuning
Advanced SQL Functions in Oracle 10g. This one has more examples on analytic functions in Oracle.

SQL Quiz: Win two e-books (Essbase & BPEL)

We’ll do something completely different today and have a quiz. And you can even win something. Thanks to the guys at Packt Publishing I have two e-books to giveaway.

Oracle Essbase 9 Implementation Guide

and

Business Process Execution Language for Web Services

When you visit their website make sure to have a look at their free section. They are always looking for bloggers to review books.

In medias res.

Here comes the question:

In retail sales analytics, business folks often need to know on which day a customer bought the item with the highest amount sold. How can we achieve this with pure SQL on the Oracle database? In Oracle there are at least three different ways to answer this question. Two of the solutions use analytic functions. The third one uses a self-join. In the resultset I would like to see this on a customer by customer basis. For our sample data below I would like to get the date on which the amount_sold was largest together with the cust_id. This should return two records on for cust_id 2 and one for cust_id 4.

And here comes the data.

The first two readers who post me two correct answers will win one of the books. Please let me know which book you prefer and I’ll try to accomodate this.

Tip: Two steps will get you to the right answer. First you need to find the highest amount sold for a customer and from there, get the date on which this transaction occured.

TIME dimension script Oracle

SELECT
   n AS time_id,
   TO_CHAR(to_date(n,'SSSSS'),'HH24') AS hour,
   TO_CHAR(to_date(n,'SSSSS'),'MI') AS minute,
   TO_CHAR(to_date(n,'SSSSS'),'SS') AS second
FROM (
   SELECT
      level-1 n
   FROM
      DUAL
   CONNECT BY LEVEL <= 86400
)

Let’s create a date dimension with pure Oracle SQL

The script below will create a date dimension in just one SQL statement. We don’t use performance-killer nonsense such as cursors, functions etc.

We just pick a start date and the number of days we want to create and with a bit of magic of the Connect By clause and the NUMTODSINTERVAL function we do the whole thing in 14 lines of code.

SQL> CREATE TABLE d_date AS
  2  SELECT
  3     n AS Date_ID,
  4     TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date,
  5     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days,
  6     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,
  7     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num,
  8     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,
  9     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS Year
 10  FROM (
 11  select level n
 12  from dual
 13  connect by level <= 2000
 14  );

Table created.

Pretty much self-explanatory. The above will create a date dimension starting at 01/01/2008 and ending at 01/01/2008+2000 days = 22/06/2013.

If you want to read up on NUMTODSINTERVAL have a look at the documentation. Intervals are also very well explained in Tom Kyte's Expert Oracle Database Architecture. It is worth buying just for this chapter.

CSV to Rows

I recently needed to convert a comma separated list of values in a table column to a column where each value in the list gets its own row in a table.

So first of all let’s create such a table. I have limited the number of comma separated values that will be created to 12.

View Listing 1

To convert our csv list to rows we will create an in-memory helper table that for the maximum number of comma separated values has a corresponding number of rows. We can then use this helper table to join to our main table and then parse the values.

The SQL below will create our helper table:

View Listing 2

Results for the helper table will look like:

Now it is simply a matter of joining our main table to this helper table and parsing the results:

View Listing 3

In order to improve performance we could create a function based index for our join column.

There are other solutions that will do the same thing and requires less code, but will not perform as well. So if you have a small set of rows to convert you can use this solution: How do I split comma delimited data in a SQL Statement