Srodne teme
Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

SQL upit - suma poslednje smene

[es] :: Baze podataka :: SQL upit - suma poslednje smene

[ Pregleda: 4940 | Odgovora: 10 ] > FB > Twit

Postavi temu Odgovori

Autor
Pretraga teme: Traži
Markiranje Štampanje RSS

BigFoot
Boban Jovanović
Arilje

Član broj: 1098
Poruke: 991
*.dynamic.sbb.co.yu.



+35 Profil

icon SQL upit - suma poslednje smene30.08.2007. u 18:10 - pre 202 meseci
Imam tabelu sa poljima DatumVreme, Smena, Iznos.
Evo primera:
Code:
... |   1   | 100.00
... |   1   | 200.00
... |   1   |  50.00
... |   1   | 100.00
... |   1   | 150.00
... |   2   | 100.00
... |   2   | 250.00
... |   2   |  80.00
... |   3   | 100.00
... |   3   | 200.00
... |   1   | 100.00
... |   1   | 200.00
... |   2   |  50.00
... |   2   | 100.00
... |   2   | 150.00


Zapisi su hronološki, a treba mi zbir Iznos-a poslednje smene, u ovom slučaju 2, dakle 50 + 100 + 150 = 300.
Pošto imam i ID (autoincrement) slogova, mogao bih uzeti MAX(ID) prethodne smene, pa da upit za sumu bude sa uslovom ID > maxPrevID, ali mi ovo rešenje liči na improvizaciju. Ima li boljih predloga?

Napomena: Smena može da počne jednog, a završi se drugog dana, npr. od 22h, do sutra u 06h.
Two beer or not two beer...
 
Odgovor na temu

Milan Andjelkovic
Product Manager
Tegus Medical
Beograd

Član broj: 4476
Poruke: 3281
*.eunet.yu.

Sajt: www.linkedin.com/in/milan..


+8 Profil

icon Re: SQL upit - suma poslednje smene30.08.2007. u 18:33 - pre 202 meseci
Hmm, recimo:
Code:
select sum(iznos) from tabela where smena = ( select smena from tabela where id = max(id) ) and DayOfYear(DatumVreme) = ( select DayOfYear(DatumVreme) from tabela where id = max(id) )


Nije ispalo narocito elegantno, barem ne kako sam prvo pomislio :)

Elem, ovo podrazumeva da svi rekordi u poslednjoj smeni (odnosno bilo kojoj smeni u jednom danu) imaju istu vrednost za DatumVreme, odnosno isti dan. Da li je to tako?
ex.mangel
 
Odgovor na temu

BigFoot
Boban Jovanović
Arilje

Član broj: 1098
Poruke: 991
*.dynamic.sbb.co.yu.



+35 Profil

icon Re: SQL upit - suma poslednje smene30.08.2007. u 19:06 - pre 202 meseci
Mislim da nije dobro. ID je različit za svaki slog jer je autoincrement. Poslednju smenu već imam i u programu pa se može pojednostaviti upit. Dakle, poznato je koja je poslednja smena. Medjutim, kao što piše u napomeni, smena može biti u dva dana, početi jednog dana, uveče, a završiti se drugog dana, ujutro.
Two beer or not two beer...
 
Odgovor na temu

Miloš Baić
Miloš Baić
ERP (Dynamics NAV) programer
Beograd

Član broj: 72468
Poruke: 1155
*.yubc.net.



Profil

icon Re: SQL upit - suma poslednje smene31.08.2007. u 13:32 - pre 202 meseci
Nešto slično: [SQL mozgalice] Mozgalica broj 7 - dolazak i odlazak s posla.
Someone's sitting in the shade today because someone planted a tree a long time ago.
 
Odgovor na temu

Zidar
Canada

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



+79 Profil

icon Re: SQL upit - suma poslednje smene31.08.2007. u 14:23 - pre 202 meseci
Meni se cini da je ovaj zadatak sasvim zreo da bude mozgalica. Jeste slicno Mozgalici Br 7, ali mislim da nijre potpuno isto.
U svakom slucaju cini mi se da se ovde trazi konkretno resenje bez mnogo teoretisanja, pa sam probao nesto, u mom MS SQL. Mislim da je kod dovoljno standardan da se moze barem razumeti.

