Использование соответствующего оператора SELECT для извлечения данных из нескольких таблиц

Поскольку вы не указали точную базу данных, сложно использовать условные выражения для суммы. Например, в MySQL есть функция if, в SQL Server есть функция select/case/when. В зависимости от механизма БД синтаксис может сильно различаться.

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

with student_year_grade(Student_ID,avg_marks,Course_Current_Year)
as (
SELECT 
    Students.Student_ID,
    AVG(Enrollment.Marks_obtained) AS avg_marks ,
    Course_Current_Year
FROM
    Students LEFT JOIN
    Enrollment ON Students.Student_ID=Enrollment.Student_ID 
WHERE 
    Enrollment.Program_Year_When_Enrolled = 'Third' 
GROUP BY 
    Students.Student_ID, Course_Current_Year
)
select Student_ID, 
   sum( if (Course_Current_Year='G',avg_marks,0) as current_mark,
   sum( if (Course_Current_Year='-1',avg_marks,0) as prev_mark,
   sum( if (Course_Current_Year='-2',avg_marks,0) as prev_mark

group by student ID 
from student_year_grade

вам придется заменить -1 кодом предыдущего года, а -2 кодом предыдущего года. и, возможно, умножить на ваш коэффициент 1/3 или 2/3

Проблема

Вам нужно изучить и понять, что такое обычный Подзапрос SQL.

• Есть два типа:
  • Скалярный подзапрос (не коррелированный)
  • Коррелированный подзапрос
• Подзапрос можно использовать везде, где можно использовать выражение.

• Оба являются базовыми, важными средствами ANSI SQL, которые у нас есть с SQL ANSI 1987.

• Вот текущая запись для коррелированного подзапроса:

ISO/IEC 9075-2:2008(E), page 1262 (Annex F):

45 E061-13 Correlated subqueries
— Subclause 8.1, /predicate/:
When a /correlation name/ can be used in a /table subquery/
as a correlated reference to a column in the outer query

• Этот Страница MS SQL о подзапросах имеет полное объяснение.

Рассмотрение

Для этого требуется тип корреляции.

Представьте себе внешнюю сетку (как в электронной таблице). Строки Student_ID, столбцы три, которые вы просили. Обычный или одиночный SELECT означает, что столбцы имеют точно такое же отношение к идентификатору строки (Student_ID). Вы пытались получить все это в одном SELECT, не понимая, что каждый столбец в электронной таблице имеет дискретное и различное отношение к идентификатору строки.

Кроме того, вы усложняли дело (запутывая себя), используя GROUP BY, что, конечно, влияет на весь SELECT (поскольку у вас был только один): GROUP BY требуется для AVG(), но не для внешнего запроса.

Решение

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

Во-вторых, вам нужен коррелированный подзапрос, который коррелирует идентификатор строки во внешней сетке (Student_ID), который является внешним запросом, к содержимому столбца, который является внутренним запросом.

• _{This is a typical mistake that developers make, fiddling around with the one SELECT, trying to "make it work", because they do not realise the Problem is in their understanding and approach, that a Subquery is called for.}

Код

SELECT  -- Student_ID,  -- humans don"t use numbers for identifiers
        Last_Name,
        First_Name,
        Course_Code,
        (SELECT  AVG( Marks_Obtained ) -- CS.A
            FROM Enrollment 
            WHERE Student_ID = S_OUTER.Student_ID
                AND Module_Year = "Second" 
            GROUP BY Enrollment.Student_ID
            ) AS Avg_2nd_Year_Marks,
        (SELECT  AVG( Marks_Obtained ) -- CS.B
            FROM Enrollment 
            WHERE Student_ID = S_OUTER.Student_ID
                AND Module_Year = "Third" 
            GROUP BY Enrollment.Student_ID
            ) AS Avg_3rd_Year_Marks,
        (SELECT  AVG( Marks_Obtained ) -- CS.A / 3 * 1
            FROM Enrollment 
            WHERE Student_ID = S_OUTER.Student_ID
                AND Module_Year = "Second" 
            GROUP BY Enrollment.Student_ID
            ) / 3 + 
        (SELECT  AVG( Marks_Obtained ) -- CS.B / 3 * 2
            FROM Enrollment 
            WHERE Student_ID = S_OUTER.Student_ID
                AND Module_Year = "Third" 
            GROUP BY Enrollment.Student_ID
             ) / 3 * 2 AS Overall_Marks
    FROM Students S_OUTER 
    WHERE Graduate_Year = "2017"  -- get current year using relevant date function
    ORDER BY Last_Name, First_Name

Объяснение и ответы на вопросы

1. CS означает Сили связанный с ним Сubquery. Корреляция между внешним запросом (псевдонимS_OUTER) и внутренним запросом осуществляется через псевдоним.

2. Продукт подзапроса — это виртуальный (не реальный) столбец, существующий на время выполнения команды. Он вычисляется или генерируется (в отличие от созданный) функцией SELECT. Такому столбцу (отсутствующему в базе данных, сгенерированному на лету) необходимо имя столбца, например следующее. Он появится в наборе результатов в вашей клиентской программе:

   • Avg_2nd_Year_Marks
   • Avg_3rd_Year_Marks
   • Overall_Marks

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

4. Код использует стандартные средства ANSI SQL (например, подзапрос) и синтаксис, он будет работать на любой платформе, совместимой с SQL (нет необходимости в CTE и т. д.).

   • Oracle проверяет себя на подзапросах (это не жалоба на SQL).
   • Не знаю, предоставляет ли бесплатное программное обеспечение, такое как postGresNONsql, обычные подзапросы SQL. Хорошо известно, что он несовместим с SQL, вопреки маркетингу и примечанию «соответствие SQL» на каждой странице руководства. Массовое мошенничество. Тот факт, что большинство искателей на этом сайте не видели подзапроса, является показателем того, что его нет в бесплатном программном обеспечении.
   • Если у него нет базового средства подзапроса SQL, есть способ использовать пытки кода подзапроса во встроенных представлениях, а затем забить его с помощью GROUP BY, чтобы получить аналогичный результат. Очень неэффективно, но ведь «платформа» — это не платформа, это ни в коем случае не сервер, она уже очень неэффективна и медленна.

Should I create a column in the table with the name Avg_3rd_Year_Marks

Точно нет.

or is there any other code which could solve this matter out

да. Довольно простой подзапрос SQL, как указано в начале этого ответа.

• На данный момент мы обнаружили, что ваша НЕ-SQL НЕ-платформа не распознает = для присвоения имени столбца, вычисляемого или реального. Я изменил синтаксис для использования AS (оба являются стандартным синтаксисом SQL).

• Если это не сработает, это означает, что бесплатное программное обеспечение [по еще одному счету] не является жалобой на SQL, даже для Основ SQL.

would this query be possible if I use module_code and module_year as a primary composite key

• Это не имеет никакого значения для внешнего запроса или подзапросов, потому что на него не ссылаются (среднее значение для всех Module_Years, всех Module_Codes, для Student_ID). Просто имя столбца изменилось.

• Это имеет значение в модели данных и для понимания. Если этот ПК является правильным ПК для Module, то да. Это правильно IFF, данный ModuleCode может быть взят более чем в один Module_Year.

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

Changed course_current_year to course_current_status for it to make more sense.

What I want to do is extract data for students graduating in 2017 i.e. the Course_Current_Status = 'Graduated-2017'.

• Это не имеет смысла. Вы, конечно, не хотите жестко кодировать = "2017" в запросе, вам нужен текущий год. Так что получите это из любой функции, которую postGresNONsql имеет для получения текущей даты, и возьмите только год.

• Похоже, вам нужен выпускной год в студенческом. Не "актуальный". В запросе вам нужны студенты, которые заканчивают обучение в текущем году. Я изменил модель данных и код соответственно.

Примечание

Чтобы понять ваш вопрос, мне пришлось отформатировать ваш текст и построить модель данных. Если вам интересно: Модель данных Хади. Модель данных обновляется в соответствии с изменениями и комментариями.

Кстати, именование в ваших таблицах лучше, чем у большинства, но все же немного сумасшедшее.

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

Одна вещь, о которой следует помнить при делении, заключается в том, что PostgreSQL применяет так называемое целочисленное деление, при котором десятичные дроби проглатываются, например. 3 / 2 = 1. Один из способов обойти это, например, умножить на 1,0.

select
  s.student_id, 
  e.average_second_year_marks, 
  e.average_third_year_marks, 
  e.overall_marks
from students s
join
(
  select student_id,
    avg(case when program_year_when_enrolled = 'Second' then marks_obtained end)
      as average_second_year_marks,
    avg(case when program_year_when_enrolled = 'Third' then marks_obtained end)
      as average_third_year_marks,
    (
     (sum(case when program_year_when_enrolled = 'Second' then marks_obtained end) * 1.0) +
     (sum(case when program_year_when_enrolled = 'Third' then marks_obtained end) * 2.0)
    ) / 3.0 as overall_marks
  from enrollment
  group by student_id
) e on e.student_id = s.student_id
where s.course_current_status = 'Graduated-2017';