Собеседования в сфере Data Science и распространённые приёмы работы с датами в SQL

Поговорим о распространённых приёмах работы с датами, которые находят применение на Data Science-собеседованиях и в обычной работе. При анализе данных весьма часто возникает необходимость извлечения из полей, хранящих даты, их частей, вроде года, дня или месяца. Нередко тому, кто проходит собеседование, предлагают, на основе поля, содержащего дату, вычислить или подсчитать какие-то показатели, сгруппированные по годам или по месяцам. Но подобное поле содержит информацию, сгруппированную по дням, поэтому для решения вышеописанной задачи нужно просто агрегировать данные на уровне месяцев или лет.



Аналитикам, занимающимся самыми разными делами, часто приходится решать подобные задачи. Но при их решении можно столкнуться с некоторыми сложностями. Например:

1. Существует множество различных функций, которые либо делают одно и то же, либо работают схожим образом, но отличаются в некоторых деталях. Сложно выбрать именно ту функцию, которая нужна при решении конкретной задачи.
2. В разных диалектах SQL имеются различные функции. Поэтому функция, которая подошла бы при работе с Postgres, может оказаться совсем неподходящей при работе с MySQL.
3. Столбец в базе данных может иметь неподходящий формат или тип данных. Поэтому придётся потратить некоторое время на преобразование данных и на приведение их в подходящий вид. Это тоже может усложнить задачу.

Давайте начнём с самого простого. А именно — рассмотрим один SQL-пример и разберём несколько функций, которые можно использовать для разбора дат на составные части. Подобными делами часто приходится заниматься тому, кто работает в сфере Data Science. А вот — видеодемонстрация приёмов работы с датами в SQL.

Работа с датами на Data Science-собеседованиях

Рассмотрим этот вопрос:

Вам предоставлен набор данных, собранный по результатам санитарных проверок. Нужно подсчитать ежегодное количество проверок, в ходе которых были выявлены нарушения в кафе 'Roxanne Cafe'. Если в ходе проверки было выявлено нарушение, то в столбце 'violation_id' будет присутствовать некое значение. Выведите количество таких проверок с группировкой по годам в нисходящем порядке.

Данные содержатся в таблице sf_restaurant_health_violations, в которой имеются следующие поля:

Имя	Тип
business_id	int
business_name	varchar
business_address	varchar
business_city	varchar
business_state	varchar
business_postal_code	float
business_latitude	float
business_longitude	float
business_locationvar	char
business_phone_number	float
inspection_id	varchar
inspection_date	datetime
inspection_score	float
inspection_type	varchar
violation_id	varchar
violation_description	varchar
risk_category	varchar

Задача это довольно простая, поэтому я не буду детально разбирать её решение. Вместо этого я уделю особое внимание тому, что имеет отношение к работе с датами.

После того, как мы поняли, что от нас требуется, рассмотрим подход к решению подобных задач.

Подход к решению задач по работе с базами данных

1. Посмотрим на данные.
2. Выберем столбцы, данные которых нужны для ответа на вопрос.
   
   
   1. Теперь, ориентируясь в данных, мы можем выбрать те столбцы, которые, как нам известно, помогут нам ответить на вопрос.
   2. В нашем случае это будут столбцы inspection_date, violation_id, business_name.
3. Примем решение о том, как должен выглядеть ответ на вопрос.
   
   
   1. Ещё один действительно важный этап решения подобных задач заключается в представлении себе того, как должны выглядеть выходные данные, получаемые при взаимодействии с базой данных, и того, как должно выглядеть решение задачи. В частности, речь идёт о том, какие столбцы понадобится включить в выходные данные.
   2. В нашем случае это — год из столбца inspection_date и число проверок, которое будет представлено в виде count().
   3. Известно, что название кафе и идентификатор нарушения будут использованы для фильтрации данных, а это значит, что они пригодятся при составлении выражения WHERE.

Я, решая подобные задачи, предпочитаю делать всё поэтапно, реализуя на каждом шаге что-то одно и тестируя получившийся код. И, собственно говоря, вам я советую тоже так поступать, когда вы, решая какую-то рабочую задачу, взаимодействуете с настоящей базой данных. Этот подход можно использовать не только на работе, но и на собеседовании. Так вы сможете в подробностях раскрыть тому, кто проводит собеседование, ход ваших размышлений.

