SQL Server и MySQL в функции Date
w3big.comLatest web development tutorials
Предыдущий: SQL CREATE VIEW, REPLACE VIEW, DROP VIEW заявление
Далее: значение NULL SQL — IS NULL и IS NOT NULL
SQL Дата (даты)
Когда мы имеем дело с датой, это, вероятно, самая сложная задача для того, чтобы вставить формат даты соответствует формату даты в столбце базы данных.
До тех пор пока данные содержат только часть даты, ваши запросы пойдет не так. Тем не менее, если время компонент участвует, ситуация немного сложнее.
Прежде чем обсуждать сложность даты запроса, давайте посмотрим на наиболее важных встроенных функций обработки даты.
Функции даты MySQL
В следующей таблице перечислены наиболее важные встроенные функции MySQL даты:
| 函数 | 描述 |
|---|---|
| NOW() | 返回当前的日期和时间 |
| CURDATE() | 返回当前的日期 |
| CURTIME() | 返回当前的时间 |
| DATE() | 提取日期或日期/时间表达式的日期部分 |
| EXTRACT() | 返回日期/时间的单独部分 |
| DATE_ADD() | 向日期添加指定的时间间隔 |
| DATE_SUB() | 从日期减去指定的时间间隔 |
| DATEDIFF() | 返回两个日期之间的天数 |
| DATE_FORMAT() | 用不同的格式显示日期/时间 |
SQL Дата сервера Функции
В следующей таблице перечислены SQL Server в наиболее важных встроенных функций даты:
| 函数 | 描述 |
|---|---|
| GETDATE() | 返回当前的日期和时间 |
| DATEPART() | 返回日期/时间的单独部分 |
| DATEADD() | 在日期中添加或减去指定的时间间隔 |
| DATEDIFF() | 返回两个日期之间的时间 |
| CONVERT() | 用不同的格式显示日期/时间 |
SQL Дата типы данных
MySQL использует следующие типы данных , хранящихся в значения даты или даты / времени в базе данных:
- ДАТА — Формат: YYYY-MM-DD
- DATETIME — Формат: YYYY-MM-DD HH: MM: SS
- TIMESTAMP — Формат: YYYY-MM-DD HH: MM: SS
- ГОД — Формат: YYYY или YY
SQL Server использует следующие типы данных , хранящихся в значения даты или даты / времени в базе данных:
- ДАТА — Формат: YYYY-MM-DD
- DATETIME — Формат: YYYY-MM-DD HH: MM: SS
- SMALLDATETIME — Формат: YYYY-MM-DD HH: MM: SS
- TIMESTAMP — Формат: Единственный цифровой
Примечание: При создании новой таблицы в базе данных, вам необходимо выбрать тип данных для столбца!
Для всех доступных типов данных, пожалуйста , посетите наши полные типы данных Справочное руководство .
Дата обработки SQL
Если деталь не включает в себя время, то можно легко сравнить две даты!
Предположим, мы имеем следующие «Заказы» таблица:
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 |
| 2 | Camembert Pierrot | 2008-11-09 |
| 3 | Mozzarella di Giovanni | |
| 4 | Mascarpone Fabioli | 2008-10-29 |
Теперь мы хотим выбрать OrderDate как «2008-11-11» записей из таблицы.
Мы используем следующую ЗЕЬЕСТ:
SELECT * FROM Orders WHERE OrderDate=’2008-11-11′
Результат устанавливается следующим образом:
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
Теперь предположим, что «Заказы» таблица выглядит следующим образом (обратите внимание на «OrderDate» колонка неполный рабочий день):
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 13:23:44 |
| 2 | Camembert Pierrot | 2008-11-09 15:45:21 |
| 3 | Mozzarella di Giovanni | 2008-11-11 11:12:01 |
| 4 | Mascarpone Fabioli | 2008-10-29 14:56:59 |
Если мы используем один и тот же оператор SELECT, как описано выше:
SELECT * FROM Orders WHERE OrderDate=’2008-11-11′
Тогда мы получим результаты! Это связано с датой запроса не содержит временную часть.
Совет: Если вы хотите , чтобы сделать запросы простой и легкий в обслуживании, то , пожалуйста , не используйте компоненты времени ваши даты!
Предыдущий: SQL CREATE VIEW, REPLACE VIEW, DROP VIEW заявление
Далее: значение NULL SQL — IS NULL и IS NOT NULL
w3big.com | HTML курс | Web курс | Web Tutorial
Блог об аналитике, визуализации данных, data science и BI: mysql
Расчет накопленной (или кумулятивной, что то же самое) суммы SQL — это очень распространенный запрос, который часто используют в анализе финансов, динамики прибыли и прочих показателей компании. В сегодняшней статье вы узнаете, что такое накопленная сумма и как можно написать SQL-запрос для ее вычисления.
Если вы вдруг являетесь начинающим пользователем SQL, то давайте, как в школьной задаче, поймем, что нам дано и что нам необходимо найти. Накопленная сумма — это совокупная сумма предыдущих чисел в столбце. Давайте посмотрим на пример ниже, чтобы точно знать, какой результат мы ожидаем увидеть в итоге.
Итак, существует таблица leftjoin.daily_sales_sample, в которой есть всего два столбца date и revenue. По столбцу revenue нам нужно рассчитать накопленную сумму и записать результат в отдельный столбец.
Что у нас есть?
| Date | Revenue |
| 10.11.2021 | 1200 |
| 11.11.2021 | 1600 |
| 12.11.2021 | 800 |
| 13.11.2021 | 3000 |
Что мы хотим найти?
| Date | Revenue | Cumulative Revenue |
| 10.11.2021 | 1200 | 1200 ↓ |
| 11.11.2021 | 1600 | 2800↓ |
| 12.11.2021 | 800 | 3600 ↓ |
| 13.11.2021 | 3000 | 6600 |
На графике две этих переменных выглядят следующим образом:
Итак, без лишних слов, давайте приступать к решению задачи.
Способ 1 — Идеальный — Используем оконные функции
Итак, если в базе данных можно пользоваться оконными функциями, то жизнь хороша и прекрасна. С их помощью можно написать простой запрос, который будет суммировать значения из столбца revenue по мере увеличения даты и сразу вернет нам таблицу с кумулятивной суммой в столбце, который мы назвали total.
SELECT date, revenue, SUM(revenue) OVER (ORDER BY date asc) as total FROM leftjoin.daily_sales_sample ORDER BY date;
Способ 2 — Хитрый — Решение без оконных функций
Вполне возможно, что вам понадобится решить такую задачу без использования оконных функций. К примеру, если вы используете MySQL (до 8 версии) или любую другую БД, в которой оконных функций нет. Тогда решение задачи чуть усложняется. Однако, вы ведь знаете, что нет ничего невозможного?
В нашем примере, для 10 ноября — 10 ноября, для 11 ноября — 10 и 11 ноября и так далее. Промежуточный запрос будет выглядеть вот так:SELECT * FROM leftjoin.daily_sales_sample ds1 INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date ORDER BY ds1.date, ds2.date;
А его результат:
| Date 1 | Revenue 1 | Date 2 | Revenue 2 |
| 10.11.2021 | 1200 | 10.11.2021 | 1200 |
| 11.11.2021 | 1600 | 10.11.2021 | 1200 |
| 11.11.2021 | 1600 | 11.11.2021 | 1600 |
| 12.11.2021 | 800 | 10.11.2021 | 1200 |
| 12.11.2021 | 800 | 11.11.2021 | 1600 |
| 12.11.2021 | 800 | 12.11.2021 | 800 |
| 13.11.2021 | 300 | 10.11.2021 | 1200 |
13. 11.2021 | 300 | 11.11.2021 | 1600 |
| 13.11.2021 | 300 | 12.11.2021 | 800 |
| 13.11.2021 | 300 | 13.11.2021 | 300 |
А затем, нужно просуммировать прибыли, группируя их по каждой дате. Если собрать все в единый запрос, то он будет выглядеть вот так:
SELECT ds1.date, ds1.revenue, SUM(ds2.revenue) as total FROM leftjoin.daily_sales_sample ds1 INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date GROUP BY ds1.date, ds1.revenue ORDER BY ds1.date;
Способ 3 — Специфический — Решение с помощью массивов в ClickHouse
Если вы используете Clickhouse, то в этой системе есть специальная функция, которая может помочь рассчитать кумулятивную сумму. Для начала, нам нужно преобразовать все столбцы таблицы в массивы и рассчитать показатель «Moving Sum» для столбца revenue.
SELECT groupArray(date) dates, groupArray(revenue) as revs, groupArrayMovingSum(revenue) AS total FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample ORDER BY date)
daily_sales_sample
ORDER BY date)Спасибо Дмитрию Титову из Altinity за комментарий про сортировку в подзапросе
Так, мы получим три массива значений:
| dates | revs | total |
| [’10.11.2021’,’11.11.2021’,’12.11.2021’,’13.11.2021’] | [1200, 1600, 800, 300] | [1200, 2800, 3600, 3900] |
Но три массива, которые записаны в ячейки — это не то, что мы хотим получить, хотя значения этих массивов уже абсолютно соответствуют искомому результату. Теперь массивы нужно привести обратно к табличному виду с помощью функции ARRAY JOIN.
SELECT dates, revs, total FROM (SELECT groupArray(date) dates, groupArray(revenue) as revs, groupArrayMovingSum(revenue) AS total FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample ORDER BY date)) as t ARRAY JOIN dates, revs, total;
Бонус — Оконные функции в Clickhouse
Если вам не хочется иметь дело с массивами, что иногда и правда бывает затратно по времени, то есть еще один вариант решения задачи.
Можно использовать оконные функции, например функцию runningAccumulate(), которая суммирует значения всех ячеек с первой до текущей.
SELECT date, runningAccumulate(revenue)
FROM
(
SELECT date, sumState(revenue) AS revenue
FROM leftjoin.daily_sales_sample
GROUP BY date
ORDER BY date ASC
)
ORDER BY dateЕсли вы столкнетесь с необходимостью рассчитать кумулятивную сумму в SQL, то теперь вы сможете решить эту задачу, в какой бы системе управления баз данных ни была организована работа 🙂
Функции даты
Таблица 1. Функции текущей даты
| Функция | Тип | Пример | Результат |
|---|---|---|---|
СЕЙЧАС() * Возвращает текущую локальную дату и время. | дата/время | СЕЙЧАС() | пр. |
ДАТА(дата) * извлекает дату из ввода. Если время включено, время отбрасывается. | дата/время | ДАТА(‘2020-01-01 11:31:31’) | ‘2020-02-24’ |
ТЕКУЩАЯ_ДАТА() * Возвращает текущую локальную дату | дата | ТЕКУЩАЯ_ДАТА | ‘2020-02-24’ |
ТЕКУЩЕЕ_ВРЕМЯ() * Возвращает текущее местное время. | время | ТЕКУЩЕЕ_ВРЕМЯ | ‘11:52:10’ |
UTC_DATE() * Возвращает текущую дату в формате UTC. | дата | UTC_DATE | ‘2020-02-24’ |
UTC_TIME() * Возвращает текущую дату в формате UTC. | время | UTC_TIME | ‘18:52:10’ |
‘2020-02-24 09:31:31’
ВЫБРАТЬ СЕЙЧАС() КАК "СЕЙЧАС()",
DATE('2020-01-01') AS 'DATE(), только дата',
CURRENT_DATE AS 'CURRENT_DATE',
CURRENT_TIME AS 'CURRENT_TIME',
UTC_DATE AS 'UTC_DATE',
UTC_TIME AS 'UTC_TIME';
Результаты:
Таблица 2. Функция DATE_ADD
| Функция | Тип | Пример | Результат |
|---|---|---|---|
DATE_ADD(дата, единица выражения интервала) | ДАТА, ДАТАВРЕМЯ | DATE_ADD(‘2020-01-01’, ИНТЕРВАЛ 1 ДЕНЬ) | ‘202-01-02’ |
Фрагмент кода:
Велосипед USE;
SELECT order_date,
DATE_ADD(order_date, INTERVAL 1 DAY) AS 'ДАТА ЗАКАЗА ПЛЮС 1 день',
DATE_ADD(order_date, INTERVAL 6 MONTH) AS 'ДАТА ЗАКАЗА ПЛЮС 6 месяцев',
DATE_ADD(order_date, INTER ВАЛ '2 12' ДЕНЬ_ЧАС)
AS 'ДАТА ЗАКАЗА ПЛЮС 2 дня 1 час'
ОТ cust_order;
Результаты:
Таблица 3.
Функция DATE_FORMAT
| Функция | Тип | Пример | Результат |
|---|---|---|---|
ФОРМАТ_ДАТЫ | ДАТА | DATE_FORMAT(‘2020-09-03’, ‘%m/%d/%y’) | 03.09.14 |
Фрагмент кода:
USE world;
ВЫБЕРИТЕ имя, континент, DATE_FORMAT('2020-01-28', '%m/%d/%y')
ИЗ страны;
Результаты:
%a
Сокращенное название дня недели (Вс..Сб)
%b
Сокращенное название месяца (янв..дек)
%с
Месяц, число (0.
.12)
%D
День месяца с английским суффиксом (0, 1, 2, 3, …)
%д
День месяца, число (00..31)
%е
День месяца, числовой (0..31)
%f
микросекунды (000000..999999)
%H
Час (00..23)
%ч
Час (01..12)
%I
Час (01..12)
%i
Минуты, числовое значение (00..59)
%j
День года (001.
.366)
%к
Час (0..23)
%l
Час (1..12)
%М
Название месяца (январь..декабрь)
%м
Месяц, число (00..12)
%р
%r
Время, 12-часовой формат ( чч:мм:сс с последующими AM или PM)
%S
Секунды (00..59)
%s
Секунды (00..59)
%Т
Время, 24 часа ( чч:мм:сс )
%U
Неделя (00.
.53), где воскресенье — первый день недели; НЕДЕЛЯ() режим 0
%
Неделя (00..53), где понедельник — первый день недели; НЕДЕЛЯ() режим 1
%В
Неделя (01..53), где воскресенье — первый день недели; НЕДЕЛЯ() режим 2; используется с %X
%v
Неделя (01..53), где понедельник — первый день недели; НЕДЕЛЯ() режим 3; используется с %x
%W
Название дня недели (воскресенье..суббота)
%w
День недели (0=воскресенье..6=суббота)
%Х
Год недели, где воскресенье — первый день недели, числовой, четыре цифры; используется с %V
%х
Год недели, где понедельник — первый день недели, числовой, четыре цифры; используется с %v
%Д
Год, числовое, четыре цифры
%г
Год, цифра (две цифры)
%%
Буквенный % символ
% х
x , для любых « x », не перечисленных выше
Пример:
SELECT DATEDIFF('2018-01-01', '2019-01-01')
AS 'Разница дат';
Результаты:
Функции даты и времени MySQL (полный список)
Перейти к содержимому
Опубликовано Ян
Ниже приведен список функций даты и времени, доступных в MySQL. Нажмите на название каждой функции, чтобы просмотреть объяснение функции, ее синтаксис и примеры.
| Имя | Описание |
|---|---|
ADDDATE () | Добавляет значения времени (интервалы) к значению даты. |
ADDTIME() | Добавляет время к значению времени. |
CONVERT_TZ() | Преобразование из одного часового пояса в другой. |
CURDATE() | Возвращает текущую дату. |
CURRENT_DATE() CURRENT_DATE | Синонимы для CURDATE() . |
CURRENT_TIME() CURRENT_TIME | Синонимы для CURTIME() . |
CURRENT_TIMESTAMP() CURRENT_TIMESTAMP | Синонимы для СЕЙЧАС() . |
CURTIME() | Возвращает текущее время. |
DATE() | Извлекает дату из выражения даты и времени . |
DATE_ADD() | Добавляет значения времени (интервалы) к значению даты. |
DATE_FORMAT() | Форматирует дату как указано. |
DATE_SUB() | Вычитает значение времени (интервал) из даты. |
DATEDIFF() | Вычитает две даты. |
ДЕНЬ() | Синоним для ДЕНЬМЕСЯЦА() . |
DAYNAME() | Возвращает название дня недели. |
ДЕНЬМЕСЯЦ() | Возвращает день месяца (0-31). |
DAYOFWEEK() | Возвращает индекс дня недели аргумента. |
ДЕНЬГОД() | Возвращает день года (1-366). |
EXTRACT() | Извлекает часть даты. |
FROM_DAYS() | Преобразует номер дня в дату. |
FROM_UNIXTIME() | Форматирует временную метку Unix как дату. |
GET_FORMAT() | Возвращает строку формата даты. |
HOUR() | Извлекает час из заданного выражения time или datetime . |
LAST_DAY() | Возвращает последний день месяца из указанной даты. |
МЕСТНОЕ ВРЕМЯ() МЕСТНОЕ ВРЕМЯ | Синоним для СЕЙЧАС() . |
LOCALTIMESTAMP() LOCALTIMESTAMP | Синоним для NOW() . |
MAKEDATE() | Создает дату из года и дня года. |
MAKETIME() | Создает время из различных частей времени (т.е. часов, минут, секунд). |
МИКРОСЕКУНДЫ() | Возвращает микросекунды из аргумента. |
МИНУТА() | Возвращает минуты из аргумента. |
МЕСЯЦ() | Возвращает месяц из переданной даты. |
MONTHNAME() | Возвращает название месяца. |
СЕЙЧАС() | Возвращает текущую дату и время. |
PERIOD_ADD() | Добавляет период к году-месяцу. |
PERIOD_DIFF() | Возвращает количество месяцев между периодами. |
КВАРТАЛ() | Возвращает квартал из аргумента даты. |
SEC_TO_TIME() | Преобразует секунды в формат чч:мм:сс . |
SECOND() | Возвращает часть секунд (0-59) из своего аргумента. |
STR_TO_DATE() | Преобразует строку в дату. |
SUBDATE() | Синоним функции DATE_SUB() при вызове с тремя аргументами. |
SUBTIME() | Вычитает значение времени из другого значения времени. |
SYSDATE() | Возвращает время выполнения функции. |
TIME() | Извлекает временную часть переданного выражения. |
TIME_FORMAT() | Форматирует значение времени в соответствии с заданной строкой формата. |
TIME_TO_SEC() | Возвращает аргумент, преобразованный в секунды. |
TIMEDIFF() | Возвращает разницу между двумя значениями времени или даты и времени . Вычитает второй раз из первого. |
TIMESTAMP() | С одним аргументом эта функция возвращает дату или дата-время выражение; с двумя аргументами, сумма аргументов. |
TIMESTAMPADD() | Добавляет интервал к выражению datetime . |
TIMESTAMPDIFF() | Вычитает интервал из выражения datetime . |
TO_DAYS() | Возвращает аргумент даты, преобразованный в дни. |
TO_SECONDS() | Возвращает дату или аргумент datetime , преобразованный в секунды, начиная с 0 года. |
UNIX_TIMESTAMP() | Возвращает метку времени Unix на основе заданного значения даты/времени или текущей даты/времени. |
UTC_DATE() | Возвращает текущую дату в формате UTC. |
UTC_TIME() | Возвращает текущее время в формате UTC. |