我有一张表，下面有三栏
Date table_name count
1/6/2023 table1 5161454381
1/6/2023 table2 286759521
1/6/2023 table3 43756630
1/6/2023 table4 27032087
1/6/2023 table5 44189394
1/6/2023 table6 63475022
1/5/2023 table1 5362300063
1/5/2023 table2 289459360
1/5/2023 table3 59042261
1/5/2023 table4 37256587
1/5/2023 table5 55511764
1/5/2023 table6 77917391
1/4/2023 table1 5316875841
1/4/2023 table2 282894202
1/4/2023 table3 57226525
1/4/2023 table4 36150830
1/4/2023 table5 54099874
1/4/2023 table6 75897787
1/3/2023 table1 5301932742
1/3/2023 table2 281278819
我需要添加一个平均为每一天以及每一桌.举个例子， 假设日期是2023-01-06，表格是表1，我需要得到过go 两周同一星期的平均值.平均2023-01-06(星期五)的平均值是用2022-12-30和2022-12-23计算的.两个都是星期五.那么它应该会显示出来.然后，我需要将其显示为数据透视表.
我try 了如下，但平均参与是不正确的，有人能帮忙改变那部分以获得所需的输出吗？
SELECT
t1.table_name as table_name,
MAX(CASE WHEN table_date=DATE_SUB(CURDATE(), INTERVAL 4 DAY) THEN count END) AS '$date04',
MAX(CASE WHEN table_date=DATE_SUB(CURDATE(), INTERVAL 3 DAY) THEN count END) AS '$date03',
MAX(CASE WHEN table_date=DATE_SUB(CURDATE(), INTERVAL 2 DAY) THEN count END) AS '$date02',
MAX(CASE WHEN table_date=DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN count END) AS '$date01',
t2.AVG
FROM tbl t1
INNER JOIN
(
SELECT table_name, AVG(count) AS AVG
FROM tbl
where table_date >= DATE(NOW() - INTERVAL 14 DAY)
GROUP BY table_name
) t2
ON t2.table_name = t1.table_name
where table_date >= DATE(NOW() - INTERVAL 4 DAY) and t1.table_name not in ('table7')
group by t1.table_name;