Data początku i końca wynajmu. Jak wyliczyć dni wynajmu w miesiącu/roku |
Data początku i końca wynajmu. Jak wyliczyć dni wynajmu w miesiącu/roku |
28.08.2019, 00:29:37
Post
#1
|
|
Grupa: Zarejestrowani Postów: 45 Pomógł: 0 Dołączył: 17.07.2011 Ostrzeżenie: (0%) |
Witam
Robię sobie mini system rezerwacji dla dla dwóch domków letniskowych. Baza ma postać: +--------------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+-------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | entry_owner_id | tinyint(4) | NO | | NULL | | | rentier_id | int(11) | NO | | NULL | | | house_nr | int(11) | NO | | NULL | | | how_many_people | tinyint(4) | NO | | NULL | | | animals | tinyint(4) | NO | | NULL | | | start_date_of_rent | date | NO | | NULL | | | end_date_of_rent | date | NO | | NULL | | | loan | smallint(6) | YES | | NULL | | | payment | mediumint(6) | YES | | NULL | | | date_of_added | timestamp | NO | | CURRENT_TIMESTAMP | | +--------------------+--------------+------+-----+-------------------+----------------+ Jak zapytać bazę aby podała mi ilość dni wynajmu na dany miesiąc/rok. start i zakończenie wynajmu nie muszą się znajdować oczywiście w jednym miesiącu a nawet nie muszą to być miesiące sąsiednie |
|
|
28.08.2019, 20:26:20
Post
#2
|
|
Grupa: Zarejestrowani Postów: 1 043 Pomógł: 141 Dołączył: 19.09.2006 Skąd: B-tów Ostrzeżenie: (0%) |
W najprostszej wersji możesz użyć funkcji datediff która podaje ilość dni pomiędzy datami
https://dev.mysql.com/doc/refman/5.5/en/dat...nction_datediff Później wystarczy zgrupować po roku i miesiącu https://dev.mysql.com/doc/refman/5.5/en/dat...unction_extract Problem pojawi się jeśli rezerwacja zaczyna się w jednym miesiącu, a kończy w drugim. Jeśli chcesz to uwzględnić to będzie jeszcze trochę zabawy z ifami |
|
|
28.08.2019, 21:11:46
Post
#3
|
|
Grupa: Zarejestrowani Postów: 95 Pomógł: 7 Dołączył: 27.10.2015 Ostrzeżenie: (0%) |
Potem:
Jak widać rekord z ID=3 ma 3 miesiące w sobie
Wynik:
Jest git? |
|
|
28.08.2019, 21:41:18
Post
#4
|
|
Grupa: Zarejestrowani Postów: 45 Pomógł: 0 Dołączył: 17.07.2011 Ostrzeżenie: (0%) |
sazian czytałem o tych funkcjach ale nic sensownego nie udało mi się na razie stworzyć...
javafxdev Mógłbyś wyjaśnić jak to działa? Bo chciałbym mieć brane pod uwagę wszystkie rezerwacje dla domku nr 1 lub 2. Stworzyć z tego zapytania raport Ten post edytował wachcio 28.08.2019, 21:53:03 |
|
|
28.08.2019, 22:07:48
Post
#5
|
|
Grupa: Zarejestrowani Postów: 95 Pomógł: 7 Dołączył: 27.10.2015 Ostrzeżenie: (0%) |
czyli dla przykładowych danych które wkleiłem chciałbyś uzyskać wynik:
Jeżeli tak to musisz opakować to jeszcze w zew. query które Ci pogrupuje i przekazać zew. start_date i end_date do tego co wkleiłem wtedy ładnie Ci samo pogrupuje. Ogólnie trochę przesadziłeś z kolumną house_nr jako INT(11) - chyba że masz tysiące domków |
|
|
28.08.2019, 22:10:51
Post
#6
|
|
Grupa: Zarejestrowani Postów: 45 Pomógł: 0 Dołączył: 17.07.2011 Ostrzeżenie: (0%) |
czyli dla przykładowych danych które wkleiłem chciałbyś uzyskać wynik:
Jeżeli tak to musisz opakować to jeszcze w zew. query które Ci pogrupuje i przekazać zew. start_date i end_date do tego co wkleiłem wtedy ładnie Ci samo pogrupuje. Ogólnie trochę przesadziłeś z kolumną house_nr jako INT(11) - chyba że masz tysiące domków Tak o coś takiego mi chodzi. Na moim poziomie znajomości MySQL to mission impossible bo ja już nie wiem jak to zapytanie działa Z wielkością pola rzeczywiście przesadziłem i wczoraj poprawiłem bo domków są dwa Ten post edytował wachcio 28.08.2019, 22:28:50 |
|
|
28.08.2019, 22:42:24
Post
#7
|
|
Grupa: Zarejestrowani Postów: 95 Pomógł: 7 Dołączył: 27.10.2015 Ostrzeżenie: (0%) |
Skoro mówisz że zrobienie tego w MySQL to mission impossible to może w PHP przeleć po wszystkich rekordach i zsumuj - wtedy będziesz wiedział co się dzieje i jak to działa?
|
|
|
28.08.2019, 22:50:41
Post
#8
|
|
Grupa: Zarejestrowani Postów: 45 Pomógł: 0 Dołączył: 17.07.2011 Ostrzeżenie: (0%) |
Skoro mówisz że zrobienie tego w MySQL to mission impossible to może w PHP przeleć po wszystkich rekordach i zsumuj - wtedy będziesz wiedział co się dzieje i jak to działa? Backend piszę akurat w Node.js i mógłbym tak oczywiście zrobić ale z czasem wysłanie iluś set zapytań do bazy chyba będzie zajmowało sporo czasu... |
|
|
29.08.2019, 05:46:53
Post
#9
|
|
Grupa: Zarejestrowani Postów: 594 Pomógł: 66 Dołączył: 22.02.2008 Skąd: Dublin Ostrzeżenie: (0%) |
Moze to ci cos rozjasni
https://www.daniweb.com/programming/databas...ween-date-range -------------------- |
|
|
29.08.2019, 18:07:13
Post
#10
|
|
Grupa: Zarejestrowani Postów: 1 043 Pomógł: 141 Dołączył: 19.09.2006 Skąd: B-tów Ostrzeżenie: (0%) |
chodziło mi o coś takiego http://sqlfiddle.com/#!9/337620/1
tylko jak widzisz problem pojawia się gdy rezerwacja jest w kilku miesiącach - zaczyna się w jednym miesiącu, a kończy w drugim - wtedy ilość dni wlicza się na pierwszy miesiąc |
|
|
29.08.2019, 18:21:13
Post
#11
|
|
Grupa: Zarejestrowani Postów: 95 Pomógł: 7 Dołączył: 27.10.2015 Ostrzeżenie: (0%) |
ogólnie jeżeli masz 2 domki, zakładając ze będziesz robił rezerwacje jednodniowe to masz 365*2 wpisów w bazie - jak będziesz robił ten raport w node.js za cały rok to myślisz że wykonanie 730 selectów będzie mulić baze i długo trwać, jeżeli tak po pobierz jednym zapytaniem 365 rekordów i przelicz całość w node.js.
Nawet jeśli ten projekt przetrwa 5 lat to ~3k zapytań nie zamuli bazy. |
|
|
29.08.2019, 19:04:29
Post
#12
|
|
Grupa: Zarejestrowani Postów: 2 592 Pomógł: 445 Dołączył: 12.03.2007 Ostrzeżenie: (0%) |
Sumowanie po miesiąc-rok, grupowanie po nr domku http://sqlfiddle.com/#!9/639237/1
Sumowanie po roku, grupowanie po domku http://sqlfiddle.com/#!9/639237/2
-------------------- |
|
|
30.08.2019, 06:22:57
Post
#13
|
|
Grupa: Zarejestrowani Postów: 1 828 Pomógł: 225 Dołączył: 20.03.2005 Skąd: Będzin Ostrzeżenie: (0%) |
@vokiel
Chyba wciąż to nie jest to: Ja dodałem rekord: (2, '2019-12-28','2020-01-03') i zamiast mieć: 2019-12 -> 3 2020-01 -> 3 (literowka byla) Mam: 2019-12 -> 6 Ten post edytował Tomplus 31.08.2019, 06:00:41 |
|
|
31.08.2019, 00:45:34
Post
#14
|
|
Grupa: Zarejestrowani Postów: 2 592 Pomógł: 445 Dołączył: 12.03.2007 Ostrzeżenie: (0%) |
Raczej "2020-01 -> 3" a nie "2020-03 -> 3" ;-)
Ale racja, to co napisałem liczy tylko dni w miesiącu rozpoczęcia rezerwacji. Policzenie tego w jednym zapytaniu będzie bardzo trudne o ile w ogóle możliwe, bo musisz wygenerować tyle wierszy ile jest miesięcy pomiędzy datami. Moim zdaniem możliwe będzie tylko przy użyciu procedury, która wygeneruje zestaw dat podzielonych miesiącami dla tych wpisów, które zawierają się w kilku miesiącach, np dla takiego zestawu: Kod (1, '2019-01-01','2019-01-15'), (2, '2019-01-02','2019-01-08'), (1, '2019-01-22','2019-01-30'), (2, '2019-01-10','2019-01-22'), (1, '2019-02-01','2019-02-14'), (2, '2019-01-23','2019-02-17'), (2, '2019-12-28','2020-01-03'), (2, '2019-12-20','2020-02-21'); wygeneruje tymczasową tabelę: Kod (1, '2019-01-01','2019-01-15'), (2, '2019-01-02','2019-01-08'), (1, '2019-01-22','2019-01-30'), (2, '2019-01-10','2019-01-22'), (1, '2019-02-01','2019-02-14'), (2, '2019-01-23','2019-01-31'), (2, '2019-02-01','2019-02-17'), (2, '2019-12-28','2019-12-31'), (2, '2020-01-01','2020-01-03'), (2, '2019-12-20','2019-21-31'); (2, '2020-01-01','2020-01-31'); (2, '2020-02-01','2020-02-21'); Wtedy praca na tych danych będzie już bardzo prosta. Jakbyś miał ograniczenie rozpiętości dat do np maksymalnie dwóch miesięcy to byłoby to wykonalne w jednym zapytaniu. Jeśli obrabiasz te dane dalej w jakimś skrypcie, to opcje są dwie - jedna to przy wstawianiu danych generować daty per miesiąc (rozbijając rezerwację zawierającą się w kilku miesiącach na wiele wierszy). Druga to pobranie dat i wyliczenie tego w skrypcie w pętli. -------------------- |
|
|
31.08.2019, 09:27:55
Post
#15
|
|
Grupa: Zarejestrowani Postów: 6 761 Pomógł: 1822 Dołączył: 11.03.2014 Ostrzeżenie: (0%) |
To dziwne zapytanie w środku, to kalendarz. Ten jest generowany na 1000 od stycznia bieżącego roku. Jeśli chcesz generować od innego roku, to możesz na sztywno wstawić rok zamiast YEAR(CURDATE()), albo po prostu datę zamiast MAKEDATE(...). Gdybyś chciał dowiedzieć się jak działa zapytanie kalendarza, to zerknij na: https://kawalekkodu.pl/marian-a-czy-ty-pami...lendarz-w-mysql Edycja: Teraz zauważyłem, że jest to rozwiązanie bardzo podobne do tego, które zaproponował javafxdev. Ten post edytował trueblue 31.08.2019, 14:03:12 -------------------- |
|
|
31.08.2019, 18:16:19
Post
#16
|
|
Grupa: Zarejestrowani Postów: 45 Pomógł: 0 Dołączył: 17.07.2011 Ostrzeżenie: (0%) |
Nadal nie bardzo wiem jak to działa ale rozwiązanie trueblue działa chyba i wydaje się, że dane są ok. Dzięki wielkie za poświęcony czas. Muszę się ostro doszkolić z MySQL. Projekt typowo hobbystyczny (dla rodziny) ale jak każdy wiele uczy.
|
|
|
31.08.2019, 19:13:56
Post
#17
|
|
Grupa: Zarejestrowani Postów: 6 761 Pomógł: 1822 Dołączył: 11.03.2014 Ostrzeżenie: (0%) |
Nie wnikając jak działa kalendarz, to ten generuje jakiś zakres dat, czyli po prostu rekordów. Założenie jest takie, że ta pula jest przynajmniej równa puli wynikającej z najstarszej i najnowszej daty rezerwacji (rezerwacje muszą mieć pokrycie w kalendarzu).
Następnie ta pula rekordów jest łączona złączeniem nierówności (non-equi join) z datą od i do danej rezerwacji (CROSS JOIN ... ON k.dzien>=r.data_od AND k.dzien<=r.data_do), dzięki czemu otrzymujemy wycinek kalendarza w zakresie tychże dat. Tak się dzieje dla każdej rezerwacji. Potem następuje po prostu grupowanie per rok-miesiąć i zliczenie rezerwacji w danej grupie. P.S. Klauzula ORDER BY r.id_domek,k.dzien jest tam niepotrzebna. -------------------- |
|
|
31.08.2019, 20:08:26
Post
#18
|
|
Grupa: Zarejestrowani Postów: 45 Pomógł: 0 Dołączył: 17.07.2011 Ostrzeżenie: (0%) |
Ale niestety nie pokazuje rezerwacji przed 2019 rokiem albo ja coś robię nie tak
EDIT: Poza tym rezerwacja od np. 1-8 czerwca to 7 dób hotelowych a nie 8. Będę musiał to jakoś sensownie ogarnąć Ok poprawka tego nie była trudna:
Aby pokazywało od roku 2017 zmieniłem na:
Jednak to też nie rozwiązuje do końca problemu. Nie pokazuje rezerwacji na przestrzeni więcej niż 3 lat. Jeśli nie da się inaczej to z backendu będę musiał wysłać kilka zapytań Ten post edytował wachcio 31.08.2019, 20:34:52 |
|
|
31.08.2019, 20:41:23
Post
#19
|
|
Grupa: Zarejestrowani Postów: 6 761 Pomógł: 1822 Dołączył: 11.03.2014 Ostrzeżenie: (0%) |
W poście z zapytaniem napisałem Ci jak rozwiązać obydwa problemy.
Tak, należy zmienić datę startową kalendarza, zresztą o tym pisałem również w kolejnym poście - rezerwacje mają mieć pokrycie w kalendarzu, czyli ten ma mieć odpowiedni zakres dat. A co za tym idzie również jego data końcowa ma być odpowiednia. Aby rozszerzyć datę końcową należy dodać kolejną grupę UNION ALL, generującą tysiące. Zerknij na post javafxdev. -------------------- |
|
|
31.08.2019, 20:43:33
Post
#20
|
|
Grupa: Zarejestrowani Postów: 45 Pomógł: 0 Dołączył: 17.07.2011 Ostrzeżenie: (0%) |
W poście z zapytaniem napisałem Ci jak rozwiązać obydwa problemy. Tak, należy zmienić datę startową kalendarza, zresztą o tym pisałem również w kolejnym poście - rezerwacje mają mieć pokrycie w kalendarzu, czyli ten ma mieć odpowiedni zakres dat. A co za tym idzie również jego data końcowa ma być odpowiednia. Aby rozszerzyć datę końcową należy dodać kolejną grupę UNION ALL, generującą tysiące. Zerknij na post javafxdev. Ok przepraszam umknęło mi to Ostatecznie jeśli dobrze rozumiem takie zapytanie powinno rozwiązać problem na długie lata:
Dodałem niezbędny dla mnie wybór użytkownika dla którego ma być raport. Dzięki wielkie za pomoc Ten post edytował wachcio 31.08.2019, 21:04:07 |
|
|
Wersja Lo-Fi | Aktualny czas: 29.03.2024 - 01:34 |