SQL: как выбрать оконные функции вместе с агрегатными функциями

Question

Мне интересно, как смешать выбор столбцов, агрегированные значения, а также "выбор из разделов". Пожалуйста, дайте мне знать, если это дублирующий вопрос!

Пример того, что я имею в виду:

Приведенный пример журнала транзакций:

ID     Food    Date     Cost
1      Apple   5/1/14   10
1      Apple   5/4/14   8
1      Apple   5/6/14   15
1      Pear    5/2/14   25
2      Apple   5/3/14   15

Цель состоит в том, чтобы выбрать для каждой уникальной комбинации ID/Food, стоимость первой покупки (по дате), последней покупки, а также среднюю стоимость.

Вывод будет выглядеть так:

ID   Food    First_Cost    Last_Cost     Avg_Cost
1    Apple   10            15            11
1    Pear    25            25            25
2    Apple   15            15            15

Я рассмотрел OVER - PARTITION ( выбрать первую строку в каждой группе GROUP BY?), А также группировку рядом с агрегатными функциями ( https://dba.stackexchange.com/questions/41494/select-first-row-grouping-add-aggregate-function), но ни один, казалось, не ответил на этот конкретный вопрос.

Это пример кода того, что я пытаюсь выполнить:

select id
      ,food
      ,firstrow(cost) OVER(PARTITION BY id, food ORDER BY date asc as first_cost
      ,avg(cost) from table
group by id, food;

С использованием ROWNUMBER() OVER(PARTITION...)и затем фильтрация с предложением where тоже не работает, так как агрегатные функции были бы отключены.

Одним из решений всегда может быть создание таблицы с первым, одним с последним, другим со средним и объединением трех, но я ищу что-то более чистое.

РЕДАКТИРОВАТЬ: Из комментария jpw, Астер имеет оконную функцию first_value(). Однако проблема заключается в том, что столбцы, которые я упорядочиваю или разделяю, должны быть включены в предложение groupby. Его предложение и одно решение могли бы состоять в том, чтобы преобразовать все агрегатные функции в оконные функции и полностью исключить группу.

Хотелось бы увидеть более чистое решение или подтверждение того, что это невозможно!

Answer 1

Запрос:

select id
      ,food
      ,first_value(cost) OVER(PARTITION BY id, food ORDER BY exp_date asc) as first_cost
      ,first_value(cost) OVER(PARTITION BY id, food ORDER BY exp_date desc) as last_cost
      ,avg(cost) OVER(PARTITION BY id, food ORDER BY exp_date asc) as avg_cost
from sm_fruits

приближает вас

    id  Food    first_cost  last_cost   avg_cost
1   1   Apple   10          15          11.00
2   1   Apple   10          15          11.00
3   1   Apple   10          15          11.00
4   1   Pear    25          25          25.00
5   2   Apple   15          15          15.00

Answer 2

Вы можете перекрестно применить таблицу к себе (дважды), чтобы получить максимальную / минимальную дату и стоимость предмета на эту дату.

Читайте на кресте примените его ужин полезным

 select id
      ,food
      ,LastCost
      ,FirstCost
      ,avg(cost) from TABLE t1
       Cross APPLY(
                    SELECT MAX(date), cost AS LastCost FROM TABLE t2 WHERE t2.food = t1.food
                  ) maxdate
       Cross APPLY(
                    SELECT MIN(date), cost AS FirstCost FROM TABLE t3 WHERE t3.food = t1.food
                  ) mindate
group by id, food;