Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Помогите с запросом  [new]
AlekZ
Member

Откуда:
Сообщений: 15
Есть парсер, он парсит файлы в БД. Файлы бывают разные- со скана, выгрузка с БД, самостоятельно ведомая БД. Данные в них одинаковые, но называются по разному…Поэтому пришлось использовать костыль в виде таблицы Замены (Рокеровки -ну я так это назвал).
Это краткое вступление-почему так коряво то все.
Теперь собственно сама проблема:
Таблица Рокеровка
IDOLD_VALNEW_VALVIDCATEGORY
1TV-232СПЕКТ 5ТВТехника
2СПЕКТ 5СПЕКТР 5ТВТехника
3Бумага СнегСнегурочка А4БумагаОфисные товары


Основная таблица(ALFA):
ID ID_NEW_NAME DAT POSIT SUMMA
1 3 18.07.20 5 2500
2 3 19.07.20 3 1500
3 3 20.07.20 2 1000


Что собственно надо, нужен отчет который выводит итоговую статистику по продажам, те позиции которые не продавались-должны показывать нули (вот с этим то и проблемы, как это сделать?).

Тот запрос который сейчас есть:
select
  sum( alfa.posit ) sum_of_posit,
  sum( alfa.summa ) sum_of_summa,
  alfa.dat, 
  rokerovka.category,
  rokerovka.vid 
from rokerovka
  inner join alfa on (rokerovka.id = alfa.id_new_name) 
where (alfa.dat between :d1 and :d2)
group by alfa.dat,rokerovka.category
order by alfa.dat


Он не выводит те "Виды"-по которым нулевые продажи. Ситуация еще немного осложняется тем что БД-FireBird (и она не может селект из селекта,ну и руки у меня кривые).
Модератор: Пользуйтесь тегами!


Сообщение было отредактировано: 20 июл 20, 10:00
20 июл 20, 09:48    [22170224]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
alekcvp
Member

Откуда:
Сообщений: 2257
Если нужны нули, то может LEFT JOIN вместо INNER?

P.S: Firebird может селект из селекта...

Сообщение было отредактировано: 20 июл 20, 09:59
20 июл 20, 10:01    [22170232]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
AlekZ
Member

Откуда:
Сообщений: 15
alekcvp, пробовал, результат тот же, только выполняется минуту вместо нескольких секунд. толи не в том порядке таблицы объединяю... пробовал и Right join. Тоже самое.
п.с. утверждать не буду. ФайрБирд 2.5 - не получилось под IBExpert выполнить такой запрос(Селект в селекте).

Сообщение было отредактировано: 20 июл 20, 10:03
20 июл 20, 10:04    [22170235]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
brick08
Member

Откуда:
Сообщений: 67
AlekZ,
or alfa.dat is null
20 июл 20, 10:39    [22170251]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
AlekZ
Member

Откуда:
Сообщений: 15
Ну вот например запрос тех позиций которые по нулям. Но как его присоединить к первому запросу...Да еще и с суммами по нулям, и сгрупировать по "ВИДу"

select VID from rokerovka2 where rokerovka2.id not in(
select alfa2.id_new_name
from alfa2
where (alfa2.dat between :d1 and :d2)
) group by VID
20 июл 20, 10:40    [22170252]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
brick08
Member

Откуда:
Сообщений: 67
AlekZ,
select
  sum( alfa.posit ) sum_of_posit,
  sum( alfa.summa ) sum_of_summa,
  alfa.dat, 
  rokerovka.category,
  rokerovka.vid 
from rokerovka
  left join alfa on (rokerovka.id = alfa.id_new_name) 
where (alfa.dat between :d1 and :d2)
  or alfa.dat is null
group by alfa.dat,rokerovka.category
order by alfa.dat
20 июл 20, 10:45    [22170259]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
alekcvp
Member

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

Ну как-то так:
select a.dat, r.category, r.vid, sum( a.posit ) sum_posit, sum( a.summa ) sum_summa from rokerovka r
  left join alfa a on a.id_new_name = r.id
  where a.dat between :d1 and :d2 or a.dat is null
  group by a.dat,  r.category, r.vid
  order by a.dat

DAT	CATEGORY	VID	SUM_POSIT	SUM_SUMMA
Техника ТВ
19.07.2020 Офисные товары Бумага 3 1500
20.07.2020 Офисные товары Бумага 2 1000

Если нужны именно '0' вместо 'null' то можно добавить coalesce().
20 июл 20, 10:49    [22170262]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
AlekZ
Member

Откуда:
Сообщений: 15
brick08 , alekcvp - так то да, но на продакшене получается ересь. Типо ТВ-ноль, потом дальше по списку видишь за этот период Тв-10 штук, например. Щас, дайте время сам переварю что пошло не так-отпишу. на тестовой базе(как в первом посте) все ок, там нет такого наполнения просто.
В любом случае спасибо, что указали как надо писать сей запрос.
20 июл 20, 11:03    [22170272]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
alekcvp
Member

Откуда:
Сообщений: 2257
AlekZ
Щас, дайте время сам переварю что пошло не так

Чтобы видеть "0" по датам необходимо откуда-то взять эту дату, чтобы потом к ней присоединять продажи...

Т.е. нужна будет ХП типа:
create or alter procedure DATE_GEN (
    DFROM date not null,
    DTO date not null)
returns (
    CDATE date)
as
begin
  cdate = dfrom;
  while (cdate <= dto) do
  begin
    suspend;
    cdate = cdate + 1;
  end
end


Сообщение было отредактировано: 20 июл 20, 11:47
20 июл 20, 11:46    [22170307]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
alekcvp
Member

Откуда:
Сообщений: 2257
Итоговый запрос:
select g.cdate, r.category, r.vid, sum( a.posit ) sum_posit, sum( a.summa ) sum_summa
  from rokerovka r right join date_gen(:d1, :d2) g on 1=1
  left join alfa a on a.id_new_name = r.id and a.dat = g.cdate
  group by g.cdate, r.category, r.vid
  order by g.cdate

CDATECATEGORYVIDSUM_POSITSUM_SUMMA
19.07.2020Офисные товарыБумага31500
19.07.2020ТехникаТВ
20.07.2020Офисные товарыБумага21000
20.07.2020ТехникаТВ


Сообщение было отредактировано: 20 июл 20, 11:52
20 июл 20, 11:54    [22170313]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 1010
Свои пять копеек внесу. Зачем отображать нулевые продажи? Представьте у Вас список товаров несколько тысяч. Многих в наличии на складе нет давно. Зачем тащить эти нули? Если уж есть какие-то продажи конкретного товара за период и нужно отобразить некие нули, то Ваш запрос "простоват" для этого. И возможно лучше это сделать на клиенте. Реально Вы насилуете сервер, пытаясь получить несуществующие данные.
20 июл 20, 12:00    [22170315]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
alekcvp
Member

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

Мне кажется этот вопрос надо задавать преподавателю
20 июл 20, 12:08    [22170320]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
WildSery
Member

Откуда: да, оттуда.
Сообщений: 18722
where a.dat between :d1 and :d2 or a.dat is null
Не надо так писать.
20 июл 20, 12:37    [22170342]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
alekcvp
Member

Откуда:
Сообщений: 2257
WildSery
where a.dat between :d1 and :d2 or a.dat is null
Не надо так писать.

А как надо?
20 июл 20, 12:55    [22170355]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
AlekZ
Member

Откуда:
Сообщений: 15
Вот так, наверное
select rokerovka2.category,rokerovka2.vid,alfa2.posit from rokerovka2
inner join alfa2 on (rokerovka2.id = alfa2.id_new_name)
where (alfa2.dat between :d1 and :d2)
group by rokerovka2.category,rokerovka2.vid,alfa2.posit
UNION
select r2.CATEGORY,r2.VID,alfa2.posit from rokerovka2 r2
left join alfa2 on (r2.id = alfa2.id_new_name)
where r2.id not in(select a2.id_new_name
from alfa2 a2
where (a2.dat between :d1 and :d2)
) group by r2.CATEGORY,r2.VID,alfa2.posit


Если кто изящнее знает-напишите. А так вопрос решен. alfa2-это дубль alfa, тоже самое с рокеровкой, чтобы прод не мучить.

Сообщение было отредактировано: 20 июл 20, 13:39
20 июл 20, 13:41    [22170383]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 1010
alekcvp
KreatorXXI,

Мне кажется этот вопрос надо задавать преподавателю


Надо тогда посмотреть задание. В голове у неофита застрянут неправильные рекомендации. Потом их оттуда не вышибешь ничем.
20 июл 20, 13:43    [22170387]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
WildSery
Member

Откуда: да, оттуда.
Сообщений: 18722
alekcvp
А как надо?
В условие соединения выносить, а не в where костыли ставить.
20 июл 20, 13:48    [22170392]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
AlekZ
Member

Откуда:
Сообщений: 15
У неофита в голове уже помойка, Вы лучше мне растолкуйте куда чего переносить.
А то на проде уже 5 минут запрос крутиться, да не выкрутиться :)
20 июл 20, 13:51    [22170396]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
alekcvp
Member

Откуда:
Сообщений: 2257
AlekZ
Вот так, наверное

И в чём смысл возвращаемого набора данных?

CATEGORY VIDPOSIT
Офисные товары Бумага2
Офисные товары Бумага3
ТехникаТВ

AlekZ
Вы лучше мне растолкуйте куда чего переносить.

Вот так не работает? (2 сообщения) 22170307

Сообщение было отредактировано: 20 июл 20, 13:52
20 июл 20, 13:53    [22170400]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 1010
AlekZ
Вот так, наверное
select rokerovka2.category,rokerovka2.vid,alfa2.posit from rokerovka2
inner join alfa2 on (rokerovka2.id = alfa2.id_new_name)
where (alfa2.dat between :d1 and :d2)
group by rokerovka2.category,rokerovka2.vid,alfa2.posit
UNION
select r2.CATEGORY,r2.VID,alfa2.posit from rokerovka2 r2
left join alfa2 on (r2.id = alfa2.id_new_name)
where r2.id not in(select a2.id_new_name
from alfa2 a2
where (a2.dat between :d1 and :d2)
) group by r2.CATEGORY,r2.VID,alfa2.posit


Если кто изящнее знает-напишите. А так вопрос решен. alfa2-это дубль alfa, тоже самое с рокеровкой, чтобы прод не мучить.


Второй запрос (после Union) вообще дурно попахивает. Вы посмотрите "Анализ плана" и "Анализ производительности".
20 июл 20, 13:58    [22170407]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
AlekZ
Member

Откуда:
Сообщений: 15
alekcvp,
хранимку пока не создавал. В ФайрБирде с ними не работал, надо прогуглить куда ее тут сувать.
Меня напрягло то что на живой базе с Вашим запросом лезут и нули по некоторым позициям, по которым есть продажи. И не совсем своими мозгами понимаю как срабатывает условие "нулевой даты" (нет записи-значит DATE нулевая?).

А смысл-вернуть те строки по которым ноль продаж. У Вас там опечатка получилась в таблице-там в POSIT null у техники.


KreatorXXI,
10 минут выполняется...чуть модифицировал запустил еще раз. Жду :)

PLAN (ALFA NATURAL)
PLAN (ALFA NATURAL)
PLAN SORT (JOIN (ALFA NATURAL, ROKEROVKA INDEX (PK_ROKEROVKA)))
PLAN SORT (JOIN (R2 NATURAL, ALFA NATURAL))

-по мне это ругань чистой воды.
Non Indexed Reads ALFA - 977 304 192

Сообщение было отредактировано: 20 июл 20, 14:13
20 июл 20, 14:07    [22170418]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
alekcvp
Member

Откуда:
Сообщений: 2257
AlekZ
alekcvp,
Меня напрягло то что на живой базе с Вашим запросом лезут и нули по некоторым позициям, по которым есть продажи.

Предыдущий запрос был не правильный, я переделал :)

P.S: Тестовые базы уже не в моде? Эксперименты на продакшене? О_о
AlekZ
хранимку пока не создавал. В ФайрБирде с ними не работал, надо прогуглить куда ее тут сувать.

Рекомендую IBExpert 1883929

Сообщение было отредактировано: 20 июл 20, 14:15
20 июл 20, 14:18    [22170430]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 1010
AlekZ

PLAN (ALFA NATURAL)
PLAN (ALFA NATURAL)
PLAN SORT (JOIN (ALFA NATURAL, ROKEROVKA INDEX (PK_ROKEROVKA)))
PLAN SORT (JOIN (R2 NATURAL, ALFA NATURAL))

-по мне это ругань чистой воды.
Non Indexed Reads ALFA - 977 304 192


Может, действительно, книжки какие для начала почитать? У Вас план по "натуралу". Это значит, что не используются никакие индексы. Плюс ещё идёт перемножение таблиц. 10 минут - это ещё хорошо при таком раскладе. Индексы по датам хоть есть?

Я бы оставил в конце концов один из первых Ваших запросов. Таблица alfа должна отрабатываться по индексу с датой, а вторая таблица (боюсь её название даже писать) должна быть связана по inner join (inner можно не писать). Нули добивайте на клиенте.
20 июл 20, 14:27    [22170436]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
AlekZ
Member

Откуда:
Сообщений: 15
alekcvp, я как раз в ИБЭксперте и сижу. Для ОгоньПтицы по-моему других админок то и нету :)
Ну ладно, я запустил Ваш "Итоговый запрос:" -не знаю , то или не то. Но пока 5 минут и тухляк (все еще выполняется) :)
Прод такой-его не жалко. Его использую я и еще 1 человек, для которого весь этот огород и делался.
Был бы прод который нельзя трогать-была бы нормальная инфраструктура/норм зарплата/ и норм специалист который такие вопросы по форумам не задает (тут я ушел плакать в дальний угол).
20 июл 20, 14:30    [22170439]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
AlekZ
Member

Откуда:
Сообщений: 15
KreatorXXI, может и книги почитать. А зачем индексация по датам? Какие плюсы?
Ткните на дельную книгу или статью. Я не ДБА (когда-то хотел им стать), но у нас не нужны ученики. так прочитаю-но через 2 года забуду.
и когда поле дата было ключевым? Или индекс и ключ не одно и тоже?

Сообщение было отредактировано: 20 июл 20, 14:31
20 июл 20, 14:32    [22170444]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Firebird, InterBase Ответить