SQL Server Include average of grouped fields inside of original query

vcudknz3 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(127)

The example data:

CREATE TABLE #Temp1 (

    ID varchar(10)
    ,ColumnOne varchar(55)
    ,ColumnTwo varchar(55)
    ,ColumnThree varchar(55)

);

INSERT INTO #Temp1 (ID, ColumnOne, ColumnTwo, ColumnThree)
VALUES('1', 'John', '12345', NULL), ('2', NULL, NULL, NULL), ('3', 'Jerry', '67890', 
'abcde')

ID	A	B	C
1	John	12345	NULL
2	NULL	NULL	NULL
3	Jerry	67890	abcde

Initial Results:

I then find the fill rates for each column and ID using:

SELECT 
    ID
    ,'A' AS ColumnNM
    ,CAST(100 * AVG(CASE WHEN A IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
FROM #Temp1
GROUP BY ID

UNION

SELECT 
    ID
    ,'B' AS ColumnNM
    ,CAST(100 * AVG(CASE WHEN B IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
FROM #Temp1
GROUP BY ID

UNION

SELECT 
    ID
    ,'C' AS ColumnNM
    ,CAST(100 * AVG(CASE WHEN C IS NOT NULL THEN 1.0 ELSE 0.0 END) AS numeric(10,2)) AS Column_fR
FROM #Temp1
GROUP BY ID

ID	ColumnNM	Column_fR
1	A	100.00
1	B	100.00
1	C	0.00
2	A	0.00
2	B	0.00
2	C	0.00
3	A	100.00
3	B	100.00
3	C	100.00

What I want:

I would like the "average fill rate for each column, but calculate that average only from IDs 1 and 2" included in the results, for each row, as such:
| ID | ColumnNM | Column_fR | IDOneTwoAverage |
| ------------ | ------------ | ------------ | ------------ |
| 1 | A | 100.00 | 50.00 |
| 1 | B | 100.00 | 50.00 |
| 1 | C | 0.00 | 0.00 |
| 2 | A | 0.00 | 50.00 |
| 2 | B | 0.00 | 50.00 |
| 2 | C | 0.00 | 0.00 |
| 3 | A | 100.00 | 50.00 |
| 3 | B | 100.00 | 50.00 |
| 3 | C | 100.00 | 0.00 |

How do I include this average in the initial query (the one with the unions)?

sql-server

来源：https://stackoverflow.com/questions/76508726/include-average-of-grouped-fields-inside-of-original-query

1条答案

按热度按时间

yacmzcpb1#

This kind of thing usually need a windowed preaggregation ahead of time:

select  ID, v.*
from (
        select  id, avg(case when a is not null then 1.0 else 0.0 end) AS A
        ,   avg(case when b is not null then 1.0 else 0.0 end) AS B
        ,   avg(case when c is not null then 1.0 else 0.0 end) AS C
        ,   Avg_a_12, avg_b_12, avg_c_12
        from (
                select *
                , avg(case when id IN (1,2) then case when a is not null then 1. else 0. end end) over() AS avg_A_12
                , avg(case when id IN (1,2) then case when b is not null then 1. else 0. end end) over() AS avg_B_12
                , avg(case when id IN (1,2) then case when c is not null then 1. else 0. end end) over() AS avg_C_12
                from (
                    VALUES  (1, N'John', N'12345', NULL)
                    ,   (2, NULL, NULL, NULL)
                    ,   (3, N'Jerry', N'67890', N'abcde')
                ) t (ID,A,B,C)
            ) x
        group by id, Avg_a_12, avg_b_12, avg_c_12
    ) x
CROSS APPLY (
    VALUES  ('A',A * 100, avg_a_12 * 100)
    ,   ('B',B * 100, avg_b_12 * 100)
    ,   ('C',C * 100, avg_c_12 * 100)
    ) v (ColumnNM, Column_fR,IDOneTwoAverage)

The avg(case when id IN (1,2) then case when a is not null then 1. else 0. end end) over() part creates the pre-aggregated values.

The rest pretty much follows your original code, but i skipped the unions and used the VALUES construct to "unpivot" the data into rows.

Output:
| ID | ColumnNM | Column_fR | IDOneTwoAverage |
| ------------ | ------------ | ------------ | ------------ |
| 1 | A | 100 | 50 |
| 1 | B | 100 | 50 |
| 1 | C | 0 | 0 |
| 2 | A | 0 | 50 |
| 2 | B | 0 | 50 |
| 2 | C | 0 | 0 |
| 3 | A | 100 | 50 |
| 3 | B | 100 | 50 |
| 3 | C | 100 | 0 |

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Include average of grouped fields inside of original query

1条答案

相关问题

热门标签

最新问答