SELECT

Содержание раздела
  1. Выбор версии данных
  2. Применение запроса
    1. Выбор непартиционированных данных
    2. Выбор партиционированных данных
  3. Статистика
  4. Синтаксис
    1. Поддерживаемые ключевые слова
    2. Ключевое слово WITH (табличное выражение)
    3. Ключевое слово LISTAGG
    4. Ключевое слово FOR SYSTEM_TIME
      1. Возможные значения FOR SYSTEM_TIME
      2. Доступность значений FOR SYSTEM_TIME
    5. Поддерживаемые типы соединений
    6. Ключевое слово WHERE
    7. Ключевое слово OFFSET
    8. Ключевое слово DATASOURCE_TYPE
    9. Ключевое слово ESTIMATE_ONLY
  5. Варианты ответа
  6. Ограничения
    1. Ограничения сущностей
    2. Ограничения ключевых слов
    3. Ограничения соединений
    4. Ограничения партиционирования
    5. Другие ограничения
  7. Примеры
    1. Звездочка и WHERE
    2. DATASOURCE_TYPE
    3. GROUP BY, ORDER BY и LIMIT
    4. ESTIMATE_ONLY
    5. SELECT без таблицы
    6. LISTAGG
      1. Запрос плоского списка
      2. Запрос списка по группам
    7. OFFSET
    8. ORDER BY, LIMIT и OFFSET
    9. WITH
    10. FOR SYSTEM_TIME AS OF DELTA_NUM
    11. FOR SYSTEM_TIME AS OF CN
    12. FOR SYSTEM_TIME STARTED TS
    13. Соединение таблиц из разных логических БД
    14. Соединение изменений дельты для двух таблиц
    15. Соединение изменений по выбранным полям
    16. Запрос изменений дельты из логического представления
    17. Запрос данных из прокси-таблицы
    18. Запрос данных из standalone-таблицы
    19. Соединение standalone-таблицы и логической таблицы
    20. Запрос данных из партиционированных таблиц
    21. Запрос данных из партиции

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

  • список имен столбцов сущности,
  • символ * для выбора всех столбцов сущности,
  • сочетание имен столбцов, констант, операторов и SQL-функций.
db_name

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

entity_name

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

select2

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

time_expression

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

alias_name

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

datasource_alias

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

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

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

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

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

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

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

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

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

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

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

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

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

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

По умолчанию результаты не сортируются, для их сортировки укажите условие сортировки.

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

Синтаксис:

LISTAGG (expression, separator) [WITHIN GROUP (ORDER BY <order_by_expression_list> [ ASC | DESC ])]

Параметры:

  • expression — выражение для выбора значений. Может иметь любой тип. Перед объединением все значения приводятся системой к типу VARCHAR;
  • separator — разделитель между значениями. Может быть любым выражением, кроме символа перевода строки;
  • order_by_expression_list — выражение для сортировки результатов. По умолчанию результаты сортируются в порядке ASC (по возрастанию).

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

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

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

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

Ключевое слово недоступно в запросах к прокси-таблицам и standalone-таблицам.

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

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

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

Возможные значения FOR SYSTEM_TIME

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 .

-- изменения по диапазону дельт
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.

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

Если операция записи sys_cn1 или sys_cn2 еще выполняется, результаты выдачи FOR SYSTEM_TIME AS OF CN/STARTED CN/FINISHED CN по этим операциям могут быть не согласованы.

FOR SYSTEM_TIME STARTED TS (datetime1, datetime2)

Записи, добавленные и (или) измененные в период времени с datetime1 по datetime2, включая обе границы.
Выражение может содержать время 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);
FOR SYSTEM_TIME FINISHED TS (datetime1, datetime2)

Записи, удаленные в период времени с datetime1 по datetime2, включая обе границы.
Выражение может содержать время 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);

Запросы с выражениями FOR SYSTEM_TIME STARTED/FINISHED IN/CN/TS возвращают разницу в состоянии данных за период, а не весь набор последовательных изменений данных за это время. Например, у клиента два раза менялся номер телефона — в дельте 3 и дельте 6. При запросе данных, добавленных и измененных в дельтах [3, 6], для этого клиента вернется только одна запись — с итоговым номером телефона. Промежуточного номера телефона в ответе не будет.

Доступность значений FOR SYSTEM_TIME

В таблице ниже описана доступность значений FOR SYSTEM_TIME в SELECT-запросах к таблицам и представлениям.

Ключевое слово 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		 +
Если в представлении есть обе дельты диапазона
FOR SYSTEM_TIME
FINISHED IN
(delta_num1, delta_num2)		 + +
Если запрос предназначен для СУБД ADP, ADB или ADG и в представлении не используется ORDER BY		 +
Если в представлении есть обе дельты диапазона
FOR SYSTEM_TIME AS OF
CN sys_cn		 + – –
FOR SYSTEM_TIME
STARTED CN
(sys_cn1, sys_cn2)		 + – –
FOR SYSTEM_TIME
FINISHED CN
(sys_cn1, sys_cn2)		 + – –
FOR SYSTEM_TIME
STARTED TS
(datetime1, datetime2)		 + + +
FOR SYSTEM_TIME
FINISHED TS
(datetime1, datetime2)		 + + +

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

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

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

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

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

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

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

В запросах к партиционированным таблицам условие 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"
}

Варианты ответа

В ответе возвращается:

  • объект ResultSet c выбранными записями или информацией о запросе при успешном выполнении запроса;
  • исключение при неуспешном выполнении запроса.

Ограничения

Ограничения сущностей

  • В запросе можно обращаться либо к логической БД, либо к сервисной БД (см. SELECT FROM INFORMATION_SCHEMA), но не к обеим одновременно.
  • Не поддерживаются запросы к материализованным представлениям, содержащие ключевые слова FOR SYSTEM_TIME + DATASOURCE_TYPE, если в представлении отсутствуют данные за момент времени, указанный с помощью FOR SYSTEM_TIME.
  • Не поддерживаются запросы к внешним readable-таблицам, связанным с топиками Kafka.

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

  • Ключевое слово LISTAGG доступно для СУБД ADB и ADP.
  • Ключевое слово ESTIMATE_ONLY возвращает план выполнения запроса только для СУБД ADB и ADP.
  • Ключевое слово OFFSET без ограничения количества строк не поддерживается.
  • FOR SYSTEM_TIME:
    • Ключевое слово недоступно в SELECT-подзапросах в составе запросов на создание и изменение логических и материализованных представлений, а также в запросах и подзапросах к обобщенным табличным выражениям.
    • Запросы к прокси-таблицам и standalone-таблицам с выражениями FOR SYSTEM_TIME STARTED IN/CN/TS и FOR SYSTEM_TIME FINISHED IN/CN/TS всегда возвращают пустой результат; другие значения FOR SYSTEM_TIME в таких запросах игнорируются.
    • Выражение FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA недоступно в запросах к логическим и материализованным представлениям.
    • Выражения FOR SYSTEM_TIME STARTED IN и FOR SYSTEM_TIME FINISHED IN недоступны в запросе к логическому представлению, если запрос предназначен для СУБД ADQM или в представлении используется ключевое слово ORDER BY.
  • Ключевое слово ORDER BY недоступно в SELECT-подзапросе в составе CREATE MATERIALIZED VIEW.
  • WITH (табличные выражения):
    • Табличные выражения доступны для СУБД ADB и ADP.
    • Недоступны рекурсивные и материализованные табличные выражения.
    • В табличных выражениях не поддерживаются операторы, изменяющих данные (INSERT, UPSERT и DELETE), и ключевое слово DATASOURCE_TYPE.

Ограничения соединений

  • Не поддерживается соединение сущностей, данные которых размещены в разных датасорсах без какого-либо общего датасорса.
  • Если ключами соединения в запросе выступают поля типа Nullable, то строки, где хотя бы один из ключей имеет значение NULL, не соединяются.

Ограничения партиционирования

  • JOIN-соединение партиционированных таблиц между собой и с другими данными возможно только в пределах единого датасорса.

Другие ограничения

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

Примеры

Звездочка и WHERE

Запрос с неявным указанием столбцов и ключевым словом WHERE:

SELECT * FROM marketing.sales
WHERE store_id = 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)

LISTAGG

Запрос плоского списка

Запрос списка ФИО клиентов с их датой рождения:

SELECT LISTAGG (last_name || ' ' || first_name || ' ' || patronymic_name || ' ' || birth_date , ', ')
WITHIN GROUP (ORDER BY last_name, first_name, patronymic_name DESC)
FROM marketing.clients datasource_type = 'adp'

Ниже на рисунке показан пример ответа на такой запрос:

Пример результата LISTAGG с плоским списком

Запрос списка по группам

Запрос списка адресов магазинов с группировкой по городам:

SELECT region, LISTAGG (address, '; ')
WITHIN GROUP (ORDER BY address)
FROM marketing.stores
GROUP BY region
ORDER BY region ASC
DATASOURCE_TYPE = 'adb'

Ниже на рисунке показан пример ответа на такой запрос:

Пример результата LISTAGG с группировкой

Описание ключевого слова см. в секции Ключевое слово LISTAGG.

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

О возможных типах соединений см. в секции Поддерживаемые типы соединений.

Соединение изменений дельты для двух таблиц

Запрос с соединением записей, добавленных и измененных в логических таблицах sales и stores в дельте 2:

SELECT st.id, st.category, s.product_code 
FROM marketing.stores FOR SYSTEM_TIME STARTED IN (2,2) AS st 
INNER JOIN marketing.sales FOR SYSTEM_TIME STARTED IN (2,2) 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