Elektroda.pl
Elektroda.pl
X

Search our partners

Find the latest content on electronic components. Datasheets.com
Please add exception to AdBlock for elektroda.pl.
If you watch the ads, you support portal and users.

Korelacja dwóch tabel SQL'a

Samuraj 11 Mar 2011 21:00 2810 5
  • #1
    Samuraj
    Level 35  
    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:
    Code:

    ID    int
    CZAS    timestamp
    STAN    decimal(11,0)    

    Przykład z bazy:
    Code:

    12657    2011-03-11 20:39:46    8
    12658    2011-03-11 20:40:47    10
    12659    2011-03-11 20:41:48    9
    12660    2011-03-11 20:42:48    10

    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 :?:
  • #2
    marcinj12
    Level 40  
    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ł:
    Code:

    id  godzina_od  godzina_do   cena  data_obowiazywania_od    data_obowiazywania_do
    1    00:00:00    21:30:00    2        2011-01-01            2011-03-05
    2    21:30:01    23:59:59    1        2011-01-01            2011-03-05
    3    00:00:00    21:30:00    2.5      2011-03-06            2011-03-10
    4    21:30:01    23:59:59    1.5      2011-03-06            2011-03-10
    5    00:00:00    21:30:00    3        2011-03-11            2099-01-01
    6    21:30:01    23:59:59    1.9      2011-03-11            2099-01-01

    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 ;)]
    Code: sql
    Log in, to see the code
  • #3
    Samuraj
    Level 35  
    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ść.
  • Helpful post
    #4
    marcinj12
    Level 40  
    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:
    Code: sql
    Log in, to see the code

    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.
  • #5
    Samuraj
    Level 35  
    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
    Code: sql
    Log in, to see the code

    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ń.
  • #6
    Samuraj
    Level 35  
    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.