Quizfrage

Bei Dominic Delmolino (http://www.oraclemusings.com/?p=57) gab's kürzlich eine interessante SQL-Aufgabe. Hier meine (nicht besonders hübsche) Lösung dazu:

SQL> r
select case when lag_col is null then min_col
when substr(min_col, 1, 1) <> substr(lag_col, 1, 1)
then substr(min_col, 1, 1)
when substr(min_col, 1, 1) = substr(lag_col, 1, 1)
and substr(min_col, 1, 2) <> substr(lag_col, 1, 2) then substr(min_col, 1, 2)
when substr(min_col, 1, 2) = substr(lag_col, 1, 2)
and substr(min_col, 1, 3) <> substr(lag_col, 1, 3) then substr(min_col, 1, 3)
when substr(min_col, 1, 3) = substr(lag_col, 1, 3)
and substr(min_col, 1, 4) <> substr(lag_col, 1, 4) then substr(min_col, 1, 4)
when substr(min_col, 1, 4) = substr(lag_col, 1, 4)
and substr(min_col, 1, 5) <> substr(lag_col, 1, 5) then substr(min_col, 1, 5)
when substr(min_col, 1, 5) = substr(lag_col, 1, 5)
and substr(min_col, 1, 6) <> substr(lag_col, 1, 6) then substr(min_col, 1, 6)
when substr(min_col, 1, 6) = substr(lag_col, 1, 6)
and substr(min_col, 1, 7) <> substr(lag_col, 1, 7) then substr(min_col, 1, 7)
when substr(min_col, 1, 7) = substr(lag_col, 1, 7)
and substr(min_col, 1, 8) <> substr(lag_col, 1, 8) then substr(min_col, 1, 8)
when substr(min_col, 1, 8) = substr(lag_col, 1, 8)
and substr(min_col, 1, 9) <> substr(lag_col, 1, 9) then substr(min_col, 1, 9)
when substr(min_col, 1, 9) = substr(lag_col, 1, 9)
and substr(min_col, 1, 10) <> substr(lag_col, 1, 10) then substr(min_col, 1, 10)
when substr(min_col, 1, 10) = substr(lag_col, 1, 10)
and substr(min_col, 1, 11) <> substr(lag_col, 1, 11) then substr(min_col, 1, 11)
when substr(min_col, 1, 11) = substr(lag_col, 1, 11)
and substr(min_col, 1, 12) <> substr(lag_col, 1, 12) then substr(min_col, 1, 12)
else null end
|| ' thru ' ||
case when lead_col is null then max_col
when substr(max_col, 1, 1) <> substr(lead_col, 1, 1)
then substr(max_col, 1, 1)
when substr(max_col, 1, 1) = substr(lead_col, 1, 1)
and substr(max_col, 1, 2) <> substr(lead_col, 1, 2) then substr(max_col, 1, 2)
when substr(max_col, 1, 2) = substr(lead_col, 1, 2)
and substr(max_col, 1, 3) <> substr(lead_col, 1, 3) then substr(max_col, 1, 3)
when substr(max_col, 1, 3) = substr(lead_col, 1, 3)
and substr(max_col, 1, 4) <> substr(lead_col, 1, 4) then substr(max_col, 1, 4)
when substr(max_col, 1, 4) = substr(lead_col, 1, 4)
and substr(max_col, 1, 5) <> substr(lead_col, 1, 5) then substr(max_col, 1, 5)
when substr(max_col, 1, 5) = substr(lead_col, 1, 5)
and substr(max_col, 1, 6) <> substr(lead_col, 1, 6) then substr(max_col, 1, 6)
when substr(max_col, 1, 6) = substr(lead_col, 1, 6)
and substr(max_col, 1, 7) <> substr(lead_col, 1, 7) then substr(max_col, 1, 7)
when substr(max_col, 1, 7) = substr(lead_col, 1, 7)
and substr(max_col, 1, 8) <> substr(lead_col, 1, 8) then substr(max_col, 1, 8)
when substr(max_col, 1, 8) = substr(lead_col, 1, 8)
and substr(max_col, 1, 9) <> substr(lead_col, 1, 9) then substr(max_col, 1, 9)
when substr(max_col, 1, 9) = substr(lead_col, 1, 9)
and substr(max_col, 1, 10) <> substr(lead_col, 1, 10) then substr(max_col, 1, 10)
when substr(max_col, 1, 10) = substr(lead_col, 1, 10)
and substr(max_col, 1, 11) <> substr(lead_col, 1, 11) then substr(max_col, 1, 11)
when substr(max_col, 1, 11) = substr(lead_col, 1, 11)
and substr(max_col, 1, 12) <> substr(lead_col, 1, 12) then substr(max_col, 1, 12)
else null end spine, min_col, max_col
from (select ntile_range,
min_col,
max_col,
lead( min_col) over (order by ntile_range) lead_col,
lag( max_col) over (order by ntile_range) lag_col
from (select ntile_range,
min(column_name) min_col,
max(column_name) max_col
from (select column_name,
ntile(15) over(order by column_name) ntile_range
from ac1
)
group by ntile_range order by ntile_range
)
) t

SPINE MIN_COL MAX_COL
------------------------- ------------------------------ ------------------------------
A thru BITMAP A BITMAP
BITMAPP thru C_ BITMAPPED C_OBJ#
C1 thru De C1 Default
D1 thru ERR_ D1 ERR_NUM
ERRO thru GENL ERRORS GENLINKS
GENO thru I_ GENOPTION I_AGREE
IN thru LOB_ INTRO LOB_COL_NAME
LOBI thru MV_QUA LOBINDEX MV_QUANTITY_SUM
MV_QUE thru Op MV_QUERY_GEN_MISMATCH Option
OS thru P_ OSHST P_REF_TIME
P1 thru R_ P1 R_CONSTRAINT_NAME
R1 thru SHORT_WAITS R1 SHORT_WAITS
SHORT_WAIT_ thru Si SHORT_WAIT_TIME_MAX Size
SU thru T_ SUMGROSSTURNOVER T_PER_EXEC
T1 thru ZERO_RESULTS T1OBJID ZERO_RESULTS

15 Zeilen ausgewählt.