Проблема с запросом

← →
jack128 (2003-12-20 15:25) [0]

День добрый.
Есть таблица common_arhive_data со следующими полями
arhive_date date, // дата архивной информации
param_type smallint not null, //тип архивной информации
param_value DOUBLE PRECISION // значение

и есть такой запрос

select arh_heat.arhive_date arhive_date,
arh_heat.param_value heat,
arh_energy.param_value electric_energy
from common_arhive_data arh_heat,
common_arhive_data arh_energy
where
arh_heat.arhive_date = arh_energy.arhive_date and
arh_heat.param_type = 0 and // если param_type = 0 то этот параметр - тепло
arh_energy.param_type = 1 // если param_type = 0 то этот параметр - электро энергия

проблема в том, что если за дату нет данных, например по эл/ энергии, то запрос не выведет тело за эту дату.
Можно ли как нибудь без ХП модифицировать этот запрос, что при отсутствии данных за дату в соответств. столбце NULL выводился?

← →
Nikolay M. (2003-12-20 17:20) [1]

LEFT JOIN о чем-нибудь говорит?
И к чему вообще джойнить таблицу саму на себя? Почему нельзя вывести param_type отдельным столбцом и на основании его значения судить о том, что, собственно, находится в param_value?

← →
jack128 (2003-12-20 17:54) [2]

> Почему нельзя вывести param_type отдельным столбцом и на
> основании его значения судить о том, что, собственно, находится
> в param_value?

Кому вывести? Юзеру? Он не поймет. Или имеется в виду самому анализировать результаты запроса и выводить их в grid в нужном формате? Гм. Это удовольствие ниже среднего..

Насчет left join"a -знал, но видимо не совсем понимаю его смысл.

почему при таком запросе

select arh_heat.arhive_date arhive_date,
arh_heat.param_value heat,
arh_energy.param_value electric_energy
from common_arhive_data arh_heat
left join common_arhive_data arh_energy
on arh_heat.arhive_date = arh_energy.arhive_date and
arh_heat.param_type = 0 and
arh_energy.param_type = 1

и исходных данных
arhive_date param_type param_value
20/11/2003 1 2.00

на выходе такая таблица
arhive_date heat electric_energy
20/11/2003 2.00 null

?

← →
kaif (2003-12-20 19:38) [3]

Мое мнение такое. Неправильно извращаться с left outer join в данном случае, если данные имеются почти на каждый день. Тогда лучше обеспечить гарантированное наличие всех данных со значениями 0 (только не NULL!). Иначе потом еще будет ряд проблем при попытке построить отчеты с группировками и агрегатами.
То решение, что ты ищешь, можно себе позволить только в случае, если данных крайне мало и гораздо больше пустых промежутков (отсутствует запись о параметре на дату). Но даже в этом случае придется сделать union двух запросов c left outer join. Один из них вернет все heat и некоторые electric_energy (другие null)
другой вернет все electric_energy и некоторые heat (другие null),
это все объединится с помощью union, который отсеет дубликаты (если только не писать union all).

← →
kaif (2003-12-20 19:45) [4]

select arh_heat.arhive_date arhive_date,
arh_heat.param_value heat,
arh_energy.param_value electric_energy
from common_arhive_data arh_heat
left join common_arhive_data arh_energy
on arh_heat.arhive_date = arh_energy.arhive_date and
arh_heat.param_type = 0 and
arh_energy.param_type = 1
union
select arh_heat.arhive_date arhive_date,
arh_heat.param_value heat,
arh_energy.param_value electric_energy
from common_arhive_data arh_energy
left join common_arhive_data arh_heat
on arh_heat.arhive_date = arh_energy.arhive_date and
arh_heat.param_type = 0 and
arh_energy.param_type = 1

Кстати, для красоты можно сделать один left join, а другой right join. Я правда такое не пробовал.
Вообще Left join означает, что выберутся все имеющиеся записи из первой таблицы, даже если условие объединения не выполняется. Просто для строк первой таблицы, для которых условие объединения не выполняется, значения полей второй таблицы будут представлены в наборе как NULL (неопределенные).
Я лично очень не люблю появления всяких NULL в местах, где очень вероятно применение агрегатных функций типа SUM() и других. А здесь я вижу перспективу такого развития задачи. Что ты будешь делать, когда потребуется просуммировать тепло и энергию за месяц? Ты получишь NULL,NULL. Как пить дать.

← →
jack128 (2003-12-20 20:11) [5]

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

← →
jack128 (2003-12-20 21:05) [6]

> kaif ©
Ваш запрос не совсем коректно работает, но вот что я надумал

