SQL dotaz pro statistiku / kalendářová tabulka? rubrika: Databáze: SQL

3 Fos4
položil/-a 16.11.2016
 
upravil/-a 16.11.2016

Ahoj všichni,

řeším jednu statistiku a zajímalo by mně jakým způsobem byste vytáhli data z databáze (v mém případě MySQL). Oč jde:

Zákazník přijde reklamovat/servisovat zboží. Do tabulky servis si uložím případ a eviduji tu dvě data: datum založení a datum ukončení.

Nyní bych chtěl zobrazit, kolik bylo průměrně otevřených těchto případů za určité časové období a vůbec vývoj, jestli se počet servisu snižuje nebo navyšuje..
Např.: 11.10.2016 jsme měli otevřených 100 servisních případů. Za říjen 2016 to bylo v průměru 80 případů atd...

Napadli mně dvě varianty jak to udělat, ale ani jedna se mně nelíbí:

  1. Vytvořit tabulku "kalendar" s jedním sloupcem kde budou před-generované dny (dotaz nelze optimalizovat, mysql neumí 2x range condition). 
    • SELECT calendar.date, COUNT(servis.id)
FROM calendar
JOIN servis ON (servis.beginDate <= calendar.date AND servis.endDate >= calendar.date)
GROUP BY calendar.date /* nebo mesic, rok...*/
  2. Ukládat si každý den otevřené případy do nové tabulky (toto mám teď, je to více dat, více práce o udržování aktuálních dat, ale je to rychlé) 
    • INSERT INTO stats_servis (SELECT CURDATE(), servis.id FROM servis WHERE servis.otevreno = '1')
    • SELECT stats_servis.date, COUNT(DISTINCT stats_servis.service_id)
FROM stats_servis
GROUP BY stats_servis.date /* mesic...rok*/

Jak by jste postupovali vy? Zvolili by jste např. úplně jinou databázi? Jde mně o obecný princip, jak efektivně vytáhnout data z databáze pokud jde o nějaký vývoj otevřených servisů, objednávek, kusů na skladě apod...

Díky všem

odkaz Vyřešeno
3 Marek Tůma
odpověděl/-a 18.11.2016
 
upravil/-a 18.11.2016

skvela otazka,resil bych pomoci PostgreSQL

1) selectnout dny ktere potrebuji pomoci selectu z time series (https://www.postgresql.org/docs/9.1/static/functions-srf.html) a joinout s pripady kde otevreno je mensi nez aktualni radek AND (zavreno IS NULL or zavreno je vetsi nez aktualni radek)

2) vytvorit si nad timto joinem view

3) kdyz potrebuji statistiku delat selecty nad timto view group by month, year...

nebo - primo na tohle jsou delane window funkce (PostgreSQL, MS T-SQL),
https://www.postgresql.org/docs/9.1/static/tutorial-window.html
http://stackoverflow.com/questions/14113469/generating-time-series-betwe...

docela pekne si s tim hraje autor tady
http://no0p.github.io/postgresql/2014/05/08/timeseries-tips-pg.html

rozhodne bych data ukladal v co nejcistsi podobe a agregaci resil potom pomoci databazovych funkci (agregovana data uz nikdy zpet "nedeagregujete")

Komentáře

  • bretislav.wajtr : Presne tak... jakmile clovek zacne chtit neco vic nez zaklad, tak zacne byt MySQL na obtiz... PostgreSQL doporucuji... Btw jen abych to upresnil - pan Tuma v podstate navrhuje reseni cislo jedna s tim, ze neni treba vytvaret tabulku s kalendarem, ale je mozne si ji vygenerovat pomoci funkce primo v SQL selectu napriklad takto: generate_series('2016-1-1'::date, '2016-1-31'::date,'1 day'). Ja bych to resil uplne stejne - pokud by dat bylo hodne a chtel bych k tem prumerum pristupovat rychle, vytvoril bych materialized view ktere bych napr jednou denne nechal prepocitat... — 18.11.2016
  • Fos4 : Díky za odpověď, podívám se na odkazy, vypadá to velmi dobře, přesně něco takového bych potřeboval, libí se mně to... ...na druhou stranu se mně MySQL přestává líbit, začínám narážet na jeho limity...projekt běží více než 10let a poslední dva tři roky to začíná být s mysql už otravné... Proč to celé řeším: ve firmě používáme nově hodně statistik, něco ve stylu google analytics, zaměstnanec si zvolí statistiku (v tomto případě servisní případy), zvolí datum (případně srovnání), zvolí metriku (počet, cena, zisk apod.) dále dimenze (středisko, typ úkonu, kategorii produkt...) a už může studovat - tady jsou limity MySQL hodně citelné a musí se to vše složitě optimalizovat nebo večer přepočítávat... — 21.11.2016
  • Marek Tůma : Vyhoda toho selectu z time series je prave v tom, ze si nepotrebuji predgenerovavat tu tabulku s aktualnimi daty (byva tam problem s generovanim prestupnych roku a tak). Nicmene pro ekonomicke vyuziti se stejne nevyhnete spravovani tabulky se statnimi svatky v cilove zemi (takova data se nepocitaji do pracovnich dni, limitu reseni reklamace a podobne). Pokud potrebujete delat vice operaci v radku nad stejnymi daty (napriklad count, sum, average) tak skutecne stoji za to si nastudovat ty window funkce. — 21.11.2016

Pro zobrazení všech 4 odpovědí se prosím přihlaste:

Rychlé přihlášení přes sociální sítě:

Nebo se přihlaste jménem a heslem:

Zadejte prosím svou e-mailovou adresu.
Zadejte své heslo.

Aktuální otázky v rubrice Databáze: SQL

Otázky uživatele Fos4