Арифметические операции с датами и временем

 

Выражения и скалярные функции часто используются для работы с датами и временем. Для пользователя SQL представляет даты в виде символьных строк, содержащих числовые или текстовые обозначения года, месяца и дня (иногда с разделителями). При вводе SQL-инструкции литеральное значение даты задается в виде строки. Ее формат зависит от языка (например, us_english) и формата даты, установленных с помощью инструкции SET DATEFORMAT. Например, следующая строка представляет 1 мая 2000 года в формате ymd:

 

'2000-05-01'

 

Та же дата в формате mdy вводится так:

 

'05-01-2000'

 

Вместо дефиса (-) в качестве разделителя может использоваться косая черта (/) или точка (.). Как правило, лучше пользоваться четырехзначным обозначением года. Если же вы пользуетесь двузначным обозначением, учтите, что все значения, меньшие, чем установлено параметром two digit year cutoff относятся к тому же столетию, что и граничный год (обычно 21-му), а значения, равные или большие граничного года, относятся к предыдущему столетию (обычно 20-му). Граничным годом (cutoff year) SQL Server no умолчанию считает 2049 год.

SQL поддерживает сложение и вычитание дат с помощью операторов + и -, а также функций DATEADD и DATEDIFF. К следующем примере е помощью функции DATEDIFF вычисляется количество дней между ShipDate и SaleDate для определе­ния задержки между оформлением заказа и отправкой товара:

 

SELECT CustID, OrderlD, SaleDate, ShipDate,

DATEDIFF(Day, SaleDate, ShipDate) AS DaysToShip

FROM Sale

WHERE ShipDate IS NOT NULL

 

Вот как еще можно было бы составить выражение, дающее тот же результат:

 

SELECT CustID, OrderlD, SaleDate, ShipDate,

CONVERT(Int, ShipDate - SaleDate) AS DaysToShip

FROM Sale

WHERE ShipDate IS NOT NULL

 

В этой инструкции для вычисления разности дат используется оператор минус (-), результатом которого является значение типа DateTime. Функция CONVERT преобразует это значение в количество дней. Без нее результатом выражения ShipDate - SaleDate для первой строки таблицы Sale (т.е. 15-05-2000 - 01-05-2000) было бы: 15-01-1900 00:00:00.000

Началу 1 января 1900 года SQL Server ставит в соответствие значение 0 и для каждого полного дня добавляет по 1.

Для добавления к дате определенного периода времени (или для его вычитания) используется функция DATEADD. У нее три аргумента: тип периода, вычитаемое или прибавляемое количество единиц и дата. В следующем примере к дате добавляется 30 дней:

 

ADDDATE (Day, 30, SaleDate)

 

Для вычитания периода из даты нужно просто задать во втором аргументе отрицательное число.

В приведенном выше примере инструкции SELECT показано, как с помощью ключевого слова AS задать имя столбца результирующей таблицы, который будет содержать результаты вычисления выражения:

 

DATEDIFF(Day, SaleDate, ShipDate) AS DaysToShip

 

С помощью того же ключевого слова AS можно задать другое имя (т.е. псевдоним) и для существующего столбца.

Примечание. Во избежание путаницы не стоит злоупотреблять возможностью задавать псевдонимы для существующих столбцов. Гораздо лучше, если во всех запросах и представлениях каждый столбец будет фигурировать под одним и тем же именем. Кроме того, учтите, что имя, заданное для столбца или выражения в списке SELECT, нельзя использовать в предложениях WHERE, GROUP BY и HAVING, поскольку в них могут фигурировать только столбцы таблиц, заданных в предложении FROM.

Утилита Query Analyzer при выводе результатов запроса использует псевдонимы столбцов в качестве их заголовков. Если вы хотите, чтобы имя столбца содержало пробелы, заключите его в двойные кавычки:

 

DATEDIFF(Day, SaleDate, ShipDate) AS "Days To Ship"

 

Обратите внимание, что заданное в нашем примере условие отбора исключает из результирующей таблицы строки с пустым столбцом ShipDate. Мы должны учитывать, что в таблице Sale для этого столбца допускаются значения NULL, поскольку для арифметических выражений это очень важно (если хотя бы один из операндов арифметического выражения имеет значение NULL, все выражение также получает значение NULL). И это совершенно оправдано. Возьмем, например, наше выражение, вычисляющее разность двух дат: очевидно, что если одна из дат неизвестна, то неизвестна и их разность, т.е. она равна NULL. Условие отбора IS NOT NULL позволяет пропустить все строки, в которых дата отгрузки (ShipDate) неизвестна, поскольку товар еще не отгружен.

Примечание. Описанное правило сравнения столбца ShipDate со значением NULL основано на стандарте ANSI, которому соответствует уже упоминавшаяся установка ANSI_NULLS. Никогда не используйте операторы сравнения с ключевым словом NULL (например, ColA = NULL или COIA <> NULL), хотя Transact-SQL это и допускает. Результат сравнения любого значения с NULL согласно стандарту ANSI всегда неизвестен.

Литеральные значения времени и арифметические операции со значениями времени в Transact-SQL такие же, как и для дат. Например, символьная строка ' 13:30:10' в тексте SQL-инструкции означает 13 часов 30 минут 10 секунд.

 

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

 

Скалярные функции обрабатывают значения из одной строки таблицы или объединения таблиц. В SQL определен и другой тип функций, предназначенных для вычислений на основе значений столбца в целом наборе строк. Такие функции называются агрегатными (статистическими) функциями (aggregate function) или функция­ми столбца (column function). Список всех агрегатных функций SQL приведен в таблице (Рис.4). С функциями AVG, CHECKSUM_AGG, COUNT, MAX, MIN и SUM может использоваться ключевое слово DISTINCT, означающее, что перед вычислением значения функции из набора обрабатываемых ею данных должны быть исключены все повторяющиеся значения.

 

Таблица 4. Агрегатные функции SQL Server

Функция	Описание
AVG (выражение)	Среднее значение набора непустых значений выражения
CHECKSUM_AGG (выражение)	Контрольная сумма непустых значений выражения
COUNT(*) COUNT(выражение) COUNT(DISTINCT выра­жение)	COUNT(*) возвращает количество строк в заданном наборе строк. В этой форме инструкции COUNT ключевое слово DISTINCT использоваться не может. COUNT (выражение) возвращает количество непустых значений выражения. Если задано ключевое слово DISTINCT, функция COUNT возвращает количество уникальных непустых значений выражения. Все формы функции COUNT возвращают значение типа int
COUNT_BIG(*) COUNT_BIG(выражение) COUNT_BIG(DISTINCT выра­жение)	Функция COUNT_BIG подобна функции COUNT с той разницей, что COUNT_BIG возвращает значение типа bigint
GROUPING (столбец)	Возвращает 1, если агрегируемая строка добавлена оператором CUBE или ROLLUP, в противном случае возвращает 0
МАХ (выражение)	Максимальное значение из всех непустых значений выражения
MIN(выражение)	Минимальное значение из всех непустых значения выражения
SUM(выражение)	Сумма всех непустых значений выражения
STDEV (выражение)	Среднеквадратичное отклонение непустых значений выражения
STDEVР(выражение)	Смещенное среднеквадратичное отклонение непустых значений выражения
VAR {выражение)	Дисперсия непустых значений выражения
VARP (выражение)	Смещенная дисперсия непустых значений выражения

 

Следующая инструкция SELECT возвращает одну строку с общим количеством клиентов и средней скидкой для них:

 

SELECT ‘Average discount for’, COUNT(*), ‘ customers is’, AVG(Discount) FROM Customer

 

Агрегатная функция COUNT(*) возвращает количество строк в исходной таблице, а агрегатная функция AVG(Discount) возвращает среднее значение набора непустых значений заданного столбца.

Со столбцами, допускающими значение NULL, агрегатные функции должны использоваться очень осторожно. Предположим, например, что столбец Discount допускает значения NULL и такое значение имеется, по меньшей мере, в одной строке таблицы Customer. В этом случае следующая инструкция вернет неожиданный результат:

 

SELECT COUNT(*), AVG(Discount), SUM(Discount)

FROM Customer

 

Сумма окажется не равной средней скидке, помноженной на количество строк. Дело в том, что функция COUNT(*) сосчитает все строки исходной таблицы, а функции AVG и SUM проигнорируют те строки, в которых столбец Discount имеет значение NULL.

Чтобы избежать такого расхождения данных, можно воспользоваться альтернативной формой функции COUNT, которая тоже пропустит строки с пустым столбцом Discount:

 

SELECT COUNT(Discount), AVG(Discount), SUM(Discount)

FROM Customer

 

Возможно также более универсальное и наглядное решение – вовсе исключить из обработки все строки с пустым столбцом Discount при помощи предложения WHERE:

 

SELECT COUNT(*), AVG(Discount), SUM(Discount)

FROM Customer

WHERE Discount IS NOT NULL

 

Когда в списке столбцов инструкции SELECT задается агрегатная функция, и при этом в инструкции отсутствует предложение GROUP BY (о котором рассказывается в следующем разделе), агрегатная функция' обрабатывает весь набор строк, определяемый предложением WHERE, и результирующая таблица запроса всегда содержит только одну строку. Если же задать предложение GROUP BY, результирующая таблица будет содержать по одной строке на каждую группу исходных строк, или же, если задано еще и предложение HAVING, - по одной строке на каждую группу, удовлетворяющую условию HAVING.

Если агрегатная функция применяется к пустому набору строк (т.е. не содержащему ни одной строки), ее результатом будет NULL. Исключение составляет только функция COUNT(*), которая для пустого набора строк возвращает ноль.

Для удаления повторяющихся значений из набора значений, обрабатываемых агрегатной функцией, нужно сразу после ее открывающейся скобки поместить ключевое слово DISTINCT. Чаше всего этот параметр используется с функцией COUNT для подсчета различных значений заданного столбца:

 

