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

Откуда:
Сообщений: 20
Есть система с версионостью. Необходимо посчитать количество изменений определенного поля в записи за период времени.

Например.
IdVersionWorker
3200 1 1099
3200 2 1122
3200 3 953
3200 4 1122
3200 5 586
3200 6 848
3200 7 1122
3200 8 1122
3200 9 1122
3200 10 811
3200 11 1122

У этой записи есть 11 разных версий. Интересует сколько раз у этой записи менялось поле Worker.
В данном случае оно менялось только 9 раз, потому что в версиях 7,8,9 поле Worker оставалось неизменным. Как получить такое число изменений?

IdWorker №Изменения
3200 1099 1
3200 1122 2
3200 953 3
3200 1122 4
3200 586 5
3200 848 6
3200 1122 7
3200 1122 7
3200 1122 7
3200 811 8
3200 1122 9
11 авг 09, 12:52    [7521954]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
на основании чего вы определяете порядок следования записей?
11 авг 09, 12:55    [7521988]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
alkersan
Member

Откуда:
Сообщений: 20
Можно по версии. Но есть еще поле "дата изменения"
11 авг 09, 12:55    [7521998]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
-- test data
declare @t table(version int, worker int)
insert into @t(version, worker)
select  1, 1099 union all
select  2, 1122 union all
select  3,  953 union all
select  4, 1122 union all
select  5,  586 union all
select  6,  848 union all
select  7, 1122 union all
select  8, 1122 union all
select  9, 1122 union all
select 10,  811 union all
select 11, 1122
-- end of test data

;with numbered as (
   select version, worker, row_number() over(order by version) as rn
     from @t
), cte as (
   select version, worker, rn, 1 as cn
     from numbered
    where rn = 1
   union all
   select n.version, n.worker, n.rn, c.cn + case when n.worker != c.worker then 1 else 0 end
     from numbered as n
     join cte as c on c.rn + 1 = n.rn
)
select version, worker, cn
  from cte
option(maxrecursion 0)

version     worker      cn
----------- ----------- -----------
1           1099        1
2           1122        2
3           953         3
4           1122        4
5           586         5
6           848         6
7           1122        7
8           1122        7
9           1122        7
10          811         8
11          1122        9

(11 row(s) affected)
11 авг 09, 13:04    [7522104]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SQL2008
USE tempdb;
SET NOCOUNT ON;
IF OBJECT_ID(N'T',N'U') IS NOT NULL DROP TABLE T;
GO
CREATE TABLE T(Id INT, [Version] INT, Worker INT);
GO
INSERT T(Id, [Version], Worker) VALUES
(3200, 1, 1099),
(3200, 2, 1122),
(3200, 3, 953),
(3200, 4, 1122),
(3200, 5, 586),
(3200, 6, 848),
(3200, 7, 1122),
(3200, 8, 1122),
(3200, 9, 1122),
(3200, 10, 811),
(3200, 11, 1122);
GO

WITH
 Numbered AS(SELECT *, ROW_NUMBER()OVER(ORDER BY [Version]) N, ROW_NUMBER()OVER(PARTITION BY Worker ORDER BY [Version]) G FROM T)
,Ranked   AS(SELECT *, MIN(N)OVER(PARTITION BY N-G,Worker) M FROM Numbered)
SELECT COUNT(DISTINCT M)
FROM Ranked;
?
11 авг 09, 13:09    [7522171]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
alkersan
Member

Откуда:
Сообщений: 20
Паганель,
Спасибо за решение.
Забыл уточнить. Можно ли такое сделать для SQL 2000?
11 авг 09, 13:12    [7522195]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
iap, а почему 2008? у меня и на 2005 Ваш вариант работает...
11 авг 09, 13:14    [7522224]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
SQL2008
USE tempdb;
SET NOCOUNT ON;
IF OBJECT_ID(N'T',N'U') IS NOT NULL DROP TABLE T;
GO
CREATE TABLE T(Id INT, [Version] INT, Worker INT);
GO
INSERT T(Id, [Version], Worker) VALUES
(3200, 1, 1099),
(3200, 2, 1122),
(3200, 3, 953),
(3200, 4, 1122),
(3200, 5, 586),
(3200, 6, 848),
(3200, 7, 1122),
(3200, 8, 1122),
(3200, 9, 1122),
(3200, 10, 811),
(3200, 11, 1122);
GO

WITH
 Numbered AS(SELECT *, ROW_NUMBER()OVER(ORDER BY [Version]) N, ROW_NUMBER()OVER(PARTITION BY Worker ORDER BY [Version]) G FROM T)
,Ranked   AS(SELECT *, MIN(N)OVER(PARTITION BY N-G,Worker) M FROM Numbered)
SELECT COUNT(DISTINCT M)
FROM Ranked;
?
Если в стиле Паганеля, но без рекурсии, то
WITH
 Numbered AS(SELECT *, ROW_NUMBER()OVER(ORDER BY [Version]) N, ROW_NUMBER()OVER(PARTITION BY Worker ORDER BY [Version]) G FROM T)
,Ranked   AS(SELECT *, MIN(N)OVER(PARTITION BY N-G,Worker) M FROM Numbered)
SELECT Id, [Version], Worker, DENSE_RANK()OVER(ORDER BY M) cn
FROM Ranked
ORDER BY cn;
11 авг 09, 13:15    [7522235]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
alkersan
Можно ли такое сделать для SQL 2000?
Наверное, придется курсором...
Я не возьмусь, не силён я в них
11 авг 09, 13:15    [7522236]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Паганель
iap, а почему 2008? у меня и на 2005 Ваш вариант работает...
Из-за VALUES(),(),(),...
Лень было UNIONы писать...
11 авг 09, 13:16    [7522257]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
[quot Паганель][quot alkersan]Можно ли такое сделать для SQL 2000б[юquot]Наверное, придется курсором...
Я не возьмусь, не силён я в них [юquot]Почему курсором? COUNT(*)+NOT EXISTS()
Сейчас попробую...
11 авг 09, 13:18    [7522274]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
if object_id('tempdb..#t') is not null drop table #t
create table #t(id int, version int, worker int)

insert #t
select 3200,1,1099
union all
select 3200,2,1122
union all
select 3200,3,953
union all
select 3200,4,1122
union all
select 3200,5,586
union all
select 3200,6,848
union all
select 3200,7,1122
union all
select 3200,8,1122
union all
select 3200,9,1122
union all
select 3200,10,811
union all
select 3200,11,1122
union all
select 3201,1,1099
union all
select 3201,2,1122
union all
select 3201,3,953
union all
select 3201,4,1122
union all
select 3201,5,586
union all
select 3201,6,848
union all
select 3201,7,1122
union all
select 3201,8,1122
union all
select 3201,9,1122
union all
select 3201,10,811
union all
select 3201,11,1122

select
tbl.id,
count(tbl.worker)
from
(
select id, version, worker from  #t
union all
select id, 0 as version, -1 as worker from #t where version=1
) tbl
left join #t t2 on t2.id=tbl.id
		and t2.version=tbl.version+1
		and t2.worker<>tbl.worker
where
t2.worker is not null
group by
tbl.id
tbl.id
11 авг 09, 13:20    [7522297]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Паганель
alkersan
Можно ли такое сделать для SQL 2000?
Наверное, придется курсором...
Хотя чего это я
select *
      ,case when isnull((select top 1 worker
                           from @t as t2
                          where t2.version < t1.version
                          order by version desc)
                        ,0x7FFFFFFF) != worker
            then 'true'
            else 'false' end as changed
  from @t as t1

version     worker      changed
----------- ----------- -------
1           1099        true
2           1122        true
3           953         true
4           1122        true
5           586         true
6           848         true
7           1122        true
8           1122        false
9           1122        false
10          811         true
11          1122        true

(11 row(s) affected)
считайте количество
11 авг 09, 13:21    [7522311]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
alkersan
Member

Откуда:
Сообщений: 20
Спасибо огромное всем.

Я совершенно не владею аналитическими функциями SQL. Надо учить...
11 авг 09, 13:28    [7522395]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
[quot Паганель][quot alkersan]Можно ли такое сделать для SQL 2000б[юquot]Наверное, придется курсором...
Я не возьмусь, не силён я в них [юquot]Почему курсором? COUNT(*)+NOT EXISTS()
Сейчас попробую...
SQL2000
SELECT COUNT(*)
FROM T
WHERE NOT EXISTS(SELECT * FROM T TT WHERE TT.[Version]<T.[Version])
OR T.[Worker]<>(SELECT TOP 1 TT.[Worker] FROM T TT WHERE TT.[Version]<T.[Version] ORDER BY TT.[Version] DESC);
11 авг 09, 13:31    [7522426]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Если Worker не может принимать нулевое значение, то можно проще
SELECT COUNT(*)
FROM T
WHERE T.[Worker]<>ISNULL((SELECT TOP 1 TT.[Worker] FROM T TT WHERE TT.[Version]<T.[Version] ORDER BY TT.[Version] DESC),0);
11 авг 09, 13:43    [7522560]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
iap
Если Worker не может принимать нулевое значение, то можно проще
SELECT COUNT(*)
FROM T
WHERE T.[Worker]<>ISNULL((SELECT TOP 1 TT.[Worker] FROM T TT WHERE TT.[Version]<T.[Version] ORDER BY TT.[Version] DESC),0);

в данном варианте все изменения для разных id сложатся
11 авг 09, 13:47    [7522599]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
pacha
Member

Откуда:
Сообщений: 239
версия для SQL Server 2000
declare @t table(version int, worker int)
insert into @t(version, worker)
select  1, 1099 union all
select  2, 1122 union all
select  3,  953 union all
select  4, 1122 union all
select  5,  586 union all
select  6,  848 union all
select  7, 1122 union all
select  8, 1122 union all
select  9, 1122 union all
select 10,  811 union all
select 11, 1122

select t0.version, t0.worker, 1 + sum(isnull(ischanged, 0)) as changed_version
from @t t0
 left join
	(
		select t2.*, case when t1.worker = t2.worker then 0 else 1 end as ischanged 
		from @t t1
		 join @t t2 on (t1.version = t2.version - 1)
	) c on (t0.version >= c.version)
group by t0.version, t0.worker
order by version
 
11 авг 09, 13:56    [7522684]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Дедушка
iap
Если Worker не может принимать нулевое значение, то можно проще
SELECT COUNT(*)
FROM T
WHERE T.[Worker]<>ISNULL((SELECT TOP 1 TT.[Worker] FROM T TT WHERE TT.[Version]<T.[Version] ORDER BY TT.[Version] DESC),0);

в данном варианте все изменения для разных id сложатся
Пример дан для одного id. Доделать для многих [Id] может и ребёнок!
SELECT T.[Id], COUNT(*)
FROM T
WHERE T.[Worker]<>ISNULL((SELECT TOP 1 TT.[Worker] FROM T TT WHERE TT.[Id]=T.[Id] AND TT.[Version]<T.[Version] ORDER BY TT.[Version] DESC),0)
GROUP BY T.[Id];
11 авг 09, 14:06    [7522776]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
iap
Пример дан для одного id. Доделать для многих [Id] может и ребёнок!

если предполагается "ребёнок" то нуно ему об этом сказать, а то ребус...
собственно я без претензий, а автор уже покинул нас :))
11 авг 09, 14:15    [7522852]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
alkersan
Member

Откуда:
Сообщений: 20
Нет, я вас не покидал. Я слежу за обсуждением. Парвда мало что понимаю
11 авг 09, 14:20    [7522898]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Дедушка
iap
Пример дан для одного id. Доделать для многих [Id] может и ребёнок!

если предполагается "ребёнок" то нуно ему об этом сказать, а то ребус...
собственно я без претензий, а автор уже покинул нас :))
Ну а почему он дал пример данных только для [Id]=3200?
Я так понял, что обобщить и для него не проблема.
11 авг 09, 14:21    [7522899]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
aleks2
Guest
declare @t table(version int, worker int, id int)
insert into @t(version, worker, id)
select  1, 1099, 1 union all
select  2, 1122, 1 union all
select  3,  953, 1 union all
select  4, 1122, 1 union all
select  5,  586, 1 union all
select  6,  848, 1 union all
select  7, 1122, 1 union all
select  8, 1122, 1 union all
select  9, 1122, 1 union all
select 10,  811, 1 union all
select 11, 1122, 1 union all
select  1, 1099, 2 union all
select  2, 1122, 2 union all
select  3,  953, 2 union all
select  4, 1122, 2 union all
select  5,  586, 2 union all
select  6,  848, 2 union all
select  7, 1122, 2 union all
select  8, 1122, 2 union all
select  9, 1122, 2 union all
select 10, 1122, 2 union all
select 11, 1122, 2



select *, 
  (select count(*)
     FROM 
     (select * FROM @t X WHERE X.Version<=T0.Version AND X.id=T0.ID) T1 
      INNER JOIN 
     (select * FROM @t X WHERE X.id=T0.ID) T2 
      ON 
      T1.Version+1=T2.Version AND T1.worker<>T2.worker
  )+1 Count
FROM 
@t T0
Внесем посильную лепту
11 авг 09, 14:34    [7523008]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать количество изменинй поля в записи  [new]
pacha
Member

Откуда:
Сообщений: 239
Обобщение моего примера для нескольких id:
declare @t table(id int, version int, worker int)
insert into @t(id, version, worker)
select 1, 1, 1099 union all
select 1, 2, 1122 union all
select 1, 3,  953 union all
select 1, 4, 1122 union all
select 1, 5,  586 union all
select 1, 6,  848 union all
select 1, 7, 1122 union all
select 1, 8, 1122 union all
select 1, 9, 1122 union all
select 1, 10,  811 union all
select 1, 11, 1122 union all
select 2, 1, 1099 union all
select 2, 2, 1099 union all
select 2, 3,  953

select t0.id, t0.version, t0.worker, 1 + sum(isnull(ischanged, 0)) as changed_version
from @t t0
 left join
	(
		select t2.*, case when t1.worker = t2.worker then 0 else 1 end as ischanged 
		from @t t1
		 join @t t2 on (t1.id = t2.id and t1.version = t2.version - 1)
	) c on (t0.id = c.id and t0.version >= c.version)
group by t0.id, t0.version, t0.worker
order by t0.id, t0.version

11 авг 09, 14:41    [7523061]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить