Select в select postgresql: PostgreSQL : Документация: 9.6: SELECT : Компания Postgres Professional

Login

Password

Forgot password?

Copyright SQL-EX © 2002-2023. All rights reserved. contact

WITH Queries (Common Table Expressions)

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

WITH предоставляет способ записи подзапросы для использования в более крупном SELECT запрос. Подзапросы, которые часто называют общими Табличные выражения или CTE могут можно рассматривать как определение временных таблиц, которые существуют только для этот запрос. Одно из применений этой функции — разбить сложные запросы на более простые части. Пример:

С Regional_sales AS (
        ВЫБЕРИТЕ регион, СУММА (сумма) КАК total_sales
        ОТ заказов
        СГРУППИРОВАТЬ ПО РЕГИОНАМ
     ), топ_регионы AS (
        ВЫБЕРИТЕ регион
        ОТ регионарных_продаж
        ГДЕ total_sales >
  (ВЫБЕРИТЕ СУММУ(total_sales)/10 FROM Regional_sales)
     )
ВЫБЕРИТЕ регион,
       продукт,
       SUM(количество) AS product_units,
       СУММА(сумма) КАК product_sales
ОТ заказов
ГДЕ регион В (ВЫБЕРИТЕ регион ИЗ top_regions)
СГРУППИРОВАТЬ ПО региону, продукту;
 

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

Необязательный модификатор RECURSIVE меняется на WITH с простого синтаксического удобство в функцию, которая выполняет вещи иначе возможно в стандартном SQL. Использование RECURSIVE, запроса WITH может ссылаться на собственный вывод. Очень простой пример — этот запрос суммировать целые числа от 1 до 100:

С РЕКУРСИВНЫМ t(n) КАК (
    ЦЕННОСТИ (1)
  СОЮЗ ВСЕХ
    ВЫБЕРИТЕ n+1 ИЗ t, ГДЕ n < 100
)
ВЫБЕРИТЕ сумму (n) ОТ t;
 

Общая форма рекурсивного оператора WITH запрос всегда нерекурсивный терм , затем СОЮЗ (или СОЮЗ ВСЕ), затем рекурсивных терминов , где только рекурсивный терм может содержать ссылку на запрос собственный выход. Такой запрос выполняется следующим образом:

Рекурсивная оценка запроса

1. Оценить нерекурсивный терм. Для UNION (но не UNION ВСЕ), отбросить повторяющиеся строки. Включить все оставшиеся строк в результате рекурсивного запроса, а также разместить их во временном

   рабочий таблица .

2. Пока рабочий стол не пуст, повторяйте эти шагов:

   1. Вычислите рекурсивный термин, заменив текущее содержимое рабочей таблицы для рекурсивного ссылка на самого себя. Для UNION (но не UNION ALL), отбросить повторяющиеся строки и строки, которые дублируют любые предыдущие строка результата. Включить все оставшиеся строки в результат рекурсивный запрос, а также поместить их во временную промежуточная таблица .

   2. Заменить содержимое рабочей таблицы на содержимое промежуточной таблицы, затем очистите промежуточный стол.

Примечание: Строго говоря, этот процесс является итерацией не рекурсия, а RECURSIVE - это терминология, выбранная комитетом по стандартам SQL.

В приведенном выше примере рабочая таблица имеет только одну строку. на каждом шаге и принимает значения от 1 до 100 в последовательные шаги. На 100-м шаге нет вывода, потому что предложения WHERE, поэтому запрос заканчивается.

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

С РЕКУРСОВЫМ включенным_частям (под_часть, часть, количество) КАК (
    ВЫБЕРИТЕ под_часть, часть, количество ИЗ частей, ГДЕ часть = 'наш_продукт'
  СОЮЗ ВСЕХ
    ВЫБЕРИТЕ p.sub_part, p.part, p.quantity
    ИЗ включенных_деталей пр, частей п
    ГДЕ p.
  part = pr.sub_part
  )
