запрос SQL

← →
Rel_ (2003-08-13 09:16) [0]

Мастера, помогите с запросом.

есть след набор данных :

Month Code Value
1 0 2
1 0 6
1 1 7
2 0 8
3 1 1
3 1 10
3 0 3

Запрос должен выводить суммы (Value) с группировкой по месяцам, деля их по коду (Code). С кодом (0) - сумма в один столбец, с кодом (1) - сумма в другой столбец.

Результат запроса такой:

Month SumCode0 SumCode1
1 2+6=8 7
2 8 0
3 3 1+10=11

Мой запрос выглядел след. образом.

select Month, Sum(Value) as SumCode0, 0 as SumCode1
from anyTable
where Code =0
group by Month

union

select Month, 0 as SumCode0, Sum(Value) as SumCode1
from anyTable
where Code =1
group by Month

Проверить его возможности нет,
но по-моему энто неправильно, и результат получится след:

Month SumCode0 SumCode1
1 2+6=8 0
2 8 0
3 3 0
1 0 7
3 0 0
4 0 1+10=11

Отсоветуйте что-нибудь

← →
DenK_vrtz © (2003-08-13 09:21) [1]

СУБД какая?

← →
HSolo © (2003-08-13 09:22) [2]

Так и будет. Как быть - зависит от СУБД

← →
Rel_ (2003-08-13 09:39) [3]

использую ORACLE

Я ещё попробовал так

select t1.Month, t1.SumCode0, t2.SumCode1 from

(
SELECT Month,
SUM (Value) as SumCode0
FROM anyTable
WHERE Code=0
GROUP BY Month
) t1,

(
SELECT Month,
SUM (Value) as SumCode1
FROM anyTable
WHERE Code=1
GROUP BY Month
) t2

where t1.Month=t2.Month(+)

ORDER BY Month

но здесь тоже кроется ошибка , т.к. используется только LeftJoin,
а мне необходимо полное объединение, т.е.

t1 + t2 = T
Month Value Month Value Month t1.Value t2.Value
1 нечто 3 1 . .
2 . 4 2
5 . 3
6 и.т.д. и.т.д. 4
5
6 и т.д.

← →
DenK_vrtz © (2003-08-13 09:39) [4]

select Month, decode(code,0,sumValue,0) SumCode0, decode(code,1,sumValue,0) SumCode1
from
( select Month, Code, sum(Value) sumValue
from anyTable
group by Month, Code
)

← →
Rel_ (2003-08-13 09:43) [5]

Всё съехало (наверное ничего не понятно). Проще, что-то по аналогии с моим 2-м запросом но с использовнаием полного объединения таблиц (всё, что совпадает в обоих таблицах + всё, что не совпадает в первой таблице + всё, что не совпадает во второй таблице). Есть ли на Oracle аналог типа Both Join.

← →
DenK_vrtz © (2003-08-13 09:45) [6]

Rel_ (13.08.03 09:43), а запрос DenK_vrtz © (13.08.03 09:39) не работает?

← →
Rel_ (2003-08-13 09:45) [7]

>>>to DenK_vrtz

Во круто. ща специально навояю табличку и попробую!!! Ну спасибо!!!

← →
Rel_ (2003-08-13 09:49) [8]

>>DenK_vrtz ©

Почему не работает??? А можно ли это хозяйство преобразовать так:

разделить весь набор данных не на code=0 и code=1 , а на code=10 (например) и code <> 10. Т.е. в первый столбец все записи с кодом 10, а другой все остальные.

← →
DenK_vrtz © (2003-08-13 09:55) [9]

Rel_ (13.08.03 09:49), мой запрос маленько не корректен, так как получится

Month SumCode0 SumCode1
1 8 0
1 0 7

вот еще вариант (прототип твоего Both Join :-))

select mon.Month, t1.SumCode0, t2.SumCode1 from

(
SELECT Month,
SUM (Value) as SumCode0
FROM anyTable
WHERE Code=0
GROUP BY Month
) t1,

(
SELECT Month,
SUM (Value) as SumCode1
FROM anyTable
WHERE Code=1
GROUP BY Month
) t2,

(
SELECT Month
FROM anyTable
GROUP BY Month
) mon

where mon.Month=t1.Month(+)
and mon.Month=t2.Month(+)

ORDER BY mon.Month

← →
DenK_vrtz © (2003-08-13 09:58) [10]

>>разделить весь набор данных не на code=0 и code=1 , а на >>code=10 (например) и code <> 10. Т.е. в первый столбец все >>записи с кодом 10, а другой все остальные

исходя из второго запроса DenK_vrtz © (13.08.03 09:55) можно. как? сам понимаешь :-)

← →
DenK_vrtz © (2003-08-13 10:10) [11]

или вот
select Month, sum(SumCode0) SumCode0, sum(SumCode1) SumCode1
from
(select Month, decode(code,0,sumValue,0) SumCode0, decode (code,1,sumValue,0) SumCode1
from
( select Month, Code, sum(Value) sumValue
from anyTable
group by Month, Code
)
)
group by Month

