Получение данных с помощью одного быстрого SQL-запроса

SELECT student_id, MAX(ExamEntry)
FROM data
WHERE Grade > :threshold
GROUP BY student_id

Как это?

Если ваш запрос очень медленный при 1000 записей в вашей таблице, что-то не так. Для современной системы баз данных таблица, содержащая 1000 записей, считается очень маленькой. Скорее всего, вы не предоставили (первичный) ключ для своей таблицы?

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

SELECT 
  Student_ID
, MAX(Grade) AS maxGrade
FROM table_name
GROUP BY Student_ID
HAVING maxGrade > MINIMUM_GRADE_NEEDED

Если вам действительно нужно, чтобы последняя оценка была выше минимальной:

SELECT 
  Student_ID
, Grade
FROM table_name
WHERE ExamEntry IN ( 
    SELECT 
      MAX(ExamEntry) 
    FROM table_name 
    GROUP BY Student_ID
)
HAVING Grade > MINIMUM_GRADE_NEEDED

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

select s.id, s.name, t.name, max(r.score)
from student s
left outer join test_result r on r.student_id = s.id
left outer join test t on r.test_id = t.id
group by s.id, s.name, t.name

Все поля с id должны быть проиндексированы.

Если у вас действительно есть только один тест (тип) в вашем домене ... тогда запрос будет

select s.id, s.name, max(r.score)
from student s
left outer join test_result r on r.student_id = s.id
group by s.id, s.name

Как уже упоминалось, индексирование - мощный инструмент для ускорения запросов. Однако порядок индекса принципиально важен.

Индекс в порядке (ExamEntry), затем (Student_ID), затем (Grade) будет почти бесполезным для поиска экзаменов, на которых студент сдал.

Указатель в обратном порядке подошел бы идеально, если бы все, что вам нужно, - это узнать, какие экзамены были сданы. Это позволит механизму запросов быстро определять строки для сданных экзаменов и просто обрабатывать их.

В MS SQL Server это можно сделать с помощью ...

CREATE INDEX [IX_results] ON [dbo].[results] 
(
    [Grade],
    [Student_ID],
    [ExamEntry]
)
ON [PRIMARY]

(Я рекомендую прочитать больше об индексах, чтобы узнать, какие еще есть варианты, такие как ClusterdIndexes и т. д., И т. Д.)

С этим индексом следующий запрос сможет очень быстро игнорировать «неудавшиеся» экзамены и просто отображать студентов, которые когда-либо сдавали экзамен ...

(Предполагается, что если вам когда-либо будет больше 60, вы засчитываете экзамен, даже если впоследствии вы снова сдадите экзамен и получите 27).

SELECT
    Student_ID
FROM
    [results]
WHERE
    Grade >= 60
GROUP BY
    Student_ID

Если вам определенно нужно самое последнее значение, вам нужно изменить порядок индекса на что-то вроде ...

CREATE INDEX [IX_results] ON [dbo].[results] 
(
    [Student_ID],
    [ExamEntry],
    [Grade]
)
ON [PRIMARY]

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

SELECT
   *
FROM
   [results]
WHERE
   [results].ExamEntry = (
                          SELECT
                              MAX([student_results].ExamEntry)
                          FROM
                              [results] AS [student_results]
                          WHERE
                              [student_results].Student_ID = [results].student_id
                         )
   AND [results].Grade > 60

Подобный подзапрос может показаться медленным, особенно потому, что он выполняется для каждой строки в [results].

Однако это не так ...
- И основной, и дополнительный запрос ссылаются на одну и ту же таблицу - Механизм запросов просматривает индекс для каждого уникального Student_ID
. - Подзапрос выполняется, для этого Student_ID
- Механизм запросов уже находится в этой части index
- Таким образом, новый поиск по индексу не требуется

Обновлено:

Было сделано замечание, что при 1000 записях индексы не актуальны. Следует отметить, что в вопросе указано, что было возвращено 1000 записей, а не то, что таблица содержит 1000 записей. Чтобы базовый запрос занимал столько времени, сколько указано, я готов поспорить, что в таблице гораздо больше, чем 1000 записей. Может это можно прояснить?

Обновлено:

Я только что исследовал 3 запроса, по 999 записей в каждом (3 результата экзамена для каждого из 333 студентов)

Метод 1: ГДЕ a.ExamEntry = (SELECT MAX (b.ExamEntry) FROM results [a] WHERE a.Student_ID = b.student_id)

Метод 2: ГДЕ a.ExamEntry IN (SELECT MAX (ExamEntry) FROM resuls GROUP BY Student_ID)

Метод 3: ИСПОЛЬЗОВАНИЕ ВНУТРЕННЕГО СОЕДИНЕНИЯ вместо предложения IN

Были найдены следующие моменты времени:

Method    QueryCost(No Index)   QueryCost(WithIndex)
   1               23%                    9%
   2               38%                   46%
   3               38%                   46%

Итак, Query 1 работает быстрее независимо от индексов, но индексы также определенно делают метод 1 значительно быстрее.

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

Спасибо за ответы !!

Я думаю, что Демс, вероятно, ближе всего к тому, что мне нужно, но я немного остановлюсь на этом вопросе.

1. Учитывается только последняя оценка. Если ученик сдал первый раз, посетил снова и проиграл, он проиграл в целом. Он / она мог бы сдать 3 или 4 экзамена, но все равно учитывается только последний.
2. Я использую MySQL сервер. Проблема, с которой я сталкиваюсь при установке как Linux, так и Windows.
3. Мой набор данных сейчас составляет около 2К записей и растет со скоростью ~ 1К за новый экзамен.

4. Запрос для конкретного экзамена также возвращает ~ 1K записей, тогда как ~ 1K будет количеством посещенных студентов (получено SELECT DISTINCT STUDENT_ID из результатов;), тогда почти все сдали, а некоторые не прошли.

5. Я выполняю в своем коде следующий запрос: SELECT ExamEntry, Student_ID из экзаменов WHERE ExamEntry в (SELECT MAX (ExamEntry) из экзаменов GROUP BY Student_ID). Поскольку подзапрос возвращает около 1000 записей, похоже, что основной запрос сканирует их в цикле, в результате чего весь запрос выполняется в течение очень долгого времени и с 50% нагрузкой на сервер (100% в Windows).

6. Я Чувствовать, что есть способ получше :-), просто пока не могу найти.

Я использовал приведенные здесь подсказки, и вот обнаруженный мной запрос, который выполняется почти на 3 порядка быстрее, чем мой первый (0,03 секунды вместо 10 секунд):

SELECT ExamEntry, Student_ID, Grade from data,
       ( SELECT max(ExamEntry) as ExId GROUP BY Student_ID) as newdata
WHERE `data`.`ExamEntry`=`newdata`.`ExId` AND Grade > 60;

Спасибо всем!

select examentry,student_id,grade 
from data 
where examentry in 
  (select max(examentry) 
   from data 
   where grade > 60 
   group by student_id)

не использовать

where grade > 60

но

where grade between 60 and 100

это должно идти быстрее