| Autor | Zpráva | ||
|---|---|---|---|
| Vakvas Profil * |
#1 · Zasláno: 5. 12. 2014, 09:01:09
Zdravím a prosím o radu,
mam tabulku se zakazkami, kde je sloupec OD a DO (date) a ja potrebuji vytahat počet aktivnich po mesicich za poslední rok. Tedy posledních 12 mesicu. Vystup by mel byt tedy napr. Listopad 558 Prosinec 555 Leden 568 Unor 568 Brezen 570 Duben 569 .... dle ukoncenych ci nove zalozenych zakazek. Jiste me napadaji subselecty pro jednotlive mesice, ale ja potrebuji proste posledních 12 mesicu. Moc dekuji! |
||
| juriad Profil |
Pokud zakázka zasahuje do více měsíců, třeba čtyřech, má se započítávat čtyřikrát? Nebo jen jednou (do kterého měsíce?)
|
||
| VaKvas Profil |
#3 · Zasláno: 5. 12. 2014, 09:12:32
Pokud je aktivni, tak do každého..
Potrebuji počet aktivnich zakazek v kazdem mesici... Moc dekuji ! |
||
| juriad Profil |
#4 · Zasláno: 5. 12. 2014, 09:48:27
Do měsíce se započítává, pokud začal v předchozí rok nebo předchozí nebo stejný měsíc ve stejný rok a zároveň skončil následující rok nebo následující nebo stejný měsíc ve stejný rok.
Pro jeden jediný rok je to takto. Rozšíření na více roků by spočívalo jen v přidání crossjoinu se sekvencí roků, nahrazení proměnné $rok za sloupec roky.rok a přidání roky.rok do vybírací a grupovací klauzule selectu. SELECT mesice.mesic,
COUNT(zakazky.id) AS pocet
FROM (SELECT 1 AS mesic
UNION ALL
SELECT 2 AS mesic
UNION ALL
SELECT 3 AS mesic
UNION ALL
SELECT 4 AS mesic
UNION ALL
SELECT 5 AS mesic
UNION ALL
SELECT 6 AS mesic
UNION ALL
SELECT 7 AS mesic
UNION ALL
SELECT 8 AS mesic
UNION ALL
SELECT 9 AS mesic
UNION ALL
SELECT 10 AS mesic
UNION ALL
SELECT 11 AS mesic
UNION ALL
SELECT 12 AS mesic) AS mesice
LEFT JOIN zakazky
ON ( YEAR(zakazky.od) < $rok
OR YEAR(zakazky.od) = $rok
AND MONTH(zakazky.od) <= mesice.mesic )
AND ( YEAR(zakazky.do) > $rok
OR YEAR(zakazky.do) = $rok
AND MONTH(zakazky.do) >= mesice.mesic )
GROUP BY mesice.mesic |
||
| VaKvas Profil |
#5 · Zasláno: 5. 12. 2014, 10:01:47
Vypada to dobře, jen to asi není uplne ono.. nebo jsem to zcela nepobral :-)
Zakazky jsou nekdy zalozeny v roce 1998 a stále jedou, tedy jsou aktivni, nemaji vyplnene datum DO (to jsem nezminil, omlouvam se). A pak se nemuzu vazat na rok, ale na posledních 12 mesicu... Tedy dotaz by asi mel obsahovat nejaky between, pro jednotlive mesice.. nebo? Moc dekuji! |
||
| juriad Profil |
#6 · Zasláno: 5. 12. 2014, 10:07:36 · Upravil/a: juriad
VaKvas:
Aha, takže ony existují neuzavřené zakázky. A také jsem myslel, že jde o jeden kalendářní rok (od ledna do prosince, nikoli posledních 12 měsíců). Proto tam je ta konstanta $rok, kterou bys měl doplnit. Mysql bohužel neumí generovat posloupnosti, proto je tam ten šílený select, ale budiž. Zkusím to upravit. Celou první část (tedy ty měsíce a roky) budeš muset vygenerovat v PHP (nebo jaký jazyk používáš). Takto by vypadal ten dotaz v květnu 2014. Between nelze použít, ten funguje s jednou hodnotou, ty zde máš dvě, které jsou na sobě závislé (rok a měsíc). Navíc between ti nebude fungovat z důvodu toho, že nějaké zakázky končí 1. v měsíci a jiné začínají 30. a všechny chceš započítat do stejného měsíce. Potřeboval bys tedy zrušit dny. Šlo by sice porovnávat řetězce "rok-měsíc", ale to nevypadá moc efektivně. SELECT mesice.mesic,
mesice.rok,
COUNT(zakazky.id) AS pocet
FROM (SELECT 2013 AS rok,
6 AS mesic
UNION ALL
SELECT 2013 AS rok,
7 AS mesic
UNION ALL
SELECT 2013 AS rok,
8 AS mesic
UNION ALL
SELECT 2013 AS rok,
9 AS mesic
UNION ALL
SELECT 2013 AS rok,
10 AS mesic
UNION ALL
SELECT 2013 AS rok,
11 AS mesic
UNION ALL
SELECT 2013 AS rok,
12 AS mesic
UNION ALL
SELECT 2014 AS rok,
1 AS mesic
UNION ALL
SELECT 2014 AS rok,
2 AS mesic
UNION ALL
SELECT 2014 AS rok,
3 AS mesic
UNION ALL
SELECT 2014 AS rok,
4 AS mesic
UNION ALL
SELECT 2014 AS rok,
5 AS mesic) AS mesice
LEFT JOIN zakazky
ON ( YEAR(zakazky.od) < mesice.rok
OR YEAR(zakazky.od) = mesice.rok
AND MONTH(zakazky.od) <= mesice.mesic )
AND ( zakazky.do IS NULL
OR YEAR(zakazky.do) > mesice.rok
OR YEAR(zakazky.do) = mesice.rok
AND MONTH(zakazky.do) >= mesice.mesic )
GROUP BY mesice.rok,
mesice.mesic
ORDER BY mesice.rok,
mesice.mesic |
||
| VaKvas Profil |
#7 · Zasláno: 5. 12. 2014, 10:31:08
Jo tohle uz vypada skvele!
Takze si musim jev v php vybrat poslední rok .. je to tak? Asi to je tady off topic, ale napada te nejaka vhodna fukce? Budu si tedy muset do query poslat nejaky start mesic a rok... !? Moc diky !!! |
||
| Kajman Profil |
#8 · Zasláno: 5. 12. 2014, 10:37:46
Myslím, že to půjde i univerzálním dotazem. Ta císla 1 až 12 [#4] použít ke generování hranic posledních měsíců od a do v typu date. To pak s joinout se zakázkami (bez nutnosti month a year). Mělo by to mýt i svižnější.
|
||
| juriad Profil |
#9 · Zasláno: 5. 12. 2014, 10:38:30
Ty nepředáváš jen rok a měsíc, ale musíš vygenerovat celý ten select (řádky 4 až 38 závisí na aktuálním datumu).
Každopádně nějaký výčet měsíců v dotazu musíš mít, protože můžou existovat zakázky aktivní v nějakém měsíci, ale zároveň v takovém měsíci žádná zakázka nezačínala ani nekončila. Možná by šlo ten seznam měsíců vytvořit univerzálněji, ale o moc lepši by to nebylo. Snad by šlo použít toto místo toho poddotazu. Pozor toto počítá s aktuálním datumem a umí právě posledních 12 měsíců. Žádný parametr není třeba do dotazu doplňovat. SELECT m.mesic,
r.rok
FROM (SELECT 1 AS mesic
UNION ALL
SELECT 2 AS mesic
UNION ALL
SELECT 3 AS mesic
UNION ALL
SELECT 4 AS mesic
UNION ALL
SELECT 5 AS mesic
UNION ALL
SELECT 6 AS mesic
UNION ALL
SELECT 7 AS mesic
UNION ALL
SELECT 8 AS mesic
UNION ALL
SELECT 9 AS mesic
UNION ALL
SELECT 10 AS mesic
UNION ALL
SELECT 11 AS mesic
UNION ALL
SELECT 12 AS mesic) AS m
CROSS JOIN (SELECT YEAR(NOW()) # aktuální rok
UNION ALL
SELECT YEAR(NOW()) - 1) AS r # minulý rok
WHERE r.rok * 12 + m.mesic BETWEEN YEAR(NOW()) * 12 + MONTH(NOW()) - 11 AND
YEAR(NOW()) * 12 + MONTH(NOW()) |
||
| Virtus Profil |
Zdravím, takový malý trik ke generování sequencí v mysql, tady to sice nemá asi význam, ale pokud bych potřeboval vygenerovat nějakou delší sequenci, tak lze využít chování
cross join a pomocí klauzule LIMIT řídit kolik výsledků si vrátím, pokud je nepotřebuju všechny:
SELECT
@seq:=@seq + 1
FROM
(SELECT 1 v UNION SELECT 2) aux
CROSS JOIN
(SELECT 1 v UNION SELECT 2) aux2
CROSS JOIN
(SELECT 1 v UNION SELECT 2) aux3
CROSS JOIN
(SELECT 1 v UNION SELECT 2) aux4,
(SELECT @seq:=0) seq
LIMIT 12CROSS JOIN
(SELECT 1 v UNION SELECT 2) aux5union select 3 vygeneruje to 81 řádků,
případně lze použít místo proměnný @seq i tohle:
SELECT
conv(concat( aux.v, aux2.v, aux3.v, aux4.v ),2,10) -- conv(expr,2,10) = převeď výraz z binární soustavy do desítkový soustavy
FROM
(SELECT 0 v UNION SELECT 1) aux
CROSS JOIN
(SELECT 0 v UNION SELECT 1) aux2
CROSS JOIN
(SELECT 0 v UNION SELECT 1) aux3
CROSS JOIN
(SELECT 0 v UNION SELECT 1) aux4 |
||
| VaKvas Profil |
#11 · Zasláno: 5. 12. 2014, 11:30:34
Kajman:
Muzu poprosit o ukázku? To nevim, jak myslis.. Diky! juriad: Zkusim to.. princip jsem pochopil.. moc diky ! Samozrejme bych to chtěl mit celem jen v query, ale asi to vazne nepujde... |
||
| Kajman Profil |
#12 · Zasláno: 5. 12. 2014, 18:03:33
Něco takového
SELECT Year(hranice.od) rok,
Month(hranice.od) mesic,
Count(zakazky.od) zakazek
FROM (SELECT cisla.n,
Date_sub(tento.od, INTERVAL cisla.n-1 MONTH) od,
Date_sub(tento.od, INTERVAL cisla.n-2 MONTH) do
FROM (SELECT 1 AS n
UNION ALL
SELECT 2 AS n
UNION ALL
SELECT 3 AS n
UNION ALL
SELECT 4 AS n
UNION ALL
SELECT 5 AS n
UNION ALL
SELECT 6 AS n
UNION ALL
SELECT 7 AS n
UNION ALL
SELECT 8 AS n
UNION ALL
SELECT 9 AS n
UNION ALL
SELECT 10 AS n
UNION ALL
SELECT 11 AS n
UNION ALL
SELECT 12 AS n) AS cisla
CROSS JOIN (SELECT Date(Concat(Date_format(Curdate(), '%Y-%m'), '-01')) od) tento) hranice
LEFT JOIN zakazky
ON hranice.od <= Ifnull(zakazky.do, Curdate())
AND hranice.do > zakazky.od
GROUP BY hranice.n,
hranice.od
ORDER BY hranice.n DESCJinak v mysql může být neladnější místo spousty unionů a cross joinů jednoduše udělat pomocnou tabulku s čísly 1.. až kolik je třeba a tu pak na takovéto dotazy používat. |
||
| VaKvas Profil |
#13 · Zasláno: 7. 12. 2014, 22:16:48
Chapu, ze mam udelat tabulku hranice, kde pro tento dotaz bude jen jeden radek s datumem napr. 1.1.1998 ?
A tam v tom hlavnim selectu :Count(zakazky.od) zakazek ... nemá byt Count(zakazky.id) zakazek a ten GROUP taky id ? Nebo me něco uniklo? Moc dekuji !!!! |
||
| Kajman Profil |
#14 · Zasláno: 8. 12. 2014, 09:18:07
VaKvas:
„mam udelat tabulku hranice, kde pro tento dotaz bude jen jeden radek s datumem napr. 1.1.1998?“ Ne, hranice je pouze alias pomocné neexistující tabulky vniklé uniony a cross joinem. Výsledek by měl být v závislosti na aktuálním datumu něco jako n od do 1 2014-12-01 2015-01-01 2 2014-11-01 2014-12-01 3 2014-10-01 2014-11-01 ... 11 2014-02-01 2014-03-01 12 2014-01-01 2014-02-01 „Count(zakazky.od) zakazek ... nemá byt Count(zakazky.id) zakazek?“ Podle [#1] nebylo poznat, zda se sloupec id v tabulce zakazky vyskytuje. Pokud od nemůže být null, bude to dávat stejné výsledky. Pokud tam necháte od a bude na tabulce zakázky dvousloupcový index (od,do), tak si db vystačí se čtením toho indexu a nemusí data tabulky vůbec načítat. „a ten GROUP taky id?“ Groupovat podle zakazkky.id nedává smysl, pokud chcete zjistit počet zakázek. |
||
| VaKvas Profil |
#15 · Zasláno: 8. 12. 2014, 11:03:17
To je přesně ono .... a už i chápu..
Mockrát dekuji !!! |
||
|
Časová prodleva: 9 let
|
|||
0