partial distinct
Posted Sep.17, 2009 in Общая
предлагаю SQL-задачку. не надуманная, возникла на работе.
контекст: музыкальный сайт, таблица (mysql) с плейлистами юзера
playlists:
{
int id, //автоинкремент
int song_id, //id самой песни
int user_id, //id юзера, который добавил себе песню
datetime added, //время, когда добавлена песня в его плейлист
}
song_id не уникальны, т.к. песню могли добавить несколько юзеров.
задача: написать SQL для выборки: “последние песни, добавленные в плейлисты пользователей”. для песен нужно вывести кто и когда их добавил в последний раз. дубликаты песен выводить не нужно, если песню добавили два юзера – вывести последнее добавление.
иными словами, нужно выбрать “последние N неповторяющихся песен в плейлистах с указанием того, кто последний и когда её добавил”.
то, что пока придумал я, имеет 4 select’а в запросе и 3й уровень вложенности этих select’ов. сможешь лучше?
P.S. конечно же, должно работать максимально быстро. в наличии есть индекс по added.
Tags: mysql, web, задачи, программирование
September 17th, 2009 on 14:22
легко. написал за 2 мин + 5 минут на тест
SELECT p.id, p.song_id, MIN(p.user_id) AS user_id, p.added FROM (SELECT `song_id` AS sid, MAX(added) AS a FROM `playlists` GROUP BY sid) AS t INNER JOIN playlists AS p ON p.song_id=t.sid AND p.added=t.a GROUP BY p.song_id ORDER BY p.added DESC LIMIT N
внутренний подзапрос t выбирает для каждой песни последнее её добавление, а потом соединяем с основной таблицей, находим минимального (чтоб не было повторений на случай одновременных добавлений) пользователя, который её добавил в выбранное в t время.
September 17th, 2009 on 14:39
Re: легко. написал за 2 мин + 5 минут на тест
хорошее решение, вот только работает за ~0.4 секунды на 50к записях даже при всевозможных индексах.
September 17th, 2009 on 14:51
Re: легко. написал за 2 мин + 5 минут на тест
Попробуй так:
SELECT p.id, p.song_id, MIN(p.user_id) AS user_id, p.added FROM (SELECT `song_id` AS sid, MAX(added) AS a FROM `playlists` GROUP BY sid ORDER BY a DESC LIMIT N) AS t INNER JOIN playlists AS p ON p.song_id=t.sid AND p.added=t.a GROUP BY p.song_id ORDER BY p.added DESC LIMIT N
на твоём дампе сработало за ~0.01 секунды
September 17th, 2009 on 20:49
Re: легко. написал за 2 мин + 5 минут на тест
уже лучше. ~0.1 секунды на 100к записях. но всё равно это медленно.
хотя, надо признать, твой вариант очень красивый, и я до такого не додумался.