SELECT

Содержание раздела
  1. Выбор версии данных
  2. Как запрос выбирает данные
    1. Выбор непартиционированных данных
    2. Выбор партиционированных данных
  3. Статистика
  4. Синтаксис
    1. WITH
    2. FOR SYSTEM_TIME
      1. FOR SYSTEM_TIME AS OF ‘timestamp’
      2. FOR SYSTEM_TIME AS OF DELTA_NUM delta_num
      3. FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA
      4. FOR SYSTEM_TIME STARTED IN (delta_num1, delta_num2)
      5. FOR SYSTEM_TIME FINISHED IN (delta_num1, delta_num2)
      6. FOR SYSTEM_TIME AS OF CN sys_cn
      7. FOR SYSTEM_TIME STARTED CN (sys_cn1, sys_cn2)
      8. FOR SYSTEM_TIME FINISHED CN (sys_cn1, sys_cn2)
      9. FOR SYSTEM_TIME STARTED TS (datetime1, datetime2)
      10. FOR SYSTEM_TIME FINISHED TS (datetime1, datetime2)
      11. Поддерживаемые сущности
      12. Способы указания значения FOR SYSTEM_TIME
      13. Особенности выражений со STARTED и FINISHED
    3. JOIN
    4. WHERE
      1. Сравнение кортежей (ROW)
      2. Операторы, доступные для партиционированных таблиц
    5. GROUP BY
    6. HAVING
    7. UNION
    8. INTERSECT
    9. EXCEPT
    10. ORDER BY
    11. LIMIT
    12. OFFSET
    13. FETCH
    14. DATASOURCE_TYPE
    15. ESTIMATE_ONLY
      1. Формат ответа
      2. Пример ответа из ADB
      3. Пример ответа из ADP
    16. Поддерживаемые функции
    17. LISTAGG
    18. Функции по управлению числовыми последовательностями
      1. NEXTVAL
      2. SETVAL
      3. CURRVAL
    19. Функции по управлению текстовым поиском
      1. TO_TSVECTOR
      2. TO_TSQUERY
      3. PLAINTO_TSQUERY
      4. PHRASETO_TSQUERY
      5. WEBSEARCH_TO_TSQUERY
  5. Варианты ответа
  6. Ограничения
    1. Ограничения сущностей
    2. Ограничения ключевых слов
    3. Ограничения соединений
    4. Ограничения партиционирования
    5. Другие ограничения
  7. Примеры
    1. Звездочка и WHERE
    2. DATASOURCE_TYPE
    3. GROUP BY, ORDER BY и LIMIT
    4. SELECT без таблицы
    5. OFFSET
    6. ORDER BY, LIMIT и OFFSET
    7. FOR SYSTEM_TIME AS OF DELTA_NUM
    8. FOR SYSTEM_TIME AS OF CN
    9. FOR SYSTEM_TIME STARTED TS
    10. Соединение таблиц из разных логических БД
    11. Соединение изменений по выбранным полям
    12. Запрос изменений дельты из логического представления
    13. Запрос данных из прокси-таблицы
    14. Запрос данных из standalone-таблицы
    15. Соединение standalone-таблицы и логической таблицы
    16. Запрос данных из партиционированных таблиц
    17. Запрос данных из партиции
    18. Запрос данных, включая системные столбцы sys_from и sys_to
    19. Запрос данных с фильтром по системному столбцу sys_from
    20. Запрос с именованными параметрами
    21. Запрос с индексированными параметрами

Поддерживается в версиях: 7.1 / 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-таблицы предполагает использование внешней readable-таблицы, которая указывает на нужную standalone-таблицу.

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

Выбор версии данных

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

Прокси- и standalone-таблицы не поддерживают версионирование данных, поэтому запросы к ним с ключевым словом FOR SYSTEM_TIME возвращают либо пустой результат (для FOR SYSTEM_TIME STARTED/FINISHED IN/CN/TS), либо текущее состояние данных.

Как запрос выбирает данные

Выбор непартиционированных данных

Запрос к сущностям, которые хранят непартиционированные данные, возвращает данные из датасорса:

  • указанного в запросе или наиболее оптимального — если данные выгружаются из логических таблиц, логических представлений без партиционированных данных и материализованных представлений;
  • содержащего таблицу-источник — если данные выгружаются из прокси-таблицы или standalone-таблицы, а также их соединений с другими сущностями.

Выбор партиционированных данных

Запрос к партиционированной таблице автоматически перенаправляется в задействованные партиции и возвращает объединенную выборку по этим партициям из всех датасорсов, выбранных для исполнения запроса. Подробнее о выборе датасорсов см. в разделе Маршрутизация запросов к данным.

Запрос напрямую к партиции выбирает данные из указанной партиции.

Статистика

Запросы SELECT учитываются в категории READ статистики. Подробнее о категориях запросов в статистике см. в разделе GET_ENTITY_STATISTICS, о способах просмотра статистики — в разделе Управление статистикой.

Синтаксис 

[ 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 ]

Ключевые слова имеют ограничения, перечисленные в секции ограничений.

В некоторых выражениях значение должно быть приведено к ожидаемому с помощью CAST. Примеры доступны в разделе Поддержка SQL.

Параметры:

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, далее — табличное выражение), используемых в основном запросе.

Табличные выражения доступны в запросах к СУБД ADB и ADP.
Рекурсивные и материализованные табличные выражения не поддерживаются.

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

В табличных выражениях можно использовать поддерживаемые ключевые слова, в том числе FOR SYSTEM_TIME, а также другие табличные выражения того же запроса. В табличных выражениях недоступно использование операторов, изменяющих данные: INSERT, UPSERT и DELETE.

Синтаксис:

WITH cte_name [(cte_column_name[, ... ])] AS (cte_select)

Где:

  • cte_name — имя обобщенного табличного выражения;
  • cte_column_name — имя столбца, выбираемого из подзапроса cte_select для создания обобщенного табличного выражения. Если столбцы не указаны, в табличном выражении используются столбцы подзапроса cte_select;
  • cte_select — SELECT-подзапрос, определяющий состав обобщенного табличного выражения.

Пример запроса:

-- запрос данных о продажах в наиболее популярных магазинах с двумя табличными выражениями
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 можно указать в любом из форматов:

-- изменения за период с указанием строковых меток времени
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 можно указать в любом из форматов:

-- изменения за период с указанием строковых меток времени
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)		 + +
Кроме ADQM и случаев, когда подзапрос представления содержит ORDER BY		 +
Кроме ADQM и случаев, когда представление еще не синхронизировалось до delta_num2
FOR SYSTEM_TIME
FINISHED IN
(delta_num1, delta_num2)		 + +
Кроме ADQM и случаев, когда подзапрос представления содержит ORDER BY		 +
Кроме ADQM и случаев, когда представление еще не синхронизировалось до 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)		 + +
Кроме ADQM и случаев, когда подзапрос представления содержит ORDER BY		 +
Кроме случаев, когда datetime2 меньше метки времени дельты 0
FOR SYSTEM_TIME
FINISHED TS
(datetime1, datetime2)		 + +
Кроме ADQM и случаев, когда подзапрос представления содержит ORDER BY		 +
Кроме случаев, когда datetime2 меньше метки времени дельты 0

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

Ключевое слово 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, а не наоборот.

Условие может включать любые столбцы задействованных сущностей, включая системные столбцы sys_from, sys_to и sys_op логических таблиц и материализованных представлений. Столбцы указываются отдельно или в составе кортежа.

Синтаксис:

WHERE condition

Пример запроса:

SELECT * FROM marketing.sales
WHERE store_id = 123

Сравнение кортежей (ROW)

Условие может содержать кортежи, сравниваемые друг с другом. Каждый кортеж — это список выражений на основе столбцов, констант и (или) функций в круглых скобках. Все кортежи в условии должны иметь одинаковое количество элементов.

Условия с кортежами поддерживаются для СУБД ADB и ADP.

При вычислении условия каждый элемент первого кортежа сравнивается с элементами, имеющими тот же порядковый номер в других кортежах. Например, условие WHERE (store_id, product_units) = (123, 4) проверяет, что store_id = 123 и product_units = 4.

Особенности сравнения кортежей:

  • результат равен NULL, если хотя бы одно из сравниваемых значений равно NULL;
  • сравнение с операторами <, <=, >, >=, BETWEEN и NOT BETWEEN работает, как в PostgreSQL, по принципу сортировки строк, и может возвращать разные результаты при разном порядке перечисления элементов в кортежах.

Синтаксис:

-- условие с оператором сравнения
WHERE [ROW] (expression[, ... ]) { = | <> | > | >= | < | <= } [ROW] (expression[, ... ])

-- условие с оператором IN или NOT IN
WHERE [ROW] (expression[, ... ]) { IN | NOT IN } ( [ROW] (expression[, ... ])[, ... ] )

-- условие с оператором BETWEEN или NOT BETWEEN
WHERE [ROW] (expression[, ... ]) { BETWEEN | NOT BETWEEN } [ROW] (expression[, ... ]) AND [ROW] (expression[, ... ])

Примеры запросов:

-- запрос с условием, содержащим столбцы и константы
SELECT * FROM marketing.sales 
WHERE ROW (transaction_date, store_id) >= ROW ('2021-01-01 00:00:00.000', 123)
DATASOURCE_TYPE = 'adp';

-- запрос с условием, содержащим столбцы и индексированные параметры (см. cURL-запрос ниже)
SELECT * FROM marketing.sales 
WHERE (product_code, store_id) IN ((?, ?), (?, ?), (?, ?), (?, ?))
DATASOURCE_TYPE = 'adp';

-- запрос с условием, содержащим столбцы и именованные параметры (см. cURL-запрос ниже)
SELECT * FROM marketing.sales
WHERE ROW (transaction_date, store_id, product_units)
  BETWEEN ROW (:transaction_date_start, :store_id_start, :product_units_start)
  AND ROW (:transaction_date_end, :store_id_end, :product_units_end)
DATASOURCE_TYPE = 'adp';
Пример cURL-запроса с индексированными параметрами в кортеже 
curl -X 'POST' \
  'http://localhost:9090/api/v1/datamarts/marketing/query?format=json' \
  -H 'x-request-id: 53ce2008-5de8-477d-92be-8824356a3046' \
  -H 'Content-Type: application/json' \
  -d '{
  "query": "SELECT * FROM sales WHERE (product_code, store_id) IN ((?, ?), (?, ?), (?, ?), (?, ?)) DATASOURCE_TYPE = 'adp'",
  "queryId": "9284746",   
  "params": [     
    {         
      "value": "ABC1835",       
      "type": "STRING"
    }, 
    {
      "value": 117,
      "type": "LONG"
    },
    {         
      "value": "ABC1835",       
      "type": "VARCHAR"
    }, 
    {
      "value": 120,
      "type": "LONG"
    },
    {         
      "value": "ABC1212",       
      "type": "STRING"
    }, 
    {
      "value": 117,
      "type": "LONG"
    },
    {         
      "value": "ABC1212",       
      "type": "STRING"
    }, 
    {
      "value": 120,
      "type": "LONG"
    }
  ] 
}'
Пример cURL-запроса с именованными параметрами в кортеже 
curl -X 'POST' \
  'http://localhost:9090/api/v1/datamarts/marketing/query?format=json' \
  -H 'x-request-id: b14168c4-1349-46d7-a08e-7a244e274b02' \
  -H 'Content-Type: application/json' \
  -d '{
  "query": "SELECT * FROM marketing.sales WHERE ROW (transaction_date, store_id, product_units) BETWEEN ROW (:transaction_date_start, :store_id_start, :product_units_start) AND ROW (:transaction_date_end, :store_id_end, :product_units_end) DATASOURCE_TYPE = 'adp';",
  "queryId": "928737892",   
  "params": [     
    {         
      "name": "transaction_date_start",
      "value": "2020-05-10 13:12:09",       
      "type": "TIMESTAMP"
    }, 
    {         
      "name": "transaction_date_end",
      "value": "2025-05-10 13:12:09",       
      "type": "TIMESTAMP"
    }, 
    {
      "name": "store_id_start",
      "value": 1,
      "type": "LONG"
    },
    {
      "name": "store_id_end",
      "value": 500,
      "type": "LONG"
    },
    {
      "name": "product_units_start",
      "value": 1,
      "type": "LONG"
    },
    {
      "name": "product_units_end",
      "value": 30,
      "type": "LONG"
    }
  ] 
}'

Операторы, доступные для партиционированных таблиц

В запросах к партиционированным таблицам условие WHERE может содержать операторы:

  • >,
  • >=,
  • <,
  • <=,
  • =,
  • IN,
  • BETWEEN,
  • IS TRUE|FALSE|NULL,
  • IS NOT TRUE|FALSE.

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

Функция объединяет значения в одну строку, перечисляя их через заданный разделитель. После последнего значения разделитель не добавляется. Строки со значением 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.

Ограничения ключевых слов

  • Ключевое слово 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

Запрос с именованными параметрами

Запрос с именованными параметрами в функции текстового поиска TO_TSQUERY и ключевых словах LIMIT и OFFSET:

SELECT * FROM marketing.sales 
WHERE to_tsvector('russian', description) @@ to_tsquery('russian', :search_item) 
ORDER BY transaction_date 
LIMIT :limit_value
OFFSET :offset_value
DATASOURCE_TYPE = 'adp'

Пример полного cURL-запроса:

curl -X 'POST' \
  'http://localhost:9090/api/v1/datamarts/marketing/query?format=json' \
  -H 'x-request-id: 33e53112-9637-4622-be63-082ce49cf741' \
  -H 'Content-Type: application/json' \
  -d '{
  "query": "SELECT * FROM marketing.sales WHERE to_tsvector('russian', description) @@ to_tsquery('russian', :search_item) ORDER BY transaction_date LIMIT :limit_value OFFSET :offset_value DATASOURCE_TYPE = 'adp'",
  "queryId": "726449",
  "params": [
    {
      "name": "search_item",
      "value": "акция & (лето | зима)",
      "type": "STRING"
    },
    {
      "name": "limit_value",
      "value": 20,
      "type": "LONG"
    },
    {
      "name": "offset_value",
      "value": 40,
      "type": "LONG"
    }
  ]
}'

Запрос с индексированными параметрами

Запрос с индексированными параметрами в функции текстового поиска TO_TSQUERY и ключевых словах LIMIT и OFFSET:

SELECT * FROM marketing.sales 
WHERE to_tsvector('russian', description) @@ to_tsquery('russian', ?) 
ORDER BY transaction_date 
LIMIT ?
OFFSET ?
DATASOURCE_TYPE = 'adp'

Пример cURL-запроса:

curl -X 'POST' \
  'http://localhost:9090/api/v1/datamarts/marketing/query?format=json' \
  -H 'x-request-id: 33e53112-9637-4622-be63-082ce49cf741' \
  -H 'Content-Type: application/json' \
  -d '{
  "query": "SELECT * FROM marketing.sales WHERE to_tsvector('russian', description) @@ to_tsquery('russian', ?) ORDER BY transaction_date LIMIT ? OFFSET ? DATASOURCE_TYPE = 'adp'",
  "queryId": "726449",
  "params": [
    {
      "value": "акция & (лето | зима)",
      "type": "STRING"
    },
    {
      "value": 20,
      "type": "LONG"
    },
    {
      "value": 40,
      "type": "LONG"
    }
  ]
}'