Oracle Analytic SQL Example: using windowing_clause

I got a request from a developer recently. Here is it (simplified):

The query would look like:
select id, name, creation_date COUNT(*)
from table_name
group by id, name
having count(*)> 1
and (creation_date difference between rows is less than one hour)

I created a test table with some data as follows.

SQL> create table t (id number, name varchar2(10), creation_date date);

Table created.

--insert some data

SQL> select * from t order by CREATION_DATE;

        ID NAME       CREATION_DATE
---------- ---------- ----------------
         1 a          2012-03-01 01:00
         1 a          2012-03-01 01:30
         1 a          2012-03-01 01:40
         1 a          2012-03-01 02:10
         1 a          2012-03-01 04:00
         1 b          2012-03-01 06:00
         1 b          2012-03-01 06:30
         1 c          2012-03-01 08:00
         1 c          2012-03-01 09:30

9 rows selected.

Here is the SQL to get the result:

SQL> select * from (
 select id,name, creation_date,
  count(*) over 
  (partition by id,name
   order by creation_date
   range BETWEEN 1/24 PRECEDING and 1/24 FOLLOWING
   ) as cnt
 from t
 ) where cnt>1;

        ID NAME       CREATION_DATE           CNT
---------- ---------- ---------------- ----------
         1 a          2012-03-01 01:00          3
         1 a          2012-03-01 01:30          4
         1 a          2012-03-01 01:40          4
         1 a          2012-03-01 02:10          3
         1 b          2012-03-01 06:00          2
         1 b          2012-03-01 06:30          2

6 rows selected.

Reference Analytic functions windowing_clause at:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#i97640

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: