文章/答案/技术大牛

发布

社区首页 >问答首页 >SQL/DAX中的多父层次结构传播

问SQL/DAX中的多父层次结构传播
EN

Stack Overflow用户

提问于 2019-02-23 02:26:12

回答 4查看 1.6K关注 0票数 5

假设我有一个表，描述了每个工作人员的主要和次要报告关系。让我们想象一个组织结构，其中首席执行官，雇员0，有两个经理(1和2)向他报告。

2经理在她的团队中有2名员工(3和4)，但是4实际上是在1经理的时区工作，所以当他把2作为他的主要报告时，他也向1经理报告作为第二报告，这样1就可以完成正常的信托管理义务(提供支持等)。

除了为员工4担任二级管理角色外，2经理还有一名向他报告的团队成员(5)。

编辑：为了说明多父母问题，让我们给团队成员4一个实习生，给工作人员6。团队成员6 现在是管理人员 1 和 2的下属，后者是通过二级报告继承的。

组织结构将如下所示：

+--+-------+---------+
|ID|Primary|Secondary|
|0 |NULL   |NULL     |
|1 |0      |NULL     |
|2 |0      |NULL     |
|3 |1      |NULL     |
|4 |1      |2        |
|5 |2      |NULL     |
|6 |4      |NULL     |
+--+-------+---------+

现在，我想将其扩展到一个SQL视图中，该视图为我提供了一个列在任何给定工作人员以下的人员列表，包括主要报告和次要报告。因此，对于2 (主次报告的经理)，我希望看到团队成员4和5，而对于首席执行官(0)，我希望看到的是除了首席执行官以外的其他工作人员。我们的新实习生6是首席执行官1和2以及他的直接经理4的下属。

这个应该是这样的：

+--+-----------+
|ID|Subordinate|
|0 |1          |
|0 |2          |
|0 |3          |
|0 |4          |
|0 |5          |
|0 |6          |
|1 |3          |
|1 |4          |
|1 |6          |
|2 |4          |
|2 |5          |
|2 |6          |
|4 |6          |
+--+-----------+

我如何在SQL中实现这一点？我正在考虑对ID进行某种OUTER APPLY操作，但我很难解决这个问题所需要的重入性(我认为)。我的背景是过程编程，我认为这是我在这里挣扎的原因之一。

NB：我想在这里预测的一个显而易见的问题是“这肯定是一个XY问题--你到底为什么要这么做？”

我想在PowerBI中使用PowerBI，让每个工作人员能够在组织结构中获得关于他们下面的个人的某些信息。不幸的是，RLS不允许每个人执行存储过程，所以我不得不执行这个组合扩展，然后简单地基于登录过滤上面的表。

话虽如此，我还是愿意用更好的方法来解决这个问题。

tsql

powerbi

dax

ssas-tabular

sql-server

回答 4

Stack Overflow用户

回答已采纳

发布于 2019-02-26 20:51:09

要在SQL中获得所需的结果，最简单的方法是使用递归的CTE。

在下面的例子中，我将工作分成两个CTE。第一种方法将集合转换成经理和下属的配对。第二个CTE从第一个获得所有结果，然后使用UNION连接到自己，其中第一个CTE的管理器是递归CTE中的一个下属。这将不断重复，直到没有匹配可以作出。

因为下属可能有多个管理器，所以可以为每个祖先返回重复行。因为这一点，所以在从递归CTE返回结果时使用不同。

WITH all_reports AS (
    SELECT [Primary] [ManagerID], ID [Subordinate]
    FROM tbl
    WHERE [Primary] IS NOT NULL
    UNION
    SELECT [Secondary], ID
    FROM tbl
    WHERE [Secondary] IS NOT NULL
)
, recursive_cte AS (
    SELECT ManagerID, Subordinate
    FROM all_reports
    UNION ALL
    SELECT ancestor.ManagerID, descendant.Subordinate
    FROM recursive_cte ancestor
    INNER JOIN all_reports descendant ON descendant.ManagerID = ancestor.Subordinate
)
SELECT DISTINCT ManagerID, Subordinate
FROM recursive_cte

如果希望获得经理和下属之间的距离，请按照以下方式重写递归CTE：

SELECT ManagerID, Subordinate, 1 [Distance]
FROM all_reports
UNION ALL
SELECT ancestor.ManagerID, descendant.Subordinate, ancestor.Distance + 1
FROM recursive_cte ancestor
INNER JOIN all_reports descendant ON descendant.ManagerID = ancestor.Subordinate

票数 2

Stack Overflow用户

发布于 2019-02-23 16:16:02

使用DAX中的父-子层次结构函数很容易解决这个问题。我不认为您需要构建任何额外的表，只需在RLS规则中添加以下条件：

对于员工N，只需检查

PATHCONTAINS(PATH('Hierarchy'[ID], 'Hierarchy'[Primary]), N)

或

PATHCONTAINS(PATH('Hierarchy'[ID], 'Hierarchy'[Secondary]), N)

请注意，这允许员工N看到自己和他们的下属，但如果您不想这样做，您可以添加一个额外的条件。

编辑：当您的结构不是树时，问题变得更加困难。这是一种应该有效的方法。

对于每个ID，查找下属以获得Level1，搜索Level1以查找下一级下属，等等，直到不存在下属。(如果结构中有一个循环将您返回到更高的级别，那么您将陷入递归。)

在这种情况下，有三个层次以下的顶部，所以我们需要三个步骤。

| ID | Primary | Secondary | Level1 | Level2 | Level3 |
|----|---------|-----------|--------|--------|--------|
| 0  |         |           | 1      | 4      | 6      |
| 0  |         |           | 2      | 4      | 6      |
| 0  |         |           | 2      | 5      |        |
| 0  |         |           | 3      |        |        |
| 1  | 0       |           | 4      | 6      |        |
| 2  | 0       |           | 4      | 6      |        |
| 2  | 0       |           | 5      |        |        |
| 3  | 0       |           |        |        |        |
| 4  | 1       | 2         | 6      |        |        |
| 5  | 2       |           |        |        |        |
| 6  | 4       |           |        |        |        |

下面是在中执行此操作的M代码：

let
    Source = Table.FromRows({{0,null,null},{1,0,null},{2,0,null},{3,0,null},{4,1,2},{5,2,null},{6,4,null}},{"ID", "Primary", "Secondary"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Primary", Int64.Type}, {"Secondary", Int64.Type}}),
    SearchNextLevel = ExpandNext(ExpandNext(ExpandNext(#"Changed Type", "Level1", "ID"), "Level2", "Level1"), "Level3", "Level2"),
    #"Appended Query" =
        Table.Combine(
            {Table.RenameColumns(Table.SelectColumns(SearchNextLevel, {"ID", "Level1"}), {"Level1","Subordinate"}),
             Table.RenameColumns(Table.SelectColumns(SearchNextLevel, {"ID", "Level2"}), {"Level2","Subordinate"}),
             Table.RenameColumns(Table.SelectColumns(SearchNextLevel, {"ID", "Level3"}), {"Level3","Subordinate"})}
        ),
    #"Filtered Rows" = Table.SelectRows(#"Appended Query", each ([Subordinate] <> null)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"ID", Order.Ascending}, {"Subordinate", Order.Ascending}})
in
    #"Sorted Rows"

下面是使用多次扩展到下一个级别的自定义函数：

let
    ExpandToNextLevel = (T as table, NextLevel as text, ThisLevel as text) as table =>
    let
        SearchNextLevel =
        Table.AddColumn(T,
            NextLevel,
            (C) =>
                Table.SelectRows(
                    T, each Record.Field(C, ThisLevel) <> null and
                       ([Primary] = Record.Field(C, ThisLevel) or
                        [Secondary] = Record.Field(C, ThisLevel))
                    )[ID]
        ),
        ExpandColumn = Table.ExpandListColumn(SearchNextLevel, NextLevel)
    in
        ExpandColumn
in
    ExpandToNextLevel

为了使这个通用化，我显然需要将展开并附加到一个递归循环中。如果时间允许，我会再谈这件事。

编辑：，这是查询的递归版本，它使用取消旋转而不是附加。

let
    Source = Table.FromRows({{0,null,null},{1,0,null},{2,0,null},{3,0,null},{4,1,2},{5,2,null},{6,4,null}},{"ID", "Primary", "Secondary"}),
    #"Changed Types" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Primary", Int64.Type}, {"Secondary", Int64.Type}}),
    IDCount = List.Count(List.Distinct(#"Changed Types"[ID])),
    RecursiveExpand = List.Generate(
        () => [i=0, InputTable = #"Changed Types"],
        each [i] < IDCount and
             List.NonNullCount(List.Last(Table.ToColumns([InputTable]))) > 0,
        each [
             CurrentLevel = if [i] = 0 then "ID" else "Level" & Text.From([i]),
             NextLevel = if [i] = 0 then "Level1" else "Level" & Text.From([i]+1),
             InputTable = ExpandNext([InputTable], NextLevel, CurrentLevel),
             i = [i] + 1
        ]
    ),
    FinalTable = List.Last(RecursiveExpand)[InputTable],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(FinalTable, {"Secondary", "Primary", "ID"}, "Level", "Subordinate"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"ID", "Subordinate"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"ID", Order.Ascending}, {"Subordinate", Order.Ascending}})
in
    #"Sorted Rows"

它将继续扩展级别，直到扩展到下一个级别为止，生成所有空值或命中级别的最大数量，以防止无限循环。

票数 4

Stack Overflow用户

发布于 2019-02-23 16:03:25

您将需要将报告层次结构和次要报告层次结构都扁平化，并将它们加载到表格模型中的单独表中。

有关如何在DAX中完全完成此操作，请参见DAX模式:父-子层次结构。也可以使用递归公共表表达式使用Server查询来夷平这两个层次结构。

在这两种情况下，它们都成为模型中的两个独立的表，以及两个独立的关系，然后您可以在RLS过滤器中引用这些关系。

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/54837627

复制

相似问题

问SQL/DAX中的多父层次结构传播
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL/DAX中的多父层次结构传播EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL/DAX中的多父层次结构传播
EN