以特殊方式按可空列分组

Location 分配给相同的 Id 然后它就违反了索引并且不允许它们。

我从我的观点开始

-- This is what I wish I could do but its wrong
SELECT Id, Location, COUNT(*) as Count, COUNT(Location) as LocationCount
FROM #X
GROUP BY Id, Location
ORDER BY Id, Location 

这说明:

DROP TABLE IF EXISTS #X 

SELECT *
INTO #X
FROM (
    SELECT 1 as Id, 1 as Location
    UNION
    Select 1 as Id, NULL as Location
    UNION
    Select 2 as Id, NULL as Location
    UNION
    Select 3 as Id, 2 as Location
) X

-- This is what I wish I could do but its wrong
SELECT Id, Location, COUNT(*) as Count, COUNT(Location) as LocationCount
FROM #X
GROUP BY Id, Location
ORDER BY Id, Location

结果:

因为Id是重复的,所以不能将其作为索引视图。

SELECT Id, AVG(Location) as Location, COUNT(*) as Count, COUNT(Location) as LocationCount
FROM #X
GROUP BY Id
ORDER BY Id, Location

但这行不通,因为 AVG 在索引视图中不允许,但也因为它的唯一分组 因此,如果将两个非空值赋给一个Id,比如6和8,那么它将返回这两个(7)的平均值,而不是不允许它。

;WITH IndexableViewNonNulls AS (
    SELECT Id, Location, COUNT(*) as Count
    FROM #X
    WHERE Location IS NOT NULL
    GROUP BY Id, Location
), IndexableViewNulls AS (
    SELECT Id, Location, COUNT(*) as Count
    FROM #X
    WHERE Location IS NULL
    GROUP BY Id, Location
), CTE AS (
    SELECT *, Count as LocationCount
    FROM IndexableViewNonNulls
    UNION ALL
    SELECT *, 0 as LocationCount
    FROM IndexableViewNulls
)

SELECT 
      Id
      ,AVG(Location) as Location
      ,SUM(Count) as Count
      ,SUM(LocationCount) as LocationCount
FROM CTE
GROUP BY Id

这是完整的小提琴: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f3df7c4b5703e9270ea1efe9bef5c152

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=544b5983bf25cd9c2ff1f55d68bd34d8

编辑2:我的解决方案是使用2个索引视图和1个普通视图将它们组合在一起: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b8cf5ff638305a0d228c856b6391246d

编辑3:表#X不是一个普通表,简单地在表上添加索引不是有效的解决方案。在实际场景中,表#X本身就是一个视图,因此解决方案必须使用索引视图