Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Циклы внутри запроса селект?  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: Циклы внутри запроса селект?  [new]
invm
Member

Откуда: Москва
Сообщений: 8750
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]     Ответить | Цитировать Сообщить модератору
 Re: Циклы внутри запроса селект?  [new]
3unknown
Member

Откуда: New York
Сообщений: 135
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]     Ответить | Цитировать Сообщить модератору
 Re: Циклы внутри запроса селект?  [new]
VictorChuff
Member

Откуда:
Сообщений: 4
invm, Спасибо большое, красивое решение - помогло наполовину решить задачу!
К сожалению пока не смог с его помощью решить вторую половину этой задачи - надо очистить выгрузку не только от операций с кодом 2, если ПОСЛЕ них следовали операции с кодом 1 на такую же сумму, но и от операций с кодом 2, если ПЕРЕД ними в течении 10 минут были операция/операции с кодом 1 на такую же сумму.
9 сен 19, 14:56    [21966921]     Ответить | Цитировать Сообщить модератору
 Re: Циклы внутри запроса селект?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19306
В общем случае задача нерешаема.
VictorChuff
надо очистить выгрузку не только от операций с кодом 2, если ПОСЛЕ них следовали операции с кодом 1 на такую же сумму, но и от операций с кодом 2, если ПЕРЕД ними в течении 10 минут были операция/операции с кодом 1 на такую же сумму.


Время (условно) сумма код
01002
4401
8601
12401
161002
9 сен 19, 15:02    [21966932]     Ответить | Цитировать Сообщить модератору
 Re: Циклы внутри запроса селект?  [new]
invm
Member

Откуда: Москва
Сообщений: 8750
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]     Ответить | Цитировать Сообщить модератору
 Re: Циклы внутри запроса селект?  [new]
VictorChuff
Member

Откуда:
Сообщений: 4
invm, еще раб большое спасибо - честно говоря не знал про существование такой полезной функции Lag, как впрочем и функции LEAD до ваших ответов!
9 сен 19, 15:29    [21966963]     Ответить | Цитировать Сообщить модератору
 Re: Циклы внутри запроса селект?  [new]
VictorChuff
Member

Откуда:
Сообщений: 4
3unknown, спасибо большое - тоже интересное решение! Будет время постараюсь сравнить его
с предыдущим решением по производительности на релаьных данных.
9 сен 19, 15:31    [21966967]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить