SQL Challenge/Puzzle: Как преобразовать художественные диапазоны ASCII в реляционные данные?
- Мотивация для этой задачи состояла в том, чтобы легко и точно смоделировать набор данных диапазонов IP-адресов, которые определенным образом связаны друг с другом.
Соревнование
Таблица содержит один столбец текстового типа. Текст содержит одну или несколько строк, где каждая строка содержит один или несколько разделов, созданных из тире. Цель состоит в том, чтобы написать запрос, который возвращает кортеж для каждого раздела с его начальной и конечной точкой.
Например
'
--- -- -
----
'
- Текст выше содержит 2 строки.
- Он содержит 4 раздела.
- 1-я строка содержит 3 раздела.
- 2-я строка содержит 1 раздел.
- Кортежи для 1-й строки (1,3),(5,6),(8,8).
- Кортеж для 2-й строки (2,5).
Требования
- Решение должно быть одним запросом SQL (подзапросы в порядке).
- Использование T-SQL, PL/SQL и т. Д. Не допускается.
Использование UDF (пользовательских функций) не допускается
При необходимости можно предположить, что в таблице есть только одна запись.
Пример данных
create table t (txt varchar (1000) not null);
insert into t (txt) values
(
'
--- --- --- ---
---------- -
- - -- -- --- ---
----- ---- --- -- -
-------
'
);
Запрашиваемый результат
* Требуются только последние 2 столбца (section_start/end), остальные для целей отладки.
line_ind section_ind section_length section_start section_end
-------- ----------- -------------- ------------- -----------
1 1 3 2 4
1 2 3 6 8
1 3 3 11 13
1 4 3 17 19
2 1 10 1 10
2 2 1 21 21
3 1 1 2 2
3 2 1 4 4
3 3 2 6 7
3 4 2 9 10
3 5 3 12 14
3 6 3 16 18
4 1 5 7 11
4 2 4 13 16
4 3 3 18 20
4 4 2 22 23
4 5 1 25 25
5 1 7 4 10
3 ответа
Teradata
with l
as
(
select line_ind
,line
from table
(
regexp_split_to_table (-1,t.txt,'\r','')
returns (minus_one int,line_ind int,line varchar(1000))
)
as l
)
select l.line_ind
,s.section_ind
,regexp_instr (l.line,'\S+',1,s.section_ind,0) as section_start
,regexp_instr (l.line,'\S+',1,s.section_ind,1) - 1 as section_end
,char_length (s.section) as section_length
from table
(
regexp_split_to_table (l.line_ind,l.line,'\s+','')
returns (line_ind int,section_ind int,section varchar(1000))
)
as s
,l
where l.line_ind =
s.line_ind
order by l.line_ind
,s.section_ind
;
оракул
select regexp_instr (txt,'-+',1,level,0) - instr (txt,chr(10),regexp_instr (txt,'-+',1,level,0) - length (txt) - 1,1) as section_start
,regexp_instr (txt,'-+',1,level,1) - 1 - instr (txt,chr(10),regexp_instr (txt,'-+',1,level,0) - length (txt) - 1,1) as section_end
from t
connect by level <= regexp_count (txt,'-+')
;
Оракул
SELECT row_n AS line_ind
,dense_rank() over(PARTITION BY row_n ORDER BY s_beg) AS section_ind
,s_end - s_beg AS section_length
,s_beg - decode(row_n, 0, 0, instr(a,chr(10),1,row_n)) AS section_start
,s_end - decode(row_n, 0, 0, instr(a,chr(10),1,row_n)) -1 AS section_end
FROM (SELECT a
,s_beg
,DECODE(s_end, 0, length(a) + 1, s_end) AS s_end
,length(substr(a, 1, s_beg))
- length(REPLACE(substr(a, 1, s_beg), chr(10))) AS row_n
,lvl
FROM (SELECT txt as a
,DECODE(LEVEL, 1, 0, regexp_instr(txt , '\s|\n', 1, LEVEL - 1)) + 1 AS s_beg
,regexp_instr(txt , '\s|\n', 1, LEVEL) AS s_end
,LEVEL AS lvl
FROM t
CONNECT BY LEVEL <= length(txt ) - length(regexp_replace(txt , '\s|\n')) + 1)
)WHERE s_beg != s_end;