← →
SergSuper (2003-08-13 10:11) [12]

а почему нельзя просто написать?
select Month, Sum(Value*(Code-1)) as SumCode0, Sum(Value*Code) as SumCode1 from anyTable group by Month

← →
Rel_ (2003-08-13 10:14) [13]

to DenK_vrtz © .

Из второго конечно можно, как энто можно сделать с помощью
decode???!!! Я бы его поковырял. Нужно нечто вроде

select ....

Sum(decode(code,<> некое значение, Value,0)),
Sum(decode(code,= некое значение, Value,0))
... и т.д.

и тогда всё будет впорядке (наверное).

Хотя со 2-м запросом ты прав конечно - всё отработает без проблем - просто хочется сделать побыстрее и покороче

← →
Rel_ (2003-08-13 10:19) [14]

to DenK_vrtz ©
//
select Month, sum(SumCode0) SumCode0, sum(SumCode1) SumCode1
from
(select Month, decode(code,0,sumValue,0) SumCode0, decode (code,1,sumValue,0) SumCode1
from
( select Month, Code, sum(Value) sumValue
from anyTable
group by Month, Code
)
)
group by Month
// - ЭТО ТО, ЧТО НАДО, вот только у тебя делется всё только по 2-м значениям : code=0 и code=1, а мне нужно к примеру (code=0 и code<>0 (всё остальное))

to SergSuper

наверное так не получится - ты же просто пермножаешь величину на код и находишь сумму, а мне нужно вывести 2 столбца сумм, разделив их по признаку (коду)

← →
DenK_vrtz © (2003-08-13 10:23) [15]

Rel_, так напиши функцию и в ней обработчик

← →
Johnmen © (2003-08-13 10:24) [16]

SELECT Month M, (SELECT SUM(T2.Value) FROM Table T2 WHERE (T2.Month=T1.Month) AND (T2.Code=0)) S1, (SELECT SUM(T3.Value) FROM Table T3 WHERE (T3.Month=T1.Month) AND (T3.Code=1)) S2 FROM Table T1 GROUP BY T1.Month

← →
DenK_vrtz © (2003-08-13 10:28) [17]

Rel_, кстати, у SergSuper классно работало бы, если code был равено ТОЛЬКО 0 или 1

SergSuper, первую сумму на -1 надо умножить, а то отрицательная сумма при code=0 получается. Так?

← →
Rel_ (2003-08-13 10:41) [18]

to DenK_vrtz © вооще ты прав - не разобрал.

Вот что я сделал:
SELECT month,
DECODE (code, 0, sumvalue, 0) sumcode0,
DECODE (code, 1, sumvalue, 0) sumcode1
FROM
(
SELECT month,
0 AS code,
SUM (value) as sumvalue
FROM anyTable
WHERE
code =0
GROUP BY month
UNION
SELECT month,
1 AS code,
SUM (value) as sumvalue
FROM anyTable
WHERE code <> 0
GROUP BY month
)

← →
DenK_vrtz © (2003-08-13 10:49) [19]

Rel_ (13.08.03 10:41), только union надо на union all заменить - быстрее работать будет.
Обрати внимание на запрос Johnmen © (13.08.03 10:24)!

← →
Rel_ (2003-08-13 10:51) [20]

to Johnmen ©

вот спасибо - гениально и просто. Вот только ругается на SUM - не пойму почему. "выражение не является выражением group by" !!!

← →
DenK_vrtz © (2003-08-13 11:17) [21]

если надо разделить только cod=0 и cod<>0
select Month, sum(SumCode0) SumCode0, sum(SumCode1) SumCode1
from
(select Month, decode(code*1,0,sumValue,0) SumCode0, decode (code*1,cod,sumValue,0) SumCode1
from
( select Month, Code, sum(Value) sumValue
from anyTable
group by Month, Code
)
)
group by Month

← →
Rel_ (2003-08-13 11:29) [22]

to DenK_vrtz © не совсем . запрос должен работать в общем случае, например с параметрами code1=1276 и code<>1276

Запрос Johnmen © работает на ура, только там ошибочка - GROUP BY T1.Month следует убрать и добавить distinct

← →
Johnmen © (2003-08-13 11:40) [23]

>Rel_ (13.08.03 11:29) [22]
>только там ошибочка - GROUP BY T1.Month

Нет, не ошибочка :) А distinct - ошибочка...

← →
DenK_vrtz © (2003-08-13 11:51) [24]

а если так попробовать

select Month, sum(SumCode0) SumCode0, sum(SumCode1) SumCode1
from
(select Month, decode(code,:параметр,sumValue,0) SumCode0, decode (code,:параметр,sumValue,0) SumCode1
from
( select Month, Code, sum(Value) sumValue
from anyTable
group by Month, Code
)
)
group by Month

И хотелось бы сравнить результаты по скорости

