Zapytanie sprzedaży

Wyciągam sprzedaż klientów w każdej grupie towarowej.
Za pomocą naturalnych joinów. Zagregowane to jest za pomocą 'group by' do: klienta i grupy oraz pokazana suma z wartości.

Jeśli nastąpiła sprzedaż w danej grupie towarowej - zobaczę wynik: sumę tej wartości.
Jeśli sprzedaż w danej grupie nie nastąpiła - wartość nie pojawi się.

Jak pokazać wszystkich klientów i dla każdego z nich sprzedaż w każdej grupie bez względu na to czy ona nastąpiła czy nie?

Składnia w uproszczeniu odnosi się do tabeli sprzedaży i towarów:

select s.klient,t.grupa, sum(s.wartosc)
from sprzedaz s join towary t on t.id = s.t_id
group by s.klient,t.grupa

nie recze za efektywnosc ale pierwsze co mi przyszlo do glowy:

select s.klient,t.grupa, sum(case when t.id = s.t_id then s.wartosc else 0 end) wartosc
from sprzedaz s cross join towary t
group by s.klient,t.grupa

katelx napisał(a):

nie recze za efektywnosc ale pierwsze co mi przyszlo do glowy:

select s.klient,t.grupa, sum(case when t.id = s.t_id then s.wartosc else 0 end) wartosc
from sprzedaz s cross join towary t
group by s.klient,t.grupa

eh... to nie ma żadnego związku z efektywnością... ;-(

Nie pamietam jak to jest w oraclu, ale kombinowalbym cos w stylu:

select blablabla 
from klient left join sprzedaz on ....

ok, to wydajniej:

select o.klient, o.grupa, sum(o.wartosc) wartosc from
(select s.klient,t.grupa, sum(s.wartosc) wartosc
from sprzedaz s join towary t on t.id = s.t_id
group by s.klient,t.grupa
union all
select s.klient,t.grupa, 0 wartosc
from sprzedaz s cross join (select distinct i.grupa from towary i) t) o

ok to tak

1. bierzesz WSZYSTKICH kontrahentów

select klient_id from klienci

2. do tego WSZYSTKIE grupy towarów

select grupa from towary group by grupa

indeks na polu grupa wysoce zalecany
3. do tego dokładasz sprzedaż na grupy

select s.klient,t.grupa, sum(s.wartosc) wartosc
from sprzedaz s, towary t where t.id = s.t_id
group by s.klient,t.grupa

4. łączysz to wszystko do "kupy"

select 
  x.klient,
  x.grupa, 
  s. wartosc
from
  (select k.klient, t.grupa from klienci k, towary t group by k.klient, t.grupa) x,
  (select s.klient, t.grupa, sum(s.wartosc) wartosc from sprzedaz s, towary t where t.id = s.t_id group by s.klient,t.grupa) s
where 
  s.klient(+) = x.klient
  and s.grupa(+) = x.klient

i masz to co chciałeś
group by s.klient,t.grupa

baza testowa
kontrahentów 7310 rekordów
grup towarowych 16 rekordów
kartezjan 116960 rekordów
faktur 53917 rekordów
pozycji faktur 519255 rekordów
sprzedaż z podziałem na kontrahentów i grupy 2051 rekordów (wiem, że mało ale taką mam bazę)

zapytania na danych jakie mam

SELECT 
  x.id_k,
  x.grupa, 
  s.wartosc
FROM
  (SELECT k.id_k, t.grupa FROM kokontr k, (SELECT t.grupa FROM totowar t GROUP BY t.grupa) t) x,
  (select f.id_k, t.grupa, sum(p.wa_net) wartosc from fa_dok_nag f, fa_dok_poz p, totowar t where p.id_dok_nag = f.id AND p.towar = t.towar GROUP BY f.id_k, t.grupa) s
WHERE 
  s.id_k(+) = x.id_k
  AND s.grupa(+) = x.grupa

i plan

i czas wykonania pomiędzy .466 a .531 sekundy

oraz z union i cross join

FROM (SELECT 
  f.id_k,
  t.grupa, 
  SUM(f.wa_net) wartosc
FROM 
  fa_dok_nag f
JOIN fa_dok_poz p ON p.id_dok_nag = f.id 
JOIN totowar t ON t.towar = p.towar
GROUP BY 
  f.id_k,
  t.grupa
UNION ALL
SELECT 
  k.id_k,
  t.grupa, 
  0 wartosc
FROM 
  kokontr k 
CROSS JOIN (SELECT DISTINCT i.grupa FROM totowar i) t

plan

czas wykonania pomiędzy 0.459 A 0.537 sekundy

Są to czasy samego wykonania bez pobierania danych bo do bazy jestem podpięty zdalnie i leciałoby to dość długo.

Moim celem nie jest pokazanie wyższości jednego nad drugim tylko pokazać, że czasami kombinowanie nie ma sensu i lepiej napisać coś najprościej jak można bo i tak na jedno wyjdzie

EDIT:
na górze się machnąłem - zamiast

(select k.klient, t.grupa from klienci k, towary t group by k.klient, t.grupa) x,

musi być

(SELECT k.id_k, t.grupa FROM kokontr k, (SELECT t.grupa FROM totowar t GROUP BY t.grupa) t) x,

ponieważ pierwsze wydłuża czas zapytania do prawie 6 sekund, czyli 10 razy dłużej!

BTW zamiana grupowania na distinct nic nie zmienia

EDIT2:
teraz zauważyłem, że drugi kod ma niepotrzebną agregację