# Встроенные функции и условная логика **Встроенные функции** **Строки:** экранирование: дополнителная кавычка '' или \\' quote() +кавычки при выборке текста: ```sql SELECT quote(text_fId) FROM string_tbl; -> 'This string didn\'t work, but it does now' ``` char() берет номера, и объединяет в строку ```sql char(97,98,99) -> abc ``` concat() (+ в MS SQL) объединяет строки ```sql UPDATE string_tbl SET text_fld = CONCAT(text_fld, 'but now it is longer'); select concat(first_name, ' ', last_name, ' has been customer since ', date(create_date)) narrative from customer; ``` length() длина строки position начало подстроки SELECT POSITION('characters' IN vchar\_fld) FROM string\_tbl; Первый с 1. 0 если не найдено. locate как position, но 3 аргумент - старт поиска insert вставка replace заменяет substring находит подстроку объединение данных из группировки в столбец ```sql SELECT id, GROUP_CONCAT(data) FROM yourtable GROUP BY id  ``` **Числа:** ceil(), floor()округление в большую или меньшую сторону к ближайшему целому числу round() десятичная часть >=0,5 округлено в большую сторону и наоборот ROUND(72.0909,3) - 3 знака оставить TRUNCATE(72.0909, 1) - простое усечение, оставит 72.0 аргумент может быть <0 TRUNCATE(17, -1) = 10, round(17, -1) = 20 sign() знак числа abs() абсолютное значение **Даты:** cast() строку формата YYY-MM-DD HH:MM:SS в дату ```sql SELECT HOUR(@dt), MINUTE(@dt), SECOND(@dt), DAY(@dt), WEEK(@dt), MONTH(@dt), QUARTER(@dt), YEAR(@dt); ``` str\_to\_date(str, 'format') Формат:
%MПолное имя месяца (January..December)
%mЧисловое значение месяца
%dЧисловое значение дня месяца (00..31)
%jДень года (001..366)
%WПолное имя дня недели (Sunday.Saturday)
%YЗначение года (четыре цифры)
%yЗначение года (две цифры)
%HЧас дня в 24-часовом формате (00..23)
%hЧас дня в 12-часовом формате (01..12)
%iМинуты в часе (00..59)
%sЧисло секунд (00..59)
%f Число микросекунд (000000..999999)
%pAM или РМ
%aКраткое имя дня недели — Sun, Mon,...
%bКраткое имя месяца — Jan,Feb,...
CURRENT\_DATE(), CURRENT\_TIME(), CURRENT\_TIMESTAMP() interval ```sql SELECT DATE ADD(CURRENT DATE(), INTERVAL 5 DAY) ``` последний день месяца ```sql LAST_DAY('2019-09-17') ``` имя месяца ```sql DAYNAME('2019-09-18') ``` извлекают элемент даты ```sql EXTRACT(YEAR FROM '2019-09-18 22:19:05') ``` кол-во полных дней ```sql DATEDIFF('2019-09-03', 2019-06-211) ``` ```sql SELECT CURRENT_TIME() AS 'CUR_TIME', ADDTIME(CURRENT_TIME(), 020000) AS 'ADDTIME', SUBTIME(CURRENT_TIME(), 020000) AS 'SUBTIME'; | CUR_TIME | ADDTIME | SUBTIME | | 10:12:34 | 12:12:34 | 08:12:34 | ``` **Условная логика** **case:** ```sql SELECT c.first_name, c.last_name, CASE WHEN active = 0 THEN 0 ELSE (select count(*) from rental r where r.customer_id = c.customer_id) END activity_type FROM customer c; ``` преимущество перед if-then в возможности использовать внутри select, insert, update, delete возвращаемые значения одного типа Использование case для разделения по столбцам ```sql SELECT SUM(CASE WHEN monthname(rental_date) = 'May' THEN 1 ELSE 0 END) May_rentals, SUM(CASE WHEN monthname(rental_date) = 'June' THEN 1 ELSE 0 END) June_rentals, SUM(CASE WHEN monthname(rental_date) = 'July' THEN 1 ELSE 0 END) July_rentals FROM rental WHERE rental date BETWEEN 2005-05-01' AND '2005-08-01'; ``` использование case для получения факта участия актера в фильмах статуса G ```sql SELECT a.first_name, a.last_name, CASE WHEN EXISTS (SELECT 1 FROM film_actor fa INNER JOIN film f ON fa.film_id = f.film_id WHERE fa.actor_id = a.actor_id AND f.rating = 'G') THEN 'Y' ELSE 'N' END g_actor FROM actor a WHERE a.last name LIKE 'S%' OR a.first name LIKE 'S%'; ``` условные обновления ```sql UPDATE customer SET active = CASE WHEN 90 <= (SELECT datediff(now(), max(rental_date) FROM rental r WHERE r.customer id = customer.customer id)) THEN 0 ELSE 1 END WHERE active = 1; ``` **Аналитические функции** Постобработка сформированных данных. Использование: функция(столбец) over (дополнительное условие группировки)
ФункцияОписание
RANKАналогична функции DENSE\_RANK() за исключением того, что в последовательности ранжированных значений есть пробелы, когда две или более строки имеют одинаковый ранг.
DENSE\_RANKПрисваивает ранг каждой строке в своем разделе на основе предложения ORDER BY. Он присваивает одинаковый ранг строкам с одинаковыми значениями. Если две или более строки имеют одинаковый ранг, то в последовательности ранжированных значений не будет пробелов.
ROW\_NUMBERНазначает последовательное целое число каждой строке в своем разделе
CUME\_DISTВычисляет совокупное распределение значения в наборе значений.
FIRST\_VALUEВозвращает значение указанного выражения относительно первой строки в рамке окна.
LAGВозвращает значение N-й строки перед текущей строкой в разделе. Возвращает NULL, если предшествующей строки не существует.
LAST\_VALUEВозвращает значение указанного выражения относительно последней строки в рамке окна.
LEADВозвращает значение N-й строки после текущей строки в разделе. Возвращает NULL, если никакой последующей строки не существует.
NTH\_VALUEВозвращает значение аргумента из N-й строки рамки окна
NTILEРаспределяет строки для каждого раздела окна в указанное количество ранжированных групп.
PERCENT\_RANKВычисляет процентильный ранг строки в разделе или наборе результатов