SQL: группировка записей для пути

Question

Мой SQL немного ржавый, поэтому вопрос, но это также означает, что я немного не уверен, как точно сформулировать вопрос. Я надеюсь, что пример ниже объясняет это лучше.

У меня есть список записей, по идентификатору пользователя:

ID | Channel  | FiscalPeriod | Amount
======================================
A  | Online   | 201710       | 20.00
A  | Online   | 201709       | 20.00
A  | Voucher  | 201708       | 20.00
A  | Voucher  | 201707       | 20.00
A  | Voucher  | 201706       | 20.00
A  | Online   | 201705       | 20.00
A  | Online   | 201704       | 20.00

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

ID | Channel  | MAX(FiscalPeriod) | Amount
==========================================
A  | Online   | 201710            | 40.00
A  | Voucher  | 201708            | 60.00
A  | Online   | 201705            | 40.00

Для отслеживания выходных данных приведенный выше порядок действительно важен, поэтому я вижу, что онлайн << ваучер << онлайн был путем пользователя

Answer 1

Я бы подошел к этому, используя подход с разницей номеров строк с агрегацией:

select id, channel
       max(fiscalperiod) as max_fiscalperiod
       sum(amount) as amount
from (select t.*,
             row_number() over (partition by id order by fiscalperiod) as seqnum_i,
             row_number() over (partition by id, channel order by fiscalperiod) seqnum_ic
      from t
     ) t
group by id, channel, (seqnum_i - seqnum_ic);

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

Answer 2

Вы можете использовать подход с разницей номеров строк, чтобы классифицировать последовательные одни и те же каналы в группу (выполните внутренний запрос, чтобы проверить это), а затем использовать его для получения суммы и максимума.

select distinct id,channel
,max(fiscalperiod) over(partition by id,channel,grp) as max_fiscalperiod
,sum(amount) over(partition by id,channel,grp) as amount
from (select t.*,
      row_number() over(partition by id order by fiscalperiod)
      -row_number() over(partition by id,channel order by fiscalperiod) as grp
      from tbl t
     ) t