SELECT

Содержание раздела

Выбор версии данных
Применение запроса
1. Выбор непартиционированных данных
2. Выбор партиционированных данных
Статистика
Синтаксис
Варианты ответа
Ограничения
Примеры

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

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

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

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

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

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

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

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

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

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

Статистика

Запросы SELECT учитываются в категории статистики READ. Статистика доступна с помощью запроса GET_ENTITY_STATISTICS и GET-методов получения статистики.

Синтаксис

[WITH with_query_name [(column_list)] AS (select1)]
SELECT expression
[FROM { [db_name.]entity_name | (select2) }]
[FOR SYSTEM_TIME time_expression [AS alias_name]]
[DATASOURCE_TYPE = datasource_alias]
[ESTIMATE_ONLY]

Параметры:

with_query_name

Имя обобщенного табличного выражения (Common Table Expressions или CTE).

column_list

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

select1

SELECT-подзапрос, определяющий состав обобщенного табличного выражения with_query_name.

expression

Выражение для составления результата SELECT-запроса. Может содержать любой из вариантов:

имена столбцов задействованных сущностей, включая имена системных столбцов sys_from, sys_to и sys_op логических таблиц и материализованных представлений;
символ * для выбора всех столбцов, кроме системных;
сочетание имен столбцов, констант, операторов и SQL-функций.

db_name

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

entity_name

Имя таблицы или представления, из которого выбираются данные.

select2

SELECT-подзапрос.

time_expression

Выражение, задающее момент или период времени, за который выбираются данные или изменения данных. Синтаксис см. ниже.

alias_name

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

datasource_alias

Имя датасорса, из которого выбираются данные. Указывается в одинарных кавычках (например, 'adg').

Поддерживаемые ключевые слова

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

WITH — определяет обобщенное табличное выражение (Common Table Expressions или CTE);
FOR SYSTEM_TIME — задает момент времени или период, за который выбираются данные или изменения данных. Поддерживается для логических таблиц, логических и материализованных представлений;
JOIN — соединяет данные нескольких таблиц и (или) представлений из одной или нескольких логических БД;
WHERE — задает условия выбора данных;
GROUP BY — группирует данные;
HAVING — задает условия выбора сгруппированных данных;
ORDER BY — сортирует данные;
LIMIT — ограничивает количество возвращаемых строк (действие ключевого слово аналогично действию FETCH... ROWS ONLY);
OFFSET — пропускает указанное количество строк в результате запроса;
FETCH {FIRST | NEXT} <N> ROWS ONLY — ограничивает количество возвращаемых строк (действие ключевого слово аналогично действию LIMIT);
DATASOURCE_TYPE — задает датасорс, из которого выбираются данные. Поддерживается для логических таблиц, логических и материализованных представлений;
ESTIMATE_ONLY — включает режим получения информации о запросе, а не самих данных.

Некоторые типы соединений доступны не во всех СУБД. Подробнее о доступных возможностях см. в разделе Поддержка SQL.

Ключевое слово WITH (табличное выражение)

Ключевое слово задает одно или несколько обобщенных табличных выражений (Common Table Expressions или CTE, далее — табличное выражение), используемых в основном запросе. Пример запроса с табличными выражениями см. ниже.

Ключевое слово доступно в запросах к СУБД ADB и ADP.

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

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

Ключевое слово доступно во всех видах SELECT-запросов и SELECT-подзапросов: в отдельных запросах на выборку данных, в подзапросах в составе DDL-запросов на создание логических и материализованных представлений, в подзапросах в составе INSERT SELECT и т.д.

Рекурсивные и материализованные табличные выражения не поддерживаются.

Ключевое слово FOR SYSTEM_TIME

Ключевое слово FOR SYSTEM_TIME задает момент, по состоянию на который запрашиваются данные, или период, за который запрашиваются изменения данных.

Ключевое слово относится к логической сущности, после имени которой оно следует. Для каждой сущности в запросе можно указать свое ключевое слово FOR SYSTEM_TIME.

Если ключевое слово FOR SYSTEM_TIME не указано:

из логических таблиц и логических представлений выбираются данные последней версии по текущему времени сервера (в выборку не попадают данные, которые имеют более позднюю метку времени);
из материализованных представлений выбираются данные последней версии на момент последней синхронизации этого представления.

Поддерживаемые сущности

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

логическим таблицам,
логическим представлениям,
материализованным представлениям.

Ключевое слово недоступно:

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

В таблице ниже описана доступность выражений 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)`	+	+	+ Если правая граница диапазона больше или равна метке времени дельты 0
`FOR SYSTEM_TIME` `FINISHED TS` `(datetime1, 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.

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

FOR SYSTEM_TIME AS OF ‘timestamp’

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

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

Метка времени 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.

SELECT * FROM marketing.clients FOR SYSTEM_TIME AS OF DELTA_NUM 10  

FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA

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

SELECT * FROM marketing.clients FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA  

FOR SYSTEM_TIME STARTED IN (delta_num1, delta_num2)

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

Выражение определяет записи, удаленные в диапазоне дельт. В выборку попадают записи, которые были удалены в дельтах с delta_num1 по delta_num2, включая обе границы, а также в отдельных операциях записи, выполненных после дельты delta_num1 - 1 и до дельты delta_num1 .

-- изменения по диапазону дельт
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. Если операция записи с номером 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, включая обе границы. Если указанные операции еще выполняются, результаты выдачи по ним могут быть не согласованы.

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

-- изменения по диапазону операций
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, включая обе границы.

Время 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, включая обе границы.

Время 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);

Особенности выражений со STARTED и FINISHED

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

Например, у клиента два раза менялся номер телефона — в дельте 3 и дельте 6. При запросе данных, добавленных и измененных в дельтах [3, 6], для этого клиента вернется только одна запись — с итоговым номером телефона. Промежуточный номер телефона в ответе не вернется.

Поддерживаемые типы соединений

Поддерживаются типы соединений:

[INNER] JOIN — внутреннее соединение,
NATURAL JOIN — внутреннее соединение по всем столбцам с одинаковыми именами, ключи соединения не указываются,
LEFT [OUTER] JOIN — левое внешнее соединение,
RIGHT [OUTER] JOIN — правое внешнее соединение,
FULL [OUTER] JOIN — полное внешнее соединение,
CROSS JOIN — декартово произведение таблиц или представлений, ключи соединения не указываются.

В запросах к СУБД ADG доступно соединение одного типа — [INNER] JOIN.

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

Соединение данных разных сущностей возможно, если есть хотя бы один датасорс, в котором размещены все соединяемые данные.

Ключевое слово WHERE

Ключевое слово WHERE задает условия выбора данных.

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

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

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

Ключевое слово OFFSET

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

Ключевое слово недоступно в запросах к СУБД ADG.

В качестве значения ключевого слова можно указать любое неотрицательное целое число, начиная с нуля, или переменную. Если для OFFSET указано значение 0, то пропускается 0 строк, что равносильно запросу без OFFSET.

Запросы с OFFSET без ограничения количества строк не поддерживаются. То есть, если ключевое слово OFFSET указано в запросе, то перед ним должно быть ключевое слово LIMIT <N> или после него должно быть ключевое слово FETCH... ROWS ONLY. Обратного ограничения нет: ключевые слова LIMIT <N> и FETCH... ROWS ONLY можно использовать без OFFSET.

Рекомендуется сочетать OFFSET с ключевым словом ORDER BY для получения упорядоченного набора строк. Ключевое слово ORDER BY необязательно, однако без него запрос с OFFSET возвращает неупорядоченный и потому непредсказуемый набор строк.

Таким образом, для ключевого слова OFFSET поддерживается синтаксис:

[ ORDER BY <column_name> ]
[LIMIT <value_1>]
OFFSET <value_2> [ ROW | ROWS ]
[FETCH {FIRST | NEXT} <value_1> ROWS ONLY]

Ключевое слово DATASOURCE_TYPE

Ключевое слово DATASOURCE_TYPE задает имя датасорса, из которого выбираются данные. Имя указывается в одинарных кавычках, регистр букв в имени не учитывается.

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

Ключевое слово DATASOURCE_TYPE, указанное в запросе к партиционированной таблице, применяется к партициям, выбранным для исполнения запроса, а не к самой партиционированной таблице.

Ключевое слово ESTIMATE_ONLY

Ключевое слово ESTIMATE_ONLY позволяет запросить информацию о выполнении запроса к данным, а не сами данные (см. пример ниже).

Если ключевое слово указано, запрос возвращает следующую информацию по каждому задействованному датасорсу:

имя датасорса, в котором предполагается выполнение запроса;
план выполнения запроса (для СУБД ADB и ADP) — результат выполнения команды EXPLAIN в датасорсе. Подробнее о команде EXPLAIN в СУБД ADB см. в документации Greenplum, о команде в СУБД ADP — в документации PostgreSQL;
обогащенный запрос — запрос, подготовленный системой на основе исходного запроса с учетом специфики типа датасорса.

Начиная с версии 6.1, система возвращает обогащенный запрос, содержащий символы переноса строки \n. В предыдущих версиях обогащенный запрос возвращался без этих символов.

В ответе возвращается объект ResultSet с количеством строк, равным количеству задействованных в запросе датасорсов. Каждая строка ResultSet содержит JSON-строку следующего формата:

{
  "datasource": "<имя_датасорса>",
  "estimation": "<план_выполнения_запроса>",
  "query": "<обогащенный_запрос>"
}

Ниже показан пример 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"
}

Ниже показан пример 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,
функции по управлению числовыми последовательностями,
функции по управлению текстовым поиском.

Подробнее о поддержке функций в запросах к разным СУБД см. в разделе Поддержка 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:

NEXTVAL,
SETVAL,
CURRVAL.

Система позволяет управлять числовыми последовательностями с помощью команд и функций, но сами последовательности создаются и хранятся в СУБД. Подробнее о числовых последовательностях см. в документации PostgreSQL и Greenplum.

Функция NEXTVAL

Функция NEXTVAL переводит текущее значение последовательности на следующее и возвращает результат.

Синтаксис:

NEXTVAL('[schema_name.]sequence_name')

Параметры:

schema_name — имя схемы СУБД, которой принадлежит числовая последовательность. Если не указано, используется схема СУБД по умолчанию;
sequence_name — имя числовой последовательности. Список числовых последовательностей можно получить, выполнив запрос GET_SEQUENCES.

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

SELECT nextval('marketing.counter_by_10') DATASOURCE_TYPE = 'adp2'

Дополнительные примеры использования функции см. в разделе Поддержка SQL.

Функция SETVAL

Функция 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

Функция 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.

Функции по управлению текстовым поиском

Система поддерживает следующие функции по управлению текстовым поиском:

TO_TSVECTOR,
TO_TSQUERY,
PLAINTO_TSQUERY,
PHRASETO_TSQUERY,
WEBSEARCH_TO_TSQUERY.

Поддерживаемые варианты сравнений текстовых значений в запросах:

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

Функция 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

Функция TO_TSQUERY подготавливает слова, указанные как аргумент функции, для сравнения с другим текстом. Если указано несколько слов, они должны быть соединены любыми из следующих операторов: & (AND), | (OR), ! (NOT), и (или) <-> (FOLLOWED BY). Возвращает список лексем, соединенных оператором & (AND).

Функция доступна в запросах к СУБД 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

Функция 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

Функция 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

Функция WEBSEARCH_TO_TSQUERY подготавливает неформатированный текст, указанный как аргумент функции, для сравнения с другим текстом, с сохранением порядка слов. В отличие от PLAINTO_TSQUERY и PHRASETO_TSQUERY, поддерживает операторы OR и - (NOT), а также включение слов с сохранением их порядка или без него. Возвращает список лексем, соединенных операторами & (AND), | (OR), ! (NOT), и <-> (FOLLOWED BY).

Функция доступна в запросах к СУБД 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.
Запросы к материализованным представлениям могут возвращать старые данные, если кластер работает в leaderless-режиме [deprecated]. Для гарантированного получения свежих данных из материализованного представления выполняйте команду SYNC перед каждым обращением к представлению или перенастройте кластер на работу с лидером.
Не поддерживаются запросы к внешним 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 = 1234

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

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.

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

Такое сочетание ключевых слов позволяет выбирать данные порциями с сохранением их порядка.

WITH

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

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