Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 51: вычисление дат
1 |
2 |
3 |
4 |
5 |
6 |
7 |
^ Номер дня |
ПН |
ВТ |
СР |
ЧТ |
ПТ |
СБ |
ВС |
|
|
|
|
|
|
1 |
2 |
1 -я неделя |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
2-я неделя |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
3-я неделя |
17 |
18 |
19 |
20 |
21 |
22 |
23 |
4-я неделя |
24 |
25 |
26 |
27 |
28 |
29 |
30 |
5-я неделя |
31 |
|
|
|
|
|
|
6-я неделя |
Учитывая только что рассмотренные примечания, напишем код. Для MySQL он выглядит следующим образом:
MySQL I Решение 7.4.1.a |
1SELECT 'sb id',
2'sb start',
3CASE
4WHEN WEEKDAY('sb start' - INTERVAL DAY('sb start')-1 DAY) <=
5 |
|
WEEKDAY('sb start') |
|
|
|
|
|
|||
6 |
THEN |
WEEK('sb start', |
5 |
- |
|
|
|
|
||
7 |
|
WEEK('sb |
start' |
- INTERVAL DAY( ' |
start')-1 |
DAY, |
5 |
+ 1 |
||
8 |
ELSE |
WEEK('sb start', |
5 |
- |
|
|
|
|
||
9 |
|
WEEK('sb |
start' |
- INTERVAL DAY( ' |
start')-1 |
DAY, |
5 |
|
||
10END AS 'W' ,
11WEEKDAY('sb start') + 1 AS 'D'
12FROM 'subscriptions'
Рассмотрение начнём со строк 8-9, в которых определяется номер недели месяца. Для получения этого результата мы выполняем следующие операции (рассмотрим на примере даты 20І6.10.20).
Код |
Идея |
Результат |
WEEK('sb_start', 5) |
Получение для анализируемой даты |
42 |
|
номера недели в году, на которую |
|
|
она приходится. |
|
WEEK('sb_start' - |
Получение номера недели в году для |
39 |
INTERVAL |
первого числа месяца, на который |
|
DAY('sb_start')-1 |
приходится анализируемая дата. |
|
DAY, 5) |
Чтобы получить значение первого |
|
|
числа месяца (2016.10.01) мы из |
|
|
анализируемой даты (2016.10.20) |
|
|
вычитаем значение, на 1 меньшее, |
|
|
чем «число месяца» (20 - 19 = 1), |
|
|
анализируемой даты. |
|
WEEK('sb_start', 5) - |
Итого вся операция. Да, значение 3 |
3 |
WEEK('sb_start' - |
не является корректным с точки |
|
INTERVAL |
зрения «номер недели месяца», но |
|
DAY('sb_start')-1 |
оно корректно в контексте |
|
DAY, 5) |
«2016.10.20 — это 3-й четверг ме- |
|
|
сяца». |
|
Параметр «5» функции WEEK определяет форму возвращения результата
(номер недели в году будет 0-53, началом недели считается понедельник, подробности см. в документации).
Остаётся рассмотреть строки 3-10, в которых применяется выражение CASE
с двумя альтернативами. Условие в строках 4-5 сравнивает номера дней недели для анализируемой даты и первого числа месяца. Если номер дня первого числа
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 560/545
Пример 51: вычисление дат
месяца оказывается < номера дня анализируемой даты, «номер недели» надо увеличить на единицу. Так для 2016.10.20 дни с понедельника по пятницу будут третьим понедельником месяца, третьим вторником месяца и т.д., а суббота и воскресенье будут, соответственно, четвёртой субботой и четвёртым воскресеньем месяца.
Функция WEEKDAY в 11 -й строке запроса возвращает порядковый номер дня
недели, где нумерация идёт так: ПН = 0, ВТ = 1, СР = 2 и т.д. Чтобы получить более привычный человеку результат, где ПН = 1, ВТ = 2, СР = 3 и т.д., мы прибавляем к результату работы функции единицу.
На этом решение для MySQL завершено, переходим к MS SQL Server.
Здесь нам придётся компенсировать эффект «начала недели с воскресенья», который будет в большинстве случаев присутствовать по умолчанию. Теоретически в MS SQL Server можно выполнить команду SET DATEFIRST 1, которая объявит
понедельник началом недели, но практически во множестве источников отмечено, что это может не сработать. Также в MS SQL Server ISO-номер недели вычисляется не так, как в MySQL (первые числа года могут оказаться не нулевой неделей, а 52- й или 53-й).
Следующий код написан для случая, когда первым днём недели MS SQL Server считает воскресенье.
MS SQL і Решение 7.4.1.a |
1WITH [iso week data] AS
2(
3SELECT CASE
4 |
WHEN DATEPART(iso week, |
|
|
5 |
CONVERT(VARCHAR(6), [sb start], 112 |
+ |
'01') > |
6 |
DATEPART(dy, |
|
|
7 |
CONVERT(VARCHAR(6), [sb start], 112 |
+ |
'01') |
8 |
THEN 0 |
|
|
9 |
ELSE DATEPART(iso week, |
|
|
10 |
CONVERT(VARCHAR(6), [sb start], 112 |
+ |
'01') |
11END AS [real iso week of month start],
12CASE
13 |
WHEN DATEPART(iso week, [sb start]) > DATEPART dy [sb start]) |
|
14 |
THEN |
0 |
15 |
ELSE |
DATEPART(iso week, [sb start]) |
16END AS [real iso week of this date],
17[sb start],
18[sb id]
19FROM [subscriptions]
20)
21SELECT [sb id],
22[sb start],
23CASE
24WHEN DATEPART dw
25 |
DATEADD(day, 1, |
|
|
26 |
CONVERT(VARCHAR 6 |
, [sb start], 112 |
|
27 |
+ '01')) <= |
|
|
28 |
DATEPART dw DATEADD(day, -1 |
[sb start])) |
|
29 |
THEN [real iso week of this date] - |
|
|
30 |
[real iso week of month start] + 1 |
|
|
31ELSE [real iso week of this date] - [real iso week of month start]
32END AS [W],
33DATEPART(dw. DATEADD(day, -1 [sb start])) AS [D]
34FROM [iso week data]
Рассмотрение начнём со строк 29-31, в которых реализована та же самая идея, что и в строках 6-9 решения для MySQL: из номера в году недели, на которую приходится анализируемая дата, вычитается номер в году недели, на которую приходится первое число месяца, к которому относится анализируемая дата.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 561/545
Пример 51: вычисление дат
В строках 24-28 реализуется та же логика, что и в строках 4-5 решения для MySQL: сравниваются номера дней недели для анализируемой даты и первого числа месяца (если номер дня первого числа месяца оказывается < номера дня анализируемой даты, «номер недели» надо увеличить на единицу).
Операция CONVERT(VARCHAR(6), [sb_start], 112) позволяет получить значение года и месяца в виде шести символов, например: 2016.10.20 даст 201610. После выполнения + '01' мы получим 20161001, т.е. первое число октября. Число 112 — очередное «магическое значение» (см. подробности в документации).
Операция DATEADD(day, -1, [sb_start]), встречающаяся множество раз во всём запросе, нужна для того, чтобы обозначить понедельник как начало недели, т.к. сейчас началом недели MS SQL Server считает воскресенье, и номер дня недели получается «некорректным» (ВС = 1, СБ = 7 и т.д.).
Остаётся рассмотреть CTE в строках 1 -20: этот код нужен для того, чтобы для первых дней года MS SQL Server не вернул номер недели как 52 или 53. Для этого номер недели сравнивается с номером дня в году. Если номер недели оказывается большим, его нужно превратить в 0.
На этом решение для MS SQL Server завершено, переходим к Oracle.
Здесь нам тоже придётся компенсировать эффект «начала недели с воскресенья», который будет в большинстве случаев присутствовать по умолчанию. В Oracle можно использовать команду ALTER SESSION SET NLS_TERRITORY=RUS- sia для установки начала дня с понедельника, но мы снова будем предполагать худшее (например, переключать этот параметр нельзя) и напишем решение для ситуации, когда первым днём недели считается воскресенье.
Oracl |
і |
Решение 7.4.1.a |
| |
|
e |
|
|||
|
|
|
|
|
1 |
WITH "iso week data" AS |
|
||
2 |
( |
|
|
|
3 |
|
SELECT CASE |
|
|
4 |
|
WHEN TO NUMBER(TO CHAR(TRUNC("sb start" |
'mm'), 'IW')) > |
|
5 |
|
|
TO NUMBER(TO CHAR(TRUNC("sb start" |
'mm'), 'DDD')) |
6 |
|
THEN 0 |
|
|
7 |
|
ELSE TO NUMBER(TO CHAR(TRUNC("sb start" |
'mm'), 'IW')) |
|
8 |
|
END AS "real iso week of month start" |
|
|
9 |
|
CASE |
|
|
10 |
|
WHEN TO NUMBER(TO CHAR "sb start", 'IW')) > |
||
11 |
|
|
TO NUMBER(TO CHAR "sb start", 'DDD')) |
|
12 |
|
THEN 0 |
|
|
13 |
|
ELSE TO NUMBER(TO CHAR "sb start", 'IW')) |
|
|
14 |
|
END AS "real iso week of this date", |
|
|
15 |
|
"sb start", |
|
|
16 |
|
"sb id" |
|
|
17 |
|
FROM "subscriptions" |
|
|
18 |
) |
|
|
|
19 |
SELECT "sb id" |
"sb start", |
|
|
20 |
|
CASE |
|
|
21 |
|
WHEN (TRUNC "sb start", 'mm') - |
|
|
22 |
|
|
NEXT DAY(TRUNC "sb start", 'mm') - 7, 'MON')) |
|
23 |
|
<= ("sb start" - NEXT DAY "sb start" - 7, 'MON')) |
||
24 |
|
THEN "real iso week of this date" - |
|
|
25 |
|
|
"real iso week of month start" + 1 |
|
26 |
|
ELSE "real iso week of this date" - |
|
|
27 |
|
|
"real iso week of month start" |
|
28END AS "W"
29"sb start" - NEXT DAY "sb start" - 7, 'MON') + 1 AS "D"
30FROM "iso week data"
ВOracle (как и в MS SQL Server) ISO-номер недели вычисляется не так, как в MySQL (первые числа года могут оказаться не нулевой неделей, а 52-й или 53-й)
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 562/545
Пример 51: вычисление дат
— это также придётся компенсировать дополнительными вычислениями по аналогии с решением для MS SQL Server (CTE в строках 1-18 аналогична CTE в строках 1-20 решения для MS SQL Server за исключением способа конвертации типов данных и способа получения первого числа месяца — в Oracle для этого можно использовать функцию TRUNC).
Основная часть запроса в строках 20-30 также аналогична решению для MS SQL Server за исключением способа получения корректного номера дня недели. Рассмотрим это подробнее на примере 29-й строки.
Функция NEXT_DAY возвращает дату следующего за указанной датой указанного дня недели (например, NEXT_DAY (2016.10.20, суббота) вернёт дату
ближайшей субботы, т.е. 2016.10.22).
Чтобы получить номер дня недели, нам нужно узнать разницу в днях между анализируемой датой и предыдущим (предшествующим ей) понедельником, дату которого мы получаем так NEXT_DAY("sb_start" - 7, 'MON') (это выражение можно прочитать как «вернуть дату ближайшего понедельника относительно дня, на неделю в прошлом от анализируемого»).
Итак, номер дня недели (почти) готов: "sb_start" - NEXT_DAY ("sb_start" - 7, 'MON'). Остаётся добавить 1, чтобы нумерация не начиналась с нуля. Так получается финальное значение номера дня недели.
На этом решение данной задачи завершено.
уЦ7
Решение 7.4.1.b{528}.
Решение данной задачи построено на логике обработки дат, представленной в решении 7.4.1.a{528}, а сам код функций специально написан не оптимальным, но максимально подробным образом и прокомментирован (все поясняющие результаты получены для даты 2016.10.20).
Код функции для MySQL выглядит следующим образом:
MySQL і Решение 7.4.1.b
1DELIMITER $$
2CREATE FUNCTION GetWeekAndDay (date var DATE)
3RETURNS CHAR 4
4BEGIN
5DECLARE day number TINYINT; — номер дня недели (4)
6DECLARE week number TINYINT; — номер полной недели месяца (3)
7DECLARE current wn TINYINT; — номер в году недели,
8 |
|
— к которой относится |
9 |
|
— анализируемая дата (42) |
10 |
DECLARE |
first_dom_wn TINYINT; — номер в году недели, к которой относится |
11 |
|
— первый день месяца, к которому относится |
12 |
|
— анализируемая дата (39) |
13 |
DECLARE |
current dom TINYINT; — номер дня в месяце (20) |
14 |
SET day |
number = WEEKDAY date var + 1 -- нумерация идёт с 0, |
15 |
|
-- потому нужен +1 (3+1 = 4) |
16SET current dom = DAY(date var ; -- (20)
17SET current wn = WEEK(date var 5 ; -- про "5" см. документацию (42)
18SET first dom wn = WEEK date var - INTERVAL current dom 1 DAY, 5 ; — (39)
19SET week number = current wn - first dom wn; -- (3)
20
21IF WEEKDAY(date var - INTERVAL current dom 1 DAY) <= WEEKDAY(date var
22THEN
23SET week number = week number + 1;
24END IF;
25RETURN CONCAT('W', week number 'D', day number); — W3D4
26END;
27$$
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 563/545
Пример 51: вычисление дат
28 DELIMITER ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 564/545