SELECT

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

Поддерживается в версиях:  6.1 / 6.0 / 5.8 / 5.7 / 5.6.1 / 5.5 / 5.4 / 5.3 / 5.2 / 5.1 / 5.0.

Запрос возвращает данные из следующих сущностей и их соединений:

Синтаксис чтения из standalone-таблицы подразумевает использование внешней readable-таблицы, которая указывает на нужную standalone-таблицу.

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

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

SELECT-запрос обрабатывается в порядке, описанном в разделе Порядок обработки запросов на чтение данных.

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

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

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

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

Синтаксис 

SELECT column_list
FROM [db_name.]entity_name
[FOR SYSTEM_TIME time_expression [AS alias_name]]
[DATASOURCE_TYPE = datasource_alias]
[ESTIMATE_ONLY]

Параметры:

column_list

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

db_name

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

entity_name

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

time_expression

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

alias_name

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

datasource_alias

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

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

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

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

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

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

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

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

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

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

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

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

FOR SYSTEM_TIME AS OF 'YYYY-MM-DD hh:mm:ss'

Запрос к версии данных, которая соответствует указанной метке времени. Возможные форматы даты и времени см. в разделе Форматы даты и времени в запросах.

FOR SYSTEM_TIME AS OF DELTA_NUM delta_num

Запрос к версии данных, которая соответствует дате и времени закрытия дельты с номером delta_num.

FOR SYSTEM_TIME AS OF LATEST_UNCOMMITTED_DELTA

Запрос к текущей версии данных, включая данные горячей дельты. По горячей дельте возвращаются записи, добавленные в рамках непрерывного диапазона завершенных операций записи (см. параметры cn_from и cn_to в разделе GET_DELTA_HOT).

FOR SYSTEM_TIME STARTED IN (delta_num1, delta_num2)

Запрос к добавленным и измененным данным, которые имеют метку времени в диапазоне [метка времени дельты delta_num1, метка времени дельты delta_num2].

FOR SYSTEM_TIME FINISHED IN (delta_num1, delta_num2)

Запрос к удаленным данным, которые имеют метку времени в диапазоне [метка времени дельты delta_num1, метка времени дельты delta_num2].

FOR SYSTEM_TIME AS OF CN sys_cn

Запрос к версии данных с номером sys_cn.

FOR SYSTEM_TIME STARTED CN (sys_cn1, sys_cn2)

Запрос к данным, добавленным или измененным с операции sys_cn1 по операцию sys_cn2 (обе включительно).

FOR SYSTEM_TIME FINISHED CN (sys_cn1, sys_cn2)

Запрос к данным, удаленным с операции sys_cn1 по операцию sys_cn2 (обе включительно).

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

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

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

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

Ключевое слово FOR SYSTEM_TIME недоступно в SELECT-подзапросах (в составе запросов на создание и изменение представлений).

Выражение Логические таблицы Логические представления Мат. представления
FOR SYSTEM_TIME AS OF
'YYYY-MM-DD hh:mm:ss'		 + + +
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)		 + – –

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

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

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

В запросах, предназначенных для датасорсов с СУБД ADG, доступно соединение одного типа — [INNER] JOIN.

Данные соединяемых сущностей должны храниться хотя бы в одном общем датасорсе, иначе соединение невозможно. Например, можно соединить логическую таблицу, размещенную в датасорсе adb, с таблицей, размещенной в датасорсах adb и adg2 (соединение выполняется в adb), но невозможно соединить таблицу, размещенную в adb, с таблицей, размещенной в adp.

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

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

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

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

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

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

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

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

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

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

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

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

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

В ответе возвращается объект 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"
}

Ограничения

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

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

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

  • Ключевое слово LISTAGG недоступно в запросах к датасорсам с СУБД ADQM и ADG.
  • Ключевое слово ESTIMATE_ONLY не возвращает план выполнения запроса для датасорсов с СУБД ADQM и ADG.
  • Ключевое слово OFFSET без ограничения количества строк не поддерживается.
  • FOR SYSTEM_TIME:
    • Запросы к standalone-таблицам с выражениями FOR SYSTEM_TIME STARTED IN/CN и FOR SYSTEM_TIME FINISHED IN/CN всегда возвращают пустой результат; другие значения 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.

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

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

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

  • Запрос без 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.

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 FETCH NEXT 20 ROWS ONLY OFFSET 9

Описание ключевого слова см. в секции Ключевое слово 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

Соединение таблиц из разных логических БД

Запрос с соединением данных логических таблиц из двух разных логических БД:

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

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

Запрос из standalone-таблицы 

-- запрос данных из standalone-таблицы, на которую указывает внешняя readable-таблица payments_ext_read_adg
SELECT p.agreement_id, p.code, SUM(p.amount) AS amount, p.currency_code 
FROM marketing.payments_ext_read_adg 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