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

ROLLUP позволяет агрегировать несколько уровней группировки, как если бы я объединил несколько простых операторов 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 с логикой свертки?