Группировка с условием

Жалко, что методически SQL тут и не пахнет. Сплошные лайфхаки — для того чтобы обойти несовершенство MySQL. В общем — если бы я у своего подчиненного увидел бы такие select — то влепил бы выговор, отправил бы на учебу или уволил. И нечем тут гордиться.

Например:
>SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);

Самый «классический». Но возникает вопрос — а индекс по article есть !? Скорее всего нет — отсюда и провал в скорости. Опять же индекс по price не поможет — т.к. сравнивать значения в float процессору тяжелее. (выход кстати есть — храните суммы в _целых числах_ — типа копейках. А делите на 100 при печати и выводе на экран). Тогда индекс (article, price) — даст вам миллиардную производительность.

>SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

Полное извращение над SQL92 — Рекомендуется в условии ON писать только соединения, а отбор (типа s1.price<s2,price) только после в WHERE. Большинство оптимизаторов это поймут правильно (но не MySQL видимо)

>SELECT article, dealer, price
FROM (
SELECT article, dealer, price
FROM shop
ORDER BY price desc)
as t
GROUP BY article
ORDER BY NULL;

Кроме order by price в присоединяемых select — который по понятным причинам в исходящую выбору (хотя жрет процессорное время) не входит и надо досортировывать — прикольным «order by null».
Не забываем что в агрегатных функциях мы нарываемся еще и на эскалацию блокировки

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

С этого и надо начинать. Если у вас часто такие запросы — то структура данных должна под это ложиться. Т.е. сначала задумываемся о «функциях» системы, а затем логически подходим к «конструкции», а не наоборот — как и положено в business driven architecture

6-й пункт отдаленно напоминает трюк max-concat. Он был описан в документации mysql к версии до 4.1 включительно (в том же разделе, на который Вы привели ссылку). Видимо, потом его удалили, чтобы народ не пользовался неоптимальными методами. Ссылка. Можно считать это 7-м подходом.

Про такие особенности СУБД полезно знать — и еще полезнее это знание не использовать :)

В 6-ом примере конечно, все получится, но вот сомнения по поводу 100 000 записей. GROUP_CONCAT имеет ограничения связанные с group_concat_max_len, который по умолчанию равен 1024 символам. При превышении данного лимита результат обрезается.
Дошел до MAX() в 6-ом примере и понял, что невнимательно посмотрел на запрос.

Но пардон — 6-ое решение действительно правильно, а вот ваше 5-ое — нет. Даже если выполнить эти два запроса, они вернут разные результаты.

Может Oracle когда-нибудь перенесет аналитические функции в младшего брата.
Тут пригодились бы KEEP или OVER.

удалено, ибо уже есть такой вариант

Четвертое решение уже сейчас не будет работать в таком виде на Maria DB (https://mariadb.com/kb/en/derived-table-merge-optimization/), чтобы работало достаточно добавить LIMIT с заведомо большим числом в derived выборку
SELECT article, dealer, price FROM ( SELECT article, dealer, price FROM shop ORDER BY price desc LIMIT 1000000000000 ) as t GROUP BY article ORDER BY NULL;

Это получается какая-то война с СУБД и с языком SQL вместо их использования… В таких случаях я сожалею, что не могу вместо SQL-запроса составить сразу его план.

Для сравнения, на Microsoft SQL Server это делается так:

select article, dealer, price
from (
    select *, rownumber() over (partition by article order by price desc) as r
    from shop) as s1
where r = 1

Особенности сортировки конечно знать надо, но вот использоваться в боевом коде — ни в коем случае! Именно поэтому все борются с выборками по index_desc, а Вы наоборот их культивируете. И вообще, зачем написана эта статья? ;)

Провел тесты на ORACLE:

CREATE TABLE tmp_shop (
  id INTEGER,
  article INTEGER NOT NULL,
  dealer VARCHAR(45) NOT NULL,
  price NUMBER(8,2) NOT NULL,
   constraint PK_tmp_shop primary key (ID) using index tablespace IDX
) TABLESPACE  DAT;


INSERT INTO tmp_shop (id, article, dealer, price) 
SELECT rownum, CEIL(dbms_random.value(8,2) * 9999), CEIL(dbms_random.value(8,2) * 999), dbms_random.value(8,2) * 9999
FROM dual
connect by level < 1000000;

create index i_tmp_shop_FND on tmp_shop (article asc) tablespace IDX;
--=0.848
SELECT article, dealer, price 
FROM   tmp_shop s1
WHERE  price=(SELECT MAX(s2.price)
    FROM tmp_shop s2
    WHERE s1.article = s2.article);
    
DROP index i_tmp_shop_FND;
create index i_tmp_shop_FND on tmp_shop (article, price) tablespace IDX;
--0.502
SELECT s1.article, dealer, s1.price
FROM tmp_shop s1
JOIN (
    SELECT article, MAX(price) AS price
    FROM tmp_shop
    GROUP BY article
) s2 ON s1.article = s2.article AND s1.price = s2.price;

--4.38
select article, dealer, price 
from (
    select s.*, row_number() over (partition by article order by price desc) as r
    from tmp_shop s)  s1
where r = 1;

--2.1
select article, MAX(dealer) KEEP(DENSE_RANK FIRST ORDER BY price DESC), MAX(price)
FROM tmp_shop
GROUP BY article

На PostgreSQL вот так выглядят результаты, только записей взял миллион
pastebin.com/u7C34tVW

По поводу замеров времени выполнения и сравнений с вариантами запросов с аналитическими функциями на ORACLE и выводов что двойной фулскан таблицы лучше одиночного с аналитикой.
Всё это не имеет смысла без снятия трейсов и полного фетча. По трейсам скорее всего будет видно, что двойные чтения упираются в обращения к диску, аналитика — в память или процессор. Что лучше — зависит от конкретных условий, поэтому выводы о том какой вариант лучше мне кажутся некорректными.
Я предочитаю одиночный фулскан таблицы с аналитикой.