MySQL упорядочить перед группировать по

Question

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

Я продолжу от текущего самого популярного вопроса и буду использовать их пример, если это хорошо.

Задача в этом случае - получить последний пост для каждого автора в базе данных.

Пример запроса дает непригодные результаты, так как он не всегда является последним возвращаемым сообщением.

SELECT wp_posts.* FROM wp_posts
    WHERE wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
    GROUP BY wp_posts.post_author           
    ORDER BY wp_posts.post_date DESC

Текущий принятый ответ

SELECT
    wp_posts.*
FROM wp_posts
WHERE
    wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author
HAVING wp_posts.post_date = MAX(wp_posts.post_date) <- ONLY THE LAST POST FOR EACH AUTHOR
ORDER BY wp_posts.post_date DESC

К сожалению, этот ответ прост и неверен и во многих случаях дает менее стабильные результаты, чем оригинальный запрос.

Мое лучшее решение - использовать подзапрос вида

SELECT wp_posts.* FROM 
(
    SELECT * 
    FROM wp_posts
    ORDER BY wp_posts.post_date DESC
) AS wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author 

Тогда у меня простой вопрос:нужно ли в любом случае упорядочивать строки перед группировкой, не прибегая к подзапросу?

Изменить: Этот вопрос был продолжением другого вопроса, и специфика моей ситуации немного отличается. Вы можете (и должны) предположить, что существует также wp_posts.id, который является уникальным идентификатором для этого конкретного сообщения.

Answer 1

Используя ORDER BY в подзапросе не лучшее решение этой проблемы.

Лучшее решение, чтобы получить max(post_date) автор должен использовать подзапрос, чтобы вернуть максимальную дату, а затем присоединить ее к вашей таблице на обоих post_author и максимальная дата.

Решение должно быть:

SELECT p1.* 
FROM wp_posts p1
INNER JOIN
(
    SELECT max(post_date) MaxPostDate, post_author
    FROM wp_posts
    WHERE post_status='publish'
       AND post_type='post'
    GROUP BY post_author
) p2
  ON p1.post_author = p2.post_author
  AND p1.post_date = p2.MaxPostDate
WHERE p1.post_status='publish'
  AND p1.post_type='post'
order by p1.post_date desc

Если у вас есть следующие образцы данных:

CREATE TABLE wp_posts
    (`id` int, `title` varchar(6), `post_date` datetime, `post_author` varchar(3))
;

INSERT INTO wp_posts
    (`id`, `title`, `post_date`, `post_author`)
VALUES
    (1, 'Title1', '2013-01-01 00:00:00', 'Jim'),
    (2, 'Title2', '2013-02-01 00:00:00', 'Jim')
;

Подзапрос вернет максимальную дату и автора:

MaxPostDate | Author
2/1/2013    | Jim

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

Смотрите SQL Fiddle с демонстрацией.

Чтобы расширить мои комментарии об использовании подзапроса для точного возврата этих данных.

MySQL не заставляет вас GROUP BY каждый столбец, который вы включаете в SELECT список. В результате, если вы только GROUP BY один столбец, но возвращают в общей сложности 10 столбцов, нет никакой гарантии, что значения других столбцов, которые принадлежат post_author это возвращается. Если столбец не находится в GROUP BY MySQL выбирает, какое значение должно быть возвращено.

Использование подзапроса с агрегатной функцией гарантирует, что каждый раз будет возвращаться правильный автор и сообщение.

Как примечание, в то время как MySQL позволяет вам использовать ORDER BY в подзапросе и позволяет применить GROUP BY чтобы не каждый столбец в SELECT перечислите, что это поведение не разрешено в других базах данных, включая SQL Server

Answer 2

Ваше решение использует расширение предложения GROUP BY, которое позволяет группировать по некоторым полям (в данном случае просто post_author):

GROUP BY wp_posts.post_author

и выберите неагрегированные столбцы:

SELECT wp_posts.*

которые не перечислены в предложении group by или не используются в статистической функции (MIN, MAX, COUNT и т. д.).

Правильное использование расширения для предложения GROUP BY

Это полезно, когда все значения неагрегированных столбцов равны для каждой строки.

Например, предположим, у вас есть таблица GardensFlowers (name сада, flower что растет в саду)

INSERT INTO GardensFlowers VALUES
('Central Park',       'Magnolia'),
('Hyde Park',          'Tulip'),
('Gardens By The Bay', 'Peony'),
('Gardens By The Bay', 'Cherry Blossom');

и вы хотите извлечь все цветы, которые растут в саду, где растут несколько цветов. Затем вы должны использовать подзапрос, например, вы можете использовать это:

SELECT GardensFlowers.*
FROM   GardensFlowers
WHERE  name IN (SELECT   name
                FROM     GardensFlowers
                GROUP BY name
                HAVING   COUNT(DISTINCT flower)>1);

Если вам нужно извлечь все цветы, которые являются единственными цветами в луковице, вы можете просто изменить условие HAVING на HAVING COUNT(DISTINCT flower)=1, но MySql также позволяет вам использовать это:

SELECT   GardensFlowers.*
FROM     GardensFlowers
GROUP BY name
HAVING   COUNT(DISTINCT flower)=1;

нет подзапроса, не стандартный SQL, но проще.

Неправильное использование расширения для предложения GROUP BY

Но что произойдет, если вы выберете неагрегированные столбцы, которые не равны для каждой строки? Какое значение выбирает MySql для этого столбца?

Похоже, что MySql всегда выбирает первое значение, с которым сталкивается.

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

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

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

Я вижу, что этот прием для получения первого значения неагрегированного столбца часто используется, и он обычно / почти всегда работает, иногда я тоже его использую (на свой страх и риск). Но поскольку это не задокументировано, вы не можете полагаться на это поведение.

Эта ссылка (спасибо ypercube!), Трюк GROUP BY был оптимизирован, показывает ситуацию, в которой один и тот же запрос возвращает разные результаты между MySql и MariaDB, возможно, из-за другого механизма оптимизации.

Так что, если этот трюк сработает, это просто вопрос удачи.

Принятый ответ на другой вопрос мне кажется неправильным:

HAVING wp_posts.post_date = MAX(wp_posts.post_date)

wp_posts.post_date является неагрегированным столбцом, и его значение будет официально не определено, но, скорее всего, оно будет первым post_date встречается. Но поскольку трюк GROUP BY применяется к неупорядоченной таблице, он не уверен, какой из них является первым post_date встречается.

Вероятно, он вернет сообщения, которые являются единственными публикациями одного автора, но даже это не всегда точно.

Возможное решение

Я думаю, что это может быть возможным решением:

SELECT wp_posts.*
FROM   wp_posts
WHERE  id IN (
  SELECT max(id)
  FROM wp_posts
  WHERE (post_author, post_date) = (
    SELECT   post_author, max(post_date)
    FROM     wp_posts
    WHERE    wp_posts.post_status='publish'
             AND wp_posts.post_type='post'
    GROUP BY post_author
  ) AND wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
  GROUP BY post_author
)

По внутреннему запросу я возвращаю максимальную дату публикации для каждого автора. Затем я принимаю во внимание тот факт, что один и тот же автор теоретически может иметь две записи одновременно, поэтому я получаю только максимальный идентификатор. А потом я возвращаю все строки, которые имеют эти максимальные идентификаторы. Это можно сделать быстрее, используя соединения вместо предложения IN.

(Если вы уверены, что ID только увеличивается, и если ID1 > ID2 также означает, что post_date1 > post_date2, тогда запрос можно было бы сделать намного проще, но я не уверен, что это так).

Answer 3

То, что вы собираетесь прочитать, довольно хакерское, так что не пытайтесь делать это дома!

В целом, в SQL ответ на ваш вопрос НЕТ, но из-за непринужденного режима GROUP BY (упомянуто @bluefeet), ответ - ДА в MySQL.

Предположим, у вас есть индекс BTREE (post_status, post_type, post_author, post_date). Как выглядит индекс под капотом?

(post_status = 'publish', post_type = 'post', post_author = 'user A', post_date = '2012-12-01') (post_status = 'publish', post_type = 'post', post_author = 'user A', post_date ='2012-12-31') (post_status = 'publish', post_type = 'post', post_author = 'user B', post_date ='2012-10-01') (post_status = 'publish', post_type = ' post ', post_author =' пользователь B', post_date='2012-12-01')

То есть данные сортируются по всем этим полям в порядке возрастания.

Когда вы делаете GROUP BY по умолчанию сортирует данные по полю группировки (post_authorв нашем случае; post_status, post_type требуются WHERE пункт), и если есть соответствующий индекс, он берет данные для каждой первой записи в порядке возрастания. То есть запрос получит следующее (первое сообщение для каждого пользователя):

(post_status = 'publish', post_type = 'post', post_author = 'user A', post_date = '2012-12-01') (post_status = 'publish', post_type = 'post', post_author = 'user B', POST_DATE ='2012-10-01')

Но GROUP BY в MySQL позволяет указывать порядок явно. И когда вы запрашиваете post_user в порядке убывания он будет проходить через наш индекс в обратном порядке, по-прежнему принимая первую запись для каждой группы, которая фактически является последней.

То есть

...
WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author DESC

даст нам

(post_status='publish', post_type='post', post_author='user B', post_date='2012-12-01') (post_status='publish', post_type='post', post_author='user A', POST_DATE ='2012-12-31')

Теперь, когда вы упорядочиваете результаты группировки по post_date, вы получаете нужные данные.

SELECT wp_posts.*
FROM wp_posts
WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author DESC
ORDER BY wp_posts.post_date DESC;

NB:

Это не то, что я бы порекомендовал для этого конкретного запроса. В этом случае я бы использовал слегка измененную версию того, что предлагает @bluefeet. Но эта техника может быть очень полезной. Посмотрите на мой ответ здесь: Получение последней записи в каждой группе

Подводные камни: недостатки подхода в том, что

• результат запроса зависит от индекса, что противоречит духу SQL (индексы должны только ускорять запросы);
• index ничего не знает о его влиянии на запрос (вы или кто-то еще в будущем можете счесть индекс слишком ресурсоемким и каким-то образом изменить его, нарушив результаты запроса, а не только его производительность)
• Если вы не понимаете, как работает запрос, скорее всего, вы забудете объяснение через месяц, и запрос запутает вас и ваших коллег.

Преимущество - производительность в тяжелых случаях. В этом случае производительность запроса должна быть такой же, как в запросе @ bluefeet, из-за объема данных, участвующих в сортировке (все данные загружаются во временную таблицу и затем сортируются; кстати, его запрос требует (post_status, post_type, post_author, post_date) индекс также).

Что бы я предложил:

Как я уже сказал, эти запросы заставляют MySQL тратить время на сортировку потенциально огромных объемов данных во временной таблице. В случае, если вам нужно подкачки страниц (то есть задействовано LIMIT), большая часть данных даже отбрасывается. То, что я хотел бы сделать, это минимизировать количество отсортированных данных: это отсортировать и ограничить минимум данных в подзапросе, а затем снова присоединиться ко всей таблице.

SELECT * 
FROM wp_posts
INNER JOIN
(
  SELECT max(post_date) post_date, post_author
  FROM wp_posts
  WHERE post_status='publish' AND post_type='post'
  GROUP BY post_author
  ORDER BY post_date DESC
  -- LIMIT GOES HERE
) p2 USING (post_author, post_date)
WHERE post_status='publish' AND post_type='post';

Тот же запрос с использованием подхода, описанного выше:

SELECT *
FROM (
  SELECT post_id
  FROM wp_posts
  WHERE post_status='publish' AND post_type='post'
  GROUP BY post_author DESC
  ORDER BY post_date DESC
  -- LIMIT GOES HERE
) as ids
JOIN wp_posts USING (post_id);

Все эти запросы с их планами выполнения на SQLFiddle.

Answer 4

Попробуй это. Просто получите список последних постов от каждого автора. Это оно

SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post' AND wp_posts.post_date IN(SELECT MAX(wp_posts.post_date) FROM wp_posts GROUP BY wp_posts.post_author) 

Answer 5

Просто используйте функцию max и функцию group

    select max(taskhistory.id) as id from taskhistory
            group by taskhistory.taskid
            order by taskhistory.datum desc

Answer 6

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

Answer 7

Не уверен, было ли это уже предложено или нет, но теперь вы можете использовать оконные функции SQL :

      SELECT * FROM (
      SELECT wp_posts.*, ROW_NUMBER() OVER (PARTITION BY wp_posts.post_author ORDER BY post_date DESC) rank
      FROM wp_posts
      WHERE wp_posts.post_status = 'publish'
      AND wp_posts.post_type = 'post'
  ) AS T
WHERE rank = 1

Все строки получают «ранжирование», тогда вам просто нужно выбрать каждую 1-ю строку.

Признаюсь, я понятия не имею о производительности, но, насколько мне известно, она должна быть вполне приемлемой.

Answer 8

Напомним, что стандартное решение использует некоррелированный подзапрос и выглядит так:

SELECT x.*
  FROM my_table x
  JOIN (SELECT grouping_criteria,MAX(ranking_criterion) max_n FROM my_table GROUP BY grouping_criteria) y
    ON y.grouping_criteria = x.grouping_criteria
   AND y.max_n = x.ranking_criterion;

Если вы используете древнюю версию MySQL или довольно небольшой набор данных, то вы можете использовать следующий метод:

SELECT x.*
  FROM my_table x
  LEFT
  JOIN my_table y
    ON y.joining_criteria = x.joining_criteria
   AND y.ranking_criteria < x.ranking_criteria
 WHERE y.some_non_null_column IS NULL;  

Answer 9

Можешь попробовать

      SELECT wp_posts.*, MIN(DISTINCT wp_posts.post_date) FROM wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author

в данном случае функция Min группирует и упорядочивает

Answer 10

На всякий случай, если это действительно так. Я много раз делал что-то вроде этого:

      select * from 
  (select max(some_quantity) over (partition by id1, id2) as max_quantity, t.*
  from table_name t) tt 
where tt.max_quantity=tt.some_quantity;

Это группировка, имеющая условие максимум поля some_quantity.

Answer 11

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

      SET @product_id := 0;

SELECT
    products.order_code,
    purchases.`date`,
    purchases.price
FROM products
LEFT JOIN (       
    SELECT
        purchases.`date`,
        purchases.price,
        IF(@product_id = purchases.product_id, 0, 1) AS is_last,
        @product_id := purchases.product_id AS product_id
    FROM purchases
    ORDER BY purchases.product_id ASC, purchases.id DESC
) purchases ON products.id = purchases.product_id
WHERE purchases.is_last = 1
ORDER BY products.order_code ASC;

Я не уверен в производительности, но это было 0,1 секунды на 50000 строк таблицы покупок. Пожалуйста, дайте мне знать, если я могу сделать какие-либо улучшения производительности.

Answer 12

** Подзапросы могут оказать плохое влияние на производительность при использовании с большими наборами данных **

Оригинальный запрос

SELECT wp_posts.*
FROM   wp_posts
WHERE  wp_posts.post_status = 'publish'
       AND wp_posts.post_type = 'post'
GROUP  BY wp_posts.post_author
ORDER  BY wp_posts.post_date DESC; 

Модифицированный запрос

SELECT p.post_status,
       p.post_type,
       Max(p.post_date),
       p.post_author
FROM   wp_posts P
WHERE  p.post_status = "publish"
       AND p.post_type = "post"
GROUP  BY p.post_author
ORDER  BY p.post_date; 

потому что я использую max в select clause ==> max(p.post_date) можно избежать запросов на дополнительный выбор и упорядочение по столбцу max после группировки по.

Answer 13

Во-первых, не используйте * в select, влияет на их производительность и препятствует использованию группы по порядку. Попробуйте этот запрос:

SELECT wp_posts.post_author, wp_posts.post_date as pdate FROM wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author           
ORDER BY pdate DESC

Когда вы не указываете таблицу в ORDER BY, просто псевдоним, они упорядочат результат выбора.