Kreairjmo tabelu i neke test podatke:
Code:

CREATE TABLE Z_Primer 
(DatumVreme datetime NOT NULL
, Smena int NOT NULL
, Iznos money NOT NULL)

INSERT INTO Z_Primer VALUES ('2007-08-25 16:11:19.527',1,200.00)
INSERT INTO Z_Primer VALUES  ('2007-08-25 20:11:19.527',1,100)
INSERT INTO Z_Primer VALUES  ('2007-08-25 21:11:19.527',2,250)
INSERT INTO Z_Primer VALUES  ('2007-08-25 22:11:19.527',1,400)
INSERT INTO Z_Primer VALUES  ('2007-08-26 06:11:19.527',1,150)
INSERT INTO Z_Primer VALUES  ('2007-08-26 08:11:19.527',1,150)
INSERT INTO Z_Primer VALUES  ('2007-08-26 10:11:19.527',2,250)
INSERT INTO Z_Primer VALUES  ('2007-08-26 11:11:19.527',2,150)
INSERT INTO Z_Primer VALUES  ('2007-08-26 12:11:19.527',3,250)
INSERT INTO Z_Primer VALUES  ('2007-08-26 14:11:19.527',3,175)
INSERT INTO Z_Primer VALUES  ('2007-08-26 16:11:19.527',3,250)
INSERT INTO Z_Primer VALUES  ('2007-08-26 19:11:19.527',3,100)
INSERT INTO Z_Primer VALUES  ('2007-08-26 21:11:19.527',2,150)
INSERT INTO Z_Primer VALUES  ('2007-08-26 22:11:19.527',2,250)
INSERT INTO Z_Primer VALUES  ('2007-08-26 23:11:19.527',2,100)
INSERT INTO Z_Primer VALUES  ('2007-08-27 02:11:19.527',2,300)

SELECT * FROM Z_Primer

DatumVreme              Smena    Iznos
2007-08-25 16:11:19.527    1    200.00
2007-08-25 20:11:19.527    1    100.00
2007-08-25 21:11:19.527    1    250.00
2007-08-25 22:11:19.527    1    400.00
2007-08-26 06:11:19.527    1    150.00
2007-08-26 08:11:19.527    1    150.00
2007-08-26 10:11:19.527    2    250.00
2007-08-26 11:11:19.527    2    150.00
2007-08-26 12:11:19.527    3    250.00
2007-08-26 14:11:19.527    3    175.00
2007-08-26 16:11:19.527    3    250.00
2007-08-26 19:11:19.527    3    100.00
2007-08-26 21:11:19.527    2    150.00
2007-08-26 22:11:19.527    2    250.00
2007-08-26 23:11:19.527    2    100.00
2007-08-27 02:11:19.527    2    300.00


Bilo bi lepo kad bi mogli da grupisemo po koloni Smena, ali bas ne ide nikako. Kad bi mogli da promenimo dizajn baze, ja bih uveo jos jednu tabelu, Smena u kju bi se upisvao jedinstveni ID za svaku smenu. Tabela Z_Primer bila bi child tabele i izgledala bi nekako ovako:
Code:

SmenaUniqueID    DatumVreme              Smena    Iznos
A                2007-08-25 16:11:19.527    1    200.00
A                2007-08-25 20:11:19.527    1    100.00
A                2007-08-25 21:11:19.527    1    250.00
A                2007-08-25 22:11:19.527    1    400.00
A                2007-08-26 06:11:19.527    1    150.00
A                2007-08-26 08:11:19.527    1    150.00
B                2007-08-26 10:11:19.527    2    250.00
B                2007-08-26 11:11:19.527    2    150.00
C                2007-08-26 12:11:19.527    3    250.00
C                2007-08-26 14:11:19.527    3    175.00
C                2007-08-26 16:11:19.527    3    250.00
C                2007-08-26 19:11:19.527    3    100.00
D                2007-08-26 21:11:19.527    2    150.00
D                2007-08-26 22:11:19.527    2    250.00
D                2007-08-26 23:11:19.527    2    100.00
D                2007-08-27 02:11:19.527    2    300.00

Onda bi zadatak bio trivijalan - GROUP BY SmenaUniqueID bi rsio problem.
Medjutim, verovatno da u ovom momentu ne mozemo menjati dizajn. Znaci, treba da nekeko iz postojecih podataka izvedemo nakakav identifier za grupe redova sa ponavljajucom smenom. Meni to nije bilo lako i nadam toplo se da ce me neko opet poklopiti sa jednostavnijim resenjem, kao u mozgalici za kraj leta :-)

Posle vise proba i gresaka, dobio sam nesto ovako:
Code:

SELECT
    DatumVreme
    , Smena
    , SmenaUPrethodnomSlogu = (SELECT Smena FROM Z_Primer WHERE DatumVreme = PrethodnoVreme)
    , BreakPoint = CASE
                        WHEN Smena = (SELECT Smena FROM Z_Primer WHERE DatumVreme = PrethodnoVreme) THEN 0
                        ELSE 1 
                    END 
    , Iznos
--    , PoslednjaSmena
    , PrethodnoVreme
FROM
(
SELECT
    DatumVreme
    , Smena
    , Iznos
    , PrethodnoVreme = (SELECT MAX(DatumVreme) FROM Z_Primer AS B
                            WHERE B.DatumVreme < A.DatumVreme
                        )
FROM Z_Primer AS A
) AS X

sto je dalo ovakav rezultat:


Code:

DatumVreme                    Smena SmenaUPrethodnomSlogu  BreakPoint                 Iznos PrethodnoVreme
----------------------- ----------- --------------------- ----------- --------------------- -----------------------
2007-08-25 16:11:19.527           1                  NULL           1                200.00 NULL
2007-08-25 20:11:19.527           1                     1           0                100.00 2007-08-25 16:11:19.527
2007-08-25 22:11:19.527           1                     1           0                400.00 2007-08-25 20:11:19.527
2007-08-26 06:11:19.527           1                     1           0                150.00 2007-08-25 22:11:19.527
2007-08-26 08:11:19.527           1                     1           0                150.00 2007-08-26 06:11:19.527
2007-08-26 10:11:19.527           2                     1           1                250.00 2007-08-26 08:11:19.527
2007-08-26 11:11:19.527           2                     2           0                150.00 2007-08-26 10:11:19.527
2007-08-26 12:11:19.527           3                     2           1                250.00 2007-08-26 11:11:19.527
2007-08-26 14:11:19.527           3                     3           0                175.00 2007-08-26 12:11:19.527
2007-08-26 16:11:19.527           3                     3           0                250.00 2007-08-26 14:11:19.527
2007-08-26 19:11:19.527           3                     3           0                100.00 2007-08-26 16:11:19.527
2007-08-26 21:11:19.527           2                     3           1                150.00 2007-08-26 19:11:19.527
2007-08-26 22:11:19.527           2                     2           0                250.00 2007-08-26 21:11:19.527
2007-08-26 23:11:19.527           2                     2           0                100.00 2007-08-26 22:11:19.527
2007-08-27 02:11:19.527           2                     2           0                300.00 2007-08-26 23:11:19.527

(15 row(s) affected)

Uocite kolonu BreakPoint. Svuda su nule, osim tamo gde pocinje nova smena. Ovo se lepo moze iskoristiti za dodelivanje nekkavog UniqueGroupID.
Prethodni kveri se moze napisati u sazetijem obliku, ovde je napisan ovako da bi onaj ko zeli mogao da razume sve medjukorake (izracunavanje PrethodneSmene i vrednosti BreakPoint)
Elem, napravio sam view koji izracunava BreakPoints i na osnovu njega sam izracunao jedinstvene oznake za grupe. Moze i bez view-a, ali je ovako jasnije sta se desava.
Code:

