2012. augusztus 18. 08:28:12

Címkék: mysql, tábla, lekérdezés, tárolt eljárás, ideiglenes tábla, csoportosítás

Mikor említésre került, már akkor érdekesnek tűnt, hogy is lehet megoldani a feladatot, így hát nekiláttam megkeresni a megoldást. Csoportosítás, tárolt eljárás meg néhány lekérdezés. Erről olvashatsz itt egy rövid bejegyzést.

---

Nemrég egy jóbarát kérdezte meg az alább leírt feladat megvalósítását, és mivel maga a kérdés érdekesnek tűnt, úgy éreztem érdemes rászánni egy kis időt és rendesen megoldani.

Feladat: adott egy news tábla, amiben mint neve is mutatja, hírek vannak. Írassuk ki a híreket dátum szerint csoportosítva, de úgy, hogy egy megadott naptól kezdve az összes napot felsoroljuk, azokat is, ahol nincs hír.

Kezdjük a legelején. Van a news táblánk:

CREATE TABLE news (
  id int(11) NOT NULL AUTO_INCREMENT,
  content text COLLATE utf8_unicode_ci,
  created datetime DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

E táblában tegyünk fel van 100 sor, ami az elmúlt 200 nap újdonságait öleli fel. No már most, az alapfeladat nem nehéz, ki kell íratni a híreket csoportosítva, tegyük is meg:

SELECT DATE(created), count(*) FROM news GROUP BY 1;

Hm, mint várható volt, a lekérdezés a következő sorokat adja vissza – persze ez csak részlet, nem akarom mind a 100 találatot kiírni :)

Dátum	Darabszám
2012.04.29	1
2012.06.09	3
2012.07.01	1
2012.08.02	2

Egy kicsit hézagos, de első próbálkozásnak jó volt. Hogyan tudnánk kiíratni minden napot, hogy betömjük a szakadékokat és szépen sorba legyenek a napok felsorolva? Ebben segíthet a józan ész, vagy maga a google.

Ugye az alapgondolatként ez jut eszünkbe, hogy count(*) és group by, mint ahogy az előbb is próbáltuk. A folytatásként talán az, hogy akkor majd segít a PHP, vagy bármi egyéb programozási nyelv nekünk, ha úgyis meg akarjuk jeleníteni majd egy listaként. Igen, a PHP segíthetne... de nem akar. Olyannyira nem, hogy jóformán nincs is számunkra megfelelő függvény. Persze aki nagyon szereti püfölni a klaviatúrát, annak valószínűleg hasznosak a date() és a mktime() függvények. Mindenesetre ezeket én most kihagynám. Bár a mktime()-mal megoldható lenne a dolog, de próbáljunk inkább arra koncentrálni, hogyan oldhatnánk meg ezt a MySQL-en belül, dolgozzon ő helyettünk, illetve a PHP helyett.

Némi keresgélés után találunk is pár stackoverflow-s hivatkozást, valamint – mint ahogy az egyik kommentelő meg is jegyzi – életem legátláthatatlanabb lekérdezését. Íme a két link:
http://stackoverflow.com/questions/2157282/generate-days-from-date-range
http://stackoverflow.com/questions/4902297/group-by-day-and-still-show-days-without-rows

De ha nagyon szeretném, akkor még tucatnyi hasonló linket másolhatnék be. Ez csak kiindulási alap.

És itt az ominózus lekérdezés is:

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-01' and now()

Hogy mi is ez? Abba inkább nem mennék bele, nem kezdtem el fejtegetni, de mi tagadás, működik :) Ha valaki ezt akarja használni, akkor egészségére, én ettől eltekintenék és keresnék egy igazi megoldást. Főleg úgy, hogy a futási ideje megközelíti a 0.1 mp-et, persze localhoston [nem egy csúcskategóriás gépről beszélünk].

Megoldás:

Tárolt eljárások. Ilyen röviden és egyszerűen. Hasznos kis kiegészítői a mysql-nek, érdemes is utánaolvasni és használni, ha valaki nem a PHP kódját akarja teleirkálni mindenféle extra lekérdezéssel, illetve jelen esetben a mktime() használatával.

Hogy mire is jók ezek a tárolt eljárások? Lényegében megkönnyítik munkánkat és az SQL-re bízzuk azokat a feladatokat, amik valójában rá is tartoznának, ezáltal megkíméljük a szervert is, valamint nem írjuk tele a programunkat mindenféle olyan lekérdezésekkel, amiknek végül is semmi keresnivalójuk ott. Így rövidebb és még olvashatóbb kódunk lesz, sőt az SQL gyorsabban végre is hajtja ezeket a feladatokat. Ha szükségünk van 1-1 adatra valamelyik táblából vagy meg akarunk változtatni egy-egy sort valamelyik táblában, vagy akár összetettebb műveleteket akarunk végezni, megoldhatjuk tárolt eljárásokkal.

Alapjában nem nehéz tárolt eljárást írnunk, csupán ennyi az egész:

CREATE PROCEDURE get_fruit() SELECT 'apple';

A fenti CREATE parancs létrehozza nekünk az eljárást, mely lényegében egy egyszerű SELECT, mely az 'apple'-lel adja vissza nekünk. Persze ezt lehet bonyolítani és hosszabb kódsorokat írni, mint amit nemsokára meg is írunk. Az elkészült eljárásunkat az alábbi módon hívhatjuk meg:

call get_fruit();

Némi próbálgatás után sikerült is összerakni egy olyan eljárást, mely a leírt feladatnak megfelelően működik és elkészíti azt az ideiglenes táblát, melyben x időponttól kezdve a mai napig tartalmazza a napokat. Próbálok egy rövid kis magyarázatot adni mindegyik sorhoz.

DELIMITER $$

A delimiter megadása, megváltoztatása azért szükséges, mert futás közben a pontosvesszőt tekintené az eljárás végének és remek kis hibaüzenettel térne vissza.

CREATE PROCEDURE create_date_list()

Magát az eljárást nevezzük nevén

BEGIN

Elvileg nem kötelező elem, amennyiben egy utasítást írunk az eljáráson belül, de ha több utasítást írunk, oda kell tennünk.

    DECLARE start DATE DEFAULT '2012-01-01';

Megadunk egy változót és beállítjuk annak értékét. Ezt lehetne paraméterként is átadni, talán úgy még szebb lenne a kód maga.

    DECLARE end DATE DEFAULT NOW();

Egy másik változó, mely a mai napot tartalmazza.

  create temporary table timedates (dater date primary key);

Létrehozunk egy ideiglenes táblát melynek csupán egy oszlopa van, amibe majd a dátumok kerülnek.

  WHILE end >= start DO

Egy ciklust használva bejárjuk a napokat

    insert into timedates VALUES (start);

és beszúrjuk az értékeket a táblánkba

    SET start = ADDDATE(start, INTERVAL 1 DAY);

Megváltoztatjuk a start változónkat úgy, hogy hozzáadunk +1 napot, mennyivel egyszerűbb így hozzáadni egy napot.

  END WHILE;

Lezárjuk a ciklusunkat

END $$

Az eljárást lezáró rész a kihagyhatatlan $$-ral

DELIMITER ;

Amennyiben további lekérdezések követnék az eljárást, visszaállítjuk a pontosvessző lezárást.

És az egész egyben, immár módosítva, hogy paraméterként adjuk át a dátumot, ezáltal könnyebben módosítható a kezdő dátum:

DELIMITER $$
CREATE PROCEDURE create_date_list(IN start_time DATE)
BEGIN
  
    DECLARE start DATE DEFAULT start_time;
    DECLARE end DATE DEFAULT NOW();
  create temporary table timedates (dater date primary key);

  WHILE end >= start DO
    insert into timedates VALUES (start);
    SET start = ADDDATE(start, INTERVAL 1 DAY);
  END WHILE;
  
END $$
DELIMITER ;

Mivel az eljárásunk már kész van és bevethető, most már csak a hozzá tartozó lekérdezést kell összeállítanunk. Első próbálkozásnak összekapcsoltam a két táblát.

call create_date_list('2012-01-01');
SELECT count(*), t.dater 
FROM news n 
RIGHT JOIN timedates t 
ON DATE(n.created) = t.dater GROUP BY 2 ORDER BY 2;

Maga a futási idő az 0.01mp körül volt nálam, ám a probléma az, hogy maga a lekérdezés nem jó. Hibás sorokat kapunk vissza. Bár az összes napot visszakapjuk, a helyzet az, hogy minden naphoz tartozik egy hír a lekérdezés szerint. Hogy is van ez? Ha alaposabban megnézzük a lekérdezést és nem csoportosítjuk, akkor kiderül, hogy a MySQL azokhoz a napokhoz, melyekhez nem tartozik hír, olyan sorokat rendel a news táblánkból, ami NULL-os mezőket tartalmaz.

Dátum	Darabszám
2012.08.01	1
2012.08.02	2
2012.08.03	1
2012.08.04	1

Ha egy kicsit módosítjuk az előbbi elképzelést úgy,hogy belső lekérdezést tartalmazzon, akkor ez a query már működik, ám közel 4x lassabbnak tűnik az előzőnél, legalábbis localhoston elsőre 0.04 mp alatt futott le, persze csak ez adja vissza a helyes eredményt.

call create_date_list('2012-01-01');
SELECT *, (SELECT count(*) FROM news 
		   WHERE DATE(created) = dater) as news_cnt 
FROM timedates ORDER BY dater;

Tehát jelen esetben a lekérdezésünk már csak egy eljáráshívásból és egy lekérdezésből áll. Csak ne feledjük meghívni az eljárásunkat, mert anélkül hibát fog dobni az SQL, ugyanis a timedates tábla csak ideiglenes, amit az eljárás készít elő.

Az alapokban tehát nincs semmi bonyolult, ha pedig ez már megvan, még némi utánajárás és bármilyen bonyolult eljárást is képesek vagyunk megírni.

További szép napot és jó munkát! :)