Korelacja dwóch tabel SQL'a

Robię analizator kosztów zużycia energii w gosp. domowym i mam problem z korelacją tabel.
Ze strony technicznej temat już mam opanowany urządzenie odczytuje dane w wybranych ostępach czasu i ładuje je do bazy danych SQL.
Tabela ze wskazaniami wygląda następująco:

Przykład z bazy:

Stan określa zużycie w danym odcinku czasu, nie jest to licznik liczący od zera w górę. Za każdym odczytem urządzenie ustawia stan na 0.
O ile nie ma problemu z odczytaniem zużycia w wybranym odcinku czasu np przez ostatnie 24 godziny, ostatni miesiąc itp o tyle mam problem z skorelowaniem tego z cennikiem.
Wymyśliłem sobie iż zrobię kolejną tabelę z cennikiem w której jeden rekord będzie określał wszelkie opłaty, oraz datę od kiedy obowiązuje cennik, wiadomo przy wyliczeniach pobiorę rekord z ceną razy stan za dany okres i mam koszty.
Jak powinno wyglądać takie zapytanie dające mi zużycie w ostatnie 24 godziny oraz cenę
Mogę co prawda powiększyć pierwszą tabelę o kolejną kolumnę CENA lecz to nie potrzebnie zwiększy wielkość i tak już dużej bazy danych.
Co będzie gdy w zapytam o koszty za ostatni miesiąc a w między czasie wystąpiła zmiana cennika
Np. zmiana cennika była 15 a ja mam stany od 1 do 30
Rozbijać to na dwa zapytania na podstawie ostatniej zmiany cennika

Zależy od silnika bazy który używasz i tego, jak zaprojektujesz tabelę z cennikiem...
Ja bym zrobił taką tabelę z cennikiem, gdzie podasz przedział godzinowy obowiązywania stawki oraz od kiedy do kiedy cennik obowiązywał:

Upewnij się że cały przedział czasu jest "pokryty" i nie ma dziur - zarówno jeśli chodzi o godziny w ciągu dnia, jak i o daty obowiązywania.

Wtedy możesz robić selecta dołączając tabelę ceny w sposób podobny do tego [składnia PostgreSQL + trochę kombinowania żeby dopasować typy ]

Idea ciekawa.
U mnie o tyle będzie prościej iż nie przewiduje rozbicie cennika na poszczególne taryfy dzienna i nocna - jedna stawka przez cały dzień.
Jeśli chodzi o silnik to używam MySQL'a.
Trochę mnie martwi Twoja propozycja odnośnie cennika gdyż nigdy nie wiemy kiedy będą wprowadzone zmiany. Tak więc okres obowiązywania ostatniej zmiany jest od okresu do teraz. Wcześniejsze można już wyliczyć.
Problemem pozostaje ładnie to ubrać w zapytanie.

W sumie to na upartego można wpisać datę do kiedy ma obowiązywać dany cennik jak w przykładzie podanym przez Ciebie - 2099 r.
A w przypadku wpisywania nowej pozycji cennika pobierać ostatni rekord z cennika i modyfikować datę końcową na datę wpisu. W tym przypadku zachowamy ciągłość.

Samuraj wrote:

Idea ciekawa.
U mnie o tyle będzie prościej iż nie przewiduje rozbicie cennika na poszczególne taryfy dzienna i nocna - jedna stawka przez cały dzień.
Jeśli chodzi o silnik to używam MySQL'a.
Trochę mnie martwi Twoja propozycja odnośnie cennika gdyż nigdy nie wiemy kiedy będą wprowadzone zmiany. Tak więc okres obowiązywania ostatniej zmiany jest od okresu do teraz. Wcześniejsze można już wyliczyć.
Problemem pozostaje ładnie to ubrać w zapytanie.

W sumie to na upartego można wpisać datę do kiedy ma obowiązywać dany cennik jak w przykładzie podanym przez Ciebie - 2099 r.
A w przypadku wpisywania nowej pozycji cennika pobierać ostatni rekord z cennika i modyfikować datę końcową na datę wpisu. W tym przypadku zachowamy ciągłość.

Skoro nie przewidujesz rozbicia na stawki godzinowe, to sprawa jest jeszcze prostsza... Odpada cały fragment zapytania odpowiedzialny za godziny, więc po usunięciu kolumn godzina_od i do z tabeli cennika zostanie:

Jak widać czysty SQL, składnia powinna być zgodna z każdym systemem bazodanowym.
Jeśli chodzi o datę końcową obowiązywania to tak jak piszesz - jest tam rok 2099 - jakaś duża date, żeby umożliwić zapytanie z BETWEEN. Jeżeli wprowadzasz nową stawkę - zmieniasz rok 2099 na datę, do_kiedy obowiązywała, a nową wprowadzasz od tej daty do 2099 - i ona obowiązuje od tego okresu do teraz.

Można też zrobić to z jedną kolumną, ale wtedy trzeba wykorzystać podzapytanie.

PS. Jeżeli chcesz zmniejszyć rozmiar bazy, rozważ zastąpienie typu DECIMAL standardowym: TINYINT, SMALLINT czy nawet zwykły INTEGER, obliczenia na nich są szybsze i mogą zajmować mniej miejsca. Poza tym - czy pole CZAS nie może być kluczem głównym? Mógłbyś wyrzucić wtedy pole ID.

Wszystko działa jak potrzeba.
W sumie to doszliśmy do tego w tym samym czasie.
Wykonałem takie zapytanie i otrzymałem to co oczekiwałem

Co do klucza głównego to jest nim ID z ustawionym atrybutem auto_increment
Dodatkowo po WHERE mogę ustawić np. CZAS>=DATE_SUB(NOW(), INTERVAL 1 WEEK) otrzymując dane za ostatni tydzień.

Wychodzi na to iż mam kolejny problem.
Wiadomo iż na rachunek wpływają opłaty zmienne - wyliczane na podstawie zużytej energii (tu nie ma problemu) oraz stałe - wyliczane na postawie długości trwania okresu rozliczeniowego.
Stworzyłem nową tabele w której mam datę wizyty inkasenta. Na postawie tej daty i bieżącej mogę wyliczyć jak długi jest okres rozliczeniowy.
Można to co prawda przemnożyć przez opłaty stałe lecz co w przypadku gdy w danym okresie nastąpi zmiana opłat
Czy ma ktoś pomysł na inny sposób rozwiązania tego problemu.
W tabeli cennik mam cenę opłat stałych, zmiennych oraz okres obowiązywania.