Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / Помогите написать запрос / 5 сообщений из 5, страница 1 из 1
09.10.2013, 14:02
    #38421536
Павел Гужанов
Павел Гужанов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Здравствуйте.

Есть такая таблица:

Код: plaintext
1.
2.
3.
ID     SUMM1    SUMM2
1     20000     25000
2     15000     25000
3     10000     25000

Нужно получить
для первой строки если SUMM1 >= SUMM2, то SUMM2, иначе SUMM1
Для остальных строк если SUMM1 >= SUMM2 - sum(то, что получилось в предыдущих строках), то SUMM2 - sum(то, что получилось в предыдущих строках), иначе sum(то, что получилось в предыдущих строках) - SUMM1.

В приведенном примере должно получиться
20000
5000
0

Подскажите, как такое сделать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2013, 15:45
    #38421737
CawaSPb
CawaSPb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Павел Гужанов,

Строго по описанию:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
CREATE OR REPLACE FUNCTION SUMM_VALUE(IN SUMM1 INTEGER, IN SUMM2 INTEGER, IN PREVIOUS_SUMM INTEGER)
  RETURNS INTEGER
  SPECIFIC SUMM_VALUE
  DETERMINISTIC  CONTAINS SQL  NO EXTERNAL ACTION
  RETURN
   CASE WHEN SUMM1 >= SUMM2 - PREVIOUS_SUMM
     THEN SUMM2 - PREVIOUS_SUMM
     ELSE PREVIOUS_SUMM - SUMM1
   END; 
 
with t1(ID, SUMM1, SUMM2) as (
 values (1, 20000, 25000),
        (2, 15000, 25000),
        (3, 10000, 25000)
),
t2(ID, SUMM1, SUMM2, RNUM) as (
  select ID, SUMM1, SUMM2, ROW_NUMBER() OVER(ORDER BY ID) as RNUM from t1
),
t3(RNUM, SUMM_VALUE, PREVIOUS_SUMM) as (
  select RNUM, LEAST(SUMM1, SUMM2), LEAST(SUMM1, SUMM2) from t2 where RNUM = 1
  union all
  select t2.RNUM
        ,SUMM_VALUE(t2.SUMM1, t2.SUMM2, t3.PREVIOUS_SUMM)
        ,SUMM_VALUE(t2.SUMM1, t2.SUMM2, t3.PREVIOUS_SUMM) + t3.PREVIOUS_SUMM
  from t2, t3 where t2.RNUM = t3.RNUM + 1
)
select SUMM_VALUE from t3 order by RNUM;



Если есть гарантии, что ID строго последовательны, то можно обойтись без t2.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2013, 16:40
    #38421822
Павел Гужанов
Павел Гужанов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Спасибо огромное! Работает правильно, с различными данными.

Пользуясь Вашей подсказкой сделал запрос без функции:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
with table1 (id,summ1,summ2) as (
 values (1, 20000, 25000),
        (2, 15000, 25000),
        (3, 10000, 25000)
),first(ID, SUMM1, SUMM2) as (
select id,summ1,summ2 from table1
),other(RNUM, SUMM_VALUE, PREVIOUS_SUMM) as (
  select id, LEAST(SUMM1, SUMM2), LEAST(SUMM1, SUMM2) 
  from first where id = 1
  union all
  select first.ID,
  case 
    when first.SUMM1>=first.SUMM2 - other.PREVIOUS_SUMM
      then first.SUMM2 - other.PREVIOUS_SUMM
    else other.PREVIOUS_SUMM - first.SUMM1
  end,
 case 
    when first.SUMM1>=first.SUMM2 - other.PREVIOUS_SUMM
      then first.SUMM2
    else other.PREVIOUS_SUMM - first.SUMM1  + other.PREVIOUS_SUMM
  end 
  from first, other where first.ID = other.RNUM + 1
)
select SUMM_VALUE from other order by RNUM



Подскажите пожалуйста, почему Вы использовали LEAST а не MIN? Или это не принципиально?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2013, 18:01
    #38421966
CawaSPb
CawaSPb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Павел Гужанов,

Отдельная DETERMINISTIC функция без EXTERNAL ACTION может "дёргаться" один (а может и не один) раз для каждой строки исходной таблицы, что может быть чуть-чуть быстрее, если это существенно.
В любом случае правило расчёта следующей суммы лучше иметь в строго одном месте, чтобы не забыть поправить, если оно поменяется в будущем.
С использованием ф-ии выражение в целом становится чуть более читаемо. Сейчас даже содержимое CASE'ов там разное, поди догадайся, что там имелось ввиду.

Я бы также рекомендовал внимательно отнестись к ID (без пропусков строго от единицы и далее через 1). Если это не данные откуда-то извне (типа номеров строк), а IDENTITY поле, то там могут быть пропуски (рубильник выключили, транзакция на вставке отвалилась, а номер уже выбрали). Если пропуски есть, то надо строки "перенумеровать" в промежуточном выражении.

LEAST(...) - только чтобы явно указать семантику. Для читаемости. То, что это скалярная функция, а не аггрегативная (максимальное значение в колонке).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.10.2013, 12:15
    #38426344
Павел Гужанов
Павел Гужанов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
К сожалению, такой вариант в некоторых ситуациях работал неправильно. Придумал другой запрос, он работает правильно. По-крайней мере, я пока не нашел такого варианта исходных данных, при котором результат получился бы неправильным (3 - это максимальное занчение поля ORDER, которое может быть):

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
with t1 (ID,SUMM1,SUMM2,ORDER) as (
values (1,20000,28000,1),
        (1,10000,28000,2),
        (1,5000,28000,3),
        (2,25000,41000,1),
        (2,15000,41000,2),
        (2,10000,41000,3)
)select t1.ID,
    	case 
      		when t1.SUMM1 >= t1.SUMM2 and t1.ORDER = 1 
      			then t1.SUMM2
      		when t1.SUMM1 < t1.SUMM2 and t1.ORDER = 1 
      			then t1.SUMM1
      		when t1.SUMM1 >= t1.SUMM2 - sum(t1.SUMM1) over (partition by t1.ID order by t1.ORDER rows between 3 preceding and 1 preceding) 
				and t1.ORDER <> 1
        				and t1.SUMM2 - sum(t1.SUMM1) over (partition by t1.ID order by t1.ORDER rows between 3 preceding and 1 preceding) > 0 
      			then t1.SUMM2 - sum(t1.SUMM1) over (partition by t1.ID order by t1.ORDER rows between 3 preceding and 1 preceding)
      		when t1.SUMM1 >= t1.SUMM2 - sum(t1.SUMM1) over (partition by t1.ID order by t1.ORDER rows between 3 preceding and 1 preceding) and t1.ORDER <> 1
       				and t1.SUMM2 - sum(t1.SUMM1) over (partition by t1.ID order by t1.ORDER rows between 3 preceding and 1 preceding) <= 0 
      			then 0
      		when t1.SUMM1 < t1.SUMM2 - sum(t1.SUMM1) over (partition by t1.ID order by t1.ORDER rows between 3 preceding and 1 preceding) and t1.ORDER <> 1
        				and t1.SUMM2 - sum(t1.SUMM1) over (partition by t1.ID order by t1.ORDER rows between 3 preceding and 1 preceding) > 0 
      			then t1.SUMM1
      		when t1.SUMM1 < t1.SUMM2 - sum(t1.SUMM1) over (partition by t1.ID order by t1.ORDER rows between 3 preceding and 1 preceding) and t1.ORDER <> 1
        				and t1.SUMM2 - sum(t1.SUMM1) over (partition by t1.ID order by t1.ORDER rows between 3 preceding and 1 preceding) <= 0 
      			then 0
    	end
from t1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / Помогите написать запрос / 5 сообщений из 5, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]