SQL: быстрое погружение

Функции даты и времени позволяют управлять данными, хранящимися в различных форматах даты и времени. В базе данных sTunes информация о дате хранится в формате DATETIME: YYYY-MM-DDHH: MM:SS. Хотя формат позволяет вводить временной код, он не используется в нашей базе данных — все временные коды пусты и отображают 00:00:00. Поэтому, чтобы убрать временной код и оставить только информацию о дате, мы использовали функцию DATE() (см. главу 5). Поскольку в разных базах данных информация о дате может храниться по-разному, важно знать, как преобразовать один формат в другой. С датами мы можем сделать гораздо больше, чем просто изменить их формат. Например, чтобы вычислить возраст сотрудников, мы можем посчитать разницу между любой заданной и текущей датой, поскольку в таблице employees имеется поле BirthDate (Дата рождения).

Чтобы рассчитать возраст сотрудников, рассмотрим функцию STRFTIME(), также известную как функция времени в строковом формате. Она позволяет отформатировать информацию о времени и дате в виде текстовой строки. Для корректной работы функции STRFTIME() требуется как минимум информация двух видов. Необходимо указать желаемый формат (его называют спецификацией преобразования) и строку времени для форматирования. Строку времени можно ввести вручную или использовать поле DATETIME. В качестве аргумента строка времени также может использовать функцию NOW. Третий аргумент, модификатор, необязательный и может применяться для дискретного сдвига даты вперед или назад и выполнения некоторых других функций.

Рис. 96

Примечание

Функцию NOW иногда называют недетерминированной функцией. Это означает, что результирующие данные, возвращаемые этой функцией, будут разными при каждом ее вызове, поскольку дата и/или время будут разными при каждом вызове. Функция STRFTIME() и большинство других функций, описанных в этой книге, — детерминированные. То есть они дают один и тот же результат каждый раз, когда используются с одними и теми же аргументами. Функцию NOW необходимо постоянно обновлять, чтобы она оставалась точной и результат соответствовал времени вашего компьютера.

Примечание

Функция STRFTIME() способна выполнять различные преобразования времени и дат. Если вы пока не совсем понимаете значения всех аргументов и выполняемых преобразований, не беспокойтесь. Главное то, что функция STRFTIME() принимает данные в формате времени и даты и использует ключевые слова для возврата определенных пользователем фрагментов даты.

Рис. 97

Рис. 98

Рис. 99

Мы можем использовать любые символы внутри одинарных кавычек при условии, что всю строку заключаем в кавычки.

SELECT

STRFTIME('The Year is: %Y The Day is: %d The Month is %m', '2011-05-22') AS [Text with Conversion Specifications]

Рис. 100

НАПОМИНАНИЕ

Спецификация в функции преобразования всегда начинается с символа %, за которым следует чувствительный к регистру буквенный символ. Использование, например, %M (верхний регистр) вместо %m даст нам минуты вместо месяцев.

Давайте рассмотрим работу функции STRFTIME() на примере вычисления возраста сотрудников. Первое, что необходимо сделать, это указать необходимый формат. Поскольку BirthDate (Дата рождения) имеет тип данных DATETIME, а время в нашей базе данных не указано, для простоты опустим временные коды. Чтобы узнать возраст сотрудников, вычислим разницу во времени между датой рождения каждого сотрудника и текущей датой. Текущую дату можно получить, используя функцию NOW.

SELECT

LastName,

FirstName,

STRFTIME('%Y-%m-%d',BirthDate) AS [Birthday No Timecode],

STRFTIME('%Y-%m-%d','now') — STRFTIME('%Y-%m-%d', BirthDate) AS [Age]

FROM

employees

ORDER BY

Age

Рис. 101

Мы можем использовать функцию STRFTIME() так же, как мы использовали функцию DATE() для удаления временных кодов. Затем, чтобы узнать возраст сотрудников, необходимо получить разницу между двумя функциями strftime.

Практические задания

• Компания sTunes отмечает дни рождения сотрудников первого числа каждого месяца. Создайте для отдела кадров таблицу, отображающую имена сотрудников, дни рождения и день празднования.

• Отдел кадров компании sTunes сообщил нам, что возраст сотрудников — это тема весьма деликатная. Перепишите этот запрос, чтобы в результате получить количество лет работы каждого сотрудника в компании.

• Какой сотрудник проработал в компании дольше всех?

Агрегатные функции

Агрегатные функции воздействуют на значения столбца, чтобы получить единое результирующее значение с помощью различных математических операций. В начале этой главы, чтобы вычислить количество клиентов с фамилией, начинающейся с буквы B, мы использовали функцию COUNT(). Существует множество практических способов использования агрегатных функций. Так, используем функцию SUM() в таблице invoices для вычисления итоговой суммы всех счетов:

SELECT

SUM(Total) AS [Total Sales]

FROM

invoices

Существует множество агрегатных функций [11], но здесь мы рассмотрим пять основных функций в SQL: SUM(), AVG(), MIN(), MAX() и COUNT().

Рис. 102

Пример

Напишем следующий запрос:

SELECT