14 июня 2011

Postgresql, которая меня кормит.

Условия:
Есть таблица, которая хранит журнал работы водителя.

journal_waybills (
  id bigserial, --
  name text, --
  _date date, -- дата рабочего дня
  start_mileage numeric(6,2), -- спидометр при выезде на линию
  end_mileage numeric(6,2), -- спидометр при въезде с линии
  ....
)

Итак, у водителя при выходе на работу и при завершении ее фиксируются показания спидометра. Кроме того водителю предоставляется автомобиль в личное пользование в нерабочее время, что чрезвычайно повышает степень ухоженности автомобиля.

Задача: 
Посчитать расстояния проезжаемые водителем на автомобиле в личное время.

Решается все просто. Запрос выглядит так:


select  *, start_mileage - yesterday(end_mileage) from journal_waybills order by _date, _name.


Функция  yesterday будет не только функцией но и некоторой конструкцией. Чтобы получить значение end_mileage для вчерашнего дня, нам необходим доступ к предыдущей строке таблицы. Для этого придуманы так называемые window functions. Нам понадобиться функция lag, которая возвращает значение указанного столбца на n строк назад, и с указанным значением по умолчанию для первых строк. Перепишем запрос.



select  *, start_mileage - lag(end_mileage, 1, 0.0::numeric(6,2))
from journal_waybills order by _date, _name.

Но так как строки в таблице храняться беспорядочно, то нам надо точно идентифицировать, какая строка должна быть предыдущей. Предыдущей должна быть строка с меньшей датой.



select  *, start_mileage - lag(end_mileage, 1, 0.0::numeric(6,2)) over (order by _date) from journal_waybills order by _date, _name.


Кроме того для каждого нового водителя подсчет должен начинаться заново:


select  *, start_mileage - lag(end_mileage, 1, 0.0::numeric(6,2)) over (partition by _name order by _date) from journal_waybills order by _date, _name.

Ну собственно вот. Надеюсь все понятно.

Комментариев нет:

Отправить комментарий