时间戳

首页 > 解决方案 > 计算与多个父级的父子关系的分数

sql-server - 计算与多个父级的父子关系的分数

问题描述

我有这棵树有多个父母

每个节点都有一个权重和分数。我正在尝试计算每个节点的复合函数

Team Score = ( (Node1 Result)*(weight1) + (Node2 Result) *(weight2) ) 
             ---------------------------------------------------------
                                  weight1 + weight2

我设法计算了这个函数，但是当节点有多个父节点时，我的问题开始了

树结构： 在此处输入图像描述

  1. 如果节点类型是当前列表= 查找当前节点，不需要我在树中向下走
  2. 如果节点类型是child , (child&linked list) = 忽略当前节点并往下走
  3. 如果节点类型为(current and child ), (current and child and linked list) = I 包含当前节点并往下走

团队表脚本中的所有节点详细信息

CREATE TABLE teams(
   TeamId            INTEGER  NOT NULL PRIMARY KEY 
  ,TeamName          VARCHAR(4) NOT NULL
  ,TeamParent_TeamId INTEGER 
  ,TeamWeight        INTEGER  NOT NULL
  ,TeamRollupType    VARCHAR(29) NOT NULL
  ,KR                INTEGER  NOT NULL
);
INSERT INTO teams(TeamId,TeamName,TeamParent_TeamId,TeamWeight,TeamRollupType,KR) VALUES (6399,'A1',NULL,1,'CurrentAndChild',0);
INSERT INTO teams(TeamId,TeamName,TeamParent_TeamId,TeamWeight,TeamRollupType,KR) VALUES (6400,'B1',6399,2,'ChildAndLinkedLists',50);
INSERT INTO teams(TeamId,TeamName,TeamParent_TeamId,TeamWeight,TeamRollupType,KR) VALUES (6401,'B3',6399,3,'CurrentAndChildAndLinkedLists',30);
INSERT INTO teams(TeamId,TeamName,TeamParent_TeamId,TeamWeight,TeamRollupType,KR) VALUES (6402,'B2',6399,4,'CurrentAndChild',10);
INSERT INTO teams(TeamId,TeamName,TeamParent_TeamId,TeamWeight,TeamRollupType,KR) VALUES (6403,'B4',6399,1,'Current List',80);
INSERT INTO teams(TeamId,TeamName,TeamParent_TeamId,TeamWeight,TeamRollupType,KR) VALUES (6404,'C4',6403,2,'Current List',50);
INSERT INTO teams(TeamId,TeamName,TeamParent_TeamId,TeamWeight,TeamRollupType,KR) VALUES (6405,'C2',6402,5,'Current List',20);
INSERT INTO teams(TeamId,TeamName,TeamParent_TeamId,TeamWeight,TeamRollupType,KR) VALUES (6406,'C1-A',6400,2,'Current List',20);
INSERT INTO teams(TeamId,TeamName,TeamParent_TeamId,TeamWeight,TeamRollupType,KR) VALUES (6407,'C1-B',6400,2,'Current List',30);
INSERT INTO teams(TeamId,TeamName,TeamParent_TeamId,TeamWeight,TeamRollupType,KR) VALUES (6408,'C3',6401,2,'Current List',40);

-- 这是存储链表的地方

CREATE TABLE linkedlists(
   TeamShortcutId            INTEGER  NOT NULL PRIMARY KEY 
  ,TeamShortcutFor_TeamId    INTEGER  NOT NULL
  ,TeamShortcutParent_TeamId INTEGER  NOT NULL
);
INSERT INTO linkedlists(TeamShortcutId,TeamShortcutFor_TeamId,TeamShortcutParent_TeamId) VALUES (509,6402,6400);
INSERT INTO linkedlists(TeamShortcutId,TeamShortcutFor_TeamId,TeamShortcutParent_TeamId) VALUES (512,6404,6401);

试图

with CTE_UnionLinkedLists as (
select * from teams
union all 
select 
         TeamShortcutFor_TeamId as TeamID ,
         tm.TeamName,
         TeamShortcutParent_TeamId AS TeamParent_TeamId,
        --,tm1.TeamName AS StartName
        TeamWeight=tm.TeamWeight,
        TeamRollupType='Linked List',
        KeyResultScore=tm.KR    
from linkedlists ts
inner join teams tm on tm.teamid=ts.[TeamShortcutFor_TeamId] 
left join teams tm1 on tm1.teamid=ts.TeamShortcutParent_TeamId 
)

 ,rcte as
