Как свернуть подсчет количества групп

Question

ROLLUP позволяет агрегировать по нескольким уровням группировки, как будто я UNIONed несколько простых операторов SELECT.

Но я хочу иметь возможность агрегировать результаты более низкого уровня группировки, как если бы я использовал вложенные операторы SELECT или цепочку CTE, зависящих друг от друга.

Например, я хочу иметь возможность подсчитывать количество групп с более низкого уровня группировки или вычислять среднее значение сумм более низкого уровня или минимум максимумов более низкого уровня и т. Д.

Более конкретный пример: если у меня есть запись для каждого автокатастрофы в США, и я хочу получить не только количество несчастных случаев на каждом уровне в ROLLUP(штат, округ, город, почтовый индекс), но и количество людей (очевидно, каждый человек может быть участвует в нескольких несчастных случаях и, следовательно, нескольких записей).

Можно ли добиться этого с ROLLUP? Если возможно, то как?

Пример SQL с результатами:

if object_id('accident') is not null drop table accident
create table accident(
     id int identity(1,1)
    ,state varchar(50)
    ,city varchar(50)
    ,zip varchar(50)
    ,person varchar(50)
)

insert accident(state,city,zip,person)values
 ('NY','Manhattan',10001,'John')
,('NY','Manhattan',10001,'John')
,('NY','Manhattan',10001,'Barbara')

select
    state,city,zip,person
    ,accidents=count(1)
    -- the following line causes error: Windowed functions cannot be used in the context of another windowed function or aggregate.
    --,people=sum(case when row_number()over(partition by person order by (select 0))=1 then 1 else 0 end)
from accident
group by rollup(state,city,zip,person)

;with person as (select state,city,zip,person from accident group by state,city,zip,person)
    select
        state,city,zip
        ,people=count(1)
    from person
    group by rollup(state,city,zip)

Результаты:

state   city    zip person  accidents
NY  Manhattan   10001   Barbara 1
NY  Manhattan   10001   John    2
NY  Manhattan   10001   NULL    3
NY  Manhattan   NULL    NULL    3
NY  NULL    NULL    NULL    3
NULL    NULL    NULL    NULL    3


state   city    zip people
NY  Manhattan   10001   2
NY  Manhattan   NULL    2
NY  NULL    NULL    2
NULL    NULL    NULL    2

См. Первый результат возвращает 3 несчастных случая для каждого уровня, а второй возвращает 2. Если вы хотите получить 3 и 2 в одном запросе ROLLUP. Моя проблема в том, что оконные функции не могут быть вложенными.

То, что я только что спросил, может быть достигнуто с помощью этого запроса:

;with person as (select state,city,zip,person,accidents=count(1) from accident group by state,city,zip,person)
        select
            state,city,zip
            ,accidents=sum(accidents)
            ,people=count(1)
        from person
        group by rollup(state,city,zip)

state   city    zip accidents   people
NY  Manhattan   10001   3   2
NY  Manhattan   NULL    3   2
NY  NULL    NULL    3   2
NULL    NULL    NULL    3   2

но такой способ требует написания CTE для каждого уровня в явном виде.

Я хочу иметь возможность написать один запрос, который имеет доступ к результатам более низкого уровня группировки независимо от количества уровней группировки.

Пробовал это:

;with
    lvl as (
            select *
                    ,lvl = -1
                    ,accidents=1
                    ,people=1
                from accident
            union all
            select accident.*
                    ,lvl = grouping_id(accident.state,accident.city,accident.zip,accident.person)
                    ,accidents=sum(accidents)
                    ,people=count(1)
                from accident
                join lvl prev on prev.lvl = (grouping_id(accident.state,accident.city,accident.zip,accident.person)+1)/2-1
                group by rollup(accident.state,accident.city,accident.zip,accident.person)
        )
        select * from lvl

Но есть ошибки:

Msg 1015, Level 15, State 1, ...
An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.
Msg 467, Level 16, State 1, ...
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'lvl'.

Связанный вопрос: рекурсивная функция sql с логикой свертки?