Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Циклы внутри запроса селект? / 8 сообщений из 8, страница 1 из 1
07.09.2019, 12:20
    #39858726
VictorChuff
VictorChuff
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Циклы внутри запроса селект?
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 циклы не работают.
Подскажите пожалуйста, какую еще конструкцию можно использовать для решения такой задачи?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2019, 12:43
    #39858733
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Циклы внутри запроса селект?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2019, 18:54
    #39858831
3unknown
3unknown
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Циклы внутри запроса селект?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2019, 14:56
    #39859370
VictorChuff
VictorChuff
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Циклы внутри запроса селект?
invm, Спасибо большое, красивое решение - помогло наполовину решить задачу!
К сожалению пока не смог с его помощью решить вторую половину этой задачи - надо очистить выгрузку не только от операций с кодом 2, если ПОСЛЕ них следовали операции с кодом 1 на такую же сумму, но и от операций с кодом 2, если ПЕРЕД ними в течении 10 минут были операция/операции с кодом 1 на такую же сумму.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2019, 15:02
    #39859376
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Циклы внутри запроса селект?
В общем случае задача нерешаема.
VictorChuffнадо очистить выгрузку не только от операций с кодом 2, если ПОСЛЕ них следовали операции с кодом 1 на такую же сумму, но и от операций с кодом 2, если ПЕРЕД ними в течении 10 минут были операция/операции с кодом 1 на такую же сумму.

Время (условно) сумма код010024401860112401161002
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2019, 15:09
    #39859381
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Циклы внутри запроса селект?
VictorChuff,

Второй подзапрос нужен. Зеркальный.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2019, 15:29
    #39859393
VictorChuff
VictorChuff
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Циклы внутри запроса селект?
invm, еще раб большое спасибо - честно говоря не знал про существование такой полезной функции Lag, как впрочем и функции LEAD до ваших ответов!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2019, 15:31
    #39859396
VictorChuff
VictorChuff
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Циклы внутри запроса селект?
3unknown, спасибо большое - тоже интересное решение! Будет время постараюсь сравнить его
с предыдущим решением по производительности на релаьных данных.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Циклы внутри запроса селект? / 8 сообщений из 8, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]