Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
VictorChuff Member Откуда: Сообщений: 4 |
id cash time Code 1 300 10.14.00 1 2 -450 10.16.00 2 3 100 10.21.00 1 4 200 10.22.00 1 5 555 10.26.00 1 6 -848 10.30.00 2 7 80 10.32.00 1 8 400 10.36.00 1 9 368 10.38.00 1 10 200 10.39.00 1 В упрощенном виде задача следующая: надо выбрать из такой таблицы только те операции с кодом 2, если за ними в течении 10 минут не было операций с кодом 1, с общей суммой нарастающим итогом равной по модулю сумме операции с кодом 2. То есть в данном случае запрос должен выдать только операцию с id=2, т.к. в последующие 10 минут нет такой последовательности операций с кодом 1, общая сумма которых равна сумме этой операции. А вот операцию с id=6 надо отбросить, т.к. сумма первых трех операций после нее с кодом 1 (id=7, 8, 9) точно равна сумме этой операции по модулю (80+400+368=848). Важный момент - в 10-минутном интервале могут быть еще операции с кодом 1 (в данном кейсе – это id=10), но их уже в расчет брать не надо, если сумма предыдущих операций нарастающим итогом оказалась равна искомой операции. Время операций фиксируется с точностью до одной минуты. Пытался решить эту задачу через запрос с условием NOT EXISTS, в котором в цикле WHILE подсчитывал сумму операций нарастающим итогом (с выходом из цикла по BREAK при равенстве суммы, или по окончании 10 минутного интервала), но оказалось, что внутри SELECT циклы не работают. Подскажите пожалуйста, какую еще конструкцию можно использовать для решения такой задачи? |
7 сен 19, 12:20 [21965905] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9683 |
declare @t table (id int, cash numeric(18,2), t datetime, Code int); insert into @t values (1, 300, '10:14:00', 1), (2, -450, '10:16:00', 2), (3, 100, '10:21:00', 1), (4, 200, '10:22:00', 1), (5, 555, '10:26:00', 1), (6, -848, '10:30:00', 2), (7, 80, '10:32:00', 1), (8, 400, '10:36:00', 1), (9, 368, '10:38:00', 1), (10, 200, '10:39:00', 1); with c2 as ( select id, cash, t, Code, lead(t, 1, '99991231') over (order by t) as t_next from @t where code = 2 ) select a.id, a.cash, a.t, a.Code from c2 a outer apply ( select 1 from (select sum(cash) over (order by t) from @t where code = 1 and t between a.t and dateadd(mi, 10, a.t) and t < a.t_next) c1(s) where c1.s = abs(a.cash) ) b(flag) where b.flag is null; |
7 сен 19, 12:43 [21965913] Ответить | Цитировать Сообщить модератору |
3unknown Member Откуда: New York Сообщений: 140 |
declare @t table (id int, cash numeric(18,2), t datetime, Code int); insert into @t values (1, 300, '10:14:00', 1), (2, -450, '10:16:00', 2), (3, 100, '10:21:00', 1), (4, 200, '10:22:00', 1), (5, 555, '10:26:00', 1), (6, -848, '10:30:00', 2), (7, 80, '10:32:00', 1), (8, 400, '10:36:00', 1), (9, 368, '10:38:00', 1), (10, 200, '10:39:00', 1); with a as( select* ,sum(case code when 1 then 0 else 1 end) over(order by id) as cum from @t ), b as( select * ,sum(cash) over(partition by cum order by id) as c from a ), d as( select cum,min(t) as mt,case when min(abs(c))<> 0 then 1 else 0 end flag from b group by cum ) select id,cash,t,code from b where cum in(select cum from d where flag = 1) and code = 2 union select id,cash,t,code from b where cum in( select b.cum from b join d on d.cum = b.cum and flag = 0 and c = 0 and datediff(MINUTE,d.mt,t)>10 ) and code = 2 |
7 сен 19, 18:54 [21966055] Ответить | Цитировать Сообщить модератору |
VictorChuff Member Откуда: Сообщений: 4 |
invm, Спасибо большое, красивое решение - помогло наполовину решить задачу! К сожалению пока не смог с его помощью решить вторую половину этой задачи - надо очистить выгрузку не только от операций с кодом 2, если ПОСЛЕ них следовали операции с кодом 1 на такую же сумму, но и от операций с кодом 2, если ПЕРЕД ними в течении 10 минут были операция/операции с кодом 1 на такую же сумму. |
9 сен 19, 14:56 [21966921] Ответить | Цитировать Сообщить модератору |
Akina Member Откуда: Зеленоград, Москва, Россия Сообщений: 21042 |
В общем случае задача нерешаема.
|
|||||||||||||||||||||
9 сен 19, 15:02 [21966932] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9683 |
VictorChuff, Второй подзапрос нужен. Зеркальный. with c2 as ( select id, cash, t, Code, lead(t, 1, '99991231') over (order by t) as t_next, lag(t, 1, '17530101') over (order by t) as t_prev from @t where code = 2 ) select a.id, a.cash, a.t, a.Code from c2 a outer apply ( select 1 from (select sum(cash) over (order by t) from @t where code = 1 and t between a.t and dateadd(mi, 10, a.t) and t < a.t_next) c1(s) where c1.s = abs(a.cash) ) b(flag) outer apply ( select 1 from (select sum(cash) over (order by t) from @t where code = 1 and t between dateadd(mi, -10, a.t) and a.t and t > a.t_prev) c1(s) where c1.s = abs(a.cash) ) c(flag) where b.flag is null and c.flag is null; |
9 сен 19, 15:09 [21966941] Ответить | Цитировать Сообщить модератору |
VictorChuff Member Откуда: Сообщений: 4 |
invm, еще раб большое спасибо - честно говоря не знал про существование такой полезной функции Lag, как впрочем и функции LEAD до ваших ответов! |
9 сен 19, 15:29 [21966963] Ответить | Цитировать Сообщить модератору |
VictorChuff Member Откуда: Сообщений: 4 |
3unknown, спасибо большое - тоже интересное решение! Будет время постараюсь сравнить его с предыдущим решением по производительности на релаьных данных. |
9 сен 19, 15:31 [21966967] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |