mysql - mysql查询以获取表中某些字段的百分比?
问题描述
我有费用表:
Expense(amount: Double, vendorId: Int, vendorType: Int)
我想创建一个 mysql 查询,它将为我提供每种供应商类型与特定供应商的百分比,例如:
vendorId vendorType percentOfExpencesOfThisType totalExpenses
所以可以说我有4个费用:
Expense(amount: 30.0, vendorId: 3, vendorType: 1)
Expense(amount: 58.5, vendorId: 3, vendorType: 1)
Expense(amount: 47.0, vendorId: 3, vendorType: 7)
Expense(amount: 21.5, vendorId: 3, vendorType: 13)
所以表格看起来:
vendorId vendorType percentOfExpencesOfThisType totalExpenses
3 1 50 4
3 7 25 4
3 13 25 4
我该怎么做?(不幸的是使用 mysql 5.6 版)
解决方案
您可以使用聚合和窗口函数:
select vendorId, vendorType,
100 * count(*) / sum(count(*)) over (partition by vendorId) as percentOfExpensesOfThisType,
sum(count(*)) over (partition by vendorId) as totalExpenses
from expense
group by vendorId, vendorType;
从 MySQL 8+ 开始可以使用窗口函数。
在早期版本中,您将加入两个聚合查询:
select vendorId, vendorType,
100 * vt.cnt / v.cnt as percentOfExpensesOfThisType,
v.cnt as totalExpenses
from (select vendorId, vendorType, count(*) as cnt
from expense
group by vendorId, vendorType
) vt join
(select vendorId, count(*) as cnt
from expense
group by vendorId
) v
on vt.vendorId = v.vendorId;
推荐阅读
- tableau-api - 从 tableau javascript api 解析从 rememberCustomViewAsync 收到的响应
- java - 将linux路径转换为windows路径
- python - 以最有效的方式组合数字
- hyperledger-fabric - IBM Blockchain Platform V2、API 和链码
- python - 如何解决错误 EGL Driver message (Error) eglQueryDeviceAttribEXT: Bad attribute using Selenium and Python
- bash - 如何查找文件名以字母开头并将其保存到变量并将其保存到var?
- excel - 需要替换工作表名称
- ionic3 - 如何从 API 调用中获取属性?
- python - GeoAlchemy 中所有 Shapely 类型的一列
- python - Python - 使用 GetRows() 函数时出现 EOF/BOF 异常(来自 MDX 查询)