Data początku i końca wynajmu. Jak wyliczyć dni wynajmu w miesiącu/roku Opcje

[trueblue]

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]

Bo robię coś źle i zapytanie:

[SQL] pobierz, plaintext 
SELECT tmp.house_nr,YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, COUNT(tmp.dzien) FROM (
 SELECT r.house_nr,k.dzien, `entry_owner_id` FROM reservations AS r
 CROSS JOIN(
  SELECT DATE_ADD(YEAR('2013-01-01'),INTERVAL a.a + 10 * b.a + 100*c.a + 1000*d.a DAY) AS dzien FROM
   (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
  CROSS JOIN
  (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
 CROSS JOIN
  (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  
     CROSS JOIN
    (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   
  ) AS k ON k.dzien>=r.start_date_of_rent AND k.dzien<=r.end_date_of_rent - 1 AND `entry_owner_id`=2
 ORDER BY r.house_nr,k.dzien
) AS tmp
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 
SELECT tmp.house_nr,YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, COUNT(tmp.dzien) FROM (
 SELECT r.house_nr,k.dzien, `entry_owner_id` FROM reservations AS r
 CROSS JOIN(
  SELECT DATE_ADD('2013-01-01',INTERVAL a.a + 10 * b.a + 100*c.a + 1000*d.a DAY) AS dzien FROM
   (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
  CROSS JOIN
  (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
 CROSS JOIN
  (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  
     CROSS JOIN
    (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   
  ) AS k ON k.dzien>=r.start_date_of_rent AND k.dzien<=r.end_date_of_rent - 1 AND `entry_owner_id`=2
 ORDER BY r.house_nr,k.dzien
) AS tmp
GROUP BY tmp.house_nr,DATE_FORMAT(tmp.dzien,'%Y-%m')
[SQL] pobierz, plaintext 

Ten post edytował wachcio 31.08.2019, 22:23:32

[trueblue]

ORDER BY nie jest potrzebne.

[wachcio]

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 
SELECT YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, tmp.value, SUM(tmp.value) AS sum_value FROM (
 SELECT r.value,k.dzien, `entry_owner_id` FROM costs AS r
 CROSS JOIN(
  SELECT DATE_ADD('2013-01-01',INTERVAL a.a + 10 * b.a + 100*c.a + 1000*d.a DAY) AS dzien FROM
   (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
  CROSS JOIN
  (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
 CROSS JOIN
  (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  
     CROSS JOIN
    (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   
  ) AS k ON r.cost_date AND `entry_owner_id`=2 AND r.value >0
 ORDER BY k.dzien, r.value
) AS tmp
GROUP BY DATE_FORMAT(tmp.dzien,'%Y-%m')
[SQL] pobierz, plaintext 

Ten post edytował wachcio 1.09.2019, 14:54:43

[javafxdev]

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 
Map<YearMonth, Cost> getCostReport(...){
 beginTime = ...
....
endTime = 
println("Czas wykonania raportu: " + (endTime - beginTime)) 
return data;
}
[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]

Dobry to za duże słowo Lepszy niż w MySQL i może masz rację chyba trzeba będzie trudniejsze zapytania rozbijać i obrabiać w node.js

[javafxdev]

Zrobiłem mały teścik jak to będzie wyglądać dla danych:

[SQL] pobierz, plaintext 
INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (1, '2019-08-28', '2019-08-29');
INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (2, '2019-08-31', '2019-09-1');
INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (3, '2019-07-31', '2019-09-01');
INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (4, '2020-01-01', '2020-12-31');
INSERT INTO dates (id, start_date_of_rent, end_date_of_rent) VALUES (5, '2021-01-01', '2021-12-31');
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

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 
$time_start = microtime(true);
$houses = $db->getAllHouses();
echo 'Getting data from db in seconds: ' . (microtime(true) - $time_start);
 
function count_days_in_row(string $beginDate, string $endDate): array
{
    $return_array = [];
    $date = date_create($beginDate);
    $end_date = date_create($endDate);
    while ($date <= $end_date) {
        $date_format = date_format($date, "Y-m");
        $return_array[] = $date_format;
        $date = date_add($date, new DateInterval('P1D'));
    }
    return $return_array;
}
 
function getHouseReport(array $houses)
{
    $report = [];
    foreach ($houses as $house) {
        $days = count_days_in_row($house['start_date_of_rent'], $house['end_date_of_rent']);
        $report = array_merge($report, $days);
    }
    return array_count_values($report);
}
 
var_dump(getHouseReport($houses));
echo 'Total execution time in seconds: ' . (microtime(true) - $time_start);
[PHP] pobierz, plaintext 

Wynik:

[PHP] pobierz, plaintext 
Getting data from db in seconds: 0.0042970180511475
array (size=39)
  '2019-08' => int 34
  '2019-09' => int 2
  '2019-07' => int 1
  '2020-01' => int 31
  '2020-02' => int 29
  '2020-03' => int 31
  '2020-04' => int 30
  '2020-05' => int 31
  '2020-06' => int 30
  '2020-07' => int 31
  '2020-08' => int 31
  '2020-09' => int 30
  '2020-10' => int 31
  '2020-11' => int 30
  '2020-12' => int 31
  '2022-01' => int 31
  '2022-02' => int 28
  '2022-03' => int 31
  '2022-04' => int 30
  '2022-05' => int 31
  '2022-06' => int 30
  '2022-07' => int 31
  '2022-08' => int 31
  '2022-09' => int 30
  '2022-10' => int 31
  '2022-11' => int 30
  '2022-12' => int 31
  '2021-01' => int 31
  '2021-02' => int 28
  '2021-03' => int 31
  '2021-04' => int 30
  '2021-05' => int 31
  '2021-06' => int 30
  '2021-07' => int 31
  '2021-08' => int 31
  '2021-09' => int 30
  '2021-10' => int 31
  '2021-11' => int 30
  '2021-12' => int 31
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 
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]

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]

Dla dokładnie

[SQL] pobierz, plaintext 
SELECT count(*) FROM dates;
[SQL] pobierz, plaintext 

Wynik: 6

[SQL] pobierz, plaintext 
 SELECT YEAR(tmp.dzien) AS rok,MONTH(tmp.dzien) AS miesiac, COUNT(tmp.dzien) FROM (
 SELECT * FROM dates AS r
 CROSS JOIN(
  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
   (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
  CROSS JOIN
  (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
 CROSS JOIN
  (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  
     CROSS JOIN
    (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   
    CROSS JOIN
    (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   
  ) AS k ON k.dzien>=r.start_date_of_rent AND k.dzien<=r.end_date_of_rent
) AS tmp
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

no cóż w PHP:

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

[PHP] pobierz, plaintext 
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 
Getting data from db in seconds: 0.25872802734375
array (size=39)
  '2019-08' => int 283356
  '2019-09' => int 16668
  '2019-07' => int 8334
  '2020-01' => int 258323
  '2020-02' => int 241657
  '2020-03' => int 258323
  '2020-04' => int 249990
  '2020-05' => int 258323
  '2020-06' => int 249990
  '2020-07' => int 258323
  '2020-08' => int 258323
  '2020-09' => int 249990
  '2020-10' => int 258323
  '2020-11' => int 249990
  '2020-12' => int 258323
  '2022-01' => int 258323
  '2022-02' => int 233324
  '2022-03' => int 258323
  '2022-04' => int 249990
  '2022-05' => int 258323
  '2022-06' => int 249990
  '2022-07' => int 258323
  '2022-08' => int 258323
  '2022-09' => int 249990
  '2022-10' => int 258323
  '2022-11' => int 249990
  '2022-12' => int 258323
  '2021-01' => int 258323
  '2021-02' => int 233324
  '2021-03' => int 258323
  '2021-04' => int 249990
  '2021-05' => int 258323
  '2021-06' => int 249990
  '2021-07' => int 258323
  '2021-08' => int 258323
  '2021-09' => int 249990
  '2021-10' => int 258323
  '2021-11' => int 249990
  '2021-12' => int 258323
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]

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]

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 
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 

Ten post edytował Tomplus 4.09.2019, 10:25:16

[trueblue]

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.

Ten post edytował trueblue 4.09.2019, 09:50:02

[Tomplus]

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]

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