еще один сложный запрос.

[sickboy]

еще один сложный запрос.

hi,
есть вот такие таблицы:

таблица учета скачивания треков.

mysql> desc music_dl_count;
+---------+----------------------+------+-----+---------+----------------+
| Field   | Type                 | Null | Key | Default | Extra          |
+---------+----------------------+------+-----+---------+----------------+
| id_msdl | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| id_mst  | smallint(5) unsigned | YES  |     | NULL    |                |
+---------+----------------------+------+-----+---------+----------------+

таблица связывающая id трека с id альбомом

mysql> desc music_tracks;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id_mt     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| id_ma     | int(10) unsigned    | NO   |     |         |                |
| track     | varchar(255)        | NO   | MUL |         |                |
+-----------+---------------------+------+-----+---------+----------------+

таблица связывающая id альбома с id исполнителем.

mysql> desc music_album;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id_ma | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| id_mz | int(10) unsigned | NO   |     |         |                |
| album | varchar(255)     | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+

Суть задачи состоит в том, чтобы определить рейтинг популярности альбома на основе загрузок треков из этого альбома.
Рейтинг учитывается так:
1. подсчитать общее кол-во скачанных треков из одного альбома,
2. поделить это число на общее число треков в альбоме.
3. и вывести список альбомов упорядоченных по этому числу.

Первый пункт я сделал так:

select 
         count(distinct(id_msdl)) as dlc, id_mst from music_dl_count
inner join
         music_tracks on music_tracks.id_mt=music_dl_count.id_mst
left join
         music_album on music_album.id_ma=music_tracks.id_ma 
group by
          music_album.album order by dlc;

получил:

+-----+--------+
| dlc | id_mst |
+-----+--------+
|   1 |    211 |
|   2 |     88 |
|  11 |    112 |
|  13 |    172 |
|  13 |     50 |
|  15 |    155 |
|  16 |     83 |
+-----+--------+

со второго шага начинаются проблемы, как получить общее кол-во треков в альбоме, но только для выше отобранных альбомов?
Если для всех, то достаточно просто:

SELECT 
           music_album.id_ma, COUNT(*) AS tracks_count
FROM
           music_album 
LEFT JOIN
           music_tracks
ON
           music_album.id_ma=music_tracks.id_ma 
GROUP BY
           music_album.id_ma;

 

[Andreika]

"но только для" === "WHERE"
или тут все намного сложнее?

 

[sickboy]

Автор оригинала: Andreika
"но только для" === "WHERE"
или тут все намного сложнее?

еще у меня был вариант:
[SQL]select
music_album.id_ma, COUNT(music_tracks.id_mt) as albums_count
from
music_album
left join
music_tracks
on
music_tracks.id_ma=music_album.id_ma
where
music_album.id_ma = any (select
music_album.id_ma
from
music_dl_count
INNER JOIN
music_tracks
on music_tracks.id_mt=music_dl_count.id_mst
left join
music_album on music_album.id_ma=music_tracks.id_ma
group by
music_album.id_ma)
group by
music_album.album;[/SQL]

но такой запрос при 10 записях уже выполняется около 1 с.

 

[Andreika]

[sql]
SELECT a.album,a.id_ma,t.id_mt,c.*,AVG(c.id_mst) as av FROM music_album a LEFT JOIN music_tracks t ON(a.id_ma=t.id_ma) LEFT JOIN music_dl_count c ON(t.id_mt=c.id_msdl ) GROUP BY a.id_ma ORDER BY av
[/sql]

это если
music_dl_count - id_msdl - id трека, id_mst - кол-во его скачиваний...
иначе проясни смысл сих полей )

 

[sickboy]

нет наоборот.
music_dl_count:
id_msdl - кол-во скачиваний, точнее:

mysql> select id_msdl, id_mst  from music_dl_count limit 5;
+---------+--------+
| id_msdl | id_mst |
+---------+--------+
|       1 |     18 |
|       2 |    258 |
|       3 |     17 |
|       4 |     17 |
|       5 |    409 |
+---------+--------+

id_msdl - auto_increment
id_mst - id трека.

-~{}~ 06.04.06 13:13:

Andreika
ваш пример как то странно работает и считает совсем не то, что нужно.

 

[Andreika]

sickboy
он и не должен считать то, что нужно в этом случае..

[sql]
SELECT a.album, a.id_ma, t.id_mt, c. * , COUNT( c.id_msdl ) / COUNT( DISTINCT id_mt ) AS av
FROM music_album a
LEFT JOIN music_tracks t ON ( a.id_ma = t.id_ma )
LEFT JOIN music_dl_count c ON ( t.id_mt = c.id_mst )
GROUP BY a.id_ma
ORDER BY av DESC

[/sql]

 

[chira]

select a.album, dwnl_tracks/alb_track AS rating
FROM 
  music_album a
  LEFT JOIN (
  SELECT t.id_ma, COUNT(*) dwnl_tracks, COUNT(DISTINCT t.id_mt) alb_track
  FROM music_dl_count c
    ,music_tracks t
  WHERE t.id_mt=c.id_mst
  GROUP BY t.id_ma
  ) t ON a.id_ma = t.id_ma
ORDER BY rating

 

[sickboy]

Andreika
Спасибо.
Ваш пример считает именно так как нужно.