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

У меня есть таблица sqlite, содержащая каждую воспроизводимую дорожку подряд с указанием даты и времени воспроизведения. Теперь я посчитаю количество воспроизведений всех исполнителей, сгруппированных по дням, а затем найду исполнителя с максимальным количеством воспроизведений в день. Я использовал этот запрос

SELECT COUNT(ARTISTID) AS artistcount,
       ARTIST AS artistname,
       strftime('%Y-%m-%d', playtime) AS day_played
FROM playcount
GROUP BY artistname, day_played

чтобы получить этот результат

"93"|"The Skygreen Leopards"|"2010-06-16"
"2" |"Arcade Fire"          |"2010-06-15"
"2" |"Dead Kennedys"        |"2010-06-15"
"2" |"Wolf People"          |"2010-06-15"
"3" |"16 Horsepower"        |"2010-06-15"
"3" |"Alela Diane"          |"2010-06-15"
"46"|"Motorama"             |"2010-06-15"
"1" |"Ariel Pink's Haunted" |"2010-06-14"

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

SELECT MAX(artistcount), artistname , day_played 
FROM
(
SELECT COUNT(ARTISTID) AS artistcount,
       ARTIST AS artistname,
       strftime('%Y-%m-%d', playtime) AS day_played
FROM playcount
GROUP BY artistname
)
GROUP BY strftime('%Y-%m-%d',day_played)

привести к этому

"93"|"lilium"     |"2010-06-16"
"46"|"Wolf People"|"2010-06-15"
"30"|"of Montreal"|"2010-06-14"

но имя исполнителя неверное. Я думаю, что через группировку по дням можно просто использовать последнего исполнителя или около того. Я тестировал такие вещи, как INNER JOIN или GROUP BY... В процессе проб и ошибок я читал примеры похожих проблем, но всегда терялся в именах столбцов и прочем (я немного сгорел)

Я надеюсь, что кто-то может дать мне подсказку. спасибо м


sql sqlite aggregate-functions
person Marcus    schedule 16.06.2010    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Это именно то, что происходит. Я бы даже не ожидал, что этот запрос будет выполнен - ​​поскольку у вас есть «имя исполнителя» в предложении SELECT, но не в GROUP BY, я ожидаю, что механизм SQL, с которым вы работаете, откажется выполнять запрос.

Чтобы решить эту проблему, просто добавьте «имя исполнителя» в свою группу GROUP BY во внешнем запросе:

SELECT MAX(artistcount), artistname , day_played 
FROM
(
SELECT COUNT(ARTISTID) AS artistcount,
ARTIST AS artistname,strftime('%Y-%m-%d', playtime) AS day_played
FROM playcount
GROUP BY artistname
)
GROUP BY artistname, strftime('%Y-%m-%d',day_played)
person SqlRyan    schedule 16.06.2010
comment
группировка снова с именем исполнителя приводит к первому выводу таблицы, который я опубликовал. Может быть, мне нужен третий выбор и ПРИСОЕДИНЯЙТЕСЬ к обоим результатам ^^. - person Marcus; 16.06.2010
comment
SQLite, как и MySQL, имеет эту функцию в предложении GROUP BY, что вам не нужно следовать стандарту SQL и можно опускать столбцы... - person OMG Ponies; 16.06.2010
comment
Это ужасная особенность, потому что она приводит к подобным вещам. В любом случае, добавление его в GROUP BY должно решить проблему. - person SqlRyan; 17.06.2010

arrow_upward
0
arrow_downward

Я нашел способ после прочтения этого: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Но это выглядит глупо, и, может быть, кто-нибудь подскажет, как это оптимизировать.

Я создал sqlite представление с именем max_play_by_artist, которое выводит все счетчики воспроизведения, сгруппированные по day_played.

CREATE VIEW "max_play_by_artist"
AS
    SELECT COUNT(artistid) AS artistcount,
    artist AS artistname,
    strftime('%Y-%m-%d', playtime) AS day_played,
    artistid as id
    FROM playcount
    GROUP BY artistid,day_played

затем я запрашиваю представление со следующим утверждением

SELECT b.artistcount, b.artistname, b.day_played, b.id
FROM 
(
    SELECT day_played, MAX(artistcount) as max_count
    FROM max_play_by_artist
    GROUP BY day_played
) AS a
INNER JOIN max_play_by_artist AS b
ON b.day_played = a.day_played
AND b.artistcount = a.max_count

это дает мне желаемый результат.

person Marcus    schedule 17.06.2010