Oracle Analytic SQL Example: using windowing_clause
March 1, 2012 Leave a comment
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