CREATE VIEW V_Breakpoints
AS
SELECT
    DatumVreme
    , Smena
    -- prethodna smena
    , SmenaUPrethodnomSlogu = (SELECT Smena 
                                FROM Z_Primer 
                                WHERE DatumVreme = ---PrethodnoVreme
                                                    (SELECT MAX(DatumVreme) 
                                                        FROM Z_Primer AS B
                                                        WHERE B.DatumVreme < A.DatumVreme
                                                    )
                                )
    -- Promena smene<=> tekuca smena nije ista kao i prethodna smena
    , BreakPoint = CASE
                        WHEN Smena = (SELECT Smena 
                                        FROM Z_Primer C 
                                        WHERE C.DatumVreme = --- PrethodnoVreme
                                                            (SELECT MAX(DatumVreme) 
                                                                FROM Z_Primer AS B
                                                                WHERE B.DatumVreme < A.DatumVreme
                                                            )
    
                                    ) THEN 0
                        ELSE 1 
                    END 
    , Iznos
FROM Z_Primer AS A
GO

Odradite SELECT * FROM V_Breakpoints i videcete da daje isti rezultat kao prethodni SELECT izraz.
Sada oradimo ovo:
Code:

SELECT
    DatumVreme
    , Smena
    , BreakPoint
    , Grupa = (SELECT SUM(BreakPoint) 
                        FROM V_BreakPoints AS B 
                        WHERE A.DatumVreme >= B.datumVreme)
    , Iznos
FROM V_BreakPoints AS A

DatumVreme                    Smena  BreakPoint       Grupa                 Iznos
----------------------- ----------- ----------- ----------- ---------------------
2007-08-25 16:11:19.527           1           1           1                200.00
2007-08-25 20:11:19.527           1           0           1                100.00
2007-08-25 22:11:19.527           1           0           1                400.00
2007-08-26 06:11:19.527           1           0           1                150.00
2007-08-26 08:11:19.527           1           0           1                150.00
2007-08-26 10:11:19.527           2           1           2                250.00
2007-08-26 11:11:19.527           2           0           2                150.00
2007-08-26 12:11:19.527           3           1           3                250.00
2007-08-26 14:11:19.527           3           0           3                175.00
2007-08-26 16:11:19.527           3           0           3                250.00
2007-08-26 19:11:19.527           3           0           3                100.00
2007-08-26 21:11:19.527           2           1           4                150.00
2007-08-26 22:11:19.527           2           0           4                250.00
2007-08-26 23:11:19.527           2           0           4                100.00
2007-08-27 02:11:19.527           2           0           4                300.00

(15 row(s) affected)

Iz poslednjeg rezultata moze se uraditi GROUP BY Grupa i dobiti resenje. Na primer ovako:
Code:

SELECT
    Grupa
    , UkupanIznos = SUM(Iznos)
    , BrojNarudzbi = COUNT(*)
FROM
    (
    SELECT
        DatumVreme
        , Smena
        , BreakPoint
        , Grupa = (SELECT SUM(BreakPoint) 
                            FROM V_BreakPoints AS B 
                            WHERE A.DatumVreme >= B.datumVreme)
        , Iznos
    FROM V_BreakPoints AS A
    ) AS X
GROUP BY Grupa

      Grupa           UkupanIznos BrojNarudzbi
----------- --------------------- ------------
          1               1000.00            5
          2                400.00            2
          3                775.00            4
          4                800.00            4

(4 row(s) affected)

Izracunali smo dakle i vise nego sto se trazilo zadatkom - sume za sve smene, ne samo za poslednju. Ako ste uvek izracunavali samo poslednju smenu, pa to upisvali u neku tabelu, vise nema potrebe za tim - poslednji SELECT vam to daje svaki put. Izracunate vrednosti ionako ne treba cuvati u tabelama :-)


U praksi, sve moze da se strpa u jedan jedini SELECT i da se to cuva ko VIEW, a moze i u delovima (Ako koristite Access, morace u delovima )

