I want to know which cinema room is more frequented daily and I need the values as relative frequency.
My tables are:
table1:
| client_id | cinema_room |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | C |
| 5 | A |
| 6 | C |
| 7 | B |
| 8 | A |
| 9 | B |
| 10 | B |
| 11 | B |
| 12 | A |
| 13 | C |
| 14 | A |
| 15 | A |
| 16 | B |
Table 2:
| day | client_id |
|---|---|
| 01/01/2022 | 1 |
| 01/01/2022 | 1 |
| 01/02/2022 | 1 |
| 01/02/2022 | 2 |
| 01/02/2022 | 4 |
| 01/03/2022 | 8 |
| 01/04/2022 | 14 |
| 01/04/2022 | 15 |
| 01/04/2022 | 16 |
So I need results like:
| day | cinema_room | avg_freq |
|---|---|---|
| 01/01/2022 | A | 1 |
| 01/01/2022 | B | 0 |
| 01/01/2022 | C | 0 |
| 01/02/2022 | A | 0.33 |
| 01/02/2022 | B | 0.33 |
| 01/02/2022 | C | 0.33 |
| 01/03/2022 | A | 1 |
| 01/03/2022 | B | 0 |
| 01/03/2022 | C | 0 |
| 01/04/2022 | A | 0.66 |
| 01/04/2022 | B | 0.33 |
| 01/04/2022 | C | 0 |
What I've got by now is:
SELECT day , cinema_room, COUNT(t2.client_id) as t2_tot
FROM table1 t1
LEFT JOIN table2 t2 ON t1.client_id = t2.client_id
GROUP BY day, cinema_room
ORDER BY day
Its insufficient results are:
NOTE 1: I'm counting, not even averaging.
NOTE 2: The first three rows are wrong af.
| day | cinema_room | count_freq |
|---|---|---|
| None | A | 0 |
| None | B | 0 |
| None | C | 0 |
| 01/01/2022 | A | 3 |
| 01/01/2022 | B | 0 |
| 01/01/2022 | C | 0 |
| 01/02/2022 | A | 1 |
| 01/02/2022 | B | 1 |
| 01/02/2022 | C | 1 |
| 01/03/2022 | A | 1 |
| 01/03/2022 | B | 0 |
| 01/03/2022 | C | 0 |
| 01/04/2022 | A | 2 |
| 01/04/2022 | B | 1 |
| 01/04/2022 | C | 0 |
Clearly I'm halfway to averaging. Can someone help me, please?
CodePudding user response：
You can try to use CROSS JOIN in a subquery which would be a calendar table, then aggregate table1 & table2 in another subquery the get count by each cinema_room per day.
SELECT t1.day , t1.cinema_room, SUM(IFNULL(t2.cnt,0)) / (SELECT COUNT(*) FROM table2 tt2 WHERE t1.day = tt2.day) as t2_tot
FROM (
SELECT DISTINCT day,cinema_room
FROM table2 t2 CROSS JOIN table1
) t1
LEFT JOIN (
SELECT day,cinema_room,COUNT(*) cnt
FROM table1 t1 INNER JOIN table2 t2
ON t1.client_id = t2.client_id
GROUP BY day,cinema_room
) t2 ON t1.day = t2.day AND t1.cinema_room = t2.cinema_room
GROUP BY t1.day , t1.cinema_room
ORDER BY t1.day