Archives of the TeradataForum

Message Posted: Mon, 16 Jun 2003 @ 20:08:59 GMT

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

I think this does what you want:

select
 p1.acct, p1.phone, count(*)
from
 cust as p1
  inner join
 cust as p2
  on p2.phone = p1.phone+4 and
     p2.acct = p1.acct
  inner join
 cust as p3
  on p3.phone between p1.phone and p2.phone and
     p3.acct = p1.acct
group by
 p1.acct, p1.phone
having
 count(*) = 5
;

The p1/p2 join creates phone number pairs with the required span of 5. It also eliminates some unproductive values where there is no exact match on p1.phone+4. The valid p1/p2 spans are then joined to p3 so the number of rows within each span can be counted. The advantage of this approach is that it can be generalized to any span size.

The first version returns duplicate rows if more than one span exists, so I tweaked it a bit to eliminate them:

select distinct
 p1.acct
from
 cust as p1
  inner join
 cust as p2
  on p2.phone = p1.phone+4 and
     p2.acct = p1.acct
  inner join
 cust as p3
  on p3.phone between p1.phone and p2.phone and
     p3.acct = p1.acct
group by
 p1.acct, p1.phone
having
 count(*) = 5
;

The explain doesn't look too bad, though it redistributes the data because of the expressions in the join conditions. I ran it on a test file with 125K rows and it returned results in 15 seconds on a 10-node 5150. YMMV.

/dave hough

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