Zapytanie sprzedaży :: 4programmers.net

supaplx

2015-04-23T15:09:29+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 2

0

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:

Kopiuj

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

katelx

2015-04-23T15:27:05+02:00

Rejestracja: dni
Ostatnio: dni
Lokalizacja: Hong Kong

0

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

Kopiuj

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

supaplx

2015-04-23T15:34:20+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 2

0

katelx napisał(a):

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

Kopiuj

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ą... ;-(

fourfour

2015-04-23T15:38:54+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 627

1

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

Kopiuj

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

katelx

2015-04-23T16:04:56+02:00

Rejestracja: dni
Ostatnio: dni
Lokalizacja: Hong Kong

0

ok, to wydajniej:

Kopiuj

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

abrakadaber

2015-04-23T20:17:32+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 6610

1

ok to tak

bierzesz WSZYSTKICH kontrahentów

Kopiuj

select klient_id from klienci

do tego WSZYSTKIE grupy towarów

Kopiuj

select grupa from towary group by grupa

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

Kopiuj

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

łączysz to wszystko do "kupy"

Kopiuj

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

Kopiuj

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
user image

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

oraz z union i cross join

Kopiuj

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
user image

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

Kopiuj

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

musi być

Kopiuj

(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ę