MySQL не уверен насчет функции агрегирования

Question

У меня была эта функция много-много таблиц.

table Actor
-----------
Actorid     Fullname

table entertainment
-------------------------
Entertainmentid   Name   Date


Actor_entertainment
-------------------------
Entertainmentid   Actorid

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

Я строю этот запрос:

SELECT 
a.fullname
, c.Name
, MIN(c.Date) 
FROM Actor a 
INNER JOIN Actor_entertainment b on b.Actorid = a.Actorid 
INNER JOIN entertainment c ON c.entertainmentID = b.entertainmentID 
GROUP BY 
a.Fullname

Запрос работает хорошо, но я не уверен, что функция MIN выбирает правильную дату. Можете ли вы прочитать этот запрос и сказать, что я где-то не прав? Если быть точным, возможна ли ошибка?

Благодарю.

Answer 1

Да, минимальное значение c.Date будет возвращен. И это будет "самая ранняя дата", пока Date столбец имеет тип данных DATE, DATETIME, TIMESTAMPили значения, хранящиеся в этом столбце, представлены в каноническом формате... более низкое значение соответствует более ранней дате.

Однако значение, возвращаемое для c.Name выражение неопределенное. То есть нет гарантии, что значение, возвращаемое для этого выражения, будет из той же строки, из которой было возвращено минимальное значение даты.

(Другие базы данных возвращали бы ошибку с этим оператором SQL, отклоняясь от "неагрегированного" выражения, появляющегося в списке SELECT без появления в GROUP BY пункт. MySQL предоставляет нестандартное расширение для GROUP BY, которое позволяет выполнять этот запрос. Поведение MySQL можно изменить, отключив это расширение, установив SQL_MODE включать ONLY_FULL_GROUP_BY.)

Есть несколько подходов к получению Name связано с этой самой ранней датой.

Для небольшого числа возвращаемых строк и подходящих доступных индексов можно использовать коррелированные подзапросы:

  SELECT a.fullname
       , ( SELECT c.Name
             FROM entertainment c
             JOIN Actor_entertainment b
               ON b.entertainmentID = c.entertainmentID 
            WHERE b.Actorid = a.Actorid
            ORDER BY c.Date ASC, c.Name ASC
            LIMIT 1
          ) AS `Name`
       , ( SELECT c.Date
             FROM entertainment c
             JOIN Actor_entertainment b
               ON b.entertainmentID = c.entertainmentID 
            WHERE b.Actorid = a.Actorid
            ORDER BY c.Date ASC, c.Name ASC
            LIMIT 1
          ) AS `Date`
    FROM Actor a
   ORDER BY a.fullname

Другой подход состоит в том, чтобы получить эту самую раннюю дату, а затем выполнить объединение, чтобы найти строки, соответствующие этой самой ранней дате. Если в Actor более одной строки с одинаковой "минимальной" датой для данной строки, будут возвращены все эти строки:

  SELECT da.fullname
       , dc.Name
       , dc.Date
    FROM ( SELECT a.actorid
                , MIN(c.Date) AS min_date
             FROM Actor a
             JOIN Actor_entertainment b
               ON b.Actorid = a.Actorid
             JOIN entertainment c
               ON c.entertainmentID = b.entertainmentID
            GROUP BY a.actorid
         ) d
    JOIN Actor da
      ON da.actorid = d.actorid
    JOIN Actor_entertainment db
      ON db.Actorid = d.Actorid
    JOIN entertainment dc
      ON dc.entertainmentID = db.entertainmentID
     AND dc.Date = d.min_date

Answer 2

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

Также смотрите вместо использования псевдонима A, B, C я использую A, AE, E чтобы помочь понять запрос.

SELECT ArtistName,
       EntertainmentName,
       Date 
FROM (
        SELECT 
            A.fullname ArtistName
          , E.Name     EntertainmentName
          , E.Date
          , (@rank := if(@prev_artist = A.fullname, 
                         @rank + 1,  -- increase rank
                         if(@prev_artist := A.fullname, --reset rank
                            0, 
                            0                       
                           )
                        )                  
           ) as ranking 
        FROM Actor A 
        INNER JOIN Actor_entertainment AE
                on A.Actorid = AE.Actorid 
        INNER JOIN entertainment E 
                ON AE.entertainmentID = E.entertainmentID 
        CROSS JOIN (select @rank := 0, @prev_artist := '') params
        ORDER BY A.Actorid, E.Date
     ) T
WHERE ranking = 1