Tüm matematik işlevleri hata durumunda NULL döndürür.
Tek eksi. Bir bağımsız değişkenin işaretini değiştirir: mysql> SELECT - 2; -> -2 Bu işleç BIGINT türündeki verilerle kullanılırsa, dönüş değerinin de BIGINT türünde olacağına dikkat edin! Bu, -2^63 değerine sahip tamsayılarda operatörü kullanmaktan kaçınmanız gerektiği anlamına gelir! ABS(X) X'in mutlak değerini döndürür: mysql> SELECT ABS(2); -> 2 mysql> ABS'Yİ SEÇ(-32); -> 32 Bu fonksiyon BIGINT değerleri için güvenle kullanılabilir. SIGN(X) X'in negatif, sıfır veya pozitif olmasına bağlı olarak argümanın işaretini -1 , 0 veya 1 olarak döndürür: mysql> SELECT SIGN(-32); -> -1 mysql> İŞARETİ SEÇ(0); -> 0 mysql> İŞARETİ SEÇ(234); -> 1 MOD(N,M) % Modulo değeri (C'deki % operatörüne benzer). N'nin M'ye bölümünden kalanı verir: mysql> SELECT MOD(234, 10); -> 4 mysql> SEÇ 253 % 7; -> 1 mysql> MOD SEÇ(29,9); -> 2 Bu fonksiyon BIGINT değerleri için güvenle kullanılabilir. FLOOR(X) X'ten büyük olmayan en büyük tamsayıyı döndürür: mysql> SELECT FLOOR(1.23); -> 1 mysql> KAT SEÇ(-1.23); -> -2 Dönüş değerinin BIGINT'e dönüştürüldüğünü unutmayın! TAVAN(X) X'ten küçük olmayan en küçük tamsayıyı döndürür: mysql> SELECT TAVAN(1.23); -> 2 mysql> TAVAN SEÇ(-1.23); -> -1 Dönüş değerinin BIGINT'e dönüştürüldüğünü unutmayın! ROUND(X) X argümanını en yakın tamsayıya yuvarlanmış olarak döndürür: mysql> SELECT ROUND(-1.23); -> -1 mysql> YUVARLAK SEÇ(-1.58); -> -2 mysql> YUVARLAK SEÇ(1.58); -> 2 Lütfen argüman değeri iki tamsayı arasında ortada olduğunda ROUND() işlevinin davranışının C kitaplığının belirli uygulamasına bağlı olduğunu unutmayın.Yuvarlama yapılabilir: en yakın çift sayıya, her zaman en yakın büyüğe, her zaman en yakın küçüğe, her zaman sıfıra doğru. Yuvarlamanın her zaman yalnızca bir yönde gerçekleşmesini sağlamak için bunun yerine TRUNCATE() veya FLOOR() gibi iyi tanımlanmış işlevleri kullanmalısınız. YUVARLAK(X,D) X bağımsız değişkenini, D ondalık basamaklı bir sayıya yuvarlanmış olarak döndürür. D 0 ise, sonuç ondalık nokta veya kesirli bölüm olmadan sunulur: mysql> SELECT ROUND(1.298, 1); -> 1.3 mysql> YUVARLAK SEÇ(1.298, 0); -> 1 EXP(X) e'nin (doğal logaritma tabanı) X'in üssüne yükseltilmiş değerini verir: mysql> SELECT EXP(2); -> 7.389056 mysql> EXP SEÇ(-2); -> 0.135335 LOG(X) X'in doğal logaritmasını döndürür: mysql> SELECT LOG(2); -> 0. 693147 mysql> GÜNLÜK SEÇ(-2); -> NULL Rastgele bir B tabanı için bir X sayısının logaritmasını almak için LOG(X)/LOG(B) formülünü kullanın. LOG10(X) X'in 10 tabanlı logaritmasını döndürür: mysql> SELECT LOG10(2); -> 0.301030 mysql> LOG10(100) SEÇİN; -> 2.000000 mysql> LOG10(-100) SEÇİN; -> NULL POW(X,Y) POWER(X,Y) X bağımsız değişkeninin Y'nin üssüne yükseltilmiş değerini döndürür: mysql> SELECT POW(2,2); -> 4.000000 mysql> SELECT POW(2,-2); -> 0.250000 SQRT(X) X'in negatif olmayan karekökünü döndürür: mysql> SELECT SQRT(4); -> 2.000000 mysql> KAREKÖK SEÇ(20); -> 4.472136 PI() "pi" değerini döndürür. Varsayılan değer 5 ondalık basamaktır, ancak MySQL dahili olarak pi'yi temsil etmek için tam çift kesinlik kullanır. mysql> PI SEÇ(); -> 3.141593 mysql> SELECT PI()+0.000000000000000000; -> 3.141592653589793116 COS(X) X'in kosinüsünü döndürür, burada X radyan cinsindendir: mysql> SELECT COS(PI()); -> -1.000000 SIN(X) X'in sinüsünü döndürür, burada X radyan cinsindendir: mysql> SELECT SIN(PI()); -> 0.000000 TAN(X) X'in radyan cinsinden tanjantını döndürür: mysql> SELECT TAN(PI()+1); -> 1.557408 ACOS(X) X'in ark kosinüsünü verir, yani kosinüsü X'e eşit olan bir değer. X, -1 ile 1 arasında değilse NULL döndürür: mysql> SELECT ACOS(1); -> 0.000000 mysql> ACOS'U SEÇ(1.0001); -> NULL mysql> ACOS'U SEÇ(0); -> 1.570796 ASIN(X) X'in arksinüsünü verir, yani sinüsü X'e eşit olan bir değer. X, -1 ila 1 aralığında değilse, NULL döndürür: mysql> SELECT ASIN(0.2); -> 0.201358 mysql> SELECT ASIN("foo"); -> 0.000000 ATAN(X) X'in ark tanjantını verir, yani tanjantı X olan bir değer: mysql> SELECT ATAN(2); -> 1.107149 mysql> SELECT ATAN(-2); -> -1.107149 ATAN(Y,X) ATAN2(Y,X) X ve Y değişkenlerinin yay tanjantını döndürür. Hesaplama, sonucun çeyreğini belirlemek için her iki argümanın işaretlerinin kullanılması dışında Y / X'in yay tanjantını hesaplamakla aynıdır: mysql> SELECT ATAN(-2,2); -> -0.785398 mysql> SELECT ATAN2(PI(),0); -> 1.570796 COT(X) X'in kotanjantını döndürür: mysql> SELECT COT(12); -> -1.57267341 mysql> COT SEÇ(0); -> NULL RAND() RAND(N) 0 ile 1.0 arasında rastgele bir kayan nokta değeri döndürür. N tamsayı argümanı verilirse, bu değerin başlangıç değeri olarak kullanılır: mysql> SELECT RAND(); -> 0. 9233482386203 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(); -> 0.63553050033332 mysql> SELECT RAND(); -> 0.70100469486881 ORDER BY ifadelerinde RAND() değerleri olan bir sütun kullanmamalısınız, çünkü ORDER BY operatörünün kullanılması o sütun üzerinde birden çok değerlendirme yapılmasına neden olacaktır. Ancak MySQL sürüm 3.23'te aşağıdaki ifadeyi verebilirsiniz: SELECT * FROM table_name ORDER BY RAND() : bu, bir kümeden rastgele bir örnek almak için kullanışlıdır SELECT * FROM table1,table2 WHERE a=b AND c
Bu makale, stok bakiyelerini hesaplama sorunu için bir Transact SQL optimizasyon çözümü sunmaktadır. Uygulanan: Bölümleme tabloları ve gerçekleştirilmiş görünümler.
Sorunun formülasyonu
Görev, SQL Server 2014 Enterprise Edition'da (x64) çözülmelidir. Şirketin birçok deposu var. Her depoda her gün birkaç bin sevkiyat ve ürün makbuzu vardır. Depo gelir/giderindeki mal hareketi tablosu bulunmaktadır. Uygulamak için gereklidir:Her ürün için tüm/herhangi bir depo için seçilen tarih ve saat (bir saate kadar) için bakiyenin hesaplanması. Analitik için, seçilen tarih aralığı için tüm depolar ve ürünler için kaynak tablonun verilerini ve ek bir hesaplama sütunu olan depo pozisyonundaki bakiyeyi görüntüleyen bir nesne (işlev, tablo, görünüm) oluşturmanız gerekir.
Bu hesaplamaların farklı tarih aralıklarında bir programa göre ve makul bir zamanda yapılması gerekiyor. Onlar. son bir saat veya gün için bakiyeler içeren bir tablonun görüntülenmesi gerekiyorsa, yürütme süresi mümkün olduğu kadar hızlı olmalıdır ve aynı verilerin analitik bir veritabanına daha sonra yüklenmesi için son 3 yıl için görüntülenmesi gerekiyorsa.
Teknik detaylar. Tablonun kendisi:
dbo.Turnover tablosu oluştur (id int kimlik birincil anahtarı, dt tarihsaat boş değil, ÜrünID int değil, StorehouseID int boş değil, Smallint işlemi boş değil kontrol (İşlem (-1,1)) -- +1 stok girişi, -1 stokta yok Miktar sayısal(20,2) boş değil, Maliyet parası boş değil)
Dt - Depoya / depodan teslim alma / iptal etme tarihi ve saati.
Ürün Kimliği - Ürün
StorehouseID - ambar
Çalışma - 2 değer gelen veya giden
Miktar - stoktaki ürünün miktarı. Ürünün parça halinde değil, örneğin kilogram cinsinden olması gerçek olabilir.
Maliyet - bir ürün partisinin maliyeti.
Problem araştırması
Tamamlanmış bir tablo oluşturalım. Benimle test etmeniz ve sonuçları görmeniz için dbo.Turnover tablosunu bir komut dosyasıyla oluşturmayı ve doldurmayı öneriyorum:object_id("dbo.Turnover","U") boş değilse dbo.Turnover tablosunu bırakın; kez şu şekilde gidin (1 id birliği seçin, id'nin olduğu zamanlardan id+1'i seçin< 10*365*24*60 -- 10 лет * 365 дней * 24 часа * 60 минут = столько минут в 10 лет)
, storehouse as
(select 1 id
union all
select id+1
from storehouse
where id < 100 -- количество складов)
select
identity(int,1,1) id,
dateadd(minute, t.id, convert(datetime,"20060101",120)) dt,
1+abs(convert(int,convert(binary(4),newid()))%1000) ProductID, -- 1000 - количество разных продуктов
s.id StorehouseID,
case when abs(convert(int,convert(binary(4),newid()))%3) in (0,1) then 1 else -1 end Operation, -- какой то приход и расход, из случайных сделаем из 3х вариантов 2 приход 1 расход
1+abs(convert(int,convert(binary(4),newid()))%100) Quantity
into dbo.Turnover
from times t cross join storehouse s
option(maxrecursion 0);
go
--- 15 min
alter table dbo.Turnover alter column id int not null
go
alter table dbo.Turnover add constraint pk_turnover primary key (id) with(data_compression=page)
go
-- 6 min
PC'de bu komut dosyasına sahibim SSD sürücüsüÇalıştırılması yaklaşık 22 dakika sürdü ve tablo boyutu yaklaşık 8 GB sabit disk alanı kapladı. Tablo oluşturmak ve doldurmak için gereken süreyi azaltmak için yıl sayısını ve ambar sayısını azaltabilirsiniz. Ancak sorgu planlarını değerlendirmek için en az 1-2 gigabayt gibi iyi bir miktar bırakmanızı öneririm.
Verileri bir saate kadar gruplama
Daha sonra, çalışılan süre boyunca depodaki ürünlere göre miktarları gruplamamız gerekiyor, problem formülasyonumuzda bu bir saattir (bir dakikaya kadar, 15 dakikaya kadar, bir gün mümkündür. Ancak açıkçası, milisaniyeye kadar, herhangi birinin rapora ihtiyaç duyması pek olası değildir). Sorgularımızı yürüttüğümüz oturumdaki (penceredeki) karşılaştırmalar için, - set istatistik zamanı açık; komutunu uygulayacağız. Ardından, sorguları kendileri yürütür ve sorgu planlarına bakarız:
top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) as dt, -- dbo'dan Quantity olarak saate kadar ProductID, StorehouseID, sum(Operation*Quantity) öğesini seçin.
İstek maliyeti - 12406
(işlenen satır sayısı: 1000)
Zaman SQL çalışması sunucu:
CPU süresi = 2096594ms, geçen süre = 321797ms.
Sayımımızın devam eden toplamı olarak kabul edilen bir bakiye ile sonuçta bir sorgu yaparsak, sorgu ve sorgu planı aşağıdaki gibi olacaktır:
dt olarak top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120)'yi seçin, -- yuvarlaktan saate ProductID, StorehouseID, sum(Operation*Quantity) Miktar olarak, sum(sum(Operation*Quantity)) over (StorehouseID'ye göre bölümleme, ProductID order by convert(datetime,convert(varchar(13),dt,120)+":0 0",120)) dbo.Turnover grubundan convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID ile Bakiye olarak
İstek maliyeti - 19329
(işlenen satır sayısı: 1000)
SQL Server çalışma süresi:
CPU süresi = 2413155ms, geçen süre = 344631ms.
Gruplandırma optimizasyonu
Burada her şey oldukça basit. Çalışan bir toplam olmadan sorgunun kendisi, gerçekleştirilmiş bir görünümle (dizin görünümü) optimize edilebilir. Gerçekleştirilmiş bir görüş oluşturmak için, özetlenenin Boş değer, toplanır(İşlem*Miktar) veya her alanı NULL DEĞİL yaparız veya ifadeye isnull/coalesce ekleriz. Gerçekleştirilmiş bir görünüm oluşturmayı öneriyorum.
dbo.TurnoverHour görünümünü, dt olarak seç convert(datetime,convert(varchar(13),dt,120)+":00",120) as dt olarak şema bağlama ile oluştur -- yuvarlaktan saate ProductID, StorehouseID, sum(isnull(Operation*Quantity,0)) as Quantity, count_big(*) qty from convert(datetime,convert(varchar(13),dt) ,120)+":00",120), ÜrünKimliği, DepoKimliği git
Ve üzerinde kümelenmiş bir dizin oluşturun. Dizinde, alanların sırasını gruplandırmada olduğu gibi (sıra gruplama için önemli değil, tüm gruplama alanlarının dizinde olması önemlidir) ve kümülatif toplamı (burada sıra önemlidir - önce bölmede ne var, sonra ne sıralamada) belirtiyoruz:
(data_compression=page) ile dbo.TurnoverHour'da (StorehouseID, ProductID, dt) benzersiz kümelenmiş dizin uix_TurnoverHour oluşturun - 19 dk
Şimdi, kümelenmiş dizini oluşturduktan sonra, görünümdeki gibi toplam toplamayı değiştirerek sorguları yeniden çalıştırabiliriz:
dbo'dan Miktar olarak top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) +":00",120) öğesini dt olarak seçin, -- saate kadar ProductID, StorehouseID, sum(isnull(Operation*Quantity,0)) dbo'dan Miktar olarak. convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, Store house ID seç top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) as dt, -- yuvarlaktan saate ProductID, StorehouseID, sum(isnull(Operation*Quantity,0)) as Quantity, sum(sum(isnull(Operation*Quantity,0))) over (StorehouseID'ye göre bölme, ProductID order by convert(datetime,convert(varchar(1) 3),dt,120)+":00",120)) dbo.Turnover grubundan convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID tarafından Bakiye olarak
Sorgu planları şu hale geldi:
Maliyet 0.008
Maliyet 0.01
SQL Server çalışma süresi:
CPU süresi = 31ms, geçen süre = 116ms.
(işlenen satır sayısı: 1000)
SQL Server çalışma süresi:
CPU süresi = 0ms, geçen süre = 151ms.
Toplamda, dizine alınmış bir görünümde, sorgunun verileri gruplandıran bir tabloyu değil, her şeyin zaten gruplandırılmış olduğu kümelenmiş bir dizini taradığını görüyoruz. Ve buna göre yürütme süresi 321797 milisaniyeden 116 ms'ye düşürüldü, yani. 2774 kez.
Bu, optimizasyonumuzun sonu olabilir, çünkü çoğu zaman tüm tabloya (görünüm) değil, seçilen aralık için tablonun bir kısmına ihtiyacımız var.
ara bakiyeler
Sonuç olarak, aşağıdaki sorguyu hızlı bir şekilde yürütmemiz gerekiyor:
tarih formatı ymd'yi ayarla; @start datetime = "2015-01-02", @finish datetime = "2015-01-03" öğesini seçin (dt, StorehouseID, ProductId, Quantity, sum(Quantity) üzerinden (StorehouseID'ye göre bölümleme, dt'ye göre ProductID sırası) dbo'dan Denge olarak seçin.<= @finish) as tmp
where dt >= @başlangıç
Plan maliyeti = 3103. Ve somutlaştırılmış görüş için değil, tablonun kendisi için olsaydı ne olacağını hayal edin.
En yakın saate yuvarlanmış bir tarihte stoktaki her ürün için gerçekleştirilmiş görünüm ve bakiye verilerinin çıktısı. Bakiyeyi hesaplamak için, en baştan (sıfır bakiyeden) belirtilen son tarihe (@bitiş) kadar tüm miktarları özetlemek ve bundan sonra toplanan sonuç kümesinde başlangıç parametresinden sonraki verileri kesmek gerekir.
Burada, belli ki, ara hesaplanan bakiyeler yardımcı olacaktır. Örneğin, her ayın 1'inde veya her Pazar. Bu tür bakiyelere sahip olan görev, önceden hesaplanan bakiyeleri toplamanın ve bakiyeyi baştan değil, son hesaplanan tarihten itibaren hesaplamanın gerekli olacağı gerçeğine indirgenmiştir. Deneyler ve karşılaştırmalar için tarihe göre kümelenmemiş ek bir dizin oluşturacağız:
dbo.TurnoverHour (dt) üzerinde ix_dt dizini oluşturun (data_compression=page); ile (Miktar) içerir; --7 dak Ve talebimiz şöyle görünecektir: set dateformat ymd; @start datetime = "2015-01-02", @finish datetime = "2015-01-03" beyan @start_month datetime = convert(datetime,convert(varchar(9),@start,120)+"1",120) arasından * seçin (dt, StorehouseID, ProductId, Quantity, sum(Quantity) over (StorehouseID'ye göre bölümleme, dt'ye göre ProductID siparişi) dbo'dan Bakiye olarak seçin .TurnoverHour with(noexpand) burada dt, @start_month ve @finish arasında) tmp olarak burada dt >
Genel olarak, bu sorgu, sorgudan etkilenen tüm alanları tamamen kapsayan tarihe göre bir dizine sahip olsa bile, kümelenmiş dizinimizi seçer ve tarar. Sonraki sıralama ile tarihe göre arama yapmak yerine. Aşağıdaki 2 sorguyu yürütmeyi ve elimizdekileri karşılaştırmayı öneriyorum, sonra neyin daha iyi olduğunu analiz edeceğiz:
tarih formatı ymd'yi ayarla; @start datetime = "2015-01-02", @finish datetime = "2015-01-03" beyan @start_month datetime = convert(datetime,convert(varchar(9),@start,120)+"1",120) arasından * seçin (dt, StorehouseID, ProductId, Quantity, sum(Quantity) over (StorehouseID'ye göre bölümleme, dt'ye göre ProductID siparişi) dbo'dan Bakiye olarak seçin .TurnoverHour with(noexpand) burada dt, @start_month ve @finish arasında) tmp olarak burada dt >= @start StorehouseID, ProductID, dt ile sipariş * arasından (dt, StorehouseID, ProductId, Quantity, sum(Quantity) seçin) üzerinden (StorehouseID'ye göre bölümleme, dt'ye göre ProductID sıralayın) dbo.TurnoverHour with(noexpand,index=ix_dt) ile burada d t @start arasında _month ve @finish) tmp olarak burada dt >= @start order by StorehouseID, ProductID, dt
SQL Server çalışma süresi:
CPU süresi = 33860ms, geçen süre = 24247ms.(işlenen satırlar: 145608)
(işlenen satır sayısı: 1)
SQL Server çalışma süresi:
CPU süresi = 6374ms, geçen süre = 1718ms.
CPU süresi = 0ms, geçen süre = 0ms.
Tarihe göre indekslemenin çok daha hızlı olduğu andan itibaren görülebilir. Ancak karşılaştırmalı sorgu planları şöyle görünür:
Otomatik olarak seçilen bir kümelenmiş dizine sahip 1. isteğin maliyeti = 2752, ancak istek tarihine göre bir dizine sahip maliyet = 3119.
Her neyse, burada dizinden iki göreve ihtiyacımız var: sıralama ve aralık seçimi. Bu sorun, bize sunulan dizinlerden biri tarafından çözülemez. İÇİNDE bu örnek veri aralığı yalnızca 1 gündür, ancak daha uzun bir süre varsa, ancak tümü değilse, örneğin 2 aydır, o zaman dizine göre arama, sıralama maliyetleri nedeniyle kesinlikle etkili olmayacaktır.
Burada gördüğüm görünür optimum çözümlerden:
- Hesaplanan bir Yıl-Ay alanı oluşturun ve bir dizin oluşturun (Yıl-Ay, kümelenmiş dizinin diğer alanları). @başlangıç_ay ile bitiş arasındaki dt koşulunda, Yıl-Ay=@ay ile değiştirin ve bundan sonra filtreyi istenen tarihlere uygulayın.
- Filtrelenmiş dizinler - dizinin kendisi kümelenmiş bir dizin gibidir, ancak filtre, istenen ay için tarihe göredir. Ve toplamda aylarımız olduğu kadar çok indeks yapmak. Fikir bir çözüme yakın, ancak burada koşul aralığı 2 filtrelenmiş dizinden geliyorsa, bir birleştirme gerekli olacaktır ve yine de daha fazla sıralama kaçınılmazdır.
- Kümelenmiş dizini, her bölüm yalnızca bir aylık veri içerecek şekilde bölümlere ayırın.
Kümelenmiş görünüm dizinini bölümlere ayırın. Her şeyden önce, tüm dizinleri görünümden kaldırın:
ix_dt dizinini dbo.TurnoverHour'a bırakın; uix_TurnoverHour dizinini dbo.TurnoverHour'a bırakın;
Ve bir işlev ve bir bölümleme şeması oluşturun:
tarih formatı ymd'yi ayarla; değerler için doğru aralık olarak pf_TurnoverHour(datetime) bölümleme işlevi oluşturun ("2006-01-01", "2006-02-01", "2006-03-01", "2006-04-01", "2006-05-01", "2006-06-01", "2006-07-01", "2006-08- 01", "2006-09-01", "2006-10-01", "2006-11-01", "2006-12-01", "2007-01-01", "2007-02-01", "2007-03-01", "2007-04-01", "2007-05-01", " 2007-06-01", "2007-07-01", "2007-08-01", "2007-09-01", "2007-10-01", "2007-11-01", "2007-12-01", "2008-01-01", "2008-02-01", "2008- 03-01", "2008-04-01", "2008-05-01", "2008-06-01", "2008-07-01", "2008-08-01", "2008-09-01", "2008-10-01", "2008-11-01", "2008-12-01" , "2009-01-01", "2009-02-01", "2009-03-01", "20 09-04-01", "2009-05-01", "2009-06-01", "2009-07-01", "2009-08-01", "2009-09-01", "200 9-10-01", "2009-11-01", "2009-12-01", "2010-01-01", "2010-02-01", "2010-03-01", "2010-04-01", "2010-05-01", "2010-06-01", "2010-07- 01", "2010-08-01", "2010-09-01", "2010-10-01", "2010-11-01", "2010-12-01", "2011-01-01", "2011-02-01", "2011-03-01", "2011-04-01", " 2011-05-01", "2011-06-01", "2011-07-01", "2011-08-01", "2011-09-01", "2011-10-01", "2011-11-01", "2011-12-01", "2012-01-01", "2012-0 2-01", "2012-03-01", "2012-04-01", "2012-05-01", "2012-06-01", "2012-07-01", "2012-08-01", "20 12-09-01", "2012-10-01", "2012-11-01" , "2012-12-01", "2013-01-01", "2013-02-01", "2013-03-01", "2013-04-01", "2013-05-01", "2013-06-01", "2013-07-01", "2013-08-01", "201 3-09-01", "2013-10-01", "2013-11-01", "2013-12-01", "2014-01-01", "2014-02-01", "2014-03-01", "2014-04-01", "2014-05-01", "2014-06- 01", "2014-07-01", "2014-08-01", "2014-09-01", "2014-10-01", "2014-11-01", "2014-12-01", "2015-01-01", "2015-02-01", "2015-03-01", "2 015-04-01", "2015-05-01", "2015-06-01", "2015-07-01", "2015-08-01", "2015-09-01", "2015-10-01", "2015-11-01", "2015-12-01", "2016-01 -01", "20 16.02.01", "2016.03.01", "2016.04.01", "2016.05.01", "2016.06.01", "2016.07.01", "2016.08.01", "2016.09.01", "2016.10.01", "2016-11-01", "2016-12-01", "2017-01-01", "2017-02-01", "2017-03-01", "2017-04-01", "2017-05-01", "2017-06-01", "2017-07-01", "2017 -08-01", "2017-09-01", "2017-10-01", "2017-11-01", "2017-12-01", "2018-01-01", "2018-02-01", "2018-03-01", "2018-04-01", "2018-05-0 1", "2018-06-01", "2018-07-01", "2018-08-01", "2018-09-01", "2018-10-01", "2018-11-01", "2018-12-01", "2019-01-01", "2019-02-01", "20 19-03-01", "2019-04-01", "2019-05-01", "2019-06-01", "20 19-07-01", "2019-08-01", "2019-09-01", "2019-10-01", "2019-11-01", "2019-12 -01"); gidin ps_TurnoverHour bölüm şemasını pf_TurnoverHour bölümü olarak oluşturun all to (); iyi git ve kümelenmiş dizin zaten bizim tarafımızdan yalnızca oluşturulan bölümleme şemasında biliniyor: dbo'da benzersiz kümelenmiş dizin uix_TurnoverHour oluşturun. ps_TurnoverHour(dt) üzerinde (data_compression=page) ile CiroHour (StorehouseID, ProductID, dt); --- 19 dk Ve şimdi elimizde ne var bir bakalım. İsteğin kendisi: set dateformat ymd; @start datetime = "2015-01-02", @finish datetime = "2015-01-03" beyan @start_month datetime = convert(datetime,convert(varchar(9),@start,120)+"1",120) arasından * seçin (dt, StorehouseID, ProductId, Quantity, sum(Quantity) over (StorehouseID'ye göre bölümleme, dt'ye göre ProductID siparişi) dbo'dan Bakiye olarak seçin .TurnoverHour with(noexpand) burada dt, @start_month ve @finish arasında) tmp olarak burada dt >= @start order by StorehouseID, ProductID, dt seçeneği(yeniden derleme);
SQL Server çalışma süresi:
CPU süresi = 7860ms, geçen süre = 1725ms.
SQL Server ayrıştırma ve derleme süresi:
CPU süresi = 0ms, geçen süre = 0ms.
Sorgu planı maliyeti = 9,4
Aslında, bir bölümdeki veriler oldukça hızlı bir şekilde kümelenmiş dizin tarafından seçilir ve taranır. Burada şunu da eklemek gerekir ki, istek parametreleştirildiğinde, parametre koklamanın hoş olmayan etkisi ortaya çıkıyor, seçenek (yeniden derleme) işleniyor.
Bu başka bir yaygın sorundur. Temel ilke, bir özelliğin (toplu öğe) değerlerini, başka bir öznitelik veya özniteliklere (sıralama öğesi) göre sıralamaya dayalı olarak, muhtemelen başka bir öznitelik veya özniteliklere (bölümleme öğesi) göre tanımlanmış satır bölümleriyle toplamaktır. Gerçek hayatta, banka bakiyelerini hesaplamak, ürünlerin stokta olup olmadığını veya cari satış rakamlarını takip etmek gibi hareketli toplamları hesaplamanın birçok örneği vardır.
SQL Server 2012'den önce, çalışan toplamları hesaplamak için kullanılan set tabanlı çözümler son derece kaynak yoğundu. Bu nedenle, insanlar genellikle yavaş, ancak bazı durumlarda yine de set tabanlı çözümlerden daha hızlı olan yinelemeli çözümlere yöneldiler. SQL Server 2012'deki pencere işlevi desteğinin geliştirilmesiyle, çalışan toplamlar, aşağıdakilere dayalı eski çözümlerden çok daha iyi performans gösteren basit küme tabanlı kod kullanılarak hesaplanabilir. T-SQL tabanlı- hem küme tabanlı hem de yinelemeli. Yeni bir çözüm gösterip bir sonraki bölüme geçebilirdim; ancak değişikliğin kapsamını gerçekten anlamanız için eski yöntemleri anlatacağım ve performanslarını yeni yaklaşımla karşılaştıracağım. Doğal olarak, sadece anlatan ilk kısmı okuma hakkına sahipsiniz. yeni yaklaşım ve makalenin geri kalanını atlayın.
Farklı çözümleri göstermek için hesap bakiyelerini kullanacağım. İşlemler tablosunu az miktarda test verisi ile oluşturan ve dolduran kod aşağıdadır:
NOCOUNT'U AÇIK AYARLAYIN; TSQL2012 KULLANIN; OBJECT_ID("dbo.Transactions", "U") IS NULL DEĞİLSE DROP TABLE dbo.Transactions; CREATE TABLE dbo.Transactions (actid INT NOT NULL, -- bölümleme sütun tranid INT NOT NULL, -- sıralama sütun val MONEY NOT NULL, -- ölçü CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid)); GO -- küçük test durumu INSERT INTO dbo.Transactions(actid, tranid, val) VALUES (1, 1, 4.00), (1, 2, -2.00), (1, 3, 5.00), (1, 4, 2.00), (1, 5, 1.00), (1, 6, 3.00), (1, 7, -4.00), (1 , 8 , -1,00), (1, 9, -2,00), (1, 10, -3,00), (2, 1, 2,00), (2, 2, 1,00), (2, 3, 5,00), (2, 4, 1,00), (2, 5, -5,00), (2, 6, 4,00), (2, 7, 2,00), (2, 8, - 4,00), (2, 9, -5,00), (2, 10, 4,00), (3, 1, -3,00), (3, 2, 3,00), (3, 3, -2,00), (3, 4, 1,00), (3, 5, 4,00), (3, 6, -1,00), (3, 7, 5,00), (3, 8, 3. 00), (3, 9, 5.00), (3, 10, -3.00);
Tablonun her satırı, hesaptaki bir banka işlemini temsil eder. Para yatırma işlemleri, val sütununda pozitif değere sahip işlemler olarak, para çekme işlemleri ise negatif işlem değeri olarak işaretlenir. Görevimiz, tranid sütununa göre sıralanmış val satırındaki işlem toplamlarını biriktirerek her an hesaptaki bakiyeyi hesaplamaktır ve bu her hesap için ayrı ayrı yapılmalıdır. İstenen sonuç şöyle görünmelidir:
Her iki çözüm de test etmek için daha fazla veri gerektirir. Bu, aşağıdaki gibi bir sorgu ile yapılabilir:
DECLARE @num_partitions AS INT = 10, @rows_per_partition AS INT = 10000; TRUNCATE TABLE dbo.İşlemler; dbo.Transactions INTO (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM(NEWID())%5)) FROM dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN dbo.GetN ums( 1, @rows_per_partition) RPP olarak;
Bir bölümdeki bölüm (hesap) ve satır (işlem) sayısını değiştirmek için girişlerinizi ayarlayabilirsiniz.
Pencere İşlevlerini Kullanan Küme Tabanlı Çözüm
SUM toplama penceresi işlevini kullanan küme tabanlı bir çözümle başlayacağım. Buradaki pencerenin tanımı oldukça açık: pencereyi actid'e göre bölmeniz, tranide göre sıralamanız ve çerçevedeki satırları en alttan (SINIRSIZ ÖNCEKİ) geçerli olana doğru filtrelemeniz gerekiyor. İşte ilgili talep:
dbo.Transactions'DAN bakiye OLARAK actid, tranid, val, SUM(val) OVER(SINIRSIZ ÖNCEKİ VE GEÇERLİ SATIR ARASINDA tranid SATIRLARA GÖRE actid ORDER BÖLÜMÜ) SEÇİN;
Bu kod yalnızca basit ve anlaşılır olmakla kalmaz, aynı zamanda hızlı çalışır. Bu sorgunun planı şekilde gösterilmiştir:
Tablo, POC uyumlu ve pencere işlevleri tarafından kullanılabilen kümelenmiş bir dizine sahiptir. Spesifik olarak, dizin anahtarları listesi, bir bölümleme öğesini (actid) ve ardından bir sıralama öğesini (tranid) temel alır ve kapsam sağlamak için dizin, sorgudaki (val) diğer tüm sütunları içerir. Plan, sıralı bir aramayı, ardından dahili bir satır numarası hesaplamasını ve ardından bir pencere toplamını içerir. Bir POC dizini olduğundan, optimize edicinin plana sıralama operatörü eklemesine gerek yoktur. Bu çok etkili bir plandır. Ek olarak, doğrusal olarak ölçeklenir. Daha sonra performans karşılaştırmasının sonuçlarını gösterdiğimde bu yöntemin eski çözümlere göre ne kadar verimli olduğunu göreceksiniz.
SQL Server 2012'den önce, alt sorgular veya birleştirmeler kullanılıyordu. Yuvalanmış bir sorgu kullanırken, çalışan toplamlar, dış satırla aynı actid değerine ve dış satırdaki değerden küçük veya ona eşit bir tranid değerine sahip tüm satırları filtreleyerek hesaplanır. Toplama daha sonra filtrelenmiş satırlara uygulanır. İşte ilgili talep:
Bağlantılar kullanılarak benzer bir yaklaşım uygulanabilir. Aynı yüklem, birleştirmenin ON yan tümcesindeki alt sorgunun WHERE yan tümcesinde olduğu gibi kullanılır. Bu durumda, T1 olarak belirlenen örnekte aynı A hesabının N'inci işlemi için, T2 örneğinde 1'den N'ye uzanan işlem numaralarıyla N eşleşme bulacaksınız. Sonuç olarak, T1'deki satırlar tekrarlanır, dolayısıyla geçerli işlem hakkında bilgi almak için satırları T1'deki tüm öğeler arasında gruplandırmanız ve çalışan toplamı hesaplamak için T2'den val özniteliğine toplama uygulamanız gerekir. Tamamlanan istek şöyle görünür:
dbo.Transactions AS T1 JOIN dbo.Transactions AS T2 ON T2.actid = T1.actid VE T2.tranid'DEN T1.actid, T1.tranid, T1.val, SUM(T2.val) AS bakiyesini SEÇİN<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;
Aşağıdaki şekil, her iki çözüm için planları göstermektedir:
Her iki durumda da, kümelenmiş dizinin tam taramasının T1 örneğinde gerçekleştirildiğini unutmayın. Daha sonra plandaki her satır için endeksin bitiş sayfasındaki cari hesap bölümünün başındaki endekste T2.tranid'in T1.tranid'den küçük veya ona eşit olduğu tüm işlemlerin okunduğu bir arama işlemi yapılır. Planlarda satır birleştirmenin gerçekleştiği nokta biraz farklı olmakla birlikte okunan satır sayısı aynıdır.
Kaç satırın görüntülendiğini anlamak için veri öğelerinin sayısı dikkate alınmalıdır. P, bölümlerin (hesapların) sayısı ve r, bir bölümdeki (işlem) satır sayısı olsun. İşlemlerin hesaplar arasında eşit olarak dağıldığını varsayarsak, tablodaki satır sayısı yaklaşık olarak p * r'ye eşittir. Böylece, üstteki tarama p*r satırları kapsar. Ama en önemlisi, İç İçe Döngüler yineleyicide ne olduğuyla ilgileniyoruz.
Her bölümde planda 1 + 2 + ... + r satır okur yani toplamda (r + r*2) / 2'dir.Planlarda işlenen toplam satır sayısı p*r + p*(r + r2) / 2'dir.Bu, plandaki işlem sayısının kare boyutuyla büyüdüğü anlamına gelir, yani bölüm boyutunu f kat artırırsanız, iş miktarı yaklaşık f 2 kat artar. Bu kötü. Örneğin, 100 satır 10 bin satıra karşılık gelir ve bin satır bir milyon satıra karşılık gelir vb. Basitçe söylemek gerekirse, ikinci dereceden işlev çok hızlı büyüdüğü için bu, oldukça büyük bir bölüm boyutuyla sorgu yürütmede güçlü bir yavaşlamaya yol açar. Bu tür çözümler, bölüm başına birkaç düzine satır için tatmin edici bir şekilde çalışır, ancak artık işe yaramaz.
İmleç Çözümleri
İmleç tabanlı çözümler kafa kafaya uygulanır. Bir imleç, verileri actid ve tranid'e göre sıralayan bir sorguya dayalı olarak bildirilir. Bundan sonra, imleç kayıtlarının yinelemeli geçişi gerçekleştirilir. Yeni bir sayı bulunduğunda, toplamı içeren değişken sıfırlanır. Her yinelemede, yeni işlemin miktarı değişkene eklenir ve ardından satır, geçerli işlem ve çalışan toplamın geçerli değeri hakkında bilgi içeren bir tablo değişkeninde saklanır. Yineleme geçişinden sonra, tablo değişkeninin sonucu döndürülür. İşte tamamlanmış çözümün kodu:
DECLARE @Result AS TABLE (actid INT, tranid INT, val MONEY, balance MONEY); @actid INT OLARAK, @prvactid INT OLARAK, @tranid INT OLARAK, @val PARA OLARAK, @balance PARA OLARAK BİLDİRİN; C İMLEÇİNİ FAST_FORWARD DECLARE FOR SELECT İÇİN actid, tranid, val FROM dbo.Transactions ORDER BY actid, tranid; AÇIK C FETCH NEXT FROM C INTO @actid, @tranid, @val; SEÇ @prvactid = @actid, @balance = 0; WHILE @@fetch_status = 0 BEGIN IF @actid<>@prvactid SEÇ @prvactid = @actid, @balance = 0; SET @balance = @balance + @val; @Sonuç DEĞERLERİNE EKLE(@actid, @tranid, @val, @balance); FETCH NEXT FROM C INTO @actid, @tranid, @val; SON KAPAT C; C AYIRIN; @Sonuçtan * SEÇİN;
İmleci kullanan sorgulama planı şekilde gösterilmiştir:
Dizinden gelen veriler yalnızca belirli bir sırayla tarandığından bu plan doğrusal olarak ölçeklenir. Ayrıca, imleçten bir satır alma işlemi yaklaşık olarak aynı satır başına maliyete sahiptir. İmlecin bir satırının işlenmesiyle oluşan yükü g'ye eşit alırsak, bu çözümün maliyeti p*r + p*r*g olarak tahmin edilebilir (unutmayın, p bölüm sayısıdır ve r bölümdeki satır sayısıdır). Yani, bölüm başına satır sayısını f kat artırırsanız, sistemdeki yük p*r*f + p*r*f*g olur, yani doğrusal olarak büyür. Satır başına işlem maliyeti yüksektir, ancak ölçeklendirmenin doğrusal doğası nedeniyle, belirli bir bölüm boyutundan itibaren bu çözüm, bu çözümlerin ikinci dereceden ölçeklendirmesi nedeniyle iç içe sorgu ve birleştirme tabanlı çözümlerden daha iyi ölçeklenecektir. Performans ölçümüm, imleç çözümünün bölüm başına birkaç yüz satır daha hızlı olduğunu gösterdi.
İmleç tabanlı çözümlerin sağladığı performans avantajlarına rağmen, ilişkisel olmadıkları için genellikle bunlardan kaçınılmalıdır.
CLR tabanlı çözümler
dayalı bir olası çözüm CLR (Ortak Dil Çalışma Zamanı) temelde imleç tabanlı bir çözüm biçimidir. Aradaki fark, bir sonraki satırı almak ve yinelemek için çok fazla kaynak gerektiren bir T-SQL imleci kullanmak yerine, çok daha hızlı olan .NET SQLDataReader ve .NET yinelemelerinin kullanılmasıdır. CLR'nin bu seçeneği daha hızlı yapan bir özelliği, sonuç satırının geçici bir tabloda gerekli olmamasıdır - sonuçlar doğrudan çağıran işleme gönderilir. CLR tabanlı çözüm mantığı, imleç ve T-SQL tabanlı çözüm mantığına benzer. Karar saklı yordamı tanımlayan C# kodu aşağıdadır:
Sistemin Kullanılması; System.Data kullanarak; System.Data.SqlClient kullanarak; System.Data.SqlTypes kullanarak; Microsoft.SqlServer.Server kullanarak; public kısmi sınıf StoredProcedures ( public static void AccountBalances() ( (SqlConnection conn = new SqlConnection("context connection=true;")) kullanarak ( SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = @"" + "ACTID, tranid, val " + "dbo.Transactions'DAN " + "ORDER BY actid, tranid; "; SqlMetaData sütunları = yeni SqlMetaData; sütunlar = yeni SqlMetaData("actid" , SqlDbType.Int); sütunlar = yeni SqlMetaData("tranid" , SqlDbType.Int); sütunlar = yeni SqlMetaData("val" , SqlDbType.Money); sütunlar = yeni SqlMetaData("denge", Sql DbType.Money); Sql DataRecord kaydı = yeni SqlDataRecord(sütunlar); SqlContext.Pipe.SendResultsStart(kayıt); conn.Open(); SqlDataReader okuyucu = comm.ExecuteReader(); SqlInt32 prvactid = 0; SqlMoney bakiyesi = 0; while (reader.Read()) ( SqlInt32 actid = reader.Get SqlInt32(0);Sq lMoney val = reader.GetSqlMoney(2);if (actid == özel) ( bakiye += val; ) else ( bakiye = val; ) prvactid = actid;record.SetSqlInt32(0, reader.GetSqlInt32(0)); record.SetSqlInt32(1, okuyucu.GetSqlInt32(1)); record.SetSqlMoney(2, val); record.SetSqlMoney(3, bakiye); SqlContext.Pipe.SendResultsRow(kayıt); ) SqlContext.Pipe.SendResultsEnd(); ) ) )
Bu saklı yordamı SQL Server'da yürütebilmek için, önce bu koda dayalı olarak AccountBalances adlı bir derleme oluşturmanız ve bunu bir TSQL2012 veritabanına dağıtmanız gerekir. SQL Server'da derlemeleri dağıtmaya aşina değilseniz, makaledeki "Stored Procedures and the Common Language Runtime" bölümünü okuyabilirsiniz. "Saklı Prosedürler".
Derlemeye AccountBalances adını verdiyseniz ve derleme dosyasının yolu "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll" ise, derlemeyi veritabanına yükleyebilir ve saklı yordamı aşağıdaki kodla kaydedebilirsiniz:
"C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll" DAN MONTAJ AccountBalances OLUŞTURUN; PROSEDÜR OLUŞTURMAYA GİDİN dbo.AccountBalances HARİCİ ADI OLARAK AccountBalances.StoredProcedures.AccountBalances;
Montaj dağıtıldıktan ve prosedür kaydedildikten sonra, aşağıdaki kodla çalıştırabilirsiniz:
YÖNETİCİ dbo.AccountBalances;
Dediğim gibi, SQLDataReader başka bir imleç biçimidir, ancak bu sürümde, satır okuma ek yükü, T-SQL'de geleneksel bir imleç kullanmaktan çok daha azdır. Ayrıca .NET'te yinelemeler T-SQL'den çok daha hızlıdır. Böylece, CLR tabanlı çözümler de doğrusal olarak ölçeklenir. Testler, bu çözümün performansının, bölümdeki satır sayısı 15'i geçtiğinde alt sorgular ve birleştirmeler kullanan çözümlerin performansından daha yüksek olduğunu göstermiştir.
Bittiğinde, aşağıdaki temizleme kodunu çalıştırın:
DÜŞÜRME PROSEDÜRÜ dbo.AccountBalances; DÜŞÜRME MONTAJ Hesap Bakiyeleri;
İç içe yinelemeler
Bu noktaya kadar yinelemeli ve set tabanlı çözümler gösterdim. Aşağıdaki çözüm, yinelemeli ve küme tabanlı yaklaşımların bir melezi olan iç içe yinelemelere dayanmaktadır. Buradaki fikir, önce kaynak tablodaki (bizim durumumuzda banka hesapları) satırları, ROW_NUMBER işlevi kullanılarak hesaplanan rownum adlı yeni bir öznitelikle birlikte geçici bir tabloya kopyalamaktır. Satır numaraları actid tarafından bölümlenir ve tranid tarafından sıralanır, böylece her banka hesabındaki ilk işleme 1 numara atanır, ikinci işleme 2 numara atanır vb. Ardından, geçici tabloda bir anahtar listesiyle (rownum, actid) kümelenmiş bir dizin oluşturulur. Ardından, tüm sayımlarda yineleme başına bir satırı işlemek için özyinelemeli bir CTE veya özel olarak hazırlanmış bir döngü kullanır. Daha sonra, geçerli satıra karşılık gelen değer önceki satırla ilişkilendirilmiş değerle toplanarak devam eden toplam hesaplanır. İşte özyinelemeli bir CTE kullanarak bu mantığın bir uygulaması:
dbo.Transactions'DAN #Transactions INTO Rownum OLARAK actid, tranid, val, ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) SEÇİN; #Transactions(rownum, actid); C AS İLE (SEÇ 1 AS rownum, actid, tranid, val, val AS sumqty FROM #Transactions WHERE rownum = 1 UNION ALL SELECT PRV.rownum + 1, PRV.actid, CUR.tranid, CUR.val, PRV.sumqty + CUR.val FROM C AS PRV JOIN #Transactions AS CUR ON CUR.rownum = PRV.rownum + 1 AND C UR.actid = PRV.actid) C SEÇENEĞİNDEN actid, tranid, val, sumqty SEÇİN (MAXRECURSION 0); DROP TABLE #İşlemler;
Ve bu, açık bir döngü kullanan uygulamadır:
dbo.Transactions'DAN #Transactions; #Transactions(rownum, actid); @rownum'u INT OLARAK BİLDİRİN; SET @rownum = 1; 1 = 1 BAŞLANGIÇ AYARI İLE @rownum = @rownum + 1; CUR.rownum = @rownum VE PRV.rownum = @rownum - 1 VE CUR.actid = PRV.actid; IF @@satır sayısı = 0 BREAK; END #İşlemlerden actid, tranid, val, sumqty SEÇİN; DROP TABLE #İşlemler;
Bu çözüm, bölümlerde az sayıda satır bulunan çok sayıda bölüm olduğunda iyi performans sağlar. Daha sonra yineleme sayısı azdır ve asıl iş, çözümün bir satır numarasıyla ilişkili satırları önceki satır numarasıyla ilişkili satırlarla birleştiren küme tabanlı kısmı tarafından yapılır.
Değişkenlerle çok satırlı güncelleme
Bu noktaya kadar gösterilen çalışan toplamları hesaplama hilelerinin doğru sonucu vermesi garanti edilir. Bu bölümde açıklanan metodoloji belirsizdir çünkü sistemin belgelenmiş davranışından ziyade gözlemlenen davranışına dayanır ve aynı zamanda görelilik ilkeleriyle de çelişir. Yüksek çekiciliği, yüksek çalışma hızından kaynaklanmaktadır.
Bu yöntem, değişkenlerle bir UPDATE ifadesi kullanır. UPDATE deyimi, bir sütunun değerine göre değişkenlere ifadeler atayabilir ve ayrıca değişkenli bir ifadeye sütunlardaki değerleri atayabilir. Çözüm, actid, tranid, val ve balance özniteliklerine sahip İşlemler adlı geçici bir tablo ve bir anahtar listesi (actid, tranid) içeren kümelenmiş bir dizin oluşturarak başlar. Geçici tablo daha sonra orijinal İşlemler veritabanındaki tüm satırlarla doldurulur ve tüm satırların bakiye sütununa 0,00 değeri girilir. Ardından, çalışan toplamı hesaplamak ve hesaplanan değeri bakiye sütununa eklemek için geçici tabloyla ilişkili değişkenlerle birlikte bir UPDATE ifadesi çağrılır.
@prevaccount ve @prevbalance değişkenleri kullanılır ve bakiye sütunundaki değer aşağıdaki ifade kullanılarak hesaplanır:
SET @prevbalance = balance = CASE WHEN actid = @prevaccount THEN @prevbalance + val BAŞKA val END
CASE ifadesi, cari ve önceki hesap kimliklerinin aynı olup olmadığını kontrol eder ve eşitlerse bakiye sütunundaki önceki ve cari değerlerin toplamını döndürür. Hesap ID'leri farklı ise cari işlem tutarı iade edilir. Ardından, CASE ifadesinin sonucu denge sütununa eklenir ve @prevbalance değişkenine atanır. Ayrı bir ifadede, ©prevaccount değişkenine cari hesabın kimliği atanır.
UPDATE ifadesinden sonra çözüm, geçici tablodaki satırları sunar ve ikincisini siler. İşte tamamlanmış çözümün kodu:
TABLO OLUŞTUR #İşlemler (aktif INT, tranid INT, val PARA, bakiye PARA); CLUSTERED INDEX OLUŞTUR idx_actid_tranid ON #Transactions(actid, tranid); (TABLOCK) İLE #İşlemlere EKLE (actid, tranid, val, balance) dbo'DAN actid, tranid, val, 0.00 SEÇİN. İşlemler ORDER BY actid, tranid; @prevaccount INT OLARAK, @prevbalance PARA OLARAK BİLDİRİN; UPDATE #Transactions SET @prevbalance = balance = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END, @prevaccount = actid FROM #Transactions WITH(INDEX(1), TABLOCKX) SEÇENEĞİ (MAXDOP 1); #İşlemlerden * SEÇİN; DROP TABLE #İşlemler;
Bu çözümün planı aşağıdaki şekilde gösterilmektedir. İlk kısım bir INSERT deyimidir, ikinci kısım bir UPDATE deyimidir ve üçüncü kısım bir SELECT deyimidir:
Bu çözüm, UPDATE yürütmesini optimize ederken, her zaman kümelenmiş dizinin sıralı bir taramasının gerçekleştirileceğini varsayar ve çözüm, eşzamanlılık gibi buna müdahale edebilecek durumları önlemek için bir dizi ipucu içerir. Sorun şu ki, optimize edicinin her zaman kümelenmiş dizin sırasına göre bakacağına dair resmi bir garanti yoktur. Kodda tanım gereği bu tür davranışı garanti edebilecek mantıksal öğeler olmadıkça, kodun mantıksal doğruluğunu sağlamak için fiziksel bilgi işlemin özelliklerine güvenemezsiniz. Bu kodda tam olarak bu davranışı garanti edebilecek hiçbir mantıksal özellik yoktur. Doğal olarak bu yöntemi kullanıp kullanmamak tamamen sizin vicdanınıza kalmıştır. Binlerce kez kontrol etmiş ve "her şey olması gerektiği gibi çalışıyor" gibi görünse bile, onu kullanmanın sorumsuzca olduğunu düşünüyorum.
Neyse ki, SQL Server 2012'de bu seçim neredeyse gereksiz hale geliyor. Pencereli toplama işlevlerini kullanan son derece verimli bir çözümle, başka çözümler düşünmeniz gerekmez.
performans ölçümü
Çeşitli tekniklerin performansını ölçtüm ve karşılaştırdım. Sonuçlar aşağıdaki şekillerde gösterilmektedir:
Sonuçları iki grafiğe ayırdım çünkü iç içe sorgu veya birleştirme yöntemi o kadar yavaştı ki bunun için farklı bir ölçek kullanmak zorunda kaldım. Her durumda, çoğu çözümün iş miktarı ile bölümün boyutu arasında doğrusal bir ilişki gösterdiğini ve yalnızca iç içe bir sorguya veya birleştirmeye dayalı çözümün ikinci dereceden bir ilişki gösterdiğine dikkat edin. Pencereli toplama işlevine dayalı yeni çözümün ne kadar verimli olduğu da açık. UPDATE tabanlı değişkenli çözüm de çok hızlıdır, ancak daha önce açıklanan nedenlerden dolayı onu kullanmanızı önermiyorum. CLR çözümü de oldukça hızlıdır, ancak tüm bu .NET kodunu yazmanızı ve derlemeyi veritabanına dağıtmanızı gerektirir. Nasıl bakarsanız bakın, pencere kümelerini kullanan set tabanlı çözüm tercih edilen çözüm olmaya devam ediyor.
SQL 2003 sözdizimi tüm platformlarda desteklenir.
KAT (ifade)
İşleve pozitif bir sayı iletirseniz, işlevin eylemi ondalık noktadan sonraki her şeyi kaldırmak olacaktır.
Dual'den KAT(100.1) SEÇİN;
Ancak, negatif sayılar söz konusu olduğunda aşağı yuvarlamanın mutlak değerde bir artışa karşılık geldiğini unutmayın.
KAT SEÇİN(-100.1) ikiliden;
ZEMİN işlevinin ters etkisini elde etmek için TAVAN işlevini kullanın.
LN
LN işlevi, bir sayının doğal logaritmasını, yani sonuç olarak verilen sayıyı elde etmek için matematiksel sabit e'nin yükseltilmesi gereken kuvveti (yaklaşık 2,718281) döndürür.
SQL 2003 sözdizimi
LN (ifade)
DB2, Oracle, PostgreSQL
DB2, Oracle ve PostgreSQL platformları, LN işlevi için SQL 2003 sözdizimini desteklerken, DB2 ve PostgreSQL de LN'nin eşanlamlısı olarak LOG işlevini destekler.
MySQL ve SQL Sunucusu
MySQL ve SQL Server'ın kendi doğal günlük işlevi olan LOG vardır.
GÜNLÜK (ifade)
Aşağıdaki Oracle örneği, yaklaşık olarak bir matematiksel sabite eşit olan bir sayının doğal logaritmasını hesaplar.
ikiliden LN(2.718281) SEÇİN;
Ters işlemi gerçekleştirmek için EXP işlevini kullanın.
mod
MOD işlevi, bölüneni bölene böldükten sonra kalanı verir. Tüm platformlar, SQL 2003 MOD deyimi sözdizimini destekler.
SQL 2003 sözdizimi
MOD (temettü, bölen)
Standart MOD işlevi, bölüneni bölene böldükten sonra kalanı elde etmek için tasarlanmıştır. Bölen sıfırsa, bölünen iade edilir.
Aşağıda MOD işlevini bir SELECT deyiminde nasıl kullanabileceğiniz gösterilmektedir.
2 SAYILARDAN MOD(12, 5) SEÇİN;
KONUM
POSITION işlevi, arama dizesindeki dizenin başlangıç konumunu gösteren bir tamsayı döndürür.
SQL 2003 sözdizimi
KONUM(satır 1 IN satır 2)
Standart POSITION işlevi, arama dizesinde (dize!) belirli bir dizenin (dize!) ilk geçtiği konumu elde etmek için tasarlanmıştır. Dize ise işlev 0 döndürür! dizgesinde oluşmaz ve bağımsız değişkenlerden herhangi biri NULL ise NULL.
DB2
DB2, eşdeğer bir POSSTR işlevine sahiptir.
MySQL
MySQL platformu, SQL 2003 standardına göre POZİSYON işlevini destekler.
GÜÇ
GÜÇ işlevi, bir sayıyı belirtilen bir kuvvete yükseltmek için kullanılır.
SQL 2003 sözdizimi
GÜÇ (taban, üs)
Bu işlevin yürütülmesinin sonucu, gösterge tarafından belirlenen güce yükseltilen tabandır. Taban negatifse, üs bir tam sayı olmalıdır.
DB2, Oracle, PostgreSQL ve SQL Server
Bu satıcıların tümü SQL 2003 sözdizimini destekler.
kehanet
Oracle'ın eşdeğer bir INSTR işlevi vardır.
postgresql
PostgreSQL platformu, SQL 2003 standardına göre POSITION işlevini destekler.
MySQL
MySQL platformu, bu POW anahtar sözcüğünü değil, bu işlevi destekler.
P0W (taban, üs)
Pozitif bir sayıyı bir güce yükseltmek oldukça açıktır.
Dual'den GÜÇ(10.3) SEÇİN;
0'ın kuvvetine yükseltilmiş herhangi bir sayı 1'dir.
ikiliden GÜÇ(0.0) SEÇİN;
Negatif bir üs, ondalık noktayı sola kaydırır.
Dual'den GÜÇ(10, -3) SEÇİN;
DÜZENLEMEK
KAREKÖK işlevi, bir sayının karekökünü döndürür.
SQL 2003 sözdizimi
Tüm platformlar SQL 2003 sözdizimini destekler.
SIRALAMA (ifade)
ikiliden KAREKÖK(100) SEÇİN;
KOVA GENİŞLİĞİ
GENİŞLİK BUCKET işlevi, eşit genişlikte bir histogramın sütunlarına değerler atar.
SQL 2003 sözdizimi
Aşağıdaki sözdiziminde ifade, histogramın sütununa atanan bir değerdir. Tipik olarak, ifade, sorgu tarafından döndürülen tablonun bir veya daha fazla sütununu temel alır.
GENİŞLİK BUCKET(ifade, min, maks, histogram_sütunları)
Histogram sütunları parametresi, minimum ila maksimum aralığında oluşturulacak histogram sütunlarının sayısını gösterir. Min parametresinin değeri aralığa dahildir ve değer maksimum parametre açılmıyor. İfade değeri, histogram sütunlarından birine atanır ve ardından işlev, karşılık gelen histogram sütununun numarasını döndürür. İfade, belirtilen sütun aralığına girmiyorsa, ifadenin min'den küçük veya max'tan büyük veya eşit olmasına bağlı olarak işlev, 0 veya max + 1 döndürür.
Aşağıdaki örnek, 1'den 10'a kadar olan tamsayı değerlerini bir histogramın iki sütunu arasında dağıtır.
Bir sonraki örnek daha ilginç. 1'den 10'a kadar olan 11 değer, aralığa dahil olan minimum değer ile aralığa dahil olmayan maksimum değer arasındaki farkı göstermek için histogramın üç sütunu arasında dağıtılır.
pivottan x, WIDTH_BUCKET(x, 1.10.3) SEÇİN;
cX=, X= 9.9 ve X-10 sonuçlarına özellikle dikkat edin.Onların girdi parametresi değeri, yani bu örnekte 1, sütun #1 x >= min olarak tanımlandığından, aralığın alt ucunu gösteren ilk sütuna düşer. Ancak max parametresinin giriş değeri maksimum sütununda yer almaz. Bu örnekte 10 sayısı max + 1 olmak üzere taşma sütununa düşüyor. 9.9 değeri 3 numaralı sütuna düşüyor ve bu, aralığın üst sınırının x olarak tanımlandığı kuralını gösteriyor.< max.