Re: An SQL Challenge...

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 23 Jun 2005 08:06:33 +0000 (UTC)
Message-ID: <d9dqi9$avt$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com>

<mike.jones_at_xenicom.com> wrote in message news:1119364666.315642.141220_at_g44g2000cwa.googlegroups.com...
>I was recently given a datacleanse task that I think makes an
> interesting SQL Puzzle. At the end are the setup statement you will
> need.
>
> I guess replies to this thread, so far I've been unable to do it purely
> in SQL I have had to drop (Briefly) into PL/SQL, but I'm sure there
> must be an SQL only solution.
>
> If it helps motivate people I've also challenged some C++ and Java
> developers to see if they can beat SQL / PL/SQL, come on now we can't
> let the side down!
>
> Mike.
>
> ----
>
> The challenge is to sequence the cells on a Site. The Sequence should
> start with the Cell that is closest to 0 degrees i.e. 10 is closer
> than 20, but 358 is closer than 10. Once the first Cell is sequenced
> the remaining cells should be sequenced clockwise (think of the degrees
> ploted on a circle) around the remaining records. E.g.
>
> Degree Seq No
> 358 1
> 10 2
> 180 3
> 181 4
>
> 359 1
> 354 2
> 355 3
>
> 0 1
> 180 2
> 350 3
>
> Where two records are equal distance say the set is 10, 180, 350) then
> either 10 or 350 can be considered "closest", but the sequencing rule
> above must still be used (Clockwise around the circle from that point)
>
> The below will create a table called TEST_DATA, This has 3 main columns
> ID, DEGREES and SEQUENCE_NO. For each ID there will be 3 records (But
> don't assume this - the solution should work with any number), with
> DEGREES between 0 and 359.
>
>
> drop table test_data
> /
>
> create table test_data unrecoverable tablespace data_large as
> select trunc(rownum / 3)id, lpad('*',500,'*') filler_1,
> lpad('@',500,'@') filler_2, lpad('#',500,'#') filler_3,
> trunc(dbms_random.value(0,360)) degrees, -1 sequence_no
> from
> ( select *
> from dual
> connect by level = level
> )
> where rownum <= 200000
> /
>
>
> create index test_data_idx1 on test_data (id) unrecoverable
> /
>
> begin
> loop
> --
> update test_data
> set degrees = degrees - decode(degrees,0, -1,1)
> where rowid in
> ( select min(rowid) min_row_id
> from test_data
> group by id, degrees
> having count(*) > 1
> );
> --
> exit when sql%rowcount = 0;
> end loop;
> end;
> /
>

The following seems to work.

It assume a degree of uniqueness that you haven't specified explicitly, and also "not null" on id and degrees. The closing 'not matched' clause exists because 9i demands it (10g doesn't) but it will never be called.

merge into test_data
using (

   select

           id,
           sequence_no,
           degrees,
           rank() over (
                   partition by id
                   order by
                   decode(first,1,0,degrees)
           )       new_seq
   from    (
           select
                   id,
                   sequence_no,
                   degrees,
                   rank() over (
                           partition by id
                           order by least(degrees, abs(360-degrees))
                   )       first
           from
                   test_data
           )
   )       ranked
on (
           test_data.id = ranked.id

    and test_data.degrees = ranked.degrees    )

when       matched then update
           set test_data.sequence_no = ranked.new_seq
when       not matched then
           insert (id, sequence_no, degrees)
           values(ranked.id, ranked.sequence_no, ranked.degrees)

/

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated June 22nd 2005

Received on Thu Jun 23 2005 - 03:06:33 CDT