(
  select 
         t.TeamID as GroupID,
         t.TeamName as GroupName,
         t.TeamRollupType as GroupType,
         t.TeamId,
         t.TeamName,
         t.TeamRollupType,
         --Value
         case t.TeamRollupType
           when 'Child List' then 0 
           when 'ChildAndLinkedLists' then 0 -- if the current node must be ignored, then the current value changes to 0
           else t.KR
         end as Value,
         --Weight
         case t.TeamRollupType
           when 'Child List' then 0 
           when 'ChildAndLinkedLists' then 0-- if the current node must be ignored, then the current value changes to 0
           else t.TeamWeight
         end as WeightValue,

         --Value*Weight
         WeightedScore=
         case t.TeamRollupType
           when 'Child List' then 0 
           when 'ChildAndLinkedLists' then 0-- if the current node must be ignored, then the current value changes to 0
           else t.KR
         end 
         *
         case t.TeamRollupType
           when 'Child List' then 0
           when 'ChildAndLinkedLists' then 0-- if the current node must be ignored, then the current value changes to 0
           else t.TeamWeight
         end 
        

  from  teams t
union all
  select 
         r.GroupID,
         r.GroupName,
         r.GroupType,
         t.TeamId,
         t.TeamName,
         --TeamRollupType
         case r.TeamRollupType
           when 'Current List' then 'Current List' -- if the parent was the end of the sum, then it stays the end of the sum
           else t.TeamRollupType
         end,
         --Value
         case
           when r.TeamRollupType = 'Current List' then 0 -- if the parent was the end of the sum, then the current value changes to 0
           when t.TeamRollupType = 'Child List' then 0 
           when t.TeamRollupType ='ChildAndLinkedLists' then 0-- if the current node must be ignored, then the current value changes to 0
           else t.KR
         end,
         --TeamWeight
         case
           when r.TeamRollupType = 'Current List' then 0 -- if the parent was the end of the sum, then the current value changes to 0
           when t.TeamRollupType = 'Child List' then 0 -- if the current node must be ignored, then the current value changes to 0
           when t.TeamRollupType ='ChildAndLinkedLists' then 0
           else t.TeamWeight
         end,

         --Teamweight*Value
         case
           when r.TeamRollupType = 'Current List' then 0 -- if the parent was the end of the sum, then the current value changes to 0
           when t.TeamRollupType = 'Child List' then 0 -- if the current node must be ignored, then the current value changes to 0
           when t.TeamRollupType ='ChildAndLinkedLists' then 0
           else t.KR

         end 
         *
         case
           when r.TeamRollupType = 'Current List' then 0 -- if the parent was the end of the sum, then the current value changes to 0
           when t.TeamRollupType = 'Child List' then 0 -- if the current node must be ignored, then the current value changes to 0
           when t.TeamRollupType ='ChildAndLinkedLists' then 0
           else t.TeamWeight
         end

  from rcte r
  join CTE_UnionLinkedLists t
    on t.TeamParent_TeamId = r.TeamId 
)
select r.GroupName,
       sum(r.weightedScore)/SUM(CASE When r.Value <> 0 Then r.WeightValue Else 0 End ) as GroupScore,
       string_agg(case when r.Value <> 0 then convert(nvarchar(255), r.Value) end, '+') as GroupSumFormula,
       string_agg(case when r.Value <> 0 then convert(nvarchar(255), r.TeamName) end, '+') as GroupSumTeam,
       string_agg(case when r.Value <> 0 then convert(nvarchar(255), r.WeightValue) end, '+') as GroupSumWeight
       --string_agg(case when r.Value <> 0 then convert(nvarchar(255), r.weightedScore) end, '+') as GroupSumrweightedScore,
    --   sum(r.Value) as GroupSum,
       --SUM(CASE When r.Value <> 0 Then r.WeightValue Else 0 End ) as TotalWeight,
       --sum(r.weightedScore) as GroupWeightedSum
       

from rcte r
group by r.GroupName
order by r.GroupName;

预期结果

+----------+-------+
| TeamName | Score |
+----------+-------+
| A1       |    30 |-- I have issue here
| B1       |  20.5 |-- I have issue here
| B2       |    16 |
| B3       |  38.5 |-- I have issue here, by chance I got same result because C4 is last node
| B4       |    80 |
| C1-A     |    20 |
| C1-B     |    30 |
| C2       |    20 |
| C3       |    40 |
| C4       |    50 |
+----------+-------+

在我用于 B1 的代码中，我正在这样做

如果类型有链表，我不应该将所有权重和 KR 加在一起

Wrong B1 =(2*20)+(2*30)+(4*10)+(5*20)
           ---------------------------- =18
               2+2+4+5

我应该首先计算 B1 分数，即 16 ，然后在其权重 4 内再次使用它

correct B1 = (2*20)+(2*30)+(4*16)
             -------------------- =20.5
                     2+2+4

标签： sql-serverssms

解决方案

推荐阅读