Оконные функции SQL Server 2012 на практике

Однажды пришлось решать некоторые аналитические задачи бизнеса. Как оказалось, новые оконные функции SQL Server 2012 оказались очень кстати.

Задача 1. Дан скрипт создания таблицы, содержащей данные о платежах клиента:

CREATE TABLE dbo.pays
(
  -- Идентификатор клиента
  client_id int           not null,
  -- Дата и время платежа
  [date]    datetime      not null,
  -- Сумма платежа
  pay_sum   decimal(19,2) not null
)

Напишите запрос, который выбирал бы все данные из таблицы с добавлением 2 полей:

• сумма платежа нарастающим итогом
• количество записей выше текущей для записей с тем же идентификатором клиента

При этом записи упорядочиваются сначала по дате, потом по идентификатору клиента.

Решение

SELECT 
  [client_id], 
  [date],
  [pay_sum],
  SUM([pay_sum]) OVER
  (
    PARTITION BY [client_id] 
    ORDER BY [date] 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS grow_sum,
  COUNT([client_id]) OVER
  (
    PARTITION BY [client_id] 
    ORDER BY [date]
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  ) AS prec_count
FROM [dbo].[pays]
ORDER BY [date], [client_id]

Задача 2. Дан следующий скрипт создания таблицы хранения тиковых котировок (изменения цен всех ценных бумаг):

CREATE TABLE dbo.tics
(
  -- Дата и время тиковой котировки с точностью до секунд
  [date]   datetime      not null,
  -- Код ценной бумаги (ЦБ)
  contract varchar(10)   not null,
  -- Цена
  price    decimal(19,6) not null
)

Для следующих входных параметров:

DECLARE
  -- Длина периода в секундах
  @period_length int      = 300,
  -- Начало временного промежутка
  @date_begin    datetime = '2007-05-01 10:12',
  -- Конец временного промежутка
  @date_end      datetime = '2007-05-01 11:12'

напишите запрос к данной таблице, который выбирал бы из этой таблицы агрегированные показатели по временным периодам внутри временного промежутка. Длина периода задана в переменной @period_length. Временной промежуток задан началом (@date_begin) и концом (@date_end). Необходимые показатели - первая цена, последняя цена, максимальное и минимальное значение цены в разрезе ЦБ за период. Начало первого периода – 2001-01-01 00:00:00. Если начало временного промежутка не попадает на начало очередного периода, в выборку должен попасть период, между началом (включительно) и концом (исключая) которого попадает начало промежутка. Аналогично для конца периода. Результат запроса должен включать:

• начало периода
• код ЦБ
• первая цена
• последняя цена
• максимальное значение
• минимальное значение

В выборку по периоду должны попасть только те ЦБ, данные по которым есть за период.

Решение (ключевая часть кода)

DECLARE
  @date_start smalldatetime = '20010101',
  @date_stop  smalldatetime = '20010101'

-- Ближайший период перед временным промежутком
SELECT @date_start = DATEADD(ss, FLOOR(DATEDIFF(ss, @date_start, @date_begin) / @period_length) * @period_length, @date_start)

SELECT @date_stop = DATEADD(ss, @period_length, @date_start)

-- Выборка за один временной промежуток
SELECT
  @date_start,
  [contract],
  ROW_NUMBER() OVER
  (
    PARTITION BY [contract]
    ORDER BY [date]
  ),
  FIRST_VALUE([price]) OVER
  ( 
    PARTITION BY [contract]
    ORDER BY [date]
  ),
  LAST_VALUE([price]) OVER
  (
    PARTITION BY [contract]
    ORDER BY [date]
    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ),
  MIN([price]) OVER (PARTITION BY [contract]),
  MAX([price]) OVER (PARTITION BY [contract])
FROM [dbo].[pays]
WHERE [date] >= @date_start and [date] < @date_stop

Задача 3. Дана таблица:

CREATE TABLE dbo.docs
(
  -- Клиент
  client_id  int  not null,
  -- Тип договора  
  type_id    int  not null,
  -- Первый день действия договора
  first_date date not null, 
  -- Последний день действия договора
  last_date  date null
)

Написать запрос, который для данных:

DECLARE
  -- Тип договора
  @type_id    int,
  -- Начало периода
  @first_date date,
  -- Конец периода
  @last_date  date  

Вернет набор данных вида:

• client_id
• first_date
• last_date

Набор данных содержит все непересекающиеся и несмежные (т.е., между двумя промежутками должен быть минимум один «пустой» день) промежутки между @first_date и @last_date включительно, когда у клиента был хоть один активный договор типа @type_id. Учесть, что у клиента может быть несколько одновременно действующих однотипных договоров.

Решение

DECLARE
  @type_id    int  = 1,
  @first_date date = '20130115',
  @last_date  date = '20130415';

WITH cte_dates AS
(
  SELECT
    client_id,
    first_date,
    LEAD (first_date) OVER
    (
      PARTITION BY client_id 
      ORDER BY first_date
    ) AS new_first_date,
    LAG (last_date) OVER
    (
      PARTITION BY client_id 
      ORDER BY last_date
    ) AS old_last_date,
    last_date
  FROM dbo.docs
  WHERE [type_id] = @type_id
    AND first_date <= @last_date
    AND (last_date IS NULL OR last_date >= @first_date)
)
SELECT
  client_id,
  IIF(first_date < @first_date, @first_date, first_date) AS first_date,
  IIF(last_date > @last_date, @last_date, last_date) AS last_date
FROM cte_dates
WHERE
(
  ABS(DATEDIFF(DAY, first_date, old_last_date)) > 1
  AND
  ABS(DATEDIFF(DAY, last_date, new_first_date)) > 1
)
OR (old_last_date IS NULL AND new_first_date IS NULL)