完整参考 SQL 命令与函数

📋SELECT 基础

基础数据选择操作

SELECT

Выборка данных из таблицы

SELECT column1, column2, ... FROM table_name

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 10;

SELECT *

Выборка всех столбцов таблицы

SELECT * FROM table_name

SELECT * FROM products;

Избегайте SELECT * в production - указывайте конкретные столбцы

SELECT DISTINCT

Выборка уникальных значений

SELECT DISTINCT column1, column2 FROM table_name

SELECT DISTINCT department_id, job_title
FROM employees;

DISTINCT ON

Первая строка для каждого уникального значения

PGGP

SELECT DISTINCT ON (column) columns FROM table ORDER BY column

SELECT DISTINCT ON (department_id) 
  department_id, employee_name, salary
FROM employees
ORDER BY department_id, salary DESC;

LIMIT / TOP / FETCH

Ограничение количества возвращаемых строк

SELECT ... LIMIT n [OFFSET m]

-- LIMIT с OFFSET
SELECT * FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 20;

-- FETCH FIRST (SQL стандарт)
SELECT * FROM products
ORDER BY price DESC
OFFSET 20 ROWS
FETCH FIRST 10 ROWS ONLY;

AS (Псевдонимы)

Назначение псевдонимов столбцам и таблицам

SELECT column AS alias, ... FROM table AS t

SELECT 
  e.first_name AS "Имя",
  e.last_name AS "Фамилия",
  d.name AS "Отдел"
FROM employees e
JOIN departments d ON e.dept_id = d.id;

SELECT INTO

Создание новой таблицы из результата запроса

SELECT columns INTO new_table FROM source

SELECT employee_id, first_name, salary
INTO high_earners
FROM employees
WHERE salary > 100000;

🔍WHERE 过滤

数据过滤条件

WHERE

Базовая фильтрация строк

SELECT ... FROM table WHERE condition

SELECT * FROM orders
WHERE status = 'pending'
  AND total_amount > 1000;

Операторы сравнения

Сравнение значений

=, <>, !=, <, >, <=, >=

SELECT * FROM products
WHERE price >= 100 
  AND price <= 500
  AND stock <> 0;

AND / OR / NOT

Логические операторы для комбинации условий

WHERE cond1 AND cond2 OR NOT cond3

SELECT * FROM employees
WHERE (department = 'IT' OR department = 'HR')
  AND NOT is_terminated
  AND hire_date >= '2020-01-01';

IN / NOT IN

Проверка вхождения в список значений

WHERE column IN (value1, value2, ...)

SELECT * FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

SELECT * FROM products
WHERE category_id NOT IN (
  SELECT id FROM categories WHERE is_archived = true
);

BETWEEN

Проверка попадания в диапазон (включительно)

WHERE column BETWEEN value1 AND value2

SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM products
WHERE price NOT BETWEEN 100 AND 500;

LIKE / ILIKE

Поиск по шаблону строки

WHERE column LIKE 'pattern'

-- % - любое количество символов
-- _ - один символ
SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

-- ILIKE - регистронезависимый (PostgreSQL)
SELECT * FROM products
WHERE name ILIKE '%phone%';

IS NULL / IS NOT NULL

Проверка на NULL значения

WHERE column IS [NOT] NULL

SELECT * FROM employees
WHERE manager_id IS NULL;

SELECT * FROM orders
WHERE shipped_date IS NOT NULL;

NULL != NULL, используйте IS NULL, а не = NULL

IS DISTINCT FROM

Сравнение с учетом NULL (NULL = NULL → true)

PGGP

WHERE a IS [NOT] DISTINCT FROM b

-- IS DISTINCT FROM: NULL-safe сравнение
SELECT * FROM t1
WHERE col1 IS DISTINCT FROM col2;

-- Эквивалент: col1 <> col2 OR (col1 IS NULL) <> (col2 IS NULL)

EXISTS / NOT EXISTS

Проверка существования строк в подзапросе

WHERE [NOT] EXISTS (subquery)

-- Клиенты с заказами
SELECT c.* FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.customer_id = c.id
);

-- Товары без продаж
SELECT p.* FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi 
  WHERE oi.product_id = p.id
);

ANY / ALL / SOME

Сравнение с результатом подзапроса

WHERE column operator ANY|ALL (subquery)

-- ANY: хотя бы одно значение
SELECT * FROM products
WHERE price > ANY (
  SELECT price FROM products WHERE category = 'Electronics'
);

-- ALL: все значения
SELECT * FROM products
WHERE price > ALL (
  SELECT price FROM products WHERE category = 'Books'
);

↕️ORDER BY 排序

结果排序

ORDER BY

Сортировка результатов запроса

SELECT ... ORDER BY column1 [ASC|DESC], column2 ...

SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;

NULLS FIRST / LAST

Управление позицией NULL при сортировке

PGGP

ORDER BY column [ASC|DESC] NULLS [FIRST|LAST]

SELECT * FROM employees
ORDER BY manager_id NULLS FIRST;

SELECT * FROM orders
ORDER BY shipped_date DESC NULLS LAST;

ORDER BY с выражениями

Сортировка по вычисляемым значениям

ORDER BY expression

SELECT name, price, quantity
FROM products
ORDER BY price * quantity DESC;

SELECT * FROM employees
ORDER BY EXTRACT(YEAR FROM hire_date) DESC;

COLLATE

Сортировка с учетом локали

ORDER BY column COLLATE collation_name

SELECT * FROM customers
ORDER BY name COLLATE "ru_RU";

SELECT * FROM products
ORDER BY name COLLATE "C";  -- байтовая сортировка

📊聚合函数

对行分组进行计算

COUNT

Подсчет количества строк

COUNT(*) | COUNT(column) | COUNT(DISTINCT column)

SELECT COUNT(*) as total_rows FROM orders;

SELECT COUNT(DISTINCT customer_id) as unique_customers
FROM orders;

SELECT COUNT(shipped_date) as shipped_orders
FROM orders;  -- не считает NULL

SUM

Сумма числовых значений

SUM([DISTINCT] expression)

SELECT SUM(total_amount) as revenue
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024;

SELECT SUM(DISTINCT price) FROM products;

AVG

Среднее арифметическое

AVG([DISTINCT] expression)

SELECT AVG(salary) as avg_salary
FROM employees
WHERE department_id = 10;

-- Среднее с округлением
SELECT ROUND(AVG(price)::numeric, 2) FROM products;

MIN / MAX

Минимальное и максимальное значения

MIN(expression) | MAX(expression)

