У меня есть такая таблица:
идентификатор (ПК) | название | курс | дата | оценка |
---|---|---|---|---|
1 | Джейн | Французский | 20.11.2016 | 20 |
3 | КАРЛ | Французский | 2015-09-02 | 30 |
4 | Анна | Французский | 20.11.2016 | 25 |
5 | ДЖОН | Французский | 2016-09-02 | 56 |
6 | Линда | английский | 2016-09-02 | 22 |
7 | ТИМ | английский | 20.11.2016 | 23 |
8 | ДЖОН | английский | 20.11.2016 | 44 |
и мне интересно, можно ли ответить на следующие два вопроса одним запросом:
Какова средняя оценка при группировке по курсам среди всех зачисленных студентов во все даты курса? (это кажется более простым, так как грубо сгруппируйте по нескольким столбцам, а затем возьмите среднее значение столбца «оценка»).
Каково среднее количество студентов, записавшихся на каждый курс по дате? Другими словами, «на английском языке», поскольку два студента были зачислены 11-20, а один студент был зачислен 09-02, среднее количество зачисленных студентов на дату занятия составляет 1,5.
Можно ли ответить на оба этих вопроса одним запросом? Или это так, что, поскольку я буду «группировать» по разным столбцам, я не могу добиться этого в одном запросе?
Я использую EF Core 5.0, поддерживаемый базой данных SQL Server, поэтому было бы неплохо, если бы я мог добиться этого с помощью оператора LINQ groupby, но если нет, я не возражаю против написания прямого SQL, если это более эффективно.
Объединить их будет несколько сложнее, и это практически невозможно сделать в EF/Linq. Я бы сказал, что это того стоит, только если дважды запрашивать таблицу крайне неэффективно. В противном случае просто выполните их либо в EF, либо в виде одного пакета T-SQL из двух запросов.
SELECT
c.Course,
AVG(c.Grade * 1.0) AvgGrade
FROM Course c
GROUP BY
c.Course;
И в Линке
from c in Course
group by c.Course into g
select new {
Course: g.Key,
AvgGrade: g.Avg(c2 => c2.Grade * 1.0)
}
SELECT
c.Course,
AVG(Count * 1.0) AvgCount
FROM (
SELECT
c.Course,
COUNT(*) Count
FROM Course c
GROUP BY
c.Course,
c.Date
) c
GROUP BY
c.Course;
И в Линке
from c in Course
group by new {c.Course, c.Date} into g
select new {
Course: g.Key.Course,
Count: g.Count()
} into c2
group by c2.Course into g2
select new {
Course: g2.Key,
AvgCount: g2.Average(c3 => c3.Count * 1.0)
}
Чтобы совместить это, вы можете использовать следующее:
Это усложняется тем фактом, что уже есть агрегирование, и вы не можете (математически) вычислить средние значения по средним. Поэтому вам нужно использовать SUM / COUNT
и вложить группировку.
SELECT
c.Course,
AVG(Count * 1.0) AvgCount,
SUM(c.TotalGrades) * 1.0 / SUM(Count) AvgGrade
FROM (
SELECT
c.Course,
COUNT(*) Count,
SUM(c.Grade) TotalGrades
FROM Course c
GROUP BY
c.Course,
c.Date
) c
GROUP BY
c.Course;
И в Линке
from c in Course
group by new {c.Course, c.Date} into g
select new {
Course: g.Key.Course,
Count: g.Count(),
TotalGrades: g.Sum(c3 => c3.Grade)
} into c2
group by c2.Course into g2
select new {
Course: g2.Key,
AvgCount: g2.Average(c3 => c3.Count * 1.0).
AvgGrade: g2.Sum(c3 => c3.TotalGrades) * 1.0 / g2.Sum(c3 => c3.Count)
}
Извините, если эта часть моего вопроса была неясна.
О, тогда все намного проще. См. правки
Спасибо, Чарлифейс, это очень полезно. Я почитаю о групповых наборах, хотя я думаю, что лучший путь вперед - это два отдельных запроса для простоты. Для второго запроса это близко, но я не думаю, что именно мне нужно: это дает мне группу для каждой комбинации курса/даты, но мне нужна просто группа для каждого курса, заполненная значением среднего количества по датам. Я мог бы легко добиться этого, выполнив некоторую постобработку для каждого набора групп, но есть ли способ сделать это в одном запросе? Итак, на английском языке у нас было два абитуриента в один день и 1 в другой, поэтому результат = 1,5.