Moze li ovo nekako jednostavnije?
 
Odgovor na temu

sasas
Saša Slavnić
radim za neke švabe

Član broj: 35478
Poruke: 617
*.dynamic.sbb.co.yu.



Profil

icon Re: SQL upit - suma poslednje smene31.08.2007. u 15:12 - pre 202 meseci
Ako bi se zadržali na prvobitnoj postavci (da se traži samo poslednja smena), moglo bi ovako:

Code:

SELECT Smena, SUM(Iznos), COUNT(*) AS BrojNarudzbi FROM Z_Primer 
WHERE DatumVreme >
    (
        SELECT MAX(DatumVreme) AS DV FROM Z_Primer
        WHERE SMENA <>
        (
            SELECT Smena FROM Z_Primer 
            WHERE DatumVreme = (SELECT MAX(DatumVreme) FROM Z_Primer)
        )
    )
GROUP BY Smena


Iako ovo gore izgleda komplikovano, u stvari nije :) Da malo objasnim ideju:

Prvo ćemo naći koja je to poslednja smena - dakle koja smena ima MAX(DatumVreme):

Code:

            SELECT Smena FROM Z_Primer 
            WHERE DatumVreme = (SELECT MAX(DatumVreme) FROM Z_Primer)


Zatim je potrebno naći smenu koja je pretposlednja, dakle ima MAX(DatumVreme), a da nije ova koju smo prethodno našli:

Code:

        SELECT MAX(DatumVreme) AS DV FROM Z_Primer
        WHERE SMENA <>
        (
            SELECT Smena FROM Z_Primer 
            WHERE DatumVreme = (SELECT MAX(DatumVreme) FROM Z_Primer)
        )


Dakle, iz gornjeg upita imamo poslednje vreme pretposlednje smene. Ostaje samo da sumiramo zapise sa vremenom većim od pronađenog (to je onaj upit na vrhu). Za testne podatke sam dobio tačan rezultat, valjda bi stvar trebala da radi.


poz.

When something is hard to do, then it's not worth doing.
 
Odgovor na temu

Zidar
Canada

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



+79 Profil

icon Re: SQL upit - suma poslednje smene31.08.2007. u 15:38 - pre 202 meseci
Nemam reci. Za mene je Sasino resenje jednostavno briljantno. Svaka cast
 
Odgovor na temu

BigFoot
Boban Jovanović
Arilje

Član broj: 1098
Poruke: 991
*.dynamic.sbb.co.yu.



+35 Profil

icon Re: SQL upit - suma poslednje smene31.08.2007. u 17:57 - pre 202 meseci
Dakle, imamo konačno rešenje! Čestitam i hvala svima. Uskoro novi, još zanimljiviji problem. Samo da lepo formulišem... ;-)

PS: Evo i male modifikacije koja pronalazi Smenu poslednjeg unosa, a izgleda mi nešto jednostavnije od ponudjenog rešenja i ima jedan ugnježdeni SELECT manje:

Code:
SELECT Smena, SUM(Iznos), COUNT(*) AS BrojNarudzbi FROM Z_Primer 
WHERE DatumVreme >
    (
        SELECT MAX(DatumVreme) AS DV FROM Z_Primer
        WHERE Smena <> (SELECT Smena FROM Z_Primer ORDER BY DatumVreme DESC LIMIT 1)
    )
GROUP BY Smena

Two beer or not two beer...
 
Odgovor na temu

Zidar
Canada

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



+79 Profil

icon Re: SQL upit - suma poslednje smene31.08.2007. u 18:52 - pre 202 meseci
Sto se tice BigFootovog poboljsanja, slazem se da se pise jedan subkveri manje. Pitanje je samo generalno da li je takav nacin odredjivanja poslednje smene efikasniji od Sasinog. Nekako mi se cini da je odredjivanje MAX() brze nego SELECT TOP 1 ... ORDER BY. No nema veze, mozd i tako, a na realnom sistemu neka svako proba i proveri execution plan ako mu je sporo :-)

Inace, prevod BigFoot resenja u MS SQL glasi

Code:

SELECT Smena, SUM(Iznos), COUNT(*) AS BrojNarudzbi 
FROM Z_Primer 
WHERE DatumVreme >
    (
        SELECT MAX(DatumVreme) AS DV FROM Z_Primer
        WHERE Smena <> ( SELECT TOP 1 Smena        --- Poslednja smena
                        FROM Z_Primer 
                        ORDER BY DatumVreme DESC)
    )
GROUP BY Smena

Edit: Sređivanje teme

[Ovu poruku je menjao chachka dana 04.09.2007. u 20:09 GMT+1]
 
Odgovor na temu

srki
Srdjan Mitrovic
Auckland, N.Z.

Član broj: 2237
Poruke: 3654
*.maxnet.net.nz.



+3 Profil

icon Re: SQL upit - suma poslednje smene16.03.2013. u 18:03 - pre 135 meseci
Dugo nisam bio aktivan na forumu pa sam resio procitam mozgalice koje ste postavljali i hteo bih malo da doprinesem ako se ne ljutite da radim bump starih tema.

Code:
select sum(iznos) keep (dense_rank first order by obrnut_redni_broj_smene)
from
(
select DatumVreme, iznos, count(promena_smene) over (order by DatumVreme desc) obrnut_redni_broj_smene

  from
  (
    SELECT DatumVreme, Iznos, case when smena<>lag(smena) over (order by DatumVreme desc) then 1 else null end as promena_smene
    FROM Z_Primer
  )
) 


U ovom resenju se samo jednom prolazi kroz tabelu Z_Primer umesto 3 puta kao sto je u prethodnim resenjima. Resenje radi u Oraclu.
Mozete da se poigrate sa resenjem na http://www.sqlfiddle.com/#!4/854b4/22

Cilj mi je da sto vise ljudi krene da koristi analiticke funkcije jer sam primetio da jako mali broj ljudi iskoriscava njihov pun potencijal. To ume da bude dosta frustrirajuce na poslu kada citam queries koji su drugi pisali jer analiticke funkcije dosta ubrzavaju izvrsavanje queryja jer se tabela cita samo jednom.

Ovaj primer je dosta dobar jer se koristi vise vrsta analitickih funkcija i lag i count over a koristi se i direktiva keep dense rank tako da ako ljudi razumeju ovaj primer bice im mnogo lakse da kasnije koriste te stvari na jednostavnijim problemima.
 
Odgovor na temu

Zidar
Canada

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



+79 Profil

icon Re: SQL upit - suma poslednje smene18.03.2013. u 14:20 - pre 134 meseci
Zahvaljujem Srdjanu na resenju i dobrodosao nazad na forum

Kad sam jutros video novi post na temi, krenuo sam odpocetka da citam - iz nas sat sam se zapitao? Boze, zasto je ovo ovako komplikovano? Pale su mi napamet analitocke funkcije, na trenutak. Kad sam procitao tvoj post, zapitao sam se zasto se toga nismo onda setili. Tema je iz 2008. U MS SQL, analiticke funkcije su se pojavile u verziji 2005, koju ja nisam odmah imao , pa verovatno u tom momentu i nisam znao da stvari mogu biti jednostavnije i brze. Gde ja radim nove verzije se uvode prilicno sporo, 2005 smo uveli negde 2007-08, danas smo jos uvek na 2005, 2008 cemo preskociti i do kraja godine poceti prelazak na 2012.

U svakom slucaju, hvala na postu. Bilo bi u stvari jako dobro kad bismo imali vremena d aprodjemo kroiz stare mozgalice i vidimo da li bi se neke od njih mogle resiti elegantnije - manje koda i da brze rade, tek da vidimo uticaj razvoija tehnologije.

Zahvaljujem jos jednom i dobrodosao nazad na forum
 
Odgovor na temu

[es] :: Baze podataka :: SQL upit - suma poslednje smene

[ Pregleda: 4940 | Odgovora: 10 ] > FB > Twit

Postavi temu Odgovori

Srodne teme
Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.