▍Фильтрация данных

Для начала применим фильтр. Обычно я начинаю работу именно с этого шага.

SELECT *
FROM sf_restaurant_health_violations
WHERE business_name = 'Roxanne Cafe'
  AND violation_id IS NOT NULL

▍Получение необходимых выходных данных

Теперь попытаемся получить необходимые нам выходные данные. Может, для извлечения сведений о годе, в котором проводилась проверка, стоит воспользоваться конструкцией вида EXTRACT(year FROM request_date::DATE), которая описана здесь и возвращает значение двойной точности?

SELECT EXTRACT (YEAR
                FROM inspection_date) AS YEAR,
               count(*) AS n_inspections
FROM sf_restaurant_health_violations
WHERE business_name = 'Roxanne Cafe'
  AND violation_id IS NOT NULL
GROUP BY YEAR
ORDER BY YEAR ASC

Но результаты работы этого запроса нас не устроят, так как столбец inspection_date, на самом деле, хранит не дату. Это — объект, который, в соответствии с особенностями платформы, хранит либо текстовые данные, либо данные типа varchar. Для работы этой платформы используется Python, поэтому кое-что из того, что можно тут увидеть, имеет отношение к Python. Со временем мы попытаемся с этим справиться.

Приведём столбец к соответствующему типу, используя либо конструкцию с двумя двоеточиями, либо функцию приведения типов. Два двоеточия — это, в сущности, и есть функция приведения типов, которой можно пользоваться в Postgres. А функции приведения типов могут использоваться и в других диалектах SQL вроде MySQL.

Допустимо, кроме того, поместить YEAR в выражение GROUP BY, так как выражение SELECT выполняется первым. В результате интерпретатору, после выполнения этого выражения, уже будет известно о том, что в запросе имеется столбец с именем YEAR:

SELECT EXTRACT (YEAR
                FROM cast(inspection_date as DATE)) AS YEAR,
               count(*) AS n_inspections
FROM sf_restaurant_health_violations
WHERE business_name = 'Roxanne Cafe'
  AND violation_id IS NOT NULL
GROUP BY YEAR
ORDER BY YEAR ASC

▍Важное замечание

Часто нужно изолировать части даты в выражении SELECT — так же, как мы сделали это в нашем примере. Но нередко встречается и необходимость изоляции частей дат в выражении WHERE, что нужно в том случае, когда требуется фильтровать данные. Если нам, например, нужно отобрать только данные, относящиеся к 2015 году, это можно сделать, воспользовавшись соответствующим фильтром:

SELECT EXTRACT (YEAR
                FROM cast(inspection_date as DATE)) AS YEAR,
               count(*) AS n_inspections
FROM sf_restaurant_health_violations
WHERE business_name = 'Roxanne Cafe'
  AND violation_id IS NOT NULL
  AND EXTRACT (YEAR FROM cast(inspection_date as DATE)) = 2015
GROUP BY YEAR
ORDER BY YEAR ASC

▍Ещё одно важное замечание

Разные диалекты SQL, например — MySQL, Postgres, Oracle и MS SQL Server, обладают различными функциями для работы с датами. Например, функция EXTRACT() имеется в большинстве диалектов.

Если вы пользуетесь Postgres, это значит, что вам доступна функция date_part(), которая похожа на EXTRACT.

SELECT date_part ('YEAR', inspection_date :: DATE) AS YEAR,
               count(*) AS n_inspections
FROM sf_restaurant_health_violations
WHERE business_name = 'Roxanne Cafe'
  AND violation_id IS NOT NULL
GROUP BY YEAR
ORDER BY YEAR ASC

В MySQL можно пользоваться функцией YEAR().

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

Итоги

Выбор части даты из соответствующего поля — это обычная задача, с которой сталкиваются аналитики и дата-сайентисты. Помимо представления неких данных с разбивкой по годам, может возникнуть необходимость в разбивке их по месяцам, или — по годам и по месяцам.

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

Как вы работаете с датами, создавая SQL-запросы?