Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: Data początku i końca wynajmu. Jak wyliczyć dni wynajmu w miesiącu/roku
Forum PHP.pl > Forum > Bazy danych > MySQL
wachcio
28.08.2019, 00:29:37
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
sazian
28.08.2019, 20:26:20
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
javafxdev
28.08.2019, 21:11:46 
[SQL] pobierz, plaintext 
  1. CREATE TABLE `dates` (
  2.     `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.     `start_date_of_rent` date NOT NULL,
  4.     `end_date_of_rent` date NOT NULL
  5. );
[SQL] pobierz, plaintext



Potem:
[SQL] pobierz, plaintext 
  1. INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (1, '2019-08-28', '2019-08-29');
  2. INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (2, '2019-08-31', '2019-09-1');
  3. INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (3, '2019-07-31', '2019-09-01');
[SQL] pobierz, plaintext


Jak widać rekord z ID=3 ma 3 miesiące w sobie


[SQL] pobierz, plaintext 
  1. SELECT d.g, count(d.g) FROM (
  2. SELECT * FROM
  3. (SELECT adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) g FROM
  4.  (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
  5.  (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
  6.  (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
  7.  (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
  8.  (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
  9. WHERE g BETWEEN (SELECT start_date_of_rent FROM dates WHERE id = 3) AND (SELECT end_date_of_rent FROM dates WHERE id = 3)) AS d GROUP BY YEAR(d.g), MONTH(d.g);
[SQL] pobierz, plaintext



Wynik:

[SQL] pobierz, plaintext 
  1. 2019-07-31	1
  2. 2019-08-01	31
  3. 2019-09-01	1
[SQL] pobierz, plaintext


Jest git?
wachcio
28.08.2019, 21:41:18
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
javafxdev
28.08.2019, 22:07:48
czyli dla przykładowych danych które wkleiłem chciałbyś uzyskać wynik:

[SQL] pobierz, plaintext 
  1. 2019	7	1
  2. 2019	8	34
  3. 2019	9	2
[SQL] pobierz, plaintext


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 smile.gif
wachcio
28.08.2019, 22:10:51
Cytat(javafxdev @ 28.08.2019, 23:07:48 ) *
czyli dla przykładowych danych które wkleiłem chciałbyś uzyskać wynik:

[SQL] pobierz, plaintext 
  1. 2019	7	1
  2. 2019	8	34
  3. 2019	9	2
[SQL] pobierz, plaintext


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 smile.gif


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 wink.gif Z wielkością pola rzeczywiście przesadziłem i wczoraj poprawiłem bo domków są dwa smile.gif
javafxdev
28.08.2019, 22:42:24
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?
wachcio
28.08.2019, 22:50:41
Cytat(javafxdev @ 28.08.2019, 23:42:24 ) *
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...
dublinka
29.08.2019, 05:46:53
Moze to ci cos rozjasni
https://www.daniweb.com/programming/databas...ween-date-range
sazian
29.08.2019, 18:07:13
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
javafxdev
29.08.2019, 18:21:13
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.
vokiel
29.08.2019, 19:04:29 
[SQL] pobierz, plaintext 
  1. CREATE TABLE daty
  2. (
  3.   house_id int,
  4.   date_start date,
  5.   date_stop date
  6. );
  7. INSERT INTO daty VALUES
  8. (1, '2019-01-01','2019-01-15'),
  9. (2, '2019-01-02','2019-01-08'),
  10. (1, '2019-01-22','2019-01-30'),
  11. (2, '2019-01-10','2019-01-22'),
  12. (1, '2019-02-01','2019-02-14'),
  13. (2, '2019-01-23','2019-02-17');
[SQL] pobierz, plaintext


Sumowanie po miesiąc-rok, grupowanie po nr domku http://sqlfiddle.com/#!9/639237/1
[SQL] pobierz, plaintext 
  1. SELECT
  2.   SUM( DATEDIFF(date_stop, date_start)) AS 'days_count'
  3.  , DATE_FORMAT(date_start, '%Y-%m') AS 'y-m'
  4.  , house_id
  5. FROM daty
  6. GROUP BY DATE_FORMAT(date_start, '%Y-%m'), house_id
[SQL] pobierz, plaintext


Sumowanie po roku, grupowanie po domku http://sqlfiddle.com/#!9/639237/2
[SQL] pobierz, plaintext 
  1. SELECT
  2.   SUM( DATEDIFF(date_stop, date_start)) AS 'days_count'
  3.  ,YEAR(date_start) AS 'y'
  4.  , house_id
  5. FROM daty
  6. GROUP BY YEAR(date_start), house_id
[SQL] pobierz, plaintext
Tomplus
30.08.2019, 06:22:57
@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
vokiel
31.08.2019, 00:45:34
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.
trueblue
31.08.2019, 09:27:55 
[SQL] pobierz, plaintext 
  1. SELECT tmp.id_domek,YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, COUNT(tmp.dzien) FROM (
  2.  SELECT r.id_domek,k.dzien FROM rezerwacja AS r
  3.  CROSS JOIN(
  4.   SELECT DATE_ADD(MAKEDATE(YEAR(CURDATE()),1),INTERVAL a.a + 10 * b.a + 100*c.a DAY) AS dzien FROM
  5.    (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
  6.   CROSS JOIN
  7.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
  8.  CROSS JOIN
  9.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c  
  10.   ) AS k ON k.dzien>=r.data_od AND k.dzien<=r.data_do
  11.  ORDER BY r.id_domek,k.dzien
  12. ) AS tmp
  13. GROUP BY tmp.id_domek,DATE_FORMAT(tmp.dzien,'%Y-%m')
[SQL] pobierz, plaintext


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.
wachcio
31.08.2019, 18:16:19
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.
trueblue
31.08.2019, 19:13:56
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.
wachcio
31.08.2019, 20:08:26
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: 
[SQL] pobierz, plaintext 
  1. SELECT tmp.house_nr,YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, COUNT(tmp.dzien) FROM (
  2.  SELECT r.house_nr,k.dzien FROM reservations AS r
  3.  CROSS JOIN(
  4.   SELECT DATE_ADD(MAKEDATE(YEAR(CURDATE()),1),INTERVAL a.a + 10 * b.a + 100*c.a DAY) AS dzien FROM
  5.    (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
  6.   CROSS JOIN
  7.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
  8.  CROSS JOIN
  9.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c  
  10.   ) AS k ON k.dzien>=r.start_date_of_rent AND k.dzien<=r.end_date_of_rent - 1
  11.  ORDER BY r.house_nr,k.dzien
  12. ) AS tmp
  13. GROUP BY tmp.house_nr,DATE_FORMAT(tmp.dzien,'%Y-%m')
[SQL] pobierz, plaintext


Aby pokazywało od roku 2017 zmieniłem na: 
[SQL] pobierz, plaintext 
  1. SELECT tmp.house_nr,YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, COUNT(tmp.dzien) FROM (
  2.  SELECT r.house_nr,k.dzien FROM reservations AS r
  3.  CROSS JOIN(
  4.   SELECT DATE_ADD(MAKEDATE(YEAR('2017-01-01'),1),INTERVAL a.a + 10 * b.a + 100*c.a DAY) AS dzien FROM
  5.    (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
  6.   CROSS JOIN
  7.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
  8.  CROSS JOIN
  9.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c  
  10.   ) AS k ON k.dzien>=r.start_date_of_rent AND k.dzien<=r.end_date_of_rent - 1
  11.  ORDER BY r.house_nr,k.dzien
  12. ) AS tmp
  13. GROUP BY tmp.house_nr,DATE_FORMAT(tmp.dzien,'%Y-%m')
[SQL] pobierz, plaintext


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ń
trueblue
31.08.2019, 20:41:23
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.
wachcio
31.08.2019, 20:43:33
Cytat(trueblue @ 31.08.2019, 21:41:23 ) *
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: 
[SQL] pobierz, plaintext 
  1. SELECT tmp.house_nr,YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, COUNT(tmp.dzien) FROM (
  2.  SELECT r.house_nr,k.dzien, `entry_owner_id` FROM reservations AS r
  3.  CROSS JOIN(
  4.   SELECT DATE_ADD(MAKEDATE(YEAR('2013-01-01'),1),INTERVAL a.a + 10 * b.a + 100*c.a + 1000*d.a DAY) AS dzien FROM
  5.    (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
  6.   CROSS JOIN
  7.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
  8.  CROSS JOIN
  9.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c  
  10.      CROSS JOIN
  11.     (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d   
  12.   ) AS k ON k.dzien>=r.start_date_of_rent AND k.dzien<=r.end_date_of_rent - 1 AND `entry_owner_id`=2
  13.  ORDER BY r.house_nr,k.dzien
  14. ) AS tmp
  15. GROUP BY tmp.house_nr,DATE_FORMAT(tmp.dzien,'%Y-%m')
[SQL] pobierz, plaintext


Dodałem niezbędny dla mnie wybór użytkownika dla którego ma być raport. Dzięki wielkie za pomoc
trueblue
31.08.2019, 21:12:21
Nie ma sensu robić daty ze stałej daty, po prostu wstaw na sztywno 2013-01-01 bez MAKEDATE, to też Ci sugerowałem.

Starczy Ci do 2040 roku. Przy założeniu, że startowy rok to 2013.
wachcio
31.08.2019, 22:21:56
Bo robię coś źle i zapytanie: 
[SQL] pobierz, plaintext 
  1. SELECT tmp.house_nr,YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, COUNT(tmp.dzien) FROM (
  2.  SELECT r.house_nr,k.dzien, `entry_owner_id` FROM reservations AS r
  3.  CROSS JOIN(
  4.   SELECT DATE_ADD(YEAR('2013-01-01'),INTERVAL a.a + 10 * b.a + 100*c.a + 1000*d.a DAY) AS dzien FROM
  5.    (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
  6.   CROSS JOIN
  7.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
  8.  CROSS JOIN
  9.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c  
  10.      CROSS JOIN
  11.     (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d   
  12.   ) AS k ON k.dzien>=r.start_date_of_rent AND k.dzien<=r.end_date_of_rent - 1 AND `entry_owner_id`=2
  13.  ORDER BY r.house_nr,k.dzien
  14. ) AS tmp
  15. GROUP BY tmp.house_nr,DATE_FORMAT(tmp.dzien,'%Y-%m')
[SQL] pobierz, plaintext


zwraca mi pusty wiersz

EDIT:
Chyba teraz będzie dobrze: 
[SQL] pobierz, plaintext 
  1. SELECT tmp.house_nr,YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, COUNT(tmp.dzien) FROM (
  2.  SELECT r.house_nr,k.dzien, `entry_owner_id` FROM reservations AS r
  3.  CROSS JOIN(
  4.   SELECT DATE_ADD('2013-01-01',INTERVAL a.a + 10 * b.a + 100*c.a + 1000*d.a DAY) AS dzien FROM
  5.    (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
  6.   CROSS JOIN
  7.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
  8.  CROSS JOIN
  9.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c  
  10.      CROSS JOIN
  11.     (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d   
  12.   ) AS k ON k.dzien>=r.start_date_of_rent AND k.dzien<=r.end_date_of_rent - 1 AND `entry_owner_id`=2
  13.  ORDER BY r.house_nr,k.dzien
  14. ) AS tmp
  15. GROUP BY tmp.house_nr,DATE_FORMAT(tmp.dzien,'%Y-%m')
[SQL] pobierz, plaintext
trueblue
31.08.2019, 22:32:02
ORDER BY nie jest potrzebne.
wachcio
1.09.2019, 14:54:50
Cytat(trueblue @ 31.08.2019, 23:32:02 ) *
ORDER BY nie jest potrzebne.


To szczegół ale ok dzięki




Próbowałem na innej tabeli zrobić również raport. Z założenia miał dodać i pogrupować koszty miesiącami. Ale niestety poległem. Jestem chociaż blisko rozwiązania?

DESCRIBE costs;
+----------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+-------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| category_id | smallint(6) | NO | | NULL | |
| entry_owner_id | smallint(6) | NO | | NULL | |
| value | smallint(6) | NO | | NULL | |
| description | text | YES | | NULL | |
| cost_date | date | NO | | NULL | |
| date_added | timestamp | NO | | CURRENT_TIMESTAMP | |
+----------------+-------------+------+-----+-------------------+----------------+


DESCRIBE cost_categories;
+------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| owner_id | smallint(6) | NO | | NULL | |
| category | tinytext | NO | | NULL | |
| date_added | timestamp | NO | | CURRENT_TIMESTAMP | |
+------------+-------------+------+-----+-------------------+----------------+

SELECT * FROM costs;
+----+-------------+----------------+-------+-------------------------------------------------+------------+---------------------+
| ID | category_id | entry_owner_id | value | description | cost_date | date_added |
+----+-------------+----------------+-------+-------------------------------------------------+------------+---------------------+
| 1 | 1 | 2 | 204 | | 2019-08-31 | 2019-08-31 18:50:26 |
| 2 | 2 | 2 | 50 | domestos, zmywaki, płyn do mycia naczyń, mopy | 2019-07-30 | 2019-08-31 18:52:08 |
| 3 | 3 | 2 | 400 | Naprawa dzrzwi | 2019-08-22 | 2019-08-31 23:44:08 |
| 4 | 3 | 2 | 50 | Naprawa łóżka | 2019-08-22 | 2019-08-31 23:44:17 |
| 5 | 4 | 2 | 200 | Płytki | 2019-08-22 | 2019-08-31 23:44:46 |
| 6 | 1 | 2 | 400 | coś | 2019-07-31 | 2019-08-31 18:50:26 |
| 7 | 1 | 2 | 300 | coś | 2019-07-31 | 2019-08-31 18:50:26 |
| 8 | 4 | 2 | 400 | cosss | 2019-08-22 | 2019-08-31 23:44:46 |
+----+-------------+----------------+-------+-------------------------------------------------+------------+---------------------+

[SQL] pobierz, plaintext 
  1. SELECT YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, tmp.value, SUM(tmp.value) AS sum_value FROM (
  2.  SELECT r.value,k.dzien, `entry_owner_id` FROM costs AS r
  3.  CROSS JOIN(
  4.   SELECT DATE_ADD('2013-01-01',INTERVAL a.a + 10 * b.a + 100*c.a + 1000*d.a DAY) AS dzien FROM
  5.    (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
  6.   CROSS JOIN
  7.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
  8.  CROSS JOIN
  9.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c  
  10.      CROSS JOIN
  11.     (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d   
  12.   ) AS k ON r.cost_date AND `entry_owner_id`=2 AND r.value >0
  13.  ORDER BY k.dzien, r.value
  14. ) AS tmp
  15. GROUP BY DATE_FORMAT(tmp.dzien,'%Y-%m')
[SQL] pobierz, plaintext
javafxdev
1.09.2019, 16:06:10
Czemu na siłę próbujesz to zrobić w MySql? Powiedziałeś że robisz to w Node.js - tam jesteś dobry - to zrób sobie funkcję np: (pseudokod)
[PHP] pobierz, plaintext 
  1. Map<YearMonth, Cost> getCostReport(...){
  2.  beginTime = ...
  3. ....
  4. endTime = 
  5. println("Czas wykonania raportu: " + (endTime - beginTime)) 
  6. return data;
  7. }
[PHP] pobierz, plaintext


która zwróci Ci dane w formie raportu i jednocześnie policzy jak długo to trwało
1) wykonuj zapytania pojedynczo a całą logike sumowania zrób po stronie node.js - zobaczysz jaki będzie czas wykonania.
2) Następnie zmodyfikuj tą funkcję żeby pobierała wszystkie dane potrzebne do raportu jednym zapytaniem select * ... i grupowała tak samo jak w 1)
Jeżeli czasy wykonania raportu nie będą zadowalające to wtedy będziesz mógł myśleć o optymalizacji. Ale gwarantuje Ci że nie będzie to potrzebne.
wachcio
1.09.2019, 17:24:09
Dobry to za duże słowo wink.gif Lepszy niż w MySQL i może masz rację chyba trzeba będzie trudniejsze zapytania rozbijać i obrabiać w node.js
javafxdev
2.09.2019, 20:48:38
Zrobiłem mały teścik jak to będzie wyglądać dla danych:

[SQL] pobierz, plaintext 
  1. INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (1, '2019-08-28', '2019-08-29');
  2. INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (2, '2019-08-31', '2019-09-1');
  3. INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (3, '2019-07-31', '2019-09-01');
  4. INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (4, '2020-01-01', '2020-12-31');
  5. INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (5, '2021-01-01', '2021-12-31');
  6. INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (6, '2022-01-01', '2022-12-31');
[SQL] pobierz, plaintext


Jak widać masz tutaj całe lata 2020-2022 wynajęte smile.gif

kod w PHP który nie jest zoptymalizowany (iteruje dzień po dniu) ale pokazuje jak długo taki raport będzie się generował w najgorszym możliwym scenariuszu - czyli konieczność przejścia przez cały rok.

[PHP] pobierz, plaintext 
  1. $time_start = microtime(true);
  2. $houses = $db->getAllHouses();
  3. echo 'Getting data from db in seconds: ' . (microtime(true) - $time_start);
  4.  
  5. function count_days_in_row(string $beginDate, string $endDate): array
  6. {
  7.     $return_array = [];
  8.     $date = date_create($beginDate);
  9.     $end_date = date_create($endDate);
  10.     while ($date <= $end_date) {
  11.         $date_format = date_format($date, "Y-m");
  12.         $return_array[] = $date_format;
  13.         $date = date_add($date, new DateInterval('P1D'));
  14.     }
  15.     return $return_array;
  16. }
  17.  
  18. function getHouseReport(array $houses)
  19. {
  20.     $report = [];
  21.     foreach ($houses as $house) {
  22.         $days = count_days_in_row($house['start_date_of_rent'], $house['end_date_of_rent']);
  23.         $report = array_merge($report, $days);
  24.     }
  25.     return array_count_values($report);
  26. }
  27.  
  28. var_dump(getHouseReport($houses));
  29. echo 'Total execution time in seconds: ' . (microtime(true) - $time_start);
[PHP] pobierz, plaintext


Wynik:


[PHP] pobierz, plaintext 
  1. Getting data from db in seconds: 0.0042970180511475
  2. array (size=39)
  3.   '2019-08' => int 34
  4.   '2019-09' => int 2
  5.   '2019-07' => int 1
  6.   '2020-01' => int 31
  7.   '2020-02' => int 29
  8.   '2020-03' => int 31
  9.   '2020-04' => int 30
  10.   '2020-05' => int 31
  11.   '2020-06' => int 30
  12.   '2020-07' => int 31
  13.   '2020-08' => int 31
  14.   '2020-09' => int 30
  15.   '2020-10' => int 31
  16.   '2020-11' => int 30
  17.   '2020-12' => int 31
  18.   '2022-01' => int 31
  19.   '2022-02' => int 28
  20.   '2022-03' => int 31
  21.   '2022-04' => int 30
  22.   '2022-05' => int 31
  23.   '2022-06' => int 30
  24.   '2022-07' => int 31
  25.   '2022-08' => int 31
  26.   '2022-09' => int 30
  27.   '2022-10' => int 31
  28.   '2022-11' => int 30
  29.   '2022-12' => int 31
  30.   '2021-01' => int 31
  31.   '2021-02' => int 28
  32.   '2021-03' => int 31
  33.   '2021-04' => int 30
  34.   '2021-05' => int 31
  35.   '2021-06' => int 30
  36.   '2021-07' => int 31
  37.   '2021-08' => int 31
  38.   '2021-09' => int 30
  39.   '2021-10' => int 31
  40.   '2021-11' => int 30
  41.   '2021-12' => int 31
  42. Total execution time in seconds: 0.037029981613159
[PHP] pobierz, plaintext


Chyba czas rzędu 0.03 sek. jest wystarczający.

Natomiast to samo ćwiczenie w MySQL:
[SQL] pobierz, plaintext 
  1. 39 row(s) returned	0.954 sec / 0.000 sec
[SQL] pobierz, plaintext


Jaki widać szybciej jest nawet robić to po stronie backendu niż w samym MySQL


trueblue
3.09.2019, 06:59:00
javafxdev, ale chyba nie osiągasz takiego czasu w MySQL na tych danych, które podałeś? Ja u siebie mam 1.1-1.2s dla ok. 65 tys. wierszy.
javafxdev
3.09.2019, 21:45:37
Dla dokładnie 
[SQL] pobierz, plaintext 
  1. SELECT count(*) FROM dates;
[SQL] pobierz, plaintext


Wynik: 6


[SQL] pobierz, plaintext 
  1.  SELECT YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, COUNT(tmp.dzien) FROM (
  2.  SELECT * FROM dates AS r
  3.  CROSS JOIN(
  4.   SELECT DATE_ADD('2013-01-01',INTERVAL a.a + 10 * b.a + 100*c.a + 1000*d.a + e.a*10000 DAY) AS dzien FROM
  5.    (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
  6.   CROSS JOIN
  7.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
  8.  CROSS JOIN
  9.   (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c  
  10.      CROSS JOIN
  11.     (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d   
  12.     CROSS JOIN
  13.     (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS e   
  14.   ) AS k ON k.dzien>=r.start_date_of_rent AND k.dzien<=r.end_date_of_rent
  15. ) AS tmp
  16. GROUP BY DATE_FORMAT(tmp.dzien,'%Y-%m');
[SQL] pobierz, plaintext


Tylko zauważ że ja dałem jeszcze e*10000 - bo potrzebuję dla dat od 1970 roku.

Ciekawe ile będzie w PHP się liczyło dla 65k wierszy smile.gif

no cóż w PHP:


[SQL] pobierz, plaintext 
  1. SELECT count(*) FROM dates; = 98304
[SQL] pobierz, plaintext 


[PHP] pobierz, plaintext 
  1. Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 10485760 bytes) 
[PHP] pobierz, plaintext


Po modyfikacji kodu w PHP:

[PHP] pobierz, plaintext 
  1. Getting data from db in seconds: 0.25872802734375
  2. array (size=39)
  3.   '2019-08' => int 283356
  4.   '2019-09' => int 16668
  5.   '2019-07' => int 8334
  6.   '2020-01' => int 258323
  7.   '2020-02' => int 241657
  8.   '2020-03' => int 258323
  9.   '2020-04' => int 249990
  10.   '2020-05' => int 258323
  11.   '2020-06' => int 249990
  12.   '2020-07' => int 258323
  13.   '2020-08' => int 258323
  14.   '2020-09' => int 249990
  15.   '2020-10' => int 258323
  16.   '2020-11' => int 249990
  17.   '2020-12' => int 258323
  18.   '2022-01' => int 258323
  19.   '2022-02' => int 233324
  20.   '2022-03' => int 258323
  21.   '2022-04' => int 249990
  22.   '2022-05' => int 258323
  23.   '2022-06' => int 249990
  24.   '2022-07' => int 258323
  25.   '2022-08' => int 258323
  26.   '2022-09' => int 249990
  27.   '2022-10' => int 258323
  28.   '2022-11' => int 249990
  29.   '2022-12' => int 258323
  30.   '2021-01' => int 258323
  31.   '2021-02' => int 233324
  32.   '2021-03' => int 258323
  33.   '2021-04' => int 249990
  34.   '2021-05' => int 258323
  35.   '2021-06' => int 249990
  36.   '2021-07' => int 258323
  37.   '2021-08' => int 258323
  38.   '2021-09' => int 249990
  39.   '2021-10' => int 258323
  40.   '2021-11' => int 249990
  41.   '2021-12' => int 258323
  42. Total execution time in seconds: 48.838152170181
[PHP] pobierz, plaintext


Jak widać samo pobranie 100k wierszy idzie szybko natomiast liczenie przerwałem przy 50k i trwa to już prawie minutę.
W samym SQL nie udało mi się doczekać (nie czekałem dłużej niż 3 min).
trueblue
4.09.2019, 08:42:52
Ja przy dodaniu 1000 oraz 10000 do UNION miałem czas 4 minuty 36 sekund (dla 65 tys. rekordów).
Przy samych 1000, 32 sekundy. A przy ograniczeniu ostatniego UNION do 4 (tak aby osiągnąć ok. pełne 10 lat), 11 sekund.
Tak więc nie jest tak różowo.

Trzeba wybrać optymalne rozwiązanie liczenia. Ewentualnie można liczyć na bieżąco niepoliczone rezerwacje i kumulować wynik w odrębnej tablicy.
Tomplus
4.09.2019, 09:17:15
Ja osobiście uważam, że lepiej na bieżąco rezerwacje uzupełniać w osobnej tabeli np. reservations_calendar i w niej stworzyć pozycje: `dt`(date), `id_house` (int), `booked` (0,1)


Wtedy mamy proste zapytanie:

[SQL] pobierz, plaintext 
  1. SELECT DATE_FORMAT(`dt`, '%Y-%m'), COUNT(1),  `id_house` FROM reservations_calendar WHERE `booked` = 1 GROUP BY DATE_FORMAT(`dt`, '%Y-%-m');
[SQL] pobierz, plaintext
trueblue
4.09.2019, 09:29:53
A czy to zapytanie czasem nie liczy ilości rezerwacji, a nie dni? Ok, rozumiem, to już docelowe zapytanie dla "zarchiwizowanych" danych.

P.S. W formacie jest drobny błąd.
Tomplus
4.09.2019, 10:29:01
Sądzę że w momencie gdy użytkownik kliknie `rezerwuję` to mają zaktualizować się rekordy z booked 0 na 1, więc sprawdzanie stanu rezerwacji może odbywać się na bieżąco.
javafxdev
4.09.2019, 20:02:46
Podsumowując:

1) Można zrobić to zapytanie w MySQL - ale będzie wolno (w pewnych przypadkach, Twój hobbistyczny projekt tego nie odczuje)
2) Można zrobić liczenie w backendzie - będzie szybciej - w zależności jak bardzo przysiądziesz do optymalizacji liczenia (ale nie ma sensu się nad tym skupiać lepiej dowieźć inne funkcjonalności bo Twój projekt tego nie odczuje)
3) Można zrobić to tak jak zasugerował Tomplus - czyli dać lepszą strukturę w bazie danych - nie trzymać okresami tylko dzień po dniu - wtedy jak Ci z rezerwacji wypadnie jakiś dzień to nic nie musisz zmieniać - a w obecnej wersji będziesz musiał rozbić to na dwa rekordy - wtedy nie ma znaczenia czy będziesz liczył w MySQL czy backendzie bo będzie szybko (i w Twoim przypadku to chyba najlepsze rozwiązanie)

Wybierz mądrze bo możesz wybrać tylko jedno wink.gif
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.