SQL Server агрегация по группам

Question

Есть таблица:

code model  color   type    price
1   1276    n   Laser   400.0000
2   1433    y   Jet     270.0000
3   1434    y   Jet     290.0000
4   1401    n   Matrix  150.0000
5   1408    n   Matrix  270.0000
6   1288    n   Laser   400.0000

Необходим разделить таблицу на группы по полю Color, каждое значение Color='n' создает новую группу, сортировка по полю Code и для каждой группы посчитать среднее по полю Price и количество уникальных значений Type

Т.е, итоговая таблица должна выглядеть так:

code model  color   type    price Group Distinct_cnt AVG_price
1   1276    n   Laser   400.0000  1      2           320
2   1433    y   Jet     270.0000  1      2           320
3   1434    y   Jet     290.0000  1      2           320
4   1401    n   Matrix  150.0000  2      1           150
5   1408    n   Matrix  270.0000  3      1           270
6   1288    n   Laser   400.0000  4      1           400

Предполагаю, что это можно сделать с помощью оконных функций, но не понимаю, как корректно указать условие для разбивки на группы,чтобы запись с Code=3 относилась к 1 группе. К тому же, при попытке использовать Distinct в оконной функции получаю ошибку Use of DISTINCT is not allowed with the OVER clause.

select *, 
row_number () over (partition by color order by code) as [group]
--, count (distinct type) over (partition by color order by code) as cnt_distinct
, avg (price) over (partition by color order by code) as avg_price
from printer
order by code

SQL Server 2019, фиддл тут

Answer 1

select *,
       avg(price) over(partition by grp) avg_price,
       sum(is_new) over(partition by grp) distinct_type_cnt
  from (
    select *,
         case when lag(type,1,'') over(partition by grp order by type) != type then 1 else 0 end is_new
      from (
        select *, sum(case color when 'n' then 1 end) over (order by code) as grp
          from printer
      ) x
  ) y
order by code

тестовый фиддл

Внутренний подзапрос (x) получает номер группы суммируя 1 для каждого color=n. Подзапрос y выставляет в поле is_new 1 если обнаружено новое значение поля type, по сравнению с предыдущей (сортировка по type в группе) строкой. Во внешнем запросе остается получить среднее для группы и сумму "новых" значений type.

Answer 2

или чуток по другому

SELECT * ,
   dense_rank() over (partition by  [group] order by  type) + dense_rank() over (partition by  [group] order by type desc) - 1 as distinct_type_cnt,
   avg(price) over(partition by  [group]) avg_price
from (select *, 
             sum(case color when 'n' then 1 else 0 end) over (order by code) as [group]    
      from printer ) AS printer
order by code

идея всё та же тестовый фиддл

за разницей реализации distinct_type_cnt. Получаем - 1 подзапрос.

БЛОГ НА HUSL

SQL Server агрегация по группам

Ответы (2 шт):