лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас

• Название: Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
• Анкор: лекция
• Дата: 21.12.2022
• Размер: 7.88 Mb.
• Имя файла: Moiseenko_SQL.pdf
• Тип: Учебник; #857633
• страница: 44 из 47

Enabling the transformation
Star transformation is controlled by the star_transformation_enabled database initialization parameter. The parameter takes 3 values:
TRUE
The Oracle Optimizer performs transformation by identifying fact and constraint dimension tables automatically. This is done in a cost-based manner, i.e. the transformation is performed only if the cost of the transformed plan is lower than the non- transformed plan. Also the optimizer will attempt temporary table transformation automatically whenever materialization improves performance.
FALSE
The transformation is not tried.
TEMP_DISABLE
This value has similar behavior as TRUE except that temporary table transformation is not tried.
The default value of the parameter is FALSE. You have to change the parameter value and create indexes on the joining columns of the fact table to take advantage of this transformation.
Summary
Star transformation improves the performance of queries with a very big fact table joined to multiple dimension tables when the dimension tables have very selective predicates. The transformation avoids the full scan of the fact table. It fetches only relevant rows from the fact table that will eventually join to the constraint dimension rows. The transformation is performed based on cost - only when the cost of the transformed plan is lower than that of the non-transformed plan. If the dimension filters do not significantly reduce the amount of data to be retrieved from the fact table, then a full table scan is more efficient.

In this post we have tried to illustrate the basic ideas behind star transformation by showing simple example queries and plans. Oracle can do star transformation in more complex cases. For example, a query with multiple fact tables, snowflakes (dimension is a join of several normalized tables instead of denormalized single table), etc.
MySQL.
Использование
переменных в
запросе
Довольно часто спрашивают, есть ли аналоги аналитических (
оконных
) функций в MySQL. Нет.
Для их замены часто используют запросы с самосоединением, сложные подзапросы и прочее.
Большинство таких решений оказываются неэффективными с точки зрения производительности.
Также в MySQL нет рекурсии
. Однако с некоторой частью задач, которые обычно решаются аналитическими функциями или рекурсией, можно справиться и средствами MySQL.
Одним из этих средств является уникальный, нехарактерный для прочих СУБД механизм работы с переменными внутри запроса SQL. Мы можем объявить переменную внутри запроса, менять ей значение и подставлять в SELECT для вывода.
Причем порядок обработки строк в запросе и, как следствие, порядок присвоения значений переменным можно задать в пользовательской сортировке!
Предупреждение. В статье подразумевается, что обработка выражений в предложении SELECT осуществляется слева направо, однако официального подтверждения такого порядка обработки в документации MySQL нет. Это необходимо иметь в виду при смене версии сервера.

Для гарантии последовательности вычисления можно использовать фиктивный оператор
CASE
или IF.
Аналог
рекурсии
Рассмотрим простой пример, который генерирует последовательность
Фибоначчи
(в последовательности
Фибоначчи каждый член равен сумме двух предыдущих, а первые 2 равны единице):
1.
SELECT
IF
(
X=
1
, Fn_1, Fn_2
)
F
2.
FROM
(
3.
SELECT
@I := @I + @J Fn_1, @J := @I + @J Fn_2 4.
FROM
5.
(
SELECT
0
dummy
UNION
ALL
SELECT
0
UNION
ALL
SELECT
0
)
a,
6.
(
SELECT
0
dummy
UNION
ALL
SELECT
0
UNION
ALL
SELECT
0
)
b,
7.
(
SELECT
@I :=
1
, @J :=
1
)
IJ
8.
)
T,
9.
/*Фиктивная таблица, для вывода последовательности в 1
столбец*/
10.
(
SELECT
1
X
UNION
ALL
SELECT
2
)
X;
Данный запрос генерирует 18 чисел Фибоначчи, не считая первых двух:
F
2
3
5

8
13
21
34
55
89
144
233
377
610
987
1597
2584
4181
6765
Разберём теперь как оно работает.
В строчках 5) 6) генерируется 9 записей. Тут ничего необычного.
В строчке 7) мы объявляем две переменные @I, @J и присваиваем им 1.
В строке 3) происходит следующее: сначала переменной @I присваивается сумма двух переменных. Затем то же самое присваиваем переменной @J, причем с учетом того, что значение @I уже поменялось.

Другими словами, вычисления в SELECT выполняются слева направо – см. также замечание в начале статьи.
Причем изменение переменных осуществляется в каждой из наших 9 записей, т.е. при обработке каждой новой строки в переменных @I и @J будут содержаться значения, вычисленные при обработке предыдущей строки.
Чтобы решить эту же задачу средствами других СУБД, нам пришлось бы писать рекурсивный запрос
!
Примечание:
Переменные нужно объявлять в отдельном подзапросе (строка
7), если бы мы объявили переменную в предложении SELECT, она,
скорее всего, вычислилась бы только 1 раз (хотя конкретное
поведение будет зависеть от версии сервера). Тип переменной
определяется значением, которым она инициализирована. Этот
тип может динамически меняться. Если переменной присвоить
NULL, её типом будет BLOB.
Порядок обработки строк в SELECT, как было сказано выше, зависит от пользовательской сортировки. Простой пример нумерации строк в заданном порядке:
1.
SELECT
val, @I:=@I
+1
Num
2.
FROM
3.
(
SELECT
30
val
UNION
ALL
SELECT
20
UNION
ALL
SELECT
10
UNION
ALL
SELECT
50
)
a,
4.
(
SELECT
@I :=
0
)
I
5.
ORDER
BY
val;
Val
Num
10
1
20
2
30
3
50
4

Аналоги
аналитических
функций
Переменные также можно использовать для замены аналитических функций. Далее несколько примеров. Для простоты будем считать, что все поля
NOT
NULL, а сортировка и партиционирование (
PARTITION BY
) происходят по одному полю. Использование NULL значений и более сложных сортировок сделает примеры более громоздкими, но суть не поменяет.
Для примеров создадим таблицу TestTable:
1.
CREATE
TABLE
TestTable
(
2. group_id INT
NOT
NULL
,
3. order_id INT
UNIQUE
NOT
NULL
,
4. value INT
NOT
NULL
5.
)
; где
group_id – идентификатор группы (аналог окна аналитической функции);
order_id – уникальное поле, по которому будет производиться сортировка;
value – некоторое числовое значение.
Заполним нашу таблицу тестовыми данными:
1.
INSERT
TestTable
(
order_id, group_id, value
)
2.
SELECT
*
3.
FROM
(
4.
SELECT
1
order_id,
1
group_id,
1
value
5.
UNION
ALL
SELECT
2
,
1
,
2 6.
UNION
ALL
SELECT
3
,
1
,
2 7.
UNION
ALL
SELECT
4
,
2
,
1 8.
UNION
ALL
SELECT
5
,
2
,
2

9.
UNION
ALL
SELECT
6
,
2
,
3 10.
UNION
ALL
SELECT
7
,
3
,
1 11.
UNION
ALL
SELECT
8
,
3
,
2 12.
UNION
ALL
SELECT
9
,
4
,
1 13.
UNION
ALL
SELECT
11
,
3
,
2 14.
)
T;
Примеры замены некоторых аналитических функций.
1) ROW_NUMBER() OVER(ORDER BY order_id)
1.
SELECT
T.*, @I:=@I
+1
RowNum
2.
FROM
TestTable T,
(
SELECT
@I:=
0
)
I
3.
ORDER
BY
order_id;
group_id
order_id
value
RowNum
1
1 1
1
1
2 2
2
1
3 2
3
2
4 1
4
2
5 2
5
2
6 3
6
3
7 1
7
3
8 2
8
4
9 1
9
3
11 2
10

2) ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY order_id)
1.
SELECT
group_id, order_id, value, RowNum
2.
FROM
(
3.
SELECT
T.*,
4.
IF
(
@last_group_id = group_id, @I:=@I
+1
, @I:=
1
)
RowNum,
5. @last_group_id := group_id
6.
FROM
TestTable T,
(
SELECT
@last_group_id:=
NULL
, @I:=
0
)
I
7.
ORDER
BY
group_id, order_id
8.
)
T;
group_id
order_id
value
RowNum
1
1 1
1
1
2 2
2
1
3 2
3
2
4 1
1
2
5 2
2
2
6 3
3
3
7 1
1
3
8 2
2
3
11 2
3
4
9 1
1 3) SUM(value) OVER(PARTITION BY group_id ORDER BY order_id)
1.
SELECT
group_id, order_id, value, RunningTotal
2.
FROM
(
3.
SELECT
T.*,

4.
IF
(
@last_group_id
= group_id,
@I:=@I+value,
@I:=value
)
RunningTotal,
5. @last_group_id := group_id
6.
FROM
TestTable T,
(
SELECT
@last_group_id:=
NULL
, @I:=
0
)
I
7.
ORDER
BY
group_id, order_id
8.
)
T;
group_id
order_id
value
RunningTotal
1
1 1
1
1
2 2
3
1
3 2
5
2
4 1
1
2
5 2
3
2
6 3
6
3
7 1
1
3
8 2
3
3
11 2
5
4
9 1
1 4) LAG(value) OVER(PARTITION BY group_id ORDER BY order_id)
1.
SELECT
group_id, order_id, value, LAG
2.
FROM
(
3.
SELECT
T.*,
4.
IF
(
@last_group_id = group_id, @last_value,
NULL
)
LAG,
5. @last_group_id := group_id,
6. @last_value := value
7.
FROM
TestTable
T,
(
SELECT
@last_value:=
NULL
,
@last_group_id:=
NULL
)
I

8.
ORDER
BY
group_id, order_id
9.
)
T;
group_id
order_id
value
LAG
1
1 1
NULL
1
2 2
1
1
3 2
2
2
4 1
NULL
2
5 2
1
2
6 3
2
3
7 1
NULL
3
8 2
1
3
11 2
2
4
9 1
NULL
Для
LEAD
всё то же самое, только нужно сменить сортировку на ORDER BY group_id, order_id DESC
Для функций COUNT, MIN, MAX всё несколько сложнее, поскольку, пока мы не проанализируем все строчки в группе(окне), мы не сможем узнать значение функции. MS SQL, например, для этих целей «спулит» окно
(временно помещает строки окна в скрытую буферную таблицу для повторного к ним обращения), в MySQL такой возможности нет. Но мы можем для каждого окна вычислить значение функции в последней строке при заданной сортировке (т.е. после анализа всего окна), а затем, отсортировав строки в окне в обратном порядке, проставить вычисленное значение по всему окну.
Таким образом, нам понадобится две сортировки. Чтобы итоговая сортировка осталась той же, что и в примерах выше, отсортируем сначала по

полям group_id ASC, order_id DESC, затем по полям group_id ASC, order_id
ASC.
5) COUNT(*) OVER(PARTITION BY group_id)
В первой сортировке мы просто нумеруем записи. Во второй всем строкам окна присваиваем максимальный номер, который и будет соответствовать количеству строк в окне.
1.
SELECT
group_id, order_id, value, Cnt
2.
FROM
(
3.
SELECT
group_id, order_id, value,
4.
IF
(
@last_group_id = group_id, @MaxRowNum, @MaxRowNum
:= RowNumDesc
)
Cnt,
5. @last_group_id := group_id
6.
FROM
(
7.
SELECT
T.*,
8.
IF
(
@last_group_id = group_id, @I:=@I
+1
, @I:=
1
)
RowNumDesc,
9. @last_group_id := group_id
10.
FROM
TestTable T,
(
SELECT
@last_group_id:=
NULL
,
@I:=
0
)
I
11.
ORDER
BY
group_id, order_id
DESC
/*первая
сортировка*/
12.
)
T,
(
SELECT
@last_group_id:=
NULL
,
@MaxRowNum:=
NULL
)
I
13.
ORDER
BY
group_id, order_id
/*вторая сортировка*/
14.
)
T;
group_id
order_id
value
Cnt
1
1 1
3
1
2 2
3
1
3 2
3
2
4 1
3
2
5 2
3

2
6 3
3
3
7 1
3
3
8 2
3
3
11 2
3
4
9 1
1
Функции MAX и MIN вычисляются по аналогии. Приведу только пример для
MAX:
6) MAX(value) OVER(PARTITION BY group_id)
1.
SELECT
group_id, order_id, value, MaxVal
2.
FROM
(
3.
SELECT
group_id, order_id, value,
4.
IF
(
@last_group_id = group_id, @MaxVal, @MaxVal :=
MaxVal
)
MaxVal,
5. @last_group_id := group_id
6.
FROM
(
7.
SELECT
T.*,
8.
IF
(
@last_group_id = group_id, GREATEST
(
@MaxVal, value
)
, @MaxVal:=value
)
MaxVal,
9. @last_group_id := group_id
10.
FROM
TestTable T,
(
SELECT
@last_group_id:=
NULL
,
@MaxVal:=
NULL
)
I
11.
ORDER
BY
group_id, order_id
DESC
12.
)
T,
(
SELECT
@last_group_id:=
NULL
, @MaxVal:=
NULL
)
I
13.
ORDER
BY
group_id, order_id
14.
)
T;
group_id
order_id
value
MaxVal
1
1 1
2
1
2 2
2

1
3 2
2
2
4 1
3
2
5 2
3
2
6 3
3
3
7 1
2
3
8 2
2
3
11 2
2
4
9 1
1 7) COUNT(DISTINCT value) OVER(PARTITION BY group_id)
Интересная вещь, которая отсутствует в MS
SQL
Server, но её можно вычислить с подзапросом, взяв MAX от
RANK
. Так же поступим и здесь. В первой сортировке вычислим RANK() OVER(PARTITION BY group_id
ORDER BY value DESC), затем во второй сортировке проставим максимальное значение всем строкам в каждом окне:
1.
SELECT
group_id, order_id, value, Cnt
2.
FROM
(
3.
SELECT
group_id, order_id, value,
4.
IF
(
@last_group_id = group_id, @Rank, @Rank := Rank
)
Cnt,
5. @last_group_id := group_id
6.
FROM
(
7.
SELECT
T.*,
8.
IF
(
@last_group_id = group_id,
9.
IF
(
@last_value = value, @Rank, @Rank:=@Rank
+1
)
10.
, @Rank:=
1
)
Rank,
11.
@last_group_id := group_id,
12.
@last_value := value
13.
FROM
TestTable T,
(
SELECT
@last_value:=
NULL
,
@last_group_id:=
NULL
, @Rank:=
0
)
I
14.
ORDER
BY
group_id, value
DESC
, order_id
DESC

15.
)
T,
(
SELECT
@last_group_id:=
NULL
, @Rank:=
NULL
)
I
16.
ORDER
BY
group_id, value, order_id
17.
)
T;
group_id
order_id
value
Cnt
1
1 1
2
1
2 2
2
1
3 2
2
2
4 1
3
2
5 2
3
2
6 3
3
3
7 1
2
3
8 2
2
3
11 2
2
4
9 1
1
Производительность
Для начала сравним по производительности нумерацию строк в запросе с помощью самосоединения и с помощью переменных:
1)
Классический
способ
с
самомоединением:

1.
SELECT
COUNT
(
*
)
N, T1.*
2.
FROM
TestTable T1 3.
JOIN
TestTable T2
ON
T1.order_id >= T2.order_id
4.
GROUP
BY
T1.order_id;
Что на 10000 записей в таблице TestTable выдаёт:
Duration / Fetch
16.084 sec / 0.016 sec
2) С использованием переменных:
1.
SELECT
@N:=@N
+1
N, T1.*
2.
FROM
TestTable T1,
(
SELECT
@N :=
0
)
M
3.
ORDER
BY
T1.order_id;
Выдаёт:
Duration / Fetch
0.016 sec / 0.015 sec
Результат говорит сам за себя. Однако надо понимать, что вычисленные с помощью переменных значения не оптимально использовать в условиях фильтрации. Сортировка и вычисление будут происходить для ВСЕХ строк, несмотря на то, что в итоге нам нужна только малая их часть.
Рассмотрим более подробно на примере такой задачи:
Вывести по 2 первые строки из таблицы TestTable для каждого
значения group_id, отсортированных по order_id.
Вот как эта задача решалась бы в СУБД с поддержкой аналитических функций:
1.
SELECT
group_id, order_id, value
2.
FROM
(
3.
SELECT
*, ROW_NUMBER
()
OVER
(
PARTITION
BY
group_id
ORDER
BY
order_id
)
RowNum

4.
FROM
TestTable
5.
)
T
6.
WHERE
RowNum <=
2
;
Поскольку СУБД «знает», как работает
ROW_NUMBER
, оптимизатору незачем нумеровать ВСЕ строки, чтобы выбрать первые две. И всё выполнится быстро (при наличии индекса по group_id, order_id, конечно).
В случае с MySQL решение с подобным алгоритмом будет выглядеть так:
1.
SELECT
group_id, order_id, value
2.
FROM
(
3.
SELECT
T.*,
4.
IF
(
@last_group_id = group_id, @I:=@I
+1
, @I:=
1
)
RowNum,
5. @last_group_id := group_id
6.
FROM
TestTable T,
(
SELECT
@last_group_id:=
NULL
, @I:=
0
)
I
7.
ORDER
BY
group_id, order_id
8.
)
T
9.
WHERE
RowNum <=
2
;
Однако оптимизатор MySQL ничего не знает о том, по каким правилам мы вычисляем поле RowNum. Ему придётся пронумеровать ВСЕ строки, и только потом отобрать нужные.
Теперь представьте, что у нас 1 миллион записей и 20 уникальных значений group_id. Т.е. чтобы выбрать 40 строк, MySQL будет вычислять значение
RowNum для миллиона строк! Красивого решения этой задачи одним запросом в MySQL нет. Но можно сначала получить список уникальных значений group_id, например, так:
1.
SELECT
DISTINCT
group_id
FROM
TestTable;
Затем средствами любого другого языка программирования сгенерировать запрос вида:

1.
SELECT
*
FROM
TestTable
WHERE
group_id=
1
ORDER
BY
order_id
LIMIT
2 2.
UNION
ALL
3.
SELECT
*
FROM
TestTable
WHERE
group_id=
2
ORDER
BY
order_id
LIMIT
2 4.
UNION
ALL
5. …
6.
SELECT
*
FROM
TestTable
WHERE
group_id=
20
ORDER
BY
order_id
LIMIT
2
;
20 лёгких запросов отработают намного быстрее, чем вычисление RowNum для миллиона строк.