Оконные функции

Оконные функции

Оконные функции вычисляются аналогично агрегатным, но не объединяют несколько записей в одну, сохраняя их независимость. Они позволяют получить дополнительную информацию об исходной выборке, например, вычислить нарастающий итог, скользящее среднее или ранжировать значения.
Реализация оконных функций в LPE выполнена с использованием принципов функционального программирования, что даёт удобную и относительно компактную форму записи параметров.

Для использования оконных функций требуется обёртка window(...), в аргументах которой указываются все необходимые параметры.

Примеры:

window(sum(c1)) // оконная функция - эквивалент sum(c1) OVER ()
window(sum(c1), partition(dt)) // sum(c1) OVER (partition by dt)
window(sum(c1), order(-dt)) // sum(c1) OVER (order by dt DESC)

Функция window(fn, partition, order)

• Функция window принимает обязательный первый аргумент - функцию fn и выполняет функцию fn в качестве оконной SQL функции.
• fn(column_name)/fn() в зависимости от типа функции может требовать аргумент или нет
• partition(column_name) определяет, как данные будут разделены на группы для выполнения оконных операций
• order(column_name) определяет порядок строк в каждой группе данных. Для корректной работы ранжирующих функций требуется указывать для них order(column_name)

Примеры:

Вызов стандартных агрегационных функций: sum(), count(), min(), max(), avg()

 measures: [
      'window(min(price),partition(date)):w_sum',
      'window(max(price),partition(date)):w_sum',
      'window(sum(price),partition(date),order(price)):w_sum',
      'window(count(productname),partition(date),order(price)):w_count',
      'window(avg(price),partition(date),order(-price)):w_avg',
    ],

Оконная функция row_number() присваивает уникальный последовательный номер каждой строке в рамках определенной группы или окна. Этот номер обычно начинается с 1 и увеличивается на 1 для каждой последующей строки.

 measures: [
      'window(row_number(),partition(country),order(date)):row_number'
      'window(row_number(),order(date)):row_number'
   ],

Оконная функция rank() также присваивает уникальный номер каждой строке в рамках определенной группы или окна, но если есть несколько строк с одинаковым значением по порядку сортировки (например, одинаковая зарплата), то функция RANK() присваивает им один и тот же ранг, пропуская следующий ранг. Например, если двум сотрудникам присвоен ранг 1, следующий сотрудник получит ранг 3, а не 2.

 measures: [
      'window(rank(),partition(country,date),order(date,productname)):rank',
      'window(rank(),order(date,productname)):rank',
   ],

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

 measures: [
      'window(dense_rank(),partition(country,date),order(date,productname)):rank',
      'window(dense_rank(),order(date,productname)):rank',
   ],

Оконная функция ntile(cnt) используется для разделения упорядоченного набора строк на указанное количество равных групп (частей). Функция NTILE принимает один аргумент - количество групп, на которые нужно разделить строки, и присваивает каждой строке номер группы, к которой она относится.

 measures: [
       'window(ntile(4),partition(country,date),order(date,productname)):ntile_4',
       'window(ntile(4),order(date,productname)):ntile_4',
   ],

Пример вычисления нарастающего итога:

 measures: [
       'window(sum(amount),order(date)):cumulative_sum',
   ],

подсказка

Функция window появилась в luxmsbi-pg 9.3.0