Autor Zpráva
Vakvas
Profil *
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
Pokud je aktivni, tak do každého..
Potrebuji počet aktivnich zakazek v kazdem mesici...

Moc dekuji !
juriad
Profil
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
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
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
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
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
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 12
Bez limitu tohle vygeneruje 16 řádků, kdybych přidal ještě jeden cross join bude výsledků 32:
CROSS JOIN
    (SELECT 1 v UNION SELECT 2) aux5
počet výsledků se exponenciálně zvětšuje: počet tabulek auxx vyjadřuje exponent a počet selectů spojených uniony v subdotazu je základ, kdybych teda v příkladu přidal do subselectů union 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
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
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 DESC

Jinak 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
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
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
Vygenerování této neuložené tabulky dotaz znepřehledňuje. Osobně bych si udělal tabulku kalendar_mesice a tam měl takováto vygenerovaná data třeba pro roky 1990-2100 (je možné je doplňovat z obou stran). Může tam být i samostatný sloupec rok, měsíc a třeba i příznak, účetní uzavřenosti. Pokud je to kompletnější systém, může se taková tabulka hodit i pro jiné sestavy.

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
To je přesně ono .... a už i chápu..
Mockrát dekuji !!!

Vaše odpověď

Mohlo by se hodit

Odkud se sem odkazuje


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: