Запросы, планы, оптимизация запросов, ...
Модераторы: kdv, CyberMax
-
bender
- Сообщения: 37
- Зарегистрирован: 24 фев 2005, 15:41
Сообщение
bender » 27 ноя 2007, 16:00
Такая вот проблема, не могу составить запрос, точнее не могу добиться требуемого результата от такого вот запроса:
Код: Выделить всё
select
(max(C.Q1) - sum(D.Q1)) as Q1
from DAY D
join CUR C on C.HDR_ID = D.HDR_ID
where (D.HDR_ID=283) and (D.CURDAY >= '11.10.2007')
Видимо после выборки данных из таблиц D и C в коде sum(D.Q1) происходит суммирование поля Q1 не только из таблицы D, но и из C. Не понимаю почему это происходит, ведь я точно указал столбец в виде
алиас таблицы.поле который надо суммировать
План запроса выглядит следующим образом:
Код: Выделить всё
PLAN JOIN (D INDEX (RDB$FOREIGN31),C INDEX (RDB$FOREIGN35))
где RDB$FOREIGN31 - это столбец D.HDR_ID, а RDB$FOREIGN35 - это столбец C.HDR_ID
Помогите пожалуйста, работа горит, бъюсь не первый день над этим запросом.
Заранее спасибо.
-
belov-evgenii
- Сообщения: 52
- Зарегистрирован: 28 сен 2007, 10:19
Сообщение
belov-evgenii » 27 ноя 2007, 17:50
А ты посмотри что он у тебя без агрегратов выводит. У тебя ведь наверное связь один ко многим.
-
bender
- Сообщения: 37
- Зарегистрирован: 24 фев 2005, 15:41
Сообщение
bender » 27 ноя 2007, 18:14
В смысле без агрегатов, мне нужно из максимального значения C.Q1 вычесть сумму D.Q1, возможно нужно изменить на max(distinct C.Q1), но это смысла не меняет, более того следующий запрос:
Код: Выделить всё
select
(sum(D.Q1)) as Q1
from DAY D
join CUR C on C.HDR_ID = D.HDR_ID
where (D.HDR_ID=283) and (D.CURDAY >= '11.10.2007')
Суммирует значения D.Q1 и приплюсовывает к ним одно значение C.Q1
Получается так, как будто в результатом выборки является таблица у которой два столбца HDR_ID и Q1 в которые слиты данные из таблиц C и D, и то что я указал алиас в агрегате никак не влияет на разделение данных.
PS работаю под Firebird 1.x
-
belov-evgenii
- Сообщения: 52
- Зарегистрирован: 28 сен 2007, 10:19
Сообщение
belov-evgenii » 27 ноя 2007, 18:32
Блин, выполни запрос без агрегатов:
Код: Выделить всё
select c.hdr_id as c_hdrid, d.hdr_id as d_hdrid,
C.Q1 as c_q1, D.Q1 as d_q1
from DAY D
join CUR C on C.HDR_ID = D.HDR_ID
where (D.HDR_ID=283) and (D.CURDAY >= '11.10.2007')
и посмотри. У тебя ж наверное не один-к-одному получается сджойнивать. Например - c.hdr_id = 283 встречается более 1 раза, в результате сджойненная выборка получается больше.
-
bender
- Сообщения: 37
- Зарегистрирован: 24 фев 2005, 15:41
Сообщение
bender » 27 ноя 2007, 20:21
Воистину правда, меня с толку сбил тот факт, что сумма получалась больше на поле C.Q1. Спасибо, завтра на работе окончательно проверю.
Получилось в таком виде:
Код: Выделить всё
select
(sum(D.Q1)) as Q1
from DAY D
join CUR C on (C.HDR_ID = D.HDR_ID) and (C.CUR_ID = (select max(C2.CUR_ID) from CUR C2 where (C2.HDR_ID = C.HDR_ID)))
where (D.HDR_ID=283) and (D.CURDAY >= '11.10.2007')
Знаю, что нельзя смешивать неявные и явные join'ы, но пока не получается написать этот запрос целиком в явном виде. Буду благодарен если поможете это сделать.
-
Tonal
- Сообщения: 104
- Зарегистрирован: 30 сен 2007, 13:42
Сообщение
Tonal » 28 ноя 2007, 07:53
bender писал(а):... мне нужно из максимального значения C.Q1 вычесть сумму D.Q1...
Максимум C.Q1 берётся по всей таблице, или по какому то подмножеству?
Сумму D.Q1 по всей таблице?
Если, например максимум по связанным, а сумма по всем, то так:
Код: Выделить всё
select
(max(C.Q1) - (select sum(D1.Q1) from DAY D1)) as Q1
from DAY D
join CUR C on C.HDR_ID = D.HDR_ID
where (D.HDR_ID=283) and (D.CURDAY >= '11.10.2007')
-
bender
- Сообщения: 37
- Зарегистрирован: 24 фев 2005, 15:41
Сообщение
bender » 29 ноя 2007, 12:05
Немного перестроил запрос:
Код: Выделить всё
select
(max(C.Q1) - sum(D.Q1)) as Q1
from CUR C
left join DAY D1 on (D.HDR_ID = C.HDR_ID) and (D.CURDAY > '29.11.2007')
where (C.HDR_ID = 29) and (C.CUR_ID = (select max(C2.CUR_ID) from CUR C2 where (C2.HDR_ID = C.HDR_ID)))
без агрегатов этот запрос возвращает значения C.Q1 = 10, а D.Q1 = NULL, с агрегатами разница почему-то равна NULL, а мне нужно 10, как такое может быть?
-
stix-s
- Заслуженный разработчик
- Сообщения: 557
- Зарегистрирован: 13 дек 2005, 11:52
Сообщение
stix-s » 29 ноя 2007, 12:42
bender писал(а):Немного перестроил запрос:
Код: Выделить всё
select
(max(C.Q1) - sum(D.Q1)) as Q1
from CUR C
left join DAY D1 on (D.HDR_ID = C.HDR_ID) and (D.CURDAY > '29.11.2007')
where (C.HDR_ID = 29) and (C.CUR_ID = (select max(C2.CUR_ID) from CUR C2 where (C2.HDR_ID = C.HDR_ID)))
без агрегатов этот запрос возвращает значения C.Q1 = 10, а D.Q1 = NULL, с агрегатами разница почему-то равна NULL, а мне нужно 10, как такое может быть?
а если из 10 вычесть null, как думаешь, что получится?
-
bender
- Сообщения: 37
- Зарегистрирован: 24 фев 2005, 15:41
Сообщение
bender » 29 ноя 2007, 13:32
Если честно, то думал, что получится 10. Так как можно выйти из этой ситуации, как внутри запроса NULL преобразовать к 0?
-
stix-s
- Заслуженный разработчик
- Сообщения: 557
- Зарегистрирован: 13 дек 2005, 11:52
Сообщение
stix-s » 29 ноя 2007, 13:47
bender писал(а):Если честно, то думал, что получится 10. Так как можно выйти из этой ситуации, как внутри запроса NULL преобразовать к 0?
1 - а ты вот проверь
что есть null? это НЕОПРЕДЕЛЕННОСТЬ и с какого перепугу сервер должет думать, что это 0?
2 - см COALESCE
и ведь ты на этом форуме зарегился 2 года назад
-
bender
- Сообщения: 37
- Зарегистрирован: 24 фев 2005, 15:41
Сообщение
bender » 29 ноя 2007, 14:56
1 - Вообще по аналогии с C++ я думал что null будет преобразован к типу первого операнда, я именно к integer и в результате получится 10 - 0 = 10;
2- COALESCE не годится у меня Firebird 1.x, перестановка сервера на 1.5 не годится как выход из положения.
Я 2 года за базы данных не садился
-
stix-s
- Заслуженный разработчик
- Сообщения: 557
- Зарегистрирован: 13 дек 2005, 11:52
Сообщение
stix-s » 29 ноя 2007, 15:13
bender писал(а):1 - Вообще по аналогии с C++ я думал что null будет преобразован к типу первого операнда, я именно к integer и в результате получится 10 - 0 = 10;
2- COALESCE не годится у меня Firebird 1.x, перестановка сервера на 1.5 не годится как выход из положения.
Я 2 года за базы данных не садился
мдя, интересные фичи в С++
что мешает версию сервера повысить? до 2.0
а хоть CASE - то в FB 1 есть?
-
bender
- Сообщения: 37
- Зарегистрирован: 24 фев 2005, 15:41
Сообщение
bender » 29 ноя 2007, 15:34
CASE есть, а версию сервера повышать не возможно из-за того, что придется проверить все SQL запросы на корректное выполнение, даже если я в них уверен на 99%, а на это нет времени, начальство торопит
-
stix-s
- Заслуженный разработчик
- Сообщения: 557
- Зарегистрирован: 13 дек 2005, 11:52
Сообщение
stix-s » 29 ноя 2007, 15:38
bender писал(а):CASE есть, а версию сервера повышать не возможно из-за того, что придется проверить все SQL запросы на корректное выполнение, даже если я в них уверен на 99%, а на это нет времени, начальство торопит
рано или поздно - а переходить придется
ну так пользуй CASE или вообще сделай все в процедуре
-
bender
- Сообщения: 37
- Зарегистрирован: 24 фев 2005, 15:41
Сообщение
bender » 29 ноя 2007, 15:52
Все перегрелся, думаю про одно пишу про другое CASE с CAST перепутал, нету в 1.х CASE'а
Процедуру ты имеешь в виду хранимую процедуру? Если да, то как ее воткнуть в запрос, таким образом не получается:
Код: Выделить всё
select
(max(C.TAU1) - (select OUT_VAL from Null2Int(sum(D1.TAU1)))) as TAU1 ...
-
stix-s
- Заслуженный разработчик
- Сообщения: 557
- Зарегистрирован: 13 дек 2005, 11:52
Сообщение
stix-s » 29 ноя 2007, 19:11
bender писал(а):Все перегрелся, думаю про одно пишу про другое CASE с CAST перепутал, нету в 1.х CASE'а
Процедуру ты имеешь в виду хранимую процедуру? Если да, то как ее воткнуть в запрос, таким образом не получается:
Код: Выделить всё
select
(max(C.TAU1) - (select OUT_VAL from Null2Int(sum(D1.TAU1)))) as TAU1 ...
да, ХП
плин, не процедура в запросе, а запросы в процедуре
процедуре на вход подаешь свои ограничения - получаешь свои агрегаты
Код: Выделить всё
select OUT1,OUT2,OUT.... from MY_procedure (IN1,IN2,....)
Это если ты несколько строк получаешь
ЗЫ
Что-то с трудом мне верится, что в FB1.0 нет аналога CASE для данного случая
-
bender
- Сообщения: 37
- Зарегистрирован: 24 фев 2005, 15:41
Сообщение
bender » 30 ноя 2007, 10:36
Все, вроде победил, подключил UDF функцию i64nvl, аналог COALESCE
Большое спасибо, за помощь!