SELECT
Содержание раздела
- Выбор версии данных
- Применение запроса
- Статистика
- Синтаксис
- WITH
- FOR SYSTEM_TIME
- FOR SYSTEM_TIME AS OF ‘timestamp’
- FOR SYSTEM_TIME AS OF DELTA_NUM delta_num
- FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA
- FOR SYSTEM_TIME STARTED IN (delta_num1, delta_num2)
- FOR SYSTEM_TIME FINISHED IN (delta_num1, delta_num2)
- FOR SYSTEM_TIME AS OF CN sys_cn
- FOR SYSTEM_TIME STARTED CN (sys_cn1, sys_cn2)
- FOR SYSTEM_TIME FINISHED CN (sys_cn1, sys_cn2)
- FOR SYSTEM_TIME STARTED TS (datetime1, datetime2)
- FOR SYSTEM_TIME FINISHED TS (datetime1, datetime2)
- Поддерживаемые сущности
- Способы указания значения FOR SYSTEM_TIME
- Особенности выражений со STARTED и FINISHED
- JOIN
- WHERE
- GROUP BY
- HAVING
- UNION
- INTERSECT
- EXCEPT
- ORDER BY
- LIMIT
- OFFSET
- FETCH
- DATASOURCE_TYPE
- ESTIMATE_ONLY
- Поддерживаемые функции
- LISTAGG
- Функции по управлению числовыми последовательностями
- Функции по управлению текстовым поиском
- Варианты ответа
- Ограничения
- Примеры
- Звездочка и WHERE
- DATASOURCE_TYPE
- GROUP BY, ORDER BY и LIMIT
- SELECT без таблицы
- OFFSET
- ORDER BY, LIMIT и OFFSET
- FOR SYSTEM_TIME AS OF DELTA_NUM
- FOR SYSTEM_TIME AS OF CN
- FOR SYSTEM_TIME STARTED TS
- Соединение таблиц из разных логических БД
- Соединение изменений по выбранным полям
- Запрос изменений дельты из логического представления
- Запрос данных из прокси-таблицы
- Запрос данных из standalone-таблицы
- Соединение standalone-таблицы и логической таблицы
- Запрос данных из партиционированных таблиц
- Запрос данных из партиции
- Запрос данных, включая системные столбцы sys_from и sys_to
- Запрос данных с фильтром по системному столбцу sys_from
Поддерживается в версиях: 7.0 / 6.12 / 6.11 / 6.10 / 6.9 / 6.8 / 6.7 / 6.6 / 6.5 / 6.4 / 6.3 / 6.2 / 6.1 / 6.0 / 5.8 / 5.7 / 5.6 / 5.5 / 5.4 / 5.3 / 5.2 / 5.1 / 5.0.
Запрос возвращает данные из следующих сущностей и их соединений:
- логических таблиц,
- логических представлений,
- материализованных представлений,
- прокси-таблиц,
- standalone-таблиц.
Синтаксис чтения из standalone-таблицы подразумевает использование внешней readable-таблицы, которая указывает на нужную standalone-таблицу.
Чтобы получить данные, включающие сотни и более записей, рекомендуется использовать потоковое чтение данных или выгрузку данных.
Помимо имен логических сущностей и их столбцов, в запросе можно указать:
- поддерживаемые ключевые слова,
- поддерживаемые функции,
- псевдонимы имен таблиц, представлений и столбцов.
Выбор версии данных
Запросы к логическим таблицам, логическим и материализованным представлениям по умолчанию возвращают текущую версию данных.
Чтобы выбрать срез данных другой версии, укажите ключевое слово FOR SYSTEM_TIME с нужным значением. Запросы с этим ключевым словом к прокси-таблицам и standalone-таблицам возвращают либо пустой результат (для FOR SYSTEM_TIME STARTED/FINISHED IN/CN/TS
), либо текущее состояние данных, так как эти таблицы не поддерживают версионирование данных (см. ограничения ключевых слов).
Применение запроса
Запрос можно использовать не только для чтения данных, но также для получения информации о самом запросе или как подзапрос в следующих запросах:
- на выгрузку данных,
- на создание или обновление логического представления,
- на создание материализованного представления,
- на вставку данных из другой сущности.
Выбор непартиционированных данных
Запрос к сущностям, которые хранят непартиционированные данные, возвращает данные из датасорса:
- указанного в запросе или наиболее оптимального — если данные выгружаются из логических таблиц, логических представлений без партиционированных данных и материализованных представлений;
- содержащего таблицу-источник — если данные выгружаются из прокси-таблицы или standalone-таблицы, а также их соединений с другими сущностями.
Выбор партиционированных данных
Запрос к партиционированной таблице автоматически перенаправляется в задействованные партиции и возвращает объединенную выборку по этим партициям из всех датасорсов, выбранных для исполнения запроса. Подробнее о выборе датасорсов см. в разделе Маршрутизация запросов к данным > Маршрутизация запросов к партиционированным таблицам.
Запрос напрямую к партиции выбирает данные из указанной партиции.
Статистика
Запросы SELECT
учитываются в категории статистики READ
. Статистика доступна с помощью запроса GET_ENTITY_STATISTICS и GET-методов получения статистики.
Синтаксис
[ WITH cte_name [(cte_column_name[, ... ])] AS (cte_select) ]
SELECT expression
[ FROM
{ [db_name1.]entity_name1 [FOR SYSTEM_TIME time_expression] | (select1) }
[ [AS] alias1 ]
[ [join_prefix] JOIN
{ [db_name2.]entity_name2 [FOR SYSTEM_TIME time_expression] | (select2) }
[ [AS] alias2 ]
ON join_condition ]
[ WHERE condition ]
[ GROUP BY column_name [, ... ] ]
[ HAVING condition ]
[ {UNION | INTERSECT | EXCEPT} [ALL | DISTINCT] (select3) ]
[ ORDER BY column_name [ASC | DESC] [, ... ] ]
[ LIMIT count ]
[ OFFSET start [ROW | ROWS] ]
[ FETCH {FIRST | NEXT} count ROWS ONLY ]
[ DATASOURCE_TYPE = 'datasource_name' ]
[ ESTIMATE_ONLY ]
Параметры:
expression
-
Выражение для составления результата SELECT-запроса. Может содержать любой из вариантов:
- имена столбцов задействованных сущностей, включая имена системных столбцов
sys_from
,sys_to
иsys_op
логических таблиц и материализованных представлений; - символ
*
для выбора всех столбцов, кроме системных; - сочетание имен столбцов, констант, операторов и SQL-функций.
- имена столбцов задействованных сущностей, включая имена системных столбцов
db_name
-
Имя логической базы данных, из которой выбираются данные. Опционально, если выбрана логическая БД, используемая по умолчанию. Значения
db_name1
иdb_name2
могут как совпадать, так и отличаться. entity_name
-
Имя таблицы или представления, откуда выбираются данные.
select
-
SELECT-подзапрос.
alias
-
Псевдоним таблицы или представления. Может включать латинские буквы, цифры и символы подчеркивания.
WITH
Задает одно или несколько обобщенных табличных выражений (Common Table Expressions или CTE, далее — табличное выражение), используемых в основном запросе.
Ключевое слово доступно во всех видах SELECT-запросов и SELECT-подзапросов: в отдельных запросах на выборку данных, в подзапросах в составе DDL-запросов на создание логических и материализованных представлений, в подзапросах в составе INSERT SELECT и т.д.
Ключевое слово доступно в запросах к СУБД ADB и ADP.
Синтаксис:
WITH cte_name [(cte_column_name[, ... ])] AS (cte_select)
Где:
cte_name
— имя обобщенного табличного выражения (Common Table Expressions или CTE);cte_column_name
- имя столбца, выбираемого из подзапросаcte_select
для создания обобщенного табличного выраженияcte_name
. Если столбцы не указаны, в табличном выражении используются столбцы подзапросаcte_select
;cte_select
— SELECT-подзапрос, определяющий состав обобщенного табличного выраженияcte_name
.
Табличные выражения вычисляются перед основным запросом и представляют собой временные структуры, которые сохраняются только на время выполнения запроса. Табличные выражения позволяют упростить сложные запросы за счет разделения на более простые части. Как правило, любой запрос с одним или несколькими подзапросами можно переписать с использованием WITH
.
В табличных выражениях можно использовать любые поддерживаемые ключевые слова, в том числе FOR SYSTEM_TIME, а также другие табличные выражения того же запроса. В табличных выражениях недоступно использование операторов, изменяющих данные: INSERT
, UPSERT
и DELETE
.
Рекурсивные и материализованные табличные выражения не поддерживаются.
Пример запроса:
-- запрос данных о продажах в наиболее популярных магазинах с двумя табличными выражениями
WITH sales_by_shops AS (
SELECT store_id, COUNT(id) AS total_sales
FROM marketing.sales
GROUP BY store_id),
top_stores AS (
SELECT store_id
FROM sales_by_shops
WHERE total_sales > 20
)
SELECT store_id,
product_code,
SUM(product_units) AS product_units,
COUNT(id) AS product_sales
FROM marketing.sales
WHERE store_id IN (SELECT store_id FROM top_stores)
GROUP BY store_id, product_code
ORDER BY store_id, product_units
DATASOURCE_TYPE = 'adp'
FOR SYSTEM_TIME
Задает момент времени или период, за который выбираются данные или изменения данных. Относится к логической сущности, после имени которой оно следует.
Синтаксис:
-- данные по состоянию на указанный момент
FOR SYSTEM_TIME AS OF { 'timestamp' | DELTA_NUM delta_num | LATEST_UNCOMMITTED_DELTA | CN sys_cn }
-- изменения за указанный период
FOR SYSTEM_TIME { {STARTED | FINISHED} IN (delta_num1, delta_num2) |
{STARTED | FINISHED} CN (sys_cn1, sys_cn2) |
{STARTED | FINISHED} TS (datetime1, datetime2)} }
Если ключевое слово FOR SYSTEM_TIME
не указано:
- из логических таблиц и логических представлений выбираются данные последней версии по текущему времени сервера (в выборку не попадают данные, которые имеют более позднюю метку времени);
- из материализованных представлений выбираются данные последней версии на момент последней синхронизации этого представления.
FOR SYSTEM_TIME AS OF ‘timestamp’
Выражение определяет версию данных на указанный момент времени. Доступно для логических таблиц, логических и материализованных представлений. Игнорируется для прокси-таблиц и standalone-таблиц.
В выборку попадают записи по состоянию:
- на последнюю завершенную операции записи в указанный момент — если выражение относится к логической таблице или логическому представлению;
- на последнюю закрытую дельту в указанный момент — если выражение относится к материализованному представлению.
Метка времени timestamp
может быть указана с любой точностью до микросекунд, подробнее см. в разделе Форматы даты и времени в запросах.
SELECT * FROM marketing.clients FOR SYSTEM_TIME AS OF '2023-01-01 00:00:00';
SELECT * FROM marketing.clients FOR SYSTEM_TIME AS OF '2023-01-01 00:00:00.134242';
FOR SYSTEM_TIME AS OF DELTA_NUM delta_num
Выражение определяет версию данных на момент закрытия дельты с номером delta_num
. Доступно для логических таблиц, логических и материализованных представлений. Игнорируется для прокси-таблиц и standalone-таблиц.
SELECT * FROM marketing.clients FOR SYSTEM_TIME AS OF DELTA_NUM 10
FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA
Выражение определяет текущую версию данных, которая включает изменения* открытой дельты. Доступно для логических таблиц без ограничений, для логических представлений — с ограничениями; игнорируется для прокси-таблиц и standalone-таблиц; недоступно для материализованных представлений (см. секцию Поддерживаемые сущности).
* Включаются изменения открытой дельты, внесенные операциями из непрерывного диапазона завершенных операций записи.
SELECT * FROM marketing.clients FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA
FOR SYSTEM_TIME STARTED IN (delta_num1, delta_num2)
Выражение определяет записи, добавленные и измененные в диапазоне дельт. Доступно для логических таблиц без ограничений, для логических и материализованных представлений — с ограничениями; для прокси-таблиц и standalone-таблиц возвращается пустой результат (см. секцию Поддерживаемые сущности).
В выборку попадают записи, которые были добавлены и (или) изменены в дельтах с delta_num1
по delta_num2
, включая обе границы, а также в отдельных операциях записи, выполненных после дельты delta_num1 - 1
и до дельты delta_num1
.
Выборка отображает разницу в состоянии данных за период без промежуточных изменений. Подробнее см. в секции Особенности выражений со STARTED и FINISHED.
-- изменения по диапазону дельт
SELECT * FROM marketing.clients FOR SYSTEM_TIME STARTED IN (10, 20);
-- изменения по одной дельте
SELECT * FROM marketing.clients FOR SYSTEM_TIME STARTED IN (10, 10);
FOR SYSTEM_TIME FINISHED IN (delta_num1, delta_num2)
Выражение определяет записи, удаленные в диапазоне дельт. Доступно для логических таблиц без ограничений, для логических и материализованных представлений — с ограничениями; для прокси-таблиц и standalone-таблиц возвращается пустой результат (см. секцию Поддерживаемые сущности).
В выборку попадают записи, которые были удалены в дельтах с delta_num1
по delta_num2
, включая обе границы, а также в отдельных операциях записи, выполненных после дельты delta_num1 - 1
и до дельты delta_num1
.
Выборка отображает разницу в состоянии данных за период без промежуточных изменений. Подробнее см. в секции Особенности выражений со STARTED и FINISHED.
-- изменения по диапазону дельт
SELECT * FROM marketing.clients FOR SYSTEM_TIME FINISHED IN (10, 20);
-- изменения по одной дельте
SELECT * FROM marketing.clients FOR SYSTEM_TIME FINISHED IN (10, 10);
FOR SYSTEM_TIME AS OF CN sys_cn
Выражение определяет версию данных с номером sys_cn
. Доступно для логических таблиц без ограничений, для логических представлений — с ограничениями; игнорируется для прокси-таблиц и standalone-таблиц; недоступно для материализованных представлений (см. секцию Поддерживаемые сущности).
Если операция записи с номером sys_cn
еще выполняется, результаты выдачи по ней могут быть не согласованы.
SELECT * FROM marketing.clients FOR SYSTEM_TIME AS OF CN 32
FOR SYSTEM_TIME STARTED CN (sys_cn1, sys_cn2)
Выражение определяет записи, добавленные и (или) измененные в диапазоне операций с sys_cn1
по sys_cn2
, включая обе границы. Доступно для логических таблиц без ограничений, для логических представлений — с ограничениями; для прокси-таблиц и standalone-таблиц возвращается пустой результат; недоступно для материализованных представлений (см. секцию Поддерживаемые сущности).
Если указанные операции еще выполняются, результаты выдачи по ним могут быть не согласованы.
Выборка отображает разницу в состоянии данных за период без промежуточных изменений. Подробнее см. в секции Особенности выражений со STARTED и FINISHED.
-- изменения по диапазону операций
SELECT * FROM marketing.clients FOR SYSTEM_TIME STARTED CN (32, 35);
-- изменения по одной операции
SELECT * FROM marketing.clients FOR SYSTEM_TIME STARTED CN (32, 32);
FOR SYSTEM_TIME FINISHED CN (sys_cn1, sys_cn2)
Выражение определяет записи, удаленные в диапазоне операций с sys_cn1
по sys_cn2
, включая обе границы. Доступно для логических таблиц без ограничений, для логических представлений — с ограничениями; для прокси-таблиц и standalone-таблиц возвращается пустой результат; недоступно для материализованных представлений (см. секцию Поддерживаемые сущности).
Если указанные операции еще выполняются, результаты выдачи по ним могут быть не согласованы.
Выборка отображает разницу в состоянии данных за период без промежуточных изменений. Подробнее см. в секции Особенности выражений со STARTED и FINISHED.
-- изменения по диапазону операций
SELECT * FROM marketing.clients FOR SYSTEM_TIME FINISHED CN (32, 35);
-- изменения по одной операции
SELECT * FROM marketing.clients FOR SYSTEM_TIME FINISHED CN (32, 32);
FOR SYSTEM_TIME STARTED TS (datetime1, datetime2)
Выражение определяет записи, добавленные и (или) измененные в период времени с datetime1
по datetime2
, включая обе границы. Доступно для логических таблиц и представлений без ограничений, для материализованных представлений — с ограничениями; для прокси-таблиц и standalone-таблиц возвращается пустой результат (см. секцию Поддерживаемые сущности).
Выборка отображает разницу в состоянии данных за период без промежуточных изменений. Подробнее см. в секции Особенности выражений со STARTED и FINISHED.
Время datetime1
и datetime2
можно указать в любом из форматов:
- строка с меткой времени в любом из форматов, описанных в разделе Форматы даты и времени в запросах;
- Unix-время — целое число микросекунд с 00:00:00 UTC 1 января 1970 года.
-- изменения за период с указанием строковых меток времени
SELECT * FROM marketing.clients FOR SYSTEM_TIME STARTED TS ('2023-04-17 10:00:00.123', '2023-04-30 22:00:00.123');
-- изменения за период с указанием меток времени относительно Unix-эпохи
SELECT * FROM marketing.clients FOR SYSTEM_TIME STARTED TS (1681725600123000, 1682892000123000);
-- изменения за период с указанием меток времени в разных форматах
SELECT * FROM marketing.clients FOR SYSTEM_TIME STARTED TS ('2023-04-17 10:00:00.123', 1682892000123000);
FOR SYSTEM_TIME FINISHED TS (datetime1, datetime2)
Выражение определяет записи, удаленные в период времени с datetime1
по datetime2
, включая обе границы. Доступно для логических таблиц и представлений без ограничений, для материализованных представлений — с ограничениями; для прокси-таблиц и standalone-таблиц возвращается пустой результат (см. секцию Поддерживаемые сущности).
Выборка отображает разницу в состоянии данных за период без промежуточных изменений. Подробнее см. в секции Особенности выражений со STARTED и FINISHED.
Время datetime1
и datetime2
можно указать в любом из форматов:
- строка с меткой времени в любом из форматов, описанных в разделе Форматы даты и времени в запросах;
- Unix-время — целое число микросекунд с 00:00:00 UTC 1 января 1970 года.
-- изменения за период с указанием строковых меток времени
SELECT * FROM marketing.clients FOR SYSTEM_TIME FINISHED TS ('2023-04-17 10:00:00.123', '2023-04-30 22:00:00.123');
-- изменения за период с указанием меток времени относительно Unix-эпохи
SELECT * FROM marketing.clients FOR SYSTEM_TIME FINISHED TS (1681725600123000, 1682892000123000);
-- изменения за период с указанием меток времени в разных форматах
SELECT * FROM marketing.clients FOR SYSTEM_TIME FINISHED TS ('2023-04-17 10:00:00.123', 1682892000123000);
Поддерживаемые сущности
Ключевое слово доступно в запросах и подзапросах к следующим сущностям:
- логическим таблицам,
- логическим представлениям,
- материализованным представлениям.
Ключевое слово игнорируется в запросах к прокси-таблицам и standalone-таблицам. Запрос к прокси-таблице и standalone-таблице возвращает либо пустой результат (для FOR SYSTEM_TIME STARTED/FINISHED IN/CN/TS
), либо текущий набор записей.
Ключевое слово недоступно:
- в подзапросах в составе запросов на создание и изменение логических и материализованных представлений;
- в запросах и подзапросах к обобщенным табличным выражениям.
В таблице ниже описана доступность выражений FOR SYSTEM_TIME
в SELECT-запросах к таблицам и представлениям.
Выражение | Логические таблицы | Логические представления | Мат. представления |
---|---|---|---|
FOR SYSTEM_TIME AS OF 'timestamp' | + | + | + |
FOR SYSTEM_TIME AS OF DELTA_NUM delta_num | + | + | + |
FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA | + | + Для простых* представлений | – |
FOR SYSTEM_TIME STARTED IN (delta_num1, delta_num2) | + | + В запросах к ADP, ADB или ADG, если представление НЕ содержит ORDER BY | + В запросах к ADP, ADB или ADG, если представление содержит обе дельты диапазона |
FOR SYSTEM_TIME FINISHED IN (delta_num1, delta_num2) | + | + В запросах к ADP, ADB или ADG, если представление НЕ содержит ORDER BY | + В запросах к ADP, ADB или ADG, если представление содержит обе дельты диапазона |
FOR SYSTEM_TIME AS OF CN sys_cn | + | + Для простых* представлений | – |
FOR SYSTEM_TIME STARTED CN (sys_cn1, sys_cn2) | + | + Для простых* представлений | – |
FOR SYSTEM_TIME FINISHED CN (sys_cn1, sys_cn2) | + | + Для простых* представлений | – |
FOR SYSTEM_TIME STARTED TS (datetime1, datetime2) | + | + В запросах к ADP, ADB или ADG, если представление НЕ содержит ORDER BY | + Если значение datetime2 больше или равно метке времени дельты 0 |
FOR SYSTEM_TIME FINISHED TS (datetime1, datetime2) | + | + В запросах к ADP, ADB или ADG, если представление НЕ содержит ORDER BY | + Если значение datetime2 больше или равна метке времени дельты 0 |
* Простое логическое представление — представление, построенное на запросе к одной таблице с простым фильтром (без подзапросов и соединений) и принадлежащее той же логической БД, что и таблица. Например, представление вида CREATE VIEW marketing.sales_view AS SELECT * FROM marketing.sales WHERE store_id > 100
.
Для логического представления, построенного на основе таблиц из разных логических баз данных, данные из всех таблиц выбираются за один и тот же момент или период времени. Момент и период времени определяются по дельтам или операциям записи той логической базы данных, которой принадлежит представление.
Ключевое слово FOR SYSTEM_TIME
в запросах к материализованным представлениям влияет на порядок маршрутизации запросов.
Способы указания значения FOR SYSTEM_TIME
Указать значение FOR SYSTEM_TIME
в запросе можно любым из способов:
- с помощью константы — например,
FOR SYSTEM_TIME STARTED CN 15
; - с помощью параметра — например,
FOR SYSTEM_TIME STARTED CN ?
илиFOR SYSTEM_TIME STARTED CN :cn_started
.
Подробнее об использовании параметров в запросах см. в разделе Параметры запросов.
Особенности выражений со STARTED и FINISHED
Запросы с выражениями FOR SYSTEM_TIME STARTED/FINISHED IN/CN/TS
возвращают разницу в состоянии данных за период, а не весь набор последовательных изменений данных за это время.
Например, у клиента два раза менялся номер телефона — в дельте 3 и дельте 6. При запросе данных, добавленных и измененных в дельтах [3, 6]
, для этого клиента вернется только одна запись — с итоговым номером телефона. Промежуточный номер телефона в ответе не вернется.
JOIN
Соединяет две выборки. Соединение возможно, если есть хотя бы один датасорс, в котором размещены все соединяемые данные.
Синтаксис:
from_item1 [join_prefix] JOIN from_item2 ON join_condition
Где:
from_item
— выборка из логической сущности или SELECT-подзапроса;join_prefix
— тип соединения из следующих:[INNER] JOIN
— внутреннее соединение,NATURAL JOIN
— внутреннее соединение по всем столбцам с одинаковыми именами, ключи соединения не указываются,LEFT [OUTER] JOIN
— левое внешнее соединение,RIGHT [OUTER] JOIN
— правое внешнее соединение,FULL [OUTER] JOIN
— полное внешнее соединение,CROSS JOIN
— декартово произведение таблиц или представлений, ключи соединения не указываются;
join_condition
— условие соединения. Может включать любые общие столбцы соединяемых сущностей, включая системные столбцыsys_from
,sys_to
иsys_op
логических таблиц и материализованных представлений.
Некоторые типы соединений доступны не во всех СУБД. Например в запросах к СУБД ADG доступно только соединение[INNER] JOIN
.
Подробнее о доступных возможностях см. в разделе Поддержка SQL.
Пример запроса:
SELECT
st.id,
st.category,
s.product_code
FROM marketing.stores AS st
INNER JOIN marketing.sales AS s
ON st.id = s.store_id
WHERE
Задает условия выбора данных. В отличие от HAVING, фильтрует данные до применения GROUP BY, а не наоборот.
Синтаксис:
WHERE condition
Условие может содержать выражение с любыми столбцами сущностей, задействованных в запросе, включая системные столбцы sys_from
, sys_to
и sys_op
логических таблиц и материализованных представлений.
В запросах к партиционированным таблицам условие WHERE
может содержать операторы:
>
,>=
,<
,<=
,=
,IN
,BETWEEN
,IS TRUE|FALSE|NULL
,IS NOT TRUE|FALSE
.
Пример запроса:
SELECT * FROM marketing.sales
WHERE store_id = 123
GROUP BY
Задает столбцы, по значениям которых группируются возвращаемые строки. Используется в сочетании с агрегационными функциями.
Синтаксис:
GROUP BY column_name [, ... ]
Пример запроса:
SELECT s.store_id, SUM(s.product_units) AS product_amount
FROM marketing.sales AS s
GROUP BY s.store_id
ORDER BY product_amount DESC
HAVING
Задает условие выбора сгруппированных данных. В отличие от WHERE, фильтрует данные после применения GROUP BY, а не наоборот.
Синтаксис:
HAVING condition
Пример запроса:
SELECT s.store_id, SUM(s.product_units) AS product_amount
FROM marketing.sales AS s
HAVING s.store_id > 120
GROUP BY (s.store_id)
ORDER BY product_amount DESC
UNION
Объединяет две выборки. Результат объединения возвращает строки, которые есть хотя бы в одной выборке.
Синтаксис:
(select1) UNION [ALL | DISTINCT] (select2)
Пример запроса:
SELECT * FROM marketing.sales UNION ALL SELECT marketing_new.sales
INTERSECT
Пересекает две выборки. Результат пересечения возвращает строки, которые есть в обеих выборках.
Синтаксис:
(select1) INTERSECT [ALL | DISTINCT] (select2)
Пример запроса:
SELECT * FROM marketing.sales INTERSECT DISTINCT SELECT marketing_new.sales
EXCEPT
Вычитает правую выборку из левой. Результат вычитания возвращает строки, которые принадлежат левой выборке, но не правой.
Синтаксис:
(select1) EXCEPT [ALL | DISTINCT] (select2)
Пример запроса:
SELECT * FROM marketing.sales EXCEPT DISTINCT SELECT marketing_new.sales
ORDER BY
Задает один или несколько столбцов, по значениям которых сортируются возвращаемые строки. Если ASC
и DESC
не указаны, результаты сортируются в возрастающем порядке (ASC
).
Синтаксис:
ORDER BY column_name [ASC | DESC] [, ... ]
Пример запроса:
SELECT * FROM marketing.sales ORDER BY id ASC, store_id DESC
LIMIT
Задает количество возвращаемых строк. Может использоваться самостоятельно или в паре с ключевым словом OFFSET. Результат применения аналогичен результату применения FETCH.
Синтаксис:
LIMIT count
Пример запроса:
SELECT * from marketing.sales LIMIT 20
OFFSET
Пропускает первые несколько строк результата и выбирает только последующие строки. Примеры запросов см. ниже.
Ключевое слово недоступно в запросах к СУБД ADG.
Синтаксис:
[ORDER BY column_name [ASC | DESC] [, ... ]]]
[LIMIT count]
OFFSET start [ ROW | ROWS ]
[FETCH {FIRST | NEXT} count ROWS ONLY]
В качестве значения ключевого слова можно указать любое неотрицательное целое число, начиная с нуля, или переменную. Если для OFFSET
указано значение 0, то пропускается 0 строк, что равносильно запросу без OFFSET
.
Запросы с OFFSET
без ограничения количества строк не поддерживаются. То есть, если ключевое слово OFFSET
указано в запросе, то перед ним должно быть ключевое слово LIMIT
или после него должно быть ключевое слово FETCH... ROWS ONLY
. Обратного ограничения нет: ключевые слова LIMIT
и FETCH... ROWS ONLY
можно использовать и без OFFSET
.
Рекомендуется сочетать OFFSET
с ключевым словом ORDER BY
для получения упорядоченного набора строк. Ключевое слово ORDER BY
необязательно, однако без него запрос с OFFSET
возвращает неупорядоченный и потому непредсказуемый набор строк.
Пример запроса:
-- запрос 20 строк, начиная с десятой, с сортировкой по id
SELECT * from marketing.sales ORDER BY id OFFSET 9 FETCH NEXT 20 ROWS ONLY
FETCH
Задает количество возвращаемых строк. Может использоваться самостоятельно или в паре с ключевым словом OFFSET. Результат применения аналогичен результату применения LIMIT.
Синтаксис:
FETCH {FIRST | NEXT} count ROWS ONLY
Пример запроса:
SELECT * from marketing.sales FETCH NEXT 20 ROWS ONLY
DATASOURCE_TYPE
Задает имя датасорса, из которого выбираются данные. Поддерживается для логических таблиц, логических и материализованных представлений. Регистр букв в имени не учитывается.
Синтаксис:
DATASOURCE_TYPE = 'datasource_name'
Пример запроса:
SELECT sold.store_id, sold.product_amount
FROM marketing.stores_by_sold_products AS sold
DATASOURCE_TYPE = 'adqm'
Ключевое слово DATASOURCE_TYPE
, указанное в запросе к партиционированной таблице, применяется к партициям, выбранным для исполнения запроса, а не к самой партиционированной таблице.
Если ключевое слово не указано, система определяет наиболее оптимальный датасорс для исполнения запроса.
ESTIMATE_ONLY
Позволяет запросить информацию о выполнении запроса к данным, а не сами данные.
Синтаксис:
ESTIMATE_ONLY
Пример запроса:
SELECT s.store_id, SUM(s.product_units) AS product_amount
FROM marketing.sales AS s
GROUP BY (s.store_id)
ORDER BY product_amount DESC
ESTIMATE_ONLY
Формат ответа
Если ключевое слово ESTIMATE_ONLY
указано в запросе, в ответе возвращается объект ResultSet с количеством строк, равным количеству задействованных в запросе датасорсов. Каждая строка ResultSet содержит JSON-строку следующего формата:
{
"datasource": "<имя_датасорса>",
"estimation": "<план_выполнения_запроса>",
"query": "<обогащенный_запрос>"
}
Где:
имя_датасорса
— имя датасорса, в котором предполагается выполнение запроса;план_выполнения_запроса
(для СУБД ADB и ADP) — результат выполнения командыEXPLAIN
в датасорсе. Подробнее о командеEXPLAIN
в СУБД ADB см. в документации Greenplum, о команде в СУБД ADP — в документации PostgreSQL;обогащенный_запрос
— запрос, подготовленный системой на основе исходного запроса с учетом специфики типа датасорса.
Начиная с версии 6.1, система возвращает обогащенный запрос, содержащий символы переноса строки \n
. В предыдущих версиях обогащенный запрос возвращался без этих символов.
Пример ответа из ADB
Ниже показан пример JSON-строки, полученной из датасорса типа ADB. Для наглядности пример представлен в виде дерева, а не плоской строки.
{
"datasource": "ADB",
"estimation": [
{
"Plan": {
"Node Type": "Gather Motion",
"Senders": 4,
"Receivers": 1,
"Slice": 1,
"Segments": 4,
"Gang Type": "primary reader",
"Startup Cost": 0.00,
"Total Cost": 433.70,
"Plan Rows": 50000,
"Plan Width": 8,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Slice": 1,
"Segments": 4,
"Gang Type": "primary reader",
"Relation Name": "sales_actual",
"Alias": "sales_actual",
"Startup Cost": 0.00,
"Total Cost": 432.18,
"Plan Rows": 50000,
"Plan Width": 8
}
]
},
"Settings": {
"Optimizer": "Pivotal Optimizer (GPORCA)"
}
}
],
"query": "SELECT id, transaction_date, product_code, product_units, store_id, description\nFROM marketing.sales_actual\nWHERE sys_from <= 21 AND COALESCE(sys_to, 9223372036854775807) >= 21"
}
Пример ответа из ADP
Ниже показан пример JSON-строки, полученной из датасорса типа ADP. Для наглядности пример представлен в виде дерева, а не плоской строки.
{
"datasource": "ADP",
"estimation": [
{
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Relation Name": "sales_actual",
"Alias": "sales_actual",
"Startup Cost": 0.00,
"Total Cost": 18.80,
"Plan Rows": 880,
"Plan Width": 64
}
}
],
"query": "SELECT id, transaction_date, product_code, product_units, store_id, description\nFROM marketing.sales_actual\nWHERE sys_from <= 21 AND COALESCE(sys_to, 9223372036854775807) >= 21"
}
Поддерживаемые функции
В запросе можно использовать все функции, отмеченные как поддерживаемые в разделе Поддержка SQL.
В этом разделе рассмотрены следующие функции:
- LISTAGG,
- функции по управлению числовыми последовательностями,
- функции по управлению текстовым поиском.
LISTAGG
Функция объединяет значения в одну строку, перечисляя их через заданный разделитель. После последнего значения разделитель не добавляется. Строки со значением NULL
пропускаются, и соответствующий разделитель не добавляется.
По умолчанию результаты не сортируются, для их сортировки укажите условие сортировки.
Функция доступна в запросах к СУБД ADB и ADP. Подробнее см. в разделе Поддержка SQL.
Синтаксис:
LISTAGG (expression, separator)
[WITHIN GROUP (ORDER BY order_by_expression_list [ ASC | DESC ])]
Где:
expression
— выражение для выбора значений. Может иметь любой тип данных; перед объединением значений система приводит их к типуVARCHAR
;separator
— разделитель между значениями. Может быть любым выражением, кроме символа перевода строки;order_by_expression_list
— выражение для сортировки результатов. По умолчанию результаты сортируются в порядке ASC (по возрастанию).
Примеры запросов:
-- запрос плоского списка ФИО клиентов с их датой рождения
SELECT
LISTAGG (last_name || ' ' || first_name || ' ' || patronymic_name || ' ' || birth_date , ', ')
WITHIN GROUP (ORDER BY last_name, first_name, patronymic_name DESC) as client_info
FROM marketing.clients datasource_type = 'adp';
|client_info |
|------------------------------------------------------------------------|
|Васильева Мария Ивановна 1985-01-10, Сидоров Василий Петрович 1980-12-26|
-- запрос списка адресов магазинов с группировкой по городам
SELECT region, LISTAGG (address, '; ') WITHIN GROUP (ORDER BY address) as addresses_by_city
FROM marketing.stores
GROUP BY region
ORDER BY region ASC
DATASOURCE_TYPE = 'adb';
|region |addresses_by_city |
|---------------+------------------------------------|
|Калуга |пер. Достоевского, 12; ул. Новая, 73|
|Москва |ул. Красная, 15; ул. Цветочная, 33/1|
|Нижний Новгород|пр. Ветеранов, 125; пр. Ленина, 1 |
Функции по управлению числовыми последовательностями
Система поддерживает следующие функции по управлению числовыми последовательностями, доступные в запросах к СУБД ADB и ADP:
Система позволяет управлять числовыми последовательностями с помощью команд и функций, но сами последовательности создаются и хранятся в СУБД. Подробнее о числовых последовательностях см. в документации PostgreSQL и Greenplum.
NEXTVAL
Переводит текущее значение последовательности на следующее и возвращает результат.
Синтаксис:
NEXTVAL('[schema_name.]sequence_name')
Где:
schema_name
— имя схемы СУБД, которой принадлежит числовая последовательность. Если не указано, используется схема СУБД по умолчанию;sequence_name
— имя числовой последовательности. Список числовых последовательностей можно получить, выполнив запрос GET_SEQUENCES.
Пример запроса:
SELECT nextval('marketing.counter_by_10') DATASOURCE_TYPE = 'adp2'
Дополнительные примеры использования функции см. в разделе Поддержка SQL.
SETVAL
Переводит текущее значение последовательности на указанное и возвращает результат.
Синтаксис:
SETVAL('[schema_name.]sequence_name', set_value[, is_called])
Где:
schema_name
— имя схемы СУБД, которой принадлежит числовая последовательность. Если не указано, используется схема СУБД по умолчанию;sequence_name
— имя числовой последовательности. Список числовых последовательностей можно получить, выполнив запрос GET_SEQUENCES;set_value
— устанавливаемое значение последовательности;is_called
— флаг, который определяет, вернется ли устанавливаемое значение при следующем вызове функцииNEXTVAL
послеSETVAL
. Возможные значения:true
(по умолчанию) — устанавливаемое значение считается уже использованным, и вызовNEXTVAL
вернет значение, следующее за установленным;false
— устанавливаемое значение вернется при вызовеNEXTVAL
.
Пример запроса:
SELECT setval('asc_counter_increased_by_1', 201) DATASOURCE_TYPE = 'adp2'
Дополнительные примеры использования функции см. в разделе Поддержка SQL.
CURRVAL
Возвращает:
- текущее значение последовательности, выданное при последнем вызове функции
NEXTVAL
илиSETVAL
(что было последним); - значение
NULL
, если для последовательности ни разу не вызывались функцииNEXTVAL
иSETVAL
.
В отличие от одноименной функции в PostgreSQL, функция возвращает последнее выданное значение последовательности независимо от того, в какой сессии (текущей или нет) оно было выдано.
Синтаксис:
CURRVAL('[schema_name.]sequence_name')
Где:
schema_name
— имя схемы СУБД, которой принадлежит числовая последовательность. Если не указано, используется схема СУБД по умолчанию;sequence_name
— имя числовой последовательности. Список числовых последовательностей можно получить, выполнив запрос GET_SEQUENCES.
Пример запроса:
SELECT currval('asc_counter_increased_by_1') DATASOURCE_TYPE = 'adp2'
Дополнительные примеры использования функции см. в разделе Поддержка SQL.
Функции по управлению текстовым поиском
Система поддерживает следующие функции по управлению текстовым поиском:
Поддерживаемые варианты сравнений текстовых значений в запросах:
tsvector @@ tsquery
,tsquery @@ tsvector
,text @@ tsquery
.
Система поддерживает функции текстового поиска, но сам поиск выполняется в СУБД. Подробнее о текстовом поиске см. в документации PostgreSQL и Greenplum.
Функции по управлению текстовым поиском подготавливают текстовые значения, указанные как аргументы этих функций, для сравнения с другими текстовыми значениями (подготовленными или нет). Подготовка текстовых значений к текстовому поиску зависит от выбранной конфигурации текстового поиска и заключается в превращении текста в список лексем.
Скорость выполнения запросов с функциями текстового поиска в СУБД ADP можно увеличить, создав индекс типа GIN
или GIST
для тех столбцов таблиц, которые будут участвовать в текстовом поиске. Например: CREATE INDEX fts_idx_for_sales ON marketing.sales_actual USING gin (to_tsvector('russian', description))
.
Конфигурацию текстового поиска можно задать как аргумент функции или использовать конфигурацию по умолчанию. Список доступных конфигураций доступен с помощью прямого запроса к СУБД: SELECT * FROM pg_catalog.pg_ts_config
.
TO_TSVECTOR
Подготавливает текстовые значения, выбранные запросом, для сравнения с другим текстом. Возвращает список лексем с их позицией в тексте.
Функция доступна в запросах к СУБД ADB и ADP.
Синтаксис:
TO_TSVECTOR([search_config,] varchar_column)
Где:
search_config
— конфигурация текстового поиска. Список доступных конфигураций можно получить, выполнив прямой запрос к СУБД:SELECT * FROM pg_catalog.pg_ts_config
;varchar_column
— имя столбца, содержащего текстовое значение. Можно указать имена нескольких столбцов, соединенных функциейCOALESCE
.
Примеры использования функции см. ниже и в разделе Поддержка SQL.
TO_TSQUERY
Подготавливает слова, указанные как аргумент функции, для сравнения с другим текстом. Возвращает список лексем, соединенных оператором &
(AND).
Если в качестве аргумента указано несколько слов, они должны быть соединены любыми из следующих операторов: &
(AND), |
(OR), !
(NOT), и (или) <->
(FOLLOWED BY).
Функция доступна в запросах к СУБД ADB и ADP.
Синтаксис:
TO_TSQUERY([search_config,] formatted_text)
Где:
search_config
— конфигурация текстового поиска. Список доступных конфигураций можно получить, выполнив прямой запрос к СУБД:SELECT * FROM pg_catalog.pg_ts_config
;formatted_text
— текст, состоящий из слов и операторов.
Пример запроса:
SELECT * FROM marketing.sales
WHERE to_tsvector('russian', description) @@ to_tsquery('russian', 'акция & (лето | зима)')
DATASOURCE_TYPE = 'adp';
|id| transaction_date |product_code|product_units|store_id|description |
|--+-----------------------+------------+-------------+--------+---------------------------|
|13|2023-07-22 13:17:47.000|ABC0002 |4 |123 |Покупка по акции яркое лето|
|12|2023-07-23 20:05:56.000|ABC0001 |6 |234 |Акция "Яркое лето" |
|14|2023-07-23 09:34:10.000|ABC0003 |3 |123 |Акция Лето |
|15|2023-10-15 10:11:01.000|ABC0003 |1 |123 |Акция "Уютная зима" |
Дополнительные примеры использования функции см. в разделе Поддержка SQL.
PLAINTO_TSQUERY
Подготавливает неформатированный текст, указанный как аргумент функции, для сравнения с другим текстом. Возвращает список лексем, соединенных оператором &
(AND).
Функция доступна в запросах к СУБД ADB и ADP.
Синтаксис:
PLAINTO_TSQUERY([search_config,] unformatted_text)
Где:
search_config
— конфигурация текстового поиска. Список доступных конфигураций можно получить, выполнив прямой запрос к СУБД:SELECT * FROM pg_catalog.pg_ts_config
;unformatted_text
— неформатированный текст, без указания операторов.
Пример запроса:
SELECT * FROM marketing.sales
WHERE to_tsvector('russian', description) @@ plainto_tsquery('russian', 'акция лето')
DATASOURCE_TYPE = 'adp';
|id|transaction_date |product_code|product_units|store_id|description |
|--+-----------------------+------------+-------------+--------+---------------------------|
|13|2023-07-22 13:17:47.000|ABC0002 |4 |123 |Покупка по акции яркое лето|
|12|2023-07-23 20:05:56.000|ABC0001 |6 |234 |Акция "Яркое лето" |
|14|2023-07-23 09:34:10.000|ABC0003 |3 |123 |Акция Лето |
Дополнительные примеры использования функции см. в разделе Поддержка SQL.
PHRASETO_TSQUERY
Подготавливает неформатированный текст, указанный как аргумент функции, для сравнения с другим текстом, с сохранением порядка слов. Возвращает список лексем, соединенных оператором <->
(FOLLOWED BY).
Функция доступна в запросах к СУБД ADP.
Синтаксис:
PHRASETO_TSQUERY([search_config,] unformatted_text)
Где:
search_config
— конфигурация текстового поиска. Список доступных конфигураций можно получить, выполнив прямой запрос к СУБД:SELECT * FROM pg_catalog.pg_ts_config
;unformatted_text
— неформатированный текст, без указания операторов.
Пример запроса:
SELECT * FROM marketing.sales
WHERE to_tsvector('russian', description) @@ phraseto_tsquery('russian', 'яркое лето')
DATASOURCE_TYPE = 'adp';
|id|transaction_date |product_code|product_units|store_id|description |
|--+-----------------------+------------+-------------+--------+---------------------------|
|13|2023-07-22 13:17:47.000|ABC0002 |4 |123 |Покупка по акции яркое лето|
|25|2023-08-23 20:05:56.000|ABC1111 |6 |234 |Покупка по яркому лету |
|12|2023-07-23 20:05:56.000|ABC0001 |6 |234 |Акция "Яркое лето" |
Дополнительные примеры использования функции см. в разделе Поддержка SQL.
WEBSEARCH_TO_TSQUERY
Подготавливает неформатированный текст, указанный как аргумент функции, для сравнения с другим текстом, с сохранением порядка слов. Возвращает список лексем, соединенных операторами &
(AND), |
(OR), !
(NOT), и <->
(FOLLOWED BY).
В отличие от PLAINTO_TSQUERY
и PHRASETO_TSQUERY
, функция поддерживает операторы OR
и -
(NOT), а также включение слов с сохранением их порядка или без него.
Функция доступна в запросах к СУБД ADP.
Синтаксис:
WEBSEARCH_TO_TSQUERY([search_config,] unformatted_text)
Где:
search_config
— конфигурация текстового поиска. Список доступных конфигураций можно получить, выполнив прямой запрос к СУБД:SELECT * FROM pg_catalog.pg_ts_config
;unformatted_text
— неформатированный текст. Может содержать операторыOR
и-
(NOT) и двойные кавычки для обрамления фразы, в которой должен быть сохранен порядок слов.
Пример запроса:
SELECT * FROM marketing.sales
WHERE to_tsvector('russian', description)
@@ websearch_to_tsquery('russian', 'зима OR лето -яркое')
DATASOURCE_TYPE = 'adp';
|id|transaction_date |product_code|product_units|store_id|description |
|--+-----------------------+------------+-------------+--------+-------------------|
|14|2023-07-23 09:34:10.000|ABC0003 |3 |123 |Акция Лето |
|15|2023-10-15 10:11:01.000|ABC0003 |1 |123 |Акция "Уютная зима"|
Дополнительные примеры использования функции см. в разделе Поддержка SQL.
Варианты ответа
В ответе возвращается:
- объект ResultSet c выбранными записями или информацией о запросе при успешном выполнении запроса;
- исключение при неуспешном выполнении запроса.
Ограничения
Ограничения сущностей
- В запросе можно обращаться либо к логической БД, либо к сервисной БД (см. SELECT FROM INFORMATION_SCHEMA), но не к обеим одновременно.
- Не поддерживаются запросы к материализованным представлениям с ключевыми словами
FOR SYSTEM_TIME
+DATASOURCE_TYPE
, если в представлении отсутствуют данные за момент времени, указанный с помощьюFOR SYSTEM_TIME
. - Не поддерживаются запросы к внешним readable-таблицам, связанным с топиками Kafka.
Ограничения ключевых слов
- Ключевое слово
LISTAGG
доступно для СУБД ADB и ADP. - Ключевое слово
ESTIMATE_ONLY
возвращает план выполнения запроса только для СУБД ADB и ADP. - Ключевое слово
OFFSET
без ограничения количества строк не поддерживается. FOR SYSTEM_TIME
:- Ключевое слово недоступно в SELECT-подзапросах в составе запросов на создание и изменение логических и материализованных представлений, а также в запросах и подзапросах к обобщенным табличным выражениям.
- Запросы к прокси-таблицам и standalone-таблицам с выражениями
FOR SYSTEM_TIME STARTED IN/CN/TS
иFOR SYSTEM_TIME FINISHED IN/CN/TS
всегда возвращают пустой результат; другие значенияFOR SYSTEM_TIME
в таких запросах игнорируются. - Выражение
FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA
недоступно в запросах к логическим и материализованным представлениям. - Выражения
FOR SYSTEM_TIME STARTED IN
иFOR SYSTEM_TIME FINISHED IN
недоступны в запросе к логическому представлению, если запрос предназначен для СУБД ADQM или в представлении используется ключевое словоORDER BY
.
- Ключевое слово
ORDER BY
недоступно в SELECT-подзапросе в составе CREATE MATERIALIZED VIEW. WITH
(табличные выражения):- Табличные выражения доступны для СУБД ADB и ADP.
- Недоступны рекурсивные и материализованные табличные выражения.
- В табличных выражениях не поддерживаются операторы, изменяющих данные (
INSERT
,UPSERT
иDELETE
), и ключевое словоDATASOURCE_TYPE
.
Ограничения соединений
- Не поддерживается соединение сущностей, данные которых размещены в разных датасорсах без какого-либо общего датасорса.
- Если ключами соединения в запросе выступают поля типа Nullable, то строки, где хотя бы один из ключей имеет значение
NULL
, не соединяются.
Ограничения партиционирования
- JOIN-соединение партиционированных таблиц между собой и с другими данными возможно только в пределах единого датасорса.
Другие ограничения
- Запрос без
FOR SYSTEM_TIME
не возвращает данные с метками времени, превышающими текущее время сервера. - Псевдонимы (алиасы) сущностей и столбцов должны начинаться с латинской буквы. После первого символа могут следовать латинские буквы, цифры и символы подчеркивания в любом порядке.
- При обработке запроса все неактивные датасорсы пропускаются без возврата ошибки. Ошибка возвращается, если не осталось ни одного активного датасорса, подходящего для исполнения запроса.
Примеры
Звездочка и WHERE
Запрос с неявным указанием столбцов и ключевым словом WHERE
:
SELECT * FROM marketing.sales
WHERE store_id = 123
DATASOURCE_TYPE
Запрос с перечислением столбцов и выбором данных из определенного датасорса:
SELECT sold.store_id, sold.product_amount
FROM marketing.stores_by_sold_products AS sold
DATASOURCE_TYPE = 'adqm'
GROUP BY, ORDER BY и LIMIT
Запрос с агрегацией, группировкой и сортировкой данных, а также выбором первых 20 строк:
SELECT s.store_id, SUM(s.product_units) AS product_amount
FROM marketing.sales AS s
GROUP BY (s.store_id)
ORDER BY product_amount DESC
LIMIT 20
SELECT без таблицы
Запрос на выделение года из метки времени:
SELECT CAST(EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') AS INT)
OFFSET
Запрос 20 строк, начиная с десятой:
SELECT * from marketing.sales OFFSET 9 FETCH NEXT 20 ROWS ONLY
Описание ключевого слова см. в секции Ключевое слово OFFSET.
ORDER BY, LIMIT и OFFSET
Запрос 20 строк, упорядоченных по значению id
и выбираемых начиная с десятой строки результата:
SELECT * from marketing.sales ORDER BY id LIMIT 20 OFFSET 9
Такое сочетание ключевых слов позволяет выбирать данные порциями с сохранением их порядка.
FOR SYSTEM_TIME AS OF DELTA_NUM
Запрос версии данных по состоянию на момент закрытия дельты с номером 9 из материализованного представления:
SELECT * FROM marketing.sales_and_stores FOR SYSTEM_TIME AS OF DELTA_NUM 9
Описание ключевого слова см. в секции Ключевое слово FOR SYSTEM_TIME.
FOR SYSTEM_TIME AS OF CN
Запрос версии данных с номером 20 из логической таблицы:
SELECT * FROM marketing.sales FOR SYSTEM_TIME AS OF CN 20
FOR SYSTEM_TIME STARTED TS
Запрос записей, добавленных и измененных за период с 2023-04-17 10:00:00.123
по 2023-04-30 22:00:00.123
включительно:
SELECT * FROM marketing.sales FOR SYSTEM_TIME STARTED TS ('2023-04-17 10:00:00.123', '2023-04-30 22:00:00.123')
Запрос добавленных и удаленных записей с указанием периода в виде Unix-времени:
SELECT * FROM marketing.sales FOR SYSTEM_TIME STARTED TS (1681725600123000, 1682892000123000)
Запрос добавленных и удаленных записей с указанием периода в виде меток времени разного формата:
SELECT * FROM marketing.sales FOR SYSTEM_TIME STARTED TS ('2023-04-17 10:00:00.123', 1682892000123000)
Соединение таблиц из разных логических БД
Запрос с соединением данных логических таблиц из двух разных логических БД:
SELECT
st.id,
st.category,
s.product_code
FROM marketing.stores AS st
INNER JOIN marketing_new.sales AS s
ON st.id = s.store_id
О возможных типах соединений см. в секции Поддерживаемые типы соединений.
Соединение изменений по выбранным полям
Запрос изменений по трем полям за два месяца:
SELECT
transaction_id,
store_id,
store_region
FROM (
SELECT
sales.id AS transaction_id,
stores.id AS store_id,
stores.region AS store_region
FROM sales FOR SYSTEM_TIME AS OF '2023-11-14 20:00:00'
JOIN stores FOR SYSTEM_TIME AS OF '2023-11-14 20:00:00'
ON sales.store_id = stores.id
) t2
EXCEPT
SELECT
transaction_id,
store_id,
store_region
FROM (
SELECT
sales.id AS transaction_id,
stores.id AS store_id,
stores.region AS store_region
FROM sales FOR SYSTEM_TIME AS OF '2023-09-14 20:00:00'
JOIN stores FOR SYSTEM_TIME AS OF '2023-09-14 20:00:00'
ON sales.store_id = stores.id
) t1
Запрос изменений дельты из логического представления
Создание логического представления:
CREATE VIEW marketing.marketing_sales_join_stores AS SELECT * FROM marketing.sales as s JOIN marketing.stores as st ON st.id = s.store_id
Запрос записей логического представления, добавленных и измененных в дельте 10:
SELECT * FROM marketing.marketing_sales_join_stores FOR SYSTEM_TIME STARTED IN (10, 10)
Запрос данных из прокси-таблицы
SELECT p.type_code, SUM(p.amount) AS amount, p.currency_code
FROM marketing.payments_proxy AS p
GROUP BY p.type_code, p.currency_code
Запрос данных из standalone-таблицы
-- запрос данных из standalone-таблицы, на которую указывает внешняя readable-таблица payments_ext_read_adqm
SELECT p.agreement_id, p.code, SUM(p.amount) AS amount, p.currency_code
FROM marketing.payments_ext_read_adqm AS p
GROUP BY p.agreement_id, p.code, p.currency_code
Соединение standalone-таблицы и логической таблицы
-- запрос данных из логической таблицы clients и standalone-таблицы, на которую указывает
-- внешняя readable-таблица agreements_ext_read_adp
SELECT a.id, a.client_id, c.last_name, c.first_name, c.patronymic_name
FROM marketing.agreements_ext_read_adp AS a
LEFT JOIN marketing.clients FOR SYSTEM_TIME AS OF delta_num 9 AS c
ON a.client_id = c.id
Запрос данных из партиционированных таблиц
Запрос из партиционированной таблицы с условием:
SELECT * FROM marketing.sales_partitioned
WHERE id > 100
AND transaction_date BETWEEN '2023-01-01 00:00:00' AND '2023-01-17 23:59:59'
Запрос из партиционированной таблицы без условия:
SELECT * FROM marketing.sales_partitioned
ORDER BY transaction_date DESC
LIMIT 10
Запрос данных из соединения двух партиционированных таблиц с условием (задействованные партиции расположены в датасорсах adp_moscow
и adp_spb
):
SELECT
st.id AS store_id,
st.region_code as store_region,
st.address AS store_address,
st.rent_agreement_id AS agreement_id,
r.number AS agreement_number,
r.closing_date AS agreement_closing_date
FROM marketing.stores_partitioned_by_regions AS st
JOIN marketing.rent_agreements_partitioned_by_regions AS r
ON st.rent_agreement_id = r.id
WHERE st.region_code IN (77, 177, 97, 197, 99, 199, 777, 78, 98, 178)
Запрос данных из партиции
SELECT * FROM marketing.sales_jan_2023
WHERE product_code <> 'ABC111' OR product_code IS NULL
Запрос данных, включая системные столбцы sys_from и sys_to
SELECT *, sys_from, sys_to FROM marketing.sales
На рисунке ниже показан пример ответа на запрос.
Ответ с системными столбцами sys_from и sys_to
Запрос данных с фильтром по системному столбцу sys_from
SELECT * FROM marketing.sales WHERE sys_from > 9
На рисунке ниже показан пример ответа на запрос.
Ответ со строками, отфильтрованными по значению системного столбца sys_from