PostgreSQL - webkoth/style-guide-php-laravel GitHub Wiki

Таблицы в БД могут быть 3-х типов:

• Описательные
• Связующие
• Служебные

В PostgreSQL функции могут быть классифицированы в следующие категории:

1. Агрегатные функции

-- AVG: Вычисление среднего значения числового столбца
SELECT AVG(salary) FROM employees;

-- COUNT: Подсчет количества строк в столбце
SELECT COUNT(*) FROM orders;

-- MAX: Поиск максимального значения в столбце
SELECT MAX(price) FROM products;

-- MIN: Поиск минимального значения в столбце
SELECT MIN(quantity) FROM inventory;

-- SUM: Вычисление суммы числового столбца
SELECT SUM(total_sales) FROM sales;

2. Математические функции

-- ABS: Получение абсолютного значения числа
SELECT ABS(-10);

-- CEIL: Округление числа вверх
SELECT CEIL(4.3);

-- FLOOR: Округление числа вниз
SELECT FLOOR(7.8);

-- ROUND: Округление числа до указанного количества десятичных знаков
SELECT ROUND(3.14159, 2);

-- TRUNC: Обрезка числа до указанного количества десятичных знаков
SELECT TRUNC(6.789, 1);

3. Строковые функции

-- CONCAT: Объединение двух строк
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

-- LENGTH: Получение длины строки
SELECT LENGTH('Hello, World!');

-- LOWER: Преобразование строки в нижний регистр
SELECT LOWER('HELLO');

-- UPPER: Преобразование строки в верхний регистр
SELECT UPPER('world');

-- SUBSTRING: Извлечение подстроки из строки
SELECT SUBSTRING('Hello, World!', 8, 5);

4. Функции преобразования типов

-- CAST: Преобразование значения в определенный тип данных
SELECT CAST('123' AS INTEGER);

-- TO_CHAR: Преобразование значения в строку с заданным форматом
SELECT TO_CHAR(current_date, 'YYYY-MM-DD');

-- TO_DATE: Преобразование строки в значение типа дата
SELECT TO_DATE('2023-07-15', 'YYYY-MM-DD');

5. Функции даты и времени

-- CURRENT_DATE: Получение текущей даты
SELECT CURRENT_DATE;

-- CURRENT_TIME: Получение текущего времени
SELECT CURRENT_TIME;

-- EXTRACT: Извлечение конкретной части из значения типа дата/время
SELECT EXTRACT(YEAR FROM timestamp_column);

-- DATE_PART: Извлечение конкретной части из значения типа дата/время
SELECT DATE_PART('hour', timestamp_column);

6. Логические функции

-- AND: Логическое И
SELECT column1 AND column2 FROM table;

-- OR: Логическое ИЛИ
SELECT column1 OR column2 FROM table;

-- NOT: Логическое НЕ
SELECT NOT column FROM table;

7. Функции работы с массивами

-- ARRAY_LENGTH: Получение длины массива
SELECT ARRAY_LENGTH(array_column, 1) FROM table;

-- ARRAY_APPEND: Добавление элемента в конец массива
SELECT ARRAY_APPEND(array_column, 'new_element') FROM table;

-- ARRAY_REMOVE: Удаление всех вхождений элемента из массива
SELECT ARRAY_REMOVE(array_column, 'element_to_remove') FROM table;

8. Функции агрегации по группам

-- GROUP BY: Группировка строк по заданному столбцу
SELECT column, COUNT(*) FROM table GROUP BY column;

-- HAVING: Фильтрация групп с использованием агрегатных условий
SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 5;

-- ROLLUP: Создание сводной таблицы с подитогами
SELECT column1, column2, SUM(quantity) FROM table GROUP BY ROLLUP (column1, column2);

9. Функции оконного разбиения

-- ROW_NUMBER: Присвоение уникального номера каждой строке внутри окна
SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column1) FROM table;

-- RANK: Присвоение ранга каждой строке внутри окна
SELECT column1, column2, RANK() OVER (ORDER BY column1) FROM table;

-- LAG: Получение значения предыдущей строки внутри окна
SELECT column1, column2, LAG(column1) OVER (ORDER BY column2) FROM table;

-- LEAD: Получение значения следующей строки внутри окна
SELECT column1, column2, LEAD(column1) OVER (ORDER BY column2) FROM table;

10. Пользовательские функции

CREATE OR REPLACE FUNCTION calculate_discount(total_amount DECIMAL)
RETURNS DECIMAL AS $$
DECLARE
    discount DECIMAL;
BEGIN
    IF total_amount > 1000 THEN
        discount := total_amount * 0.1; -- 10% скидка, если сумма больше 1000
    ELSE
        discount := 0;
    END IF;

    RETURN discount;
END;
$$ LANGUAGE plpgsql;


SELECT calculate_discount(1500); -- Возвращает 150 (10% от 1500)

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

Информация о сущностях:

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

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

Данные о категориях и подкатегориях, такие как иерархия товаров или классификация клиентов. Информация о структуре организации, включая департаменты, подразделения и связи между ними. Информация о взаимосвязях:

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

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