SQL计算列,递归总结层次
[简化]迄今为止的故事:SQL计算列,递归总结层次
在Visual Studio 2010下密度纤维板DB,我有如下表:
CREATE TABLE [dbo].[SandTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[isDone] [bit] NOT NULL,
[percentComplete] AS ([dbo].[CompletePercent]([id],[isDone])),
[parentId] [int] NULL,
CONSTRAINT [PK_SandTable] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ALTER TABLE [dbo].[SandTable] WITH CHECK ADD CONSTRAINT [FK_SandTable_SandTable] FOREIGN KEY([parentId])
的想法是该行形成为树/森林,其中parentId用作父节点的“指针”。
的“PERCENTCOMPLETE”计算列使用函数CompletePercent计算多少子树,在行扎根的,是完整的,如下:
- 如果某行的“isDone”位为1 ,那么我们认为整个子树是100%完成的(这是一个用户覆盖),所以返回1.0。
- 但是,如果'isDone'是0,我需要计算整个子树的'完整性'。我通过递归地平均直接孩子的“完整性”来做到这一点,这对他们的孩子是这样做的,等等直到叶子。
起初,我试着让'CompleteCommon'的平均值为直接子节点'percentComplete'列。但是,正如我发现的(后来在线确认),计算列不能用作计算列计算的一部分。
目前我通过始终得到1“isDone” = 1行; 0“isDone” = 0行,使用下面的实现CompletePercent的沮丧:
CREATE FUNCTION [dbo].[CompletePercent]
(
@id int,
@isDone bit = 0
)
RETURNS float
AS
BEGIN
DECLARE @result float
IF @isDone = 1
SET @result = 1.0
ELSE
SET @result =
(SELECT
CASE
WHEN (COUNT(*) = 0) THEN 0.0
ELSE AVG(dbo.CompletePercent(id, isDone))
END
FROM dbo.SandTable
WHERE parentId = @id
)
RETURN @result
END
我希望有简单的东西在这里,我只是失踪,从盯着它那么久。
我的下一步是尝试使用我正在研究的递归CTE。但是,我并不确定如何编码所需的“特殊”条件平均值。
如果任何人都可以在我的行为中发现错误,或引导我在CTE的方向,我会非常感激。
[编辑:]我已经来到了一个死胡同,即使在CTE的轨道,用下面的疯狂(也可能是浪费的,如果它可以运行)查询:
WITH Weights AS (SELECT SandTable.id, COUNT(NULLIF (SandTable.isDone, 0)) AS isDone, 100.0 AS weight, COUNT(ST.id) AS kids
FROM SandTable INNER JOIN
SandTable AS ST ON SandTable.id = ST.parentId
WHERE (SandTable.parentId IS NULL)
GROUP BY SandTable.id
UNION ALL
SELECT SandTable_1.id, COUNT(NULLIF (SandTable_1.isDone, 0)) AS isDone, MyCTE_2.weight/MyCTE_2.kids AS weight, COUNT(ST_1.id) AS kids
FROM SandTable AS SandTable_1 INNER JOIN
MyCTE AS MyCTE_2 ON SandTable_1.parentId = MyCTE_2.id AND MyCTE_2.isDone = 0 INNER JOIN
SandTable AS ST_1 ON SandTable.id = ST_1.parentId
WHERE (SandTable_1.parentId IS NOT NULL)
GROUP BY SandTable_1.id)
SELECT SUM(weight)
FROM Weights AS Weights_1
WHERE (isDone > 0)
当时的想法是沿着层次结构(目前来自根,但我计划修改它以一个特定的ID开始),并且为每个节点计算子节点的数量并测试'isDone'(在此处作为汇总计算JOIN用于执行计数,现在isDone在CTE结果中不是0时被认为是'真')。每个节点的“权重”(实际上它贡献给总数的百分比)是它的父权重除以其兄弟(包括它自己)的数量,并将根设置为100%。
对于'isDone'节点或树叶停止下行。两者都将有下一个递归步骤返回0行)。
最后,将'idDone'节点的总权重相加(其他节点仅用于递归)。
但是,由于产生的错误状态: “GROUP BY,HAVING或聚合函数不允许在递归公用表表达式中使用GROUP BY,HAVING或聚合函数。
此外,任何方向任何进展的任何暗示将不胜感激。
问候, ShaiB
它可能会是一个相当昂贵的操作,无论你选择的路线。但是,下面是一些可能有所帮助的想法:
首先,您是否考虑过使用视图?您可以将计算列放在表上并将其添加到视图上,这可能会让您绕过计算列约束。您还可以使视图可更新(通过替代触发器),因此对于您的应用程序,它的行为类似于表格。
其次,你可以通过存储过程来做到这一点。使用游标一次一行遍历基表,计算percentComplete
列的值,并将结果存储在表变量中。 (你可能会这样写,这样你只需要访问基表中的每一行一次。)然后简单地返回(即SELECT)表变量的结果。
第三,与第二个类似,在插入/更新/删除之后,编写触发器以重新计算每个行的percentComplete
,而不是使用计算列。虽然这会给你读取速度非常快,但写入时可能会非常缓慢。第四,你可以通过CLR函数来做这件事(例如,用C#编写它并将它导入服务器)。用CLR函数打破许多SQL Server(愚蠢)函数的规则就可以逃脱。 (虽然这并不意味着它总是一个好主意。)
第五,也许最复杂的是,你可以编写一个CLR表函数来读取表中的行(不包括percentComplete
)并计算并追加percentComplete
列到结果集。然后,将此用作视图的基础(即,SELECT * FROM dbo.GetTheTree()
),然后使用替代触发器使视图可更新(与第二个选项类似)。
希望能给你一些想法!
您正在使用哪种SQL Server版本? (“* Visual Studio 2010 *”只是一个客户端应用程序,它不会告诉我们任何有关您的数据库的信息) – 2013-02-24 08:24:07
对不起,没有指定:我的MS SQL Server 2008 R2安装在我的开发机器上。我正在通过VS的服务器浏览器编辑所有内容。 – 2013-02-24 08:36:55