Обзор команды SELECT

Андрей Аксенов

Contents

Обзор
- Обзор синтаксиса
- Выполнение запроса SELECT
Примеры использования

Команда SELECT позволяет получать строки из таблиц и представлений. Она позволяет указать, данные из каких столбцов необходимо вернуть, применить фильтры и отсортировать вывод. В СУБД Greengage, предназначенной для выполнения аналитических запросов, SELECT является одной из наиболее часто используемых команд для исследования и анализа данных.

Упрощенный синтаксис команды SELECT для получения строк из таблицы выглядит следующим образом:

SELECT [ * | <column> [, ...] ]
  [FROM <table_name> [, ...]]
  [WHERE <condition>]

где:

<column> [, …] — имена одного или нескольких столбцов для выборки из таблицы или представления. Используйте *, чтобы выбрать все столбцы.
<table_name> [, …] — имя одной или нескольких таблиц или представлений (при необходимости с указанием схемы), из которых производится выборка данных.
<condition> — выражение, используемое для фильтрации строк, возвращаемых SQL-запросом.

Команда SELECT поддерживает множество дополнительных выражений для группировки, фильтрации и сортировки результатов:

[ WITH [ RECURSIVE ] <with_query> [, ...] ]
SELECT [ALL | DISTINCT [ON (<expression> [, ...])]]
  * | <expression> [[AS] <output_name>] [, ...]
  [FROM <from_item> [, ...]]
  [WHERE <condition>]
  [GROUP BY <grouping_element> [, ...]]
  [HAVING <condition> [, ...]]
  [WINDOW <window_name> AS (<window_definition>) [, ...]]
  [{UNION | INTERSECT | EXCEPT} [ALL | DISTINCT] <select>]
  [ORDER BY <expression> [ASC | DESC | USING <operator>] [NULLS {FIRST | LAST}] [, ...]]
  [LIMIT {<count> | ALL}]
  [OFFSET <start> [ROW | ROWS]]
  [FETCH {FIRST | NEXT} [<count>] {ROW | ROWS} ONLY]
  [FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE} [OF <table_name> [, ...]] [NOWAIT] [...]]

TABLE { [ONLY] <table_name> [*] | <with_query_name> }

Последовательность обработки запроса SELECT следующая:

WITH

Выполняются все запросы в выражении WITH. Фактически они служат временными таблицами, к которым можно обращаться в списке FROM.
FROM

Вычисляются все элементы в списке FROM. Каждый элемент списка FROM — это реальная или виртуальная таблица, например, подзапрос или присоединенная таблица. Если в списке FROM указано несколько элементов, они объединяются перекрестным соединением (cross join).
WHERE

Если указано выражение WHERE, из результата будут исключены все строки, не удовлетворяющие этому условию.
GROUP BY / HAVING

Если указано выражение GROUP BY или присутствуют вызовы агрегатных функций, вывод группируется по заданным ключам. Результаты агрегатных функций вычисляются для каждой группы. Если присутствует выражение HAVING, группы, не удовлетворяющие заданному условию, исключаются из вывода.
Выражения в списке SELECT

Результирующие строки формируются на основе выражений, перечисленных в списке SELECT. Выражения могут включать константы, ссылки на столбцы, вычисления, вызовы функций или оконные функции.
DISTINCT / DISTINCT ON / ALL

Эти параметры определяют, следует ли удалять повторяющиеся строки из результата и каким образом это делать:
- SELECT DISTINCT исключает из результата повторяющиеся строки.
- SELECT DISTINCT ON исключает строки, совпадающие по указанным выражениям.
- SELECT ALL (по умолчанию) возвращает все строки результата, включая дубликаты.
UNION / INTERSECT / EXCEPT

С помощью операторов UNION, INTERSECT и EXCEPT можно объединять результаты нескольких запросов SELECT в один результирующий набор. Каждый запрос должен возвращать одинаковое количество столбцов с совместимыми типами данных:
- Оператор UNION возвращает все строки, которые присутствуют хотя бы в одном из входных наборов.
- Оператор INTERSECT возвращает только те строки, которые присутствуют в обоих наборах.
- Оператор EXCEPT возвращает все строки, которые присутствуют в первом наборе, но отсутствуют во втором.
По умолчанию эти операторы удаляют повторяющиеся строки. Чтобы сохранить повторяющиеся строки, используйте ключевое слово ALL (например, UNION ALL). Обратите внимание, что по умолчанию для UNION, INTERSECT и EXCEPT используется DISTINCT, хотя для SELECT по умолчанию применяется ALL.
ORDER BY

Если указано выражение ORDER BY, возвращаемые строки сортируются в заданном порядке. Если выражение не указано, порядок может быть произвольным и зависит от плана выполнения, выбранного СУБД.
LIMIT / OFFSET / FETCH

Если указано выражение LIMIT, FETCH FIRST или OFFSET, то оператор SELECT возвращает только часть результирующих строк.
FOR UPDATE / …

Если указано FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE или FOR KEY SHARE, то оператор SELECT блокирует выбранные строки, предотвращая их изменение другими транзакциями.

ВАЖНО

Запросы, использующие ORDER BY на больших наборах данных или DISTINCT на большом числе полей, могут привести к созданию spill-файлов. Узнайте больше в статье Управление spill-файлами.

Предварительные требования

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Создайте новую базу данных и подключитесь к ней:

CREATE DATABASE marketplace;
\c marketplace

Затем создайте три связанных таблицы и заполните их данными:

categories

Создайте таблицу для хранения различных категорий товаров:

CREATE TABLE categories
(
    category_code TEXT PRIMARY KEY,
    name          TEXT
)
    DISTRIBUTED REPLICATED;

Добавьте данные:

INSERT INTO categories
VALUES ('elec', 'Electronics'),
       ('cloth', 'Clothing'),
       ('home', 'Home');

products

Создайте таблицу для хранения информации о каждом товаре, включая категорию, к которой он относится:

CREATE TABLE products
(
    id            INT,
    name          TEXT,
    category_code TEXT REFERENCES categories (category_code),
    price         NUMERIC(8, 2),
    stock         INT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

Добавьте данные:

INSERT INTO products
VALUES (1, 'Laptop', 'elec', 1200.00, 15),
       (2, 'Headphones', 'elec', 150.00, 40),
       (3, 'Coffee Maker', 'home', 85.00, 20),
       (4, 'T-Shirt', 'cloth', 25.00, 100),
       (5, 'Desk Chair', 'home', 200.00, 10),
       (6, 'Smartphone', 'elec', 800.00, 30),
       (7, 'Jacket', 'cloth', 60.00, 50),
       (8, 'Blender', 'home', 55.00, 25);

sales

Создайте таблицу, содержащую записи о продажах:

CREATE TABLE sales
(
    id         INT,
    product_id INT,
    quantity   INT,
    date       DATE
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (product_id);

Добавьте данные:

INSERT INTO sales
VALUES (1, 1, 1, '2025-01-03'),
       (2, 2, 2, '2025-01-05'),
       (3, 3, 1, '2025-01-07'),
       (4, 4, 3, '2025-01-10'),
       (5, 5, 1, '2025-01-15'),
       (6, 6, 2, '2025-01-22'),
       (7, 2, 1, '2025-02-01'),
       (8, 4, 2, '2025-02-03'),
       (9, 1, 1, '2025-02-07'),
       (10, 8, 1, '2025-02-10'),
       (11, 7, 2, '2025-02-13'),
       (12, 6, 1, '2025-02-18'),
       (13, 3, 1, '2025-03-01'),
       (14, 5, 2, '2025-03-05'),
       (15, 7, 1, '2025-03-07'),
       (16, 2, 3, '2025-03-10'),
       (17, 1, 1, '2025-03-15'),
       (18, 4, 2, '2025-03-20');

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

Следующий SQL-запрос выводит константное строковое значение и присваивает ему псевдоним:

SELECT 'Hello, world!' AS greeting;

Результат выглядит так:

   greeting
---------------
 Hello, world!
(1 row)

Чтобы получить все столбцы и строки из таблицы, используйте SELECT *:

SELECT *
FROM categories;

Можно также использовать команду TABLE:

TABLE categories;

Результат выглядит следующим образом:

 category_code |    name
---------------+-------------
 elec          | Electronics
 cloth         | Clothing
 home          | Home
(3 rows)

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

ВНИМАНИЕ

Используйте SELECT * с осторожностью для больших наборов данных: запрос обращается ко всем столбцам и может негативно влиять на производительность.

Вместо того чтобы получить все столбцы с помощью SELECT *, вы можете явно указать, какие столбцы возвращать. Например, этот SQL-запрос возвращает только название товара и цену из таблицы products:

SELECT name,
       price
FROM products;

Вы также можете использовать полные имена столбцов, указав имя таблицы в качестве префикса:

SELECT products.name,
       products.price
FROM products;

Это полезно в запросах с соединениями, где имена столбцов могут повторяться. Результат должен выглядеть так:

     name     |  price
--------------+---------
 Coffee Maker |   85.00
 T-Shirt      |   25.00
 Jacket       |   60.00
 Blender      |   55.00
 Laptop       | 1200.00
 Headphones   |  150.00
 Desk Chair   |  200.00
 Smartphone   |  800.00
(8 rows)

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

SELECT name  AS product_name,
       price AS unit_price
FROM products;

Тот же запрос без использования AS:

SELECT name  product_name,
       price unit_price
FROM products;

Результат выглядит следующим образом:

 product_name | unit_price
--------------+------------
 Coffee Maker |      85.00
 T-Shirt      |      25.00
 Jacket       |      60.00
 Blender      |      55.00
 Desk Chair   |     200.00
 Smartphone   |     800.00
 Laptop       |    1200.00
 Headphones   |     150.00
(8 rows)

Также можно использовать ключевое слово AS, чтобы указать псевдоним для таблицы:

SELECT p.name,
       p.price
FROM products AS p;

Тот же запрос без использования AS:

SELECT p.name,
       p.price
FROM products p;

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

Функция UPPER() преобразует названия товаров в верхний регистр:

SELECT UPPER(name) AS product_name_caps,
       price
FROM products;

Результат выглядит следующим образом:

 product_name_caps |  price
-------------------+---------
 COFFEE MAKER      |   85.00
 T-SHIRT           |   25.00
 JACKET            |   60.00
 BLENDER           |   55.00
 LAPTOP            | 1200.00
 DESK CHAIR        |  200.00
 SMARTPHONE        |  800.00
 HEADPHONES        |  150.00
(8 rows)

Следующий запрос вычисляет цену каждого товара со скидкой 10%:

SELECT name,
       price,
       price * 0.9 AS discounted_price
FROM products;

Результат выглядит так:

     name     |  price  | discounted_price
--------------+---------+------------------
 Coffee Maker |   85.00 |           76.500
 T-Shirt      |   25.00 |           22.500
 Jacket       |   60.00 |           54.000
 Blender      |   55.00 |           49.500
 Desk Chair   |  200.00 |          180.000
 Smartphone   |  800.00 |          720.000
 Laptop       | 1200.00 |         1080.000
 Headphones   |  150.00 |          135.000
(8 rows)

Выражение CASE позволяет возвращать разные значения в зависимости от условий:

SELECT name,
       price,
       CASE
           WHEN price >= 500 THEN 'high'
           WHEN price >= 100 THEN 'medium'
           ELSE 'low'
           END AS price_level
FROM products;

Результат выглядит так:

     name     |  price  | price_level
--------------+---------+-------------
 Coffee Maker |   85.00 | low
 T-Shirt      |   25.00 | low
 Jacket       |   60.00 | low
 Blender      |   55.00 | low
 Laptop       | 1200.00 | high
 Desk Chair   |  200.00 | medium
 Smartphone   |  800.00 | high
 Headphones   |  150.00 | medium
(8 rows)

Выражение WHERE позволяет отфильтровать строки по заданным условиям. Это помогает сузить результаты и получить только те данные, которые соответствуют определенным критериям. Можно сравнивать значения, использовать поиск по шаблону и комбинировать несколько условий с помощью логических операторов AND и OR.

Используйте оператор равенства (=), чтобы найти строки, в которых значение столбца соответствует заданному значению:
```
SELECT name,
       price
FROM products
WHERE name = 'Laptop';
```
Результат будет выглядеть так:
```
  name  |  price
--------+---------
 Laptop | 1200.00
(1 row)
```
ПРИМЕЧАНИЕ

Для проверки значения на NULL используйте оператор IS NULL.

Оператор IN позволяет сравнивать значение столбца со списком значений:

SELECT name,
       price
FROM products
WHERE name IN ('Laptop', 'Smartphone');

Результат выглядит так:

    name    |  price
------------+---------
 Laptop     | 1200.00
 Smartphone |  800.00
(2 rows)

Оператор LIKE позволяет осуществлять поиск по шаблону с использованием подстановочных символов. Символ процента (%) соответствует любой последовательности символов:
```
SELECT name,
       price
FROM products
WHERE name LIKE '%phone%';
```
Результат выглядит так:
```
    name    | price
------------+--------
 Smartphone | 800.00
 Headphones | 150.00
(2 rows)
```

Можно комбинировать несколько условий с помощью AND и OR, чтобы применить более детальные фильтры:

SELECT name,
       price,
       stock
FROM products
WHERE price > 100
  AND stock > 10;

Результат должен выглядеть так:

    name    |  price  | stock
------------+---------+-------
 Laptop     | 1200.00 |    15
 Smartphone |  800.00 |    30
 Headphones |  150.00 |    40
(3 rows)

Соединение таблиц позволяет объединять строки из двух или более таблиц на основе общего столбца.

Вы можете соединять таблицы, явно указывая условие соединения с помощью ключевого слова ON. В этом примере таблица products соединяется с таблицей categories по совпадению значений в столбцах category_code:

SELECT products.name   AS product_name,
       categories.name AS category_name,
       products.stock
FROM products
         JOIN categories ON products.category_code = categories.category_code;

Если столбец, используемый для соединения, имеет одинаковое имя в обеих таблицах, можно использовать выражение USING:

SELECT products.name   AS product_name,
       categories.name AS category_name,
       products.stock
FROM products
         JOIN categories USING (category_code);

Результат выглядит следующим образом:

 product_name | category_name | stock
--------------+---------------+-------
 Laptop       | Electronics   |    15
 Desk Chair   | Home          |    10
 Smartphone   | Electronics   |    30
 Headphones   | Electronics   |    40
 Coffee Maker | Home          |    20
 T-Shirt      | Clothing      |   100
 Jacket       | Clothing      |    50
 Blender      | Home          |    25
(8 rows)

Аналогично можно выполнить соединение более чем двух таблиц. Следующий SQL-запрос соединяет три таблицы и возвращает информацию о товаре, его категории и продажах:

SELECT products.name   AS product_name,
       categories.name AS category_name,
       sales.quantity  AS quantity_sold,
       sales.date      AS sale_date
FROM products
         JOIN categories ON products.category_code = categories.category_code
         JOIN sales ON products.id = sales.product_id;

Агрегатные функции позволяют вычислять сводные значения, группируя строки по одному или нескольким столбцам. К агрегатным функциям относятся SUM(), COUNT(), AVG(), MIN() и MAX(). В сочетании с GROUP BY эти функции вычисляют агрегированные значения для каждой группы.

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

Без использования JOIN

SELECT category_code,
       SUM(stock) AS total_stock
FROM products
GROUP BY category_code;

Результат выглядит следующим образом:

 category_code | total_stock
---------------+-------------
 home          |          55
 elec          |          85
 cloth         |         150
(3 rows)

С использованием JOIN

SELECT categories.name     AS category_name,
       SUM(products.stock) AS total_stock
FROM products
         JOIN categories USING (category_code)
GROUP BY categories.name;

Результат выглядит следующим образом:

 category_name | total_stock
---------------+-------------
 Home          |          55
 Clothing      |         150
 Electronics   |          85
(3 rows)

Выражение HAVING позволяет фильтровать группы после агрегации, аналогично тому, как WHERE фильтрует отдельные строки до группировки. Это полезно, когда нужно получить только те группы, которые соответствуют определенным условиям по итоговым значениям.

В следующих SQL-запросах показано, как использовать HAVING для фильтрации категорий товаров с суммарным остатком, превышающим 80:

Без использования JOIN

SELECT category_code,
       SUM(stock) AS total_stock
FROM products
GROUP BY category_code
HAVING SUM(stock) > 80;

Результат должен выглядеть следующим образом:

 category_code | total_stock
---------------+-------------
 cloth         |         150
 elec          |          85
(2 rows)

С использованием JOIN

SELECT categories.name     AS category_name,
       SUM(products.stock) AS total_stock
FROM products
         JOIN categories USING (category_code)
GROUP BY categories.name
HAVING SUM(products.stock) > 80;

Результат должен выглядеть следующим образом:

 category_name | total_stock
---------------+-------------
 Clothing      |         150
 Electronics   |          85
(2 rows)

После группировки и агрегирования данных можно использовать оператор ORDER BY для сортировки результатов по агрегированному значению или по столбцу группировки.

Следующие запросы сортируют категории товаров по общему остатку в порядке убывания:

Без использования JOIN

SELECT category_code,
       SUM(stock) AS total_stock
FROM products p
GROUP BY category_code
ORDER BY total_stock DESC;

Результат выглядит так:

 category_code | total_stock
---------------+-------------
 cloth         |         150
 elec          |          85
 home          |          55
(3 rows)

С использованием JOIN

SELECT categories.name     AS category_name,
       SUM(products.stock) AS total_stock
FROM products
         JOIN categories USING (category_code)
GROUP BY categories.name
ORDER BY total_stock DESC;

Результат выглядит так:

 category_name | total_stock
---------------+-------------
 Clothing      |         150
 Electronics   |          85
 Home          |          55
(3 rows)

Ключевое слово DISTINCT используется для возврата только уникальных значений из указанного столбца или комбинации столбцов. Например, чтобы получить список уникальных категорий товаров из таблицы products, выполните следующий запрос:

SELECT DISTINCT category_code
FROM products;

Результат должен выглядеть следующим образом:

 category_code
---------------
 home
 elec
 cloth
(3 rows)

Выражение LIMIT ограничивает количество строк, возвращаемых SQL-запросом:

SELECT name,
       price
FROM products
LIMIT 5;

Результат выглядит так:

     name     |  price
--------------+---------
 Laptop       | 1200.00
 Coffee Maker |   85.00
 T-Shirt      |   25.00
 Jacket       |   60.00
 Blender      |   55.00
(5 rows)

Обратите внимание, что если использовать LIMIT без ORDER BY, порядок строк будет случайным и может меняться при повторных запросах.

Оператор UNION позволяет объединять результаты двух и более запросов SELECT в один результирующий набор.

Следующий запрос разделяет товары на ценовые категории: low для цен ниже 100 и high для цен выше 500:

SELECT name,
       price,
       'low' AS price_level
FROM products
WHERE price < 100

UNION

SELECT name,
       price,
       'high' AS price_level
FROM products
WHERE price > 500;

Результат должен выглядеть следующим образом:

     name     |  price  | price_level
--------------+---------+-------------
 Coffee Maker |   85.00 | low
 Blender      |   55.00 | low
 Jacket       |   60.00 | low
 Laptop       | 1200.00 | high
 T-Shirt      |   25.00 | low
 Smartphone   |  800.00 | high
(6 rows)

Подзапрос — это запрос, вложенный в другой запрос. Подзапросы можно использовать в выражениях SELECT, FROM и WHERE для получения динамического значения или промежуточного результата, от которого зависит внешний запрос.

В этом примере используется подзапрос в выражении WHERE для фильтрации товаров, цена которых выше средней цены всех товаров:

SELECT name,
       price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Результат выглядит так:

    name    |  price
------------+---------
 Laptop     | 1200.00
 Smartphone |  800.00
(2 rows)

Общие табличные выражения (Common Table Expression, CTE) позволяют определить временный набор результатов, который можно использовать в запросе SELECT. В следующем примере CTE с именем avg_price вычисляет среднюю цену товара:

WITH avg_price AS (SELECT AVG(price) AS value FROM products)
SELECT name,
       price
FROM products,
     avg_price
WHERE price > avg_price.value;

Результат должен выглядеть следующим образом:

    name    |  price
------------+---------
 Laptop     | 1200.00
 Smartphone |  800.00
(2 rows)

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

Следующие запросы вычисляют накопительный итог по количеству для каждого product_id с течением времени с помощью оконной функции SUM(). Окно определено так, что оно начинается заново для каждого товара и следует порядку дат продаж. В данном запросе параметры окна задаются непосредственно внутри выражения OVER():

SELECT product_id,
       date,
       quantity,
       SUM(quantity) OVER (PARTITION BY product_id ORDER BY date) AS running_total_quantity
FROM sales
WHERE product_id IN (1, 2)
ORDER BY product_id, date;

В этом запросе параметрам окна присваивается имя с помощью выражения WINDOW:

SELECT product_id,
       date,
       quantity,
       SUM(quantity) OVER w AS running_total_quantity
FROM sales
WHERE product_id IN (1, 2)
WINDOW w AS (PARTITION BY product_id ORDER BY date)
ORDER BY product_id, date;

Результат выглядит так:

 product_id |    date    | quantity | running_total_quantity
------------+------------+----------+------------------------
          1 | 2025-01-03 |        1 |                      1
          1 | 2025-02-07 |        1 |                      2
          1 | 2025-03-15 |        1 |                      3
          2 | 2025-01-05 |        2 |                      2
          2 | 2025-02-01 |        1 |                      3
          2 | 2025-03-10 |        3 |                      6
(6 rows)

Представления и материализованные представления

JOIN

Обзор команды SELECT

Обзор

Обзор синтаксиса

Выполнение запроса SELECT

Примеры использования

Получение константного значения

Получение всех столбцов таблицы

Получение определенных столбцов таблицы

Использование псевдонимов

Использование функций и выражений

Фильтрация данных

Соединение таблиц

Агрегирование данных

Фильтрация агрегированных данных

Сортировка данных

Получение уникальных значений

Ограничение возвращаемых данных

Объединение результатов

Использование подзапросов

Общие табличные выражения (CTE)

Оконные функции