Как посчитать все на свете одним sql запросом
Перейти к содержимому

Как посчитать все на свете одним sql запросом

  • автор:

Как подсчитать количество по каждому месяцу одним запросом, если известен начало и конец работы?

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

ID Name Start_Date End_date
1 John 15.01.2020 06.08.2020
2 Nikhil 14.03.2020 25.11.2020
. . . .

Ожидаю получить такой результат:

month count(ID)
Jun 2
Feb 8
. .
Dec 3

Я знаю, как узнать количество для каждого месяца отдельним запросом:

Select COUNT(ID) FROM tab where Start_Date==01.01.2020 

Но поскольку такую операцию мне нужду сделать для каждого месяца для нескольких лет, то менять даты вручную для каждого месяца слишком долго.

Отслеживать
51.6k 204 204 золотых знака 67 67 серебряных знаков 251 251 бронзовый знак
задан 21 дек 2020 в 19:22
GreatFilter GreatFilter
151 7 7 бронзовых знаков
CTE, генерирующий список месяцев диапазона.
21 дек 2020 в 20:25

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

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

with params as ( select date'2020-01-01' from#, 12 months# from dual ) select to_char (mon, 'Mon rr') month, count (id) from ( select add_months (trunc (from#, 'mm'), level-1) mon from params connect by level<=months#) left join tab t on (t.sdt <= last_day (mon) and t.edt >= mon) group by mon order by mon / 
MONTH COUNT(ID) --------------- ---------- Jan 20 1 Feb 20 1 Mar 20 2 Apr 20 2 May 20 2 Jun 20 2 Jul 20 2 Aug 20 3 Sep 20 2 Oct 20 2 Nov 20 1 Dec 20 0 

Как посчитать всё на свете одним SQL-запросом. Оконные функции PostgreSQL

Я с удивлением обнаружил, что многие разработчики, даже давно использующие postgresql, не понимают оконные функции, считая их какой-то особой магией для избранных. Ну или в лучшем случае «копипастят» со StackOverflow выражения типа «row_number() OVER ()», не вдаваясь в детали. А ведь оконные функции — полезнейший функционал PostgreSQL.
Попробую по-простому объяснить, как можно их использовать.

Для начала хочу сразу пояснить, что оконные функции не изменяют выборку, а только добавляют некоторую дополнительную информацию о ней. Т.е. для простоты понимания можно считать, что postgres сначала выполняет весь запрос (кроме сортировки и limit), а потом только просчитывает оконные выражения.

Синтаксис примерно такой:

функция OVER окно 

Окно — это некоторое выражение, описывающее набор строк, которые будет обрабатывать функция и порядок этой обработки.
Причем окно может быть просто задано пустыми скобками (), т.е. окном являются все строки результата запроса.

Например, в этом селекте к обычным полям id, header и score просто добавится нумерация строк.

SELECT id, section, header, score, row_number() OVER () AS num FROM news; id | section | header | score | num ----+---------+-----------+-------+----- 1 | 2 | Заголовок | 23 | 1 2 | 1 | Заголовок | 6 | 2 3 | 4 | Заголовок | 79 | 3 4 | 3 | Заголовок | 36 | 4 5 | 2 | Заголовок | 34 | 5 6 | 2 | Заголовок | 95 | 6 7 | 4 | Заголовок | 26 | 7 8 | 3 | Заголовок | 36 | 8 

В оконное выражение можно добавить ORDER BY, тогда можно изменить порядок обработки.

SELECT id, section, header, score, row_number() OVER (ORDER BY score DESC) AS rating FROM news ORDER BY id; id | section | header | score | rating ----+---------+-----------+-------+-------- 1 | 2 | Заголовок | 23 | 7 2 | 1 | Заголовок | 6 | 8 3 | 4 | Заголовок | 79 | 2 4 | 3 | Заголовок | 36 | 4 5 | 2 | Заголовок | 34 | 5 6 | 2 | Заголовок | 95 | 1 7 | 4 | Заголовок | 26 | 6 8 | 3 | Заголовок | 36 | 3 

Обратите внимание, что я добавил еще и в конце всего запоса ORDER BY id, при этом рейтинг посчитан все равно верно. Т.е. посгрес просто отсортировал результат вместе с результатом работы оконной функции, один order ничуть не мешает другому.

Дальше — больше. В оконное выражение можно добавить слово PARTITION BY [expression],
например row_number() OVER (PARTITION BY section), тогда подсчет будет идти в каждой группе отдельно:

SELECT id, section, header, score, row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section FROM news ORDER BY section, rating_in_section; id | section | header | score | rating_in_section ----+---------+-----------+-------+------------------- 2 | 1 | Заголовок | 6 | 1 6 | 2 | Заголовок | 95 | 1 5 | 2 | Заголовок | 34 | 2 1 | 2 | Заголовок | 23 | 3 4 | 3 | Заголовок | 36 | 1 8 | 3 | Заголовок | 36 | 2 3 | 4 | Заголовок | 79 | 1 7 | 4 | Заголовок | 26 | 2 

Если не указывать партицию, то партицией является весь запрос.

Тут сразу надо немного сказать о функциях, которые можно использовать, так как есть очень важный нюанс.
В качестве функции можно использовать, так сказать, истинные оконные функции из мануала — это row_number(), rank(), lead() и т.д., а можно использовать функции-агрегаты, такие как: sum(), count() и т.д. Так вот, это важно, агрегатные функции работают слегка по-другому: если не задан ORDER BY в окне, идет подсчет по всей партиции один раз, и результат пишется во все строки (одинаков для всех строк партиции). Если же ORDER BY задан, то подсчет в каждой строке идет от начала партиции до этой строки.

Давайте посмотрим это на примере. Например, у нас есть некая (сферическая в вакууме) таблица пополнений балансов.

SELECT transaction_id, change FROM balance_change ORDER BY transaction_id; transaction_id | change ----------------+-------- 1 | 1.00 2 | -2.00 3 | 10.00 4 | -4.00 5 | 5.50 

и мы хотим узнать заодно, как менялся остаток на балансе при этом:

SELECT transaction_id, change, sum(change) OVER (ORDER BY transaction_id) as balance FROM balance_change ORDER BY transaction_id; transaction_id | change | balance ----------------+--------+--------- 1 | 1.00 | 1.00 2 | -2.00 | -1.00 3 | 10.00 | 9.00 4 | -4.00 | 5.00 5 | 5.50 | 10.50 

Т.е. для каждой строки идет подсчет в отдельном фрейме. В данном случае фрейм — это набор строк от начала до текущей строки (если было бы PARTITION BY, то от начала партиции).

Если же мы для агрегатной фунции sum не будем использовать ORDER BY в окне, тогда мы просто посчитаем общую сумму и покажем её во всех строках. Т.е. фреймом для каждой из строк будет весь набор строк
от начала до конца партиции.

SELECT transaction_id, change, sum(change) OVER () as result_balance FROM balance_change ORDER BY transaction_id; transaction_id | change | result_balance ----------------+--------+---------------- 1 | 1.00 | 10.50 2 | -2.00 | 10.50 3 | 10.00 | 10.50 4 | -4.00 | 10.50 5 | 5.50 | 10.50 

Вот такая особенность агрегатных функций, если их использовать как оконные. На мой взгляд, это довольно-таки странный, интуитивно неочевидный момент SQL-стандарта.

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

SELECT transaction_id, change, sum(change) OVER (ORDER BY transaction_id) as balance, sum(change) OVER () as result_balance, round( 100.0 * sum(change) OVER (ORDER BY transaction_id) / sum(change) OVER (), 2 ) AS percent_of_result, count(*) OVER () as transactions_count FROM balance_change ORDER BY transaction_id; transaction_id | change | balance | result_balance | percent_of_result | transactions_count ----------------+--------+---------+----------------+-------------------+-------------------- 1 | 1.00 | 1.00 | 10.50 | 9.52 | 5 2 | -2.00 | -1.00 | 10.50 | -9.52 | 5 3 | 10.00 | 9.00 | 10.50 | 85.71 | 5 4 | -4.00 | 5.00 | 10.50 | 47.62 | 5 5 | 5.50 | 10.50 | 10.50 | 100.00 | 5 

Если у вас много одинаковых выражений после OVER, то можно дать им имя и вынести отдельно с ключевым словом WINDOW, чтобы избежать дублирования кода. Вот пример из мануала:

SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); 

Здесь w после слова OVER идет без уже скобок.

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

SELECT * FROM ( SELECT id, section, header, score, row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section FROM news ORDER BY section, rating_in_section ) counted_news WHERE rating_in_section  

Еще пример для закрепления. Помимо row_number() есть несколько других функций. Например lag, которая ищет строку перед последней строкой фрейма. К примеру мы можем найти насколько очков новость отстает от предыдущей в рейтинге:

SELECT id, section, header, score, row_number() OVER w AS rating, lag(score) OVER w - score AS score_lag FROM news WINDOW w AS (ORDER BY score DESC) ORDER BY score desc; id | section | header | score | rating | score_lag ----+---------+-----------+-------+--------+----------- 6 | 2 | Заголовок | 95 | 1 | 3 | 4 | Заголовок | 79 | 2 | 16 8 | 3 | Заголовок | 36 | 3 | 43 4 | 3 | Заголовок | 36 | 4 | 0 5 | 2 | Заголовок | 34 | 5 | 2 7 | 4 | Заголовок | 26 | 6 | 8 1 | 2 | Заголовок | 23 | 7 | 3 2 | 1 | Заголовок | 6 | 8 | 17 

Прошу в коментариях накидать примеров, где особенно удобно применять оконные фунции. А также, какие с ними могут возникнуть проблемы, если таковые имеются.

Больше полезного можно найти на telegram-канале о разработке "Cross Join", где мы обсуждаем базы данных, языки программирования и всё на свете!

Как посчитать все на свете одним SQL запросом и стать настоящим магом данных

Для того чтобы посчитать все на свете одним SQL запросом, вам нужно использовать функцию SUM() в сочетании с оператором GROUP BY . Это позволит вам группировать данные и подсчитывать их сумму для каждой группы. Вот пример:

 SELECT category, SUM(quantity) as total_quantity FROM products GROUP BY category; 

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

 SELECT category, SUM(quantity) as total_quantity FROM products WHERE price > 10 GROUP BY category; 

Этот запрос выберет только товары с ценой выше 10 и посчитает общее количество товаров для каждой категории. Надеюсь, это помогает! Если у вас есть еще вопросы, не стесняйтесь задавать.

Детальный ответ

Как посчитать все на свете одним SQL запросом?

Привет! В этой статье мы разберем, как можно использовать SQL запросы для того, чтобы подсчитать информацию о мире вокруг нас одним запросом. SQL (Structured Query Language) – это язык программирования, который используется для работы с реляционными базами данных. Стандартный SQL позволяет нам извлекать, изменять и анализировать данные в базе данных. Но может ли SQL запрос подсчитать все на свете? Давайте вместе разберемся!

1. Как подсчитать количество людей на Земле?

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

SELECT SUM(population_count) AS total_population FROM population;

В этом примере мы используем агрегатную функцию SUM, чтобы посчитать общее количество людей, представленных в таблице "population". Результатом запроса будет общая численность населения на Земле.

2. Как подсчитать количество городов в мире?

Давайте перейдем к следующему вопросу - как подсчитать количество городов в мире. Для этого опять же нам понадобится база данных, содержащая таблицу с информацией о городах. Предположим, у нас есть таблица "cities" с полями "city_name" и "country".

SELECT COUNT(*) AS total_cities FROM cities;

В данном SQL запросе мы используем агрегатную функцию COUNT, чтобы подсчитать количество записей в таблице "cities". В результате мы получим общее количество городов в базе данных.

3. Как подсчитать общую площадь суши на планете?

Теперь перейдем к более сложному вопросу - как посчитать общую площадь суши на планете. Для этого нам понадобится база данных, содержащая таблицу с информацией о географических объектах. Предположим, у нас есть таблица "geography" с полями "object_name" и "area".

SELECT SUM(area) AS total_land_area FROM geography WHERE object_name = 'Land';

В этом примере мы используем агрегатную функцию SUM, чтобы просуммировать площади всех объектов, имеющих название "Land" (суша). Результатом будет общая площадь суши на планете.

4. Как подсчитать общую длину всех рек на Земле?

Наконец, рассмотрим, как посчитать общую длину всех рек на Земле. Для этого нам понадобится база данных, содержащая таблицу с информацией о реках. Допустим, у нас есть таблица "rivers" с полями "river_name" и "length".

SELECT SUM(length) AS total_river_length FROM rivers;

В этом примере мы снова используем агрегатную функцию SUM, чтобы просуммировать длины всех рек в таблице "rivers". Результатом будет общая длина всех рек на Земле.

Заключение

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

Как посчитать все на свете одним sql запросом

В данной статье мы рассмотрим работу с оконными функциями в T-SQL 2012.

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

—Создаем базу и и добавляем тестовые данные
USE [master];
GO

IF DB_ID('sbase') IS NOT NULL
DROP DATABASE sbase;

CREATE DATABASE sbase;
GO

—id — номер заказа, product — товар, qty — количество, cost — цена
CREATE TABLE sales(
id SMALLINT,
product VARCHAR(MAX),
qty SMALLINT,
cost SMALLINT);

INSERT INTO sales
VALUES (1, 'wine', 10, 30)
,(1, 'beer', 5, 15)
,(1, 'cognac', 4, 50)
,(2, 'cognac', 8, 40)
,(2, 'vodka', 2, 30)
,(3, 'beer', 15, 12)
,(3, 'cognac', 12, 46)
,(3, 'vodka', 10, 25)
,(4, 'vodka', 1, 30);

—С помощью PARTITION BY мы делим результат запроса на окна
—Общее количество товара и его стоимость по заказам, детально по товару

SELECT id AS N'Заказ', product AS N'Товар',
SUM(qty) OVER (PARTITION BY id, product) AS N'Общее кол-во в заказе',
SUM(qty * cost) OVER (PARTITION BY id, product) AS N'Общая с-мость в заказе',
SUM(qty * cost) OVER () AS N'Общая с-мость'
FROM sales

—С помощью ORDER BY мы можем задать сортировку
SELECT id, product, qty,
SUM(qty) OVER (PARTITION BY product) AS allQtyProduct,
SUM(qty) OVER (ORDER BY id DESC) AS allQtyId
FROM sales

—Запрос имеет 9 строк и 4 диапазона
—ROWS отвечпет за строки.
—RANGE отвечпет за диапазон.
—CURRENT ROW — текущая строка или диапазон

SELECT id, product, qty,
SUM(qty*cost) OVER (ORDER BY id DESC ROWS CURRENT ROW) AS IdQtyCost
FROM sales

SELECT id, product, qty,
SUM(qty*cost) OVER (ORDER BY id DESC RANGE CURRENT ROW) AS IdAllQtyCost
FROM sales

—UNBOUNDED PRECEDING — указывает, что надо учитывать все строки/диапазоны с первого и по текущий
—Будет суммировать каждую следующую строку

SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS SumQty
FROM sales

—Будет суммировать каждый следующий диапазон
SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING) AS SumQty
FROM sales

—UNBOUNDED FOLLOWING — указывает, что надо учитывать все строки/диапазоны с текущего и по последний.
—Может быть указанным только в предложении BETWEEN как конечная точка.
—BETTWEEN — используется для указания границ.
—Будет суммировать каждую следующую строку между указанными значениями

SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS SumQty
FROM sales

—Будет суммировать каждый следующий диапазон между указанными значениями
SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS SumQty
FROM sales

—PRECEDING — указывает, что нужно учитывать текущую строку и кол-во строк до нее.
—Не допускается в предложении RANGE.

SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id ROWS 1 PRECEDING) AS SumQty
FROM sales

—FOLLOWING — указывает, что нужно учитывать диапазон кол-во строк после текущей строчки.
—Может быть использовано только в предложении BETWEEN. Не допускается в предложении RANGE.

SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS SumQty
FROM sales

—ROW_NUMBER() — задает каждой строчке окна уникальный, последовательный номер, начиная с единицы.
—Функция "ROW_NUMBER" должна содержать предложение OVER вместе с предложением ORDER BY

SELECT id, product, qty,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS R
FROM sales

—Пример вывода с "постраничной навигацией" используя ROW_NUMBER()
DECLARE
@pagenum AS INT = 3,
@pagesize AS INT = 2;

WITH C AS
(
SELECT ROW_NUMBER() OVER( ORDER BY product, id ) AS rownum,
id, product
FROM sales
)
SELECT id, product
FROM C
WHERE rownum BETWEEN (@pagenum — 1) * @pagesize + 1 —4
AND @pagenum * @pagesize; —6

—RANK() — Возвращает ранг каждой строки в окне.
—Ранг для каждого уникального значения столбца или столбцов указанных в ORDER BY вычисляется лишь единожды, при первом нахождении оного.
—По формуле единица плюс количество строк до строки от начала окна.

SELECT id, product, qty,
RANK() OVER (ORDER BY id) AS R
FROM sales

—DENSE_RANK() — возвращает ранг строк в окне без прыжков через значения.
—Ранг строки равен количеству уникальных значений указанных в ODER BY, предшествующих строке, увеличенному на единицу.

SELECT id, product, qty,
DENSE_RANK() OVER (ORDER BY id) AS R
FROM sales

—NTILE — Распределяет строки в окне на заданное количество групп.
—Группы нумеруются, начиная с единицы.
—Для каждой строки функция NTILE возвращает номер группы, которой принадлежит строка.

SELECT id, product, qty,
NTILE(2) OVER (PARTITION BY id ORDER BY id) AS R,
NTILE(2) OVER (ORDER BY id) AS RO
FROM sales

—LAG — возвращает предыдущее значение для указанного столбца, сгруппированого по некому столбцу
—LEAD — возвращает следующее значение для указанного столбца, сгруппированого по некому столбцу
—FIRST_VALUE — возвращает первое значение для указанного столбца, сгруппированого по некому столбцу
—LAST_VALUE — возвращает последнее значение для указанного столбца, сгруппированого по некому столбцу

SELECT id, product, qty,
LAG(qty) OVER (PARTITION BY id ORDER BY id) AS [prev]
,LEAD(qty) OVER (PARTITION BY id ORDER BY id) AS [next]
,FIRST_VALUE(qty) OVER (PARTITION BY id ORDER BY id) AS [first]
,LAST_VALUE(qty) OVER (PARTITION BY id ORDER BY id) AS [last]
FROM sales

На этом наша статья окончена.

При создании использовал материалы с msdn, описание некоторых функций взял отсюда , очень доступно человек расписал , что некоторые функции делают: http://alexanderkobelev.blogspot.ru/2013/03/tsql-over-sql-server-2012.html

Для дальнейшего освоения темы рекомендую книгу: Microsoft SQL Server 2012. Высокопроизводительный код T-SQL. Оконные функции

Так-же замечу, что не все примеры работают в ms sql server 2012 express , в редакции стандарт все примеры — работают без проблем.

Анонимам нельзя оставоять комментарии, зарегистрируйтесь!

В оракловых аналитических функциях можно использовать предложение partition by для группировки данных возвращаемых SQL-select запросом. Именно для данных в этих группах будет применена аналитическая функция. Создадим тестовую таблицу для демонстрации примера:

Теперь заполним таблицу некоторым количеством данных — парами из цифр и дат:

Теперь создадим вьюху/представление которое будет иметь флаг указывающий на наибольшую/последнюю дату в месяце. Под последней датой имеем ввиду не 30.06.2000 для примера, а 14.06.2000 — последняя дата в июне, присутствующая в нашей таблице. Для реализации задуманного используем аналитическую функцию max вместе с предложением partition by.

Предложение OVER (Transact-SQL)

C помощью partition by сгруппируем данные возвращаемые запросом по месяцам, а функцию max используем для нахождения максимальной даты в каждой группе:

Запрос вернет следующее:

Источник: http://adp-gmbh.ch/ora/sql/analytical/partition_by.html

Также про использование предложения Partition by можно почитать в записи о сортировке по одному полю в таблице из нескольких столбцов.

Запись опубликована 01.06.2011 в 12:55 дп и размещена в рубрике Книга SQL. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.

CREATETABLE test_month ( val number, dt date );

ALTER session SET nls_date_format =’DD.MM.YYYY’; INSERTINTO test_month (val,dt)VALUES(18,’28.08.2000’); INSERTINTO test_month (val,dt)VALUES(19,’02.08.2000’); INSERTINTO test_month (val,dt)VALUES(22,’27.09.2000’); INSERTINTO test_month (val,dt)VALUES(23,’04.09.2000’); INSERTINTO test_month (val,dt)VALUES(20,’12.08.2000’); INSERTINTO test_month (val,dt)VALUES(24,’15.09.2000’); INSERTINTO test_month (val,dt)VALUES(19,’27.07.2000’); INSERTINTO test_month (val,dt)VALUES(18,’01.07.2000’); INSERTINTO test_month (val,dt)VALUES(21,’26.07.2000’); INSERTINTO test_month (val,dt)VALUES(24,’03.06.2000’); INSERTINTO test_month (val,dt)VALUES(22,’11.07.2000’); INSERTINTO test_month (val,dt)VALUES(21,’14.06.2000’);

CREATEVIEW test_last_of_month ASSELECT val, dt,(case when dt=max_dt then ‘Y’ else ‘N’ end) last_dt FROM(SELECT val, dt, max(dt) over (partition BY to_char(dt,’YYYY.MM’)) max_dt FROM test_month); SELECT*FROM test_last_of_month;

24 03.06.2000 N 21 14.06.2000 Y 9 27.07.2000 Y 18 01.07.2000 N 22 11.07.2000 N 21 26.07.2000 N 8 28.08.2000 Y 19 02.08.2000 N 20 12.08.2000 N 2 27.09.2000 Y 23 04.09.2000 N 24 15.09.2000 N

Я долго считал партиционирование плохой практикой, а само слово не любил из-за кальки с английского, которую крайне сложно выговорить с первого раза. И если слово «партиционирование» я так с первого раза и не выговариваю, то саму практику пришлось признать как необходимое и неизбежное зло. Чтобы никто не подумал, что я делаю что-то плохое, я использую для этого термин «инженерный компромисс», звучит умнее и не так обидно.

Если бы партиционирование укладывалось в рамки официальной документации, то и писать бы о нем не стоило.

Предложение OVER (Transact-SQL)

Но есть особенности, которые не сразу ясны из официальной документации, либо, вообще, в ней не раскрываются.

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

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

Во-первых, в должны быть IMMUTABLE функции. День у меня ушел на то, чтобы понять, что TIMESTAMP WITH TIME ZONE не является IMMUTABLE.

Пример того, как выглядит генерация триггера на вставку в партиционированную таблицу. Для этой статьи я добавил комментарии для больше понятности, но все равно выглядит громоздко.

После выполнения мы получим 36 новых таблиц в БД и триггер, похожий на этот.

При партиционировании перестает работать , а это значит, что при вставке новой записи нельзя узнать ее id. Для этого существует костыль, который на каждую вставку делает дополнительную вставку и удаление, чтобы получить id записи. Я не рискнул использовать его в бою, поскольку у нас и так очень интенсивная нагрузка на БД.

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

Какой выигрыш от такого усложнения? Во-первых, вместо одного большого индекса у вас будет теперь много маленьких, которые помещаются в память. Если вам надо сделать выборку по дате, то seq scan будет идти только по нужным партициям. В нашем случае, например, все запросы, в основном, делаются по последнему месяцу, поэтому она оказывается в кэше БД и, самое главное, помещается туда целиком. А как мы знаем, БД для web-проекта либо помещается в память, либо не работает, но об этом я напишу как-нибудь в другой раз.

Сам понимаю, что получилось немного сумбурно, поэтому с радостью раскрою непонятные вопросы в комментариях.

Автор Иван ЕвтуховичPostgreSQLpartitioning

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *