Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

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

Oracle Решение 7.4.1.b

40-- Актуально, например, для 2017.01.01

41IF (first_dom_wn > first_dom_doy)

42THEN real_iso_week_of_month_start := 0;

43ELSE real_iso_week_of_month_start := first_dom_wn;

44END IF;

45

46-- Актуально, например, для 2017.01.02

47IF (current_wn > current_doy)

48THEN real_iso_week_of_this_date := 0;

49ELSE real_iso_week_of_this_date := current_wn;

50END IF;

51

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;

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

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

1SELECT "sb_id",

2"sb_start",

3GetWeekAndDay("sb_start") AS "DW"

4FROM "subscriptions";

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

Задание 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 Стр: 535/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 есть хотя бы две записи), например:

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

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

7.4.2.b{538}.

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

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

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

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

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

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

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

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

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

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

MySQL Решение 7.4.2.a

1SELECT `id_first`,

2`id_second`

7CROSS JOIN

8(SELECT `s_id` AS `id_second`

9 FROM `subscribers`

10ORDER BY RAND()

11LIMIT 1) AS `set2`

12WHERE `id_first` != `id_second`

13LIMIT 1

Встроках 3-6 мы получаем пару значений для первого набора, в строках 8- 11 получаем одно значение для второго набора, а оператор 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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 537/545

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

Oracle Решение 7.4.2.a

1SELECT "id_first",

2"id_second"

19WHERE "id_first" != "id_second") "prepared_data"

20WHERE "rn" = 1

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

Решение 7.4.2.b{536}.

Внимание! Решение данной задачи приведено как пример того, что делать нельзя. Далее будет объяснено, почему именно это нельзя делать.

Поскольку MySQL не поддерживает общие табличные выражения, для данной СУБД решение задачи объективно не существует, и мы сразу переходим к MS SQL Server и Oracle (решения для обеих СУБД одинаковы, отличия – лишь в способе упорядочивания и получения первой записи, но эти особенности уже рассмотрены в решении{536} задачи 7.4.2.a{536}).

Сначала рассмотрим код (напоминание: это неправильный код) для обеих

СУБД.

MS SQL Решение 7.4.2.b (неправильный код)

1WITH [first_source]

2AS (SELECT TOP 1 [s_id]

5[second_source]

6AS (SELECT TOP 1 [s_id]

10ORDER BY NEWID())

11SELECT [first_source].[s_id] AS [id_first],

12[second_source].[s_id] AS [id_second]

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