company_banner

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

Автор оригинала: Rakib Raihan
  • Перевод
Поговорим о распространённых приёмах работы с датами, которые находят применение на 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-запросы?

RUVDS.com
VDS/VPS-хостинг. Скидка 10% по коду HABR

Комментарии 7

    +3
    Как говорили в детстве на футболе, «разбег на рубль, удар на копейку». Одна простенькая задача, кмк не очень специфичная для Data Science, которую легко решит большинство программистов, имевших дело с базами данных. Зато очень многообещающий заголовок.
      0
      Задачка действительно простенькая, но для меня статья не совсем уж бесполезная, или даже совсем не бесполезная. Ссылочка на ресурс stratascratch.com для меня оказалась новой и интересной — как оказалось, там можно потренироваться, размяться немного в навыках составления SQL-запросов и, вроде, там же есть упражнения на python, но туда я не заглядывал.
        +2
        когда больше уже не о чем написать, но план по написанию статей есть
          +3
          Прочитал — и обалдел…

          Вот как соотносятся эти две цитаты?
          inspection_date — datetime
          и
          столбец inspection_date, на самом деле, хранит не дату. Это — объект, который, в соответствии с особенностями платформы, хранит либо текстовые данные, либо данные типа varchar.

          В подавляющем большинстве СУБД DATETIME — это тип данных, который хранится в упакованном бинарном виде (из распространённых, пожалуй, только SQLite выделилась в этом вопросе). Текст — это представление даты при отдаче результата запроса «наружу» (и на это преобразование сервер потратит определённые ресурсы — человеки, увы, не умеют читать дату в бинарном виде).

          К слову, а какая разница в данном контексте между «текстовыми данными» и VARCHAR?

          И пошло-поехало — CAST в дату, экстракция года… а ведь на финише ещё и преобразование в текстовое представление (по уже описанной причине). А не проще сразу перегнать в текст, строковой функцией откусить из нужного места 4 символа (или сразу задать формат «только год» при преобразовании в строку, если есть такая функция), и по полученному значению группировать?

          И последнее. Задание просило
          Выведите количество таких проверок с группировкой по годам в нисходящем порядке.
          Так вот, согласно правилам русского языка «в нисходящем порядке» — это не про годы, а про количество проверок. Так что увы-увы…

          И совсем последнее. Прямо под наименованием статьи есть гиперлинк с текстом «Автор оригинала: Rakib Raihan». Увы, ведущий в никуда… «We can't find the page you're looking for.»
            0
            Допустимо, кроме того, поместить YEAR в выражение GROUP BY, так как выражение SELECT выполняется первым. В результате интерпретатору, после выполнения этого выражения, уже будет известно о том, что в запросе имеется столбец с именем YEAR:

            Вообще, интересно — это часть стандарта или нет? Для MS SQL Server — это процитированное утверждение ложно, обращаться к столбцу по псевдониму в GROUP BY в нём нельзя. SQL Bolt и Towads Data Science, без привязки к СУБД, тоже говорят о том, что GROUP BY «выполняется до» SELECT. А PostgreSQL позволяет такое, MySQL, видимо тоже.
              0
              приятно работать с текстовой датой, year=substr (date, 1, 4)
              но боже мой, с датами столько всяких плясок бывает — юникс таймстемп, исо8601, временные зоны и их преобразования… и все это во всех диалектах по разному… так что тема не раскрыта
                0
                EXTRACT (YEAR FROM cast(inspection_date as DATE)) = 2015 — так в принципе не получится использовать индекс по inspection_date.

                Если inspection_date имеет тип DATE, то существенно эффективней было бы сделать индекс по inspection_date и использовать выражение вида inspection_date BETWEEN CAST ('2015-01-01' AS DATE) AND CAST ('2015-12-31' AS DATE)
                Со строкой аналогично но уже в виде inspection_date>='2015' AND inspection_date<'2016'

                Что касается Data Science, тут даты индексируются в подавляющем большинстве случаев. Потому как на входе миллиарды записей.

                Так же, настоятельно не рекомендую подстраивать платформу под инструмент (Python), вместо того, чтобы подстраивать инструменты под платформу и цели. Это уже отчетливо пахнет «золотым молотком». Существенно эффективней хранить данные в оптимальном формате для БД, а не для одного из используемых инструментов. Тогда, по необходимости, и инструменты можно менять аки перчатки.

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое