MySQL SELECT i grupiranje po vremenskom razdoblju ... od 01:00 - 02:00 pa 02:00-03:00 ....

MySQL SELECT i grupiranje po vremenskom razdoblju ... od 01:00 - 02:00 pa 02:00-03:00 .... 13.10.2011. u 10:17 · pre 183 meseca #1

Pozdrav forumasima.

Trazio sam po forumu ali nisam uspio naci nesto slicno.

Dalke molim malu pomoc oko jednog upita.

Imam tablicu doc u koju se,izmedu ostaloga, upisuje vreme kreiranja dokumenta u polje timestamp. Sad bi trebao report koji bi mi prikazao koji su dokumenti, tj. SUM(kolicina * cijena) bili u vremenskim periodima.

Recimo od 13:00 - 14:00 toliko i toliko, od 14:00 - 15:00 toliko .......

Neki obican upit u tablu izgleda

Code:

SELECT * FROM doc
INNER JOIN adoc ON doc.doc_id = adoc.doc_id
WHERE ......

gdje je doc glava dokumenta

CREATE TABLE doc (
doc_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
broj_doc INT(10) UNSIGNED NOT NULL,
timest TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
partneri_partner_id INT(10) UNSIGNED NOT NULL,
prodavac_prodavac_id INT(10) UNSIGNED NOT NULL,
nplacanja_nplacanja_id INT(10) UNSIGNED NOT NULL,
vrsta_dokumenta INT(10) UNSIGNED NOT NULL,
src_skl INT(10) UNSIGNED NOT NULL DEFAULT '1',
dest_skl INT(10) UNSIGNED NOT NULL DEFAULT '1',
........
....

a adoc tabla stavke po doc-u

CREATE TABLE adoc (
adoc_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
artikli_artikl_id INT(10) UNSIGNED NOT NULL,
kolicina DECIMAL(10,2) NOT NULL,
cijena DECIMAL(10,2) NOT NULL,
rabat DECIMAL(10,5) NOT NULL,
doc_id INT(10) UNSIGNED NOT NULL,
timest TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
ncijena DOUBLE(10,2) NOT NULL,
pstopa DOUBLE(10,2) UNSIGNED
...
......
........

Unaprijed zahvaljujem

lp

Re: MySQL SELECT i grupiranje po vremenskom razdoblju ... od 01:00 - 02:00 pa 02:00-03:00 .... 13.10.2011. u 11:21 · pre 183 meseca #2

Rijesio sam.

Evo ako nekog zanima kako.

Code:

SELECT
SUM(CASE WHEN TIME(doc.timest) BETWEEN '00:00' AND '01:00' THEN adoc.kolicina * adoc.cijena END) as '00-01',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '01:00' AND '02:00' THEN adoc.kolicina * adoc.cijena END) as '01-02',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '02:00' AND '03:00' THEN adoc.kolicina * adoc.cijena END) as '02-03',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '03:00' AND '04:00' THEN adoc.kolicina * adoc.cijena END) as '03-04',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '04:00' AND '05:00' THEN adoc.kolicina * adoc.cijena END) as '04-05',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '05:00' AND '06:00' THEN adoc.kolicina * adoc.cijena END) as '05-06',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '06:00' AND '07:00' THEN adoc.kolicina * adoc.cijena END) as '06-07',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '07:00' AND '08:00' THEN adoc.kolicina * adoc.cijena END) as '07-08',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '08:00' AND '09:00' THEN adoc.kolicina * adoc.cijena END) as '08-09',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '09:00' AND '10:00' THEN adoc.kolicina * adoc.cijena END) as '09-10',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '10:00' AND '11:00' THEN adoc.kolicina * adoc.cijena END) as '10-11',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '11:00' AND '12:00' THEN adoc.kolicina * adoc.cijena END) as '11-12',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '12:00' AND '13:00' THEN adoc.kolicina * adoc.cijena END) as '12-13',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '13:00' AND '14:00' THEN adoc.kolicina * adoc.cijena END) as '13-14',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '14:00' AND '15:00' THEN adoc.kolicina * adoc.cijena END) as '14-15',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '15:00' AND '16:00' THEN adoc.kolicina * adoc.cijena END) as '15-16',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '16:00' AND '17:00' THEN adoc.kolicina * adoc.cijena END) as '16-17',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '17:00' AND '18:00' THEN adoc.kolicina * adoc.cijena END) as '17-18',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '18:00' AND '19:00' THEN adoc.kolicina * adoc.cijena END) as '18-19',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '19:00' AND '20:00' THEN adoc.kolicina * adoc.cijena END) as '19-20',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '20:00' AND '21:00' THEN adoc.kolicina * adoc.cijena END) as '20-21',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '21:00' AND '22:00' THEN adoc.kolicina * adoc.cijena END) as '21-22',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '22:00' AND '23:00' THEN adoc.kolicina * adoc.cijena END) as '22-23',
SUM(CASE WHEN TIME(doc.timest) BETWEEN '23:00' AND '24:00' THEN adoc.kolicina * adoc.cijena END) as '23-24' From doc INNER JOIN adoc ON doc.doc_id = adoc.doc_id

Re: MySQL SELECT i grupiranje po vremenskom razdoblju ... od 01:00 - 02:00 pa 02:00-03:00 .... 13.10.2011. u 11:26 · pre 183 meseca #3

Ovo je OK samo problem može da bude BETWEEN koji uključuje i granice. To znači da će nešto što se desilo u 09:00:00 ući u dva sum-a. Bolje je napisati umesto between >= i < tako da se obezbediš da gornja i donja granica zadovolje samo jedan uslov.

Re: MySQL SELECT i grupiranje po vremenskom razdoblju ... od 01:00 - 02:00 pa 02:00-03:00 .... 13.10.2011. u 11:39 · pre 183 meseca #4

Još bolje rešenje

Code (sql):

SELECT HOUR(doc.timest), SUM(adoc.kolicina*adoc.cijena)

FROM doc INNER JOIN adoc ON doc.doc_id = adoc.doc_id

GROUP BY HOUR(doc.timest)

ili da upodrebiš hour() funkciju u CASE izrazu.

Re: MySQL SELECT i grupiranje po vremenskom razdoblju ... od 01:00 - 02:00 pa 02:00-03:00 .... 13.10.2011. u 11:47 · pre 183 meseca #5

Hvala puno djoka. Ovo mi se cini puno elegantnije.

lp

Forum kao aplikacija na telefonu

Srodne teme