mysql - 是否可以使用重复数据加入 2 个视图?
问题描述
我正在创建一个虚拟数据仓库并尝试加入 2 个单独的视图,每个视图都可以正常工作。但是,当组合在一起时,我的 group by 语句中似乎有一些东西导致数据不正确。
create view CentreSizing as
select CentreID, Name,
case
when Capacity between 0 and 199 then 'Small'
when Capacity between 200 and 900 then 'Medium'
else 'Large'
end as Size
from Centre;
create view sizeQuantities as
select cs.Size, count(*) as "Occurences" from
CentreSizing cs
group by cs.Size;
create view AvgSpendings2018 as
select cs.Size, monthname(d.DateStamp) as "Month", sum(e.Cost)/quant.Occurences as
"AverageExpense"
from Expense e join CentreSizing cs on e.CentreID = cs.CentreID
join DateTime d on e.DateTimeID = d.DateTimeID
join sizeQuantities quant on quant.Size = cs.Size
where YEAR(d.DateStamp) = 2018
group by cs.Size, MONTH(d.DateStamp);
create view TotalSpendings2018 as
select e.CentreID, c.Name, cs.Size , monthname(d.DateStamp) as "Month", sum(e.Cost) as
"TotalExpense" from Expense e
left join DateTime d on e.DateTimeID = d.DateTimeID
join Centre c on c.CentreID = e.CentreID
join CentreSizing cs on c.CentreID = cs.CentreID
where year(d.DateStamp) = 2018
group by month(d.DateStamp), e.CentreID;
所有的视图创建都按预期工作,但是当我尝试结合结果来显示每月的总费用时,与类似规模的中心的平均值相比,结果并不像预期的那样。
select ts.CentreID, ts.Name, ts.Size, ts.Month, avg.AverageExpense
from TotalSpendings2018 ts join AvgSpendings2018 avg on
ts.Size = avg.Size
Group by ts.CentreID, ts.Size, ts.Month;
| CentreID | Name | Size | Month | AverageExpense
|
+----------+-------------------------------------+--------+-----------+----------------
+
| 1 | Queen Alexandra Hospital | Large | April | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | August | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | December | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | February | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | January | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | July | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | June | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | March | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | May | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | November | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | October | 1490.0000
|
| 1 | Queen Alexandra Hospital | Large | September | 1490.0000
|
| 2 | Southampton General Hospital | Large | April | 1490.0000
|
| 2 | Southampton General Hospital | Large | August | 1490.0000
|
| 2 | Southampton General Hospital | Large | December | 1490.0000
|
| 2 | Southampton General Hospital | Large | February | 1490.0000
|
| 2 | Southampton General Hospital | Large | January | 1490.0000
|
| 2 | Southampton General Hospital | Large | July | 1490.0000
|
| 2 | Southampton General Hospital | Large | June | 1490.0000
|
| 2 | Southampton General Hospital | Large | March | 1490.0000
|
| 2 | Southampton General Hospital | Large | May | 1490.0000
|
| 2 | Southampton General Hospital | Large | November | 1490.0000
|
| 2 | Southampton General Hospital | Large | October | 1490.0000
|
| 2 | Southampton General Hospital | Large | September | 1490.0000
|
| 3 | St Richard's Hospital | Medium | April | 445.0000
|
| 3 | St Richard's Hospital | Medium | August | 445.0000
|
| 3 | St Richard's Hospital | Medium | December | 445.0000
|
解决方案
查看 TotalSpendings2018 在 GROUP BY 子句中缺少 c.Name(如果每个中心都有唯一的名称,那应该没问题)。虽然与大多数其他 RDBMS 不同,mysql 允许这样做,但这是容易出错且不是良好的编码习惯。
关于查询:
- 它实际上在我看来你不需要 GROUP BY,因为没有使用聚合函数(否则:在 GROUP BY 中缺少 ts.Name 和 avg.AverageExpense)
- 从视图的描述中,我还猜想您缺少“月份”的连接条件,因为此列在两个视图中都可用
- 为什么不在输出中显示 TotalExpense?我会认为这就是你想要做的(否则,你不需要加入)
我(希望受过教育)对您的查询的猜测:
select
ts.CentreID,
ts.Name,
ts.Size,
ts.Month,
avg.AverageExpense,
ts.TotalExpense
from
TotalSpendings2018 ts
join AvgSpendings2018 avg
on ts.Size = avg.Size
and ts.Month = avg.Month
推荐阅读
- regex - 使正则表达式与 sed 一起使用以捕获 git repo 名称
- node.js - 为什么this.props.param.match.id未定义,导致Express报错?
- php - 如何在 WordPress 主题中创建指向页面的链接
- node.js - 猫鼬快递计数器不增加,范围问题
- asp.net-mvc - 使用带有实体框架、UnityConfig 和多租户的 ASP.NET MVC 处理 DB 事务/DbContext
- java - 带有 Undertow 的 MockServer
- c - 堆栈上未初始化数据的不同编译器行为
- airflow - 如何限制 dag 重试次数?
- python - 使用 AWS Lambda 重写 URL 路径
- java - 当使用 Spring MVC 在另一个数据表中单击一行时,使用数据启动一个新的数据表以生成数据