Материал: Using_MySql,_MS_SQL_Server_and_Oracle

Пример 51: вычисление дат

57<= @day number

58SET @week_number = @week_number + 1;

60RETURN CONCAT('W', @week number, 'D', @day number ; -- W3D4

61END;

Проверить работоспособность полученной функции можно выполнением следующего кода:

MS SQL і Решение 7.4.1.b (проверка работоспособности) і

1SELECT [зЬЧаТ,

2[sb_start] ,

dbo GetWeekAndDayi[sb_start]) AS [DW] 4 FROM [subscriptions];

Код функции для Oracle выглядит следующим образом:

16real_iso_week_of_month_start NUMBER(3);

17-- ISO-номер в году недели, к которой относится первое число месяца,

21real_iso_week_of_this_date NUMBER 3 ;

22-- ISO-номер в году недели, к которой относится анализируемаядата (42),

23-- для первых чисел января будет 0, а не 52 или 53, как это

24-- вычисляет Oracle

25BEGIN

26day_number := date_var - NEXT_DAY date_var - 8, 'MON') + 1;

27-- day_number := 2016.10.20 - NEXT_DAY(2016.10.20 - 8, 'MON') + 1 =

33— first_dom_wn := TO_NUMBER(TO_CHAR(TRUNC(2016.10.20, 'mm'), 'IW')) =

34— TO_NUMBER(TO_CHAR(2016.10.01, 'IW')) = 39

35

36current_doy := TO_NUMBER(TO_CHAR(date_var, 'DDD')); — 294

37first_dom_doy := TO_NUMBER(TO_CHAR(TRUNC date_var, 'mm'), 'DDD'));

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 566/545

52week number := real iso week of this date - real iso week of month start

53-- (3)

57THEN week number := week number + 1;

58END IF;

59

60RETURN 'W' || week number || 'D' || day number;

61END;

Проверить работоспособность полученной функции можно выполнением следующего кода:

На этом решение данной задачи завершено.

Задание 7.4.1.TSK.A: переписать решение{528} задачи 7.4.1.a{528} таким образом, чтобы запрос возвращал номер недели и номер дня недели в виде одной строки (например, W5D1) — аналогично тому, как результат возвращает функция, полученная в решении{532} задачи 7.4.1.b{528}.

Задание 7.4.1.TSK.B: переписать решение{528} задачи 7.4.1.a{528} для MS SQL Server и Oracle без использования общего табличного выражения.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 567/545

Пример 52: получение комбинации неповторяющихся идентификаторов

7.4.2.Пример 52: получение комбинации неповторяющихся идентификаторов

□Задача 7.4.2.a{536}: написать запрос, формирующий пару гарантированно неповторяющихся идентификаторов читателей.

О Задача 7.4.2.b{538}: оформить решение задачи 7.4.2.а{536} с использованием общих табличных выражений.

Ожидаемый результат 7.4.2.a.

После выполнения запроса должна быть сформирована пара идентификаторов читателей, значения которых гарантированно не должны дублироваться (при условии, что в таблице subscribers есть хотя бы две записи), например:

id_first id_second

3 1

Ожидаемый результат 7.4.2.b.

Поскольку само решение и является ожидаемым результатом, см. решение

7.4.2.b{538}.

ч Р Решение 7.4.2.a{536}.

Предположим, что у нас нет никакой информации об идентификаторах читателей (минимальное значение, максимальное значение, общее количество, непрерывность последовательности и т.д.), кроме того факта, что в таблице subscribers есть как минимум две записи (в противном случае задача не имеет решения).

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

Поясним эту идею на примере. Допустим, что мы случайных образом выбрали два набора идентификаторов читателей, причём нам «не повезло», и в обоих наборах есть одно и то же значение.

Декартово проиведение таких наборов даст следующий результат:

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

Работа с MySQL, MS SQL Server и Oracle в примерах ©Богдан Марчук Стр: 568/545

Пример 52: получение комбинации неповторяющихся идентификаторов

Эта логика может быть нарушена в том и только в том случае, если в первом наборе оба значения идентификаторов совпадут, но мы можем не опасаться этого варианта развития событий, т.к. будем строить этот набор на основе значений первичного ключа, которые являются уникальными по определению.

Итак, решение данной задачи для MySQL выглядит следующим образом.

MySQL і Решение 7.4.2.a

1SELECT 'id first'

2'id second'

7CROSS JOIN

8(SELECT 's id' AS 'id second'

9FROM 'subscribers'

10ORDER BY RAND()

11LIMIT 1) AS 'set2'

12WHERE 'id first' != 'id second'

13LIMIT 1

Встроках 3-6 мы получаем пару значений для первого набора, в строках 811 получаем одно значение для второго набора, а оператор CROSS JOIN в строке 7

обеспечивает нам получение декартового произведения наборов. Условие в строке 12 гарантирует выполнение условия задачи (значения идентификаторов не должны совпадать).

Решение для MS SQL Server отличается лишь способом указания колчества возвращаемых строк (TOP вместо LIMIT) и способом упорядочивания по случайному числу (т.к. в MS SQL Server функция RAND будет возвращать одно и то же число для всех вызовов в рамках одного запроса).

Решение для Oracle также отличается лишь способом указания количества возвращаемых строк и (подзапрос с ROW_NUMBER) и способом упорядочивания по случайному числу (DBMS_RANDOM.RANDOM).

Решенеие получается таким «синтаксически перегруженным» потому, что Oracle начинает поддерживать синтаксис OFFSET ... FETCH только с 12-й версии, а мы работаем с 11-й.

Работа с MySQL, MS SQL Server и Oracle в примерах ©Богдан Марчук Стр: 569/545