Группировка результатов поиска часто полезна для получения количества совпадений по каждой группе или других агрегированных данных. Например, это удобно для создания графика с количеством совпадающих блог-постов по месяцам или группировки веб-поиска по сайтам, а сообщений на форумах — по авторам и т.д.
Manticore поддерживает группировку результатов поиска по одному или нескольким столбцам и вычисляемым выражениям. Результаты могут:
- Быть отсортированы внутри группы
- Возвращать более одной строки на группу
- Фильтровать группы
- Сортировать группы
- Агрегироваться с использованием функций агрегации
- SQL
- JSON
Общий синтаксис
SELECT {* | SELECT_expr [, SELECT_expr ...]}
...
GROUP BY {field_name | alias } [, ...]
[HAVING where_condition]
[WITHIN GROUP ORDER BY field_name {ASC | DESC} [, ...]]
...
SELECT_expr: { field_name | function_name(...) }
where_condition: {aggregation expression alias | COUNT(*)}Группировка очень проста — просто добавьте "GROUP BY smth" в конец вашего запроса SELECT. Что именно — может быть:
- Любое поле из таблицы, не являющееся полнотекстовым: целое число, число с плавающей точкой, строка, MVA (мультизначный атрибут)
- Или, если вы использовали псевдоним в списке
SELECT, вы можете сгруппировать по нему
Вы можете опустить любые функции агрегации в списке SELECT, и это всё равно будет работать:
- SQL
SELECT release_year FROM films GROUP BY release_year LIMIT 5;+--------------+
| release_year |
+--------------+
| 2004 |
| 2002 |
| 2001 |
| 2005 |
| 2000 |
+--------------+В большинстве случаев, однако, вы захотите получить агрегированные данные для каждой группы, такие как:
COUNT(*), чтобы просто получить количество элементов в каждой группе- или
AVG(field), чтобы вычислить среднее значение поля в группе
Для HTTP JSON-запросов использование одного контейнера aggs с limit=0 на уровне основного запроса работает аналогично SQL-запросу с GROUP BY и COUNT(*), обеспечивая эквивалентное поведение и производительность.
- SQL1
- SQL2
- JSON
- PHP
- Python
- Python-asyncio
- Javascript
- Java
- C#
- Rust
- TypeScript
- Go
SELECT release_year, count(*) FROM films GROUP BY release_year LIMIT 5;+--------------+----------+
| release_year | count(*) |
+--------------+----------+
| 2004 | 108 |
| 2002 | 108 |
| 2001 | 91 |
| 2005 | 93 |
| 2000 | 97 |
+--------------+----------+По умолчанию группы не сортируются, и следующим типичным шагом является упорядочение их по чему-то, например, по полю, по которому происходит группировка:
- SQL
SELECT release_year, count(*) from films GROUP BY release_year ORDER BY release_year asc limit 5;+--------------+----------+
| release_year | count(*) |
+--------------+----------+
| 2000 | 97 |
| 2001 | 91 |
| 2002 | 108 |
| 2003 | 106 |
| 2004 | 108 |
+--------------+----------+Или вы можете сортировать по агрегации:
- по
count(*), чтобы сначала отображать группы с наибольшим количеством элементов - по
avg(rental_rate), чтобы показывать сначала фильмы с наивысшим рейтингом. Обратите внимание, что в примере это делается через псевдоним:avg(rental_rate)сначала отображается вavgв спискеSELECT, а затем просто делаетсяORDER BY avg
- SQL1
- SQL2
SELECT release_year, count(*) FROM films GROUP BY release_year ORDER BY count(*) desc LIMIT 5;+--------------+----------+
| release_year | count(*) |
+--------------+----------+
| 2004 | 108 |
| 2002 | 108 |
| 2003 | 106 |
| 2006 | 103 |
| 2008 | 102 |
+--------------+----------+В некоторых случаях вы можете захотеть группировать не по одному полю, а сразу по нескольким, например, по категории фильма и году:
- SQL
- JSON
SELECT category_id, release_year, count(*) FROM films GROUP BY category_id, release_year ORDER BY category_id ASC, release_year ASC;+-------------+--------------+----------+
| category_id | release_year | count(*) |
+-------------+--------------+----------+
| 1 | 2000 | 5 |
| 1 | 2001 | 2 |
| 1 | 2002 | 6 |
| 1 | 2003 | 6 |
| 1 | 2004 | 5 |
| 1 | 2005 | 10 |
| 1 | 2006 | 4 |
| 1 | 2007 | 5 |
| 1 | 2008 | 7 |
| 1 | 2009 | 14 |
| 2 | 2000 | 10 |
| 2 | 2001 | 5 |
| 2 | 2002 | 6 |
| 2 | 2003 | 6 |
| 2 | 2004 | 10 |
| 2 | 2005 | 4 |
| 2 | 2006 | 5 |
| 2 | 2007 | 8 |
| 2 | 2008 | 8 |
| 2 | 2009 | 4 |
+-------------+--------------+----------+Иногда полезно видеть не просто один элемент на группу, а несколько. Это легко достигается с помощью GROUP N BY. Например, в следующем примере мы получаем два фильма на каждый год, а не только один, как это было бы при обычном GROUP BY release_year.
- SQL
SELECT release_year, title FROM films GROUP 2 BY release_year ORDER BY release_year DESC LIMIT 6;+--------------+-----------------------------+
| release_year | title |
+--------------+-----------------------------+
| 2009 | ALICE FANTASIA |
| 2009 | ALIEN CENTER |
| 2008 | AMADEUS HOLY |
| 2008 | ANACONDA CONFESSIONS |
| 2007 | ANGELS LIFE |
| 2007 | ARACHNOPHOBIA ROLLERCOASTER |
+--------------+-----------------------------+Еще одним важным требованием аналитики является сортировка элементов внутри группы. Для этого используйте конструкцию WITHIN GROUP ORDER BY ... {ASC|DESC}. Например, давайте выберем фильм с наивысшим рейтингом за каждый год. Обратите внимание, что это работает параллельно с обычным ORDER BY:
WITHIN GROUP ORDER BYсортирует результаты внутри группы- тогда как просто
GROUP BYсортирует самих группы
Эти два работают полностью независимо друг от друга.
- SQL
SELECT release_year, title, rental_rate FROM films GROUP BY release_year WITHIN GROUP ORDER BY rental_rate DESC ORDER BY release_year DESC LIMIT 5;+--------------+------------------+-------------+
| release_year | title | rental_rate |
+--------------+------------------+-------------+
| 2009 | AMERICAN CIRCUS | 4.990000 |
| 2008 | ANTHEM LUKE | 4.990000 |
| 2007 | ATTACKS HATE | 4.990000 |
| 2006 | ALADDIN CALENDAR | 4.990000 |
| 2005 | AIRPLANE SIERRA | 4.990000 |
+--------------+------------------+-------------+HAVING expression — полезное выражение для фильтрации групп. В то время как WHERE применяется до группировки, HAVING работает с группами. Например, оставим только те годы, когда средняя ставка аренды фильмов за этот год была выше 3. Мы получаем всего четыре года:
- SQL
SELECT release_year, avg(rental_rate) avg FROM films GROUP BY release_year HAVING avg > 3;+--------------+------------+
| release_year | avg |
+--------------+------------+
| 2002 | 3.08259249 |
| 2001 | 3.09989142 |
| 2000 | 3.17556739 |
| 2006 | 3.26184368 |
+--------------+------------+Примечание: Значение total_found в метаинформации поискового запроса отражает количество групп, которые соответствуют условию HAVING. Это обеспечивает правильную постраничную навигацию при использовании условий HAVING вместе с GROUP BY.
Существует функция GROUPBY(), которая возвращает ключ текущей группы. Она полезна во многих случаях, особенно когда вы группируете по MVA или по значению JSON.
Её также можно использовать в HAVING, например, чтобы оставить только года 2000 и 2002.
Обратите внимание, что GROUPBY() не рекомендуется использовать при группировке сразу по нескольким полям. Она всё равно будет работать, но так как ключ группы в таком случае является составным из значений полей, он может появиться в неожиданном виде.
- SQL
SELECT release_year, count(*) FROM films GROUP BY release_year HAVING GROUPBY() IN (2000, 2002);+--------------+----------+
| release_year | count(*) |
+--------------+----------+
| 2002 | 108 |
| 2000 | 97 |
+--------------+----------+Manticore поддерживает группировку по MVA. Чтобы показать, как это работает, давайте создадим таблицу "shoes" с MVA "sizes" и добавим в неё несколько документов:
create table shoes(title text, sizes multi);
insert into shoes values(0,'nike',(40,41,42)),(0,'adidas',(41,43)),(0,'reebook',(42,43));итак, у нас есть:
SELECT * FROM shoes;
+---------------------+----------+---------+
| id | sizes | title |
+---------------------+----------+---------+
| 1657851069130080265 | 40,41,42 | nike |
| 1657851069130080266 | 41,43 | adidas |
| 1657851069130080267 | 42,43 | reebook |
+---------------------+----------+---------+Если теперь сделать GROUP BY по "sizes", будут обработаны все наши мульти-значимые атрибуты и возвращена агрегация для каждого, в данном случае просто подсчёт:
- SQL
- JSON
- PHP
- Python
- Javascript
- Python-asyncio
- Java
- C#
- Rust
- Go
SELECT groupby() gb, count(*) FROM shoes GROUP BY sizes ORDER BY gb asc;+------+----------+
| gb | count(*) |
+------+----------+
| 40 | 1 |
| 41 | 2 |
| 42 | 2 |
| 43 | 2 |
+------+----------+If you have a field of type JSON, you can GROUP BY any node from it. To demonstrate this, let's create a table "products" with a few documents, each having a color in the "meta" JSON field:
create table products(title text, meta json);
insert into products values(0,'nike','{"color":"red"}'),(0,'adidas','{"color":"red"}'),(0,'puma','{"color":"green"}');This gives us:
SELECT * FROM products;
+---------------------+-------------------+--------+
| id | meta | title |
+---------------------+-------------------+--------+
| 1657851069130080268 | {"color":"red"} | nike |
| 1657851069130080269 | {"color":"red"} | adidas |
| 1657851069130080270 | {"color":"green"} | puma |
+---------------------+-------------------+--------+To group the products by color, we can simply use GROUP BY meta.color, and to display the corresponding group key in the SELECT list, we can use GROUPBY():
- SQL
- JSON
- PHP
- Python
- Javascript
- Java
- C#
- Rust
- TypeScript
- Go
SELECT groupby() color, count(*) from products GROUP BY meta.color;+-------+----------+
| color | count(*) |
+-------+----------+
| red | 2 |
| green | 1 |
+-------+----------+Помимо COUNT(*), который возвращает количество элементов в каждой группе, вы можете использовать различные другие функции агрегации:
В то время как COUNT(*) возвращает количество всех элементов в группе, COUNT(DISTINCT field) возвращает количество уникальных значений поля в группе, что может значительно отличаться от общего количества. Например, в группе может быть 100 элементов, но все с одинаковым значением определённого поля. COUNT(DISTINCT field) помогает определить это. Чтобы продемонстрировать это, создадим таблицу "students" с именем студента, возрастом и специализацией:
CREATE TABLE students(name text, age int, major string);
INSERT INTO students values(0,'John',21,'arts'),(0,'William',22,'business'),(0,'Richard',21,'cs'),(0,'Rebecca',22,'cs'),(0,'Monica',21,'arts');так у нас есть:
MySQL [(none)]> SELECT * from students;
+---------------------+------+----------+---------+
| id | age | major | name |
+---------------------+------+----------+---------+
| 1657851069130080271 | 21 | arts | John |
| 1657851069130080272 | 22 | business | William |
| 1657851069130080273 | 21 | cs | Richard |
| 1657851069130080274 | 22 | cs | Rebecca |
| 1657851069130080275 | 21 | arts | Monica |
+---------------------+------+----------+---------+В примере видно, что при группировке по "major" и отображении как COUNT(*), так и COUNT(DISTINCT age), становится ясно, что есть два студента, выбравших специализацию "cs" с двумя уникальными возрастами, но для "arts" тоже два студента, но только один уникальный возраст.
В запросе может быть не более одного COUNT(DISTINCT).
По умолчанию подсчёты являются приблизительными
Фактически, некоторые из них точные, другие приближённые. Подробнее об этом ниже.
Manticore поддерживает два алгоритма подсчёта количества уникальных значений. Один — устаревший алгоритм, который использует много памяти и обычно медленный. Он собирает пары {group; value}, сортирует их и периодически удаляет дубликаты. Преимущество этого подхода — гарантированная точность подсчётов в обычной таблице. Его можно включить, установив опцию distinct_precision_threshold в 0.
Другой алгоритм (включён по умолчанию) загружает подсчёты в хеш-таблицу и возвращает её размер. Если хеш-таблица становится слишком большой, её содержимое перемещается в HyperLogLog. Здесь подсчёты становятся приближенными, так как HyperLogLog — вероятностный алгоритм. Преимущество в том, что максимальное использование памяти на одну группу фиксировано и зависит от точности HyperLogLog. Общее потребление памяти также зависит от настройки max_matches, которая отражает количество групп.
Опция distinct_precision_threshold задаёт порог, ниже которого подсчёты гарантированно точные. Настройки точности HyperLogLog и порог конверсии из хеш-таблицы в HyperLogLog выводятся из этой опции. Важно использовать этот параметр с осторожностью, так как его удвоение удвоит максимальный объём памяти, необходимый для вычисления подсчётов. Максимальное использование памяти можно примерно оценить формулой: 64 * max_matches * distinct_precision_threshold. Обратите внимание, что это худший сценарий, и в большинстве случаев использование памяти будет значительно меньше.
COUNT(DISTINCT) для распределённой таблицы или RT-таблицы, состоящей из нескольких дисковых чанков, может возвращать неточные результаты, но результат должен быть точным для распределённой таблицы, состоящей из локальных обычных или RT-таблиц с одинаковой схемой (одинаковым набором/порядком полей, но с возможными различиями в настройках токенизации).
- SQL
SELECT major, count(*), count(distinct age) FROM students GROUP BY major;+----------+----------+---------------------+
| major | count(*) | count(distinct age) |
+----------+----------+---------------------+
| arts | 2 | 1 |
| business | 1 | 1 |
| cs | 2 | 2 |
+----------+----------+---------------------+Часто хочется лучше понять содержимое каждой группы. Для этого можно использовать GROUP N BY, но он вернёт дополнительные строки, которые вам могут быть не нужны в выводе. GROUP_CONCAT() улучшает группировку, конкатенируя значения конкретного поля в группе. Возьмём предыдущий пример и улучшим его, отображая все возраста в каждой группе.
GROUP_CONCAT(field) возвращает список значений, разделённых запятыми.
- SQL
SELECT major, count(*), count(distinct age), group_concat(age) FROM students GROUP BY major+----------+----------+---------------------+-------------------+
| major | count(*) | count(distinct age) | group_concat(age) |
+----------+----------+---------------------+-------------------+
| arts | 2 | 1 | 21,21 |
| business | 1 | 1 | 22 |
| cs | 2 | 2 | 21,22 |
+----------+----------+---------------------+-------------------+- SQL
SELECT release_year year, sum(rental_rate) sum, min(rental_rate) min, max(rental_rate) max, avg(rental_rate) avg FROM films GROUP BY release_year ORDER BY year asc LIMIT 5;+------+------------+----------+----------+------------+
| year | sum | min | max | avg |
+------+------------+----------+----------+------------+
| 2000 | 308.030029 | 0.990000 | 4.990000 | 3.17556739 |
| 2001 | 282.090118 | 0.990000 | 4.990000 | 3.09989142 |
| 2002 | 332.919983 | 0.990000 | 4.990000 | 3.08259249 |
| 2003 | 310.940063 | 0.990000 | 4.990000 | 2.93339682 |
| 2004 | 300.920044 | 0.990000 | 4.990000 | 2.78629661 |
+------+------------+----------+----------+------------+Группировка выполняется в фиксированной памяти, которая зависит от настройки max_matches. Если max_matches позволяет сохранить все найденные группы, результаты будут на 100% точными. Однако, если значение max_matches меньше, результаты будут менее точными.
При параллельной обработке ситуация может стать сложнее. При включённом pseudo_sharding и/или использовании RT-таблицы с несколькими дисковыми чанками каждый чанк или псевдо-шард получает набор результатов не больше max_matches. Это может привести к неточностям в агрегатах и подсчётах групп при объединении результатов из разных потоков. Для устранения этого можно использовать большее значение max_matches или отключить параллельную обработку.
Если Manticore обнаруживает, что группировка может вернуть неточные результаты, он попытается увеличить max_matches до max_matches_increase_threshold. Обнаружение основано на числе уникальных значений атрибута группировки, получаемом из вторичных индексов (если они есть).
Для обеспечения точных агрегатов и/или подсчёта групп при использовании RT-таблиц или pseudo_sharding можно включить accurate_aggregation. Это попытается увеличить max_matches до порога, а если порог будет недостаточным, Manticore отключит параллельную обработку для запроса.
- SQL
MySQL [(none)]> SELECT release_year year, count(*) FROM films GROUP BY year limit 5;
+------+----------+
| year | count(*) |
+------+----------+
| 2004 | 108 |
| 2002 | 108 |
| 2001 | 91 |
| 2005 | 93 |
| 2000 | 97 |
+------+----------+
MySQL [(none)]> SELECT release_year year, count(*) FROM films GROUP BY year limit 5 option max_matches=1;
+------+----------+
| year | count(*) |
+------+----------+
| 2004 | 76 |
+------+----------+
MySQL [(none)]> SELECT release_year year, count(*) FROM films GROUP BY year limit 5 option max_matches=2;
+------+----------+
| year | count(*) |
+------+----------+
| 2004 | 76 |
| 2002 | 74 |
+------+----------+
MySQL [(none)]> SELECT release_year year, count(*) FROM films GROUP BY year limit 5 option max_matches=3;
+------+----------+
| year | count(*) |
+------+----------+
| 2004 | 108 |
| 2002 | 108 |
| 2001 | 91 |
+------+----------+