时间戳

首页 > 解决方案 > 为聚合列添加小计列

sql - 为聚合列添加小计列

问题描述

这是我的交易、交易者和交易对手数据集：

TRADER_ID  | TRADER_NAME     | EXEC_BROKER | TRADE_AMOUNT  | TRADE_ID
    ABC123 | Jules Winnfield | GOLD        | 10000         | ASDADAD
    XDA241 | Jimmie Dimmick  | GOLD        | 12000         | ASSVASD
    ADC123 | Vincent Vega    | BARC        | 10000         | ZXCZCX
    ABC123 | Jules Winnfield | BARC        | 15000         | ASSXCQA
    ADC123 | Vincent Vega    | CRED        | 250000        | RFAQQA
    ABC123 | Jules Winnfield | CRED        | 5000          | ASDQ23A
    ABC123 | Jules Winnfield | GOLD        | 5000          | AVBDQ3A

我希望生成一份可重复的月度报告，让我可以查看在交易对手（EXEC_BROKER 字段）级别汇总的交易活动，并带有小计 - 如下所示：

TRADER_ID | TRADER_NAME | NO._OF_CCP_USED | CCP | TRADED_AMT_WITH_CCP | VALUE_OF_TOTAL_TRADES | TRADES_WITH_CCP | TOTAL_TRADES
    ABC123 | Jules Winnfield | 3 | GOLD | 15000 | 35000 | 2 | 4
    ABC123 | Jules Winnfield | 3 | BARC | 15000 | 35000 | 1 | 4
    ABC123 | Jules Winnfield | 3 | CRED | 5000 | 35000 | 1 | 4

...等等其余的。

这个想法是汇总每个交易对手的交易数量（我已经使用计数函数完成了），以及与 ccp 的交易金额的总和，但我正在努力让每个交易者旁边的“小计”字段如图所示在上面我想要的输出中 - 所以你可以在这里看到 Jules 总共处理了 3 个交易对手，他们之间有 4 笔交易，总金额为 35000。

我曾尝试使用聚合和 over by 函数的组合，但无济于事。

SELECT
     OT.TRADER_ID,
     OT.TRADER_NAME,
     OT.EXEC_BROKER,
     SUM(OT.TRADE_AMOUNT) AS VALUE_OF_TOTAL_TRADES,
     COUNT(OT.TRADE_ID) AS TOTAL_TRADES,
     COUNT(OT.EXEC_BROKER) OVER PARTITION BY (OT.TRADER_ID) AS NO._OF_CCP_USED, 
     SUM(OT.TRADE_AMOUNT) OVER PARTITION BY (OT.EXEC_BROKER) AS TRADED_AMT_WITH_CCP,
     COUNT(OT.TRADE_ID) OVER PARTITION BY (OT.EXEC_BROKER) AS TRADES_WITH_CCP

FROM dbo.ORDERS_TRADES OT

GROUP BY OT.TRADER_ID, OT.TRADER_NAME, OT.EXEC_BROKER, OT.TRADE_AMOUNT, OT.TRADE_ID

上面的代码运行但返回数百万行。当我按行删除分区时，我得到所需的结果减去我正在寻找的小计列。

请问有什么建议吗？非常感谢！

编辑：

最终代码为我提供了所需的输出：更新我的问题以提供此响应（感谢 Gordon Linoff），以便其他人可以受益：

SELECT
         OT.TRADER_ID,
         OT.TRADER_NAME,
         OT.EXEC_BROKER,
         RANK() OVER (PARTITION BY OT.TRADER_ID ORDER BY 
                SUM(OT.TRADE_AMOUNT) DESC) AS CCP_RANK,
         SUM(OT.TRADE_AMOUNT) AS TRADED_AMT_WITH_CCP,
         SUM(SUM(OT.TRADE_AMOUNT)) OVER (PARTITION BY OT.TRADER_ID) AS 
         VALUE_OF_TOTAL_TRADES,
         COUNT(*) OVER (PARTITION BY OT.TRADER_ID) AS NUM_OF_CCP_USED,
         SUM(COUNT(OT.TRADE_ID)) OVER (PARTITION BY OT.TRADER_ID) AS 
         TOTAL_TRADES
    
    FROM dbo.ORDERS_TRADES OT
    
    GROUP BY OT.TRADER_ID, OT.TRADER_NAME, OT.EXEC_BROKER

标签： sqlsql-serveraggregate-functionswindow-functions

解决方案

你似乎想要：

SELECT OT.TRADER_ID, OT.TRADER_NAME, OT.CCP,
       COUNT(*) OVER (PARTITION BY OT.TRADER_ID) as NUM_CCP,
       SUM(OT.TRADED_AMT) AS TRADED_AMT_WITH_CCP,
       SUM(SUM(OT.TRADED_AMT)) OVER (PARTITION BY OT.TRADER_ID) AS VALUE_OF_TOTAL_TRADES,
       COUNT(OT.TRADE_ID) AS CCP_TRADES,
       SUM(COUNT(OT.TRADE_ID)) OVER (PARTITION BY OT.TRADER_ID) AS TOTAL_TRADES
FROM ORDERS_TRADES OT
GROUP BY OT.TRADER_ID, OT.TRADER_NAME, OT.CCP;

我不确定您的查询与您想要的结果有什么关系。这些列与您的要求无关。

这是一个 db<>fiddle。

推荐阅读