我正在尝试计算在可变时间线上每小时，一周中每天的平均通话次数。到目前为止，我已经能够解析来电并为他们分配一周中的一天。这是我正在处理的表的摘录：
+------+------+------+------+------+
| day | H1 | H2 | H3 | H4 |
+------+------+------+------+------+
| Tue | 1 | 1 | 3 | 5 |
| Wed | 3 | 2 | 3 | 4 |
| Thu | 0 | 3 | 0 | 5 |
| Fri | 3 | 2 | 4 | 11 |
| Sat | 0 | 0 | 0 | 1 |
| Sun | 0 | 0 | 0 | 2 |
| Mon | 1 | 2 | 2 | 8 |
| Tue | 1 | 1 | 2 | 6 |
| Wed | 1 | 1 | 4 | 3 |
| Thu | 0 | 1 | 2 | 3 |
| Fri | 0 | 3 | 3 | 6 |
| Sat | 0 | 0 | 0 | 4 |
| Sun | 0 | 0 | 0 | 0 |
| Mon | 1 | 2 | 2 | 8 |
| Tue | 1 | 4 | 2 | 7 |
| Wed | 1 | 3 | 2 | 6 |
| Thu | 1 | 1 | 2 | 6 |
我很难尝试找出一种方法来将每天的每个小时列加总，然后取平均值。
最终结果应该是这样的
Day H1 H2 H3 H4
Tue 1 2 2.33 6
Wed 1.66 2 3 4.33
Thu 0.33 1.66 1.33 4.66
每天加起来的每一列，然后除以3，即当天的实例数。请记住，每天实例的数量会发生变化，例如星期一和星期五仅在表上显示两次。
我一直在努力寻找一种干净的方法，但是我也很难弄清楚如何在给定的时间范围内每天进行计数。
谢谢，麻烦您了！
DW
最佳答案
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,day CHAR(3) NOT NULL
,H INT NOT NULL
,val INT NOT NULL
);
INSERT INTO my_table (day,h,val) VALUES
('Tue' , 1 , 1),
('Wed' , 1 , 3),
('Thu' , 1 , 0),
('Fri' , 1 , 3),
('Sat' , 1 , 0),
('Sun' , 1 , 0),
('Mon' , 1 , 1),
('Tue' , 1 , 1),
('Wed' , 1 , 1),
('Thu' , 1 , 0),
('Fri' , 1 , 0),
('Sat' , 1 , 0),
('Sun' , 1 , 0),
('Mon' , 1 , 1),
('Tue' , 1 , 1),
('Wed' , 1 , 1),
('Thu' , 1 , 1),
('Tue' , 2 , 1),
('Wed' , 2 , 2),
('Thu' , 2 , 3),
('Fri' , 2 , 2),
('Sat' , 2 , 0),
('Sun' , 2 , 0),
('Mon' , 2 , 2),
('Tue' , 2 , 1),
('Wed' , 2 , 1),
('Thu' , 2 , 1),
('Fri' , 2 , 3),
('Sat' , 2 , 0),
('Sun' , 2 , 0),
('Mon' , 2 , 2),
('Tue' , 2 , 4),
('Wed' , 2 , 3),
('Thu' , 2 , 1),
('Tue' , 3 , 3),
('Wed' , 3 , 3),
('Thu' , 3 , 0),
('Fri' , 3 , 4),
('Sat' , 3 , 0),
('Sun' , 3 , 0),
('Mon' , 3 , 2),
('Tue' , 3 , 2),
('Wed' , 3 , 4),
('Thu' , 3 , 2),
('Fri' , 3 , 3),
('Sat' , 3 , 0),
('Sun' , 3 , 0),
('Mon' , 3 , 2),
('Tue' , 3 , 2),
('Wed' , 3 , 2),
('Thu' , 3 , 2),
('Tue' , 4 , 5),
('Wed' , 4 , 4),
('Thu' , 4 , 5),
('Fri' , 4 , 11),
('Sat' , 4 , 1),
('Sun' , 4 , 2),
('Mon' , 4 , 8),
('Tue' , 4 , 6),
('Wed' , 4 , 3),
('Thu' , 4 , 3),
('Fri' , 4 , 6),
('Sat' , 4 , 4),
('Sun' , 4 , 0),
('Mon' , 4 , 8),
('Tue' , 4 , 7),
('Wed' , 4 , 6),
('Thu' , 4 , 6);
SELECT day,h,AVG(val)x FROM my_table GROUP BY day,h;
+-----+---+--------+
| day | h | x |
+-----+---+--------+
| Fri | 1 | 1.5000 |
| Fri | 2 | 2.5000 |
| Fri | 3 | 3.5000 |
| Fri | 4 | 8.5000 |
| Mon | 1 | 1.0000 |
| Mon | 2 | 2.0000 |
| Mon | 3 | 2.0000 |
| Mon | 4 | 8.0000 |
| Sat | 1 | 0.0000 |
| Sat | 2 | 0.0000 |
| Sat | 3 | 0.0000 |
| Sat | 4 | 2.5000 |
| Sun | 1 | 0.0000 |
| Sun | 2 | 0.0000 |
| Sun | 3 | 0.0000 |
| Sun | 4 | 1.0000 |
| Thu | 1 | 0.3333 |
| Thu | 2 | 1.6667 |
| Thu | 3 | 1.3333 |
| Thu | 4 | 4.6667 |
| Tue | 1 | 1.0000 |
| Tue | 2 | 2.0000 |
| Tue | 3 | 2.3333 |
| Tue | 4 | 6.0000 |
| Wed | 1 | 1.6667 |
| Wed | 2 | 2.0000 |
| Wed | 3 | 3.0000 |
| Wed | 4 | 4.3333 |
+-----+---+--------+
关于mysql - 在设定时间内每天平均每小时的小时数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51899366/