SELECT sub_part, SUM(количество) как total_quantity
ИЗ включенных_деталей
СГРУППИРОВАТЬ ПО sub_part
 

При работе с рекурсивными запросами важно быть уверенным что рекурсивная часть запроса в конечном итоге не вернет кортежи, иначе запрос будет бесконечно повторяться. Иногда, использование UNION вместо UNION ALL может выполнить это, отбрасывая строки которые дублируют предыдущие выходные строки. Однако часто цикл не включать в себя выходные строки, которые полностью повторяются: это может быть необходимо проверить только одно или несколько полей, чтобы увидеть, совпадают ли точка была достигнута раньше. Стандартный способ обработки таких ситуациях заключается в вычислении массива уже посещенных ценности. Например, рассмотрим следующий запрос, который ищет табличный график с использованием поля ссылки:

С РЕКУРСИВНЫМ search_graph(идентификатор, ссылка, данные, глубина) КАК (
        ВЫБРАТЬ g.id, g.link, g.data, 1
        С графа г
      СОЮЗ ВСЕХ
        ВЫБЕРИТЕ g.  id, g.link, g.data, sg.depth + 1
        FROM граф г, search_graph sg
        ГДЕ g.id = sg.link
)
SELECT * FROM search_graph;
 

Этот запрос зациклится, если ссылка отношения содержат циклы. Поскольку нам требуется вывод «глубины», простое изменение UNION ALL на UNION приведет к не устранить зацикливание. Вместо этого мы должны признать, что мы снова достигли той же строки, следуя определенному путь ссылок. Мы добавляем два столбца пути и цикла к склонный к петле запрос:

WITH RECURSIVE search_graph(id, ссылка, данные, глубина, путь, цикл) КАК (
        ВЫБРАТЬ g.id, g.link, g.data, 1,
          МАССИВ[g.id],
          ЛОЖЬ
        С графа г
      СОЮЗ ВСЕХ
        ВЫБЕРИТЕ g.id, g.link, g.data, sg.depth + 1,
          путь || г.ид,
          g.id = ЛЮБОЙ (путь)
        FROM граф г, search_graph sg
        ГДЕ g.id = sg.link И НЕ цикл
)
SELECT * FROM search_graph;
 

Помимо предотвращения циклов, значение массива часто полезно сам по себе как представляющий «путь», по которому можно добраться до любой конкретной строки.

В общем случае, когда необходимо указать более одного поля. проверено, чтобы распознать цикл, используйте массив строк. Например, если бы нам нужно было сравнить поля f1 и f2:

WITH RECURSIVE search_graph(id, ссылка, данные, глубина, путь, цикл) КАК (
        ВЫБРАТЬ g.id, g.link, g.data, 1,
          МАССИВ[СТРОКА(g.f1, g.f2)],
          ЛОЖЬ
        С графа г
      СОЮЗ ВСЕХ
        ВЫБЕРИТЕ g.id, g.link, g.data, sg.depth + 1,
          путь || РЯД(g.f1, g.f2),
          РЯД(g.f1, g.f2) = ЛЮБОЙ(путь)
        FROM граф г, search_graph sg
        ГДЕ g.id = sg.link И НЕ цикл
)
SELECT * FROM search_graph;
 

Совет: Не используйте синтаксис ROW() в общем случае, когда нужно проверить только одно поле распознать цикл. Это позволяет использовать простой массив, а не массив составного типа, который будет использоваться, повышая эффективность.

Совет: Алгоритм оценки рекурсивного запроса производит вывод в порядке поиска в ширину. Ты можешь отображать результаты в порядке поиска в глубину, сделав внешний запрос ORDER BY для столбца "путь", построенного таким образом.

Полезный прием для тестирования запросов, когда вы не уверены если они могут зациклиться, это установить LIMIT в родительском запросе. Например, этот запрос будет зацикливаться вечно. без ОГРАНИЧЕНИЯ:

С РЕКУРСИВНЫМ t(n) КАК (
    ВЫБЕРИТЕ 1
  СОЮЗ ВСЕХ
    ВЫБРАТЬ n+1 ИЗ t
)
ВЫБЕРИТЕ n ОТ ПРЕДЕЛА 100;
 

Это работает, потому что реализация PostgreSQL оценивает только столько строк запроса WITH, сколько есть фактически извлекается родительским запросом. Используя этот прием в производство не рекомендуется, потому что другие системы могут работать иначе. Кроме того, обычно это не сработает, если вы сделаете внешний запрос сортирует результаты рекурсивного запроса или объединяет их с некоторыми другой стол.

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

Оператор SELECT для PostgreSQL

Postgres поддерживает оператор SELECT для извлечения записей из нуля или более таблиц или представлений в базе данных.

 ВЫБЕРИТЕ [ * | столбец1, столбец2,... | выражение ]
    [ FROM [таблица1 [ таблица2,. .] [представление1, представление2..]
    [ ГДЕ состояние ]
    [ СГРУППИРОВАТЬ ПО [столбец1 [столбец2,..]]]
    [ ЕСТЬ состояние ]
    [ WINDOW имя_окна КАК ( определение_окна )]
    [ ORDER BY выражение [ ASC | DESC | ИСПОЛЬЗОВАНИЕ оператора ] ]
    [ ПРЕДЕЛ { количество | ВСЕ } ]
    [ СМЕЩЕНИЕ начало [ РЯД | РЯДЫ ] ]
    [ ВЫБЕРИТЕ { ПЕРВЫЙ | СЛЕДУЮЩИЙ } [ количество ] { СТРОКА | РЯДЫ } { ТОЛЬКО | С СВЯЗЯМИ } ]
    [ДЛЯ {ОБНОВЛЕНИЯ | НЕТ ОБНОВЛЕНИЯ КЛЮЧА | ПОДЕЛИТЬСЯ | KEY SHARE } [ OF table_name] [ NOWAIT | ПРОПУСТИТЬ ЗАБЛОКИРОВАНО ]]
 

С оператором SELECT,

• Укажите одно или несколько имен столбцов после предложения SELECT, для которых вы хотите получить данные. Имена нескольких столбцов должны быть разделены запятой.
• При желании укажите выражение также с предложением SELECT.
• Укажите звездочку (*) после SELECT, чтобы выбрать все столбцы. Список выбора также может содержать выражения или литеральные значения.
• Укажите одно или несколько имен таблиц, из которых вы хотите выбрать данные, после предложения FROM.
• При необходимости используйте предложение WHERE, GROUP BY, HAVING, ORDER BY, WINDOW, LIMIT, OFFSET, FETCH, FOR.

Ключевые слова SQL нечувствительны к регистру, поэтому вы можете писать SELECT, Select или select. В целях форматирования мы сохраним все ключевые слова SQL заглавными буквами.

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

Следующая инструкция SELECT извлекает данные из одного столбца таблицы сотрудников .

 ВЫБЕРИТЕ first_name ОТ сотрудника;
 

Ниже показан результат в pgAdmin.

Следующая инструкция SELECT извлекает данные из нескольких столбцов таблицы employee .

 ВЫБЕРИТЕ emp_id, имя, фамилию ОТ сотрудника;
 

Ниже показан результат в pgAdmin.

Следующая инструкция SELECT использует * для извлечения данных из всех столбцов таблицы employee .

 ВЫБЕРИТЕ * ОТ сотрудника;
 

Ниже показан результат в pgAdmin.

Выражение с пунктом SELECT

Вы можете использовать выражение в операторе SELECT. Следующее объединяет столбцы first_name и last_name с помощью || оператор.

 ВЫБЕРИТЕ имя_имя || ' ' || фамилия ОТ сотрудника;
 

Ниже показан результат в pgAdmin.

Он также может возвращать результат основной математической операции.

 ВЫБЕРИТЕ 6 * 4;
 

Псевдоним столбца

Псевдоним позволяет присвоить временное имя столбцу или выражению в операторе SELECT.