select arh_heat.arhive_date,
arh_heat.param_value heat,
arh_energy.param_value electric_energy
from common_arhive_data arh_heat
left join common_arhive_data arh_energy
on arh_heat.arhive_date = arh_energy.arhive_date and
arh_energy.param_type = 1
where arh_heat.param_type = 0
union
select arh_energy.arhive_date,
arh_heat.param_value,
arh_energy.param_value electric_energy
from common_arhive_data arh_energy
left join common_arhive_data arh_heat
on arh_heat.arhive_date = arh_energy.arhive_date and
arh_heat.param_type = 0
where arh_energy.param_type = 1
Правда как с производительностью у этого запроса будет не совсем ясно..

← →
Nikolay M. (2003-12-20 23:54) [7]

> jack128 © (20.12.03 21:05) [6]
> Правда как с производительностью у этого запроса будет не
> совсем ясно..

Попробуй на больших данных еще прогнать такой запрос, хотя, имхо, он далеко не оптимален, даже, скорее, наоборот - тормоз :) Но работать должен, сам, ессесно, не проверял.

SELECT
arh_heat.arhive_date,
(SELECT param_value AS heat
FROM common_arhive_data
WHERE arh_heat.arhive_date = arhive_date
AND param_type = 0),
(SELECT param_value AS energy
FROM common_arhive_data
WHERE arh_heat.arhive_date = arhive_date
AND param_type = 1)
FROM
common_arhive_data arh_heat

← →
jack128 (2003-12-21 01:13) [8]

Работает, если добавить distinct

SELECT distinct
arh.arhive_date,
(SELECT arh_heat.param_value
FROM common_arhive_data arh_heat
WHERE arh.arhive_date = arh_heat.arhive_date
AND arh_heat.param_type = 0) heat,
(SELECT arh_energy.param_value
FROM common_arhive_data arh_energy
WHERE arh.arhive_date = arh_energy.arhive_date
AND arh_energy.param_type = 1) energy
FROM
common_arhive_data arh

но в таком случае ошибка
Invalid token.
invalid request BLR at offset 162.
context already in use (BLR error). к чему бы это?..

← →
kaif (2003-12-21 03:45) [9]

А почему такая организация данных? Неужели список возможных параметров настолько неопределен, что нельзя сделать нормальную таблицу типа:
create table allinfo
(energy double precision,
heat double precision,
и так далее...) ?
Зачем весь это изврат с "доменизацией" параметров?
я уверен, что проще (если что...) добавить еще одну колонку командой alter table ... add any_new_param double precision, чем хранить все в таком
"универсальном", но убивающем все преимущества нормальной реляционной таблицы, виде.
К тому же тот способ хранения, что ты используешь, обрекает все параметры иметь одинаковый тип (например, double precision). В некоторых параметрах оптимальнее может быть хранить в виде integer. К тому же, параметры в основном поступают группами (энергия+мощность+реактивная мощность+фаза+...), то экономичнее хранить их строкой, чем вертикально (с датами и видами).
К тому же еще раз настаиваю, что лучше избежать значений NULL и иметь везде 0. Иначе будут проблемы. Я уже жду вопрос на форум:
"как мне сделать, чтобы при суммировании значения NULL суммировались, как 0" и кучу ответов типа "используй udf, используй хп, а почему вообще ты так сделал? и т.д."

← →
kaif (2003-12-21 03:51) [10]

Пусть 90% твоих полей будут нулями. Зато тебе не придется объединять минимум 5 раз таблицу 5-кратной длины, если тебе нужно вывести 5 параметров одновременно. Лучше потерять место на диске, чем скорость... К тому же не факт, что при таком хранении (дата, вид, параметр) вместо
(дата, параметр1, параметр2, параметр3, параметр4, параметр5)
вообще будет выигрыш места на диске. Ты еще место под индекс не забудь...
В общем, у меня интуитивное ощущение, что это изначально кривой путь. Не в запросах тут дело, а в самой организации данных.

← →
kaif (2003-12-21 03:55) [11]

И еще учти, что smallint работает медленнее integer. Почитай статьи об этих двух типах данных. На процессорах intel рекомендуют integer вместо smallint. Хотя вопрос спорный, но я уже не раз сталкивался с такой точкой зрения, что integer оптимальнее.

← →
jack128 (2003-12-21 11:07) [12]

← →
Nikolay M. (2003-12-21 11:46) [13]

← →
Anatoly Podgoretsky (2003-12-21 11:53) [14]

← →
jack128 (2003-12-21 14:16) [15]

> Дистинкт убери, поставь GROUP BY
гм. Я конечно могу группировать по дате, а в качестве агрегатов для тепла и энергии поставить min (или max - без разницы) - но это уже полное извращение какое то.

Я не пониманию, почему distinct не работает там, где он должен работать без вопросов..

Проблема с запросом Найти похожие ветки