лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Oracle Oracle позволяет использовать составные интервалы, например, 1 день и 3 часа: 1. SELECT timestamp '2018-01-27 13:00:00' + interval '01 03' DAY TO HOUR FROM dual; и 1 день 15 секунд 1. SELECT timestamp '2018-01-27 13:00:00' + interval '01 00:00:15' DAY TO SECOND FROM dual; Разумеется, можно также прибавить два простых интервала, как и в случае PostgreSQL. Функция DATEDIFF Синтаксис: 1. DATEDIFF ( datepart, startdate, enddate ) Функция возвращает интервал времени, прошедшего между двумя временными отметками — startdate (начальная отметка) и enddate (конечная отметка). Этот интервал может быть измерен в разных единицах. Возможные варианты определяются аргументом datepart и перечислены выше применительно к функции DATEADD. Пример 7.1.2 Определить количество дней, прошедших между первым и последним совершенными рейсами. 1. SELECT DATEDIFF ( dd, ( SELECT MIN ( date ) 2. FROM pass_in_trip 3. ) , 4. ( SELECT MAX ( date ) 5. FROM pass_in_trip 6. ) 7. ) ; Пример 7.1.3 Определить продолжительность рейса 1123 в минутах. Здесь следует принять во внимание, что время вылета (time_out) и время прилета (time_in) хранится в полях типа datetime таблицы Trip. Как уже отмечалось, SQL Server вплоть до версии 2005 не имел отдельных темпоральных типов данных для даты и времени. Поэтому при вставке в поле datetime только времени (например, UPDATE trip SET time_out = ’17:24:00’ WHERE trip_no = 1123), время будет дополнено значением даты по умолчанию (1900-01-01), являющейся начальной точкой отсчета времени. Напрашивающееся решение 1. SELECT DATEDIFF ( mi, time_out, time_in ) dur 2. FROM trip 3. WHERE trip_no = 1123 ; (которое дает 760 минут) будет неверным по двум причинам. Во-первых, для рейсов, которые вылетают в один день, а прилетают на следующий, вычисленное таким способом значение будет неправильным; Во-вторых, ненадежно делать какие либо предположения относительно дня, который присутствует только в силу необходимости соответствовать типу datetime. Но как определить, что самолет приземлился на следующий день? Тут помогает описание предметной области, где говорится, что полет не может продолжаться более суток. Итак, если время прилета не больше, чем время вылета, то этот факт имеет место. Теперь второй вопрос: как посчитать только время, с каким бы днем оно ни стояло? Здесь может помочь функция T-SQL DATEPART, речь о которой пойдет в следующем пункте Следует иметь в виду одну особенность использования функции DATEDIFF. Начнем с примеров. Сначала посчитаем число недель с воскресенья 23-10- 2005 до субботы 29-10-2005. Итак, 1. SELECT DATEDIFF ( wk, '20051023' , '20051029' ) ; Здравый смысл подсказывает, что это полная неделя, однако, вышеприведенный запрос дает 0. Теперь возьмем интервал с субботы 29-10- 2005 до воскресенья 30-10-2005: 1. SELECT DATEDIFF ( wk, '20051029' , '20051030' ) ; В результате получим 1, то есть одну неделю. Пора дать объяснения. Дело в том, что функция DATEDIFF фактически считает неделей не число дней, а число переходов с субботы на воскресенье. Если это иметь в виду, то тогда становится понятным еще более удивительный пример: 1. SELECT DATEDIFF ( wk, '20051029 23:59:00' , '20051030 00:01:00' ) ; который тоже дает единицу! Странно? Возможно, но, как говорится, кто предупрежден, тот вооружен. То же самое имеет место и для других интервалов. Например, количество дней дает нам не число часов, деленное нацело на 24 (количество часов в сутках), а число переходов через полночь. Хотите подтверждения? Пожалуйста 1. SELECT DATEDIFF ( dd, '20051029 23:59:00' , '20051030 00:01:00' ) ; В результате получаем один день, в то же время 1. SELECT DATEDIFF ( dd, '20051029 00:00:00' , '20051029 23:59:59' ) ; дает нам 0. Если хотите, можете продолжить эксперименты с другими временными интервалами. Вы можете воспользоваться для этого предоставляемой Консолью. Задача. Посчитать число минут в интервале между двумя датами – '2011-10-07 23:43:00' и '2011-10-08 01:23:00' SQL Server Встроенная функция DATEDIFF решает проблему: 1. SELECT DATEDIFF ( minute, '2011-10-07T23:43:00' , '2011-10- 08T01:23:00' ) ; Результат – 100 минут. Примечание: В запросе используется стандартное представление даты (ISO) в виде текстовой строки 'yyyy-mm-ddThh:mm:ss', которое не зависит ни от локальных настроек сервера, ни и от самого сервера. MySQL Функция DATEDIFF есть и в MySQL, однако она имеет совсем другой смысл. DATEDIFF вычисляет число дней между двумя датами, являющихся аргументами этой функции. При этом если дата представлена в формате дата- время, используется только составляющая даты. Поэтому все три нижепредставленных запроса дадут один и тот же результат -1. Положительный результат будет получен, если первый аргумент больше второго. 1. SELECT DATEDIFF ( '2011-10-07T23:43:00' , '2011-10- 08T01:23:00' ) ; 2. SELECT DATEDIFF ( '2011-10-07' , '2011-10-08' ) ; 3. SELECT DATEDIFF ( '2011-10-07T23:43:00' , '2011-10-08' ) ; Один день мы получим даже в случае, если интервал между датами составляет все одну секунду: 1. SELECT DATEDIFF ( '2011-10-07T23:59:59' , '2011-10- 08T00:00:00' ) ; Решение же нашей задачи можно получить при помощи другой встроенной функции – TIMESTAMPDIFF, которая аналогичная функции DATEDIFF в SQL Server: 1. SELECT TIMESTAMPDIFF ( minute, '2011-10-07T23:43:00' , '2011-10-08T01:23:00' ) ; PostgreSQL В PostgreSQL нет функции, подобной DATEDIFF (SQL Server) или TIMESTAMPDIFF (MySQL). Поэтому для решения задачи можно применить следующую последовательность действий: 1. представить разность между двумя датами интервалом; 2. посчитать число секунд в интервале; 3. поделить полученное на шаге 2 значение на 60, чтобы выразить результат в минутах. Для получения интервала можно взять разность двух значений темпорального типа, При этом требуется явное преобразование типа: 1. SELECT timestamp '2011-10-08T01:23:00' - timestamp '2011- 10-07T23:43:00' ; или в стандартном исполнении 1. SELECT CAST ( '2011-10-08T01:23:00' AS timestamp ) - CAST ( '2011-10-07T23:43:00' AS timestamp ) ; Результат - "01:40:00", который есть не что иное, как один час и сорок минут. Можно также воспользоваться встроенной функцией AGE, которая выполняет заодно неявное преобразование типа своих аргументов: 1. SELECT AGE ( '2011-10-08T01:23:00' , '2011-10- 07T23:43:00' ) ; Для получения числа секунд в интервале воспользуемся функцией 1. EXTRACT ( EPOCH FROM < interval > ) Значение интервала представим последним способом как наиболее коротким. Для получения окончательного решения задачи поделим полученный результат на 60: 1. SELECT EXTRACT ( EPOCH FROM AGE ( '2011-10-08T01:23:00' , '2011-10-07T23:43:00' )) / 60 ; В данном случае мы имеем результат, выраженный целым числом, поскольку оба значения времени в задаче не содержали секунд. В противном случае, будет получено десятичное число, например: 99.75: 1. SELECT EXTRACT ( EPOCH FROM AGE ( '2011-10-08T01:23:00' , '2011-10-07T23:43:15' )) / 60 ; Oracle Oracle тоже не имеет в своем арсенале функции типа DATEDIFF. Кроме того, Oracle не поддерживает стандартное текстовое представление даты/времени. Мы можем посчитать интервал в минутах, приняв во внимание, что разность дат (значений типа date) в Oracle дает в результате число дней (суток). Тогда для вычисления интервала в минутах нужно просто разность дат умножить на 24 (число часов в сутках), а затем на 60 (число минут в часе): 1. SELECT ( CAST ( '2011-10-08 01:23:00' AS date ) - CAST ( '2011- 10-07 23:43:00' AS date )) * 24 * 60 2. FROM dual; Подобным образом можно получить и другие временные интервалы. Функция DATEPART Синтаксис: 1. DATEPART ( datepart , date ) Эта функция возвращает целое число, представляющее собой указанную аргументом datepart часть заданной вторым аргументом даты (date). Список допустимых значений аргумента datepart , описанный выше в данном разделе, дополняется еще одним значением Datepart Допустимые сокращения Weekday — день недели dw Заметим, что возвращаемое функцией DATEPART значение в этом случае (номер дня недели) зависит от настроек, которые можно изменить с помощью оператора SET DATEFIRST, устанавливающего первый день недели. Для кого-то понедельник — день тяжелый, а для кого-то — воскресенье. Как и многие региональные настройки, по умолчанию это значение в SQL Server зависит от выбранного языка. Однако вернемся к примеру 7.1.3 . В предположении, что время вылета/прилета является кратным минуте, мы можем его определить как сумму часов и минут. Поскольку функции даты/времени работают с целочисленными значениями, приведем результат к наименьшему интервалу — минутам. Итак, время вылета рейса 1123 в минутах: 1. SELECT DATEPART ( hh, time_out ) * 60 + DATEPART ( mi, time_out ) 2. FROM Trip 3. WHERE trip_no = 1123 ; и время прилета 1. SELECT DATEPART ( hh, time_in ) * 60 + DATEPART ( mi, time_in ) 2. FROM Trip 3. WHERE trip_no = 1123 ; Теперь мы должны сравнить, превышает ли время прилета время вылета. Если это так, следует вычесть из первого второе, чтобы получить продолжительность рейса. В противном случае к разности нужно добавить одни сутки (24*60 = 1440 минут). 1. SELECT CASE 2. WHEN time_dep >= time_arr 3. THEN time_arr - time_dep + 1440 4. ELSE time_arr - time_dep 5. END dur 6. FROM ( SELECT DATEPART ( hh, time_out ) * 60 + DATEPART ( mi, time_out ) time_dep, 7. DATEPART ( hh, time_in ) * 60 + DATEPART ( mi, time_in ) time_arr 8. FROM Trip 9. WHERE trip_no = 1123 10. ) tm; Здесь, чтобы не повторять длинные конструкции в операторе CASE, использован подзапрос. Конечно, результат получился достаточно громоздким, зато абсолютно корректным в свете сделанных к этой задаче замечаний. Пример 7.1.4 Определить дату и время вылета рейса 1123. В таблице совершенных рейсов Pass_in_trip содержится только дата рейса, но не время, так как в соответствии с предметной областью каждый рейс может выполняться только один раз в день. Для решения этой задачи нужно к дате, хранящейся в таблице Pass_in_trip, добавить время из таблицы Trip 1. SELECT DISTINCT pt.trip_no, DATEADD ( mi, DATEPART ( hh,time_out ) * 60 + 2. DATEPART ( mi,time_out ) , date ) [ time ] 3. FROM Pass_in_trip pt JOIN 4. Trip t ON pt.trip_no = t.trip_no 5. WHERE t.trip_no = 1123 ; Выполнив запрос, получим следующий результат Trip_no Time 1123 2003-04-05 16:20:00.000 1123 2003-04-08 16:20:00.000 DISTINCT необходим здесь, чтобы исключить возможные дубликаты, поскольку номер и дата рейса дублируются в этой таблице для каждого пассажира данного рейса. В ряде случаев функцию DATEPART можно заменить более простыми функциями. Вот они: DAY(date) — целочисленное представление дня указанной даты. Эта функция эквивалентна функции DATEPART(dd, date). MONTH(date) — целочисленное представление месяца указанной даты. Эта функция эквивалентна функции DATEPART(mm, date). YEAR(date) — целочисленное представление года указанной даты. Эта функция эквивалентна функции DATEPART(yy, date). MySQL В MySQL для извлечения каждой составляющей даты имеется соответствующая функция. Так, например, можно получить минуты времени отправления рейсов, которые вылетают в 1-ом часу дня (база данных "Аэропорт"): Решение 1 1. SELECT time_out, MINUTE ( time_out ) 2. FROM trip 3. WHERE HOUR ( time_out ) = 12 ; time_out min 1900-01-01 12:35:00 35 1900-01-01 12:00:00 0 Компоненту времени можно также получить при помощи функции EXTRACT. С помощи этой функции решение задачи можно записать в виде: Решение 2 1. SELECT time_out, EXTRACT ( MINUTE FROM time_out ) AS MIN 2. FROM trip 3. WHERE EXTRACT ( HOUR FROM time_out ) = 12 ; Дополнительно с помощью функции EXTRACT можно получить составные компоненты даты/времени, например, год и месяц. Решим такую задачу. Посчитать количество окрасок по месяцам с учетом года (база данных "Окраска"). Решение 3 1. SELECT YEAR ( b_datetime ) AS y, MONTH ( b_datetime ) AS m, 2. EXTRACT ( YEAR_MONTH FROM b_datetime ) AS ym, COUNT ( * ) AS qty 3. FROM utb 4. GROUP BY EXTRACT ( YEAR_MONTH FROM b_datetime ) ; y m ym qty 2000 1 200001 1 2001 1 200101 1 2002 1 200201 1 2002 6 200206 1 2003 1 200301 69 2003 2 200302 7 2003 3 200303 2 2003 4 200304 2 2003 5 200305 2 2003 6 200306 2 Полный список допустимых компонент можно найти, например, здесь Примечание: Здесь уместно будет отметить довольно вольную трактовку группировки в MySQL, при которой в списке столбцов предложения SELECT могут присутствовать столбцы с детализированными данными, отсутствующими в предложении GROUP BY. Очевидно, что агрегаты здесь подразумеваются, т.к., в противном случае, отсутствует однозначная интерпретация операции. Предполагаю, что здесь неявно используются функция MIN или MAX, но я даже не буду это специально выяснять, т.к. предпочитаю в подобных случаях следовать стандарту. PostgreSQL Функций типа Year, Month и т.д. в PostgreSQL, насколько мне известно, нет. Однако есть функция EXTRACT, и второе решение первой задачи, которое мы написали для MySQL, будет работать и под этой СУБД: 1. SELECT time_out, EXTRACT ( MINUTE FROM time_out ) AS MIN 2. FROM trip 3. WHERE EXTRACT ( HOUR FROM time_out ) = 12 ; Имеется также функция, аналогичная DATEPART в MSSQL. Различия в синтаксисе, надеюсь, станут очевидны из примера решения той же задачи с использованием этой функции: 1. SELECT time_out, DATE_PART ( 'MINUTE' , time_out ) AS MIN 2. FROM trip 3. WHERE DATE_PART ( 'HOUR' , time_out ) = 12 ; Перейдем к решению второй задачи. Составные компоненты не поддерживаются в функции EXTRACT для PostgreSQL. Поэтому можно использовать "классическую" групировку по двум компонентам - году и месяцу: Решение 4 1. SELECT EXTRACT ( YEAR FROM b_datetime ) AS y, 2. EXTRACT ( MONTH FROM b_datetime ) AS m, COUNT ( * ) AS qty 3. FROM utb 4. GROUP BY EXTRACT ( YEAR FROM b_datetime ) , EXTRACT ( MONTH FROM b_datetime ) ; Тем не менее, мы можем реализовать идею составной компоненты при помощи функции форматирования даты TO_CHAR : 1. SELECT TO_CHAR ( b_datetime, 'YYYYMM' ) AS ym, COUNT ( * ) AS qty 2. FROM utb 3. GROUP BY TO_CHAR ( b_datetime, 'YYYYMM' ) ; Результат будет аналогичен результату решения 3 за отсутствием первых двух столбцов, которые, разумеется, можно добавить в вывод, включив их одновременно в предложение GROUP BY, как это сделано в решении 4. Функция DATENAME Функция DATENAME( datepart, date ) возвращает символьное представление составляющей (datepart) указанной даты (date). Аргумент, определяющий составляющую даты, может принимать одно из значений, перечисленных в таблице в начале этой главы Это дает нам простую возможность конкатенировать компоненты даты, получая любой нужный формат представления. Например, конструкция 1. SELECT DATENAME ( weekday, '20031231' ) + ', ' + DATENAME ( day, '20031231' ) + 2. ' ' + DATENAME ( month, '20031231' ) + ' ' + DATENAME ( year, '20031231' ) ; даст нам следующий результат Wednesday, 31 December 2003 Следует отметить, что данная функция выявляет отличие значений day и dayofyear аргумента datepart. Первый дает символьное представление дня указанной даты, в то время как второй дает символьное представление этого дня от начала года. То есть 1. SELECT DATENAME ( day, '20031231' ) ; даст нам 31, в то время как для 1. SELECT DATENAME ( dayofyear, '20031231' ) ; результатом будет 365. |