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

Откуда:
Сообщений: 61
Коллеги, подскажите пожалуйста как решить следующую задачу
Имеется таблица:
create table #t(dt datetime, val float, rep int)

insert #t(dt, val) values('2009-08-12 01:00:00',800.000)
insert #t(dt, val) values('2009-08-12 02:00:00',600.000)
insert #t(dt, val) values('2009-08-12 03:00:00',600.000)
insert #t(dt, val) values('2009-08-12 04:00:00',600.000)
insert #t(dt, val) values('2009-08-12 05:00:00',600.000)
insert #t(dt, val) values('2009-08-12 06:00:00',787.619)
insert #t(dt, val) values('2009-08-12 07:00:00',800.000)
insert #t(dt, val) values('2009-08-12 08:00:00',800.000)
insert #t(dt, val) values('2009-08-12 09:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 10:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 11:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 12:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 13:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 14:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 15:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 16:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 17:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 18:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 19:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 20:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 21:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 22:00:00',1200.000)
insert #t(dt, val) values('2009-08-12 23:00:00',1200.000)
insert #t(dt, val) values('2009-08-13 00:00:00',1200.000)
dtvalrep
2009-08-12 01:00:00800.000
2009-08-12 02:00:00600.000
2009-08-12 03:00:00600.000
2009-08-12 04:00:00600.000
2009-08-12 05:00:00600.000
2009-08-12 06:00:00787.619
2009-08-12 07:00:00800.000
2009-08-12 08:00:00800.000
2009-08-12 09:00:001200.000
2009-08-12 10:00:001200.000
2009-08-12 11:00:001200.000
2009-08-12 12:00:001200.000
2009-08-12 13:00:001200.000
2009-08-12 14:00:001200.000
2009-08-12 15:00:001200.000
2009-08-12 16:00:001200.000
2009-08-12 17:00:001200.000
2009-08-12 18:00:001200.000
2009-08-12 19:00:001200.000
2009-08-12 20:00:001200.000
2009-08-12 21:00:001200.000
2009-08-12 22:00:001200.000
2009-08-12 23:00:001200.000
2009-08-13 00:00:001200.000

Нужно в поле "rep" записать "1", если значение в поле "val" в предыдущей и следующей записях одинаковы относительно текущего. В остальных случаях в "rep" проставить "0".
Т.е. необходимо получить таблицу следующего вида:
dtvalrep
2009-08-12 01:00:00800.0000
2009-08-12 02:00:00600.0000
2009-08-12 03:00:00600.0001
2009-08-12 04:00:00600.0001
2009-08-12 05:00:00600.0000
2009-08-12 06:00:00787.6190
2009-08-12 07:00:00800.0000
2009-08-12 08:00:00800.0000
2009-08-12 09:00:001200.0000
2009-08-12 10:00:001200.0001
2009-08-12 11:00:001200.0001
2009-08-12 12:00:001200.0001
2009-08-12 13:00:001200.0001
2009-08-12 14:00:001200.0001
2009-08-12 15:00:001200.0001
2009-08-12 16:00:001200.0001
2009-08-12 17:00:001200.0001
2009-08-12 18:00:001200.0001
2009-08-12 19:00:001200.0001
2009-08-12 20:00:001200.0001
2009-08-12 21:00:001200.0001
2009-08-12 22:00:001200.0001
2009-08-12 23:00:001200.0001
2009-08-13 00:00:001200.0000

Естественно всё это желательно сделать без использования курсоров.
Я пробовал сделать что-то на основе "нарастающего итога", но что-то всё-таки не дошло.
12 ноя 09, 11:16    [7918732]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторов значений  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
WITH CTE(N,GN,dt,val) AS(SELECT ROW_NUMBER()OVER(ORDER BY dt), ROW_NUMBER()OVER(PARTITION BY val ORDER BY dt),dt,val FROM #t)
SELECT dt,val, CASE N WHEN MIN(N)OVER(PARTITION BY N-GN,val) THEN 0 WHEN MAX(N)OVER(PARTITION BY N-GN) THEN 0 ELSE 1 END rep
FROM CTE ORDER BY dt;
12 ноя 09, 11:32    [7918863]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторов значений  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Поправка:
iap
WITH CTE(N,GN,dt,val) AS(SELECT ROW_NUMBER()OVER(ORDER BY dt), ROW_NUMBER()OVER(PARTITION BY val ORDER BY dt),dt,val FROM #t)
SELECT dt,val, CASE N WHEN MIN(N)OVER(PARTITION BY N-GN,val) THEN 0 WHEN MAX(N)OVER(PARTITION BY N-GN,val) THEN 0 ELSE 1 END rep
FROM CTE ORDER BY dt;
12 ноя 09, 11:35    [7918890]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторов значений  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
select
        t1.dt
        ,t1.val
        ,case when t2.val is not null and t3.val is not null then 1 else 0 end as rep
    from
        #t t1
        left join #t t2 on t2.val=t1.val
				and t2.dt=dateadd(hh,-1,t1.dt)
        left join #t t3 on t3.val=t1.val
				and t3.dt=dateadd(hh,1,t1.dt)
--------------------------------------------------------------
Дьявол кроется в деталях.
12 ноя 09, 11:43    [7918971]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторов значений  [new]
Albert71
Member

Откуда:
Сообщений: 61
Дедушка
select
        t1.dt
        ,t1.val
        ,case when t2.val is not null and t3.val is not null then 1 else 0 end as rep
    from
        #t t1
        left join #t t2 on t2.val=t1.val
				and t2.dt=dateadd(hh,-1,t1.dt)
        left join #t t3 on t3.val=t1.val
				and t3.dt=dateadd(hh,1,t1.dt)

Небольшая уточнение: изменение даты может быть не всегда равно часу. В таблице записи не обязательно идут через равный интервал времени.
Не совсем удачный пример в первом посте.
12 ноя 09, 11:49    [7919016]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторов значений  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Albert71
Дедушка
select
        t1.dt
        ,t1.val
        ,case when t2.val is not null and t3.val is not null then 1 else 0 end as rep
    from
        #t t1
        left join #t t2 on t2.val=t1.val
				and t2.dt=dateadd(hh,-1,t1.dt)
        left join #t t3 on t3.val=t1.val
				and t3.dt=dateadd(hh,1,t1.dt)

Небольшая уточнение: изменение даты может быть не всегда равно часу. В таблице записи не обязательно идут через равный интервал времени.
Не совсем удачный пример в первом посте.
Лучше бы рассказали про версию сервера.
12 ноя 09, 11:51    [7919036]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторов значений  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
Albert71
Дедушка
select
        t1.dt
        ,t1.val
        ,case when t2.val is not null and t3.val is not null then 1 else 0 end as rep
    from
        #t t1
        left join #t t2 on t2.val=t1.val
				and t2.dt=dateadd(hh,-1,t1.dt)
        left join #t t3 on t3.val=t1.val
				and t3.dt=dateadd(hh,1,t1.dt)

Небольшая уточнение: изменение даты может быть не всегда равно часу. В таблице записи не обязательно идут через равный интервал времени.
Не совсем удачный пример в первом посте.

Если вы вводите некий порядок (предыдущая, последующая) то у вас должен быть принцип сортировки. Что мешает ввести автоинкремент и заменить условия соединения по дата-часу на автоинкремент (+\-)?
12 ноя 09, 11:58    [7919108]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторов значений  [new]
Albert71
Member

Откуда:
Сообщений: 61
iap
Лучше бы рассказали про версию сервера.
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
12 ноя 09, 12:05    [7919179]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторов значений  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Albert71
iap
Лучше бы рассказали про версию сервера.
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Моё решение даёт правильный результат?
12 ноя 09, 12:07    [7919202]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторов значений  [new]
Albert71
Member

Откуда:
Сообщений: 61
iap
Моё решение даёт правильный результат?
Ваше решение даёт "Incorrect syntax near 'CTE'."
12 ноя 09, 14:13    [7920544]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторов значений  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Albert71
iap
Моё решение даёт правильный результат?
Ваше решение даёт "Incorrect syntax near 'CTE'."
Это всё сообщение или там что-нибудь про точку с запятой?
12 ноя 09, 14:15    [7920557]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить