sql - sql计算每个组的运行总数和百分比
问题描述
我正在使用 SQL Server 2017 根据每个符号、created_date 和 call_put值计算一些列值。在这里,我只发布一个符号,即PFE作为示例,但我在真实桌子上有很多。看来我需要窗口/透视来计算百分比和总和。
当前查询,
select symbol, created_date ,call_put,sum(volume) as ContractCount,sum(premium) as totalamount
from options_flow of2 where symbol ='PFE' GROUP by call_put,created_date,symbol
电流输出:
symbol|created_date|call_put|ContractCount|totalamount|
------+------------+--------+-------------+-----------+
PFE | 2021-06-11|CALL | 3087| 260754|
PFE | 2021-06-11|PUT | 2445| 167671|
预期输出:
symbol|created_date|CallContractCount|PutContractCount|calltotalamount|puttotalamount|CallsContractPercentage|PUTsContractsPercentage| TotalCount
------+------------+-----------------+----------------+---------------+---------------+---------------------------+------------------------------
PFE | 2021-06-11 | 3087 | 2445 | 260754 |167671 | (3087 / (3087+2445))*100 | (2445 / (3087+2445))*100 | 3087+2445
我试过的:
SELECT
created_date, symbol,
(SUM(CallsContractCount) / NULLIF (SUM(CallsContractCount) + SUM(PutsContractCount), 0) * 100) CallsContractPercentage,
(SUM(PutsContractCount) / NULLIF (SUM(CallsContractCount) + SUM(PutsContractCount), 0) * 100) PUTsContractsPercentage,
SUM(CallsTotalAmount) CallsTotalAmount , SUM(PUTstotalamount) PUTstotalamount,
SUM(CallsContractCount) CallsContractCount,
(SUM(CallsContractCount) + SUM(PutsContractCount)) TotalCount,
SUM(PutsContractCount) PutsContractCount
FROM
(
select created_date, symbol,call_put ,
CASE WHEN call_put = 'CALL' THEN volume ELSE 0 END CallsContractCount,
CASE WHEN call_put = 'PUT' THEN volume ELSE 0 END PutsContractCount,
CASE WHEN call_put = 'CALL' THEN premium ELSE 0 END CallsTotalAmount,
CASE WHEN call_put = 'PUT' THEN volume ELSE 0 END PUTstotalamount
from options_flow
) t WHERE symbol = 'PFE'
GROUP BY created_date, symbol, call_put
解决方案
您可以将聚合与其他逻辑一起使用:
select cs.*,
(call_volume * 1.0 / (put_volume + call_volume)) as CallsContractPercentage,
(put_volume * 1.0 / (put_volume + call_volume)) as PutContractPercentage,
(put_volume + call_volume) as total_count
from (select symbol,
sum(case when call_put = 'Call' then volume end) as call_volume,
sum(case when call_put = 'Call' then premium end) as call_premium,
sum(case when call_put = 'Put' then volume end) as put_volume,
sum(case when call_put = 'Put' then premium end) as put_premium
from options_flow of2
where symbol ='PFE'
group by created_date, symbol
) cs;
推荐阅读
- javascript - Angular 6,Materialize:初始化javascript
- sql - 单个空格的正则表达式,然后是数字
- matplotlib - ModuleNotFoundError: No module named 'mpl_toolkits.basemap' 如何安装底图
- mapkit - 苹果地图不显示地图覆盖
- c++ - 在基类中存储指向派生类函数的指针
- python - 将列表中的嵌入列表写为文件中的行
- node.js - 在 Azure 中创建 NodeJS Web 应用程序失败
- windows - 我可以将 iPhone 连接到 Windows 10 机器以查看我在 Flutter 中的工作吗?
- java - 重置单选按钮中的数据
- mysql - Sequelize 更新忽略无效的列名