oracle sql: разделить строку и вернуть непревзойденную часть как другую

У меня есть таблица, которая выглядит так:

AMT       TYPE
100       red, yellow, green, purple
200       red, green, blue, yellow
90        pink, blue, light red
......

Я хочу сложить суммы по цвету. Например, конечный результат должен быть таким:

AMT       TYPE
300       red
300       yellow
300       green
290       blue
190       other

Обратите внимание, что 1. я не хочу включать light red в red и 2. я хочу включить все цвета, кроме красного, желтого, зеленого и синего, в новую категорию «другое».

Мой текущий код

select sum(red), ... from (
      select
        case when trim(regexp_substr(type, red',1,1,'i')) is not null
             then amt
        else 0 end as red
        ......
      from mytable)

Но это не решает проблемы, о которых я упоминал ранее. Я также пробовал следующий метод, но он становится настолько медленным, что никогда не заканчивается. (Или, может быть, в этом коде есть какая-то ошибка?)

select color, sum(amt)
from (
     select trim(regexp_substr(type,'[^,]+', 1, level)) as color
     from mytable
     connect by level <= regexp_count(type, ',')+1)
group by color

Как я могу это решить?

Спасибо!


sql regex oracle split
person lareven    schedule 26.06.2014    source источник
comment
Теперь вы знаете, насколько громоздкой становится схема, если она не соответствует 1NF. Я мог бы подумать о создании (временной) пары таблиц, описывающих взаимосвязь между количествами и цветами в надлежащем реляционном стиле, а затем написать очевидный запрос.   -  person 9000    schedule 27.06.2014
comment
Точно... Но у меня нет контроля над базой данных... Не могли бы вы описать, как мне создать временную таблицу?   -  person lareven    schedule 27.06.2014
comment
stackoverflow.com/ вопросы/2671518/   -  person 9000    schedule 27.06.2014
comment
Извините за путаницу, но я спрашиваю, как создать это отношение между количеством и цветом...   -  person lareven    schedule 27.06.2014
comment
Вам не нужно явно создавать отношение, так как в вашей таблице нет правильно сформированного столбца цвета. Обычно вы должны использовать ограничение «внешний ключ». Смотрите мой ответ ниже для возможного решения.   -  person 9000    schedule 27.06.2014

Ответы (2)


arrow_upward
0
arrow_downward

У вас есть полный список цветов?

Представьте, что вы делаете. Пусть есть table color(name varchar2(...) not null primary key), в котором перечислены все цвета.

Тогда вы могли бы написать что-то вроде:

select 
  color.name, sum(crazy_table.amt)
from
  color, crazy_table -- the latter is your original data
where
  crazy_table.type like '%, ' || color.name -- at the end of string
  or 
  crazy_table.type like color.name || ', %' -- at the start of string
  or  crazy_table.type like '%, ' || color.name || ', %' -- middle
  or
  crazy_table.type = color.name -- single entry, no commas
  or
  color.name = 'other' and not exists ( -- no known color matches
    select 1 from color as c2
    where instr(crazy_table.type, c2.name) > 0
  )
group by color.name

Он будет полностью сканировать crazy_table, которая предположительно велика, выполняя поиск по индексу в таблице color, которая предположительно намного меньше, поэтому производительность должна быть в порядке.

person 9000    schedule 26.06.2014
comment
Ну, к сожалению, я не знаю. Но у меня есть список основных цветов и прочего. - person lareven; 27.06.2014
comment
Я обновил свой ответ, предполагая, что SQL не замыкает операторы ИЛИ. - person 9000; 27.06.2014

arrow_upward
0
arrow_downward

Следующий запрос даст вам желаемый результат. Это

1.first разбивает значения цвета на отдельные строки на основе решения 737905, данного для преобразования строки, разделенной запятыми, в строки на дискуссионном форуме OTN.

2. использует оператор CASE для обозначения категории «другие» для цветов, кроме красного, желтого, зеленого и синего.

3.группы по цвету

4. заказы по заранее определенному порядку цветов

WITH SPLIT_COLORS AS
  (
    SELECT
      AMT,
      TRIM(EXTRACT(column_value,'/e/text()')) COLOR
    FROM
      mytable x,
      TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<ROW><e>'
      ||REPLACE(type,',','</e><e>')
      ||'</e></ROW>'),'//e')))
  )
SELECT
  CASE
    WHEN color NOT IN ('red', 'yellow', 'green', 'blue') THEN 'other'
    ELSE color
  END AS color,
  SUM(amt) amt
FROM
  SPLIT_COLORS
GROUP BY
  CASE
    WHEN color NOT IN ('red', 'yellow', 'green', 'blue') THEN 'other'
    ELSE color
  END
ORDER BY
  CASE color
    WHEN 'red' THEN 1
    WHEN 'yellow' THEN 2
    WHEN 'green' THEN 3
    WHEN 'blue' THEN 4
    ELSE 5
  END;

Вы можете протестировать только вывод первой части (CTE — Common Table Expressions), как показано ниже:

WITH SPLIT_COLORS AS
  (
    SELECT
      AMT,
      TRIM(EXTRACT(column_value,'/e/text()')) COLOR
    FROM
      mytable x,
      TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<ROW><e>'
      ||REPLACE(type,',','</e><e>')
      ||'</e></ROW>'),'//e')))
  )
SELECT *
FROM SPLIT_COLORS;
person Joseph B    schedule 26.06.2014