Выборка с группировкой и разбиением

gudzon10

Доброго времени суток! Обращаюсь с таким вопросом: БД Oracle, нужен select, выдающий таблицу в определённом виде. Дана таблица каких-либо операций, где среди прочих есть следующие поля: DATE, ID, AMOUNT, ID_EQUIP По ключу ID в итоговый запрос вытаскивается из другой таблицы другой номер, скажем ID_NUMBER Соответственно строка данных выглядит следующим образом (пример) 01.01.13 == 123456789 == 254000 == 1 01.01.13 == 123456790 == 24000 == 2 | итд.Задача следующая - вытащить список ID_NUMBER, у которых сумма AMOUNT за месяц превышает значение 200000, причём в разрезе по ID_EQIP. Период учитываемых операций - 8 месяцев. То есть, результат должен получится примерно такой: 01.13 == 123456789 == 120000 == 1 01.13 == 123456789 == 95000 == 2 01.13 == 123456790 == 320000 == 1 02.13 == 123456791 == 260000 == 2 02.13 == 123456792 == 80000 == 3 02.13 == 123456793 == 140000 == 2 итдМой запрос примерно следующий:
SELECT to_char(op.date, "MM.YY"), lst.id_number, SUM(op.amount), op.equip FROM operations op, id_list lst
WHERE op.id = lst.id
AND op.date BETWEEN '01.01.13' AND '31.08.13'
GROUP BY to_char(op.date, "MM.YY"), lst.id_number, op.equip
HAVING SUM(op.amount) > 200000
ORDER BY to_char(op.date, "MM.YY")
Но при таком раскладе не получается разбить поле op.equip, берётся значение, которого было больше - например если 10 сумм с equip 1, а одна с 2, напишется 1, хотя суммы требованию удовлетворяют. Пробовал в два запроса - сначала получить список требуемых ID_NUMBER, а по этому списку уже искал дальше - таким образом equip отображается верно, но значения id_number берутся в других месяцах даже при сумме < 200000, если хотя бы в одном было> 200000. Вручную вычищать - не вариант, делать 8 запросов по каждому месяцу - тоже.Помогите, пожалуйста, добрые люди.Заранее спасибо.
6 ответов

gudzon10

Вы серьёзно? За 2 дня ни у кого никаких мыслей не возникло?


gudzon10

Давайте нормальный пример. Что есть в одной таблице, что есть в другой, и что должно получиться. PS. На форуме поддерживаются таблицы. Тэг - TABLE. Разделять столбцы - |
[TABLE]col1|col2|col3
val1|val2|val3[/TABLE]


gudzon10

Ну да конкретный пример с реальными данными не помешал бы так просто не очень понятно.


gudzon10

Хорошо, сейчас попробую. Есть таблица transaction, в ней есть следующие поля:
DATE ID AMOUNT ID_EQUIP
01.01.2015 1001 120 1
02.01.2015 1002 130 2
06.01.2015 1001 105 1
07.04.2015 1002 220 2
05.01.2015 1001 150 2
03.03.2015 1001 110 1
03.01.2015 1003 140 3
Есть ещё таблица account, в ней следующие поля:
ID NUMBER_ACCOUNT
1001 145623647
1002 134678641
1003 147631567
Нужно выбрать такие NUMBER_ACCOUNT, у которых AMOUNT в месяц > 200, но при этом указать суммы для разных ID_EQUIP, и всё это в разрезе месяца. То есть, итоговая таблица должна быть вида:
Месяц NUMBER_ACCOUNT Сумма ID_EQUIP
01.15 145623647 225 1
01.15 145623647 150 2
04.15 134678641 220 2
Надеюсь, так нагляднее. Спасибо заранее)


gudzon10

Как-то так. Переименовал столбец DATE в DATE_OF, чтобы Oracle не ругался
SELECT MONTH
     , NUMBER_ACCOUNT
     , SUM(AMOUNT)
     , ID_EQUIP
FROM (
   SELECT trunc(DATE_OF, 'MONTH') MONTH, A.NUMBER_ACCOUNT, T.AMOUNT, T.ID_EQUIP
        , SUM(AMOUNT) OVER (partition BY trunc(DATE_OF, 'MONTH'), A.NUMBER_ACCOUNT) SUM_BY_ACCOUNT_MONTH
   FROM TRANSACTION T
        JOIN ACCOUNT A ON T.ID = A.ID
)
WHERE SUM_BY_ACCOUNT_MONTH > 200
GROUP BY MONTH, NUMBER_ACCOUNT, ID_EQUIP
Вся фишка - в использовании аналитической функции sum() over. Она позволяет аггрегировать результат без, собственно, схлопывания строк.http://www.sqlfiddle.com/#!4/23782/7


gudzon10

turbanoff, спасибо! Ничего не понял, но есть теперь с чем разбираться) Пойду слушать про аналитические функции, спасибо ещё раз)