Всем привет. Заранее прошу пардон: тут очень длинное предисловие, но без него будет просто непонятен вопрос.
Есть большой список электронных контрольных работ, сформирован один раз и НЕ пополняется.
Он делится на "пачки" разного размера и они передаются проверяющему автомату с интервалом в 5 минут. Например, в момент времени=100 передаётся на проверку 4 работы, в момент времени=105 - 7 работ и т.п. Каждая работа имеет "штамп" времени (dts_stamp), когда она в первый раз была подана на проверку. Кроме того, она имеет id автора (person_id), который присваивается студентам в хронологическом порядке.
Автомат определяет одну из работ пачки как "лучшую" и начинает выполнять с ней другие манипуляции (например, подсчет слов и т.п.), которые длятся ровно 5 минут.
Остальные работы "заворачиваются" и через эти самые 5 минут снова идут "в горнило" автомата, но уже в составе НОВОЙ пачки, т.е. вместе с ними - еще и другие работы. Так длится до тех пор, пока автомат не прожует все работы списка. Хочу обратить внимание, что на каждой итерации список получается разным, в нём есть как "старые", так и "новые" работы, т.е. выбор лучшая работа определяется как из повторно проверяемых "старых", так и из "новых".
Критерий отбора "лучшей" работы из пачки:
1) в ней содержится меньше всего ошибок (faults_qty)
2) при равенстве числа ошибок в >=2 работах выбирается та, которая начинала проверяться раньше, т.е. имеет меньший dts_stamp
3) при равенстве по пп 1 и 2 - та, у которой меньший person_id.
Поскольку сам список с момента первой проверки уже НЕ пополняется, то когда-то наступит момент, при котором НОВЫЕ работы поступать уже не будут, т.е. будут только перепроверяться ранее выполнявшиеся. В результате список будет весь переработан.
ТРЕБУЕТСЯ: для каждого person_id, по имеющимся данным о числе ошибок в его работе (faults_qty) и моменту начала её первой проверки (dts_stamp) определить, когда автомат "заберёт" её как "лучшую".
Мы тут с коллегами поспорили: можно ли эту задачу решить одним селектом (видимо, рекурсивным) ? Вроде, всё должно получиться: определяем anchor-часть как список работ, поступивших на проверку в первой пачке (в примере - отметка времени 100). В этой части находим одну, самую "лучшую", и пишем её person_id в поле passed_Ok. Остальные - в поле delay2next (типа, "до следующей проверки"). Это - первая проверка, поле step=1.
Затем делаем UNION ALL и соединяем общий список (p) с рекурсивной СТЕ (cx) так, чтобы на вторую проверку (step=2) попали:
1) те работы, которые не прошли в первой проверке: p.person_id=cx.delay2next
2) новые работы, которые не были в первой пачке и появились только сейчас:
or cx.passed_Ok>0 and (p.dts_stamp-p.dts_init)/5=cx.step
/*условие cx.passed_Ok>0 означает соединение только с одной записью, чтобы не было дублей строк, условие (p.dts_stamp-p.dts_init)/5=cx.step означает отбор только тех, которые пришли на проверку через 5 минут*/
Так вот: попытки рекурсивного решения привели к "открытию": одно обращение к рекурсивному member'у приводит к тому, что данные, получаемые "внутри" него, добавляются к СТЕ как-то странно. Часть строк выдается "на гора" одна-за-одной, а другая часть - "все скопом". Хотя это ОДИН шаг, ОДНО обращение к рекурсивному члену. В результате, результат выборки, которая указана после UNION ALL, не даёт возможность найти в нём "лучшую" запись.
Вот скрипт:
if object_id('tempdb..#t') is not null drop table tempdb..#t
go
create table #t(person_id int, dts_stamp int, faults_qty int, dts_init int)
insert #t values(9761,100,125,100)
insert #t values(9805,100,125, 100)
insert #t values(9888,100,125, 100)
insert #t values(9890,100,125, 100)
insert #t values(9795,105,114, 100)
insert #t values(9799,105,114, 100)
insert #t values(9803,105,125, 100)
insert #t values(9823,105,124, 100)
insert #t values(9833,105,123, 100)
insert #t values(9843,105,122, 100)
insert #t values(9853,105,121, 100)
insert #t values(9802,110,125, 100)
insert #t values(9790,110,112, 100)
insert #t values(9794,110,114, 100)
insert #t values(9798,115,115, 100)
insert #t values(9800,115,115, 100)
go
--select * from #t
WITH
cteTest as
(
select person_id
,dts_stamp
,faults_qty
,passed_Ok=
case when not exists(select * from #t pz
where pz.dts_stamp=p.dts_stamp and
(pz.faults_qty<p.faults_qty or
pz.faults_qty=p.faults_qty and pz.person_id<p.person_id
)
)
then person_id
end
,delay2next=
case when exists(select * from #t pz
where pz.dts_stamp=p.dts_stamp and
(pz.faults_qty<p.faults_qty or
pz.faults_qty=p.faults_qty and pz.person_id<p.person_id
)
)
then person_id
end
,step=1
,r_debug=0
,q_debug=0
from #t p where not exists(select * from #t px
where px.dts_stamp<p.dts_stamp or
px.dts_stamp=p.dts_stamp and px.faults_qty<p.faults_qty
)
UNION ALL
select p.person_id
,p.dts_stamp
,p.faults_qty
,passed_Ok=
case when 1=row_number()over
(partition by cx.step+1 order by p.faults_qty,p.dts_stamp,p.person_id)
then p.person_id
end
,delay2next=
case when 1<row_number()over
(partition by cx.step+1 order by p.faults_qty,p.dts_stamp,p.person_id)
then p.person_id
end
,step=cx.step+1
,r_debug=cast(row_number()over
(partition by cx.step+1 order by p.faults_qty,p.dts_stamp,p.person_id)
as int
)
,q_debug=count(*)over()
from #t p
join cteTest cx
on
p.person_id=cx.delay2next /*работы, не прошедшие в предыдущий раз*/
or
cx.passed_Ok>0 and (p.dts_stamp-p.dts_init)/5=cx.step /*работы, поступившие сейчас в первый раз*/
)
select * from cteTest order by stepВ результате его выполнения будет 70 строк, вот данные только для step=1 и step=2:
| person_id | dts_stamp | faults_qty | passed_Ok | delay2next | step | r_debug | q_debug | | 9761 | 100 | 125 | 9761 | NULL | 1 | 0 | 0 | | 9805 | 100 | 125 | NULL | 9805 | 1 | 0 | 0 | | 9888 | 100 | 125 | NULL | 9888 | 1 | 0 | 0 | | 9890 | 100 | 125 | NULL | 9890 | 1 | 0 | 0 | | 9890 | 100 | 125 | 9890 | NULL | 2 | 1 | 1 | | 9888 | 100 | 125 | 9888 | NULL | 2 | 1 | 1 | | 9805 | 100 | 125 | 9805 | NULL | 2 | 1 | 1 | | 9795 | 105 | 114 | 9795 | NULL | 2 | 1 | 7 | | 9799 | 105 | 114 | NULL | 9799 | 2 | 2 | 7 | | 9853 | 105 | 121 | NULL | 9853 | 2 | 3 | 7 | | 9843 | 105 | 122 | NULL | 9843 | 2 | 4 | 7 | | 9833 | 105 | 123 | NULL | 9833 | 2 | 5 | 7 | | 9823 | 105 | 124 | NULL | 9823 | 2 | 6 | 7 | | 9803 | 105 | 125 | NULL | 9803 | 2 | 7 | 7 |
|
Видно, что на первом шаге (step=1) всё Ок: прошла 1 работа, 9761, а остальные (9805, 9888 и 9890) вернулись в общий список. Эти три работы ПЛЮС еще 7 других будут снова отданы автомату на проверку на втором шаге. То есть, на втором шаге автомат должен будет выбрать "лучшую" из ДЕСЯТИ работ. Но таблица показывает, что на самом деле в СТЕ сначала добавляются те самые три, которые были на первом шаге, и только затем туда добавляются 7. Да, при step=2 добавились действительно 10 строк, но они добавились... вроде как не за один присест, а за два ?!
Автомат считает "лучшими" для step=2 не одну, а сразу 4 работы: три с прошлого прохода, и одну (9795) из семи новых.Получается, для определения "лучшей" из добавленных строк нельзя применить оконную функцию типа row_number()over(partition by step order by faults_qty, dts_stamp, person_id). Она проставит напротив каждой значения, указанные в графе r_debug, т.е. как будто бы добавления шли порознь.
Нельзя также применять группировку - синтаксис не позволяет. Нельзя также определять через where not exists(select * from ctetest cx2 where ...) - вылезет ошибка многократной ссылки на СТЕ внутри неё самой.
Итак, у меня вопрос:
действительно ли может так оказаться, что за ОДНО обращение к рекурсивной части СУБД будет дополнять СТЕ строками НЕ за один "присест", а за несколько ?Спасибо всем, кто дочитал. А тем, кто ответит по существу - большое спасибо! :-)