Группировка результатов поиска часто полезна для получения количества совпадений по группам или других агрегаций. Например, она полезна для создания графика, иллюстрирующего количество соответствующих постов в блоге за месяц, или для группировки результатов веб-поиска по сайту или постов на форуме по автору и т.д.
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 что-то" в конец вашего 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(), которая возвращает ключ текущей группы. Это полезно во многих случаях, особенно когда вы GROUP BY по 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 таблиц с одинаковой схемой (идентичный набор/порядок полей, но с возможными разными настройками токенизации).
- 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 |
+------+----------+