Task 12 (painting)

–Under assumption that there are holes among IDs (q_id) of squares, find the minimal and maximal "free" IDs in a range

–between available maximal and minimal IDs.

–If holes are not present, deduce NULL.

–Example: For the sequence of square IDs 1,2,5,7, the result must be 3 and 6

 

select q_min, q_max from

(

select MIN(ID) q_min from

(

select Q_ID + 1 ID from utQ

WHERE

Q_ID <= (SELECT MAX(Q_ID) from utQ )

except

select

Q_ID from utQ) z) z

,

(select MAX(ID) q_max from

(

select Q_ID 1 ID from utQ

WHERE

Q_ID <= (SELECT MAX(Q_ID) from utQ ) AND Q_ID > (SELECT MIN(Q_ID) from utQ )

except

select

Q_ID from utQ) y) y

 
Forum’s solution:
 
select Min(x), Max(x) from
  (select q_id-1 from utQ where q_id > (select Min(q_id) from utQ)
    union all
   select q_id+1 from utQ where q_id < (select Max(q_id) from utQ)
    except
   select q_id   from utQ) Q(x)

cost 0.02839327044785
operations 19
то же, но через left join:

select Min(x), Max(x) from 
  (select q_id-1 from utQ where q_id > (select Min(q_id) from utQ) 
    union all
   select q_id+1 from utQ where q_id < (select Max(q_id) from utQ)) Q(x)
  left join utQ on x=q_id where q_id is null

cost	0.024536108598113
operations	17

то же, но через not exists:

select Min(x), Max(x) from 
  (select q_id-1 from utQ where q_id > (select Min(q_id) from utQ) 
    union all
   select q_id+1 from utQ where q_id < (select Max(q_id) from utQ)) Q(x)
where not exists (select * from utQ where q_id=x)

cost	0.024512492120266
operations	16

Но еще лучше, конечно же вот так:

select Min(x), Max(x) from 
  (select q_id+a from utQ, (select -1 union all select 1) A(a)
    where q_id > (select Min(q_id) from utQ) 
      and q_id < (select Max(q_id) from utQ)) Q(x)
where not exists (select * from utQ where q_id=x)

cost	0.015696810558438
operations	14

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s