组成员之间的拆分计数

Question

我有一个SQL结果，看起来像这样：

+-----------+------------+
| AGENTCODE |  DEAL_ID   |
+-----------+------------+
|    110434 | SO20001353 |
|    108415 | SO20001344 |
|    106188 | SO20001344 |
|    107689 | SO20001342 |
|    109150 | SO20001340 |
|    109150 | SO20001341 |
+-----------+------------+

如果我按代理代码分组并计算deal_id，我会为每个代理获得多个交易，
从逻辑上讲，如果一个交易有多个代理，则每个代理应该为该交易获得1/个代理。这是SQL的一个选项吗？还是我需要为此编写一个程序？

我想要的结果是这样的：

+-----------+-------+
| AGENTCODE | DELAS |
+-----------+-------+
|    110434 |     1 |
|    108415 |   0.5 |
|    106188 |   0.5 |
|    107689 |     1 |
|    109150 |     2 |
+-----------+-------+

谢谢。

当前代码

SELECT d.AGENTCODE,
SUM(c.DEAL_COUNT) AS DEALS
FROM (
SELECT AGENTS.AGENTCODE,
ORDERS.ORD
FROM AGENTS,
BRANCHES,
CPROFTYPES,
ORDERS,
ORDSTATUS,
ORDERITEMS,
TRANSPAYMENT,
TRANSORDER
WHERE ORDERITEMS.EPI_AGENT = AGENTS.AGENT
AND ORDERITEMS.PART = 25 /*5003*/
AND ORDERS.ORD = ORDERITEMS.ORD
AND ORDERS.BRANCH = BRANCHES.BRANCH
AND ORDERS.ORDTYPE = CPROFTYPES.CPROFTYPE
AND TRANSORDER.EPI_PAYDATE BETWEEN 16830720 AND 16917120
AND ORDERS.ORDSTATUS = ORDSTATUS.ORDSTATUS
AND ORDSTATUS.ORDSTATUSDES != 'canceled'
AND TRANSPAYMENT.ORD = ORDERS.ORD
AND TRANSPAYMENT.TRANS = TRANSORDER.TRANS
AND TRANSORDER.EPI_CKPAY = 'Y'
AND TRANSORDER.TRANS != 0
AND NOT EXISTS(SELECT 'X'
FROM TRANSPAYMENT TRP
, TRANSORDER TRS
WHERE TRP.ORD = ORDERS.ORD
AND TRP.TRANS = TRS.TRANS
AND TRS.EPI_PAYDATE
> 16917120
AND TRS.TRANS != 0)
AND CPROFTYPES.TYPECODE IN ('21', '22', '26', '29')
) AS d
CROSS APPLY (SELECT 1.00 / COUNT(*) AS DEAL_COUNT FROM ORDERS WHERE ORD = d.ORD) c
GROUP BY d.AGENTCODE
ORDER BY d.AGENTCODE;

Answer 1

select AGENTCODE,   
COUNT(AGENTCODE ) OVER (PARTITION  BY  DEAL_ID) * 1.00  / COUNT( DEAL_ID) OVER (PARTITION  BY   AGENTCODE) * 1.00  AS DELAS 
FROM Data; 

演示：https://rextester.com/ENK8799

结果：

AGENTCODE   DEAL_ID
1   110434  SO20001353
2   108415  SO20001344
3   106188  SO20001344
4   107689  SO20001342
5   109150  SO20001340
6   109150  SO20001341
AGENTCODE   DELAS
1   106188  2,000000000000000
2   107689  1,000000000000000
3   108415  2,000000000000000
4   109150  0,500000000000000
5   109150  0,500000000000000
6   110434  1,000000000000000

Answer 2

一种可能的方法：

表：

CREATE TABLE Data (
AGENTCODE int,
DEAL_ID varchar(10)
)
INSERT INTO Data
(AGENTCODE, DEAL_ID)
VALUES
(110434, 'SO20001353'),
(108415, 'SO20001344'),
(106188, 'SO20001344'),
(107689, 'SO20001342'),
(109150, 'SO20001340'),
(109150, 'SO20001341')

声明：

SELECT d.AGENTCODE, SUM(c.DEAL_COUNT) AS DEAL_COUNT
FROM Data d
CROSS APPLY (SELECT 1.00 / COUNT(*) AS DEAL_COUNT FROM Data WHERE DEAL_ID = d.DEAL_ID) c
GROUP BY d.AGENTCODE
ORDER BY d.AGENTCODE

结果：

AGENTCODE  DEAL_COUNT
106188     0.5000000000000
107689     1.0000000000000
108415     0.5000000000000
109150     2.0000000000000
110434     1.0000000000000