Аренды по дням недели

Задача

Управляющий планирует смены и хочет знать нагрузку по дням недели: «В какие дни у нас пик аренд? Посчитай число выдач по дням недели — от понедельника к воскресенью».

Верни день недели и число аренд в этот день.

Схема данных

Учебная база проката dvdrental, одна таблица:

• rental(rental_id, rental_date, ...) — факты выдачи дисков; rental_date — момент выдачи (timestamp).

Решение

SELECT
  to_char(rental_date, 'Day') AS weekday,
  count(*) AS rentals
FROM rental
GROUP BY weekday, extract(isodow FROM rental_date)
ORDER BY extract(isodow FROM rental_date);

Разбор

Задача про то, как группировать по «части» даты, а не по самой дате. В rental_date лежит точный момент вплоть до секунд — если сгруппировать по нему как есть, каждая аренда окажется в своей группе. Нам же нужен день недели, поэтому дату надо привести к нужному разрезу.

to_char(rental_date, 'Day') возвращает название дня словом — Monday, Tuesday и так далее, — красиво для отчёта. Но по алфавиту эти слова выстроятся неправильно (Friday раньше Monday), поэтому сортировать по названию нельзя. Здесь выручает extract(isodow FROM rental_date) — он даёт номер дня по ISO: понедельник = 1, воскресенье = 7. По этому числу и сортируем, получая естественный порядок недели.

Тонкий момент, на котором спотыкаются: раз в ORDER BY участвует extract(isodow ...), а в SELECT его нет, это выражение обязано попасть в GROUP BY. Иначе PostgreSQL не сможет сортировать группы по полю, которого в группировке нет. Поэтому в GROUP BY стоят оба: и текстовое имя дня, и его числовой номер — они однозначно связаны, так что группы не дробятся.

Почему isodow, а не dow? У обычного dow воскресенье = 0, и неделя начинается с воскресенья — американская традиция. isodow начинает с понедельника, как привычно в России. Мелочь, но на реальном отчёте она сдвигает всю картину.

COUNT(*) считает выдачи в каждый день недели. На выходе — до семи строк, по одной на день, выстроенных от понедельника к воскресенью.

Ожидаемый результат

weekday | rentals
--- | ---
Monday    | 2247
Tuesday   | 2463
Wednesday | 2231
Thursday  | 2200
Friday    | 2272
Saturday  | 2311
Sunday    | 2320