Archives of the TeradataForum

Message Posted: Mon, 16 Jun 2003 @ 20:02:50 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Consecutive sequence sql logic

From:		Dieter N�th

Burton, Bruce wrote:

I'm trying to find phone numbers that exist on a given account that have at least 5 consecutive phone numbers in a sequence. From the below example the 1st five phone numbers would meet the criteria and the distinct account would be selected for further processing. The 2nd account would not be selected because the phone number is not in sequence or does not have at least 5 consec. sequenced numbers:

acct         phone

121212       555-1212
121212       555-1213
121212       555-1214
121212       555-1215
121212       555-1216

921212       555-1111
921212       555-4444

Any ideas on how to convert this logic into SQL?

Calculating runs and sequences with traditional SQL techniques is rather inefficient, just look at advanced SQL book like Joe Celko's "SQL for Smarties".

But with OLAP functions it's quite fast. I modified a puzzle i use in my advanced SQL classes ;-)

create table p816_master as
(
select
   cast(calendar_date as int) / 10 as acct
   ,trim(calendar_date (format 'yyyymmdd')) as mobile
   ,1 as bus_ind
   ,'A' as bus_seg
from
   sys_calendar.calendar
sample 20000
) with data;

select
   acct
   ,count(*) as consecutive_numbers
   ,min(mobile) as "From"
   ,max(mobile) as "To"
from
  (
   select
     acct
     ,mobile
     ,diff
/** this is the trick:
     calculate groups of rows, so consecutive rows with a diff of 0
     will be part of the same datagroup **/
     ,sum(diff) over (partition by acct
                      order by mobile
                      rows unbounded preceding) as datagroup
   from
    (
     select
       acct
       ,mobile
/** moving difference - 1 between two numbers,
     so consecutive rows have a diff of 0 **/
       ,coalesce(mdiff(cast(mobile as int), 1, mobile)-1, 0) as diff
     from p816_master
     where bus_ind=1
     and bus_seg ='A'
     group by acct
    ) dt
  ) dt
group by acct, datagroup
having count(*) >=5
order by 1,2
;

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference