Блог

В SQLite, если я сделаю:

 CREATE TABLE fraction (
  id Int,
  tag Int,
  num Int,
  den Int,
  PRIMARY KEY (id)
);

INSERT INTO fraction VALUES (1,1,3,4);
INSERT INTO fraction VALUES (2,1,5,6);
INSERT INTO fraction VALUES (3,2,3,8);
INSERT INTO fraction VALUES (4,2,5,7);
INSERT INTO fraction VALUES (5,1,10,13);
INSERT INTO fraction VALUES (6,2,5,7);

SELECT fraction.tag, max(1.0 * fraction.num / fraction.den)
FROM fraction
GROUP BY fraction.tag;
  

Я получу результат:

 1|0.833333333333333
2|0.714285714285714
  

Затем, если я выдам:

 SELECT fraction.tag, max(1.0 * fraction.num / fraction.den),
  fraction.num, fraction.den
FROM fraction
GROUP BY fraction.tag;
  

Я получу результат:

 1|0.833333333333333|5|6
2|0.714285714285714|5|7
  

Последнее — это то, чего я ожидал, но это больше похоже на счастливую случайность, чем на что-либо предсказуемое или надежное. Например, sum если бы вместо агрегатной функции min был какой-то столбец типа «rider», не имело бы смысла.

В текущем проекте, который я делаю, я использую таблицу, присоединенную к самой себе, для имитации последней:

 SELECT DISTINCT fraction_a.tag, fraction_a.high,
  fraction_b.num, fraction_b.den
FROM
  (SELECT fraction.tag, max(1.0 * fraction.num / fraction.den) AS high
    FROM fraction
    GROUP BY fraction.tag)
  AS fraction_a JOIN
  (SELECT fraction.tag, fraction.num, fraction.den
    FROM fraction)
  AS fraction_b
  ON fraction_a.tag = fraction_b.tag
    AND fraction_a.high = 1.0 * fraction_b.num / fraction_b.den;
  

выдача

 1|0.833333333333333|5|6
2|0.714285714285714|5|7
  

Но я нахожу этот синтаксис уродливым, непрактичным и недостижимым.

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

Когда вы используете GROUP BY, база данных должна создать одну выходную строку из (возможно) нескольких входных строк.

Столбцы, упомянутые в предложении GROUP BY, имеют одинаковое значение для всех строк в группе, поэтому это выходное значение, которое будет использоваться.

Столбцы с некоторой агрегатной функцией используют это для вычисления выходного значения.

Однако другие столбцы представляют собой проблему, поскольку в группе могут быть разные значения. Стандарт SQL запрещает это. MySQL забывает проверить наличие этой ошибки и выдает некоторое случайное значение строки для вывода. SQLite допускает это для совместимости с MySQL.

Начиная с версии 3.7.11, когда вы используете MIN или MAX, SQLite гарантирует, что другие столбцы будут получены из записи, имеющей минимальное / максимальное значение.

Включение неагрегированных столбцов в ваше SELECT предложение, которые не отображаются в вашем GROUP BY предложении, является непереносимым и, вероятно, приведет к ошибкам / неожиданным результатам. Используемый вами синтаксис не является более чистым — он явно неправильный и работает на SQLite. Это не будет работать в Oracle (вызывая синтаксическую ошибку), это не будет работать должным образом в MySQL (где он будет возвращать случайные значения из группы), и, вероятно, не будет работать в других СУБД.

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

Пожалуйста, обратите внимание, что ваш второй подход («уродливый» запрос) вернет несколько строк для каждого тега, если у вас окажется несколько максимумов. Это может быть или не быть тем, что вы хотите.

Так что стисните зубы и используйте что-то вроде вашего уродливого подхода — он переносим и будет работать так, как ожидалось.