Порядок MySQL перед группой

Ответ 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 with Demo.

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

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

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

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

Ответ 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 всегда выбирает значение FIRST, с которым он сталкивается.

Чтобы убедиться, что первое значение, которое он встречает, - это именно то значение, которое вы хотите, вам нужно применить 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, тогда запрос может быть сделан намного проще, но я не уверен, что это так).

Ответ 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 = 'пользователь A', post_date = '2012-12-31') (post_status = 'publish', post_type = 'post', post_author = 'пользователь 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 (индексы должны только ускорять запросы);
• индекс ничего не знает о его влиянии на запрос (вы или кто-то еще в будущем может найти индекс слишком ресурсоемким и каким-то образом изменить его, нарушая результаты запроса, а не только его производительность).
• Если вы не понимаете, как работает запрос, скорее всего, вы забудете объяснение в течение месяца, и запрос смутит вас и ваших коллег.

Преимуществом является производительность в жестких случаях. В этом случае производительность запроса должна быть такой же, как в запросе @bluefeet, из-за количества данных, участвующих в сортировке (все данные загружаются во временную таблицу и затем сортируются; btw, для его запроса требуется индекс (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.

Ответ 4

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

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) 

Ответ 5

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

Ответ 6

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

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;  

Ответ 7

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

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

Ответ 8

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

Исходный запрос

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; 

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

Ответ 9

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

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, просто псевдоним, они будут заказывать результат выбора.

Ответ 10

Хотя другие опубликовали работу по актуальному вопросу:

* Есть ли способ упорядочить строки перед группировкой, не прибегая к подзапросу?

На самом деле НЕ ЕСТЬ WAY ЗАКАЗАТЬ запрос MySQL - перед GROUPING - без подзапроса.

Если вы похожи на меня и пришли к этому вопросу, чтобы определить, действительно ли есть способ сделать это БЕЗ СУБКЕРИРОВАНИЯ - фактический ответ НЕТ Там не, чтобы упорядочить запрос перед его группировкой.

Чтобы достичь порядка, прежде чем он повлияет на группировку - , ВЫ ДОЛЖНЫ ИСПОЛЬЗОВАТЬ СУБКЕРУ.

(Если вы собираетесь проголосовать за мой ответ... почему бы не быть не-детским и опубликовать ответ, который на самом деле дает результат без подзапроса. Да... это то, что я подумал: D Вы не можете сделать это без подзапроса, чтобы он сходил с ума, и вы проголосовали за истинную правду. Ах да... В эпоху Трампа.... NO ONE - хочет посмотреть правду;))