Дано: база — sqlite3 (более-менее стандартный sql, вложенные запросы поддерживает, из джойнов — только inner и left outer).

Структура таблиц (та часть, которая относится к делу):

questions
  id

sessions
  id

question2session
  question_id
  session_id

(есть вопросы для тестов, есть "тестовые сессии", в question2session — отношение "на какие вопросы отвечал студент проходя данную сессию")

choices
  id
  question_id
  is_right

варианты ответа на вопрос. вариантов может быть сколько угодно (2 и больше), из них любое количество может быть верными (is_right).

answers
  session_id
  question_id
  choice_id

ответы студента. если ответ с данным choice_id присутствует в базе, значит студент выбрал этот вариант из вариантов ответа.

ВНИМАНИЕ, ВОПРОС: сформировать запрос "количество правильных ответов в данной сессии". т.е. количество таких вопросов, для которых набор "правильные ответы на данный вопрос" (select id from choices where question_id = this_question and is_right = true) точно совпал с набором "ответы студента на данный вопрос" (select choice_id from answers where question_id = this_question and session_id = this_session).

на данный момент делается глупо: для каждого вопроса в сессии (select question_id from questions join question2session where question2session.session_id = this_session) выполняются описанные ранее запросы (все правильные ответы, все ответы студента) и результаты сравниваются как массивы. Скорость чудовищна.

соответственно, ищется один из вариантов ответа:
* вот единственный запрос, который это делает
* вот так надо изменить структуру базы; и тогда вот единственный запрос, который это сделает
* и др. варианты ускорения (добавь индексы, используй вот эти 2, 3, 4 запроса — в общем, практически любые идеи)

спасибо!

Здравствуйте, Аноним, Вы писали:

А>соответственно, ищется один из вариантов ответа:
А>* вот единственный запрос, который это делает
А>* вот так надо изменить структуру базы; и тогда вот единственный запрос, который это сделает
А>* и др. варианты ускорения (добавь индексы, используй вот эти 2, 3, 4 запроса — в общем, практически любые идеи)

Для начала, если поле в ответах, отвечающее за студента? (просто спросил, ничего личного)

select count(id)
from choices
where is_right = true and question_id = @QID

select count(choice_id)
from answers
where question_id = @QID
and choise_id in (select id
from choices
where is_right = true and question_id = @QID)

Далее сравниваются два числа и пожалуйста. т.е. по массиву надо пробежаться всего count(question_id) раз, вызовов sql будет count(question_id)*2.

сессию добавить во все подзапросы надо будет

Поддерживаются ли хранимки и временные таблицы?


А>спасибо!

не за что.

Здравствуйте, Аноним, Вы писали:

А>...

А>соответственно, ищется один из вариантов ответа:
А>* вот единственный запрос, который это делает
А>...


Что-то типа

select 
    session_id, 
    question_id
from 
    answers  
join choices 
    on answers.choice_id = choices.id 
where 
    choices.is_right = 'y' 
group by 
    session_id, 
    question_id
having 
    count(*) = (
                  select 
                      count(*) 
                  from 
                      choices 
                  where 
                      is_right = 'y' 
                  group by 
                      question_id
                )

спасибо всем поучаствовавшим! Решение, как водится, подтвердило мое убеждение, что если требуется настолько сложный запрос — что-то не так с моделью данных. Короче говоря, в результате оказалось
* "количество верных ответов в сессии" нужно вычислять для каждой сессии всего 1 раз — по закрытию сессии
* answer должен хранить каков, собственно, был вариант, и был ли он правильным.
(оба изменения — потому что вопросы и ответы могут поменяться после того, как какие-то студенты уже прошли тестирование; и результаты тестирования для этих студентов не должны зависеть от будущих изменений).

В результате, таблица answers приобрела следующий вид:

id,
session_id,
question_id,
choice_id,
choice_text,
choice_value, -- is_right из таблицы choices
selected      -- выбрал ли студент этот вариант

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

Вычисление же количества правильных ответов делается 2 довольно тривиальными запросами (не слишком оптимально, можно, наверное, и в один запрос свести, но учитывая, что это вычисление проводится 1 раз — по завершению студентом тестирования — и так хорошо):

1. найти все вопросы в этой сессии:

select distinct question_id from answers where session_id = @current_session

2. найти все вопросы, на которые неверно отвечено "да" или "нет":

select distinct question_id from answers 
where 
  session_id = @current_session and 
  (choice_value = 't' and selected = 'f'
    or
  choice_value = 'f' and selected = 't')

3. вычесть множество (2) из множества (1)
4. вернуть размер результирующего множества.

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