Помогите со сложным [для меня] запросом

Запросы, планы, оптимизация запросов, ...

Модераторы: kdv, CyberMax

frostyland
Сообщения: 38
Зарегистрирован: 05 июл 2007, 13:24

Помогите со сложным [для меня] запросом

Сообщение frostyland » 02 сен 2008, 16:58

Заплюхался я с запросом из 4 таблиц. :x

Есть таблички

Код: Выделить всё

CREATE TABLE CSTMONTH( // месяцы года
  MONTH_NO MONTHNO NOT NULL,
  CSTMONTH VARCHAR(8)
);

CREATE TABLE DEPT( // отделы
  DEPT_NO    DEPTNO NOT NULL,
  DEPT VARCHAR(25)
);

CREATE TABLE PDN( // расходы по отделам за каждый месяц
  PDN         DECIMAL(9,2) DEFAULT 0,
  FK_DEPT_NO  INTEGER,
  FK_MONTH_NO INTEGER
);

CREATE TABLE DEAL( // списки договоров по отделам за каждый месяц
  DEAL VARCHAR(25)
  VALUE         DECIMAL(9,2) DEFAULT 0,
  FK_DEPT_NO  INTEGER,
  FK_MONTH_NO INTEGER
);
Каждый отдел в DEPT имеет ежемесячную сумму расходов, поэтому таблички связаны так
DEPT <-- PDN --> CSTMONTH.
Это позволяет смотреть РАСХОДЫ из PDN помесячно для каждого отдела (пример вывода параметризованного запроса за месяцы 1, 2, 3)
Изображение

Но каждый месяц по каждому отделу проходит списко договоров, составляющих вместе определенную сумму
Договора имеют связь с месяцами и отделами вот такую
DEPT <-- DEAL --> CSTMONTH.
И в идеале бы хотелось видеть табло, где видно и затраты по всем отделам списком за ВЫБРАННЫЙ месяц и сумму договоров по отделам за этот же месяц!!!
вот так например
Изображение
То есть, по отделам, где в данном месяце не было договоров, тоже должна быть строка (с ноликом или уж просто пустая :) )
Я что-то не могу наваять такой запрос. Видимо где-то SQL-дыра в башке :)
Подскажите, плззз

Merlin
Динозавр IB/FB
Сообщения: 1502
Зарегистрирован: 27 окт 2004, 11:44

Re: Помогите со сложным [для меня] запросом

Сообщение Merlin » 02 сен 2008, 17:17

frostyland писал(а):Видимо где-то SQL-дыра в башке :)
В области, отвечающей за понимание LEFT JOIN.

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Re: Помогите со сложным [для меня] запросом

Сообщение kdv » 02 сен 2008, 17:18

поскольку исходный запрос не показан, предложу left/right join и посмотреть сюда - www.ibase.ru/devinfo/joins.htm

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Re: Помогите со сложным [для меня] запросом

Сообщение WildSery » 02 сен 2008, 17:28

Не, Merlin, у него двойной лефт джойн, и нужно чтобы записи не размножались.
Классика, конечно, но для новичка тяжело.

Автор, версия сервера какая?

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Re: Помогите со сложным [для меня] запросом

Сообщение kdv » 02 сен 2008, 18:02

эээ, тут два момента
1. мы не видим запрос, который пробовал автор вопроса.
2. мы видим спец-грид, который группирует данные

так что фиг знает, что именно и в каком виде поставляет данные в грид.

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Re: Помогите со сложным [для меня] запросом

Сообщение WildSery » 02 сен 2008, 18:11

kdv писал(а):так что фиг знает, что именно и в каком виде поставляет данные в грид.
Это не спецгрид, это Excel :)
По-моему, как раз всё ясно - DEPT, sum(PDN), sum(DOGOVOR)

frostyland
Сообщения: 38
Зарегистрирован: 05 июл 2007, 13:24

Re: Помогите со сложным [для меня] запросом

Сообщение frostyland » 02 сен 2008, 18:16

WildSery писал(а):
kdv писал(а):так что фиг знает, что именно и в каком виде поставляет данные в грид.
Это не спецгрид, это Excel :)
По-моему, как раз всё ясно - DEPT, sum(PDN), sum(DOGOVOR)
Да - это я просто в Экселе скинул типа желаемые варианты . Конечно это не спец-грид.
Читаю сейчас ссылку уважаемого kdv... Попробую радобраться

Merlin
Динозавр IB/FB
Сообщения: 1502
Зарегистрирован: 27 окт 2004, 11:44

Re: Помогите со сложным [для меня] запросом

Сообщение Merlin » 02 сен 2008, 19:00

WildSery писал(а):Не, Merlin, у него двойной лефт джойн, и нужно чтобы записи не размножались.
Имхо на затраты INNER - постоянные всё равно идут, была деятельность, нет...

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Re: Помогите со сложным [для меня] запросом

Сообщение WildSery » 02 сен 2008, 21:20

Merlin писал(а):Имхо на затраты INNER - постоянные всё равно идут, была деятельность, нет...
Да не суть, это он сам решит, есть у него такие записи или нет. Проблема-то у автора в размножении строк.
А автор так и не ответил, сервер какой версии используется.

frostyland
Сообщения: 38
Зарегистрирован: 05 июл 2007, 13:24

Re: Помогите со сложным [для меня] запросом

Сообщение frostyland » 03 сен 2008, 06:33

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

Автор, версия сервера какая?
Version: WI-V1.5.3.4870

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Re: Помогите со сложным [для меня] запросом

Сообщение WildSery » 03 сен 2008, 11:22

frostyland писал(а):Version: WI-V1.5.3.4870
Если за 1 месяц данные - то DEPT JOIN PDN GROUP BY а сумму по DEAL как подзапрос.
Если за несколько указанных месяцев сразу - аналогично, только в подзапрос ещё месяц из PDN пихать.

Gera
Сообщения: 53
Зарегистрирован: 12 мар 2008, 17:34

Re: Помогите со сложным [для меня] запросом

Сообщение Gera » 03 сен 2008, 13:16

Я в таких случаях использую представление с таким содержанием:

Код: Выделить всё

SELECT
  D.DEPT_NO,
  C.MONTH_NO,
  D.DEPT AS DEPARTMENT,
  C.CSTMONTH,
  COALESCE(PDN.PDN, 0) AS PDN,
  COALESCE(DEAL.VALUE, 0) AS SUM_DOGOV
FROM CSTMONTH C
CROSS JOIN DEPT D
LEFT JOIN
  (SELECT
    SUM(PDN) AS PDN,
    FK_DEPT_NO,
    FK_MONTH_NO
  FROM PDN
  GROUP BY FK_DEPT_NO, FK_MONTH_NO
  ) AS PDN
  ON PDN.FK_DEPT_NO = D.DEPT_NO AND PDN.FK_MONTH_NO = C.MONTH_NO
LEFT JOIN
  (SELECT
    SUM(VALUE) AS VALUE,
    FK_DEPT_NO,
    FK_MONTH_NO
  FROM DEAL
  GROUP BY FK_DEPT_NO, FK_MONTH_NO
  ) AS DEAL
  ON DEAL.FK_DEPT_NO = D.DEPT_NO AND DEAL.FK_MONTH_NO = C.MONTH_NO

frostyland
Сообщения: 38
Зарегистрирован: 05 июл 2007, 13:24

Re: Помогите со сложным [для меня] запросом

Сообщение frostyland » 03 сен 2008, 13:38

Gera писал(а):Я в таких случаях использую представление с таким содержанием:
...........
Не прокатило... BlazeTop ругнулся

Код: Выделить всё

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 9, char 5.
CROSS.

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Re: Помогите со сложным [для меня] запросом

Сообщение kdv » 05 сен 2008, 18:01

это не blazetop, а сервер ругнулся.

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Re: Помогите со сложным [для меня] запросом

Сообщение WildSery » 05 сен 2008, 18:08

Gera писал(а):Я в таких случаях использую представление с таким содержанием:
А я всегда сперва читаю ответ автора о версии сервера.

frostyland
Сообщения: 38
Зарегистрирован: 05 июл 2007, 13:24

Re: Помогите со сложным [для меня] запросом

Сообщение frostyland » 06 сен 2008, 11:26

В общем, благодаря сообществу, родился вот такой запрос

Код: Выделить всё

SELECT
  C.MONTH_NO,
  D.DEPT_NO,
  D.DEPARTMENT,
  P.PDN,
  P.FK_DEPT_NO, 
  P.FK_MONTH_NO,
  COALESCE(S.SUMMA,0) AS SUM_DOGOVOR
FROM DEPARTMENT D
  INNER JOIN CSTMONTH C
    INNER JOIN 
      PDN P
        LEFT JOIN
              (SELECT
                D.FK_MONTH_NO,
                D.FK_DEPT_NO,
                Sum(D.DEALSUM) AS SUMMA,
                COUNT(*) AS CNT
              FROM DEAL AS D
              GROUP BY D.FK_MONTH_NO, D.FK_DEPT_NO) S
        ON P.FK_DEPT_NO = S.FK_DEPT_NO
            AND P.FK_MONTH_NO = S.FK_MONTH_NO
    ON C.MONTH_NO = P.FK_MONTH_NO
  ON D.DEPT_NO = P.FK_DEPT_NO
WHERE
  C.MONTH_NO  = :MONZ
Спасибо за поддержку

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Re: Помогите со сложным [для меня] запросом

Сообщение WildSery » 08 сен 2008, 12:44

frostyland писал(а):В общем, благодаря сообществу, родился вот такой запрос
Так, стоять. Куда пошёл?! Поддержка не закончена :)
1. Если у тебя не выбирается наименование месяца, то зачем ты цепляешь таблицу CSTMONTH?
2. Зачем дубликаты полей выбираешь? Т.е. нафиг FK_MONTH_NO и MONTH_NO отдельно, если они равны? Так же зачем DEPT_NO и FK_DEPT_NO?
3. COUNT(*) тебе зачем во вложенном запросе?
4. Собственно, зачем сам вложенный запрос, если ты оттуда кроме суммы ничего не берёшь?

Я бы сделал так:

Код: Выделить всё

SELECT P.FK_MONTH_NO, D.DEPT_NO, D.DEPARTMENT, P.PDN,
       (SELECT COALESCE(SUM(DEALSUM), 0)
          FROM DEAL
          WHERE FK_DEPT_NO = P.FK_DEPT_NO AND FK_MONTH_NO = P.FK_MONTH_NO
       ) AS SUM_DOGOVOR
  FROM DEPARTMENT D
       JOIN PDN P ON D.DEPT_NO = P.FK_DEPT_NO
  WHERE P.MONTH_NO  = :MONZ

frostyland
Сообщения: 38
Зарегистрирован: 05 июл 2007, 13:24

Re: Помогите со сложным [для меня] запросом

Сообщение frostyland » 08 сен 2008, 18:06

WildSery писал(а): Так, стоять. Куда пошёл?! Поддержка не закончена :)
1. Если у тебя не выбирается наименование месяца, то зачем ты цепляешь таблицу CSTMONTH?
2. Зачем дубликаты полей выбираешь? Т.е. нафиг FK_MONTH_NO и MONTH_NO отдельно, если они равны? Так же зачем DEPT_NO и FK_DEPT_NO?
3. COUNT(*) тебе зачем во вложенном запросе?
4. Собственно, зачем сам вложенный запрос, если ты оттуда кроме суммы ничего не берёшь?
Стою :)
1, 2. CSTMONTH и прочие, на первый взгляд дубликаты я выбирал просто для теста, чтобы наглядно убедиться по результатам запроса в правильности выборок
3. Это да, косячок, не понял от куда он там взялся. Пропустил.
4. Слабовата у меня теория, теперь вот благодаря Вам знаю альтернативные способы запросить аггрегирование суммы из другой таблички. Со временем попроще будет :)

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Re: Помогите со сложным [для меня] запросом

Сообщение WildSery » 08 сен 2008, 18:32

Вообще, мне сперва показалось, по таблице PDN тоже суммировать надо, а из твоего решения с удивлением увидел, что 1-к-1 связь.
Или это-таки неправильно, и суммировать всё же надо? 2 записи в PDN могут быть за 1 месяц в каком-нибудь департаменте?

frostyland
Сообщения: 38
Зарегистрирован: 05 июл 2007, 13:24

Re: Помогите со сложным [для меня] запросом

Сообщение frostyland » 09 сен 2008, 07:53

WildSery писал(а):Вообще, мне сперва показалось, по таблице PDN тоже суммировать надо, а из твоего решения с удивлением увидел, что 1-к-1 связь.
Или это-таки неправильно, и суммировать всё же надо? 2 записи в PDN могут быть за 1 месяц в каком-нибудь департаменте?
Нет, смысл ПДН в том, что это "порог доходности направления [в данном месяце]" и, соответственно, ежемесячно для каждого направления есть только одна запись. Но почему же это связь 1-к-1? Для каждого DEPT в PDN есть столько записей, сколько существует месяцев. И каждый месяц мы вводим в PDN столько записей, сколько есть направлений. Всяко "ноги-на-ноги" (как перефразировал один знакомый Много-Ко-Многим).

Ответить