Блог

Главная - Вопросы по программированию - SQL в отдельную часть строки — Oracle SQL

SQL в отдельную часть строки — Oracle SQL

#sql #oracle

#sql #Oracle

Вопрос:

У меня есть таблица table1 со столбцом line , который имеет тип CLOB

Вот значения:

 seq    line
------------------------------
1       ISA*00*TEST
        ISA*00*TEST1
        GS*123GG*TEST*456:EHE
        ST*ERT*RFR*EDRR*EER
        GS*123GG*TEST*456:EHE
-------------------------------
2       ISA*01*TEST
        GS*124GG*TEST*456:EHE
        GS*125GG*TEST*456:EHE
        ST*ERQ*RFR*EDRR*EER
        ST*ERW*RFR*EDRR*EER
        ST*ERR*RFR*EDRR*EER

Я пытаюсь найти отдельную строку подстроки перед второй звездочкой.

Вывод был бы:

 distinct_line_value   count
ISA*00                2
GS*123GG              2
ST*ERT                1
ISA*01                1
GS*124GG              1
GS*125GG              1
ST*ERQ                1
ST*ERW                1
ST*ERR                1

Есть идеи, как я могу сделать это на основе distinct для первых 2 звезд?

Комментарии:

1. Принадлежат ли эти выборочные значения одной строке (что может быть так, учитывая, что это CLOB) или многим строкам?

2. Все это в 1 строке.

3. Просто для ясности — строк много, но в каждой строке несколько строк.

4. Я обновил вопрос, чтобы очистить его.

5. Спасибо; это делает вещи намного более понятными. Я опубликовал ответ, посмотрите, пожалуйста.

Ответ №1:

Вот один из вариантов:

Тестовый пример:

 SQL> select * from test;

       SEQ LINE
---------- --------------------------------------------------
         1 ISA*00*TEST
           ISA*00*TEST1
           GS*123GG*TEST*456:EHE
           ST*ERT*RFR*EDRR*EER
           GS*123GG*TEST

         2 ISA*01*TEST
           GS*124GG*TEST*456:EHE
           GS*125GG*TEST*456:EHE
           ST*ERQ*RFR*EDRR*EER
           ST*E

Запрос (смотрите Комментарии в коде; кроме того, здесь REGEXP_SUBSTR имеет решающее значение его 'm' параметр match, который обрабатывает входную строку как несколько строк):

 SQL> with
  2  -- split CLOB values to rows
  3  inter as
  4    (select seq,
  5       regexp_substr(line, '^.*$', 1, column_value, 'm') res
  6     from test,
  7       table(cast(multiset(select level from dual
  8                           connect by level <= regexp_count(line, chr(10))   1
  9                          ) as sys.odcinumberlist))
 10    ),
 11  -- convert CLOB to VARCHAR2 (so that SUBSTR works)
 12  inter2 as
 13    (select to_char(res) res From inter)
 14  -- the final result
 15  select substr(res, 1, instr(res, '*', 1, 2)) val, count(*)
 16  from inter2
 17  group by substr(res, 1, instr(res, '*', 1, 2))
 18  order by 1;

VAL                                                  COUNT(*)
-------------------------------------------------- ----------
GS*123GG*                                                   2
GS*124GG*                                                   1
GS*125GG*                                                   1
ISA*00*                                                     2
ISA*01*                                                     1
ST*ERQ*                                                     1
ST*ERR*                                                     1
ST*ERT*                                                     1
ST*ERW*                                                     1

9 rows selected.

SQL>

Комментарии:

1. Обработка моих 30 тысяч записей занимает действительно много времени. Есть ли способ ускорить это?

2. Значения, которые вы сохранили в CLOB, определенно не помогают. Также 30 тысяч строк. Также регулярные выражения. Следовательно, вам приходится иметь дело с довольно многими недостатками . Если бы вы могли перепроектировать модель данных и сохранить каждую строку (которая теперь является частью CLOB) в отдельный столбец (row), возможно, в столбец VARCHAR2, у вас был бы простой select substr(line, 1, up_to_the_2nd_*), count(*) ... group by substr... и наслаждайтесь скоростью света. Если нет, насколько я могу судить, вам придется смириться с его медлительностью.