IMHO.WS - Вопрос по SQL

IMHO.WS (http://www.imho.ws/index.php)

- Программирование (http://www.imho.ws/forumdisplay.php?f=40)

- - Вопрос по SQL (http://www.imho.ws/showthread.php?t=85113)

Вопрос по SQL

Помогите пожалуйста сделать такой SQL запрос: надо высчитать максимальное среднее значение по определеному столбцу, знаю как найти максимальное значение, знаю как найти среднее, но как это вмести объединить? В книжках и мануалах прочитал только что вложеных агрегатных запросов делать незя, но ни где не нашел как решить эту проблему.

Дык вроде среднее значение по определению одно - что ты понимаешь под "максимальным средним"?

«Максимальное среднее» высчитывается обычно за несколько периодов или итераций, т.е. просто берется максимум среди средних значений за каждый период (итерацию). Сразу по всему столбцу данных же можно вычислить только одно среднее. Задача видимо требует уточнения условия.

да виноват не корректно задал вопрос, а дело вот в чем у меня вот такие 3 таблицы, нарисовал как мог :) , -- – внешний ключ

Код:

таблица:  ГРУППА             СТУДЕНТ               ЭКЗАМЕН

поля:     имя кафедры        идентификатор---------идентификатор

          номер группы-------номер группы          оценка

                             фамилия студента

необходими найти на каждой кафедре студента с наибольшим средним балом, вот откуда взялось максимальное среднее.

Вот до чего дошел я:

Код:

SELECT s.stud_id stud_id, cath_id, avgm FROM student s,

(SELECT stud_id, AVG(mark) avgm FROM exam_marks GROUP BY stud_id) a

WHERE s.stud_id = a.stud_id;

При этом получается таблица из трех полей: номер группы (cath_id), идентификатор студента (stud_id) и его средний балл (avgm). В принципе можно найти MAX(avgm), сгруппировав по полю cath_id, но при этом теряется поле stud_id - выбирать можно только те поля, которые переписаны в GROUP BY или являются аргументами агрегирующих функций; если же в GROUP BY добавить и это поле (т.е. группировать по cath_id и stud_id), то смысл поиска MAX теряется - строки группируются по одной (очевидно - не может быть двух строк с одинаковыми обоими полями и stud_id, и cath_id). Надо еще подумать... Но, может, дальше и сам до чего-недь додумаешься?...

З.Ы. Кстати, на чем пишешь? Если на Oracle (как и я), то может стоит заколбасить процедурку на PL/SQL?

[QUOTE=Ghost]Вот до чего дошел я:[CODE]
SELECT s.stud_id stud_id, cath_id, avgm FROM student s,

Цитата:

Сообщение от Ghost

Вот до чего дошел я:

Код:

SELECT s.stud_id stud_id, cath_id, avgm FROM student s,

(SELECT stud_id, AVG(mark) avgm FROM exam_marks GROUP BY stud_id) a

WHERE s.stud_id = a.stud_id;

К сожалению вынужден писать на Access-e :( .
Может я что-то недопонял, но по моему то что ты написал чуть-чуть не то т.к. необходимо найти на каждой кафедре студента с наибольшим средним балом, то отсюда следует, что надо группировать именно по полю имя кафедры а не по каким либо другим, потому что на одной кафедре могут быть (и есть) разные группы, а уже потом в каждой группу выбирать максимальный средний бал.
Если бы можно делать вложенный агрегатные запросы, то это выглядело наверно так :

Код:

SELECT Группа.Кафедра, Студент.Фамилия, Max(Avg(Экзамен.Оценка) AS [Avg-Оценка])

FROM (Группа INNER JOIN Студент ON Группа.[Номер группы]=Студент.[Номер группы]) INNER JOIN Экзамен ON Студент.Идентификатор=Экзамен.[Идентификатор студента]

GROUP BY Группа.Кафедра;

но, как я уже говорил, так делать незя.
Я пытался делать с вложенными запросами :

Код:

SELECT Группа.Кафедра, Студент.Фамилия, Avg(Экзамен.Оценка) AS [Avg-Оценка]

FROM (Группа INNER JOIN Студент ON Группа.[Номер группы]=Студент.[Номер группы]) INNER JOIN Экзамен ON Студент.Идентификатор=Экзамен.[Идентификатор студента]

GROUP BY Группа.Кафедра, Студент.Фамилия

HAVING Max  ( 

                        SELECT Avg(Экзамен.Оценка) AS [Avg-Оценка] FROM (Группа INNER JOIN Студент ON Группа.[Номер группы]=Студент.[Номер группы]) INNER JOIN Экзамен ON Студент.Идентификатор=Экзамен.[Идентификатор студента]

GROUP BY Группа.Кафедра

                      );

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

Так что я все сижу и думаю как сделать эту хрень......

А вот такие штучки а Accesse можно?

Код:

select k.kafedra, k.student, k.avgrade

from (                                                 

        select G.K kafedra, S.SNAME student, AVG(E.GRADE) avgrade

        from G join S on G.NG=S.GN join E on S.S=E.S             

          group by  G.K, S.SNAME) k

where (k.kafedra, k.avgrade) in (

        select m.kafedra, MAX(m.avgrade)

        from (

                select G.K kafedra, AVG(E.GRADE) avgrade

                from G join S on G.NG=S.GN join E on S.S=E.S

                group by  G.K) m

        group by m.kafedra

        )

Код:

SELECT Группа.Кафедра, Студент.Фамилия, Max(Avg(Экзамен.Оценка) AS [Avg-Оценка])

FROM (Группа INNER JOIN Студент ON Группа.[Номер группы]=Студент.[Номер группы]) INNER JOIN Экзамен ON Студент.Идентификатор=Экзамен.[Идентификатор студента]

GROUP BY Группа.Кафедра;

Такой код не прокатит, хотябы потому, что Студент.Фамилия не входит в group by (Вот заметил что ты говоришь что так нельзя, именно по этой причине или group by не катит в Accesse?)

Интересная задачка. Самому стало интересно. Сделал финт ушами: объеденил средний балл и номер студента в одно поле с разделителем ",," - максимально ищется нормально, остается только его разбить на части. У меня, правда, таблицы несколько отличаются:

Код:

CREATE TABLE UNIVERSITY (

  UNIV_ID    INTEGER PRIMARY KEY,

  UNIV_NAME  CHAR(96) NOT NULL UNIQUE,

  RATING     INTEGER,

  CITY       CHAR(20) NOT NULL

);



CREATE TABLE STUDENT (

  STUD_ID   INTEGER PRIMARY KEY,

  SURNAME   CHAR(20) NOT NULL,

  NAME      CHAR(20) NOT NULL,

  STIPEND   INTEGER,

  KURS      INTEGER NOT NULL,

  CITY      CHAR(20) NOT NULL,

  BIRTHDAY   DATE,

  UNIV_ID   INTEGER REFERENCES UNIVERSITY(UNIV_ID)

);



CREATE TABLE EXAM_MARKS (

  EXAM_ID    INTEGER PRIMARY KEY,

  STUD_ID    INTEGER REFERENCES STUDENT(STUD_ID),

  SUBJ_ID    INTEGER REFERENCES SUBJECT(SUBJ_ID),

  MARK       INTEGER,

  EXAM_DATE  DATE,

  CHECK ((MARK>=2) AND (MARK<=5))

);

Т.е. вместо таблицы ГРУППА - таблица УНИВЕРСИТЕТЫ, но это не принципиально. В результате получаются три поля: univ_id - идентификатор университета, maxmark - максимальная средняя оценка для данного университета, stud_id - идентификатор студента, имеющего эту оценку:

Код:

select univ_id, substr(csm, 1, instr(csm, ',,') - 1) maxmark,

substr(csm, instr(csm, ',,') + 2, length(csm) - instr(csm, ',,') - 1) stud_id from

(select univ_id, max(csa) csm from

(select univ_id, to_char(round(avgm, 2)||',,'||s.stud_id) csa from student s,

(select stud_id, avg(mark) avgm from exam_marks group by stud_id) a

where s.stud_id = a.stud_id) group by univ_id)

Зная univ_id и stud_id несложно выковырять название университета (кафедры в твоем случае) и ф.и.о. студента из таблиц university и student соответственно.

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

З.Ы. К сожалению, переложить и проверить это решения для Access'а не могу в виду его (Access'а) хронического отсутствия и незнания. Но, думаю, ход моих мыслей тебе понятен.

Цитата:

Сообщение от obrp

А вот такие штучки а Accesse можно?

Код:

select k.kafedra, k.student, k.avgrade

from (                                                 

        select G.K kafedra, S.SNAME student, AVG(E.GRADE) avgrade

        from G join S on G.NG=S.GN join E on S.S=E.S             

          group by  G.K, S.SNAME) k

where (k.kafedra, k.avgrade) in (

        select m.kafedra, MAX(m.avgrade)

        from (

                select G.K kafedra, AVG(E.GRADE) avgrade

                from G join S on G.NG=S.GN join E on S.S=E.S

                group by  G.K) m

        group by m.kafedra

        )

Код:

SELECT Группа.Кафедра, Студент.Фамилия, Max(Avg(Экзамен.Оценка) AS [Avg-Оценка])

FROM (Группа INNER JOIN Студент ON Группа.[Номер группы]=Студент.[Номер группы]) INNER JOIN Экзамен ON Студент.Идентификатор=Экзамен.[Идентификатор студента]

GROUP BY Группа.Кафедра;

По второму примеру, там код не работает, как ты правильно заметил, из-за того что Студент.Фамилия не входит в group by, но и еще из-за вложенного запроса

Код:

Max(Avg(Экзамен.Оценка)

, что я и хотел показать этим примером.

Теперь по первому примеру.
в первом подзапросе

Код:

select G.K kafedra, S.SNAME student, AVG(E.GRADE) avgrade

        from G join S on G.NG=S.GN join E on S.S=E.S             

          group by  G.K, S.SNAME

фактически получаем средний бал каждого студента

в третьем подзапросе

Код:

select G.K kafedra, AVG(E.GRADE) avgrade

from G join S on G.NG=S.GN join E on S.S=E.S

group by  G.K

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

Код:

SELECT k.kafedra, k.student, k.avgrade

FROM [SELECT Группа.Кафедра, Студент.Фамилия, Avg(Экзамен.Оценка) AS [Avg-Оценка]

FROM (Группа INNER JOIN Студент ON Группа.[Номер группы]=Студент.[Номер группы]) INNER JOIN Экзамен ON Студент.Идентификатор=Экзамен.[Идентификатор студента]

GROUP BY Группа.Кафедра, Студент.Фамилия

]. AS k

WHERE (k.kafedra, k.avgrade) in (

select m.kafedra, MAX(m.avgrade)

from (

SELECT Группа.Кафедра, Avg(Экзамен.Оценка) AS [Avg-Оценка]

FROM (Группа INNER JOIN Студент ON Группа.[Номер группы]=Студент.[Номер группы]) INNER JOIN Экзамен ON Студент.Идентификатор=Экзамен.[Идентификатор студента]

GROUP BY Группа.Кафедра) m

group by m.kafedra

);

и вот что он мне ответил:
Из подчиненного запроса, который не использует ключевое слово EXISTS в предложении FROM главного запроса, возвращается более одного поля. Укажите в инструкции SELECT подчиненного запроса возвращение только одного поля. (Ошибка 3306)
по моему это ошибка вот тут

Код:

MAX(m.avgrade)

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

В oracle это работает:

Код:

SQL> select k.kafedra, k.student, k.avgrade

  2  from (                                                 

  3     select G.K kafedra, S.SNAME student, AVG(E.GRADE) avgrade

  4     from G join S on G.NG=S.GN join E on S.S=E.S             

  5     group by  G.K, S.SNAME) k

  6  where (k.kafedra, k.avgrade) in (

  7     select m.kafedra, MAX(m.avgrade)

  8     from (

  9             select G.K kafedra, AVG(E.GRADE) avgrade

 10             from G join S on G.NG=S.GN join E on S.S=E.S

 11             group by  G.K, S.SNAME) m

 12     group by m.kafedra

 13     );



KAFEDRA              STUDENT       AVGRADE

-------------------- ---------- ----------

kat1                 Petrov              7

kat2                 Vasin               7



SQL> select G.K kafedra, S.SNAME student, AVG(E.GRADE) avgrade

  2     from G join S on G.NG=S.GN join E on S.S=E.S             

  3     group by  G.K, S.SNAME;



KAFEDRA              STUDENT       AVGRADE

-------------------- ---------- ----------

kat1                 Ivanov     5.33333333

kat1                 Petrov              7

kat1                 Pupkin              5

kat1                 Sidorov    4.83333333

kat2                 Lenin      6.33333333

kat2                 Mishin              6

kat2                 Popov      5.66666667

kat2                 Vasin               7



8 rows selected.



SQL> select G.K kafedra, AVG(E.GRADE) avgrade

  2             from G join S on G.NG=S.GN join E on S.S=E.S

  3             group by  G.K, S.SNAME;



KAFEDRA                 AVGRADE

-------------------- ----------

kat1                 5.33333333

kat1                          7

kat1                          5

kat1                 4.83333333

kat2                 6.33333333

kat2                          6

kat2                 5.66666667

kat2                          7



8 rows selected.



SQL> select m.kafedra, MAX(m.avgrade)

  2     from (

  3             select G.K kafedra, AVG(E.GRADE) avgrade

  4             from G join S on G.NG=S.GN join E on S.S=E.S

  5             group by  G.K, S.SNAME) m

  6     group by m.kafedra;



KAFEDRA              MAX(M.AVGRADE)

-------------------- --------------

kat1                              7

kat2                              7

Похоже что в Accesse Inline View не работают.

Цитата:

Сообщение от Ghost

Код:

select univ_id, substr(csm, 1, instr(csm, ',,') - 1) maxmark,

substr(csm, instr(csm, ',,') + 2, length(csm) - instr(csm, ',,') - 1) stud_id from

(select univ_id, max(csa) csm from

(select univ_id, to_char(round(avgm, 2)||',,'||s.stud_id) csa from student s,

(select stud_id, avg(mark) avgm from exam_marks group by stud_id) a

where s.stud_id = a.stud_id) group by univ_id)

Принцип понял, действительно финт ушами :) . Но думаю что вот этот кусок

Код:

 

max(csa)

Access не поймет, по анологии с тем что я писал в предыдущем своем сообщении я там уже пытался ему такое "скормить".
И еще не могу понять как работает вот это

Код:

substr(csm, 1, instr(csm, ',,') - 1)

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

Вот наваял тебе решение в Аксессе (Аксеса2003). Там три запроса, конечный результат выдает запрос под названием Result.

Всем большее ааааагромное спасибо !

to obrp:
принцип понял, пытаюсь "перевести" Аксесу.

to Guns_linger:
просто хочу это все запихнуть в один запрос.