[es] - SQL Mozgalica za Juli 2007

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

SQL Mozgalica za Juli 2007

^{13.07.2007. u 18:53 - pre 204 meseci}

Jedan problem koji se cesto javljao na Access forumu:

Firma prodaje neku robu. Kupci su u tabeli Customers. Svake kalendarske gosdine tabele se prazne i pocinej se od nule. Znaci, godina je uvek jedna ista za sve transakcije. Prodaja se belezi u tabeli Orders. Pitanje glasi:
"Napraviti izvestaj o prodaji za mesec Oktobar. Prikazati ImeKupca, BrojNarudzbi". Moraju biti prikazani svi kupci, cak i oni koji nisu imali ni jednu narudzbu u Oktobru.

Ova mozgalica je relativno laka, leto je pa ne zelimo bas mnogo da se mucimo. Ipak, ima vise nacina da se resi, pa niej lose da ih vidimo.

Code:

create table Customers
(    CustomerID int identity(1,1)
    , CustomerName varchar(50)
)
go
create table Orders
(    OrderID int identity(1,1)
    , CustomerID int
    , OrderPlaceDate datetime
    , OrderShipDate datetime
)
go
insert Customers select 'Goca'
insert Customers select 'Ceca'
insert Customers select 'Miki'
insert Customers select 'Pera'

insert Orders select 1, '9-1-05', '9-4-05'
insert Orders select 1, '10-1-05', '10-7-05'
insert Orders select 1, '11-1-05', '11-24-05'

insert Orders select 2, '9-11-05', '9-15-05'
insert Orders select 2, '9-3-05', '9-24-05'
insert Orders select 2, '9-30-05', '10-9-05'

insert Orders select 3, '10-1-05', '10-7-05'

Racunati sa datumom narudzbe, kolona Orders.OrderPlaceDate

Prvi pokusaj bi mogao da bude nesto ovako:

Code:

SELECT
    C.CustomerName
    , COUNT(O.OrderID) AS CountOforders
FROM Customers AS C
JOIN Orders AS O ON O.CustomerID = C.CustomerID
WHERE datepart(m, O.OrderPlaceDate) = 10
GROUP BY
    C.CustomerName
    , C.CustomerID

-- Rezultat
CustomerName                                       CountOforders
-------------------------------------------------- -------------
Goca                                                           1
Miki                                                           1

(2 row(s) affected)

Rezultat je ocigledno pogresan. Prikazuje samo kupce koji su imali narudzbu u Oktobru.

Nista zato, ja sam strucnjak za T-SQL pa sam primenio LEFT JOIN:

Code:

SELECT
    C.CustomerName
    , C.CustomerID
    , COUNT(O.OrderID) AS CountOforders
FROM Customers AS C
LEFT JOIN Orders AS O ON O.CustomerID = C.CustomerID
WHERE datepart(m, O.OrderPlaceDate) = 10
GROUP BY
    C.CustomerName
    , C.CustomerID

CustomerName                                        CustomerID CountOforders
-------------------------------------------------- ----------- -------------
Goca                                                         1             1
Miki                                                         3             1

(2 row(s) affected)

Jak strucnjak, opet isti rezultat.

Sta dalje? Vidi li neko gresku u poslednjoj naredbi (sa LEFT JOIN) ili postoji i neki drugi nacin da se ovo resi?
Ispravan rezultat glasi:

Code:

CustomerName                                        CustomerID CountOforders
-------------------------------------------------- ----------- -------------
Goca                                                         1             1
Ceca                                                         2             0
Miki                                                         3             1
Pera                                                         4             0

Kako doci do ispravnog rezultata?

Odgovor na temu

CandyMan

Član broj: 3420
Poruke: 147

+49 Profil

Re: SQL Mozgalica za Juli 2007

^{13.07.2007. u 23:38 - pre 204 meseci}

Zdravo,

Rešenje je instant, ali šta da se radi... Sutra možda okačim nešto pametnije

Code:

mysql> select * from Customers;
+------------+--------------+
| CustomerID | CustomerName |
+------------+--------------+
|          1 | Prvi         |
|          2 | Drugi        |
|          3 | Treci        |
|          4 | Cetvrti      |
+------------+--------------+
4 rows in set (0.00 sec)

mysql> select * from Orders;
+---------+------------+---------------------+
| OrderID | CustomerID | PlaceDate           |
+---------+------------+---------------------+
|       1 |          1 | 2007-10-01 00:00:00 |
|       2 |          1 | 2007-10-01 00:00:00 |
|       3 |          1 | 2007-11-01 00:00:00 |
|       4 |          2 | 2007-10-01 00:00:00 |
|       5 |          2 | 2007-10-01 00:00:00 |
|       6 |          2 | 2007-11-01 00:00:00 |
|       7 |          2 | 2007-10-01 00:00:00 |
|       8 |          3 | 2007-10-01 00:00:00 |
|       9 |          3 | 2007-11-01 00:00:00 |
|      10 |          1 | 2007-10-01 00:00:00 |
|      11 |          1 | 2007-11-01 00:00:00 |
+---------+------------+---------------------+
11 rows in set (0.00 sec)

mysql> select a.*, ifnull(b.xxx,0) NoOfOrders
    ->   from Customers a left join
    ->             (select CustomerID,
    ->                     count(*) as xxx
    ->                from Orders
    ->               group by CustomerID) b
    ->        on a.CustomerID=b.CustomerID;
+------------+--------------+------------+
| CustomerID | CustomerName | NoOfOrders |
+------------+--------------+------------+
|          1 | Prvi         |          5 |
|          2 | Drugi        |          4 |
|          3 | Treci        |          2 |
|          4 | Cetvrti      |          0 |
+------------+--------------+------------+
4 rows in set (0.00 sec)

mysql>

U where ugnježdenog upita može da se uglavi bilo kakav uslov tipa mesec i sl.

Code:

mysql> select a.*, ifnull(b.xxx,0) NoOfOrders
    ->   from Customers a left join
    ->             (select CustomerID,
    ->                     count(*) as xxx
    ->                from Orders
    ->        where month(PlaceDate) = 10
    ->               group by CustomerID) b
    ->        on a.CustomerID=b.CustomerID;
+------------+--------------+------------+
| CustomerID | CustomerName | NoOfOrders |
+------------+--------------+------------+
|          1 | Prvi         |          3 |
|          2 | Drugi        |          3 |
|          3 | Treci        |          1 |
|          4 | Cetvrti      |          0 |
+------------+--------------+------------+
4 rows in set (0.00 sec)

Nisam ni znao da znam dok nisam prob'o!

Odgovor na temu

Shinhan
PHP programmer
Subotica

Član broj: 12327
Poruke: 372
*.ADSL.neobee.net.

Jabber: shinhan@elitesecurity.org
ICQ: 400847988

+4 Profil

Re: SQL Mozgalica za Juli 2007

^{15.07.2007. u 17:32 - pre 204 meseci}

Evo moje varijante, za MySQL:

Code:

select customername, sum(not(isnull(orderid)) && month(orderplacedate)=10) as ordercount
from customers
left outer join orders using (customerid)
group by customerid

Ovakve stvari volim da rešavam ubacivanjem logike u sum().

EDIT: Ubaceni su code tagovi

_{[Ovu poruku je menjao chachka dana 22.07.2007. u 16:39 GMT+1]}

"Common sense is not so common." - Voltaire

Odgovor na temu

CandyMan

Član broj: 3420
Poruke: 147

+49 Profil

Re: SQL Mozgalica za Juli 2007

^{15.07.2007. u 19:07 - pre 204 meseci}

Zdravo!
Brzina mi je vrlina! Svoj prethodni post nisam hteo da brišem, da ostane školski primer trčanja pred rudu... Znam da se dobije dobar rezultat, ali može to i bolje.
Zidar reče da nešto fali u njegovom upitu, evo i šta

Code:

mysql> select a.CustomerID,
    ->        a.CustomerName,
    ->        count(b.Cu stomerID)
    ->   from Customers a left join Orders b on a.CustomerID = b.CustomerID
    ->  where b.PlaceDate is null  or -- ovo fali
    ->        month(b.PlaceDate) = 10
    ->  group by a.CustomerID,
    ->        a.CustomerName;
+------------+--------------+---------------------+
| CustomerID | CustomerName | count(b.CustomerID) |
+------------+--------------+---------------------+
|          1 | Prvi         |                   3 |
|          2 | Drugi        |                   3 |
|          3 | Treci        |                   1 |
|          4 | Cetvrti      |                   0 |
+------------+--------------+---------------------+

Kad smo već napravili left join, očekujemo da neka polja imaju vrednost null.
U ovom slučaju nas interesuju null-ovi (Customeri bez Ordera) i (ili) Orderi sa datumom iz oktobra.

Pozdrav!

_{[Ovu poruku je menjao CandyMan dana 16.07.2007. u 00:17 GMT+1]}

Nisam ni znao da znam dok nisam prob'o!

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: SQL Mozgalica za Juli 2007

^{16.07.2007. u 14:07 - pre 204 meseci}

Hvala CandyMan :-)

Prvo resenje radi, prevedeno na MS SQL izgleda ovako:

Code:

select a.*, isnull(b.xxx,0) NoOfOrders
      from Customers a left join
                (select CustomerID,
                         count(*) as xxx
                  from Orders
                  group by CustomerID) b
          on a.CustomerID=b.CustomerID;

i dobije se ovo:

Code:

CustomerID CustomerName                                        NoOfOrders
----------- -------------------------------------------------- -----------
          1 Goca                                                         3
          2 Ceca                                                         3
          3 Miki                                                         1
          4 Pera                                                         0

(4 row(s) affected)

Drugo resenje medjutim ne radi bas kako treba, barem na MS SQL:

Code:

select a.CustomerID,
           a.CustomerName,
           count(b.CustomerID)
from Customers a
left join Orders b on a.CustomerID = b.CustomerID
where b.OrderPlaceDate is null
or -- ovo fali -- by Zidar; veom zanimljiva upotreba OR !
  month(b.OrderPlaceDate) = 10
group by a.CustomerID,
   a.CustomerName;

daje ovakav rezultat:

Code:

CustomerID CustomerName
----------- -------------------------------------------------- -----------
          1 Goca                                                         1
          3 Miki                                                         1
          4 Pera                                                         0
Warning: Null value is eliminated by an aggregate or other SET operation.

(3 row(s) affected)

Vraca tri umesto cetiri reda? Da li u MySQL dobijes sva cetiri reda?

Meni je proradilo ovako u MS SQL:

Code:

SELECT
    C.CustomerName
    , C.CustomerID
    , COUNT(O.OrderID) AS CountOforders
FROM Customers AS C
LEFT JOIN Orders AS O ON O.CustomerID = C.CustomerID
                    AND datepart(m, O.OrderPlaceDate) = 10
GROUP BY
    C.CustomerName
    , C.CustomerID

i dobije se

Code:

CustomerName                                        CustomerID CountOforders
-------------------------------------------------- ----------- -------------
Goca                                                         1             1
Ceca                                                         2             0
Miki                                                         3             1
Pera                                                         4             0
Warning: Null value is eliminated by an aggregate or other SET operation.

(4 row(s) affected)

Trik je u prebacivanju WHERE uslova u JOIN. Kad ima s LEFT JOIN, WHERE ima smisla samo na levoj strani (na setu odakle se prikazuju SVI redovi, ono sto je FROM). Ako u WHERE stavis neku kolonu iz seta je na desnoj strani (ono sto stoji iz a LEFT JOIN), nikad ne znas kako ce se ponasati redovi koji su NULL. S druge strane, nija bas uvek jasno ni kako ce se ponasati slozeni JOIN, pa sta je radilo u ovom slucaju, ne mora da radi u nekim drugim uslovima.

Moguce je da su resenja sa subkverijima puzdanija u situacijama kao sto je ova. Tvoje resenej sa subkverijem u MS SQL moze da izgleda ovako:

Code:

SELECT
    C.CustomerName
    , C.CustomerID
    , (SELECT   COUNT(*) FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID
        AND datepart(m, O.OrderPlaceDate) = 10
        GROUP BY O.CustomerID
    ) AS CountOforders
FROM Customers AS C

CustomerName                                        CustomerID CountOforders
-------------------------------------------------- ----------- -------------
Brian                                                        1             1
Andy                                                         2          NULL
Steve                                                        3             1
Delaney                                                      4          NULL

(4 row(s) affected)

Da se resimo NULL una izlazu, mozemo da to malo modifikujemo, ovako:

Code:

SELECT
    C.CustomerName
    , C.CustomerID
    , isnull(
                (SELECT   COUNT(*) FROM Orders AS O
                    WHERE O.CustomerID = C.CustomerID
                    AND datepart(m, O.OrderPlaceDate) = 10
                    GROUP BY O.CustomerID
                )
        , 0 )
    AS CountOforders
FROM Customers AS C

CustomerName                                        CustomerID CountOforders
-------------------------------------------------- ----------- -------------
Brian                                                        1             1
Andy                                                         2             0
Steve                                                        3             1
Delaney                                                      4             0

(4 row(s) affected)

:-)

Odgovor na temu

CandyMan

Član broj: 3420
Poruke: 147

+49 Profil

Re: SQL Mozgalica za Juli 2007

^{20.07.2007. u 23:02 - pre 204 meseci}

Lepo, ne verujući da upit ne radi na MSSQL-u, čak sam i instalirao isti i dobio rezultat isti kao na MySQL-u, tj. ispravan.
Nisam dobio pomenutu poruku, ali me baš zanima objašnjenje... Pogledaćemo

Nisam ni znao da znam dok nisam prob'o!

Prikačeni fajlovi

image001.png - 19.17k

Odgovor na temu

negyxo
Aleksandar Perkuchin

Član broj: 29751
Poruke: 898
77.46.156.*

+171 Profil

Re: SQL Mozgalica za Juli 2007

^{21.07.2007. u 01:52 - pre 204 meseci}

Pa da ja pojasnim malo.

@Zidar koje su ti sve vrednosti u tabeli Orders za polje b.CustomerID? Pretpostavljam da postoje NULL vrednosti, jer ovaj waring se javlja kada se koristi agregatna funkcija nad NULL vrednostima. To je normalno ponasanje SQL Servera. Da je kojim slucajem umesto ovoga

Code:

select a.CustomerID,
a.CustomerName,
count(b.CustomerID)
...

bilo

Code:

select a.CustomerID,
a.CustomerName,
count(*)
...

pretpostavljam da bi radilo, ako nista drugo bar bi broj trebao da je drukciji. Naime, SQL Server kada se izostavi naziv kolone broji sve redove u rezultatu, dok, kada se navede ime kolone izostavlja NULL vrednosti. Ovo je 'by design' tako da ne ispadne da je bug, inace o ovome sam nasao tekst u knjizi Professional SQL Server 2005, koja je odlicna, napokon neka knjiga da nije bas za pocetnike.

Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

+4 Profil

Re: SQL Mozgalica za Juli 2007

^{21.07.2007. u 21:51 - pre 204 meseci}

Cini mi se da CandyMan ne koristi skup testnih podataka kojeg je Zidar dao na pocetku. Ako je tako nije ni cudo sto se rezultati razlikuju.

Ja sam pustio CandyMan-ov drugi upit (varijanta OR u WHERE klauzuli) na Firebird 2.0.1, PostgreSQL 8.2 i Oracle XE 10.2g. U sva tri slucaja sam dobio identican neispravan rezultat:

Code:

customerid   customername   count
----------   ------------   -----
         1   Goca               1
         3   Miki               1
         4   Pera               0

CandyMan-ov prvi upit (subselekt u INNER JOIN-u) mi na sva tri sistema daje trazeni rezultat:

Code:

customerid   customername   count
----------   ------------   -----
         1   Goca               1
         2   Ceca               0
         3   Miki               1
         4   Pera               0

_{[Ovu poruku je menjao chachka dana 21.07.2007. u 23:02 GMT+1]}

_{[Ovu poruku je menjao chachka dana 21.07.2007. u 23:04 GMT+1]}

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Odgovor na temu

CandyMan

Član broj: 3420
Poruke: 147

+49 Profil

Re: SQL Mozgalica za Juli 2007

^{22.07.2007. u 09:50 - pre 204 meseci}

Zdravo,

Chacka je u pravu, nije bio isti set testnih podataka pa sam ja umislio da je rešenje OK - izvinjavam se ako sam uneo zabunu.
Dakle, kada se napravi join, where uslov isključi sve one koji imaju ordere a isti nisu iz oktobra, so, disregard solution number 2.

Evo jednog MSSQL (i ne samo MSSQL uz minimalne izmene) rešenja koje radi:

Code:

insert into customers values(1,'Goca');
insert into customers values(2,'Ceca');
insert into customers values(3,'Miki');
insert into customers values(4,'Pera');

insert into orders values (1,1,'09/01/05');
insert into orders values (1,2,'10/01/05');
insert into orders values (1,3,'11/01/05');

insert into orders values (2,1,'09/11/05');
insert into orders values (2,2,'09/3/05');
insert into orders values (2,3,'09/30/05');

insert into orders values (3,1,'10/01/05');

select a.CustomerID,
       a.CustomerName,
       count(b.CustomerID) UkupnoOrdera,
       sum(case month(b.PlaceDate)
             when 10 then 1
             else 0
           end) OktobarskiOrderi
  from Customers a left join Orders b on a.CustomerID = b.CustomerID
group by a.CustomerID, a.CustomerName;

_{[Ovu poruku je menjao CandyMan dana 22.07.2007. u 11:00 GMT+1]}

Nisam ni znao da znam dok nisam prob'o!

Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

+4 Profil

Re: SQL Mozgalica za Juli 2007

^{22.07.2007. u 15:49 - pre 204 meseci}

Da eto jos jednog resenja. Ovo je varijanta upita koja lici na Shinhan-ovu. Shinan je iskoristio osobinu MySQL-a da je true = 1, a false = 0. CandyMan-ova varijanta koristi standardni SQL, pa je i vise sistema prepoznaje.

Postoji suptilna razlika izmedju Zidarovog i CandyMan-ovog resenja (odnosno zasto uopste Zidarovo resenje sa dva uslova u JOIN-u valja).

Code:

-- Zidarovo resenje koje valja
SELECT
    C.CustomerName
    , C.CustomerID
    , COUNT(O.OrderID) AS CountOforders
FROM Customers AS C
LEFT JOIN Orders AS O ON O.CustomerID = C.CustomerID
                    AND datepart(m, O.OrderPlaceDate) = 10
GROUP BY
    C.CustomerName
    , C.CustomerID

Code:

-- CandyMan-ovo resenje koje ne valja
select a.CustomerID,
       a.CustomerName,
       count(b.Cu stomerID)
  from Customers a left join Orders b on a.CustomerID = b.CustomerID
where b.PlaceDate is null  or
       month(b.PlaceDate) = 10
group by a.CustomerID,
          a.CustomerName;

_{[Ovu poruku je menjao chachka dana 23.07.2007. u 06:18 GMT+1]}

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Odgovor na temu

CandyMan

Član broj: 3420
Poruke: 147

+49 Profil

Re: SQL Mozgalica za Juli 2007

^{22.07.2007. u 22:24 - pre 204 meseci}

Zdravo!
Evo razlike..

Kako izgleda left join, bez restrikcije i agregatnih funkcija

Code:

mysql> select a.CustomerID,
    ->        a.CustomerName,
    ->        b.CustomerID,
    ->        b.OrderID,
    ->        month(b.PlaceDate) Mesec
    ->   from Customers a left join Orders b on a.CustomerID = b.CustomerID;
+------------+--------------+------------+---------+-------+
| CustomerID | CustomerName | CustomerID | OrderID | Mesec |
+------------+--------------+------------+---------+-------+
|          1 | Goca         |          1 |       1 |     9 |
|          1 | Goca         |          1 |       2 |    10 |
|          1 | Goca         |          1 |       3 |    11 |
|          2 | Ceca         |          2 |       1 |     9 |
|          2 | Ceca         |          2 |       2 |     9 |
|          2 | Ceca         |          2 |       3 |     9 |
|          3 | Miki         |          3 |       1 |    10 |
|          4 | Pera         |       NULL |    NULL |  NULL |
+------------+--------------+------------+---------+-------+
8 rows in set (0.00 sec)

Kada se, prema Zidarevom rešenju, u left join doda uslov, dobije se (ono što sam ja mislio da ću da dobijem sa onim OR):

tj. sužavanje skupa ordera se odigra "pre" left join-a i dobije se dobar rezultat.

U mom rešenju je pogrešno to što se rezultat filtrira u where klauzli upita tj, "posle left join-a" i otpadnu oni koji nemaju nijedan order u oktobru a imaju u drugim mesecima.

Code:

mysql> select a.CustomerID,
    ->        a.CustomerName,
    ->        b.CustomerID,
    ->        b.OrderID,
    ->        month(b.PlaceDate) Mesec
    ->   from Customers a left join Orders b on a.CustomerID = b.CustomerID
    ->  where b.PlaceDate is null or month(b.PlaceDate) = 10;
+------------+--------------+------------+---------+-------+
| CustomerID | CustomerName | CustomerID | OrderID | Mesec |
+------------+--------------+------------+---------+-------+
|          1 | Goca         |          1 |       2 |    10 |
|          3 | Miki         |          3 |       1 |    10 |
|          4 | Pera         |       NULL |    NULL |  NULL |
+------------+--------------+------------+---------+-------+
3 rows in set (0.00 sec)

To bi trebalo da je to.

Nisam ni znao da znam dok nisam prob'o!

Odgovor na temu