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