基于AGENT_RANKING的分裂求和方法

Question

POL_NO  AGENT   AGENT_RANKING   SPLIT
100     001      1.1            0.1500
100     002      1.2            0.5000
100     003      1.3            0.3500
100     004      2.1            0.1500
100     005      2.2            0.5000
100     006      2.3            0.2500
100     007      3.1            0.5000
100     008      3.2            0.5000

基于AGENT_RANKING(1.1到1.3和3.1到3.2)的拆分之和应该是1.0000。我想填充下面的RESULT.CAN，有人帮助吗？它能用循环来完成吗？

结果：

POL_NO  AGENT   AGENT_RANKING   SPLIT
100     001      1.1            0.1500
100     002      1.2            0.5000
100     003      1.3            0.3500
100     007      3.1            0.5000
100     008      3.2            0.5000

Answer 1

设置：

Create Table #tbl
(
pol_no int,
agent Char(3),
agent_ranking Float,
split Decimal(5,4)
)
Insert Into #tbl Values

(100,'001',1.1,0.1500),
(100,'002',1.2,0.5000),
(100,'003',1.3,0.3500),
(100,'004',2.1,0.1500),
(100,'005',2.2,0.5000),
(100,'006',2.3,0.2500),
(100,'007',3.1,0.5000),
(100,'008',3.2,0.5000)
;

查询：

With cte As
(
Select 
  *,
  Sum(split) over (partition by pol_no, Floor(agent_ranking)) as splitsum
From #tbl
)
Select * From cte Where splitsum = 1

Answer 2

这是一个空隙和岛屿问题。您可以尝试创建一个行号来表示组。

使用SUM按组号获得总计，然后再次进行连接。

WITH CTE AS (
    SELECT  *,ROW_NUMBER() OVER(PARTITION BY floor(cast(AGENT_RANKING as money)),POL_NO order by AGENT_RANKING) -
                row_number() OVER(PARTITION BY POL_NO order by AGENT_RANKING) grp
    FROM T
),CTE2 AS (
    SELECT grp
    FROM CTE
    GROUP BY grp
    HAVING SUM(SPLIT) = 1
)
SELECT t1.*
FROM CTE t1 JOIN CTE2 t2 on t1.grp = t2.grp

木琴

Answer 3

您的结果可以通过以下方式返回：

select t.*
from t
where t.agent_ranking between 1.1 and 1.3;

除数已经等于1。如果要保证这一点，可以使用窗口函数重新计算它们：

select t.*,
       ( split / sum(split) over (partition by pol_no) ) as new_split
from t
where t.agent_ranking between 1.1 and 1.3;

如果您只想得到之和为1的初始代理，那么：

select t.*
from (select t.*,
             sum(split) over (partition by pol_no order by agent_ranking) as running_sum
      from t
     ) t
where (running_sum - split) < 1
order by pol_no, agent_ranking;

这将返回前三个代理，因为它们的和是第一个命中或超过1的。