Home > Software design >  How to calculate daily average frequency in a table in function of GROUP BY from another table?
How to calculate daily average frequency in a table in function of GROUP BY from another table?

Time:02-16

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 

sqlfiddle

  • Related