← →
Rel_ (2003-08-13 11:54) [25]

to Johnmen ©
А в чём в данном случае разница и почему тогда при испоьлзовании group bu компилятор ругается на SUM (по моему пытаясь сопоставить его с GROUP BY), хотя в вашем примере всё логично (не спорю).

← →
DenK_vrtz © (2003-08-13 11:57) [26]

Rel_, результаты по скорости можно узнать? Интересно!

← →
Rel_ (2003-08-13 12:05) [27]

Конечно, 1 секунда, но запрос запускаю уже раз 10-ый, поэтому и проходит быстро (особенность ORACLE) - поэтому время точно не услежу. При первом запуске отрабатывал по-моему секунд 5-6.

>Rel_ (13.08.03 11:54) [25]
>тогда при испоьлзовании group bu компилятор ругается на SUM

Можно глянуть на РЕАЛЬНЫЙ запрос ? И на сообщение об ошибке ?

Rel_ (13.08.03 12:05), я не понял! У всех запросов время выполнения одно и тоже?

← →
SergSuper (2003-08-13 12:33) [30]

Я чё-то еще меньше понимаю. Неужели из-за такой элементарной задачки надо писать такие монстрообразные запросы?
В MS SQL есть такая конструкция CASE, наверняка она есть и в ORACLE (это ж вроде стандартная конструкция). Тогда можно писать так
select Month, Sum(case Code when 0 then Value else 0 end) as SumCode0, Sum(case Code when 1 then Value else 0 end) as SumCode1 from anyTable group by Month

Но есть Code может быть только 0 или 1 то можно использовать мой предыдущий вариант, только вместо Code-1 надо конечно писать 1-Code, ну пардон, ошибся.

Rel_, прежде чем отвергать - может стоит попробывать(то, что думать не хочется - я уже понял)?

← →
Rel_ (2003-08-13 13:11) [31]

Народ, извините, что долго отсутствовал - уходил на обед. Итак, если беседа всё ещё актуальная и интересна С УДОВОЛЬСТВИЕМ продолжаю.

SergSuper Спасибо, щас обязательно попробую.

Johnmen © я приведу уже исправленный запрос (т.е. где distinct)

SELECT distinct mes_opl,
(SELECT SUM (t2.oplata+t2.KWPL+t2.REMON+t2.ELEK-t2.DOMOF)
FROM opl22 t2
WHERE (t2.mes_opl = t1.mes_opl) AND (t2.n_bol <> "999999")
) s1,
(SELECT SUM (t3.oplata+t3.KWPL+t3.REMON+t3.ELEK)
FROM opl22 t3
WHERE (t3.mes_opl = t1.mes_opl) AND (t3.n_bol = "999999")
) s2
FROM opl22 t1
WHERE t1.n_bol <> "555555" and t1.n_bol <> "666666" and t1.mes_opl<=7

union all

SELECT 13, s1.s, s2.s
FROM (SELECT SUM ( oplata+ kwpl+ remon+ elek- domof) s
FROM opl22
WHERE (mes_opl > 7)
AND (n_bol <> "999999")
AND (n_bol <> "555555")
AND (n_bol <> "666666")) s1,

(SELECT SUM ( oplata+ kwpl+ remon+ elek) s
FROM opl22
WHERE (mes_opl > 7) AND (n_bol = "999999")) s2

да, без 0,5 не разберешься!!!
На втором select после union all имеешь декартово произведение - это полохо!

>Rel_ (13.08.03 13:11) [31]

Если с дистинктом работает, то это особенности ораклового диалекта SQL. Идеологически это верно.
Если не работает с группировкой - это опять же особенности... Но это тоже верно.

А вообще, некоторый интерес представляет сравнение производительности разных предложенных здесь вариантов.

блин, а как вообще этот запрос може работать?! union all не пожет объединять запросы с разноименными полями

Сор, я не прав! Может объединять, только нехорошо это!

← →
Rel_ (2003-08-13 13:28) [36]

to DenK_vrtz © а где здесь одноимённые поля.

Кстати, попробовал запрос SergSuper - просто круто. Смотрите, во что превратился мой монстр:

SELECT mes_opl,
SUM (CASE
WHEN n_bol <> "999999"
THEN oplata+KWPL+REMON+ELEK-DOMOF
ELSE 0
END) AS sumcode0,
SUM (CASE
WHEN n_bol = "999999"
THEN oplata+KWPL+REMON+ELEK-DOMOF
ELSE 0
END) AS sumcode1
FROM opl22
WHERE n_bol <> "555555" AND n_bol <> "666666" AND mes_opl <= 7
GROUP BY mes_opl

union -- ну здесь то же самое

← →
Rel_ (2003-08-13 13:30) [37]

Работает мгновенно. Мастера, для меня вопрос определился. ОГРОМНОЕ СПАСИБО!!! Могу реализовать любые эксперементы с обсужаемыми запросами - они все у меня на экране.

запрос SQL Найти похожие ветки