Common Table Expression Issue
To calculate clustering coefficient I have created the following query:
--calculate embeddedness and local_gatekepping_role_count (for two-way connections)
declare @observedUser varchar(50)
declare @clusteringCoefficient decimal(18, 7)
declare @possibleConnectionsCount int
declare @neighborConnectionsCount int
declare @rowsAffected int
SET @rowsAffected = 1;
WHILE @rowsAffected > 0
BEGIN
SELECT TOP 1 @observedUser = msisdn FROM users_in_group_91 WHERE clustering_coefficient IS NULL
SET @rowsAffected = @@ROWCOUNT
IF @rowsAffected = 0 break;
;WITH neighbors AS
(
SELECT called_party AS neighbor FROM connections_in_group_91 WHERE calling_party = @observedUser
UNION
SELECT calling_party AS neighbor FROM connections_in_group_91 WHERE called_party = @observedUser
),
possibleConnections AS
(
SELECT n1.neighbor AS user1, n2.neighbor AS user2 FROM neighbors n1 CROSS JOIN neighbors n2 WHERE n1.neighbor < n2.neighbor
)
SELECT @clusteringCoefficient = CAST(COUNT(*) AS decimal)/(SELECT COUNT(*) FROM possibleConnections) FROM possibleConnections pc INNER JOIN connections_in_group_91 c91 ON c91.calling_party = pc.user1 AND c91.called_party = pc.user2
)
UPDATE users_in_group_91 SET clustering_coefficient = @clusteringCoefficient WHERE msisdn = @observedUser
END
which works fine.As there would be useful to check how many times the observed user plays the role of gatekeeper I tried to modify the query above the following way:
declare @observedUser varchar(50)
declare @clusteringCoefficient decimal(18, 7)
declare @gatekeepingRoleCount int
declare @possibleConnectionsCount int
declare @neighborConnectionsCount int
declare @rowsAffected int
SET @rowsAffected = 1;
WHILE @rowsAffected > 0
BEGIN
SELECT TOP 1 @observedUser = msisdn FROM users_in_group_91 WHERE clustering_coefficient IS NULL
SET @rowsAffected = @@ROWCOUNT
IF @rowsAffected = 0 break;
;WITH neighbors AS
(
SELECT called_party AS neighbor FROM connections_in_group_91 WHERE calling_party = @observedUser
UNION
SELECT calling_party AS neighbor FROM connections_in_group_91 WHERE called_party = @observedUser
),
possibleConnections AS
(
SELECT n1.neighbor AS user1, n2.neighbor AS user2 FROM neighbors n1 CROSS JOIN neighbors n2 WHERE n1.neighbor < n2.neighbor
)
SELECT @possibleConnectionsCount = COUNT(*) FROM possibleConnections
SELECT @clusteringCoefficient = CAST(COUNT(*) AS decimal)/(@possibleConnectionsCount) FROM possibleConnections pc INNER JOIN connections_in_group_91 c91 ON c91.calling_party = pc.user1 AND c91.called_party = pc.user2
SET @gatekeepingRoleCount = @possibleConnectionsCount * 开发者_StackOverflow社区(1 - @clusteringCoefficient)
UPDATE users_in_group_91 SET clustering_coefficient = @clusteringCoefficient, local_gatekeeping_role_count = @gatekeepingRoleCount WHERE msisdn = @observedUser
END
but the latter query returns an error: Msg 208, Level 16, State 1, Line 25 Invalid object name 'possibleConnections'.
Does anyone know why it is not possible to query the same expression multiple times this way and how to modify the query above in order to calculate @clusteringCoefficient and @gatekeepingRoleCount in the same loop?
Thank you!
The CTE is only good for the following statement.
In your case if you get rid of the scalar variables and just put the subqueries inline it should work.
精彩评论