SELECT 
  MIN(price) as cheapest,
  MAX(price) as most_expensive,
  MAX(price) - MIN(price) as price_range
FROM products;

SELECT MIN(order_date), MAX(order_date)
FROM orders;

STRING_AGG / LISTAGG

Конкатенация строк в группе

STRING_AGG(expression, delimiter [ORDER BY ...])

SELECT department_id,
  STRING_AGG(employee_name, ', ' ORDER BY employee_name) as employees
FROM employees
GROUP BY department_id;

ARRAY_AGG

Агрегация значений в массив

PGGP

ARRAY_AGG(expression [ORDER BY ...])

SELECT department_id,
  ARRAY_AGG(employee_id ORDER BY hire_date) as emp_ids
FROM employees
GROUP BY department_id;

BOOL_AND / BOOL_OR

Логические агрегаты

PGGP

BOOL_AND(expression) | BOOL_OR(expression)

SELECT order_id,
  BOOL_AND(is_shipped) as all_shipped,
  BOOL_OR(is_returned) as any_returned
FROM order_items
GROUP BY order_id;

PERCENTILE_CONT / PERCENTILE_DISC

Вычисление перцентилей

PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY column)

SELECT 
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) as p95
FROM employees;

VARIANCE / STDDEV

Дисперсия и стандартное отклонение

VARIANCE(column) | STDDEV(column)

SELECT 
  VARIANCE(salary) as variance,
  VAR_POP(salary) as population_variance,
  VAR_SAMP(salary) as sample_variance,
  STDDEV(salary) as stddev,
  STDDEV_POP(salary) as pop_stddev
FROM employees;

📦GROUP BY 分组

按行分组进行聚合

GROUP BY

Группировка строк по столбцам

SELECT ... FROM ... GROUP BY column1, column2, ...

SELECT department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id;

HAVING

Фильтрация групп после агрегации

GROUP BY ... HAVING condition

SELECT department_id, AVG(salary) as avg_sal
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

HAVING применяется после GROUP BY, WHERE - до

GROUPING SETS

Множественная группировка в одном запросе

GROUP BY GROUPING SETS ((cols1), (cols2), ...)

SELECT 
  department_id, job_title, 
  SUM(salary) as total_salary
FROM employees
GROUP BY GROUPING SETS (
  (department_id, job_title),
  (department_id),
  (job_title),
  ()
);

ROLLUP

Иерархическая группировка с промежуточными итогами

GROUP BY ROLLUP (col1, col2, ...)

SELECT 
  year, quarter, month,
  SUM(revenue) as total
FROM sales
GROUP BY ROLLUP (year, quarter, month);
-- Создает: (year,quarter,month), (year,quarter), (year), ()

CUBE

Полная группировка по всем комбинациям

GROUP BY CUBE (col1, col2, ...)

SELECT 
  region, product_category,
  SUM(sales) as total
FROM sales_data
GROUP BY CUBE (region, product_category);
-- Создает все комбинации: (r,p), (r), (p), ()

GROUPING()

Определение является ли NULL итоговой строкой

GROUPING(column)

SELECT 
  CASE WHEN GROUPING(department_id) = 1 
       THEN 'Всего' 
       ELSE department_id::text END as dept,
  SUM(salary)
FROM employees
GROUP BY ROLLUP (department_id);

🔗JOIN

合并多张表的数据

INNER JOIN

Возвращает строки с совпадениями в обеих таблицах

SELECT ... FROM t1 INNER JOIN t2 ON condition

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

LEFT JOIN

Все строки левой таблицы + совпадения справа

SELECT ... FROM t1 LEFT [OUTER] JOIN t2 ON condition

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

RIGHT JOIN

Все строки правой таблицы + совпадения слева

SELECT ... FROM t1 RIGHT [OUTER] JOIN t2 ON condition

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

FULL OUTER JOIN

Все строки обеих таблиц

SELECT ... FROM t1 FULL [OUTER] JOIN t2 ON condition

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

CROSS JOIN

Декартово произведение (все комбинации)

SELECT ... FROM t1 CROSS JOIN t2

SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;

SELF JOIN

Соединение таблицы с самой собой

SELECT ... FROM t1 a JOIN t1 b ON condition

SELECT e.name as employee, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

NATURAL JOIN

Автоматическое соединение по одноименным столбцам

PGGP

SELECT ... FROM t1 NATURAL JOIN t2

SELECT * FROM orders
NATURAL JOIN customers;
-- Соединяет по всем столбцам с одинаковыми именами

USING

Соединение по одноименному столбцу

PGGP

SELECT ... FROM t1 JOIN t2 USING (column)

SELECT * FROM orders
JOIN customers USING (customer_id);

LATERAL JOIN

Подзапрос с доступом к столбцам внешнего запроса

SELECT ... FROM t1, LATERAL (subquery) alias

SELECT d.name, top_emp.*
FROM departments d,
LATERAL (
  SELECT * FROM employees e
  WHERE e.dept_id = d.id
  ORDER BY salary DESC
  LIMIT 3
) top_emp;

CROSS APPLY / OUTER APPLY

MSSQL-специфичный LATERAL JOIN

MS

SELECT ... FROM t1 CROSS|OUTER APPLY (subquery)

-- PostgreSQL: используйте LATERAL
SELECT d.name, e.*
FROM departments d,
LATERAL (SELECT * FROM employees 
         WHERE dept_id = d.id LIMIT 1) e;

🎯子查询

嵌套查询

Скалярный подзапрос

Подзапрос, возвращающий одно значение

SELECT (SELECT single_value FROM ...)

SELECT name, salary,
  (SELECT AVG(salary) FROM employees) as avg_salary,
  salary - (SELECT AVG(salary) FROM employees) as diff
FROM employees;

Табличный подзапрос (derived table)

Подзапрос как виртуальная таблица в FROM

SELECT ... FROM (subquery) alias

SELECT dept_name, avg_salary
FROM (
  SELECT d.name as dept_name, AVG(e.salary) as avg_salary
  FROM departments d
  JOIN employees e ON d.id = e.dept_id
  GROUP BY d.name
) dept_stats
WHERE avg_salary > 50000;

Коррелированный подзапрос

Подзапрос, ссылающийся на внешний запрос

SELECT ... WHERE col = (SELECT ... WHERE outer.col = ...)

-- Сотрудники с зарплатой выше средней в их отделе
SELECT * FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees
  WHERE dept_id = e.dept_id
);

Выполняется для каждой строки внешнего запроса - может быть медленным

📝CTE (WITH)

公用表表达式：命名子查询

WITH (CTE)

Определение именованного подзапроса

WITH cte_name AS (query) SELECT ... FROM cte_name

WITH dept_stats AS (
  SELECT dept_id, AVG(salary) as avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, ds.avg_sal
FROM employees e
JOIN dept_stats ds ON e.dept_id = ds.dept_id
WHERE e.salary > ds.avg_sal;

Множественные CTE

Несколько CTE в одном запросе

WITH cte1 AS (...), cte2 AS (...) SELECT ...

WITH 
  active_customers AS (
    SELECT * FROM customers WHERE is_active = true
  ),
  recent_orders AS (
    SELECT * FROM orders 
    WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
  )
SELECT c.name, COUNT(o.id) as order_count
FROM active_customers c
LEFT JOIN recent_orders o ON c.id = o.customer_id
GROUP BY c.name;

WITH RECURSIVE

Рекурсивные запросы для иерархий и графов

WITH RECURSIVE cte AS (base UNION ALL recursive) SELECT ...

-- Иерархия сотрудников
WITH RECURSIVE emp_hierarchy AS (
  -- Базовый случай: топ-менеджеры
  SELECT id, name, manager_id, 1 as level
  FROM employees WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Рекурсивный случай
  SELECT e.id, e.name, e.manager_id, h.level + 1
  FROM employees e
  JOIN emp_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM emp_hierarchy ORDER BY level;

Генерация последовательностей

Создание числовых/временных рядов

WITH RECURSIVE seq AS (...) SELECT ...

-- Числа от 1 до 100
WITH RECURSIVE numbers AS (
  SELECT 1 as n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers;

-- Даты за последний год
WITH RECURSIVE dates AS (
  SELECT CURRENT_DATE - INTERVAL '1 year' as d
  UNION ALL
  SELECT d + INTERVAL '1 day' FROM dates 
  WHERE d < CURRENT_DATE
)
SELECT * FROM dates;

🪟窗口函数

对行组计算而不折叠结果

OVER()

Базовый синтаксис оконных функций

function() OVER ([PARTITION BY ...] [ORDER BY ...])

SELECT name, department, salary,
  SUM(salary) OVER () as total_salary,
  SUM(salary) OVER (PARTITION BY department) as dept_salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

ROW_NUMBER()

Присвоение уникального номера каждой строке

ROW_NUMBER() OVER (ORDER BY column)

SELECT 
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank,
  name, salary
FROM employees;

-- Нумерация внутри групп
SELECT 
  ROW_NUMBER() OVER (
    PARTITION BY department 
    ORDER BY salary DESC
  ) as dept_rank,
  name, department, salary
FROM employees;

RANK() / DENSE_RANK()

Ранжирование с обработкой одинаковых значений

RANK() OVER (...) | DENSE_RANK() OVER (...)

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) as rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;
-- RANK: 1,2,2,4 (пропускает номера)
-- DENSE_RANK: 1,2,2,3 (не пропускает)

NTILE(n)

Разбиение на n равных групп

NTILE(n) OVER (ORDER BY column)

-- Разбиение на квартили
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

LEAD() / LAG()

Доступ к следующей/предыдущей строке

LEAD|LAG(column, offset, default) OVER (...)

SELECT order_date, amount,
  LAG(amount, 1, 0) OVER (ORDER BY order_date) as prev_amount,
  LEAD(amount, 1, 0) OVER (ORDER BY order_date) as next_amount,
  amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) as diff
FROM orders;

FIRST_VALUE() / LAST_VALUE()

Первое/последнее значение в окне

FIRST_VALUE(col) OVER (...)

SELECT name, department, salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
  ) as top_earner,
  LAST_VALUE(name) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) as lowest_earner
FROM employees;

ROWS / RANGE / GROUPS

Определение границ окна

OVER (... ROWS BETWEEN start AND end)

SELECT date, amount,
  -- Скользящее среднее за 7 дней
  AVG(amount) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7d,
  
  -- Накопительная сумма
  SUM(amount) OVER (
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM sales;

PERCENT_RANK() / CUME_DIST()

Относительный ранг в процентах

PERCENT_RANK() OVER (...)

SELECT name, salary,
  PERCENT_RANK() OVER (ORDER BY salary) as pct_rank,
  CUME_DIST() OVER (ORDER BY salary) as cume_dist
FROM employees;
-- PERCENT_RANK: (rank-1)/(count-1)
-- CUME_DIST: count(<=value)/count(*)

FILTER (WHERE)

Условная агрегация в оконных функциях

PGGP

aggregate FILTER (WHERE condition) OVER (...)

SELECT department, month, revenue,
  SUM(revenue) FILTER (WHERE revenue > 0) OVER (
    PARTITION BY department
  ) as positive_revenue_total,
  COUNT(*) FILTER (WHERE status = 'completed') OVER (
    PARTITION BY department
  ) as completed_count
FROM sales;

⊕集合运算

结果的并集、交集与差集

UNION / UNION ALL

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

SELECT ... UNION [ALL] SELECT ...

-- UNION: без дубликатов
SELECT name FROM customers
UNION
SELECT name FROM suppliers;

-- UNION ALL: с дубликатами (быстрее)
SELECT email FROM users
UNION ALL
SELECT email FROM newsletter_subscribers;

INTERSECT

Пересечение результатов (общие строки)

SELECT ... INTERSECT SELECT ...

-- Клиенты, которые также являются поставщиками
SELECT email FROM customers
INTERSECT
SELECT email FROM suppliers;

EXCEPT / MINUS

Разность множеств (строки в первом, но не во втором)

SELECT ... EXCEPT SELECT ...

-- Клиенты без заказов
SELECT customer_id FROM customers
EXCEPT
SELECT DISTINCT customer_id FROM orders;

❓条件逻辑

条件表达式与 NULL 处理

CASE WHEN

Условное выражение

CASE WHEN cond1 THEN val1 ... ELSE default END

SELECT name, salary,
  CASE 
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 50000 THEN 'Middle'
    ELSE 'Junior'
  END as level
FROM employees;

Simple CASE

Простое сравнение значений

CASE expression WHEN val1 THEN res1 ... END

SELECT order_id,
  CASE status
    WHEN 'P' THEN 'Pending'
    WHEN 'S' THEN 'Shipped'
    WHEN 'D' THEN 'Delivered'
    ELSE 'Unknown'
  END as status_name
FROM orders;

COALESCE

Первое не-NULL значение из списка

COALESCE(val1, val2, ..., default)

SELECT 
  COALESCE(phone, mobile, email, 'No contact') as contact
FROM customers;

SELECT 
  COALESCE(discount, 0) as final_discount
FROM orders;

NULLIF

Возвращает NULL если значения равны

NULLIF(expression1, expression2)

-- Избежание деления на ноль
SELECT total / NULLIF(count, 0) as average
FROM stats;

-- Замена пустых строк на NULL
SELECT NULLIF(TRIM(name), '') as clean_name
FROM products;

GREATEST / LEAST

Максимальное/минимальное из списка значений

GREATEST(val1, val2, ...) | LEAST(val1, val2, ...)

SELECT 
  GREATEST(price1, price2, price3) as max_price,
  LEAST(stock1, stock2) as min_stock
FROM products;

IIF (MSSQL)

Короткая форма CASE для MSSQL

MS

IIF(condition, true_value, false_value)

-- PostgreSQL: используйте CASE
SELECT 
  CASE WHEN stock > 0 THEN 'In Stock' ELSE 'Out of Stock' END
FROM products;

📝字符串函数

处理字符串

CONCAT / ||

Конкатенация строк

CONCAT(str1, str2, ...) | str1 || str2

SELECT first_name || ' ' || last_name as full_name
FROM employees;

SELECT CONCAT(first_name, ' ', last_name) as full_name
FROM employees;

LENGTH / LEN

Длина строки

LENGTH(string) | LEN(string)

SELECT name, LENGTH(name) as name_length
FROM products;

SELECT CHAR_LENGTH(description) as char_count
FROM articles;

SUBSTRING / SUBSTR

Извлечение подстроки

SUBSTRING(string FROM start [FOR length])

SELECT SUBSTRING(phone FROM 1 FOR 3) as area_code
FROM customers;

SELECT SUBSTR(email, 1, POSITION('@' IN email) - 1) as username
FROM users;

LEFT / RIGHT

Первые/последние n символов

LEFT(string, n) | RIGHT(string, n)

SELECT LEFT(name, 10) as short_name,
  RIGHT(phone, 4) as last_four
FROM customers;

TRIM / LTRIM / RTRIM

Удаление пробелов и символов

TRIM([chars FROM] string)

SELECT TRIM('  hello  ') as trimmed;
SELECT LTRIM('  hello') as left_trimmed;
SELECT RTRIM('hello  ') as right_trimmed;

-- Удаление конкретных символов
SELECT TRIM(BOTH '-' FROM '--hello--') as trimmed;

UPPER / LOWER / INITCAP

Преобразование регистра

UPPER(string) | LOWER(string) | INITCAP(string)

SELECT 
  UPPER(name) as upper_name,
  LOWER(email) as lower_email,
  INITCAP(title) as title_case
FROM users;

REPLACE

Замена подстроки

REPLACE(string, from, to)

SELECT REPLACE(phone, '-', '') as clean_phone
FROM customers;

SELECT REPLACE(REPLACE(text, '
', ' '), '	', ' ')
FROM documents;

POSITION / CHARINDEX

Поиск позиции подстроки

POSITION(substring IN string)

SELECT POSITION('@' IN email) as at_position
FROM users;

SELECT STRPOS(url, '://') as protocol_end
FROM links;

LPAD / RPAD

Дополнение строки до заданной длины

LPAD(string, length, fill)

SELECT LPAD(CAST(id AS TEXT), 6, '0') as padded_id
FROM orders;  -- '000123'

SELECT RPAD(name, 20, '.') as formatted_name
FROM products;

SPLIT_PART / STRING_SPLIT

Разбиение строки по разделителю

SPLIT_PART(string, delimiter, part)

SELECT SPLIT_PART(email, '@', 1) as username,
  SPLIT_PART(email, '@', 2) as domain
FROM users;

-- Разворачивание в строки
SELECT id, tag
FROM products, 
  UNNEST(STRING_TO_ARRAY(tags, ',')) as tag;

Регулярные выражения

Работа с регулярными выражениями

column ~ pattern | REGEXP_REPLACE(...)

-- Проверка паттерна
SELECT * FROM users WHERE email ~ '^[a-z]+@';

-- Замена
SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') as digits_only
FROM customers;

-- Извлечение
SELECT REGEXP_MATCHES(log, '\d{4}-\d{2}-\d{2}')
FROM logs;

🔢数值函数

数学运算

ROUND

Округление числа

ROUND(number, decimals)

SELECT ROUND(price, 2) as rounded_price
FROM products;

SELECT ROUND(123.456, 0);  -- 123
SELECT ROUND(123.456, -1); -- 120

CEIL / FLOOR

Округление вверх/вниз

CEIL(number) | FLOOR(number)

SELECT CEIL(4.2) as ceiling,   -- 5
  FLOOR(4.8) as floor_val;     -- 4

SELECT CEIL(price) as min_whole_price
FROM products;

ABS

Абсолютное значение

ABS(number)

SELECT ABS(-15) as absolute;  -- 15

SELECT * FROM transactions
WHERE ABS(amount) > 1000;

MOD / %

Остаток от деления

MOD(dividend, divisor) | dividend % divisor

SELECT MOD(17, 5) as remainder;  -- 2
SELECT 17 % 5 as remainder;       -- 2

-- Четные/нечетные
SELECT * FROM products
WHERE id % 2 = 0;

POWER / SQRT

Возведение в степень и корень

POWER(base, exponent) | SQRT(number)

SELECT POWER(2, 10) as two_to_ten;  -- 1024
SELECT SQRT(144) as square_root;    -- 12

SELECT POWER(base, 2) as squared,
  SQRT(value) as root
FROM numbers;

LOG / LN / EXP

Логарифмы и экспонента

LOG(number) | LN(number) | EXP(number)

SELECT LN(2.718281828) as natural_log;  -- ~1
SELECT LOG(10, 100) as log_base_10;     -- 2
SELECT EXP(1) as e;                      -- ~2.718

RANDOM / RAND

Случайное число

RANDOM() | RAND()

SELECT RANDOM();  -- 0.0 до 1.0

-- Случайное целое от 1 до 100
SELECT FLOOR(RANDOM() * 100 + 1)::int;

-- Случайная выборка
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 5;

TRUNC / TRUNCATE

Усечение числа (без округления)

TRUNC(number, decimals)

SELECT TRUNC(123.789, 2);  -- 123.78
SELECT TRUNC(123.789, 0);  -- 123
SELECT TRUNC(123.789, -1); -- 120

📅日期与时间

处理日期和时间

CURRENT_DATE / NOW

Текущая дата и время

CURRENT_DATE | CURRENT_TIMESTAMP | NOW()

SELECT CURRENT_DATE;        -- 2024-03-15
SELECT CURRENT_TIME;        -- 10:30:45
SELECT CURRENT_TIMESTAMP;   -- 2024-03-15 10:30:45
SELECT NOW();               -- То же что CURRENT_TIMESTAMP

EXTRACT / DATE_PART

Извлечение компонента даты

EXTRACT(part FROM date)

SELECT 
  EXTRACT(YEAR FROM order_date) as year,
  EXTRACT(MONTH FROM order_date) as month,
  EXTRACT(DAY FROM order_date) as day,
  EXTRACT(DOW FROM order_date) as day_of_week,
  EXTRACT(EPOCH FROM created_at) as unix_timestamp
FROM orders;

DATE_TRUNC

Усечение даты до указанной точности

DATE_TRUNC('precision', date)

SELECT 
  DATE_TRUNC('month', order_date) as month_start,
  DATE_TRUNC('year', order_date) as year_start,
  DATE_TRUNC('hour', created_at) as hour_start
FROM orders;

-- Группировка по месяцам
SELECT DATE_TRUNC('month', order_date), COUNT(*)
FROM orders
GROUP BY 1;

Добавление интервала

Добавление периода к дате

date + INTERVAL | DATEADD(part, n, date)

SELECT 
  order_date + INTERVAL '7 days' as week_later,
  order_date + INTERVAL '1 month' as month_later,
  order_date - INTERVAL '1 year' as year_ago
FROM orders;

SELECT NOW() + INTERVAL '2 hours 30 minutes';

Разница дат

Вычисление разницы между датами

date1 - date2 | DATEDIFF(part, start, end)

SELECT shipped_date - order_date as days_to_ship
FROM orders;

SELECT AGE(NOW(), hire_date) as tenure
FROM employees;

SELECT EXTRACT(EPOCH FROM (end_time - start_time)) as seconds
FROM events;

Форматирование дат

Преобразование даты в строку

TO_CHAR(date, format) | FORMAT(date, format)

SELECT 
  TO_CHAR(order_date, 'DD.MM.YYYY') as formatted,
  TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS') as iso,
  TO_CHAR(order_date, 'Day, DD Month YYYY') as full_date
FROM orders;

Создание даты

Создание даты из компонентов

MAKE_DATE(year, month, day)

SELECT MAKE_DATE(2024, 3, 15);
SELECT MAKE_TIMESTAMP(2024, 3, 15, 10, 30, 0);

SELECT MAKE_DATE(year, 1, 1) as year_start
FROM (SELECT DISTINCT EXTRACT(YEAR FROM order_date)::int as year 
      FROM orders) y;

AT TIME ZONE

Работа с часовыми поясами

timestamp AT TIME ZONE 'zone'

SELECT 
  created_at,
  created_at AT TIME ZONE 'UTC' as utc,
  created_at AT TIME ZONE 'Europe/Moscow' as moscow
FROM events;

-- Конвертация
SELECT timestamp '2024-03-15 10:00:00' AT TIME ZONE 'America/New_York';

🔄数据类型与转换

数据类型转换

CAST

Стандартное преобразование типа

CAST(expression AS type)

SELECT CAST('123' AS INTEGER);
SELECT CAST(price AS NUMERIC(10,2));
SELECT CAST(created_at AS DATE);

:: (PostgreSQL)

Короткая форма CAST для PostgreSQL

PGGP

expression::type

SELECT '123'::integer;
SELECT price::numeric(10,2);
SELECT '2024-03-15'::date;
SELECT row_to_json(t)::text FROM table t;

CONVERT (MSSQL)

MSSQL-специфичное преобразование со стилями

MS

CONVERT(type, expression [, style])

-- PostgreSQL: используйте CAST или TO_CHAR
SELECT TO_CHAR(date, 'DD.MM.YYYY');

TRY_CAST / TRY_CONVERT

Безопасное преобразование (NULL при ошибке)

MS

TRY_CAST(expression AS type)

-- PostgreSQL: функция-обертка или обработка ошибок
CREATE OR REPLACE FUNCTION try_cast_int(text)
RETURNS integer AS $$
BEGIN
  RETURN $1::integer;
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

📋JSON 函数

处理 JSON 数据

Извлечение из JSON

Получение значений из JSON

json->'key' | json->>'key' | JSON_VALUE()

-- -> возвращает JSON, ->> возвращает текст
SELECT 
  data->'name' as json_name,
  data->>'name' as text_name,
  data->'address'->>'city' as city
FROM users;

-- #> для пути, #>> для текста
SELECT data#>>'{address,city}' as city FROM users;

Создание JSON

Создание JSON объектов

JSON_BUILD_OBJECT(k1, v1, ...) | JSON_OBJECT()

SELECT JSON_BUILD_OBJECT(
  'id', id,
  'name', name,
  'tags', JSON_BUILD_ARRAY('tag1', 'tag2')
) FROM products;

SELECT JSON_AGG(JSON_BUILD_OBJECT('id', id, 'name', name))
FROM products;

JSONB операторы

Проверка содержимого JSONB (PostgreSQL)

PGGP

jsonb @> jsonb | jsonb ? key

-- @> содержит
SELECT * FROM products
WHERE tags @> '["electronics"]';

-- ? содержит ключ
SELECT * FROM users
WHERE profile ? 'premium';

-- ?& все ключи, ?| любой ключ
SELECT * FROM data WHERE info ?& ARRAY['a','b'];

Разворачивание JSON

Преобразование JSON в строки/столбцы

JSONB_EACH() | OPENJSON()

-- Ключи и значения
SELECT key, value FROM users,
  JSONB_EACH(profile);

-- Массив в строки
SELECT id, elem
FROM products,
  JSONB_ARRAY_ELEMENTS(tags) as elem;

✏️DML（数据修改）

INSERT, UPDATE, DELETE, MERGE

INSERT VALUES

Вставка строк с явными значениями

INSERT INTO table (cols) VALUES (vals), ...

INSERT INTO products (name, price, category)
VALUES 
  ('Product A', 99.99, 'Electronics'),
  ('Product B', 49.99, 'Books');

INSERT SELECT

Вставка из результата запроса

INSERT INTO table (cols) SELECT ...

INSERT INTO archive_orders (id, customer, total)
SELECT id, customer_id, total_amount
FROM orders
WHERE order_date < '2023-01-01';

UPSERT (ON CONFLICT)

Вставка или обновление при конфликте

INSERT ... ON CONFLICT ... DO UPDATE/NOTHING

INSERT INTO products (sku, name, price)
VALUES ('SKU123', 'Widget', 29.99)
ON CONFLICT (sku) 
DO UPDATE SET 
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = NOW();

-- Игнорировать конфликт
INSERT INTO logs (id, message)
VALUES (1, 'test')
ON CONFLICT DO NOTHING;

UPDATE

Обновление существующих строк

UPDATE table SET col = val [WHERE ...]

UPDATE products
SET price = price * 1.1,
    updated_at = NOW()
WHERE category = 'Electronics';

-- UPDATE с JOIN
UPDATE orders o
SET status = 'VIP'
FROM customers c
WHERE o.customer_id = c.id AND c.is_vip = true;

DELETE

Удаление строк

DELETE FROM table [WHERE ...]

DELETE FROM sessions
WHERE expires_at < NOW();

-- DELETE с USING (JOIN)
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id AND o.status = 'cancelled';

RETURNING / OUTPUT

Возврат измененных строк

INSERT/UPDATE/DELETE ... RETURNING cols

INSERT INTO users (email, name)
VALUES ('test@test.com', 'Test')
RETURNING id, created_at;

UPDATE products
SET price = price * 0.9
WHERE category = 'Sale'
RETURNING id, name, price as new_price;

DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING user_id;

TRUNCATE

Быстрое удаление всех строк

TRUNCATE TABLE table_name [CASCADE]

TRUNCATE TABLE logs;

-- С каскадом для зависимых таблиц
TRUNCATE TABLE orders CASCADE;

-- Сброс sequence
TRUNCATE TABLE users RESTART IDENTITY;

Быстрее DELETE, но не вызывает триггеры

🏗️DDL（数据定义）

创建、修改、删除表与对象

CREATE TABLE

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

CREATE TABLE name (column type [constraints], ...)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW(),
  is_active BOOLEAN DEFAULT true
);

CREATE TABLE AS (CTAS)

Создание таблицы из результата запроса

CREATE TABLE new_table AS SELECT ...

CREATE TABLE active_users AS
SELECT id, email, name
FROM users
WHERE is_active = true;

ALTER TABLE

Изменение структуры таблицы

ALTER TABLE name ADD/DROP/ALTER COLUMN ...

-- Добавление столбца
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Удаление столбца
ALTER TABLE users DROP COLUMN phone;

-- Изменение типа
ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(12,2);

-- Установка NOT NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

DROP TABLE

Удаление таблицы

DROP TABLE [IF EXISTS] name [CASCADE]

DROP TABLE IF EXISTS temp_data;
DROP TABLE orders CASCADE;  -- Удаляет зависимые объекты

CREATE INDEX

Создание индекса

CREATE [UNIQUE] INDEX name ON table (columns)

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_products_sku ON products (sku);

-- Частичный индекс
CREATE INDEX idx_active_users ON users (id) WHERE is_active = true;

-- Составной индекс
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);

CREATE VIEW

Создание представления

CREATE [OR REPLACE] VIEW name AS SELECT ...

CREATE OR REPLACE VIEW active_orders AS
SELECT o.*, c.name as customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status != 'cancelled';

🔒约束

PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK

PRIMARY KEY

Первичный ключ таблицы

column type PRIMARY KEY | CONSTRAINT pk PRIMARY KEY (cols)

-- При создании таблицы
CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

-- Составной первичный ключ
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  PRIMARY KEY (order_id, product_id)
);

-- Добавление к существующей таблице
ALTER TABLE users ADD PRIMARY KEY (id);

FOREIGN KEY

Внешний ключ (ссылка на другую таблицу)

FOREIGN KEY (col) REFERENCES table(col) [ON DELETE ...]

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  product_id INT,
  FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

-- Добавление FK
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

UNIQUE

Уникальность значений

column type UNIQUE | CONSTRAINT uq UNIQUE (cols)

CREATE TABLE users (
  email VARCHAR(255) UNIQUE
);

-- Составной уникальный ключ
ALTER TABLE employee_assignments
ADD CONSTRAINT uq_emp_project 
UNIQUE (employee_id, project_id);

CHECK

Проверка условия для значений

CHECK (condition)

CREATE TABLE products (
  price DECIMAL CHECK (price > 0),
  quantity INT CHECK (quantity >= 0),
  CONSTRAINT valid_discount CHECK (discount BETWEEN 0 AND 100)
);

NOT NULL / DEFAULT

Обязательность и значение по умолчанию

column type NOT NULL DEFAULT value

CREATE TABLE users (
  email VARCHAR(255) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;

🔐事务

BEGIN、COMMIT、ROLLBACK、隔离级别

BEGIN / COMMIT / ROLLBACK

Управление транзакциями

BEGIN; ... COMMIT; | ROLLBACK;

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- При ошибке
BEGIN;
  INSERT INTO orders (...) VALUES (...);
  -- Если что-то пошло не так
ROLLBACK;

SAVEPOINT

Точки сохранения внутри транзакции

SAVEPOINT name; ROLLBACK TO name;

BEGIN;
  INSERT INTO orders (...) VALUES (...);
  SAVEPOINT order_created;
  
  INSERT INTO order_items (...) VALUES (...);
  -- Ошибка в items
  ROLLBACK TO order_created;
  
  -- Продолжаем с другими данными
  INSERT INTO order_items (...) VALUES (...);
COMMIT;

Уровни изоляции

Управление видимостью изменений между транзакциями

SET TRANSACTION ISOLATION LEVEL ...

-- Уровни: READ UNCOMMITTED, READ COMMITTED (default), 
--         REPEATABLE READ, SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  SELECT * FROM accounts WHERE id = 1;
  -- Гарантированно не изменится до COMMIT
COMMIT;

-- Или при старте
BEGIN ISOLATION LEVEL REPEATABLE READ;

FOR UPDATE / FOR SHARE

Блокировка строк при чтении

SELECT ... FOR UPDATE [NOWAIT|SKIP LOCKED]

-- Эксклюзивная блокировка (для UPDATE/DELETE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Разделяемая блокировка (для чтения)
SELECT * FROM products WHERE id = 1 FOR SHARE;

-- Не ждать, если заблокировано
SELECT * FROM inventory WHERE sku = 'ABC' FOR UPDATE NOWAIT;

-- Пропустить заблокированные (для очередей)
SELECT * FROM tasks WHERE status = 'pending' 
FOR UPDATE SKIP LOCKED LIMIT 1;

⚙️系统函数

元数据、版本、当前用户

VERSION()

Версия СУБД

SELECT VERSION();

SELECT VERSION();
-- PostgreSQL 15.2 on x86_64-pc-linux-gnu...

CURRENT_USER

Текущий пользователь

SELECT CURRENT_USER;

SELECT CURRENT_USER;
SELECT SESSION_USER;
SELECT CURRENT_DATABASE();
SELECT CURRENT_SCHEMA();

EXPLAIN

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

EXPLAIN [ANALYZE] query

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

-- С реальным выполнением
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;

-- Подробный вывод
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 1;

information_schema

Стандартные представления метаданных

SELECT * FROM information_schema.tables

-- Список таблиц
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public';

-- Столбцы таблицы
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users';

∅NULL 处理

处理 NULL 值

IS NULL / IS NOT NULL

Проверка на NULL значения

WHERE column IS [NOT] NULL

SELECT * FROM employees
WHERE manager_id IS NULL;  -- топ-менеджеры

SELECT * FROM orders
WHERE shipped_date IS NOT NULL;  -- отгруженные

NULL != NULL, нельзя сравнивать через = или <>

COALESCE

Первое не-NULL значение из списка

COALESCE(val1, val2, ..., default)

SELECT 
  COALESCE(phone, mobile, email, 'Нет контакта') as contact
FROM customers;

-- Замена NULL на 0
SELECT COALESCE(discount, 0) as discount FROM orders;

NULLIF

Возвращает NULL если значения равны

NULLIF(expression1, expression2)

-- Защита от деления на ноль
SELECT total / NULLIF(count, 0) as average
FROM stats;

-- Пустая строка → NULL
SELECT NULLIF(TRIM(name), '') as clean_name
FROM products;

ISNULL / NVL

Замена NULL на указанное значение

ISNULL(expr, replacement) | NVL(expr, replacement)

-- PostgreSQL: используйте COALESCE
SELECT COALESCE(phone, 'N/A') FROM customers;

IS [NOT] DISTINCT FROM

NULL-безопасное сравнение (NULL = NULL → true)

PGGP

a IS [NOT] DISTINCT FROM b

-- Стандартное сравнение: NULL = NULL → NULL (false)
-- IS NOT DISTINCT FROM: NULL = NULL → true

SELECT * FROM t1
WHERE col1 IS NOT DISTINCT FROM col2;

-- Эквивалент:
-- WHERE col1 = col2 OR (col1 IS NULL AND col2 IS NULL)

NULLS FIRST / LAST

Управление позицией NULL при сортировке

PGGP

ORDER BY col [ASC|DESC] NULLS [FIRST|LAST]

-- NULL в начале
SELECT * FROM employees
ORDER BY manager_id NULLS FIRST;

-- NULL в конце (по умолчанию для ASC)
SELECT * FROM orders
ORDER BY shipped_date DESC NULLS LAST;

📦数组

处理数组（PostgreSQL/Greenplum）

ARRAY[]

Создание массива

PGGP

ARRAY[val1, val2, ...] | '{val1,val2}'

SELECT ARRAY[1, 2, 3];
SELECT ARRAY['a', 'b', 'c'];
SELECT '{1,2,3}'::int[];

-- Многомерный массив
SELECT ARRAY[[1,2], [3,4]];

ARRAY_AGG

Агрегация значений в массив

ARRAY_AGG(expression [ORDER BY ...])

SELECT department_id,
  ARRAY_AGG(employee_name ORDER BY hire_date) as employees
FROM employees
GROUP BY department_id;

-- С DISTINCT
SELECT ARRAY_AGG(DISTINCT category) FROM products;

UNNEST

Разворачивание массива в строки

PGGP

UNNEST(array)

SELECT UNNEST(ARRAY[1,2,3]) as num;

-- С сохранением индекса
SELECT * FROM UNNEST(ARRAY['a','b','c']) 
  WITH ORDINALITY as t(val, idx);

-- Разворачивание столбца
SELECT id, UNNEST(tags) as tag
FROM products;

Доступ к элементам

Получение элементов массива по индексу

PGGP

array[index] | array[start:end]

SELECT (ARRAY['a','b','c'])[1];  -- 'a' (индексы с 1)
SELECT (ARRAY['a','b','c'])[2:3]; -- {'b','c'}

-- Для столбца
SELECT tags[1] as first_tag FROM products;

Функции массивов

Операции над массивами

PGGP

ARRAY_LENGTH, ARRAY_CAT, ARRAY_APPEND, ...

-- Длина
SELECT ARRAY_LENGTH(ARRAY[1,2,3], 1);  -- 3
SELECT CARDINALITY(ARRAY[1,2,3]);      -- 3

-- Конкатенация
SELECT ARRAY[1,2] || ARRAY[3,4];       -- {1,2,3,4}
SELECT ARRAY_CAT(ARRAY[1,2], ARRAY[3,4]);

-- Добавление элементов
SELECT ARRAY_APPEND(ARRAY[1,2], 3);    -- {1,2,3}
SELECT ARRAY_PREPEND(0, ARRAY[1,2]);   -- {0,1,2}

-- Удаление
SELECT ARRAY_REMOVE(ARRAY[1,2,2,3], 2); -- {1,3}

-- Позиция элемента
SELECT ARRAY_POSITION(ARRAY['a','b','c'], 'b'); -- 2

Операторы массивов

Сравнение и проверка массивов

PGGP

@> | <@ | && | = | <>

-- Содержит
SELECT ARRAY[1,2,3] @> ARRAY[2,3];  -- true

-- Содержится в
SELECT ARRAY[2] <@ ARRAY[1,2,3];   -- true

-- Пересечение (есть общие элементы)
SELECT ARRAY[1,2] && ARRAY[2,3];   -- true

-- ANY: элемент в массиве
SELECT * FROM products
WHERE 'electronics' = ANY(tags);

-- ALL: все элементы удовлетворяют
SELECT * FROM data
WHERE 100 > ALL(values);

ARRAY_TO_STRING / STRING_TO_ARRAY

Конвертация между массивом и строкой

ARRAY_TO_STRING(array, delimiter)

-- Массив → строка
SELECT ARRAY_TO_STRING(ARRAY['a','b','c'], ', ');
-- 'a, b, c'

-- Строка → массив
SELECT STRING_TO_ARRAY('a,b,c', ',');
-- {a,b,c}

-- С NULL обработкой
SELECT ARRAY_TO_STRING(ARRAY['a',NULL,'c'], ',', 'N/A');
-- 'a,N/A,c'

⚡索引

创建与管理索引

B-tree индекс

Стандартный индекс для сравнений и сортировки

CREATE INDEX name ON table (columns)

CREATE INDEX idx_users_email ON users (email);

-- Составной индекс
CREATE INDEX idx_orders_customer_date 
ON orders (customer_id, order_date DESC);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_products_sku ON products (sku);

Partial / Filtered индекс

Индекс только для подмножества строк

CREATE INDEX ... WHERE condition

-- Индекс только для активных пользователей
CREATE INDEX idx_active_users 
ON users (email) 
WHERE is_active = true;

-- Индекс для необработанных заказов
CREATE INDEX idx_pending_orders 
ON orders (created_at) 
WHERE status = 'pending';

Expression индекс

Индекс по вычисляемому выражению

CREATE INDEX ... ON table (expression)

-- Индекс по нижнему регистру
CREATE INDEX idx_users_email_lower 
ON users (LOWER(email));

-- Индекс по году
CREATE INDEX idx_orders_year 
ON orders (EXTRACT(YEAR FROM order_date));

-- Использование:
SELECT * FROM users WHERE LOWER(email) = 'test@test.com';

Covering индекс (INCLUDE)

Индекс с дополнительными столбцами для index-only scan

CREATE INDEX ... INCLUDE (columns)

-- Покрывающий индекс: все нужные данные в индексе
CREATE INDEX idx_orders_customer 
ON orders (customer_id) 
INCLUDE (order_date, total_amount);

-- Запрос использует только индекс:
SELECT order_date, total_amount 
FROM orders 
WHERE customer_id = 123;

Типы индексов

Специализированные типы индексов

CREATE INDEX ... USING method

-- Hash: только для равенства
CREATE INDEX idx_hash ON users USING hash (email);

-- GIN: для массивов, JSONB, полнотекстового поиска
CREATE INDEX idx_tags ON products USING gin (tags);
CREATE INDEX idx_data ON docs USING gin (data jsonb_path_ops);

-- GiST: для геометрии, диапазонов, полнотекста
CREATE INDEX idx_location ON places USING gist (coordinates);

-- BRIN: для больших таблиц с естественной сортировкой
CREATE INDEX idx_created ON logs USING brin (created_at);

CONCURRENTLY

Создание индекса без блокировки таблицы

PGMS

CREATE INDEX CONCURRENTLY ...

-- Без блокировки записи (но медленнее)
CREATE INDEX CONCURRENTLY idx_users_email 
ON users (email);

-- Удаление без блокировки
DROP INDEX CONCURRENTLY idx_users_email;

Обслуживание индексов

Перестроение и анализ индексов

REINDEX | REBUILD | ANALYZE

-- Перестроение индекса
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- Обновление статистики
ANALYZE users;

-- Проверка использования индексов
SELECT * FROM pg_stat_user_indexes 
WHERE relname = 'users';

🔀方言特性

各 DBMS 的独有能力

PostgreSQL специфика

Уникальные возможности PostgreSQL

PGGP

Различные PostgreSQL-only возможности

-- DISTINCT ON
SELECT DISTINCT ON (department_id) *
FROM employees ORDER BY department_id, salary DESC;

-- FILTER в агрегатах
SELECT 
  COUNT(*) FILTER (WHERE status = 'active') as active,
  COUNT(*) FILTER (WHERE status = 'pending') as pending
FROM users;

-- RETURNING
INSERT INTO users (email) VALUES ('test@test.com')
RETURNING id, created_at;

-- Upsert
INSERT INTO products (sku, name) VALUES ('A1', 'Product')
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name;

-- Generate series
SELECT generate_series(1, 10);
SELECT generate_series('2024-01-01'::date, '2024-12-31', '1 month');

-- Lateral join
SELECT d.*, e.* FROM departments d,
LATERAL (SELECT * FROM employees 
         WHERE dept_id = d.id LIMIT 3) e;

Greenplum специфика

Уникальные возможности Greenplum для MPP

GP

DISTRIBUTED BY, партиционирование, внешние таблицы

-- См. PostgreSQL специфика

MS SQL Server специфика

Уникальные возможности MS SQL Server

MS

TOP, APPLY, PIVOT, временные таблицы

-- См. PostgreSQL специфика

Различия синтаксиса

Таблица различий основного синтаксиса

Сравнение синтаксиса между диалектами

-- Ограничение строк
SELECT * FROM t LIMIT 10 OFFSET 20;
SELECT * FROM t FETCH FIRST 10 ROWS ONLY;

-- Конкатенация
SELECT 'Hello' || ' ' || 'World';
SELECT CONCAT('Hello', ' ', 'World');

-- Текущее время
SELECT NOW(), CURRENT_TIMESTAMP;

-- Автоинкремент
CREATE TABLE t (id SERIAL PRIMARY KEY);
CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY);

-- Boolean
SELECT * FROM t WHERE flag = true;
SELECT * FROM t WHERE flag IS TRUE;

-- Комментарии
-- однострочный
/* многострочный */

完整参考SQL 命令与函数

📋SELECT 基础

SELECT

SELECT *

SELECT DISTINCT

DISTINCT ON

LIMIT / TOP / FETCH

AS (Псевдонимы)

SELECT INTO

🔍WHERE 过滤

WHERE

Операторы сравнения

AND / OR / NOT

IN / NOT IN

BETWEEN

LIKE / ILIKE

IS NULL / IS NOT NULL

IS DISTINCT FROM

EXISTS / NOT EXISTS

ANY / ALL / SOME

↕️ORDER BY 排序

ORDER BY

NULLS FIRST / LAST

ORDER BY с выражениями

COLLATE

📊聚合函数

COUNT

SUM

AVG

MIN / MAX

STRING_AGG / LISTAGG

ARRAY_AGG

BOOL_AND / BOOL_OR

PERCENTILE_CONT / PERCENTILE_DISC

VARIANCE / STDDEV

📦GROUP BY 分组

GROUP BY

HAVING

GROUPING SETS

ROLLUP

CUBE

GROUPING()

🔗JOIN

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

CROSS JOIN

SELF JOIN

NATURAL JOIN

USING

LATERAL JOIN

CROSS APPLY / OUTER APPLY

🎯子查询

Скалярный подзапрос

Табличный подзапрос (derived table)

Коррелированный подзапрос

📝CTE (WITH)

WITH (CTE)

Множественные CTE

WITH RECURSIVE

Генерация последовательностей

🪟窗口函数

OVER()

ROW_NUMBER()

RANK() / DENSE_RANK()

NTILE(n)

LEAD() / LAG()

FIRST_VALUE() / LAST_VALUE()

ROWS / RANGE / GROUPS

PERCENT_RANK() / CUME_DIST()

FILTER (WHERE)

⊕集合运算

UNION / UNION ALL

INTERSECT

EXCEPT / MINUS

❓条件逻辑

CASE WHEN

Simple CASE

COALESCE