Версия: 2.0.x

SELECT

SELECT запрашивает данные из одной или нескольких таблиц, views или materialized views. Оператор SELECT обычно состоит из следующих частей:

WITH
WHERE и операторы
GROUP BY
HAVING
UNION
INTERSECT
EXCEPT/MINUS
ORDER BY
LIMIT
OFFSET
Joins
Subqueries
DISTINCT
Alias

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

Синтаксис запросов Selena в основном соответствует стандарту SQL92. Вот краткое описание поддерживаемого использования SELECT.

NOTE

Для запроса данных из таблиц, views или materialized views во внутренней таблице Selena вы должны иметь привилегию SELECT на эти объекты. Для запроса данных из таблиц, views или materialized views во внешнем источнике данных вы должны иметь привилегию USAGE на соответствующий external catalog.

WITH

Часть, которая может быть добавлена перед оператором SELECT для определения псевдонима для сложного выражения, на которое ссылаются несколько раз внутри SELECT.

Аналогично CRATE VIEW, но имена таблиц и столбцов, определенные в части, не сохраняются после завершения запроса и не конфликтуют с именами в реальной таблице или VIEW.

Преимущества использования части WITH:

Удобно и легко поддерживать, сокращает дублирование внутри запросов.

Упрощает чтение и понимание SQL кода, абстрагируя наиболее сложные части запроса в отдельные блоки.

Примеры:

-- Define one subquery at the outer level, and another at the inner level as part of the
-- initial stage of the UNION ALL query.

with t1 as (select 1),t2 as (select 2)
select * from t1 union all select * from t2;

Join

Join операции объединяют данные из двух или более таблиц и затем возвращают набор результатов из некоторых столбцов из некоторых из них.

Selena поддерживает self joins, cross joins, inner joins, outer joins, semi joins и anti joins. Outer joins включают left joins, right joins и full joins.

Синтаксис:

SELECT select_list FROM
table_or_subquery1 [INNER] JOIN table_or_subquery2 |
table_or_subquery1 {LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]} JOIN table_or_subquery2 |
table_or_subquery1 {LEFT | RIGHT} SEMI JOIN table_or_subquery2 |
table_or_subquery1 {LEFT | RIGHT} ANTI JOIN table_or_subquery2 |
[ ON col1 = col2 [AND col3 = col4 ...] |
USING (col1 [, col2 ...]) ]
[other_join_clause ...]
[ WHERE where_clauses ]

SELECT select_list FROM
table_or_subquery1, table_or_subquery2 [, table_or_subquery3 ...]
[other_join_clause ...]
WHERE
col1 = col2 [AND col3 = col4 ...]

SELECT select_list FROM
table_or_subquery1 CROSS JOIN table_or_subquery2
[other_join_clause ...]
[ WHERE where_clauses ]

Self Join

Selena поддерживает self-joins, которые являются самосоединениями. Например, разные столбцы одной и той же таблицы соединяются.

На самом деле нет специального синтаксиса для идентификации self-join. Условия с обеих сторон join в self-join исходят из одной и той же таблицы.

Нам нужно назначить им разные псевдонимы.

Примеры:

SELECT lhs.id, rhs.parent, lhs.c1, rhs.c2 FROM tree_data lhs, tree_data rhs WHERE lhs.id = rhs.parent;

Cross Join

Cross join может производить много результатов, поэтому cross join следует использовать с осторожностью.

Даже если вам нужно использовать cross join, необходимо использовать условия фильтрации и убедиться, что возвращается меньше результатов. Пример:

SELECT * FROM t1, t2;

SELECT * FROM t1 CROSS JOIN t2;

Inner Join

Inner join — это наиболее известный и часто используемый join. Возвращает результаты из столбцов, запрошенных из двух похожих таблиц, соединенных, если столбцы обеих таблиц содержат одинаковое значение.

Если имя столбца обеих таблиц совпадает, нам нужно использовать полное имя (в форме table_name.column_name) или использовать псевдоним для имени столбца.

Примеры:

Следующие три запроса эквивалентны.

SELECT t1.id, c1, c2 FROM t1, t2 WHERE t1.id = t2.id;

SELECT t1.id, c1, c2 FROM t1 JOIN t2 ON t1.id = t2.id;

SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;

Outer Join

Outer join возвращает левую или правую таблицу или все строки обеих. Если нет соответствующих данных в другой таблице, устанавливается NULL. Пример:

SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id;

SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id;

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;

Эквивалентные и неэквивалентные join

Обычно пользователи используют чаще всего эквивалентные join, которые требуют, чтобы оператор условия join был знаком равенства.

Неэквивалентные join можно использовать с условиями join !=. Неэквивалентные join производят большое количество результатов и могут превысить лимит памяти во время вычисления.

Используйте с осторожностью. Неэквивалентные join поддерживают только inner join. Пример:

SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;

SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id > t2.id;

Semi Join

Left semi join возвращает только те строки в левой таблице, которые соответствуют данным в правой таблице, независимо от того, сколько строк соответствует данным в правой таблице.

Эта строка левой таблицы возвращается не более одного раза. Right semi join работает аналогично, за исключением того, что возвращаемые данные являются правой таблицей.

Примеры:

SELECT t1.c1, t1.c2, t1.c2 FROM t1 LEFT SEMI JOIN t2 ON t1.id = t2.id;

Anti Join

Left anti join возвращает только строки из левой таблицы, которые не соответствуют правой таблице.

Right anti join меняет это сравнение, возвращая только строки из правой таблицы, которые не соответствуют левой таблице. Пример:

SELECT t1.c1, t1.c2, t1.c2 FROM t1 LEFT ANTI JOIN t2 ON t1.id = t2.id;

Equi-join и Non-equi-join

Различные joins, поддерживаемые Selena, могут быть классифицированы как equi-joins и non-equi-joins в зависимости от условий join, указанных в joins.

Equi-joins	Self joins, cross joins, inner joins, outer joins, semi joins и anti joins
Non-equi-joins	cross joins, inner joins, left semi joins, left anti joins и outer joins

Equi-joins

Equi-join использует условие join, в котором два элемента join объединяются оператором =. Пример: a JOIN b ON a.id = b.id.
Non-equi-joins

Non-equi-join использует условие join, в котором два элемента join объединяются оператором сравнения, таким как <, <=, >, >= или <>. Пример: a JOIN b ON a.id < b.id. Non-equi-joins работают медленнее, чем equi-joins. Мы рекомендуем проявлять осторожность при использовании non-equi-joins.

Следующие два примера показывают, как выполнять non-equi-joins:
```
SELECT t1.id, c1, c2
FROM t1
INNER JOIN t2 ON t1.id < t2.id;

SELECT t1.id, c1, c2
FROM t1
LEFT JOIN t2 ON t1.id > t2.id;
```

Join с частью USING

Начиная с v2.0.0, Selena поддерживает указание условий join через часть USING в дополнение к ON. Это помогает упростить equi-joins со столбцами одинакового имени. Например: SELECT * FROM t1 JOIN t2 USING (id).

Различия между версиями:

Версии до v2.0.0

USING рассматривается как синтаксический сахар и внутренне преобразуется в условие ON. Результат будет включать столбцы USING из обеих левой и правой таблиц как отдельные столбцы, и квалификаторы псевдонимов таблиц (например, t1.id) были разрешены при ссылке на столбцы USING.

Пример:
```
SELECT t1.id, t2.id FROM t1 JOIN t2 USING (id);  -- Returns two separate id columns
```
v2.0.0 и позже

Selena реализует семантику USING в соответствии со стандартом SQL. Ключевые особенности включают:
- Поддерживаются все типы join, включая FULL OUTER JOIN.
- Столбцы USING появляются как один объединенный столбец в результатах. Для FULL OUTER JOIN используется семантика COALESCE(left.col, right.col).
- Квалификаторы псевдонимов таблиц (например, t1.id) больше не поддерживаются для столбцов USING. Необходимо использовать неквалифицированные имена столбцов (например, id).
- Для результата SELECT * порядок столбцов: [USING columns, left non-USING columns, right non-USING columns].
Пример:
```
SELECT t1.id FROM t1 JOIN t2 USING (id);        -- ❌ Error: Column 'id' is ambiguous
SELECT id FROM t1 JOIN t2 USING (id);           -- ✅ Correct: Returns a single coalesced 'id' column
SELECT * FROM t1 FULL OUTER JOIN t2 USING (id); -- ✅ FULL OUTER JOIN is supported
```

Эти изменения приводят поведение Selena в соответствие с базами данных, совместимыми со стандартом SQL.

ASOF Join

ASOF Join — это тип временного или основанного на диапазоне join, обычно используемого в аналитике временных рядов. Он позволяет объединять две таблицы на основе равенства определенных ключей и условия неравенства по времени или полям последовательности, например, t1.time >= t2.time. ASOF Join выбирает наиболее недавнюю соответствующую строку из правой таблицы для каждой строки в левой таблице. Поддерживается начиная с v2.0.0.

В реальных сценариях аналитика, связанная с данными временных рядов, часто сталкивается со следующими проблемами:

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

Традиционные equi joins (INNER Join) часто приводят к значительной потере данных при обработке таких данных, в то время как inequality joins могут привести к проблемам производительности. ASOF Join был разработан для решения этих конкретных проблем.

ASOF Joins обычно используются в следующих случаях:

Анализ финансового рынка
- Сопоставление цен акций с объемами торговли
- Согласование данных с разных рынков
- Сопоставление эталонных данных для ценообразования производных инструментов
Обработка данных IoT
- Согласование потоков данных нескольких датчиков
- Корреляция изменений состояния устройств
- Интерполяция данных временных рядов
Анализ логов
- Корреляция системных событий с действиями пользователей
- Сопоставление логов различных сервисов
- Анализ неисправностей и отслеживание проблем

Синтаксис:

SELECT [select_list]
FROM left_table [AS left_alias]
ASOF LEFT JOIN right_table [AS right_alias]
    ON equality_condition
    AND asof_condition
[WHERE ...]
[ORDER BY ...]

ASOF LEFT JOIN: Выполняет неэквивалентный join на основе ближайшего соответствия по времени или последовательности. ASOF LEFT JOIN возвращает все строки из левой таблицы, заполняя несоответствующие строки правой таблицы значением NULL.
equality_condition: Стандартное условие равенства (например, сопоставление символов ticker или идентификаторов).
asof_condition: Условие диапазона, обычно записываемое как left.time >= right.time, указывающее на поиск наиболее недавних записей right.time, не превышающих left.time.

примечание

В asof_condition поддерживаются только типы DATE и DATETIME. И поддерживается только одно asof_condition.

Пример:

SELECT *
FROM holdings h ASOF LEFT JOIN prices p
ON h.ticker = p.ticker
AND h.when >= p.when
ORDER BY ALL;

Ограничения:

В настоящее время поддерживаются только Inner Join (по умолчанию) и Left Outer Join.
В asof_condition поддерживаются только типы DATE и DATETIME.
Поддерживается только одно asof_condition.

ORDER BY

Часть ORDER BY оператора SELECT сортирует набор результатов, сравнивая значения из одного или нескольких столбцов.

ORDER BY — это трудоемкая и ресурсоемкая операция, поскольку все результаты должны быть отправлены на один узел для слияния, прежде чем результаты могут быть отсортированы. Сортировка потребляет больше ресурсов памяти, чем запрос без ORDER BY.

Поэтому, если вам нужны только первые N результатов из отсортированного набора результатов, вы можете использовать часть LIMIT, которая уменьшает использование памяти и сетевые накладные расходы. Если часть LIMIT не указана, по умолчанию возвращаются первые 65535 результатов.

Синтаксис:

ORDER BY <column_name>
    [ASC | DESC]
    [NULLS FIRST | NULLS LAST]

ASC указывает, что результаты должны быть возвращены в порядке возрастания. DESC указывает, что результаты должны быть возвращены в порядке убывания. Если порядок не указан, ASC (возрастание) является порядком по умолчанию. Пример:

select * from big_table order by tiny_column, short_column desc;

Порядок сортировки для значений NULL: NULLS FIRST указывает, что значения NULL должны быть возвращены перед значениями, отличными от NULL. NULLS LAST указывает, что значения NULL должны быть возвращены после значений, отличных от NULL.

Примеры:

select  *  from  sales_record  order by  employee_id  nulls first;

GROUP BY

Часть GROUP BY часто используется с агрегатными функциями. Столбцы, указанные в части GROUP BY, не будут участвовать в операции агрегирования.

Синтаксис

SELECT
...
aggregate_function() [ FILTER ( where boolean_expression ) ]
...
FROM ...
[ ... ]
GROUP BY [
    , ... |
    GROUPING SETS [, ...] (  groupSet [ , groupSet [ , ... ] ] ) |
    ROLLUP(expr  [ , expr [ , ... ] ]) |
    CUBE(expr  [ , expr [ , ... ] ])
    ]
[ ... ]

Параметры

FILTER может использоваться вместе с агрегатными функциями. Только отфильтрованные строки будут участвовать в вычислении агрегатной функции.
NOTE
- Часть FILTER поддерживается только в функциях AVG, COUNT, MAX, MIN, SUM, ARRAY_AGG и ARRAY_AGG_DISTINCT.
- Часть FILTER не поддерживается для COUNT DISTINCT.
- Когда указана часть FILTER, части ORDER BY не разрешены внутри функций ARRAY_AGG и ARRAY_AGG_DISTINCT.
GROUPING SETS, CUBE и ROLLUP являются расширениями части GROUP BY. В части GROUP BY они могут использоваться для достижения групповой агрегации нескольких наборов. Результаты эквивалентны результату UNION нескольких частей GROUP BY.

Примеры

Пример 1: FILTER

Следующие два запроса эквивалентны.

SELECT
  COUNT(*) AS total_users,
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_users,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_users
FROM users;

SELECT
  COUNT(*) AS total_users,
  COUNT(*) FILTER (WHERE gender = 'M') AS male_users,
  COUNT(*) FILTER (WHERE gender = 'F') AS female_users
FROM users;

Пример 2: GROUPING SETS, CUBE и ROLLUP

ROLLUP(a,b,c) эквивалентен следующей части GROUPING SETS.

GROUPING SETS (
(a,b,c),
(a,b  ),
(a    ),
(     )
)

CUBE (a, b, c) эквивалентен следующей части GROUPING SETS.

GROUPING SETS (
( a, b, c ),
( a, b    ),
( a,    c ),
( a       ),
(    b, c ),
(    b    ),
(       c ),
(         )
)

Тестирование на реальном наборе данных.

SELECT * FROM t;
+------+------+------+
| k1   | k2   | k3   |
+------+------+------+
| a    | A    |    1 |
| a    | A    |    2 |
| a    | B    |    1 |
| a    | B    |    3 |
| b    | A    |    1 |
| b    | A    |    4 |
| b    | B    |    1 |
| b    | B    |    5 |
+------+------+------+
8 rows in set (0.01 sec)

SELECT k1, k2, SUM(k3) FROM t
GROUP BY GROUPING SETS ( (k1, k2), (k2), (k1), ( ) );
+------+------+-----------+
| k1   | k2   | sum(`k3`) |
+------+------+-----------+
| b    | B    |         6 |
| a    | B    |         4 |
| a    | A    |         3 |
| b    | A    |         5 |
| NULL | B    |        10 |
| NULL | A    |         8 |
| a    | NULL |         7 |
| b    | NULL |        11 |
| NULL | NULL |        18 |
+------+------+-----------+
9 rows in set (0.06 sec)

SELECT k1, k2, GROUPING_ID(k1,k2), SUM(k3) FROM t
GROUP BY GROUPING SETS ((k1, k2), (k1), (k2), ());
+------+------+---------------+----------------+
| k1   | k2   | grouping_id(k1,k2) | sum(`k3`) |
+------+------+---------------+----------------+
| a    | A    |             0 |              3 |
| a    | B    |             0 |              4 |
| a    | NULL |             1 |              7 |
| b    | A    |             0 |              5 |
| b    | B    |             0 |              6 |
| b    | NULL |             1 |             11 |
| NULL | A    |             2 |              8 |
| NULL | B    |             2 |             10 |
| NULL | NULL |             3 |             18 |
+------+------+---------------+----------------+
9 rows in set (0.02 sec)

HAVING

Часть HAVING не фильтрует данные строк в таблице, но фильтрует результаты агрегатных функций.

Как правило, HAVING используется с агрегатными функциями (такими как COUNT(), SUM(), AVG(), MIN(), MAX()) и частями GROUP BY.

Примеры:

select tiny_column, sum(short_column)
from small_table
group by tiny_column
having sum(short_column) = 1;

+-------------+---------------------+
|tiny_column  | sum('short_column') |
+-------------+---------------------+
|         2   |        1            |
+-------------+---------------------+

1 row in set (0.07 sec)

select tiny_column, sum(short_column)
from small_table
group by tiny_column
having tiny_column > 1;

+-------------+---------------------+
|tiny_column  | sum('short_column') |
+-------------+---------------------+
|      2      |          1          |
+-------------+---------------------+

1 row in set (0.07 sec)

LIMIT

Части LIMIT используются для ограничения максимального количества возвращаемых строк. Установка максимального количества возвращаемых строк может помочь Selena оптимизировать использование памяти.

Эта часть в основном используется в следующих сценариях:

Возвращает результат запроса top-N.

Подумайте о том, что включено в таблицу ниже.

Размер набора результатов запроса необходимо ограничить из-за большого объема данных в таблице или из-за того, что часть where не фильтрует слишком много данных.

Инструкции по использованию: Значение части LIMIT должно быть числовой литеральной константой.

Примеры:

mysql> select tiny_column from small_table limit 1;

+-------------+
|tiny_column  |
+-------------+
|     1       |
+-------------+

1 row in set (0.02 sec)

mysql> select tiny_column from small_table limit 10000;

+-------------+
|tiny_column  |
+-------------+
|      1      |
|      2      |
+-------------+

2 rows in set (0.01 sec)

OFFSET

Часть OFFSET заставляет набор результатов пропустить первые несколько строк и вернуть следующие результаты напрямую.

Набор результатов по умолчанию начинается со строки 0, поэтому offset 0 и без offset возвращают одинаковые результаты.

Как правило, части OFFSET необходимо использовать с частями ORDER BY и LIMIT, чтобы быть действительными.

Примеры:

mysql> select varchar_column from big_table order by varchar_column limit 3;

+----------------+
| varchar_column |
+----------------+
|    beijing     |
|    chongqing   |
|    tianjin     |
+----------------+

3 rows in set (0.02 sec)

mysql> select varchar_column from big_table order by varchar_column limit 1 offset 0;

+----------------+
|varchar_column  |
+----------------+
|     beijing    |
+----------------+

1 row in set (0.01 sec)

mysql> select varchar_column from big_table order by varchar_column limit 1 offset 1;

+----------------+
|varchar_column  |
+----------------+
|    chongqing   |
+----------------+

1 row in set (0.01 sec)

mysql> select varchar_column from big_table order by varchar_column limit 1 offset 2;

+----------------+
|varchar_column  |
+----------------+
|     tianjin    |
+----------------+

1 row in set (0.02 sec)

Примечание: Разрешается использовать синтаксис offset без order by, но offset не имеет смысла в этом случае.

В этом случае берется только значение limit, а значение offset игнорируется. Таким образом, без order by.

Offset превышает максимальное количество строк в наборе результатов и по-прежнему является результатом. Рекомендуется пользователям использовать offset с order by.

UNION

Объединяет результаты нескольких запросов.

Синтаксис:

query_1 UNION [DISTINCT | ALL] query_2

DISTINCT (по умолчанию) возвращает только уникальные строки. UNION эквивалентен UNION DISTINCT.
ALL объединяет все строки, включая дубликаты. Поскольку дедупликация требует много памяти, запросы, использующие UNION ALL, выполняются быстрее и потребляют меньше памяти. Для лучшей производительности используйте UNION ALL.

NOTE

Каждый оператор запроса должен возвращать одинаковое количество столбцов, и столбцы должны иметь совместимые типы данных.

Примеры:

Создайте таблицы select1 и select2.

CREATE TABLE select1(
    id          INT,
    price       INT
    )
DISTRIBUTED BY HASH(id);

INSERT INTO select1 VALUES
    (1,2),
    (1,2),
    (2,3),
    (5,6),
    (5,6);

CREATE TABLE select2(
    id          INT,
    price       INT
    )
DISTRIBUTED BY HASH(id);

INSERT INTO select2 VALUES
    (2,3),
    (3,4),
    (5,6),
    (7,8);

Пример 1: Вернуть все идентификаторы в двух таблицах, включая дубликаты.

mysql> (select id from select1) union all (select id from select2) order by id;

+------+
| id   |
+------+
|    1 |
|    1 |
|    2 |
|    2 |
|    3 |
|    5 |
|    5 |
|    5 |
|    7 |
+------+
11 rows in set (0.02 sec)

Пример 2: Вернуть все уникальные идентификаторы в двух таблицах. Следующие два оператора эквивалентны.

mysql> (select id from select1) union (select id from select2) order by id;

+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    7 |
+------+
6 rows in set (0.01 sec)

mysql> (select id from select1) union distinct (select id from select2) order by id;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    7 |
+------+
5 rows in set (0.02 sec)

Пример 3: Вернуть первые три идентификатора среди всех уникальных идентификаторов в двух таблицах. Следующие два оператора эквивалентны.

mysql> (select id from select1) union distinct (select id from select2)
order by id
limit 3;
++------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
4 rows in set (0.11 sec)

mysql> select * from (select id from select1 union distinct select id from select2) as t1
order by id
limit 3;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

INTERSECT

Вычисляет пересечение результатов нескольких запросов, то есть результаты, которые появляются во всех наборах результатов. Эта часть возвращает только уникальные строки среди наборов результатов. Ключевое слово ALL не поддерживается.

Синтаксис:

query_1 INTERSECT [DISTINCT] query_2

NOTE

INTERSECT эквивалентен INTERSECT DISTINCT.

Каждый оператор запроса должен возвращать одинаковое количество столбцов, и столбцы должны иметь совместимые типы данных.

Примеры:

Используются две таблицы из UNION.

Вернуть уникальные комбинации (id, price), которые являются общими для обеих таблиц. Следующие два оператора эквивалентны.

mysql> (select id, price from select1) intersect (select id, price from select2)
order by id;

+------+-------+
| id   | price |
+------+-------+
|    2 |     3 |
|    5 |     6 |
+------+-------+

mysql> (select id, price from select1) intersect distinct (select id, price from select2)
order by id;

+------+-------+
| id   | price |
+------+-------+
|    2 |     3 |
|    5 |     6 |
+------+-------+

EXCEPT/MINUS

Возвращает уникальные результаты левого запроса, которых не существует в правом запросе. EXCEPT эквивалентен MINUS.

Синтаксис:

query_1 {EXCEPT | MINUS} [DISTINCT] query_2

NOTE

EXCEPT эквивалентен EXCEPT DISTINCT. Ключевое слово ALL не поддерживается.

Каждый оператор запроса должен возвращать одинаковое количество столбцов, и столбцы должны иметь совместимые типы данных.

Примеры:

Используются две таблицы из UNION.

Вернуть уникальные комбинации (id, price) в select1, которые не могут быть найдены в select2.

mysql> (select id, price from select1) except (select id, price from select2)
order by id;
+------+-------+
| id   | price |
+------+-------+
|    1 |     2 |
+------+-------+

mysql> (select id, price from select1) minus (select id, price from select2)
order by id;
+------+-------+
| id   | price |
+------+-------+
|    1 |     2 |
+------+-------+

DISTINCT

Ключевое слово DISTINCT дедуплицирует набор результатов. Пример:

-- Returns the unique values from one column.
select distinct tiny_column from big_table limit 2;

-- Returns the unique combinations of values from multiple columns.
select distinct tiny_column, int_column from big_table limit 2;

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

-- Counts the unique values from one column.
select count(distinct tiny_column) from small_table;

+-------------------------------+
| count(DISTINCT 'tiny_column') |
+-------------------------------+
|             2                 |
+-------------------------------+
1 row in set (0.06 sec)

-- Counts the unique combinations of values from multiple columns.
select count(distinct tiny_column, int_column) from big_table limit 2;

Selena поддерживает использование distinct в нескольких агрегатных функциях одновременно.

-- Count the unique value from multiple aggregation function separately.
select count(distinct tiny_column, int_column), count(distinct varchar_column) from big_table;

Subquery

Подзапросы классифицируются на два типа с точки зрения релевантности:

Некоррелированный подзапрос получает свои результаты независимо от своего внешнего запроса.
Коррелированный подзапрос требует значений из своего внешнего запроса.

Некоррелированный подзапрос

Некоррелированные подзапросы поддерживают [NOT] IN и EXISTS.

Пример:

SELECT x FROM t1 WHERE x [NOT] IN (SELECT y FROM t2);

SELECT * FROM t1 WHERE (x,y) [NOT] IN (SELECT x,y FROM t2 LIMIT 2);

SELECT x FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE y = 1);

Начиная с v1.5.2, вы можете указать несколько полей в части WHERE SELECT... FROM... WHERE... [NOT] IN, например, WHERE (x,y) во втором операторе SELECT.

Коррелированный подзапрос

Связанные подзапросы поддерживают [NOT] IN и [NOT] EXISTS.

Пример:

SELECT * FROM t1 WHERE x [NOT] IN (SELECT a FROM t2 WHERE t1.y = t2.b);

SELECT * FROM t1 WHERE [NOT] EXISTS (SELECT a FROM t2 WHERE t1.y = t2.b);

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

Примеры:

Некоррелированный скалярный подзапрос с предикатом = знак. Например, вывести информацию о человеке с самой высокой заработной платой.
```
SELECT name FROM table WHERE salary = (SELECT MAX(salary) FROM table);
```
Некоррелированные скалярные подзапросы с предикатами >, < и т.д. Например, вывести информацию о людях, которым платят больше среднего.
```
SELECT name FROM table WHERE salary > (SELECT AVG(salary) FROM table);
```
Связанные скалярные подзапросы. Например, вывести информацию о самой высокой зарплате для каждого отдела.
```
SELECT name FROM table a WHERE salary = (SELECT MAX(salary) FROM table b WHERE b.Department= a.Department);
```
Скалярные подзапросы используются в качестве параметров обычных функций.
```
SELECT name FROM table WHERE salary = abs((SELECT MAX(salary) FROM table));
```

Where и операторы

SQL операторы — это серия функций, используемых для сравнения и широко используемых в частях where операторов select.

Арифметический оператор

Арифметические операторы обычно появляются в выражениях, содержащих левый, правый и чаще всего левый операнды

+и-: можно использовать как единичный или как 2-арный оператор. При использовании в качестве единичного оператора, такого как +1, -2.5 или -col_name, что означает, что значение умножается на +1 или -1.

Таким образом, единичный оператор + возвращает неизменное значение, а единичный оператор - изменяет знаковые биты этого значения.

Пользователи могут наложить два единичных оператора, такие как +5 (возвращая положительное значение), -+2 или +2 (возвращая отрицательное значение), но пользователи не могут использовать два последовательных знака -.

Потому что -- интерпретируется как комментарий в следующем операторе (когда пользователь может использовать два знака, требуется пробел или скобка между двумя знаками, такими как -(-2) или - -2, что фактически приводит к +2).

Когда + или - является бинарным оператором, например, 2+2, 3+1.5 или col1+col2, это означает, что левое значение добавляется или вычитается из правого значения. Оба левое и правое значения должны быть числовыми типами.

и/: представляют умножение и деление соответственно. Операнды с обеих сторон должны быть типами данных. Когда два числа умножаются.

Меньшие операнды могут быть продвинуты при необходимости (например, SMALLINT в INT или BIGINT), и результат выражения будет продвинут до следующего большего типа.

Например, TINYINT, умноженный на INT, произведет результат типа BIGINT. Когда два числа умножаются, оба операнда и результаты выражения интерпретируются как типы DOUBLE, чтобы избежать потери точности.

Если пользователь хочет преобразовать результат выражения в другой тип, его необходимо преобразовать с помощью функции CAST.

%: Оператор модуляции. Возвращает остаток от деления левого операнда на правый операнд. Оба левый и правый операнды должны быть целыми числами.

&, |и ^: Побитовый оператор возвращает результат побитовых операций AND, побитовых OR, побитовых XOR над двумя операндами. Оба операнда требуют целочисленный тип.

Если типы двух операндов побитового оператора несовместимы, операнды меньшего типа продвигаются к операндам большего типа, и выполняются соответствующие побитовые операции.

Несколько арифметических операторов могут появляться в выражении, и пользователь может заключить соответствующее арифметическое выражение в скобки. Арифметические операторы обычно не имеют соответствующих математических функций для выражения тех же функций, что и арифметические операторы.

Например, у нас нет функции MOD() для представления оператора %. И наоборот, математические функции не имеют соответствующих арифметических операторов. Например, функция возведения в степень POW() не имеет соответствующего оператора возведения в степень **.

Пользователи могут узнать, какие арифметические функции мы поддерживаем, в разделе "Математические функции".

Оператор Between

В части where выражения могут сравниваться как с верхними, так и с нижними границами. Если выражение больше или равно нижней границе и меньше или равно верхней границе, результат сравнения истинен.

Синтаксис:

expression BETWEEN lower_bound AND upper_bound

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

Инструкции по использованию: Если операнд имеет тип string, обратите внимание, что длинная строка, начинающаяся с верхней границы, не будет соответствовать верхней границе, которая больше верхней границы. Например, "between'A'and'M' не будет соответствовать 'MJ'.

Если вам нужно убедиться, что выражение работает правильно, вы можете использовать такие функции, как upper(), lower(), substr(), trim().

Пример:

select c1 from t1 where month between 1 and 6;

Операторы сравнения

Операторы сравнения используются для сравнения двух значений. =, !=, >= применяются ко всем типам данных.

Операторы <> и != эквивалентны, что указывает на то, что два значения не равны.

Оператор In

Оператор In сравнивает коллекцию VALUE и возвращает TRUE, если он может соответствовать любому из элементов в коллекции.

Параметры и коллекции VALUE должны быть сопоставимыми. Все выражения, использующие оператор IN, могут быть записаны как эквивалентные сравнения, связанные с OR, но синтаксис IN проще, точнее и легче для оптимизации Selena.

Примеры:

select * from small_table where tiny_column in (1,2);

Оператор Like

Этот оператор используется для сравнения со строкой. '_' (подчеркивание) соответствует одному символу, '%' соответствует нескольким символам. Параметр должен соответствовать полной строке. Как правило, размещение '%' в конце строки является более практичным.

Примеры:

mysql> select varchar_column from small_table where varchar_column like 'm%';

+----------------+
|varchar_column  |
+----------------+
|     milan      |
+----------------+

1 row in set (0.02 sec)

mysql> select varchar_column from small_table where varchar_column like 'm____';

+----------------+
| varchar_column |
+----------------+
|    milan       |
+----------------+

1 row in set (0.01 sec)

Логический оператор

Логические операторы возвращают значение BOOL, включая единичные и множественные операторы, каждый из которых обрабатывает параметры, которые являются выражениями, возвращающими значения BOOL. Поддерживаемые операторы:

AND: 2-арный оператор, оператор AND возвращает TRUE, если параметры слева и справа оба вычисляются как TRUE.

OR: 2-арный оператор, который возвращает TRUE, если один из параметров слева и справа вычисляется как TRUE. Если оба параметра FALSE, оператор OR возвращает FALSE.

NOT: Единичный оператор, результат инвертирования выражения. Если параметр TRUE, оператор возвращает FALSE; Если параметр FALSE, оператор возвращает TRUE.

Примеры:

mysql> select true and true;

+-------------------+
| (TRUE) AND (TRUE) |
+-------------------+
|         1         |
+-------------------+

1 row in set (0.00 sec)

mysql> select true and false;

+--------------------+
| (TRUE) AND (FALSE) |
+--------------------+
|         0          |
+--------------------+

1 row in set (0.01 sec)

mysql> select true or false;

+-------------------+
| (TRUE) OR (FALSE) |
+-------------------+
|        1          |
+-------------------+

1 row in set (0.01 sec)

mysql> select not true;

+----------+
| NOT TRUE |
+----------+
|     0    |
+----------+

1 row in set (0.01 sec)

Оператор регулярного выражения

Определяет, соответствует ли регулярное выражение. Использование стандартных регулярных выражений POSIX, '^' соответствует первой части строки, '$' соответствует концу строки.

"." соответствует любому отдельному символу, "*" соответствует нулю или более опциям, "+" соответствует одной или более опциям, "?" означает жадное представление и так далее. Регулярные выражения должны соответствовать полным значениям, а не только частям строк.

Если вы хотите сопоставить среднюю часть, передняя часть регулярного выражения может быть записана как '^.' или '.'. '^' и '$' обычно опускаются. Оператор RLIKE и оператор REGEXP являются синонимами.

Оператор '|' является опциональным оператором. Регулярные выражения с обеих сторон '|' должны удовлетворять только одному условию стороны. Оператор '|' и регулярные выражения с обеих сторон обычно должны быть заключены в ().

Примеры:

mysql> select varchar_column from small_table where varchar_column regexp '(mi|MI).*';

+----------------+
| varchar_column |
+----------------+
|     milan      |
+----------------+

1 row in set (0.01 sec)

mysql> select varchar_column from small_table where varchar_column regexp 'm.*';

+----------------+
| varchar_column |
+----------------+
|     milan      |
+----------------+

1 row in set (0.01 sec)

Alias

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

Когда нужен псевдоним, вы можете просто добавить часть AS после имени таблицы, столбца и выражения в списке select или списке from. Ключевое слово AS является необязательным. Вы также можете указать псевдонимы непосредственно после исходного имени без использования AS.

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

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

Примеры:

select tiny_column as name, int_column as sex from big_table;

select sum(tiny_column) as total_count from big_table;

select one.tiny_column, two.int_column from small_table one, <br/> big_table two where one.tiny_column = two.tiny_column;

PIVOT

Эта функция поддерживается начиная с v1.5.2.

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

На самом деле, PIVOT — это синтаксический сахар, который может упростить написание оператора запроса, такого как sum(case when ... then ... end).

Синтаксис

pivot:
SELECT ...
FROM ...
PIVOT (
  aggregate_function(<expr>) [[AS] alias] [, aggregate_function(<expr>) [[AS] alias] ...]
  FOR <pivot_column>
  IN (<pivot_value>)
)

pivot_column:
<column_name>
| (<column_name> [, <column_name> ...])

pivot_value:
<literal> [, <literal> ...]
| (<literal>, <literal> ...) [, (<literal>, <literal> ...)]

Параметры

В операции PIVOT вам нужно указать несколько ключевых компонентов:

aggregate_function(): Агрегатная функция, такая как SUM, AVG, COUNT и т.д., используемая для суммирования данных.
alias: Псевдоним для агрегированного результата, делающий результат более понятным.
FOR pivot_column: Указывает имя столбца, для которого будет выполнено преобразование строк в столбцы.
IN (pivot_value): Указывает конкретные значения pivot_column, которые будут преобразованы в столбцы.

Примеры

create table t1 (c0 int, c1 int, c2 int, c3 int);
SELECT * FROM t1 PIVOT (SUM(c1) AS sum_c1, AVG(c2) AS avg_c2 FOR c3 IN (1, 2, 3, 4, 5));
-- The result is equivalent to the following query:
SELECT SUM(CASE WHEN c3 = 1 THEN c1 ELSE NULL END) AS sum_c1_1,
       AVG(CASE WHEN c3 = 1 THEN c2 ELSE NULL END) AS avg_c2_1,
       SUM(CASE WHEN c3 = 2 THEN c1 ELSE NULL END) AS sum_c1_2,
       AVG(CASE WHEN c3 = 2 THEN c2 ELSE NULL END) AS avg_c2_2,
       SUM(CASE WHEN c3 = 3 THEN c1 ELSE NULL END) AS sum_c1_3,
       AVG(CASE WHEN c3 = 3 THEN c2 ELSE NULL END) AS avg_c2_3,
       SUM(CASE WHEN c3 = 4 THEN c1 ELSE NULL END) AS sum_c1_4,
       AVG(CASE WHEN c3 = 4 THEN c2 ELSE NULL END) AS avg_c2_4,
       SUM(CASE WHEN c3 = 5 THEN c1 ELSE NULL END) AS sum_c1_5,
       AVG(CASE WHEN c3 = 5 THEN c2 ELSE NULL END) AS avg_c2_5
FROM t1
GROUP BY c0;

EXCLUDE

Эта функция поддерживается начиная с версии 4.0.

Ключевое слово EXCLUDE используется для исключения указанных столбцов из результатов запроса, упрощая SQL операторы, когда определенные столбцы могут быть проигнорированы. Это особенно удобно при работе с таблицами, содержащими большое количество столбцов, избегая необходимости явно перечислять все столбцы, которые должны быть сохранены.

Синтаксис

SELECT
  * EXCLUDE (<column_name> [, <column_name> ...])
  | <table_alias>.* EXCLUDE (<column_name> [, <column_name> ...])
FROM ...

Параметры

* EXCLUDE Выбирает все столбцы с подстановочным знаком *, за которым следует EXCLUDE и список имен столбцов для исключения.
<table_alias>.* EXCLUDE Когда существует псевдоним таблицы, это позволяет исключить определенные столбцы из этой таблицы (должно использоваться с псевдонимом).
<column_name> Имя(имена) столбца для исключения. Несколько столбцов разделяются запятыми. Столбцы должны существовать в таблице; в противном случае будет возвращена ошибка.

Примеры

Базовое использование

-- Create test_table.
CREATE TABLE test_table (
  id INT,
  name VARCHAR(50),
  age INT,
  email VARCHAR(100)
) DUPLICATE KEY(id);

-- Exclude a single column (age).
SELECT * EXCLUDE (age) FROM test_table;
-- Above is equivalent to:
SELECT id, name, email FROM test_table;

-- Exclude multiple columns (name, email).
SELECT * EXCLUDE (name, email) FROM test_table;
-- Above is equivalent to:
SELECT id, age FROM test_table;

-- Exclude columns using a table alias.
SELECT test_table.* EXCLUDE (email) FROM test_table;
-- Above is equivalent to:
SELECT id, name, age FROM test_table;

WITH​

Join​

Self Join​

Cross Join​

Inner Join​

Outer Join​

Эквивалентные и неэквивалентные join​

Semi Join​

Anti Join​

Equi-join и Non-equi-join​

Join с частью USING​

ASOF Join​

ORDER BY​

GROUP BY​

Синтаксис​

Параметры​

Примеры​

HAVING​

LIMIT​

OFFSET​

UNION​

INTERSECT​

EXCEPT/MINUS​

DISTINCT​

Subquery​

Некоррелированный подзапрос​

Коррелированный подзапрос​

Where и операторы​

Арифметический оператор​

Оператор Between​

Операторы сравнения​

Оператор In​

Оператор Like​

Логический оператор​

Оператор регулярного выражения​

Alias​

PIVOT​

Синтаксис​

Параметры​

Примеры​

EXCLUDE​

Синтаксис​

Параметры​

Примеры​

Базовое использование​

WITH

Join

Self Join

Cross Join

Inner Join

Outer Join

Эквивалентные и неэквивалентные join

Semi Join

Anti Join

Equi-join и Non-equi-join

Join с частью USING

ASOF Join

ORDER BY

GROUP BY

Синтаксис

Параметры

Примеры

HAVING

LIMIT

OFFSET

UNION

INTERSECT

EXCEPT/MINUS

DISTINCT

Subquery

Некоррелированный подзапрос

Коррелированный подзапрос

Where и операторы

Арифметический оператор

Оператор Between

Операторы сравнения

Оператор In

Оператор Like

Логический оператор

Оператор регулярного выражения

Alias

PIVOT

Синтаксис

Параметры

Примеры

EXCLUDE

Синтаксис

Параметры

Примеры

Базовое использование