Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите с запросом  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
Есть таблица с данными:
declare @table table ([Date] date not null,[Value] bit not null)
insert into @table values
('2018-03-10',1),
('2018-03-11',1),
('2018-03-12',0),
('2018-03-13',0),
('2018-03-14',0),
('2018-03-15',1),
('2018-03-16',1),
('2018-03-17',0),
('2018-03-18',1),
('2018-03-19',0),
('2018-03-20',0),
('2018-03-21',0)

Как вывести первую (по [Date] asc) запись, где [Value] = 0, но находится между двух аналогичных записей? Курсором не хотелось бы.
Для приведённых данных, верным решением были бы строки c [Date] '2018-03-13' и '2018-03-20'.
27 мар 18, 21:30    [21291390]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20197
WHERE [Value]=0 AND LEAD([Value])=0 AND LAG([Value])=0
27 мар 18, 22:46    [21291564]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
как вариант (добавил несколько строк в исходные данные):
declare @table table (date date not null, value bit not null)
insert @table
values
	('2018-03-10',1),
	('2018-03-11',1),
	('2018-03-12',0),
	('2018-03-13',0),
	('2018-03-14',0),
	('2018-03-15',1),
	('2018-03-16',1),
	('2018-03-17',0),
	('2018-03-18',1),
	('2018-03-19',0),
	('2018-03-20',0),
	('2018-03-21',0),
	('2018-03-22',0),
	('2018-03-23',1),
	('2018-03-24',0),
	('2018-03-25',0),
	('2018-03-26',1)

;
with
	t1 as
	(
	select
		date, value,
		datediff(dd, '20180101', date) - row_number()over(partition by value order by date) as g
	from
		@table
	where
		value = 0
	),
	t2 as
	(
	select
		date, value,
		count(g)over(partition by g) as cn,
		row_number()over(partition by g order by date) as rn
	from
		t1
	)

select
	date, value
from
	t2
where
	cn > 2 and
	rn = 2
28 мар 18, 00:09    [21291687]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Дедушка,
Я не вижу в задании ограничения, что даты должны быть последовательны...
28 мар 18, 05:03    [21291772]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Дедушка,

Ну последнее условие некорректно (не попадает '2018-03-21'), правильнее:
where rn between 2 and cn-1
28 мар 18, 05:07    [21291774]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
Kopelly
Я не вижу в задании ограничения, что даты должны быть последовательны...
поэтому я и написал "как вариант", точные условия знает ТС ему и решать.

Kopelly
Ну последнее условие некорректно (не попадает '2018-03-21')
=Сергей=
Как вывести первую (по [Date] asc) запись, где [Value] = 0, но находится между двух аналогичных записей
28 мар 18, 09:58    [21292234]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
Зачем leag&lag???
with Q as 

(select X.date, X.bit
   from (values ('2018-03-10',1),
                ('2018-03-11',1),
                ('2018-03-12',0),
                ('2018-03-13',0),
                ('2018-03-14',0),
                ('2018-03-15',1),
                ('2018-03-16',1),
                ('2018-03-17',0),
                ('2018-03-18',1),
                ('2018-03-19',0),
                ('2018-03-20',0),
                ('2018-03-21',0)) as X(date, bit))

select top 1 with ties q.date, q.bit
  from Q as q
 order by 
       max(q.bit) over(order by q.date rows between 1 preceding and 1 following),
          sum(-1) over(order by q.date rows between 1 preceding and 1 following)
28 мар 18, 14:12    [21293207]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
nullin, sqlfiddle
28 мар 18, 14:14    [21293224]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
=Сергей=,
А для такого набора данных, Вы что ожидаете?
('2018-03-10',1),
('2018-03-11',1),
('2018-03-12',0),
('2018-03-12',0),
('2018-03-13',0),
('2018-03-13',0),
('2018-03-14',0),
('2018-03-15',1),

|       date | bit |
|------------|-----|
| 2018-03-12 |   0 |
| 2018-03-13 |   0 |
| 2018-03-13 |   0 |
| 2018-03-20 |   0 |

или чтобы 2018-03-13 была 1 раз, а для 2018-03-12 какую предпочитать?
28 мар 18, 14:21    [21293265]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
nullin,

1. Если нет записей удовлетворяющих условию, то все-равно что-то неверное вернет.
2. Нет отсева по "Как вывести первую (по [Date] asc) запись, где [Value] = 0, но находится между двух аналогичных записей"
28 мар 18, 14:22    [21293270]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
=Сергей=,

WHERE [Value]=0 AND LEAD([Value])=0 AND LAG([Value])=0 AND LEAD([Value],2,1)=1
28 мар 18, 14:27    [21293296]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Kopelly
=Сергей=,

WHERE [Value]=0 AND LEAD([Value])=0 AND LAG([Value])=0 AND LEAD([Value],2,1)=1
окна в предикате уже можно?
28 мар 18, 14:29    [21293308]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
Поправел,
+
with Q as 

(select X.date, X.bit
from (values 
('2018-03-10',1),
('2018-03-11',1),
('2018-03-12',0),
('2018-03-13',0),
('2018-03-14',0),
('2018-03-15',0),
('2018-03-16',0),
('2018-03-17',0),
('2018-03-18',1),
('2018-03-19',0),
('2018-03-20',0),
('2018-03-21',0),
('2018-03-22',1),
('2018-03-23',0),
('2018-03-24',0),
('2018-03-25',0)) as X(date, bit))

select top 1 with ties a.date, a.bit
  from (select top 1 with ties q.date, q.bit,
                     sum(-1) over(order by q.date rows between 1 preceding and unbounded following)
               -row_number() over(partition by bit order by date) as r
          from Q as q
         order by max(q.bit) over(order by q.date rows between 1 preceding and 1 following)) as a
  order by row_number() over(partition by a.r order by a.date)


sqlfiddle
28 мар 18, 14:47    [21293394]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
nullin,

6 сортировок?
в чём цимес?
28 мар 18, 14:54    [21293423]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
Дедушка, если вы про фиддл?
28 мар 18, 14:57    [21293433]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
Друзья, всем огромное спасибо, очень интересные решения!!!
28 мар 18, 14:57    [21293434]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
=Сергей=, ой, бяда, случицца, если все однёрки ТС, не принимай близко к сердцу
28 мар 18, 15:07    [21293474]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
творчество
28 мар 18, 15:07    [21293476]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
+
with Q as 

(select X.date, X.bit
   from (values ('2018-03-01',0),  
                ('2018-03-02',0),
                ('2018-03-03',0),      
                ('2018-03-05',0),      
                ('2018-03-07',0),
                ('2018-03-08',0),      
                ('2018-03-10',1),
                ('2018-03-11',1),
                ('2018-03-13',0),
                ('2018-03-13',0),
                ('2018-03-14',0),
                ('2018-03-15',0),
                ('2018-03-16',0),
                ('2018-03-17',0),
                ('2018-03-18',1),
                ('2018-03-19',0),
                ('2018-03-20',0),
                ('2018-03-20',0),
                ('2018-03-21',0),
                ('2018-03-22',1),
                ('2018-03-23',0),
                ('2018-03-24',0),
                ('2018-03-27',0)) as X(date, bit))
select min(b.date)
  from (select a.date, a.bit, a.s,
               row_number() over(order by a.date) 
             - row_number() over(partition by a.s order by a.date) as r
          from (select q.date, q.bit,
                       sum(1 - q.bit) over(order by q.date rows between 1 preceding and 1 following) as s 
                                                              /*between @preced_Row preceding and @follow_Row*/
                  from Q as q) as a) as b
                 where b.s = 3  /*@preced_Row + @follow_Row + 1*/
 group by b.r

select b.date, b.bit
  from (select a.date, a.bit, a.x,
               lag(a.x, 1, null) over(order by a.date) as lx
          from (select q.date, q.bit,
                       iif(q.bit = 0 and 
                       lead(q.bit, 1, null) over(order by q.date) = 0 and
                        lag(q.bit, 1, null) over(order by q.date) = 0, 'x', '') as x
                  from Q as q) as a) as b
 where b.x = 'x' and b.lx = ''

Пожалуй, разница будет только в том, захочет ли, когда либо ТС увеличить окно от "между двух аналогичных записей" до N записей. И еще неплохо было бы понять, что будет при дублях даты с разными битами.
28 мар 18, 17:09    [21293935]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
Kopelly
Дедушка,
Ну последнее условие некорректно (не попадает '2018-03-21'), правильнее:
where rn between 2 and cn-1

Сломается, и так, на данных из спойлера выше.
Дедушка
... точные условия знает ТС ему и решать.

ТС набросил!
28 мар 18, 22:42    [21294667]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
marsyakupov
Member

Откуда:
Сообщений: 3
Select [Date],[Value]
From @table t1
Where t1.[Value] = 0
and not exists (select * from @table t2 where t2.[Date]=DATEADD (day,1,t1.[Date] ) and t2.[Value]=1)
and not exists (select * from @table t2 where t2.[Date]=DATEADD (day,-1,t1.[Date] ) and t2.[Value]=1)
and exists (select * from @table t2 where t2.[Date]=DATEADD (day,1,t1.[Date] ))
31 мар 18, 15:41    [21301864]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить