Блог

У меня возникли проблемы с выполнением вычислений в одной таблице с использованием условных операторов. У меня есть таблица ‘df’ со следующими именами столбцов:

• идентификатор — int
• время — временная метка
• корректность — логическое значение
• тема — текст

Каждый студент (id) выполняет задания по определенному предмету (subject). Система присваивает значение «True» в столбце «правильность», если назначение выполнено правильно, и «False», если нет. Время (time), когда учащийся завершает задание, также сохраняется системой.

Мне нужно написать оптимальный SQL-запрос, который подсчитывает всех студентов, успешно выполнивших 20 заданий в течение часа в марте 2020 года.

Заранее спасибо!

Вы можете сделать это без подзапросов, используя:

 select distinct s.id
from t
where t.timestamp >= '2020-03-01' and t.timestamp < '2020-04-01'
group by s.id, date_trunc('hour', timestamp)
having count(*) >= 20;
  

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

Для повышения производительности требуется индекс на (timestamp) .

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

Это означает, что вы должны внутренне объединить задачу с самой собой, а затем посчитать их.

 select distinct on(tasks.id) tasks.id, tasks.time, sum(previous_tasks.id)
from tasks
inner join tasks previous_tasks
    on tasks.id = previous_tasks.id
         and (previous_tasks.time - tasks.time) < interval '1 hour'
         and previous_tasks.correctness
         and tasks.time >= '2020-03-01' and tasks.time < '2020-04-01'
         and previous_tasks.time >= '2020-03-01' and previous_tasks.time < '2020-04-01'
group by 1, 2
having sum(previous_tasks.id) >= 20