SELECT COUNT(DISTINCT ShipCity) FROM Customer

 

Этот пример возвращает одну строку с единственным столбцом, содержащим количество городов, в которых у компании имеется хоть один клиент.

Предложение GROUP BY

 

Предложение GROUP BY используется для применения агрегатных функций к подгруппам строк, отобранных инструкцией SELECT. Например, следующая инструкция

 

SELECT ShipCity, COUNT(*) AS "Customer Count",

AVG(Discount) AS "Average Discount"

FROM Customer

GROUP BY ShipCity

 

возвращает по одной строке на каждую группу клиентов, проживающих в одном городе.

В этом примере ShipCity играет роль столбца группировки, по значениям которого строки таблицы Customer разделяются на группы — отдельная группа для каждого значения столбца ShipCity. Агрегатные функции COUNT и AVG по очереди приме­няются к каждой группе, так что в результирующей таблице оказывается по одной строке на каждую группу. Если столбец группировки допускает пустые значения, для значения NULL тоже создается отдельная группа.

Примечание. В данном примере намеренно не исключены строки с пустыми

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

В предложении GROUP BY указывается список столбцов и скалярных выражений. Например, в следующей инструкции SELECT создается отдельная группа для каждой комбинации вычисляемого столбца DaysToShip и столбца SaleDate:

 

SELECT SaleDate,

DATEDIFF(Day, SaleDate, ShipDate) AS DaysToShip,

AVG(TotalAmt) AS DaysToShip

FROM Sale

WHERE ShipDate IS NOT NULL

GROUP BY SaleDate, DATEDIFF(Day, SaleDate, ShipDate)

 

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

Если для отбора строк используется предложение WHERE, оно может исключить строки с некоторым значением столбца (или столбцов) группировки. Если в группу попала ни одна строка, SQL Server не генерирует для нее строку результирующей таблицы. Таким образом, следующая инструкция

 

SELECT ShipCity, COUNT(*) AS "Customer Count",

FROM Customer

WHERE Discount >.01

GROUP BY ShipCity

 

сгенерирует таблицу, не имеющую строк с данными для Albany и Seattle

Для того чтобы получить строку для каждой группы, даже если она окажется пустой, поместите за ключевыми словами GROUP BY ключевое слово ALL:

 

SELECT ShipCity, COUNT(*) AS "Customer Count",

FROM Customer

WHERE Discount >.01

GROUP BY ALL ShipCity

Предложение HAVING

 

Предложение HAVING используется для отбора строк результирующей таблицы уже после применения к сгруппированным строкам агрегатных функций. Оно похоже на предложение WHERE, служащее для отбора строк перед группировкой. Например, следующая инструкция SELECT возвращает данные о городах, в которых средняя скидка клиентам превысила один процент:

 

SELECT ShipCity, COUNT(*) AS "Customer Count",

AVG(Discount) AS "Average Discount"

FROM Customer

WHERE Discount IS NOT NULL

GROUP BY ShipCity

HAVING AVG(Discount) >.01

 

Условие отбора, заданное в предложении HAVING, может содержать столбцы группировки, как например ShipCity, или агрегатные функции, как например AVG(Discount)..

Примечание. Хотя предложение HAVING может быть задано и без предложения GROUP BY, так поступают редко.

Предложение ORDER BY

 

Предложение ORDER BY служит для сортировки результирующей таблицы инструкции SELECT перед ее возвращением приложению. В этом предложении задается список столбцов и порядок сортировки значений каждого из этих столбцов: по возрастанию (задается ключевым словом ASC и подразумевается по умолчанию) или по убыванию (задается ключевым словом DESC).

Примечание. Предложение ORDER BY не является частью предложения SELECT, включаемого в другие инструкции, оно используется только в инструкции SELECT. Его можно включать в инструкции SELECT, INSERT, но не в определения представлений.

Следующая инструкция возвращает информацию о заказах на уже отгруженные товары, отсортированную по кодам клиентов, а для каждого клиента еще и по количеству дней, прошедшему от момента оформления заказа до времени отгрузки:

 

SELECT CustId, OrderId, SaleDate, ShipDate,

DATEDIFF(Day, SaleDate, ShipDate)

FROM Sale

WHERE ShipDate IS NOT NULL

ORDER ВY Custld, 5 DESC

 

В этом примере безымянный пятый столбец, получающийся путем вычисления выражения DATEDIFF(Day, SaleDate, ShipDate), используется для сортировки по убыванию строк с одинаковыми значениями столбца Custld.

Чтобы сделать эту инструкцию более понятной, можно определить для вычисляе­мого столбца псевдоним-и использовать его в предложении ORDER BY:

 

SELECT CustId, OrderId, SaleDate, ShipDate,

DATEDIFF(Day, SaleDate, ShipDate) AS DaysToShip

FROM Sale

WHERE ShipDate IS NOT NULL

ORDER ВY Custld,

DaysToShip DESC

 

Задание к работе

Создать представления с помощью мастера SQL Server и языка Transact-SQL для выборки данных и формирования отчетов.