我已经在一个问题上停留了很长时间了。我必须找到一种方法来显示一周内的平均电影放映量,但对于我的生活,我不知道该怎么做。我查找的所有内容都可以让我获得一段时间内出现的日期的平均值(将它们转换为可以使用平均值但不能使用结果平均值的数据形式。
ERD 图
我可以使用以下方法找到两个日期之间的结果:
SELECT Day(screeningDay) as MovieDay, COUNT(ScreeningDay) as countMovieDay
FROM screening
WHERE Day(ScreeningDay) BETWEEN '20' AND '24'
GROUP BY ScreeningDay
ORDER BY countMovieDay
我需要的是获取该查询结果的平均值。
使用包装查询选择平均值会起作用吗?
SELECT Avg(tmp.numScreenings) FROM (
SELECT COUNT(ScreeningDay) as numScreenings
FROM screening
WHERE Day(ScreeningDay) BETWEEN '20' AND '24'
GROUP BY Day(ScreeningDay)
) as tmp
如果你想要有电影的天数的平均值:
SELECT COUNT(*) / COUNT(DISTINCT DAY(ScreeningDay))
FROM screening
WHERE Day(ScreeningDay) BETWEEN 20 AND 24;
如果要将没有电影的天数计为"0",则:
SELECT COUNT(*) / (24 - 20 + 1)
FROM screening
WHERE Day(ScreeningDay) BETWEEN 20 AND 24;