MYSQL и медиана

270

13 июля 2018, 08:20

Есть 2 таблицы (subj, rait) Необходимо вывести медиальное значение по предметам, то есть id_предмета, имя предмета, медиальное значение по данному предмету.

Как вывести медиальное по предметам без имени предмета - я понял, а вот как туда впихнуть и имя предмета - хоть убейте не понимаю.

    SELECT `subject_id`,(MAX(`value`)+MIN(`value`))/2 FROM
(SELECT cs.`subject_id`,`value`
   FROM
   (SELECT subject_id,value, (
                             SELECT COUNT(1) FROM rating WHERE `value`<o.`value` AND `subject_id`=o.`subject_id`
                             ) as ls
                           , (
                               SELECT COUNT(1) FROM rating WHERE `value`<=o.`value` AND `subject_id`=o.`subject_id`
                             ) as lse
                            FROM rating o
   ) cs JOIN
   (SELECT `subject_id`,COUNT(1)*.5 as cn
      FROM rating
      GROUP BY `subject_id`
    ) cc ON cs.`subject_id`=cc.`subject_id`
 WHERE cn between ls and lse
 ) AS medians
GROUP BY `subject_id`

Answer 1

Если у вас выводит subj_id в результирующем запросе, то его надо просто сджойнить с таблицей названий.

select subj.id, subj.name, T1.val from (
SELECT `subject_id`,(MAX(`value`)+MIN(`value`))/2 as val FROM ...
) as T1
LEFT JOIN subj ON subj.id=T1.subj_id

Answer 2

Немного поломав голову (всё оказалось гораздо проще - нужен был перекур и чайная пауза) - было допилено до следующего варианта, который отдает всё в нужном виде:

SELECT subject_id, name, (MAX(`value`)+MIN(`value`))/2 FROM
(
    SELECT cs.`subject_id`,cs.`name`,`value`
   FROM
   (SELECT o.subject_id, subject.name, o.value, (
                             SELECT COUNT(1) FROM rating WHERE `value`<o.`value` AND `subject_id`=o.`subject_id`
                             ) as ls
                           , (
                               SELECT COUNT(1) FROM rating WHERE `value`<=o.`value` AND `subject_id`=o.`subject_id`
                             ) as lse
                            FROM rating o, subject
   ) cs JOIN
   (SELECT `subject_id`,COUNT(1)*.5 as cn
      FROM rating
      GROUP BY `subject_id`
    ) cc ON cs.`subject_id`=cc.`subject_id`
 WHERE cn between ls and lse
    ) as finaldata 
    GROUP BY subject_id