Как следует из названия, я хотел бы выбрать первую строку каждого набора строк, сгруппированных с помощью GROUP BY
.
В частности, если у меня есть таблица purchases
, которая выглядит так:
SELECT * FROM purchases;
Мой вывод:
id | customer | total ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
Я хотел бы запросить id
самой большой покупки (total
), сделанной каждым customer
. Что-то вроде этого:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Ожидаемый результат:
FIRST(id) | customer | FIRST(total) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3
WITH summary AS (
SELECT p.id,
p.customer,
p.total,
ROW_NUMBER() OVER(PARTITION BY p.customer
ORDER BY p.total DESC) AS rk
FROM PURCHASES p)
SELECT s.*
FROM summary s
WHERE s.rk = 1
Но вам нужно добавить логику для разрыва связей:
SELECT MIN(x.id), -- change to MAX if you want the highest
x.customer,
x.total
FROM PURCHASES x
JOIN (SELECT p.customer,
MAX(total) AS max_total
FROM PURCHASES p
GROUP BY p.customer) y ON y.customer = x.customer
AND y.max_total = x.total
GROUP BY x.customer, x.total
В PostgreSQL это обычно проще и быстрее (подробнее об оптимизации производительности ниже):
SELECT DISTINCT ON (customer)
id, customer, total
FROM purchases
ORDER BY customer, total DESC, id;
Или короче (если не так ясно) с порядковыми номерами выходных столбцов:
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
Если total
может быть NULL (не повредит в любом случае, но вы захотите соответствовать существующим индексам):
...
ORDER BY customer, total DESC NULLS LAST, id;
DISTINCT ON
- расширение стандарта PostgreSQL (где определяется только DISTINCT
во всем списке SELECT
).
Укажите любое количество выражений в предложении DISTINCT ON
, объединенное значение строки определяет дубликаты. Руководство:
Очевидно, что две строки считаются различными, если они отличаются хотя бы одним значением столбца. Нулевые значения считаются равными в этом сравнении.
Жирный акцент мой.
DISTINCT ON
можно комбинировать с ORDER BY
. Ведущие выражения должны соответствовать ведущим выражениям DISTINCT ON
в том же порядке. Вы можете добавить дополнительные выражения в ORDER BY
чтобы выбрать определенную строку из каждой группы пиров. Я добавил id
как последний элемент для разрыва связей:
"Выберите строку с наименьшим id
из каждой группы, разделяющей наибольшее total
".
Если total
может быть NULL, вы, скорее всего, захотите строку с наибольшим ненулевым значением. Добавьте NULLS LAST
как показано. Подробности:
Список SELECT
никак не ограничен выражениями в DISTINCT ON
или ORDER BY
. (Не требуется в простом случае выше):
Вам не нужно включать какие-либо выражения в DISTINCT ON
или ORDER BY
.
Вы можете включить любое другое выражение в список SELECT
. Это способствует замене гораздо более сложных запросов подзапросами и агрегатными/оконными функциями.
Я тестировал с Postgres версии 8.3 - 11. Но эта функция была там, по крайней мере, начиная с версии 7.1, так что в основном всегда.
Идеальным индексом для вышеуказанного запроса будет многостолбцовый индекс, охватывающий все три столбца в соответствующей последовательности и с соответствующим порядком сортировки:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Может быть слишком специализированным. Но используйте его, если производительность чтения для конкретного запроса имеет решающее значение. Если в запросе указано DESC NULLS LAST
, используйте его в индексе, чтобы порядок сортировки соответствовал и индекс был применим.
Взвесьте стоимость и выгоду перед созданием индивидуальных индексов для каждого запроса. Потенциал вышеуказанного индекса во многом зависит от распределения данных.
Индекс используется, потому что он предоставляет предварительно отсортированные данные. В Postgres 9.2 или более поздних версиях запрос также может быть полезен при сканировании только индекса, если индекс меньше базовой таблицы. Индекс должен быть отсканирован полностью.
Для нескольких строк на клиента (высокая мощность в столбце customer
) это очень эффективно. Тем более, если вам все равно нужен отсортированный вывод. Преимущество уменьшается с ростом числа строк на одного клиента.
В идеале у вас должно быть достаточно work_mem
для обработки соответствующего шага сортировки в оперативной памяти, а не для сброса на диск. Но обычно слишком высокое значение work_mem
может иметь негативные последствия. Рассмотрим SET LOCAL
для исключительно больших запросов. Узнайте, сколько вам нужно с EXPLAIN ANALYZE
. Упоминание "Диск:" на этапе сортировки указывает на необходимость большего:
Для многих строк на одного клиента (низкая мощность в столбце customer
) сканирование свободных индексов (так называемое "сканирование с пропуском") будет (намного) более эффективным, но оно не реализовано до Postgres 11. (Реализация сканирования только по индексу запланировано на Postgres 12. Смотрите здесь и здесь.)
Пока есть более быстрые методы запроса, чтобы заменить это. В частности, если у вас есть отдельная таблица с уникальными покупателями, это типичный вариант использования. Но также, если вы этого не сделаете:
У меня был простой тест, который уже устарел. Я заменил его подробным тестом в этом отдельном ответе.
DISTINCT ON
становится чрезвычайно медленным. Реализация всегда сортирует всю таблицу и просматривает ее на наличие дубликатов, игнорируя все индексы (даже если вы создали необходимый многостолбцовый индекс). См. Объяснение xtended.com/2009/05/03/postgresql-optimizing-distinct для возможного решения.
Тестирование наиболее интересных кандидатов с помощью Postgres 9.4 и 9.5 с полпути реалистичной таблицей 200 тыс. строк в purchases
и 10k customer_id
(около 20 строк на одного клиента).
Для Postgres 9.5 я провел 2-е тестирование с эффективными 86446 клиентами. См. Ниже (примерно 2,3 строки на одного клиента).
Основная таблица
CREATE TABLE purchases (
id serial
, customer_id int -- REFERENCES customer
, total int -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);
Я использую serial
(ограничение PK, добавленное ниже) и целое число customer_id
, поскольку это более типичная настройка. Также добавлен some_column
, чтобы составить обычно больше столбцов.
Dummy data, PK, index - типичная таблица также имеет некоторые мертвые кортежи:
INSERT INTO purchases (customer_id, total, some_column) -- insert 200k rows
SELECT (random() * 10000)::int AS customer_id -- 10k customers
, (random() * random() * 100000)::int AS total
, 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM generate_series(1,200000) g;
ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);
DELETE FROM purchases WHERE random() > 0.9; -- some dead rows
INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int AS customer_id -- 10k customers
, (random() * random() * 100000)::int AS total
, 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM generate_series(1,20000) g; -- add 20k to make it ~ 200k
CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);
VACUUM ANALYZE purchases;
customer
table - для превосходного запроса
CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM purchases
GROUP BY 1
ORDER BY 1;
ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);
VACUUM ANALYZE customer;
В моем втором тесте для 9.5 я использовал ту же настройку, но с random() * 100000
для генерации customer_id
, чтобы получить только несколько строк на customer_id
.
purchases
Сгенерировано с помощью этого запроса.
what | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 20496384 | 20 MB | 102
visibility_map | 0 | 0 bytes | 0
free_space_map | 24576 | 24 kB | 0
table_size_incl_toast | 20529152 | 20 MB | 102
indexes_size | 10977280 | 10 MB | 54
total_size_incl_toast_and_indexes | 31506432 | 30 MB | 157
live_rows_in_text_representation | 13729802 | 13 MB | 68
------------------------------ | | |
row_count | 200045 | |
live_tuples | 200045 | |
dead_tuples | 19955 | |
row_number()
в CTE, (см. другой ответ)WITH cte AS (
SELECT id, customer_id, total
, row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM purchases
)
SELECT id, customer_id, total
FROM cte
WHERE rn = 1;
row_number()
в подзапросе (моя оптимизация)SELECT id, customer_id, total
FROM (
SELECT id, customer_id, total
, row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM purchases
) sub
WHERE rn = 1;
DISTINCT ON
(см. другой ответ)SELECT DISTINCT ON (customer_id)
id, customer_id, total
FROM purchases
ORDER BY customer_id, total DESC, id;
LATERAL
(см. здесь)WITH RECURSIVE cte AS (
( -- parentheses required
SELECT id, customer_id, total
FROM purchases
ORDER BY customer_id, total DESC
LIMIT 1
)
UNION ALL
SELECT u.*
FROM cte c
, LATERAL (
SELECT id, customer_id, total
FROM purchases
WHERE customer_id > c.customer_id -- lateral reference
ORDER BY customer_id, total DESC
LIMIT 1
) u
)
SELECT id, customer_id, total
FROM cte
ORDER BY customer_id;
customer
таблица с LATERAL
(см. здесь)SELECT l.*
FROM customer c
, LATERAL (
SELECT id, customer_id, total
FROM purchases
WHERE customer_id = c.customer_id -- lateral reference
ORDER BY total DESC
LIMIT 1
) l;
array_agg()
с ORDER BY
(см. другой ответ)SELECT (array_agg(id ORDER BY total DESC))[1] AS id
, customer_id
, max(total) AS total
FROM purchases
GROUP BY customer_id;
Время выполнения для вышеуказанных запросов с EXPLAIN ANALYZE
(и все опции выключены), наилучшим из 5 запусков.
В запросах использовалось Индекс только сканировать на purchases2_3c_idx
(среди прочих шагов). Некоторые из них только для меньшего размера индекса, другие более эффективно.
customer_id
1. 273.274 ms
2. 194.572 ms
3. 111.067 ms
4. 92.922 ms
5. 37.679 ms -- winner
6. 189.495 ms
1. 288.006 ms
2. 223.032 ms
3. 107.074 ms
4. 78.032 ms
5. 33.944 ms -- winner
6. 211.540 ms
customer_id
1. 381.573 ms
2. 311.976 ms
3. 124.074 ms -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms
Я провел три теста с PostgreSQL 9.1 в таблице реальной жизни из 65579 строк и одноколоночных индексов btree на каждом из трех задействованных столбцов и занял самое лучшее время выполнения 5 прогонов.
Сравнивая @OMGPonies 'первый запрос (A
) в выше DISTINCT ON
solution ( B
):
Выберите всю таблицу, в результате получим 5958 строк.
A: 567.218 ms
B: 386.673 ms
Использовать условие WHERE customer BETWEEN x AND y
, что приводит к 1000 строкам.
A: 249.136 ms
B: 55.111 ms
Выберите одного клиента с WHERE customer = x
.
A: 0.143 ms
B: 0.072 ms
Тот же тест повторяется с индексом, описанным в другом ответе
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
1A: 277.953 ms
1B: 193.547 ms
2A: 249.796 ms -- special index not used
2B: 28.679 ms
3A: 0.120 ms
3B: 0.048 ms
Это общая проблема greatest-n-per-group, которая уже имеет хорошо протестированные и высоко оптимизированные решения. Лично я предпочитаю левое решение Билла Карвина (оригинальный пост с множеством других решений).
Обратите внимание, что кучу решений этой распространенной проблемы можно найти в одном из самых официальных источников, руководстве по MySQL ! См. Примеры распространенных запросов :: Строки, удерживающие групповой максимум определенного столбца.
DISTINCT ON
намного короче, проще и, как правило, работает лучше в Postgres, чем альтернативы с самостоятельным LEFT JOIN
или полу-анти-объединением с NOT EXISTS
. Это также "хорошо проверено".
В Postgres вы можете использовать array_agg
следующим образом:
SELECT customer,
(array_agg(id ORDER BY total DESC))[1],
max(total)
FROM purchases
GROUP BY customer
Это даст вам id
самой большой покупки каждого клиента.
Некоторые примечания:
array_agg
является агрегированной функцией, поэтому она работает с GROUP BY
.array_agg
позволяет указать порядок, охваченный только собой, поэтому он не ограничивает структуру всего запроса. Существует также синтаксис того, как вы сортируете NULL, если вам нужно сделать что-то отличное от стандартного.array_agg
аналогичным образом для своего третьего столбца вывода, но max(total)
проще.DISTINCT ON
, использование array_agg
позволяет сохранить ваш GROUP BY
, если вы хотите по другим причинам.Решение не очень эффективно, как указал Эрвин, из-за наличия SubQs
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;
Я использую этот путь (только postgresql): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29
-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT $1;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
sfunc = public.first_agg,
basetype = anyelement,
stype = anyelement
);
-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT $2;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
sfunc = public.last_agg,
basetype = anyelement,
stype = anyelement
);
Затем ваш пример должен работать почти так:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY FIRST(total) DESC;
CAVEAT: игнорирует строки NULL
Теперь я использую этот способ: http://pgxn.org/dist/first_last_agg/
Для установки на ubuntu 14.04:
apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'
Это расширение postgres, которое дает вам первую и последнюю функции; очевидно, быстрее, чем выше.
Если вы используете агрегированные функции (например, эти), вы можете заказывать результаты без необходимости иметь уже заказанные данные:
http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES
Таким образом, эквивалентный пример с упорядочением будет выглядеть примерно так:
SELECT first(id order by id), customer, first(total order by id)
FROM purchases
GROUP BY customer
ORDER BY first(total);
Конечно, вы можете заказать и отфильтровать, как вы сочтете нужным в совокупности; это очень мощный синтаксис.
Очень быстрое решение
SELECT a.*
FROM
purchases a
JOIN (
SELECT customer, min( id ) as id
FROM purchases
GROUP BY customer
) b USING ( id );
и действительно очень быстро, если таблица индексируется по id:
create index purchases_id on purchases (id);
Запрос:
SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
WHERE p.total IS NULL
КАК РАБОТАЕТ! (я там был)
Мы хотим убедиться, что у нас есть только самая высокая сумма для каждой покупки.
Некоторые теоретические материалы (пропустите эту часть, если вы хотите понять только запрос)
Пусть Total будет функцией T (customer, id), где она возвращает значение, заданное именем и идентификатором Чтобы доказать, что данное общее значение (T (customer, id)) является наивысшим, мы должны доказать, что Мы хотим доказать, что
ИЛИ
Первый подход потребует от нас получить все записи для этого имени, которые мне не нравятся.
Второй нужен умный способ сказать, что не может быть записи выше этой.
Назад к SQL
Если мы вышли из таблицы, имя и общая сумма будут меньше, чем объединенная таблица:
LEFT JOIN purchases as p
ON
p.customer = purchases.customer
AND
purchases.total < p.total
мы следим за тем, чтобы все записи имели другую запись с более высокой суммой для того, чтобы один и тот же пользователь был присоединен:
purchases.id, purchases.customer, purchases.total, p.id, p.customer, p.total
1 , Tom , 200 , 2 , Tom , 300
2 , Tom , 300
3 , Bob , 400 , 4 , Bob , 500
4 , Bob , 500
5 , Alice , 600 , 6 , Alice , 700
6 , Alice , 700
Это поможет нам фильтровать самую высокую общую сумму для каждой покупки без необходимости группировки:
WHERE p.total IS NULL
purchases.id, purchases.name, purchases.total, p.id, p.name, p.total
2 , Tom , 300
4 , Bob , 500
6 , Alice , 700
И этот ответ нам нужен.
Принятое решение OMG Ponies "Поддерживается любой базой данных" имеет хорошую скорость от моего теста.
Здесь я предоставляю одно и то же, но более полное и чистое решение любой базы данных. Рассматриваются связи (предположим, что для каждого клиента есть только одна строка, даже несколько записей для максимальной суммы для каждого клиента), а также другие поля покупки (например, purchase_payment_id) будут выбраны для реальных совпадающих строк в таблице закупок.
Поддерживается любой базой данных:
select * from purchase
join (
select min(id) as id from purchase
join (
select customer, max(total) as total from purchase
group by customer
) t1 using (customer, total)
group by customer
) t2 using (id)
order by customer
Этот запрос является достаточно быстрым, особенно когда в таблице покупки есть составной индекс, например (клиент, всего).
Примечание:
t1, t2 - псевдоним подзапроса, который можно удалить в зависимости от базы данных.
Caveat: предложение using (...)
в настоящее время не поддерживается в MS-SQL и Oracle db этого изменения в январе 2017 года. Вы должны развернуть его самостоятельно, например. on t2.id = purchase.id
и т.д. Синтаксис USING работает в SQLite, MySQL и PostgreSQL.
Для SQl Server наиболее эффективным способом является:
with
ids as ( --condition for split table into groups
select i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i)
)
,src as (
select * from yourTable where <condition> --use this as filter for other conditions
)
,joined as (
select tops.* from ids
cross apply --it like for each rows
(
select top(1) *
from src
where CommodityId = ids.i
) as tops
)
select * from joined
и не забудьте создать кластерный индекс для используемых столбцов
В SQL Server вы можете сделать это:
SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY customer
ORDER BY total DESC) AS StRank, *
FROM Purchases) n
WHERE StRank = 1
Пояснение: здесь Группировка по производится на основе клиента, а затем заказывается по сумме, затем каждой такой группе присваивается серийный номер как StRank, и мы выбираем первого 1 клиента, чей StRank равен 1
Если вы хотите выбрать любую (по вашему определенному условию) строку из набора агрегированных строк.
Если вы хотите использовать еще одну (sum/avg
) функцию агрегации в дополнение к max/min
. Таким образом, вы не можете использовать ключ с DISTINCT ON
Вы можете использовать следующий подзапрос:
SELECT
(
SELECT **id** FROM t2
WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )
) id,
name,
MAX(amount) ma,
SUM( ratio )
FROM t2 tf
GROUP BY name
Вы можете заменить amount = MAX( tf.amount )
на любое условие, которое вы хотите, с одним ограничением: этот подзапрос не должен возвращать более одной строки
Но если вы хотите делать такие вещи, вы, вероятно, ищете функции окна