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

Откуда:
Сообщений: 175
Есть у меня табличка операций в таком виде:
МагазинКассаКартаНоминал_картыСумма_операции
mag1kas11101
mag1kas11102
mag1kas12203
mag1kas21104


Номиналы находятся во взаимно однозначном отношении с номерами карт.

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

Количество разных карт проблем не вызывает: COUNT(distinct card_num).

Не могу придумать, как подсчитать номиналы для разных карт. Что-то вроде SUM(nominal OVER distinct card_num). :D

Для выше приведённых значений подойдёт следующий select. Он даёт как раз то, что хочется, но если у каких-то карт номиналы совпадут, рассчёты будут не верными:
WITH t AS (
SELECT 'mag1' magaz, 'kas1' kassa, 1 card_num, 10 nominal, 1 oper_amount from dual UNION ALL
SELECT 'mag1' magaz, 'kas1' kassa, 1 card_num, 10 nominal, 2 oper_amount from dual UNION ALL
SELECT 'mag1' magaz, 'kas1' kassa, 2 card_num, 20 nominal, 3 oper_amount from dual UNION ALL
SELECT 'mag1' magaz, 'kas2' kassa, 1 card_num, 10 nominal, 4 oper_amount from dual --UNION ALL
)
SELECT magaz, kassa
  ,COUNT(oper_amount) as oper_cnt
  ,COUNT(DISTINCT card_num) as card_num_cnt
  ,SUM(oper_amount) AS oper_sum
  ,SUM(distinct nominal) AS nominal_sum
FROM t
GROUP BY grouping sets((magaz,kassa),magaz)

Думаю, тут нужны фичи аналитического SQL, но точнее не знаю, в каком направлении "курить". :(
16 мар 11, 13:54    [10379451]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли сделать SUM по distinct другой колонки  [new]
Elic
Member

Откуда:
Сообщений: 29976
PranT
сумму номиналов для обслуженных разных карт.
Проблема в ненормализованности данных
PranT
Думаю, тут нужны фичи аналитического SQL
Промежуточная группировка.
16 мар 11, 15:15    [10380261]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли сделать SUM по distinct другой колонки  [new]
PranT
Member

Откуда:
Сообщений: 175
Elic
Промежуточная группировка.

А по какому полю её выполнять? Чтобы во всех разрезах group sets это учитывалось корректно.

Пока придумал такой вариант:
SUM(distinct card_num*nominal_max+nominal)-SUM(distinct card_num*nominal_max) AS nominal_sum,
где nominal_max = max(nominal) over()

:D
17 мар 11, 06:20    [10383001]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли сделать SUM по distinct другой колонки  [new]
-2-
Member

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

Напиши свою sum_дистинкт(карта_номинал(Карта, Номинал_карты))
17 мар 11, 07:55    [10383102]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли сделать SUM по distinct другой колонки  [new]
PranT
Member

Откуда:
Сообщений: 175
Уже что-то новое (для меня). :) Я могу написать свою собственную агрегирующую функцию?

А вообще, мне почему-то кажется, что в Oracle должно быть решение для такой задачи.
17 мар 11, 08:10    [10383131]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли сделать SUM по distinct другой колонки  [new]
Elic
Member

Откуда:
Сообщений: 29976
PranT
Elic
Промежуточная группировка.
А по какому полю её выполнять?
group by ..., card_num, nominal
17 мар 11, 08:21    [10383154]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли сделать SUM по distinct другой колонки  [new]
-2-
Member

Откуда:
Сообщений: 15330
Полагаясь, что номинал и номер карты оба короче 37 знаков.
  trunc(sum(distinct to_number(to_char(nominal,'TM')||to_char(0.1)||reverse(to_char(card_num,'TM'))))
       - sum(distinct to_char(0.1)||reverse(to_char(card_num,'TM')))) дистинкт_сум
17 мар 11, 08:29    [10383174]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли сделать SUM по distinct другой колонки  [new]
PranT
Member

Откуда:
Сообщений: 175
Спасибо.

Кстати, очень похоже на предложенный мною вариант с nominal_max. :)
17 мар 11, 09:04    [10383259]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли сделать SUM по distinct другой колонки  [new]
PranT
Member

Откуда:
Сообщений: 175
Elic, увы, не очень понимаю Ваш вариант решения. :(
17 мар 11, 09:08    [10383270]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли сделать SUM по distinct другой колонки  [new]
Elic
Member

Откуда:
Сообщений: 29976
PranT
Elic, увы, не очень понимаю Ваш вариант решения. :(
Увы, не понимаю непонимания.
(select magaz, kassa, card_num, nominal, sum(oper_amount) as oper_amount from ...
17 мар 11, 10:16    [10383588]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить