Оконные функции SQL простым языком с примерами
Сразу хочется отметить, что данная статья написана исключительно для людей, начинающих свой путь в изучении SQL и оконных функций. Здесь могут быть не разобраны сложные применения функций и могут не использоваться сложные формулировки определений — все написано максимально простым языком для базового понимания.
P.S. Если автор что-то не разобрал и не написал, значит он посчитал это не обязательным в рамках этой статьи)))
Для примеров будем использовать небольшую таблицу, которая показывает оценки учеников по разным предметам. В БД табличка выглядит следующим образом
--создание таблицы create table student_grades ( name varchar, subject varchar, grade int); -- наполнение таблицы данными insert into student_grades ( values ('Петя', 'русский', 4), ('Петя', 'физика', 5), ('Петя', 'история', 4), ('Маша', 'математика', 4), ('Маша', 'русский', 3), ('Маша', 'физика', 5), ('Маша', 'история', 3)); --запрос всех данных из таблицы select * from student_grades;
SQL часто используется для вычислений в данных различных метрик или агрегаций значений по измерениям. Помимо функций агрегации для этого широко используются оконные функции.
Оконная функция в SQL — функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце.
Партиции (окна из набора строк) — это набор строк, указанный для оконной функции по одному из столбцов или группе столбцов таблицы. Партиции для каждой оконной функции в запросе могут быть разделены по различным колонкам таблицы.
В чем заключается главное отличие оконных функций от функций агрегации с группировкой?
При использовании агрегирующих функций предложение GROUP BY сокращает количество строк в запросе с помощью их группировки.
При использовании оконных функций количество строк в запросе не уменьшается по сравнении с исходной таблицей.
Порядок расчета оконных функций в SQL запросе
Сначала выполняется команда выборки таблиц, их объединения и возможные подзапросы под командой FROM.
Далее выполняются условия фильтрации WHERE, группировки GROUP BY и возможная фильтрация c HAVING
Только потом применяется команда выборки столбцов SELECT и расчет оконных функций под выборкой.
После этого идет условие сортировки ORDER BY, где тоже можно указать столбец расчета оконной функции для сортировки.
Здесь важно уточнить, что партиции или окна оконных функций создаются после разделения таблицы на группы с помощью команды GROUP BY, если эта команда используется в запросе.
Синтаксис оконных функций
Синтаксис оконных функций вне зависимости от их класса будет так или иначе состоять из идентичных команд.
Оконные функции можно прописывать как под командой SELECT, так и в отдельном ключевом слове WINDOW, где окну дается алиас (псевдоним), к которому можно обращаться в SELECT выборке.
Классы Оконных функций
Множество оконных функций можно разделять на 3 класса:
- Агрегирующие (Aggregate)
- Ранжирующие (Ranking)
- Функции смещения (Value)
Агрегирующие:
Можно применять любую из агрегирующих функций — SUM, AVG, COUNT, MIN, MAX
select name, subject, grade, sum(grade) over (partition by name) as sum_grade, avg(grade) over (partition by name) as avg_grade, count(grade) over (partition by name) as count_grade, min(grade) over (partition by name) as min_grade, max(grade) over (partition by name) as max_grade from student_grades;
Ранжирующие:
В ранжирующих функция под ключевым словом OVER обязательным идет указание условия ORDER BY, по которому будет происходить сортировка ранжирования.
ROW_NUMBER() — функция вычисляет последовательность ранг (порядковый номер) строк внутри партиции, НЕЗАВИСИМО от того, есть ли в строках повторяющиеся значения или нет.
RANK() — функция вычисляет ранг каждой строки внутри партиции. Если есть повторяющиеся значения, функция возвращает одинаковый ранг для таких строчек, пропуская при этом следующий числовой ранг.
DENSE_RANK() — то же самое что и RANK, только в случае одинаковых значений DENSE_RANK не пропускает следующий числовой ранг, а идет последовательно.
select name, subject, grade, row_number() over (partition by name order by grade desc), rank() over (partition by name order by grade desc), dense_rank() over (partition by name order by grade desc) from student_grades;
Про NULL в случае ранжирования:
Для SQL пустые NULL значения будут определяться одинаковым рангом
Функции смещения:
Это функции, которые позволяют перемещаясь по выделенной партиции таблицы обращаться к предыдущему значению строки или крайним значениям строк в партиции.
LAG() — функция, возвращающая предыдущее значение столбца по порядку сортировки.
LEAD() — функция, возвращающая следующее значение столбца по порядку сортировки.
На простом примере видно, как можно в одной строке получить текущую оценку, предыдущую и следующую оценки Пети в четвертях.
--создание таблицы create table grades_quartal ( name varchar, quartal varchar, subject varchar, grade int); --наполнение таблицы данными insert into grades_quartal ( values ('Петя', '1 четверть', 'физика', 4), ('Петя', '2 четверть', 'физика', 3), ('Петя', '3 четверть', 'физика', 4), ('Петя', '4 четверть', 'физика', 5) ); --запрос всех данных из таблицы select * from grades_quartal;
select name, quartal, subject, grade, lag(grade) over (order by quartal) as previous_grade, lead(grade) over (order by quartal) as next_grade from grades_quartal;
FIRST_VALUE()/LAST_VALUE() — функции возвращающие первое или последнее значение столбца в указанной партиции. В качестве аргумента указывает столбец, значение которого нужно вернуть. В оконной функции под словом OVER обязательное указание ORDER BY условия.
В следующей версии статьи разберем отдельно такое понятие как фрейм окна функции или window frame и рассмотрим на простых примерах как он используется.
Партиционирование таблиц в MySql
Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям.
Разбиение таблицы на разделы очень полезно, если таблица содержит большое количество данных. Разбиение ускорит выборку и запись в таблицу. Вот некоторые преимущества партиционирования:
- Можно сохранять большее количество данных в одной таблице, чем может быть записано на одиночном диске или файловой системе.
- Данные, которые теряют полноценность, часто легко могут быть удалены из таблицы, удаляя раздел, содержащий только эти данные. Наоборот, процесс добавления новых данных в некоторых случаях может быть значительно облегчен, добавляя новый раздел специально для этих данных.
- Некоторые запросы могут быть значительно оптимизированы в том, что данные, удовлетворяющие предложению WHERE могут быть сохранены только на одном или большем количестве разделов, таким образом исключая любые остающиеся разделы из поиска. Поскольку разделы могут быть изменены после того, как разбитая на разделы таблица была создана, Вы можете реорганизовать данные, чтобы расширить частые запросы, которые, возможно, были медленными, когда схема выделения разделов была сначала установлена. Эта возможность, иногда упоминаемая как сокращение раздела (partition pruning), была выполнена в MySQL 5.1.6.
- Запросы, включающие составные функции типа SUM() и COUNT(), легко могут быть распараллелены. Простым примером такого запроса мог бы быть SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. Запрос может быть выполнен одновременно на каждом разделе, и результат получен просто суммируя результаты, полученные для всех разделов.
- Достижение большей производительности запроса благодаря разбросу данных по разным дискам.
Типы Разбиения
В MySql доступны следующие типы разделения:
- RANGE partitioning: назначает строки разделам, основанным на значениях столбца, попадающих внутрь заданного диапазона.
- LIST partitioning: подобно выделению разделов диапазоном, за исключением того, что раздел выбран основанным на столбцах, соответствующих одному из набора дискретных значений.
- HASH partitioning: раздел выбран основанным на значении, возвращенном определяемым пользователем выражением, которое функционирует на значениях столбца в строках, которые будут вставлены в таблицу. Функция может состоять из любого выражения, допустимого в MySQL, которое выдает не отрицательное целочисленное значение.
- KEY partitioning: подобно выделению разделов hash, за исключением того, что обеспечены только один или большее количество столбцов, которые будут оценены, и сервер MySQL обеспечивает собственную хэш-функцию. Эти столбцы могут содержать не целочисленные значения, так как хэш-функция, обеспеченная MySQL, гарантирует целочисленный результат, независимо от типа данных столбца.
Примеры
RANGE Partitioning:
Таблица, которая разбита на разделы диапазоном, разбита на разделы таким способом, которым каждый раздел содержит строки, для которых значение выражения выделения разделов находится внутри данного диапазона. Диапазоны должны быть непрерывны, но не перекрываться и определены, используя оператор VALUES LESS THAN
CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL) PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21));
В этой схеме выделения разделов все строки, соответствующие записям, занимающим номера от 1 до 5, сохранены в разделе p0, от 6 до 10 в p1 и т. д.
LIST Partitioning:
Как в выделении разделов RANGE, каждый раздел должен быть явно определен. Главное различие в том, что в выделении разделов списка, каждый раздел определен и выбран основываясь на членстве значения столбца в одном наборе значений списков.
CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT) PARTITION BY LIST(store_id) ( PARTITION pNorth VALUES IN (3,5,6,9,17), PARTITION pEast VALUES IN (1,2,10,11,19,20), PARTITION pWest VALUES IN (4,12,13,14,18), PARTITION pCentral VALUES IN (7,8,15,16));
HASH Partitioning:
Выделение разделов HASH используется прежде всего, чтобы гарантировать четкое распределение данных среди предопределенного числа разделов. Например, следующая инструкция создает таблицу, которая использует хэширование на столбце store_id и разделена на 4 раздела:
CREATE TABLE employees (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT) PARTITION BY HASH(store_id) PARTITIONS 4;
KEY Partitioning:
Выделение разделов ключом подобно выделению разделов хэшем за исключением того, что выделение разделов хэшем использует определяемое пользователем выражение, а хэш-функция для выделения разделов ключом обеспечена MySQL.
CREATE TABLE tm1 (s1 CHAR(32) PRIMARY KEY) PARTITION BY KEY(s1) PARTITIONS 10;
Партицирование баз данных
Партицирование, или секционирование, базы данных — разделение данных, хранящихся в базе данных, на части. Партицирование позволяет осуществлять горизонтальное масштабирование, так как вертикальное масштабирование имеет потолок — нельзя бесконечно долго добавлять память на один сервер. При разделении же данных на части их можно хранить на разных серверах, а серверы добавлять по мере необходимости. Доступность и производительность повышаются, так как запросы обращаются не к огромному куску данных, а к более маленьким и легковесным частям.
Управление ИТ услугами
- горизонтальное партицирование — таблица данных разбивается на строки;
- вертикальное партицирование — таблица данных разбивается на столбцы;
- функциональное партицирование — данные группируются согласно контексту их использования в системе.
Манипуляции с партициями и кусками
Для работы с партициями доступны следующие операции:
- DETACH PARTITION — перенести партицию в директорию detached ;
- DROP PARTITION — удалить партицию;
- ATTACH PARTITION | PART — добавить партицию/кусок в таблицу из директории detached ;
- ATTACH PARTITION FROM — скопировать партицию из другой таблицы;
- REPLACE PARTITION — скопировать партицию из другой таблицы с заменой;
- MOVE PARTITION TO TABLE — переместить партицию в другую таблицу;
- CLEAR COLUMN IN PARTITION — удалить все значения в столбце для заданной партиции;
- CLEAR INDEX IN PARTITION — очистить построенные вторичные индексы для заданной партиции;
- FREEZE PARTITION — создать резервную копию партиции;
- UNFREEZE PARTITION — удалить резервную копию партиции;
- FETCH PARTITION | PART — скачать партицию/кусок с другого сервера;
- MOVE PARTITION | PART — переместить партицию/кускок на другой диск или том.
- UPDATE IN PARTITION — обновить данные внутри партиции по условию.
- DELETE IN PARTITION — удалить данные внутри партиции по условию.
DETACH PARTITION | PART
ALTER TABLE table_name [ON CLUSTER cluster] DETACH PARTITION|PART partition_expr Перемещает заданную партицию в директорию detached . Сервер не будет знать об этой партиции до тех пор, пока вы не выполните запрос ATTACH.
ALTER TABLE mt DETACH PARTITION '2020-11-21'; ALTER TABLE mt DETACH PART 'all_2_2_0'; Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
После того как запрос будет выполнен, вы сможете производить любые операции с данными в директории detached . Например, можно удалить их из файловой системы.
Запрос реплицируется — данные будут перенесены в директорию detached и забыты на всех репликах. Обратите внимание, запрос может быть отправлен только на реплику-лидер. Чтобы узнать, является ли реплика лидером, выполните запрос SELECT к системной таблице system.replicas. Либо можно выполнить запрос DETACH на всех репликах — тогда на всех репликах, кроме реплик-лидеров (поскольку допускается несколько лидеров), запрос вернет ошибку.
DROP PARTITION | PART
ALTER TABLE table_name [ON CLUSTER cluster] DROP PARTITION|PART partition_expr Удаляет партицию. Партиция помечается как неактивная и будет полностью удалена примерно через 10 минут.
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
Запрос реплицируется — данные будут удалены на всех репликах.
ALTER TABLE mt DROP PARTITION '2020-11-21'; ALTER TABLE mt DROP PART 'all_4_4_0'; DROP DETACHED PARTITION | PART
ALTER TABLE table_name [ON CLUSTER cluster] DROP DETACHED PARTITION|PART partition_expr Удаляет из detached кусок или все куски, принадлежащие партиции. Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
ATTACH PARTITION | PART
ALTER TABLE table_name [ON CLUSTER cluster] ATTACH PARTITION|PART partition_expr Добавляет данные в таблицу из директории detached . Можно добавить данные как для целой партиции, так и для отдельного куска. Примеры:
ALTER TABLE visits ATTACH PARTITION 201901; ALTER TABLE visits ATTACH PART 201901_2_2_0; Как корректно задать имя партиции или куска, см. в разделе Как задавать имя партиции в запросах ALTER.
Этот запрос реплицируется. Реплика-иницатор проверяет, есть ли данные в директории detached . Если данные есть, то запрос проверяет их целостность. В случае успеха данные добавляются в таблицу.
Если реплика, не являющаяся инициатором запроса, получив команду присоединения, находит кусок с правильными контрольными суммами в своей собственной папке detached , она присоединяет данные, не скачивая их с других реплик. Если нет куска с правильными контрольными суммами, данные загружаются из любой реплики, имеющей этот кусок.
Вы можете поместить данные в директорию detached на одной реплике и с помощью запроса ALTER . ATTACH добавить их в таблицу на всех репликах.
ATTACH PARTITION FROM
ALTER TABLE table2 [ON CLUSTER cluster] ATTACH PARTITION partition_expr FROM table1 Копирует партицию из таблицы table1 в таблицу table2 .
Обратите внимание, что:
- Данные не удаляются ни из table1 , ни из table2 .
- table1 может быть временной таблицей.
Следует иметь в виду:
- Таблицы должны иметь одинаковую структуру.
- Для таблиц должен быть задан одинаковый ключ партиционирования, одинаковый ключ сортировки и одинаковый первичный ключ.
- Для таблиц должна быть задана одинаковая политика хранения (диск, на котором хранится партиция, должен быть доступен для обеих таблиц).
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
REPLACE PARTITION
ALTER TABLE table2 [ON CLUSTER cluster] REPLACE PARTITION partition_expr FROM table1 Копирует партицию из таблицы table1 в таблицу table2 с заменой существующих данных в table2 .
Обратите внимание, что:
- Данные из table1 не удаляются.
- table1 может быть временной таблицей.
Следует иметь в виду:
- Таблицы должны иметь одинаковую структуру.
- Для таблиц должен быть задан одинаковый ключ партиционирования, одинаковый ключ сортировки и одинаковый первичный ключ.
- Для таблиц должна быть задана одинаковая политика хранения (диск, на котором хранится партиция, должен быть доступен для обеих таблиц).
Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
MOVE PARTITION TO TABLE
ALTER TABLE table_source [ON CLUSTER cluster] MOVE PARTITION partition_expr TO TABLE table_dest Перемещает партицию из таблицы table_source в таблицу table_dest (добавляет к существующим данным в table_dest ) с удалением данных из таблицы table_source .
Следует иметь в виду:
- Таблицы должны иметь одинаковую структуру.
- Для таблиц должен быть задан одинаковый ключ партиционирования, одинаковый ключ сортировки и одинаковый первичный ключ.
- Для таблиц должна быть задана одинаковая политика хранения (диск, на котором хранится партиция, должен быть доступен для обеих таблиц).
- Движки таблиц должны быть одинакового семейства (реплицированные или нереплицированные).
CLEAR COLUMN IN PARTITION
ALTER TABLE table_name [ON CLUSTER cluster] CLEAR COLUMN column_name IN PARTITION partition_expr Сбрасывает все значения в столбце для заданной партиции. Если для столбца определено значение по умолчанию (в секции DEFAULT ), то будет выставлено это значение.
ALTER TABLE visits CLEAR COLUMN hour in PARTITION 201902 CLEAR INDEX IN PARTITION
ALTER TABLE table_name [ON CLUSTER cluster] CLEAR INDEX index_name IN PARTITION partition_expr Работает как CLEAR COLUMN , но сбрасывает индексы вместо данных в столбцах.
FREEZE PARTITION
ALTER TABLE table_name [ON CLUSTER cluster] FREEZE [PARTITION partition_expr] [WITH NAME 'backup_name'] Создаёт резервную копию для заданной партиции. Если выражение PARTITION опущено, резервные копии будут созданы для всех партиций.
Примечание
Создание резервной копии не требует остановки сервера.
Для таблиц старого стиля имя партиций можно задавать в виде префикса (например, 2019 ). В этом случае, резервные копии будут созданы для всех соответствующих партиций. Подробнее о том, как корректно задать имя партиции, см. в разделе Как задавать имя партиции в запросах ALTER.
Запрос формирует для текущего состояния таблицы жесткие ссылки на данные в этой таблице. Ссылки размещаются в директории /var/lib/clickhouse/shadow/N/. , где:
- /var/lib/clickhouse/ — рабочая директория ClickHouse, заданная в конфигурационном файле;
- N — инкрементальный номер резервной копии.
- если задан параметр WITH NAME , то вместо инкрементального номера используется значение параметра ‘backup_name’ .
Примечание
При использовании нескольких дисков для хранения данных таблицы директория shadow/N появляется на каждом из дисков, на которых были куски, попавшие под выражение PARTITION .
Структура директорий внутри резервной копии такая же, как внутри /var/lib/clickhouse/ . Запрос выполнит chmod для всех файлов, запрещая запись в них.
Обратите внимание, запрос ALTER TABLE t FREEZE PARTITION не реплицируется. Он создает резервную копию только на локальном сервере. После создания резервной копии данные из /var/lib/clickhouse/shadow/ можно скопировать на удалённый сервер, а локальную копию удалить.
Резервная копия создается почти мгновенно (однако, сначала запрос дожидается завершения всех запросов, которые выполняются для соответствующей таблицы).
ALTER TABLE t FREEZE PARTITION копирует только данные, но не метаданные таблицы. Чтобы сделать резервную копию метаданных таблицы, скопируйте файл /var/lib/clickhouse/metadata/database/table.sql
Чтобы восстановить данные из резервной копии, выполните следующее:
- Создайте таблицу, если она ещё не существует. Запрос на создание можно взять из .sql файла (замените в нём ATTACH на CREATE ).
- Скопируйте данные из директории data/database/table/ внутри резервной копии в директорию /var/lib/clickhouse/data/database/table/detached/ .
- С помощью запросов ALTER TABLE t ATTACH PARTITION добавьте данные в таблицу.
Восстановление данных из резервной копии не требует остановки сервера.
Подробнее о резервном копировании и восстановлении данных читайте в разделе Резервное копирование данных.
UNFREEZE PARTITION
ALTER TABLE table_name [ON CLUSTER cluster] UNFREEZE [PARTITION 'part_expr'] WITH NAME 'backup_name' Удаляет с диска «замороженные» партиции с указанным именем. Если секция PARTITION опущена, запрос удаляет резервную копию всех партиций сразу.
FETCH PARTITION | PART
ALTER TABLE table_name [ON CLUSTER cluster] FETCH PARTITION|PART partition_expr FROM 'path-in-zookeeper' Загружает партицию с другого сервера. Этот запрос работает только для реплицированных таблиц.
Запрос выполняет следующее:
- Загружает партицию/кусок с указанного шарда. Путь к шарду задается в секции FROM (‘path-in-zookeeper’). Обратите внимание, нужно задавать путь к шарду в ZooKeeper.
- Помещает загруженные данные в директорию detached таблицы table_name . Чтобы прикрепить эти данные к таблице, используйте запрос ATTACH PARTITION | PART.
ALTER TABLE users FETCH PARTITION 201902 FROM '/clickhouse/tables/01-01/visits'; ALTER TABLE users ATTACH PARTITION 201902; - FETCH PART
ALTER TABLE users FETCH PART 201901_2_2_0 FROM '/clickhouse/tables/01-01/visits'; ALTER TABLE users ATTACH PART 201901_2_2_0; Следует иметь в виду:
- Запрос ALTER TABLE t FETCH PARTITION|PART не реплицируется. Он загружает партицию в директорию detached только на локальном сервере.
- Запрос ALTER TABLE t ATTACH реплицируется — он добавляет данные в таблицу сразу на всех репликах. На одной из реплик данные будут добавлены из директории detached , а на других — из соседних реплик.
Перед загрузкой данных система проверяет, существует ли партиция и совпадает ли её структура со структурой таблицы. При этом автоматически выбирается наиболее актуальная реплика среди всех живых реплик.
Несмотря на то что запрос называется ALTER TABLE , он не изменяет структуру таблицы и не изменяет сразу доступные данные в таблице.
MOVE PARTITION | PART
Перемещает партицию или кусок данных на другой том или диск для таблиц с движком MergeTree . Смотрите Хранение данных таблицы на нескольких блочных устройствах.
ALTER TABLE table_name [ON CLUSTER cluster] MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name' Запрос ALTER TABLE t MOVE :
- Не реплицируется, т.к. на разных репликах могут быть различные конфигурации политик хранения.
- Возвращает ошибку, если указан несконфигурированный том или диск. Ошибка также возвращается в случае невыполнения условий перемещения данных, которые указаны в конфигурации политики хранения.
- Может возвращать ошибку в случае, когда перемещаемые данные уже оказались перемещены в результате фонового процесса, конкурентного запроса ALTER TABLE t MOVE или как часть результата фоновой операции слияния. В данном случае никаких дополнительных действий от пользователя не требуется.
ALTER TABLE hits MOVE PART '20190301_14343_16206_438' TO VOLUME 'slow' ALTER TABLE hits MOVE PARTITION '2019-09-01' TO DISK 'fast_ssd' UPDATE IN PARTITION
Манипулирует данными в указанной партиции, соответствующими заданному выражению фильтрации. Реализовано как мутация mutation.
ALTER TABLE [db.]table [ON CLUSTER cluster] UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id] WHERE filter_expr Пример
ALTER TABLE mt UPDATE x = x + 1 IN PARTITION 2 WHERE p = 2; Смотрите также
DELETE IN PARTITION
Удаляет данные в указанной партиции, соответствующие указанному выражению фильтрации. Реализовано как мутация mutation.
ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE [IN PARTITION partition_id] WHERE filter_expr Пример
ALTER TABLE mt DELETE IN PARTITION 2 WHERE p = 2; Смотрите также
Как задавать имя партиции в запросах ALTER
Чтобы задать нужную партицию в запросах ALTER . PARTITION , можно использовать:
- Имя партиции. Посмотреть имя партиции можно в столбце partition системной таблицы system.parts. Например, ALTER TABLE visits DETACH PARTITION 201901 .
- Кортеж из выражений или констант, совпадающий (в типах) с кортежем партиционирования. В случае ключа партиционирования из одного элемента, выражение следует обернуть в функцию tuple(. ) . Например, ALTER TABLE visits DETACH PARTITION tuple(toYYYYMM(toDate(‘2019-01-25’))) .
- Строковый идентификатор партиции. Идентификатор партиции используется для именования кусков партиции на файловой системе и в ZooKeeper. В запросах ALTER идентификатор партиции нужно указывать в секции PARTITION ID , в одинарных кавычках. Например, ALTER TABLE visits DETACH PARTITION ID ‘201901’ .
- Для запросов ATTACH PART и DROP DETACHED PART: чтобы задать имя куска партиции, используйте строковой литерал со значением из столбца name системной таблицы system.detached_parts. Например, ALTER TABLE visits ATTACH PART ‘201901_1_1_0’ .
Использование кавычек в имени партиций зависит от типа данных столбца, по которому задано партиционирование. Например, для столбца с типом String имя партиции необходимо указывать в кавычках (одинарных). Для типов Date и Int* кавычки указывать не нужно.
Замечание: для таблиц старого стиля партицию можно указывать и как число 201901 , и как строку ‘201901’ . Синтаксис для таблиц нового типа более строг к типам (аналогично парсеру входного формата VALUES).
Правила, сформулированные выше, актуальны также для запросов OPTIMIZE. Чтобы указать единственную партицию непартиционированной таблицы, укажите PARTITION tuple() . Например:
OPTIMIZE TABLE table_not_partitioned PARTITION tuple() FINAL; IN PARTITION указывает на партицию, для которой применяются выражения UPDATE или DELETE в результате запроса ALTER TABLE . Новые куски создаются только в указанной партиции. Таким образом, IN PARTITION помогает снизить нагрузку, когда таблица разбита на множество партиций, а вам нужно обновить данные лишь точечно.