Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Запросик на добавление новых строк  [new]
tik-tak
Member

Откуда: Санкт-Петербург
Сообщений: 62
Набор данных
with t as
(
  select 1 my_type, 1 val from dual
   union all
  select 1 my_type, null val from dual
   union all
  select 1 my_type, 5 val from dual  
   union all
  select 1 my_type, 3 val from dual    
   union all
  select 1 my_type, 3 val from dual    
   union all
  select 1 my_type, 3 val from dual   
   union all
  select 3 my_type, 1 val from dual  
   union all
  select 2 my_type, 5 val from dual    
   union all
  select 2 my_type, 3 val from dual
   union all
  select 2 my_type, null val from dual       
)
select * from t;

 MY_TYPE        VAL
---------- ----------
         1          1
         1 
         1          5
         1          3
         1          3
         1          3
         3          1
         2          5
         2          3
         2 
 
10 rows selected

Надо выровнять количество строк с разным my_type.
C my_type = 1 есть 6 строк, значит надо сделать для my_type in (2,3) тоже по 6 строк.
Для набора с добавлеными строками, должны существовать такие же значения val, как и для большего набора. То есть, для набора с my_type=2 должны быть строки с val = 1,5,3,3,3, null
В малых наборах не будут содержаться значения, которых нет в большом наборе.

Пример того, что должно быть на выходе


MY_TYPE VAL
---------- ----------
1 1
1
1 5
1 3
1 3
1 3

3 1
3
3 5
3 3
3 3
3 3

2 5
2 3
2 3
2 3
2 1
2

Пример того как можно добить до нужного количества строк пустыми значениями:
with t as
(
  select 1 my_type, 1 val from dual
   union all
  select 1 my_type, null val from dual
   union all
  select 1 my_type, 5 val from dual  
   union all
  select 1 my_type, 3 val from dual    
   union all
  select 1 my_type, 3 val from dual    
   union all
  select 1 my_type, 3 val from dual   
   union all
  select 3 my_type, 1 val from dual  
   union all
  select 2 my_type, 5 val from dual    
   union all
  select 2 my_type, 3 val from dual
   union all
  select 2 my_type, null val from dual       
)
--
--select * from t
select my_type,
       case when column_value > 1 then null
            else val
       end val
      
from ( 
      -- получаем количество строк, которое необходимо добавить
      select my_type, 
             val, 
             rn, 
             max_rn,
             case when my_type = next_type then 1
                  else max_rn - rn + 1
             end add_rn,
             next_type
      from (
            -- получаем max количество строк и тип строки идущий следующим
            select my_type,
                   val,
                   rn,
                   max(rn) over()          as max_rn,
                   lead(my_type,1,0) over(order by my_type) next_type
            from (
                  -- получаем количество строк для каждого типа
                  select my_type, 
                         val, 
                         count(1) over(partition by my_type) as rn 
                  from t
                 )
            )
      ) d
      ,table(select collect(level) from dual connect by level<=d.add_rn)

Подскажите, пожалуйста )
2 авг 10, 13:14    [9192977]     Ответить | Цитировать Сообщить модератору
 Re: Запросик на добавление новых строк  [new]
-2-
Member

Откуда:
Сообщений: 15330
tik-tak,

select ct.column_value my_type, nullif(cv.column_value,-1) val
from (
   select my_type, 
          cast(collect(nvl(val,-1)) as sys.odcinumberlist) cval,
          cast(collect(my_type) over() as sys.odcinumberlist) ctype,
          row_number() over(order by count(*) desc) r
   from t 
   group by my_type
), table(ctype) ct, table(cval) cv
where r = 1
;
2 авг 10, 13:49    [9193403]     Ответить | Цитировать Сообщить модератору
 Re: Запросик на добавление новых строк  [new]
Elic
Member

Откуда:
Сообщений: 29980
tik-tak
Для набора с добавлеными строками, должны существовать такие же значения val, как и для большего набора.
В малых наборах не будут содержаться значения, которых нет в большом наборе.
select t.my_type, e.val
  from (select distinct my_type from t) t
     , (select * from t where my_type = (select min(my_type) keep (dense_rank last order by count(*)) from t group by my_type)) e
;
2 авг 10, 13:52    [9193432]     Ответить | Цитировать Сообщить модератору
 Re: Запросик на добавление новых строк  [new]
tik-tak
Member

Откуда: Санкт-Петербург
Сообщений: 62
Elic,

спасибо большое, буду разбираться.
2 авг 10, 13:58    [9193480]     Ответить | Цитировать Сообщить модератору
 Re: Запросик на добавление новых строк  [new]
tik-tak
Member

Откуда: Санкт-Петербург
Сообщений: 62
-2-,

у меня, к сожалению, не отрабатывает данный запрос

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 

with t as
(
  select 1 my_type, 1 val from dual
   union all
  select 1 my_type, null val from dual
   union all
  select 1 my_type, 5 val from dual
   union all
  select 1 my_type, 3 val from dual
   union all
  select 1 my_type, 3 val from dual
   union all
  select 1 my_type, 3 val from dual
   union all
  select 3 my_type, 1 val from dual
   union all
  select 2 my_type, 5 val from dual
   union all
  select 2 my_type, 3 val from dual
   union all
  select 2 my_type, null val from dual
)
select ct.column_value my_type, nullif(cv.column_value,-1) val
from (
   select my_type,
          cast(collect(nvl(val,-1)) as sys.odcinumberlist) cval,
          cast(collect(my_type) over() as sys.odcinumberlist) ctype,
          row_number() over(order by count(*) desc) r
   from t
   group by my_type
), table(ctype) ct, table(cval) cv
where r = 1
 
ORA-03113: принят сигнал конца файла по коммуникационному каналу
2 авг 10, 14:18    [9193731]     Ответить | Цитировать Сообщить модератору
 Re: Запросик на добавление новых строк  [new]
tik-tak
Member

Откуда: Санкт-Петербург
Сообщений: 62
Дам более полный обзор исходных данных, ибо слишком упростил.

Необходима еще группировка по полю id, и добавлять так же значение val2 (так же как val1).
Комбинация val1 и val2 из меньших наборов, всегда присутствует в большом наборе.

with t as
(
  select 80 id,  1 my_type, 1 val  , 1 val2 from dual
   union all
  select 80 id, 1 my_type, null val, 1 val2 from dual
   union all
  select 80 id, 1 my_type, 5 val, 2 val2 from dual  
   union all
  select 80 id, 1 my_type, 3 val, 1 val2 from dual    
   union all
  select 80 id, 1 my_type, 3 val, 1 val2 from dual    
   union all
  select 80 id, 1 my_type, 3 val, 1 val2 from dual   
   union all
  select 80 id, 3 my_type, 1 val, 1 val2 from dual  
   union all
  select 80 id, 2 my_type, 5 val, 2 val2 from dual    
   union all
  select 80 id, 2 my_type, 3 val, 1 val2 from dual
   union all
  select 80 id, 2 my_type, null val , 1 val2 from dual       
   union all
  select 90 id, 1 my_type, null val, 1 val2 from dual   
   union all
  select 90 id, 1 my_type, 1 val, 1 val2 from dual     
   union all
  select 90 id, 1 my_type, 3 val, 1 val2 from dual     
   union all   
  select 90 id, 2 my_type, 1 val, 1 val2 from dual  
)
select * from t
2 авг 10, 14:35    [9193945]     Ответить | Цитировать Сообщить модератору
 Re: Запросик на добавление новых строк  [new]
Lecter
Member

Откуда: Киев
Сообщений: 2032
tik-tak

ORA-03113: принят сигнал конца файла по коммуникационному каналу

Ой как похоже на какой нить баг :)
2 авг 10, 14:43    [9194035]     Ответить | Цитировать Сообщить модератору
 Re: Запросик на добавление новых строк  [new]
tik-tak
Member

Откуда: Санкт-Петербург
Сообщений: 62
Lecter,

ему не нравиться конструкция
... collect(my_type) over() ... 
2 авг 10, 14:48    [9194084]     Ответить | Цитировать Сообщить модератору
 Re: Запросик на добавление новых строк  [new]
Elic
Member

Откуда:
Сообщений: 29980
tik-tak
Необходима еще
Ничего сложного, т.е. пора включать собственный мыслительный аппарат.
2 авг 10, 14:55    [9194141]     Ответить | Цитировать Сообщить модератору
 Re: Запросик на добавление новых строк  [new]
tik-tak
Member

Откуда: Санкт-Петербург
Сообщений: 62
Elic,

На основе показаного вами решения, можно составить следующий запрос

with d as
(
  select 80 id,  1 my_type, 1 val  , 1 val2 from dual
   union all
  select 80 id, 1 my_type, null val, 1 val2 from dual
   union all
  select 80 id, 1 my_type, 5 val, 2 val2 from dual  
   union all
  select 80 id, 1 my_type, 3 val, 1 val2 from dual    
   union all
  select 80 id, 1 my_type, 3 val, 1 val2 from dual    
   union all
  select 80 id, 1 my_type, 3 val, 1 val2 from dual   
   union all
  select 80 id, 3 my_type, 1 val, 1 val2 from dual  
   union all
  select 80 id, 2 my_type, 5 val, 2 val2 from dual    
   union all
  select 80 id, 2 my_type, 3 val, 1 val2 from dual
   union all
  select 80 id, 2 my_type, null val , 1 val2 from dual       
   union all
  select 90 id, 1 my_type, null val, 1 val2 from dual   
   union all
  select 90 id, 1 my_type, 1 val, 1 val2 from dual     
   union all
  select 90 id, 1 my_type, 3 val, 1 val2 from dual     
   union all   
  select 90 id, 2 my_type, 1 val, 1 val2 from dual  
)
select * from 
(select id, my_type from d group by id, my_type) t1,
( select * from d t where my_type = 
(select min(my_type) keep (dense_rank last order by count(*)) from d where id= t.id group by my_type)) t2
where t1.id = t2.id
order by t1.id, t1.my_type

        ID    MY_TYPE         ID    MY_TYPE        VAL       VAL2
---------- ---------- ---------- ---------- ---------- ----------
        80          1         80          1          1          1
        80          1         80          1          3          1
        80          1         80          1          5          2
        80          1         80          1          3          1
        80          1         80          1                     1
        80          1         80          1          3          1
        80          2         80          1          1          1
        80          2         80          1          3          1
        80          2         80          1          3          1
        80          2         80          1          5          2
        80          2         80          1                     1
        80          2         80          1          3          1
        80          3         80          1          3          1
        80          3         80          1          5          2
        80          3         80          1                     1
        80          3         80          1          1          1
        80          3         80          1          3          1
        80          3         80          1          3          1
        90          1         90          1                     1
        90          1         90          1          1          1
        90          1         90          1          3          1
        90          2         90          1                     1
        90          2         90          1          1          1
        90          2         90          1          3          1
 
24 rows selected

Но для большой выборки cost весьма великоват. Хочется надеяться, что можно это сделать более быстродействующим способом.
2 авг 10, 15:08    [9194241]     Ответить | Цитировать Сообщить модератору
 Re: Запросик на добавление новых строк  [new]
tik-tak
Member

Откуда: Санкт-Петербург
Сообщений: 62
Приходится еще усложнять выборку.

Если строка для меньшего набора уже существует, то val3 оставлять из меньшего набора. иначе null.

with t as
(
  select 80 id,  1 my_type, 1 val  , 1 val2, 7 val3 from dual
   union all
  select 80 id, 1 my_type, null val, 1 val2, 7 val3 from dual
   union all
  select 80 id, 1 my_type, 5 val, 2 val2, 8 val3 from dual  
   union all
  select 80 id, 1 my_type, 3 val, 1 val2, 2 val3 from dual    
   union all
  select 80 id, 1 my_type, 3 val, 1 val2, 9 val3 from dual    
   union all
  select 80 id, 1 my_type, 3 val, 1 val2, 9 val3 from dual   
   union all
  select 80 id, 3 my_type, 1 val, 1 val2, 7 val3 from dual  
   union all
  select 80 id, 2 my_type, 5 val, 2 val2, 7 val3 from dual    
   union all
  select 80 id, 2 my_type, 3 val, 1 val2, 7 val3 from dual
   union all
  select 80 id, 2 my_type, null val , 1 val2, 8 val3 from dual       
   union all
  select 90 id, 1 my_type, null val, 1 val2, 4 val3 from dual   
   union all
  select 90 id, 1 my_type, 1 val, 1 val2, 8 val3 from dual     
   union all
  select 90 id, 1 my_type, 3 val, 1 val2, 7 val3 from dual     
   union all   
  select 90 id, 2 my_type, 1 val, 1 val2, 7 val3 from dual  
)
select * from t;

        ID    MY_TYPE        VAL       VAL2       VAL3
---------- ---------- ---------- ---------- ----------
        80          1          1          1          7
        80          1                     1          7
        80          1          5          2          8
        80          1          3          1          2
        80          1          3          1          9
        80          1          3          1          9
        80          3          1          1          7
        80          2          5          2          7
        80          2          3          1          7
        80          2                     1          8
        90          1                     1          4
        90          1          1          1          8
        90          1          3          1          7
        90          2          1          1          7
2 авг 10, 15:27    [9194415]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить