Forum PHP.pl

Mam tabelę z danymi:

201501;100
201502;80
...

czyli rok i miesiąc (jako liczba) oraz jakaś wartość liczbowa. Kolumn w sumie jest nieco więcej i dla każdego miesiąca mam tak naprawdę ~1800 rekordów ale nie ma to większego znaczenia dla problemu. Potrzebuję teraz przedstawić dane w ujęciu tygodniowym tj. jaka wartość przypada na każdy tydzień roku. Problemem jest tutaj fakt, ze tygodnie mogą zachodzić na 2 miesiące. Czy jest jakiś sprytny sposób żeby to ogarnąć po stronie bazy?

Na chwile obecną tworzę tabelę tymczasową dla pojedynczych dni na zasadzie selectów połączonych unionami (mam więc 365 takich selectów). Dla każdego dnia wstawiam:
wartość dla miesiąca / liczba dni w miesiącu
a następnie korzystam z tej tabeli z użyciem funkcji week(). Może jest jakieś szybsze rozwiązanie?

W jaki sposób rozróżniasz jakie dane należą do jakiego tygodnia skoro w bazie danych masz informację jedynie co do roku i miesiąca?

Napisałem przecież: jeśli wartość dla stycznia to 310 to na jeden dzień stycznia przypada 10, do tabeli tymczasowej wstawiam więc 10 dla 2015-01-01, 2015-01-02, ..., 2015-01-31. Mając wartość rozbitą na dni mogę skorzystać z funkcji week(). Rozwiązanie niestety nie jest wybitnie wydajne z racji tabeli tymczasowej tworzonej przy każdym wyświetleniu raportu. Jeśli nikt nie zasugeruje niczego innego to chyba zrobię osobną tabelę z odpowiednimi indeksami zawierającą już rozbite dane. Tabela byłaby aktualizowana triggerem w momencie zasilania tabeli zagregowanej. Wydajność powinna być lepsza ale kosztem zdublowanie danych.

Właśnie o tego typu podpowiedź mi chodziło. Jutro będę kombinował z podejściem kalendarzowym, dzięki!

Można kombinować jeszcze z jednym zapytaniem, czyli podzielić stringa data i stworzyć datę na której da się użyć week.
Czyli coś takiego:

[SQL] pobierz, plaintext 
SELECT SUM(wartosc),  
	WEEK(
			CONCAT( 
			LEFT(SUBSTRING(data_str, 1, LOCATE(';', data_str)-1), 4) , 
			'-' , 
			RIGHT(SUBSTRING(data_str, 1,  LOCATE(';', data_str)-1), 2) , 
			'-',
			SUBSTRING(data_str,  LOCATE(';', data_str)+1, LENGTH(data_str)) / 10
			)
		)  AS tydzien
FROM test2
GROUP BY tydzien
[SQL] pobierz, plaintext 

Można przemyśleć na optymalizacją tego zapytania,a jak się będzie zachowywało przy większej liczbie rekordów to trzeba byłoby przetestować.

Ten post edytował DarkAbso 23.06.2015, 21:25:22

Chyba sie nie zrozumieliśmy. Średnik w przykładowych danych oddziela 2 kolumny. Rok i miesiąc mam zapisane w 1 kolumnie jako liczbę, a ta druga wartość to po prostu jakaś liczba przypisana do danego okresu. Idąc Twoim tokiem rozumowania chyba jednak nie będę w stanie nic sensownego osiągnąć. Tak czy inaczej dzięki za odzew.

Rzeczywiście nie dogadaliśmy się, a moje rozwiązanie traci sens. W takim razie rozwiązanie @trueblue będzie najrozsądniejsze. Jak by to była inna baza to ewentualnie można było by sobie stworzyć funkcję zwracającą tabelę kalendarz i do niej się JOIN'ować, ale niestety w Mysql to nie przejdzie.

Ten post edytował DarkAbso 23.06.2015, 22:09:55

Jeszcze można uprościć.
kalendarz wystarczy, że będzie zawierał tylko daty.
Warunek wtedy:

[SQL] pobierz, plaintext 
SUBSTRING(l.DATA,-2)=DATE_FORMAT(m.DATA,'%m')
[SQL] pobierz, plaintext 

Rozwiązanie ~trueblue spisuje się wybornie. Utworzyłem tabelę calendar_date_period z kolumnami:
date - data, np. 2015-01-01, klucz główny
period - okres czyli rokmiesiąc, np. 201501, indeks.
Joinuję do tej tabeli po kolumnie period, otrzymuję wszystkie dni danego okresu, a wartości traktuję poprzez:

[SQL] pobierz, plaintext 
SUM(wartosc / CAST(DAY(LAST_DAY(calendar_date_period.date)) AS UNSIGNED)) AS wartosc
[SQL] pobierz, plaintext 

Całość działa bardzo sprawnie. Trzeba tylko pamiętać by w tabeli calendar_date_period mieć odpowiedni zakres danych - ja wygenerowałem sobie do 2030-12-31

Hyhy teraz pozostało Tobie tylko zapamiętać, aby wygenerować kolejne dane za 15 lat.