Как найти средний балл sql

to continue to Google Sites

SELECT [ALL/DISTINCT] <список атрибутов>/*
FROM <список таблиц>
[WHERE <условие выборки>]
[ORDER BY <список атрибутов>]
[GROUP BY <список атрибутов>]
[HAVING <условие>]
[UNION<выражение с оператором SELECT>]

SELECT id_st, surname 
FROM student

SELECT id_st, mark 
FROM mark_st
WHERE id_st = 1

SELECT id_st, mark 
FROM mark_st
WHERE ( MARK >= 2 ) AND ( MARK <= 3 )

SELECT id_st, mark
FROM mark_st
WHERE mark IN (2,3)

SELECT id_st, mark
FROM mark_st
WHERE mark BETWEEN 2 AND 3

SELECT id_st, surname
FROM student
WHERE surname LIKE 'А%'

SELECT 	AVG(mark)
FROM 	mark_st

SELECT AVG(mark)
FROM  mark_st 
WHERE id_st = 100

SELECT  AVG(mark)
FROM  mark_st
WHERE id_ex = 10

SELECT   id_st, AVG(mark)
FROM     mark_st
GROUP BY id_st

SELECT  id_st, AVG(mark)
FROM  mark_st
WHERE id_ex = 100
GROUP BY  id_st

SELECT  'Средний балл=', AVG(mark)
FROM  mark_st 
WHERE  id_ex = 10

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

Оператор IS NULL позволяет обнаруживать в таблицы неопределенные значения (напомним, что подобные значения называются

NULL).

Так, например, следующий запрос покажет всех абитуриентов, которые не указали место своего рождения:

Использование агрегатных функций. Простые запросы

Очень часто возникает необходимость произвести вычисление минимальных, максимальных или средних значений в столбцах. Так, например, Вам может понадобиться вычислить средний балл или что-то другое. Для осуществления подобных вычислений в языке программирования высокого уровня Вы бы предприняли некоторые действия, в частности организовали бы цикл, сосчитали сумму, потом разделили бы ее на количество слагаемых в сумме, получив значение среднего балла. SQL избавляет Вас от необходимости программирования всей последовательности подобных действий. Для этого SQL предоставляет Вам специальные агрегатные функции:

Так, следующие запросы считают, соответственно, минимум, максимум, сумму, среднее среди всех баллов, полученными студентами на экзаменах. Количество оценок считает последний запрос.

166

Заметим, что, изменив следующим образом текст последнего запроса, мы получим количество разных оценок, полученных на экзаменах:

SELECT COUNT(DISTINCT mark)

FROM mark

Использование агрегатных функций. Комбинирование с предложением WHERE

Дополнительно к описанным выше простым случаям, Вы можете использовать агрегатные функции совместно с предложением

WHERE:

Данный запрос вычислит средний балл студента с кодом 100 по результатам всех сданных им экзаменов.

Данный запрос вычислит средний балл студентов по результатам сдачи экзамена с кодом 10.

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

167

Использование агрегатных функций и группировка. Предложение GROUP BY

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

Для этого в SQL существует специальная конструкция GROUP BY, предназначенная для указания того столбца, по значениям которого будет производиться группировка.

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

Все это, как обычно, может быть совмещено с предложением WHERE. При этом, не вдаваясь в тонкости выполнения запроса внутри СУБД, Вы можете считать, что сначала выполняется выборка тех строк таблицы, которые удовлетворяют условиям из предложения WHERE, а потом производится группировка и агрегирование.

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

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

168

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

Использование агрегатных функций и группировка. Ограничение результатов запроса. Предложение HAVING

В языке SQL присутствуют средства, которые позволяют Вам сделать следующее: допустим, Вы хотите получить в качестве результатов запроса данные только тех абитуриентов, средний балл которых превышает 4. Как это сделать? Проблема в том, что стандарт языка не допускает использование агрегатных функций в предложении WHERE. Получается, что сначала необходимо выполнить запрос и сосчитать среднее, а потом ограничить результат по условию «Среднее > 4». Для осуществления подобных действий стандартом языка предусмотрена специальная секция HAVING. Так, запрос может выглядеть следующим образом:

Форматирование вывода. Выражения в запросе. Упорядочение

Для того, чтобы форматировать вывод, Вы можете использовать различные возможности SQL. Так, например, допустимым является включение текста в запрос. Рассмотрим пример того, как это делается:

В результате данного запроса Вы увидите не просто некоторое число, а число, сопровожденное поясняющим текстом.

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

Так, например, Вы можете перейти от 5-балльной к 10-балльной системе, преобразовав значение среднего балла следующим образом:

169

Следующий важный момент связан с осуществлением сортировки (упорядочения) данных. Для организации сортировок в стандарте SQL предусмотрена специальная секция ORDER BY. В этой секции Вам необходимо указать, по какому столбцу (по каким столбцам) упорядочивать результаты запроса. При этом, при использовании нескольких столбцов упорядочение будет производится последовательно (по первому критерию, если значения совпали – по второму критерию и т.д.).

Следующий запрос распечатывает список абитуриентов, в котором фамилии следуют в алфавитном порядке.

По умолчанию подразумевается, что сортировка производится по возрастанию. Если Вам необходимо упорядочивать по убыванию, необходимо использовать указание «DESC».

Для упорядочения по возрастанию присутствует указание «ASC», но его обычно не пишут явным образом.

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

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

170

GROUP BY entrant_id, mod_date

HAVING AVG(mark) > 4

ORDER BY entrant_id

6.4.2.Использование SQL для выбора информации из нескольких таблиц

До сих пор мы рассматривали ту часть SQL, которая касалась выбора информации из единственной таблицы. Естественно, возможности языка этим не ограничиваются. Так, Вы можете запрашивать информацию из нескольких таблиц, реализуя описанные в соответствующем разделе учебника реляционные операции. Рассмотрим некоторые примеры того, как это делается. Стоит упомянуть, что полное рассмотрение этой темы выходит за рамки данного учебника. Подробно этот вопрос можно изучить при помощи, например, [6, 8]. Мы рассмотрим некоторые несложные случаи, часто встречающиеся на практике, в которых присутствует необходимость выбора информации из нескольких таблиц сразу.

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

171

Рис. 49. Пример связанных таблиц

В примере (рис. 48) тоже присутствуют такие таблицы. Рассмот-

рим таблицы entrant, mark и exam.

Таблица mark (оценки) связана с таблицей exam (экзамены) по по-

лям id–entrant_id.

Таблица mark (оценки) связана с таблицей entrant (абитуриенты)

по полям id– exam_id.

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

Заметим, следующие изменения по сравнению с теми запросами, которые мы писали ранее:

1.В секции FROM указаны 2 таблицы.

2.Так таблиц стало больше одной, появилась некоторая неоднозначность при упоминании полей. Так, во многих случаях неизвестно, из какой таблицы из списка FROM брать поле. Для устранения неоднозначности имена полей указываются с префиксом – именем таблицы. Имя таблицы от имени поля отделяется точкой.

3.В предложении WHERE указано условие соединения таблиц.

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

Нетрудно заметить, что использование префиксов-имен таблиц сильно загромождает запрос. Для того, чтобы избежать подобного загромождения, используются псевдонимы. Так, мы можем переписать предыдущий запрос следующим образом:

172

6.4.3.Использование SQL для вставки, редактирования и удаления данных в таблицах

Для добавления данных в таблицу в стандарте SQL предусмотрена команда INSERT. Синтаксис команды проще всего понять из следующего запроса:

INSERT INTO mark

VALUES (1, 2, 5, ’01.08.2003’, 11)

Данный запрос вставляет в таблицу mark строку, содержащую значения, перечисленные в списке VALUES.

Если Вы не хотите указывать значение какого-то поля, Вы можете присвоить ему NULL:

INSERT INTO mark

VALUES (1, 2, 5, ’01.08.2003’, NULL)

В случае, если Вы считаете необходимым использование для некоторых полей значений по умолчанию, SQL позволяет явно указать, какие поля необходимо заполнить Вашими данными, а какие – значениями по умолчанию.

INSERT INTO mark (entrant_id, exam_id, mark) VALUES (1, 2, 5)

Кроме явного указания значений, Вы также можете вставить в таблицу данные, представляющие собой результат выполнения другого запроса. Так, пусть у Вас есть вспомогательная таблица entrant_temp с

полями id, name.

INSERT INTO entrant_temp (name)

Для удаления данных из таблицы существует команда DELETE.

DELETE

173

[challenge_log]
User_id   |    challenge  |  Try   |   Points
==============================================
  1               1           1            5
  1               1           2            8
  1               1           3            10
  1               2           1            5
  1               2           2            8
  2               1           1            5
  2               2           1            8
  2               2           2            10

User_id   |    challenge  |  Points
===================================
  1               1           10
  1               2           8
  2               1           5
  2               2           10

User_id   |  Points
===================
  1           18
  2           15

AVG = SUM (Points from step 2) / number of users = 16.5

2014-08-21 18:01

4
ответа

Решение

select sum(Points) / count(distinct userid)
from (select userid, challenge, max(Points) as Points
      from challenge_log
      group by userid, challenge
     ) cl;

select sum(Points) / count(distinct userid)
from challenge_log cl
where not exists (select 1
                  from challenge_log cl2
                  where cl2.userid = cl.userid and
                        cl2.challenge = cl.challenge and
                        cl2.points > cl.points
                 );

SELECT USER_ID, AVG(POINTS)
FROM STEP1
GROUP BY USER_ID

    Select BestShot.[User_ID], AVG(cast (BestShot.MostPoints as money))
    from (select tLog.Challenge, tLog.[User_ID], MostPoints = max(tLog.points) 
            from dbo.tmp_Challenge_Log tLog
            Group by tLog.User_ID, tLog.Challenge
        ) BestShot
    Group by BestShot.User_ID