Функция агрегации SQL

Итак, у меня есть этот код здесь:

SELECT MOVIETITLE AS "Movie Title", MIN(AVG(RATING)) AS "Lowest Average Rating"
FROM MOVIE, RATING
WHERE MOVIE.MOVIEID = RATING.MOVIEID
GROUP BY MOVIETITLE;

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

MIN(AVG(RATING))

Я продолжаю получать эту ошибку, и я не могу понять, как ее решить:

ORA-00937: not a single-group group function

Я новичок в SQL и Oracle, поэтому для меня это все очень ново...

ИЗМЕНИТЬ

Хорошо, просто чтобы прояснить ситуацию, несколько человек оценивают одни и те же фильмы в таблице рейтингов, и в основном нужно получить среднее значение всех оценок для каждого фильма и перечислить фильм с самым низким средним значением.


sql function oracle aggregation
person bloodstorm17    schedule 19.11.2012    source источник
comment
Что вы подразумеваете под самым низким средним рейтингом? Я понимаю, как фильм может иметь самый низкий рейтинг и средний рейтинг, но это две разные вещи.   -  person Brian Willis    schedule 19.11.2012
comment
В таблице рейтингов несколько человек оценивают одни и те же фильмы, и в основном нужно получить среднее значение всех оценок для каждого фильма и перечислить фильм с самым низким средним значением.   -  person bloodstorm17    schedule 19.11.2012

Ответы (6)


arrow_upward
3
arrow_downward

И еще один скрипт SQL

select min(rating)
from (select m.movietitle, avg(r.rating) as rating
      from movie m, rating r
      where m.movieid = r.movieid
      group by m.movietitle) t;
person Olaf Dietsche    schedule 19.11.2012
comment
Это, наверное, лучше, если название фильма также не требуется. - person David Aldridge; 19.11.2012

arrow_upward
2
arrow_downward

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

SELECT MOVIETITLE AS "Movie Title", AVG(RATING) AS "AVGRating"
FROM MOVIE, RATING
WHERE MOVIE.MOVIEID = RATING.MOVIEID
GROUP BY MOVIETITLE
HAVING AVG(RATING) =
(
    SELECT MIN(AVG(RATING)) AS "AVGRating"
    FROM MOVIE, RATING
    WHERE MOVIE.MOVIEID = RATING.MOVIEID
    GROUP BY MOVIETITLE
)
person John Woo    schedule 19.11.2012

arrow_upward
2
arrow_downward

Другой подход (если есть несколько фильмов с одинаковым минимальным рейтингом, то все они будут отображаться):

-- sample of data just for the sake of demonstration
SQL> with movie as(
  2     select 1  as movieid , 'Departed' as movietitle from dual union all
  3     select 2, 'Shutter Island' from dual union all
  4     select 3, 'Terminator'     from dual
  5  ),
  6  rating as(
  7    select 1  as movieid, 7 as rating from dual union all
  8    select 1, 8 from dual union all
  9    select 1, 9 from dual union all
 10    select 1, 6 from dual union all
 11    select 1, 7 from dual union all
 12    select 2, 9 from dual union all
 13    select 2, 5 from dual union all
 14    select 2, 6 from dual union all
 15    select 3, 6 from dual union all
 16    select 3, 5 from dual union all
 17    select 3, 6 from dual
 18  ) -- the query
 19   select w.movietitle     as "Movie Title"
 20        , round(w.mavr, 1) as "Lowest Average Rating"
 21     from ( select movietitle
 22                 , min(avg(rating)) over() as mavr
 23                 , avg(rating) as avr
 24              from movie
 25                 , rating
 26             where movie.movieid = rating.movieid
 27             group by movietitle
 28          ) w
 29    where w.mavr = w.avr
 30  ;

Результат:

Movie Title    Lowest Average Rating
-------------- ---------------------
Terminator                       5,7
person Nick Krasnov    schedule 19.11.2012

arrow_upward
2
arrow_downward

Было бы неплохо, если бы существовал стандартный способ включения дополнительных значений в агрегаты. Я обнаружил, что объединяю множество значений в одно значение RAW, беру их совокупность, а затем извлекаю исходные значения из совокупности:

/* lowest returns a single row */
with lowest as (
  select min(
  /* combine movieid and avg(rating) into a single raw
   * binary value with avg(rating) first so that min(..)
   * will sort by rating then by movieid */
  utl_raw.overlay(
  utl_raw.cast_from_binary_integer(movieid),
  utl_raw.cast_from_number(avg(rating)), 5)) packed
  from rating group by movieid)

/* extract our rating and movieid from the packed aggregation
 * and use it to lookup our movietitle */
select movietitle,
  utl_raw.cast_to_number(utl_raw.substr(packed,1,3)) rating
from movie m, lowest l
where m.movieid=
  utl_raw.cast_to_binary_integer(utl_raw.substr(packed,5,4))

примечание: здесь предполагается, что movieid является целым числом, а рейтинг — числом (см. SQL Fiddle DDL). Если оба являются целыми числами или числами, вы также можете «упаковать» их, сдвинув более значимое значение влево (умножив его на степень 2) и сложив их вместе.

скрипт SQL

person gordy    schedule 19.11.2012
comment
это интересно. Как он работает с огромными объемами данных? - person Kirill Leontev; 19.11.2012
comment
@beherenow Я пришел к этому решению, чтобы сделать то же самое для огромных объемов данных. Если вы сравните планы выполнения, вы увидите, что они более эффективны, чем другие решения здесь, поскольку рейтинг сканируется один раз и из фильма извлекается одна строка. - person gordy; 19.11.2012
comment
Я восхищаюсь изобретательностью подхода, но я считаю, что одним из качеств хорошего кода является его легкость понимания, и я не думаю, что он проходит этот тест. - person David Aldridge; 19.11.2012
comment
@DavidAldridge полностью согласен, и я думаю, что SQL мог бы использовать некоторую стандартную семантику для упаковки и распаковки для агрегирования. Что-то вроде min(rating, movieid).movieid - даже если бы вы могли просто сформировать кортеж, было бы неплохо. Это трудное решение — предпочесть удобочитаемость эффективности, когда потеря производительности может быть настолько серьезной. В этом случае я бы выбрал комментарии (сейчас я добавлю несколько комментариев ..) - person gordy; 19.11.2012

arrow_upward
0
arrow_downward

Подсчитайте средний рейтинг, упорядочите по возрастанию и возьмите первый результат.

 SELECT *
 FROM   (
        SELECT   MOVIETITLE AS "Movie Title",
                 AVG(RATING) AS "Lowest Average Rating"
        FROM     MOVIE, RATING
        WHERE    MOVIE.MOVIEID = RATING.MOVIEID
        GROUP BY MOVIETITLE
        ORDER BY 2 ASC)
 WHERE ROWNUM = 1;
person David Aldridge    schedule 19.11.2012
comment
Единственная проблема с этим решением - если у вас есть более одного названия с одинаковым самым низким рейтингом. - person Grisha Weintraub; 19.11.2012
comment
Да, действительно ... вероятно, это не проблема в контексте веб-сайта с рейтингом фильмов, а скорее проблема с чем-то вроде «покажите мне поставщиков с самой низкой ценой» ... или саммит - person David Aldridge; 19.11.2012

arrow_upward
0
arrow_downward

Если вам также нужно название фильма, я бы сделал это с помощью аналитической функции, чтобы получить мин. Это позволяет вам обращаться к каждой таблице только один раз (где решение, данное 今 草 顿 웃, будет попадать в каждую таблицу дважды... один раз при основном выборе и один раз при выборе "имея").

select movietitle as "Movie Title", avgrating as "Lowest Average Rating"
from (
    select
        m.movietitle,
        avg(r.rating) avgrating,
        rank() over (order by avg(rating)) rank
    from
        movie m
        inner join rating r
            on r.movieid = m.movieid
    group by
        m.movietitle
)
where rank = 1;
person Craig    schedule 14.11.2013