Masalah

Saya mencoba mendeteksi dan bereaksi terhadap perubahan dalam tabel di mana setiap pembaruan dicatat sebagai baris baru dengan beberapa nilai sama dengan aslinya, beberapa diubah (yang ingin saya deteksi) dan beberapa nilai NULL (tidak dianggap berubah).

Misalnya, diberikan tabel berikut MyData, dan dengan asumsi OrderNumber adalah nilai umum,

ID  OrderNumber   CustomerName   PartNumber  Qty   Price   OrderDate
1   123           Acme Corp.     WG301       4     15.02   2020-01-02
2   456           Base Inc.      AL337       7     20.15   2020-02-03
3   123           NULL           WG301b      5     19.57   2020-01-02

Jika saya menjalankan kueri untuk OrderNumber = 123, saya ingin data berikut dikembalikan:

Column      OldValue  NewValue
ID          1         3
PartNumber  WG301     WG301b
Qty         4         5
Price       15.02     19.57

Atau mungkin satu baris hasil dengan hanya perubahan yang diisi, seperti ini (namun, saya lebih suka format sebelumnya):

ID  OrderNumber   CustomerName   PartNumber  Qty   Price   OrderDate
3   NULL          NULL           WG301b      5     19.57   NULL

Solusi Saya

Saya belum memiliki kesempatan untuk menguji ini, tetapi saya sedang mempertimbangkan untuk menulis kueri dengan pendekatan berikut (kode semu):

select
  NewOrNull(last.ID, prev.ID) as ID,
  NewOrNull(last.OrderNumber, prev.OrderNumber) as OrderNumber
  NewOrNull(last.CustomerName, prev.CustomerName) as CustomerName,
  ...
  from last row with OrderNumber = 123
  join previous row where OrderNumber = 123

Di mana fungsi NewOrNull(lastVal, prevVal) mengembalikan NULL jika nilainya sama atau nilai lastVal adalah NULL, jika tidak lastVal.

Mengapa Saya Mencari Jawaban

Saya khawatir bergabung dengan jelek, jumlah panggilan ke fungsi, dan pendekatan prosedural dapat membuat pendekatan ini tidak dapat diskalakan. Sebelum saya mulai menyusuri lubang kelinci, saya bertanya-tanya...

Pertanyaan

...apakah ada pendekatan lain yang harus saya coba, atau praktik terbaik apa pun untuk memecahkan jenis masalah khusus ini?

0
Jens Ehrich 3 Juli 2020, 01:15

1 menjawab

Jawaban Terbaik

Saya menemukan solusi untuk format kedua (kurang disukai):

Data

Menggunakan data berikut:

INSERT INTO MyData
    ([ID], [OrderNumber], [CustomerName], [PartNumber], [Qty], [Price], [OrderDate])
VALUES
    (1, 123, 'Acme Corp.', 'WG301', '4', '15.02', '2020-01-02'),
    (2, 456, 'Base Inc.', 'AL337', '7', '20.15', '2020-02-03'),
    (3, 123, NULL, 'WG301b', '5', '19.57', '2020-01-02'),
    (4, 123, 'ACME Corp.', 'WG301b', NULL, NULL, '2020-01-02'),
    (6, 456, 'Base Inc.', NULL, '7', '20.15', '2020-02-05');

Fungsi:

Fungsi ini mengembalikan nilai yang diperbarui jika telah berubah, jika tidak NULL:

CREATE FUNCTION dbo.NewOrNull
(
  @newValue sql_variant, 
  @oldValue sql_variant
)  
RETURNS sql_variant
AS   
BEGIN  
    DECLARE @ret sql_variant
    SELECT @ret = CASE 
      WHEN @newValue IS NULL THEN NULL
      WHEN @oldValue IS NULL THEN @newValue
      WHEN @newValue = @oldValue THEN NULL
      ELSE @newValue
      END
    RETURN @ret
END;

Pertanyaan

Kueri ini mengembalikan riwayat perubahan untuk nomor pesanan yang diberikan:

select dbo.NewOrNull(new.ID, old.ID) as ID,
       dbo.NewOrNull(new.OrderNumber, old.OrderNumber) as OrderNumber,
       dbo.NewOrNull(new.CustomerName, old.CustomerName) as CustomerName,
       dbo.NewOrNull(new.PartNumber, old.PartNumber) as PartNumber,
       dbo.NewOrNull(new.Qty, old.Qty) as Qty,
       dbo.NewOrNull(new.Price, old.Price) as Price,
       dbo.NewOrNull(new.OrderDate, old.OrderDate) as OrderDate
from MyData new
left join MyData old
  on old.ID = (
    select top 1 ID 
    from MyData pre
    where pre.OrderNumber = new.OrderNumber
      and pre.ID < new.ID
    order by pre.ID desc
    )
where new.OrderNumber = 123

Hasil

ID  OrderNumber CustomerName    PartNumber  Qty Price   OrderDate
1   123 Acme Corp.  WG301   4   15.02   2020-01-02
3   (null)  (null)  WG301b  5   19.57   (null)
4   (null)  ACME Corp.  (null)  (null)  (null)  (null)

Biola

Inilah SQL Fiddle yang menunjukkan semuanya dalam tindakan.

http://sqlfiddle.com/#!18/b720f/5/0

1
Jens Ehrich 3 Juli 2020, 02:36