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

Откуда: Minsk Power Station
Сообщений: 457
Приветствую форумчан!

Подскажите, есть таблица tblWorkFlow, где есть поля - PersonID(int), BookID(int), Dateofrent(datetime), Info(nvarchar(50)).
Постоянно возникает надобность удалить все записи конкретного PersonID и вставить новые.
Но перед тем как удалить, я хотел бы предварительно сохранить слепок удаляемых записей, чтобы впоследствии сравнить со вновь вставляемыми.

Поэтому, задумал план такой: завожу транзитную таблицу tblHistory, и потом
a) снимаем копию с рабочей таблицы текущих данных:
INSERT INTO tblHistory (PersonID, BookID, Dateofrent, Info) VALUES (SELECT PersonID и BookID, Dateofrent, Info FROM tblWorkFlow)

b) вставляем свежие данные в tblWorkFlow
INSERT INTO tblWorkFlow (PersonID, BookID, Dateofrent, Info) VALUES (...from source)


И теперь вот вопрос, каким образом получить событие, что есть изменённые данные(изменённые, удалённые, вновь добавленные BookID)? (В том случае, если они действительно есть). Т.е. я пытаюсь придумать некий триггер, что после того, как на сервере произошло удаление и вставка данных, проводилось бы сравнение двух выборок данных, как если бы сравнивали два рекордсета:
SELECT BookID, Dateofrent, Info FROM tblWorkFlow WHERE PersonID = X);
--c
SELECT BookID, Dateofrent, Info FROM tblHistory WHERE PersonID = X);

и только если есть отличия, отбирались бы затронутые BookID.

П.С. Если перевести на человеческий язык, то в БД есть Пользователи, которые берут книги в библиотеке.
Например, изначально, Пользователь Иван взял 5 книг. Затем, 2 вернул, и взял ещё какую-то.
Библиотекарь, удаляет всю информацию в БД касаемо этого пользователя и вносит новые сведения.
Задача получить ID книг, с которыми произошли манипуляции.
5 апр 19, 22:54    [21854468]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 31392
Блог
palladin600,

Ваш синтаксис нерабочий.
Также инструкция delete имеет ключевое слово output
6 апр 19, 00:30    [21854513]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 31392
Блог
Ну и в таблице приемнике нужно просто сделать поле со временем вставки. Это самое простое.
6 апр 19, 00:33    [21854515]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
aleks222
Member

Откуда:
Сообщений: 541
Я, канешно, понимаю, что библиотекари и бухгалтеры - это разные люди.
Но ценный опыт перенимать надо.

1. Никаких историй.
2. Одна таблица.
3. В нее вносим выдачу. ДАТА.
4. В нее же вносим возврат. ОТДЕЛЬНОЙ СТРОКОЙ. ДАТА.
5. Профит.
6. Вся история в одном флаконе. И на любую дату.
6 апр 19, 08:13    [21854551]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28168
palladin600
Например, изначально, Пользователь Иван взял 5 книг. Затем, 2 вернул, и взял ещё какую-то.
Библиотекарь, удаляет всю информацию в БД касаемо этого пользователя и вносит новые сведения.
Нужно в базу записывать факты.

Не надо "удалять", "вносить", нужно делать так же, как без компьютера.
Вот в библиотеке раньше, до компьютеров, была карточка читателя. Когда читатель приходил, карточку не сжигали, заводя новую. Туда просто писали факт: Взял книгу "ааа". Вернул книгу "ббб".
На каждую книгу одна запись.

И в базе делайте так же.
Это будет основная таблица (наряду с справочником книг и справочником читателей), по ней можно получить всю информацию.
Об этом уже написал aleks222, я просто немного подробнее пытаюсь объяснить :-)

Кроме того, когда наберётесь опыта, можете сделать агрегированные данные - количество выданных книг в таблице книг, и таблицу со списоком текущих выданных книг (простая связь PersonID, BookID). Это позволит быстрее получать данные. Но сразу лучше этим не заниматься.
6 апр 19, 10:24    [21854574]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 457
Так, спасибо, немного внесём ясности,
а) никакой исторической информации в таблице не должно быть, т.к. это текущие актуальные данные, с которыми работают другие сервисы
б) опираться исключительно на поле дата не эффективно, т.к. вставляться могут те же самые данные, что были удалены

Рассмотрим задачу схематично:
--текущий список
declare @tblWorkFlow table(id int, PersonID int, BookID int, Info nvarchar(50))
insert into @tblWorkFlow(id, PersonID, BookID, Info)
select 1, 1, 1, '' union all
select 2, 1, 2, '' union all
select 3, 1, 3, '' union all
select 4, 1, 4, '' union all
select 5, 1, 5, ''

--новый пакет данных, которые вставляет библиотекарь
declare @SourceData table(id int, PersonID int, BookID int, Info nvarchar(50))
insert into @SourceData(id, PersonID, BookID, Info)
select 6, 1, 1, '' union all
select 7, 1, 2, '' union all
select 8, 1, 3, 'порвал обложку' union all
select 9, 1, 6, ''

условно говоря, что мы здесь видим:
записи @tblWorkFlow.BookID IN (1,2) - не изменились (они удалились, но затем были вновь добавлены, правда уже с новым id 6 и 7)
запись @tblWorkFlow.BookID = 3 - изменено поле Info (запись удалена, затем была вставлена новая, под новым id = 8)
записи @tblWorkFlow.BookID IN (4,5) - удалены
запись @tblWorkFlow.BookID = 6 - вновь добавленная

И вот как вычислить именно @tblWorkFlow.BookID IN (3,4,5,6) - что они новые или по ним были изменения в полях?
6 апр 19, 10:39    [21854577]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
palladin600
Так, спасибо, немного внесём ясности,
а) никакой исторической информации в таблице не должно быть, т.к. это текущие актуальные данные, с которыми работают другие сервисы
б) опираться исключительно на поле дата не эффективно, т.к. вставляться могут те же самые данные, что были удалены

Рассмотрим задачу схематично:
--текущий список
declare @tblWorkFlow table(id int, PersonID int, BookID int, Info nvarchar(50))
insert into @tblWorkFlow(id, PersonID, BookID, Info)
select 1, 1, 1, '' union all
select 2, 1, 2, '' union all
select 3, 1, 3, '' union all
select 4, 1, 4, '' union all
select 5, 1, 5, ''

--новый пакет данных, которые вставляет библиотекарь
declare @SourceData table(id int, PersonID int, BookID int, Info nvarchar(50))
insert into @SourceData(id, PersonID, BookID, Info)
select 6, 1, 1, '' union all
select 7, 1, 2, '' union all
select 8, 1, 3, 'порвал обложку' union all
select 9, 1, 6, ''


условно говоря, что мы здесь видим:
записи @tblWorkFlow.BookID IN (1,2) - не изменились (они удалились, но затем были вновь добавлены, правда уже с новым id 6 и 7)
запись @tblWorkFlow.BookID = 3 - изменено поле Info (запись удалена, затем была вставлена новая, под новым id = 8)
записи @tblWorkFlow.BookID IN (4,5) - удалены
запись @tblWorkFlow.BookID = 6 - вновь добавленная

И вот как вычислить именно @tblWorkFlow.BookID IN (3,4,5,6) - что они новые или по ним были изменения в полях?

select * from @SourceData S
full join  @tblWorkFlow W on S.PersonID  = W.PersonID  and S.BookID = W.BookID 
where IsNull(S.Info, '')<> IsNull(W.Info, '') 
6 апр 19, 10:56    [21854580]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
iap
Member

Откуда: Москва
Сообщений: 46656
Deff
select * from @SourceData S
full join  @tblWorkFlow W on S.PersonID  = W.PersonID  and S.BookID = W.BookID 
where IsNull(S.Info, '')<> IsNull(W.Info, '') 
ISNULL здесь лишний
6 апр 19, 11:03    [21854583]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 457
Deff,
здесь у вас запрос возвращает только BookID = 3.
а то что добавляется новая книга BookID = 6?
а то что были удалены прежние 2 книги BookID = 4 и 5?
6 апр 19, 11:04    [21854584]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
palladin600
Deff,
здесь у вас запрос возвращает только BookID = 3.
а то что добавляется новая книга BookID = 6?
а то что были удалены прежние 2 книги BookID = 4 и 5?
Ну тогда уберите условие "and S.BookID = W.BookID", и будет только по пользователю
6 апр 19, 11:05    [21854586]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
iap
Deff
select * from @SourceData S
full join  @tblWorkFlow W on S.PersonID  = W.PersonID  and S.BookID = W.BookID 
where IsNull(S.Info, '')<> IsNull(W.Info, '') 

ISNULL здесь лишний
Нет, не лишний. При отсутствии записи придет Null, и он не попадет в сравнение.
6 апр 19, 11:09    [21854588]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 457
Deff
Ну тогда уберите условие "and S.BookID = W.BookID", и будет только по пользователю

по-моему, что-то здесь не так, вернулись такие записи:
id	PersonID	BookID	Info	id	PersonID	BookID	Info
3 1 3 порвал обложку 1 1 1
3 1 3 порвал обложку 2 1 2
3 1 3 порвал обложку 3 1 3
3 1 3 порвал обложку 4 1 4
3 1 3 порвал обложку 5 1 5
здесь все книги что и были изначально в таблице, и к тому же нет вновь добавляемой BookID = 6.

Нужен ответ такой:
BookID
3
4
5
6
6 апр 19, 11:26    [21854601]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
palladin600
Deff
Ну тогда уберите условие "and S.BookID = W.BookID", и будет только по пользователю

по-моему, что-то здесь не так, вернулись такие записи:
id	PersonID	BookID	Info	id	PersonID	BookID	Info
3 1 3 порвал обложку 1 1 1
3 1 3 порвал обложку 2 1 2
3 1 3 порвал обложку 3 1 3
3 1 3 порвал обложку 4 1 4
3 1 3 порвал обложку 5 1 5
здесь все книги что и были изначально в таблице, и к тому же нет вновь добавляемой BookID = 6.

Нужен ответ такой:
BookID
3
4
5
6
Пришлось все таки подключиться к работе.

select  W.BookID from @tblWorkFlow W
	left join  @SourceData S on S.PersonID  = W.PersonID  and S.BookID = W.BookID 
where (S.Info <> W.Info or S.Id is null)
union
select S.BookID from @SourceData S
	left join  @tblWorkFlow W on S.PersonID  = W.PersonID  and S.BookID = W.BookID 
where (S.Info <> W.Info or W.Id is null)
6 апр 19, 11:45    [21854607]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Вместо join лучше на exist переделать - планы могут быть получше, но вам возможно нужно больше полей, но тогда там надо доделать, чтобы distinct сработал.
6 апр 19, 11:48    [21854610]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Или через full join
select ISNULL(S.BookID, W.BookID) from @SourceData S
full join  @tblWorkFlow W on S.PersonID  = W.PersonID  and S.BookID = W.BookID 
where S.Info<> W.Info or W.Id is null or  S.Id is null
6 апр 19, 12:05    [21854619]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28168
palladin600
Нужен ответ такой:
select f.id, s.id, isnull(f.PersonID, s.PersonID), isnull(f.BookID, s.BookID), isnull(nullif(f.Info, ''), nullif(s.Info, ''))
from @tblWorkFlow f
	full join @SourceData s
		on s.PersonID = f.PersonID and s.BookID = f.BookID
where isnull(s.PersonID, 0) <> isnull(f.PersonID, 0) or isnull(s.BookID, 0) <> isnull(f.BookID, 0) or isnull(s.Info, '') <> isnull(f.Info, '')
6 апр 19, 12:10    [21854622]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6142
where S.Info<> W.Info or W.Id is null or  S.Id is null

за буквы доплачивают? допишитн ещё 1 = 1 OR 2 = 2
6 апр 19, 12:10    [21854623]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
TaPaK
where S.Info<> W.Info or W.Id is null or  S.Id is null


за буквы доплачивают? допишитн ещё 1 = 1 OR 2 = 2
У меня верный запрос. Как короче написать?
6 апр 19, 12:12    [21854627]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6142
Deff
TaPaK
where S.Info<> W.Info or W.Id is null or  S.Id is null


за буквы доплачивают? допишитн ещё 1 = 1 OR 2 = 2
У меня верный запрос. Как короче написать?

select ISNULL(S.BookID, W.BookID) from @SourceData S
full join  @tblWorkFlow W on S.PersonID  = W.PersonID  and S.BookID = W.BookID  AND S.Info<> W.Info
6 апр 19, 12:30    [21854651]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
TaPaK
Deff
пропущено...
У меня верный запрос. Как короче написать?

select ISNULL(S.BookID, W.BookID) from @SourceData S
full join  @tblWorkFlow W on S.PersonID  = W.PersonID  and S.BookID = W.BookID  AND S.Info<> W.Info
Это мой первый запрос запрос, но он не правильный.

Ключевой момент про книги. Можно взять одну, а сдать другую.
6 апр 19, 12:37    [21854652]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
aleks222
Member

Откуда:
Сообщений: 541
palladin600
Так, спасибо, немного внесём ясности,
а) никакой исторической информации в таблице не должно быть, т.к. это текущие актуальные данные, с которыми работают другие сервисы
б) опираться исключительно на поле дата не эффективно, т.к. вставляться могут те же самые данные, что были удалены

Рассмотрим задачу схематично:


Ээх, страдалец, лежит твой путь в управдомы. И чем быстрее ты переквалифицируешься - тем менее мучительно больно будет.

ЗЫ. Не из актуальных данных делать историю надо. А из истории - актуальные данные.
6 апр 19, 13:57    [21854685]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 457
Ребят, спасибо за подсказки. Насколько я могу судить по результатам, варианты от Deff и alexeyvg меня успокоили.
aleks222
Ээх, страдалец, лежит твой путь в управдомы. И чем быстрее ты переквалифицируешься - тем менее мучительно больно будет.

ЗЫ. Не из актуальных данных делать историю надо. А из истории - актуальные данные.

Здесь ну что ответить, конечно, я новатор-экспериментатор в этих запросах, пытаюсь последовательно решать какие-то задачи. Но, с другой стороны, если представить что это не книги, а некие состояния каких-то регистров. Здесь, библиотеку и книги я привёл, ну чтоб не вдаваться в технические дебри устройства. Иначе говоря, мне вся история не особо нужна. Нужно только иметь возможность сравнения последнего состояния со вновь прибывшим.
За критику спасибо, надо подумать, может быть есть смысл действительно хранить всю историю. Я и думал её хранить, она полезна для статистики. Но выбросил из головы идею, потому что не охота набивать таблицами бд. Хотя бы справиться с тем объёмом данных, которые циркулируют на самом верхнем слое.
6 апр 19, 15:06    [21854697]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28168
palladin600
Но, с другой стороны, если представить что это не книги, а некие состояния каких-то регистров.
То всё равно концепция странная.
Вам нужно передать информацию об изменении состояния, но вы её формируете как "изменение состояния междду выполнениями запроса".
Через некоторое время вы поймёте принципиальную ошибочность такого подхода, т.к. работа системы будет сопровождаться мелкими и трудноуловимыми багами. Мало ли, как там выполнился запрос, чем сервер был занят, как там проходили сетевые пакеты, и по тем или иным причинам запрос приходится повторить? А в новом запросе результат не будет возвращён, изменений то нет!

И задумаетесь, "не сделать ли моменты времени получения справки об изменении состояния какими то овеществлёнными?" И получать информация об изменении состояния не "от запроса до запроса", а между вот этими моментами времени?
Вот тогда придётся переходить на схему с записью фактов, и получением отчётов по этим фактам.
6 апр 19, 20:13    [21854820]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
uaggster
Member

Откуда:
Сообщений: 554
palladin600
Приветствую форумчан!

Подскажите, есть таблица tblWorkFlow, где есть поля - PersonID(int), BookID(int), Dateofrent(datetime), Info(nvarchar(50)).
Постоянно возникает надобность удалить все записи конкретного PersonID и вставить новые.
Но перед тем как удалить, я хотел бы предварительно сохранить слепок удаляемых записей, чтобы впоследствии сравнить со вновь вставляемыми.

Начиная с 2016 - темпоральные таблицы бесплатны и доступны для всех редакций сервера.
http://www.korshikov.guru/2015/06/sql-server-2016-temporal-tables/
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017

Не благодарите.
:-)
8 апр 19, 08:29    [21855415]     Ответить | Цитировать Сообщить модератору
 Re: Как, скажите, лучше проводить сравнение данных  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3878
Критик
palladin600,

Ваш синтаксис нерабочий.
Также инструкция delete имеет ключевое слово output


Также все это можно сделать конструкцией MERGE включая кстати возможность не удалять вставлять дубли, если их большинство.
8 апр 19, 13:30    [21855843]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить