使用WITH CUBE时如何获得中值？

简而言之：在MSSQL 2005中使用WITH CUBE时，有没有办法直接计算中值，最好不使用循环？ 一些代码： 
CREATE TABLE #Example (
    ID1 INT,
    ID2 INT,
    [Value] INT
)

INSERT #Example(ID1,ID2,[Value])
    SELECT 1, 1, 45 UNION
    SELECT 1, 2, 78 UNION
    SELECT 1, 3, 23 UNION
    SELECT 1, 4, 119 UNION
    SELECT 1, 5, 85 UNION
    SELECT 2, 1, 143 UNION
    SELECT 2, 2, 124 UNION
    SELECT 2, 3, 161 UNION
    SELECT 2, 4, 171 UNION
    SELECT 2, 5, 142

SELECT ID1, ID2, COUNT(1) AS 'NumValues', MIN([Value]) AS 'Minimum', AVG([Value]) AS 'Average', MAX([Value]) AS 'Maximum'
    FROM #Example
    GROUP BY ID1, ID2
    WITH CUBE
    HAVING ID1 IS NULL OR ID2 IS NULL
    ORDER BY ISNULL(ID1, 0), ISNULL(ID2, 0)

DROP TABLE #Example
如何获得中值的列？ 到目前为止，我所管理的最好的方法是循环ID1和ID2值，并计算每组ID的中位数，但这似乎效率低下且难看。 任何想法都感激不尽！     
2019-11-04 2 条评论
分享

没有找到相关结果

    已邀请:

    葛瞎说漓

    有点姗姗来迟，我认为这会奏效： 
    select * from
(
    SELECT ID1, ID2, COUNT(1) AS 'NumValues', MIN([Value]) AS 'Minimum', AVG([Value]) AS 'Average', MAX([Value]) AS 'Maximum'
    FROM #Example
    GROUP BY ID1, ID2
    WITH CUBE
    HAVING ID1 IS NULL OR ID2 IS NULL
) T
OUTER APPLY 
(
    select avg(val) as median from
    (
        select [value] as val, row_number() over (order by [value]) as rn
        from #Example E 
        where (T.ID1=E.ID1 or T.ID1 is null) and (T.ID2=E.ID2 or T.ID2 is null) 
    ) I
    where abs(rn - (T.NumValues+1)/2.0) <1
) T2
        
    2019-11-04 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.043416023254395