Programlama. Oracle. Analitik fonksiyonlar

Bu Oracle öğreticisi, Oracle / PLSQL'in nasıl kullanılacağını açıklar lAG işlevi sözdizimi ve örneklerle.

Açıklama

Oracle / PLSQL LAG işlevi, bir tabloya eklenmeden aynı anda bir tablodaki birden fazla satırı sorgulamanıza izin veren analitik bir işlevdir. Bu, tablodaki önceki satırın değerlerini döndürür. Sonraki satırdan bir değer döndürmek için LEAD işlevini kullanmayı deneyin.

Sözdizimi

Oracle / PLSQL LAG işlevinin sözdizimi şöyledir:

LAG (ifade [, uzaklık [, varsayılan]])
fazla ([sorgu_bölümü_clause] order_by_clause)

Parametreler veya bağımsız değişkenler

ifade, diğer yerleşik işlevleri içerebilen, ancak analitik işlevleri içeremeyen bir ifadedir.

ofset isteğe bağlıdır. Bu, tablodaki geçerli satırın fiziksel ofsetidir. Bu parametre belirtilmezse, varsayılan 1'dir.

varsayılan isteğe bağlıdır. Bu, ofset tablonun dışındaysa döndürülen değerdir. Bu parametre belirtilmezse, varsayılan olarak Null olur.

query_partition_clause İsteğe bağlı. Sonuçları bir veya daha fazla ifadeye göre gruplara ayırmak için kullanılır.

order_by_clause isteğe bağlıdır. Her bölümdeki verileri düzenlemek için kullanılır.

LAG işlevi, tablodaki önceki satırın değerlerini döndürür.

Uygulama

LAG işlevi Oracle / PLSQL'in aşağıdaki sürümlerinde kullanılabilir:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Misal

LAG işlevi Oracle / PLSQL'de kullanılabilir.

Bir örneğe bakalım. Aşağıdaki verileri içeren bir sipariş tablomuz varsa:

SİPARİŞ TARİHİ	ÜRÜN KİMLİĞİ	MİKTAR
25/09/2007	1000	20
26/09/2007	2000	15
27/09/2007	1000	8
28/09/2007	2000	12
29/09/2007	2000	2
30/09/2007	1000	4

Ve aşağıdaki isteği yerine getireceğiz:

Oracle PL / SQL

siparişlerden ürün_kimliği, sipariş_tarihi, LAG (sipariş_tarihi, 1) üzerinden (sipariş_tarihine göre SİPARİŞ) AS prev_order_date seçin;

1 yerine ofset \u003d 2 kullansaydık, sorgu ORDER_DATE 2 konum daha önce döndürürdü. Offset \u003d 3 kullansaydık, sorgu ORDER_DATE 3 pozisyon erken döndürürdü…. ve bunun gibi.

Yalnızca belirli bir ürün kimliği için sipariş almak istiyorsak, aşağıdaki SQL sorgusunu yürüteceğiz:

Oracle PL / SQL

Sonucu alıyoruz:

Bu örnekte, LAG işlevi sonuçları ürün_kimliği'ne göre böler ve sonra da belirtildiği gibi sipariş_tarihine göre sıralar. SİPARİŞ_tarihine göre ürün_kimliğine göre bölümlendirme... Bu, LAG işlevinin yalnızca, ürün_kimliği geçerli kaydın ürün_kimliği ile eşleştiğinde sipariş_tarihi değerini değerlendireceği anlamına gelir. Yeni bir ürün_kimliği ile karşılaşıldığında, LAG işlevi hesaplamalarını yeniden başlatır ve ilgili ürün_kimliği bölümünü kullanır.

Gördüğünüz gibi, sonuç kümesindeki ilk kayıt prev_order_date için NULL'dur çünkü bu, product_id'nin 1000 olduğu (order_date'e göre sıralanmış) bir bölüm için ilk kayıttır, bu nedenle daha düşük order_date değeri yoktur. Bu, product_id'nin 2000 olduğu 4. kayıt için de geçerlidir.

Ofset penceresi işlevleri iki kategoriye ayrılır. İlk kategori, ofseti geçerli satıra göre gösterilen işlevlerdir. Bunlar LAG ve LEAD'dir. İkinci kategorinin işlevlerinde, ofset, pencere çerçevesinin başlangıcı veya bitişi ile ilişkili olarak belirtilir. Bu, FIRST_VALUE, LAST_VALUE ve NTH_VALUE işlevlerini içerir. SQL Server 2012 LAG, LEAD, FIRST_VALUE ve LAST_VALUE destekler ve NTH_VALUE desteklemez.

İlk kategorinin (LAG ve LEAD) işlevleri, bölümlemenin yanı sıra pencere sıralaması önerisini destekler. Açıkçası, ikinci bölüm yer değiştirmeye anlam katıyor. İkinci kategorideki (FIRST_VALUE, LAST_VALUE ve NTH_VALUE) işlevler, pencere bölümleme ve sıralama yan tümcesine ek olarak pencere çerçevesi maddesini destekler.

LAG ve LEAD işlevleri

LAG ve LEAD işlevleri, geçerli satırdan önce (LAG) veya sonra (LEAD) belirli bir ofsette olan bir pencere bölümündeki dizeden bir değer ifadesi döndürmenize izin verir. Varsayılan ofset "1" dir ve herhangi bir ofset belirtilmezse uygulanır. Örneğin, aşağıdaki sorgu her müşteri siparişi için mevcut değeri ve aynı müşteriden önceki ve sonraki siparişlerin değerini döndürür:

Sales.OrderValues \u200b\u200b'den sonraki değer olarak custid, orderdate, orderid, val, LAG (val) OVER (custid ORDER BY orderdate, orderid) öğesini seçin;

Açıkça bir ofset belirtmediğimiz için, varsayılan, bir ofset varsaymaktır. Fonksiyondaki veriler custid'e göre bölündüğünden, satır araması yalnızca bir müşterinin verilerini içeren aynı bölüm içinde gerçekleştirilir. Pencere sıralaması söz konusu olduğunda, "önceki" ve "sonraki" kavramları, isteğe bağlı bir parametre olarak sipariş tarihine ve sipariş numarasına göre sıralama ile tanımlanır. İlk satırdan önce başka satır olmadığından, LAG'ın sorgu sonuçlarında pencere bölümünün ilk satırı için NULL döndürdüğünü unutmayın; benzer şekilde LEAD, son satır için NULL döndürür.

Birden farklı bir ofsete ihtiyacınız varsa, bu sorguda olduğu gibi giriş değeri ifadesinden sonra bunu belirtmeniz gerekir:

Sales.OrderValues'DEN prev3val olarak custid, orderdate, orderid, LAG (val, 3) OVER (sipariş tarihine, sipariş kimliğine göre custid SİPARİŞE GÖRE BÖLME) SEÇİN;

Belirtildiği gibi, verilen ofsette satır yoksa LAG ve LEAD varsayılan olarak NULL döndürür. Farklı bir değer döndürmeniz gerekirse, bunu işlevin üçüncü bağımsız değişkeni olarak belirtebilirsiniz. Örneğin, LAG (değer, 3, 0.00), geçerli satırdan önce göreli konum 3'te satır yoksa "0.00" döndürür.

Bu davranışı LAG ve LEAD'de SQL Server 2012'den önceki SQL Server sürümlerinde uygulamak için aşağıdaki yaklaşımı uygulayabilirsiniz:

İstenilen bölümleme ve sıralama seçenekleriyle satır numaralarını döndüren bir sorgu yazın ve bundan bir tablo ifadesi oluşturun.

Birden çok tablo ifadesini geçerli, önceki ve sonraki satırı temsil edecek şekilde birleştirin.

Birleştirme koşulunda, farklı örneklerin bölümleme sütunlarını eşleştirin (şimdiki ile önceki veya sonraki). Ayrıca birleştirme yükleminde, geçerli ve önceki veya sonraki örneğin satır sayısı arasındaki farkı hesaplayın ve ardından hesaplamalarınızda ihtiyaç duyduğunuz ofset değerine göre filtre uygulayın.

İşte bu yaklaşımı uygulayan ve her sipariş için geçerli, önceki ve sonraki satış siparişi değerlerini döndüren bir sorgu:

SİPARİŞLERLE RN AS (SEÇİN custid, orderdate, orderid, val, ROW_NUMBER () OVER (ORDER BY custid, orderdate, orderid) AS rn olarak Sales.OrderValues) SELECT C.custid, C.orderdate, C.orderid, C.val P.val AS prevval, N.val AS nextval FROM OrdersRN AS C LEFT OUTER JOIN OrdersRN AS P ON C.custid \u003d P.custid AND C.rn \u003d P.rn + 1 LEFT OUTER JOIN OrdersRN AS N ON C.custid \u003d N.custid VE C.rn \u003d N.rn - 1;

Bu sorunu basit iç içe geçmiş sorgular kullanarak da çözebileceğiniz açıktır.

FIRST_VALUE, LAST_VALUE ve NTH_VALUE İşlevleri

Önceki bölümde, mevcut satıra göre bir ofset ayarlamanıza izin veren LAG ve LEAD ofset fonksiyonlarından bahsetmiştim. Bu bölüm, bir pencere çerçevesinin başlangıcına veya sonuna göre bir öteleme tanımlamanıza izin veren işlevlere odaklanır. Bunlar FIRST_VALUE, LAST_VALUE ve NTH_VALUE işlevleridir, ikincisi SQL Server 2012'de uygulanmamaktadır.

Bir hatırlatma olarak, LAG ve LEAD bölümleme ve sıralama maddelerini destekler ancak pencere çerçeveleme maddesini desteklemez. Ofsetin mevcut satıra göre olup olmadığı anlamlıdır. Pencerenin başlangıcına veya sonuna göre bir ofset belirten işlevlerde, kırpma mantıklıdır. FIRST_VALUE ve LAST_VALUE işlevleri, çerçevedeki ilk ve son satırdan sırasıyla istenen değer ifadesini döndürür. Her müşteri siparişiyle birlikte ilk ve son siparişlerin değerlerinin yanı sıra bu siparişin geçerli değerinin nasıl döndürüleceğini gösteren bir sorgu aşağıda verilmiştir:

Val_firstorder olarak custid, orderdate, orderid, val, FIRST_VALUE (val) OVER (sipariş tarihine göre partisyon, sipariş kimliği), LAST_VALUE (val) OVER (sipariş tarihine göre custid SİPARİŞE GÖRE BÖLME, ARASI sipariş satırları) SEÇİN val_lastorder FROM Sales.OrderValues;

Teknik açıdan, bölümün ilk ve son satırındaki değerlere ihtiyacımız var. FIRST_VALUE kolaydır çünkü varsayılan kırpmayı kullanabilirsiniz. Kırpma destekleniyorsa ve bir kırpma penceresi cümlesi belirtmezseniz, varsayılan değerin SINIRSIZ ÖNCEKİ SATIR ARASINDAKİ ARALIK olacağını unutmayın. Ancak LAST_VALUE ile, sonuncu geçerli satır olduğu için varsayılan kırpma işe yaramaz. Bu nedenle, bu örnek, alt çerçeve sınırı olarak UNBOUNDED FOLLOWING ile açık bir çerçeve tanımı kullanır.

Tipik olarak, ilk veya son değer, önceki örnekte olduğu gibi tüm dize ayrıntılarıyla birlikte döndürülmez - hesaplamalar genellikle tek bir rakamla ve bir pencere işlevi tarafından döndürülen değerle çalışır. Aşağıdaki örnekte, sorgu, her müşteri siparişiyle birlikte geçerli siparişin değerini ve bununla ilk ve son satış siparişinin maliyeti arasındaki farkı döndürür:

Custid, orderdate, orderid, val, val - FIRST_VALUE (val) OVER (sipariş tarihine, sipariş numarasına göre bölümleme), val - LAST_VALUE (val) OVER (sipariş tarihine göre SIRALAMA, sipariş tarihine GÖRE BÖLÜMLEME, SIRALAMA ARASI) SINIRSIZ AŞAĞIDAKİ) Sales.OrderValues'lardan farklı olarak;

Dediğim gibi, standart NTH_VALUE işlevi SQL Server 2012'de uygulanmaz. Bu işlev, pencere çerçevesindeki ilk veya son satırdan satır sayısı ile ifade edilen, belirli bir uzaklıktaki bir değer ifadesini sorgulamanıza izin verir. Göreli konum, değer ifadesinden sonraki ikinci giriş değerinde ve ilk veya son satırdan uzaklığın sayılıp sayılmayacağını belirten FROM_FIRST veya FROM_LAST anahtar sözcüğünde belirtilir. Örneğin, aşağıdaki ifade, bölümdeki en düşük seviyeden başlayarak üçüncü satırdaki değeri döndürür.

Analitik fonksiyonları kullanmak için genel sözdizimi aşağıdaki gibidir

fonksiyon adı (<аргумент>,< аргумент >, . . . )
bitmiş (<конструкция_фрагментации><конструкция_упорядочения><конструкция_окна>)

Bu sözdiziminin ana bölümlerine bakalım.

1. <конструкция_фрагментации>

Parçalanma yapısını belirtmek için sözdizimi aşağıdaki gibidir

bölümle ifade [, ifade] [, ifade]

Bu yapı, sonuçtaki kümeyi mantıksal olarak N gruba ayırır.
parçalanma ifadeleri tarafından belirtilen kriterlere göre. Analitik fonksiyonlar her gruba bağımsız olarak uygulanır - her yeni grup için sıfırlanırlar. Bir parçalama yapısı belirtmezseniz, tüm sonuç kümesi dikkate alınır
bir grup.

2. <конструкция_упорядочения>

Sipariş yapısı aşağıdaki sözdizimine sahiptir

ifadeye göre sırala

Tasarım tarafından sipariş her gruptaki (her bir parçadaki) verileri sıralama kriterlerini belirler. Bu şüphesiz herhangi bir analitik işlevin sonucunu etkiler. Bir tasarımın varlığında (veya yokluğunda) tarafından sipariş analitik fonksiyonlar farklı şekilde hesaplanır. Örneğin.
- tarafından sipariş yok

ename, sal, avg (sal) over () seçin
emp'den

Tarafından sipariş ile

ename, sal, avg (sal) over (ename ile sırala) seçin
emp'den

Aslında, inşaatın varlığı burada belirtilmelidir. tarafından sipariş analitik işlev çağrısında standart pencere yapısı eklenir - SINIRSIZ ÖNCEKİ ARALIK... Bu, hesaplamanın geçerli parçadaki tüm önceki ve geçerli satırları kullandığı anlamına gelir. Yokluğunda tarafından sipariş standart pencere parçanın tamamıdır. Yani aslında bir önceki istek şöyle görünecek

ename, sal, avg (sal) over (ename RANGE UNBOUNDED PRECEDING ile sırala)
emp'den

3. <конструкция_окна>

Pencere tasarımı, hareketli veya sağlam bir şekilde bağlantılı bir pencere (set) tanımlamanıza olanak tanır
analitik işlevin çalışacağı grup içindeki veriler.
Örneğin, bir departmandaki mevcut ve önceki iki çalışanın maaşlarını gösteren bir rapor oluşturmak için aşağıdaki sorguyu çalıştırabilirsiniz.

seçin deptno, ename, sal,
sum (sal) over (öndeki ename satırları 2'ye göre bölüm sırasına göre bölüm) sliding_total
emp'den
deptno tarafından sipariş, ename

İki kritere göre pencere oluşturabilirsiniz: aralığa göre ( ARALIK) veri değerleri veya tarafından
yer değiştirme ( SATIRLAR) mevcut satıra göre... İnşaat kullanımı aralıkdaha önce belirtildiği gibi, bazı durumlarda dolaylı olarak kullanılır, SINIRSIZ ÖNCEKİ ARALIK Örneğin. İnşaat tarafından belirtilen sıraya göre mevcut olana kadar tüm hatların alınmasını gerektirir. tarafından sipariş... Windows kullanmayı unutmayın
inşaatı ayarlamak gerekli tarafından sipariş.

Pencere, belirtilen sıraya göre birleştirilen satır aralığıyla tanımlanır.
Tasarım uygula aralıksayısal ifadelerle ( NUMARA) veya değeri tarih olan ifadelerle ( TARİH). Bu tür pencereler için bir başka sınırlama, inşaatta tarafından sipariş yalnızca bir sütun olabilir - aralıklar doğası gereği tek boyutludur. N boyutlu uzayda bir aralık belirleyemezsiniz. Misal.
Her çalışanın maaşını ve önceki 100 gün içinde işe alınanların ortalama maaşını ve sonraki 100 gün için işe alınanların ortalama maaşını seçmek istediğinizi varsayalım. İlgili istek şu şekilde görünecektir:

ename, hiredate, sal, seçin
avg (sal) over (asc asc aralığı 100'den önce sipariş) avg_sal_100_days_before,
ort (sal) üzerinden (işe alınan tanım aralığı 100 öncesindeki sipariş) avg_sal_100_days_after
emp'den
kiralanan desc ile sipariş

Aralığa göre bir pencere tanımlamaya ek olarak ( ARALIK), pencereler ayrıca satır sayısına göre belirlenir ( SATIRLAR). Sıralı pencereler için, doğal pencere aralığı kısıtlamaları yoktur; veriler herhangi bir türde olabilir ve herhangi bir sayıda sütuna göre sıralanabilir.
Örneğin, bir çalışanın ortalama maaşını ve önceki ve sonraki beş işe alımını hesaplamak istediğinizi varsayalım. Talep aşağıdaki şekilde yazılabilir

ename, hiredate, sal, seçin
ort (sal) üzeri (tarafından sipariş hiredate asc satırları 5 önceki) avg_5_before,
ort (sal) üzeri (tarafından sipariş hiredate 5. satırdan önceki) avg_5_after
emp'den
kiralayan tarafından sipariş

Pencerelerin nasıl tanımlandığını bilerek (aralık veya satır sayısı ile), pencerelerin nihayet nasıl tanımlandığına bakalım. En basit durumda, aşağıdaki üç birbirini dışlayan yapıdan biri kullanılarak bir pencere belirtilir.

- SINIRSIZ ÖNCEKİ TEKNİK.
Pencere, mevcut grubun ilk satırından başlar ve işlenen mevcut satırla biter.

- GÜNCEL SATIR.
Pencere mevcut satırla başlar (ve biter).

- Sayısal_ifade ÖNCELİKLİ.
Pencere sıranın arkasında başlar sayısal_ifade satırlar tarafından belirtilmişse veya sırayla cümleciğe göre belirtilen sütunun değerinden daha küçük bir satırdan, aralık ile belirtilmişse sayısal bir ifadeden fazla olmamak kaydıyla satırlar geçerli olana.

- Sayısal_ifade TAKİP EDİLİYOR.
Pencere bir çizgi ile biter (veya başlar) sayısal_ifade geçerli olandan sonraki satırlar, satır satır veya daha büyük bir satırdan ayarlanmışsa
aralık ile belirtilmişse, numeric_expression'dan fazla olmayacak şekilde, cümlecik sırasına göre belirtilen sütunun değerine göre.

Pencerenin GÜNCEL SATIR En basit haliyle, analitik işlevin kullanımını tek bir geçerli satırla sınırladığından ve bu analitik işlevler için gerekli olmadığından muhtemelen hiç kullanılmaz. Daha karmaşık bir durumda, pencereye inşaat da verilir. ARASINDA... İçinde GÜNCEL SATIR pencerenin başlangıç \u200b\u200bveya bitiş çizgisi olarak belirtilebilir. Yapıdaki pencerenin başlangıç \u200b\u200bve bitiş çizgisi ARASINDAyukarıdaki yapılardan herhangi biri kullanılarak belirtilebilir. Örneğin, pencereyi işlenen hattın görünmemesi için ayarlayabilirsiniz.
sonuncusu, ancak pencerenin ortasında bir yerde olacak

isim seçin, işe alın,
first_value (ename) over (hiredate tarafından sıralama yükselen 100 arasında değişir önceki ve 100 takip eden),
last_value (ename) over (hiredate tarafından sıralama yükselen 100 arasında önceki ve 100 takip)
emp'den
asc ile sipariş

Bu talep ayrıca işlevleri kullanır ilk_değer () ve last_value ()Sırasıyla geçerli pencerenin ilk değerlerini ve geçerli pencerenin son değerini döndüren, pencere aralığı geçerli kayan tarih ile solda - 100 gün ve sağda geçerli kayan tarih + 100 gün ile sınırlıdır, ifadenin anlamı budur - 100 arası önceki ve 100 takip .

seç
seviye
count (*) over (düzeye göre sırala, önceki satırlar 2'den önce) asc_count,
count (*) over (düzeye göre sırala, önceki 2. satırlar) desc_count
ikiliden
seviyeye göre bağlan<= 10
seviyeye göre sırala

Sorgu sonucunda görüldüğü gibi pencereden önceki satır 2, 1 ila 3 satır içerir (bu, geçerli satırın grubun başlangıcından ne kadar uzakta olduğuna göre belirlenir). Grubun ilk satırı için 1 değerine sahibiz (önceki satır yok). Bu tür satırlar grubundaki bir sonraki satır için 2. Son olarak, üçüncü ve sonraki satırlar için count (*) değeri sabit kalır, çünkü sadece geçerli satırı ve önceki iki satırı sayarız.

seç
n,
toplam (n) üzerinden (önceki gün aralığı 2'ye göre sırala) n_sum,
günler
itibaren (
seç
seviye n,
(to_date ("01/10/2014", "gg.aa.yyyy") + (seviye - 1)) gün
ikiliden
seviyeye göre bağlan<= 10
günlere göre sipariş

Bu durumda, aralığı hareketli bir gün ve önceki 2 gün olan bir pencere içinde bir özet vardır.

seç
ename,
sal,
rank () over (sal sipariş) rank,
dense_rank () over (sal ile sipariş) dens_rank,
row_number () over (sal ile sırala) row_number
emp'den
sal sipariş

Bu sorgu, sıralama işlevlerinin rank (), yoğun_rank () ve row_number () çalışan maaşlarına göre nasıl çalıştığını gösterir. Aynı maaş değerlerine sahip satırlarda bu işlevlerin davranışına dikkat edin.

seç
ename,
deptno
sal,
rank () over (sal'a göre bölüm sırasına göre) rank,
dense_rank () over (sal ile deptno sırasına göre bölümleme) dens_rank,
row_number () over (sal ile bölüm sırasına göre bölüm) row_number
emp'den
bölüm tarafından sipariş

Önceki sorguyu departmana göre parçalayabilirsiniz, yani onu departmana göre gruplara bölebilir, analitik fonksiyonların içinde çalışacağı, grupların dışında olduğunda sonuçları sıfırlayacaktır. Sonuç aşağıda gösterilmiştir

Lag () ve lead () işlevlerini kullanma örneğine bakalım. Mevcut satırdaki önceki satırlara başvurabilmek için - lag () fonksiyonunu kullanmak gerekir. Söz dizimi aşağıdaki gibidir

gecikme ( field_to_request, ofset, value_to_replace_null)
over (bölüm ifade tarafından sipariş ifade)

Field_for_reference - değerleri görüntülemek istediğiniz alan;
ofset - alanın görüntülendiği uzaklık çizgisi, varsayılan olarak 1'e eşittir, 0'ı ayarlarsanız, o zaman geçerli alan görüntülenecektir;
value_to_replace_null - varsayılan olarak null değerine eşittir, görüntülenen alanda değer yoksa bu değeri döndürür. Burada, ikame edilen değerin görüntülenen alanla aynı türde olması gerektiğini belirtmek gerekir;
bu işlev için kullanılması zorunludur tarafından sipariş

ile
ana olarak (
emp'den
sal sipariş
),
numerated_main as (
empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum'u seçin
asıldan
seç
ename,
sal,
lag (sal) over (rownum'a göre sırala) previous_sal
numerated_main'den

Veya bunun gibi

ile
ana olarak (
empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum'u seçin
emp'den
sal sipariş
),
numerated_main as (
empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum'u seçin
asıldan
seç
ename,
sal,
lag (sal, 2, 0) over (rownum'a göre sırala) previous_sal
numerated_main'den

Lead () işlevinin mantığı ve sözdizimi, tek farkla önceki işleve benzer: görünüm geriye değil ileriye gider

İle
ana olarak (
empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum'u seçin
emp'den
sal sipariş
),
numerated_main as (
empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum'u seçin
asıldan
seç
ename,
sal,
lead (sal, 2, 0) over (rownum'a göre sırala) previous_sal
numerated_main'den

Aşağıda referansınız için analitik işlevlerin eksik bir listesi bulunmaktadır. Daha fazla bilgi için belgelere bakın. Herkese başarı.

Analitik işlev	Açıklama
AVG (ifade)	Bir grup ve pencere içindeki bir ifadenin ortalamasını hesaplamak için kullanılır. Yinelenen değerleri kaldırdıktan sonra ortalamayı bulmak için şunları yapabilirsiniz: DISTINCT anahtar kelimesini belirtin
CORR (ifade, ifade)	Sayısal değerler döndüren bir ifade çifti için korelasyon katsayısını döndürür. İstatistiksel anlamda, korelasyon derecesidir değişkenler arasındaki ilişkiler. Değişkenler arasındaki ilişki, bir değişkenin değerinin bir dereceye kadar diğerinin değerinden tahmin edilebileceği anlamına gelir. Korelasyon katsayısı, -1 (yüksek ters korelasyon) ile 1 (yüksek korelasyon) arasında değişen bir sayı olarak korelasyon derecesini temsil eder. 0 değeri korelasyon olmadığına karşılık gelir
COUNT ([*] [ifade])	Bu işlev, satırları gruplar halinde sayar. * Veya NULL dışında herhangi bir sabit belirtirseniz, COUNT işlevi tüm satırları sayar. Bir ifade belirtirseniz, COUNT işlevi, ifadenin NULL olmadığı satırları sayar. Yinelenen satırları kaldırdıktan sonra satırları gruplar halinde saymak için DISTINCT değiştiricisini belirtebilirsiniz.
COVAR_POP (ifade, ifade)	Sayısal değerlerle bir çift ifadenin popülasyon kovaryansını verir
COVAR_SAMP (ifade, ifade)	Sayısal değerlere sahip bir ifade çiftinin örnek kovaryansını verir
CUME_DIST	Bir gruptaki bir dizenin göreceli konumunu hesaplar. CUME_DIST işlevi her zaman 0'dan büyük ve 1'e eşit veya daha küçük bir sayı döndürür. Bu sayı, bir N yay grubundaki bir satırın "konumunu" temsil eder. Örneğin, üç satırlık bir grupta şu kümülatif dağılım değerleri döndürülür: 1/3, 2/3 ve 3/3
DENSE_RANK	Bu işlev, bir sorgu tarafından döndürülen her satırın diğer satırlara göre göreceli sıralamasını ORDER BY yan tümcesindeki ifadelerin değerlerine göre hesaplar. Gruptaki veriler ORDER BY yan tümcesine göre sıralanır ve ardından her satıra 1'den başlayarak bir sayısal sıra atanır. Sıra, ORDER BY yan tümcesine dahil edilen ifadelerin değerleri her değiştiğinde artar. Aynı değerlere sahip satırlar aynı sırayı alır (bu karşılaştırmada NULL değerler aynı kabul edilir). Bu işlev tarafından döndürülen "yoğun" sıra, aralıksız sıra değerleri verir. Aşağıdaki RANK işlevi ile karşılaştırın.
FIRST_VALUE	Bir gruptaki ilk değeri verir
LAG (ifade,<смещение>, <стандартное değer\u003e)	LAG işlevi, sonuç kümesindeki diğer satırlara erişim sağlayarak kendi kendine birleştirme ihtiyacını ortadan kaldırır. İmleç ile bir dizi olarak çalışmanıza izin verir. Bir grupta mevcut satırdan önceki satırlara başvurabilirsiniz. Bir gruptaki sonraki satırlara nasıl erişileceğini öğrenmek için LEAD işlevine bakın. Göreli konum, standart değeri 1 olan pozitif bir tam sayıdır (önceki satır). Dizin pencerenin dışındaysa varsayılan değer döndürülür (varsayılan değer, grubun ilk satırı için döndürülür)
LAST_VALUE	Bir gruptaki son değeri verir
LEAD (ifade,<смещение>,<стандартное değer\u003e)	LEAD, LAG'nin tersidir. LAG işlevi grubun önceki satırlarına erişime izin verirken, LEAD işlevi mevcut satırı takip eden satırlara erişim sağlar. Uzaklık, standart değeri 1 olan pozitif bir tamsayıdır (sonraki satır). Dizin pencerenin dışındaysa varsayılan değer döndürülür (varsayılan değer, grubun son satırı için döndürülür)
MAX (ifade)	Gruptaki bir pencerede bir ifadenin maksimum değerini bulur
MIN (ifade)	Gruptaki bir pencerede bir ifadenin minimum değerini bulur
NTILE (ifade)	Grubu, ifadenin değerine göre parçalara ayırır. Örneğin, ifade \u003d 4 ise, gruptaki her satıra ait olduğu bölüme göre 1'den 4'e kadar bir sayı atanır. Grupta 20 satır varsa, ilk 5 1 değerini, sonraki 5 2 değerini alacaktır ve bu böyle devam eder. Bir gruptaki satır sayısı, kalansız ifadenin değeriyle bölünemezse, satırlar, hiçbir bölümde satır sayısı diğer parçalardaki minimum sayıyı 1'den fazla aşmayacak ve ek satırlar, daha düşük parça numaralı gruplar halinde olacak şekilde dağıtılır. Örneğin, yine ifade \u003d 4 ve satır sayısı \u003d 21 ise, ilk parçanın 6 satırı olacak, ikincisi ve sonraki olanlar - 5
YÜZDE SIRALAMA	CUME_DIST ile aynıdır (kümülatif dağılım). Bir gruptaki bir satırın sıralamasını eksi 1'in işlenen satır sayısı eksi 1'e bölerek hesaplar. Bu işlev her zaman 0 ile 1 arasındaki değerleri (dahil
RANK	Bu işlev, ORDER BY yan tümcesindeki ifadelerin değerlerine göre sorgu tarafından döndürülen her satırın göreli sıralamasını hesaplar. Gruptaki veriler ORDER BY yan tümcesine göre sıralanır ve ardından her satıra 1'den başlayarak sayısal bir sıra atanır. ORDER BY yan tümcesine dahil edilen ifadelerin aynı değerlerine sahip satırlar aynı sırayı alır, ancak iki satır aynı sırayı alırsa sonraki değer rütbe atlandı. İki satır 1. sıraya sahipse, 2. sıraya sahip bir sıra olmayacaktır; gruptaki sonraki satır 3. sırada yer alacaktır. Bu, değerleri atlamayan DENSE_RANK işlevinden farklıdır
RATIO_TO_REPORT (ifade)	Bu işlev, grup satırlarında ifade / (TOPLA (ifade)) değerini değerlendirir. Bu, SUM (ifade) ile ilişkili olarak geçerli satırın değeri olan yüzdeyi verir
REGR_xxxxxxx (ifade, ifade)	Bu doğrusal regresyon fonksiyonları, standart doğrusal en küçük kareler regresyonu birkaç ifade. 9 farklı regresyon işlevi sunulur
SATIR NUMARASI	Dizenin başlangıcına göre ofsetini verir sıralı grup. Belirli kriterlere göre sıralı satır numaralandırması için kullanılabilir
STDDEV (ifade)	Standart (ortalama kare kare) sapmayı hesaplar gruba göre mevcut satırın (standart sapma)
STDDEV_POP (ifade)	Bu işlev, popülasyon standart sapmasını hesaplar ve popülasyon varyansının karekökünü döndürür. VAR_POP işlevinin sonucunun kareköküyle eşleşen değeri döndürür.
STDDEV_SAMP (ifade)	Bu işlev, kümülatif örnek standart sapmasını hesaplar ve örnek varyansının karekökünü döndürür. Fonksiyon sonucunun karekökünü döndürür VAR_SAMP
SUM (ifade)	Bir grup için ifade değerlerinin toplamını hesaplar
VAR_POP (ifade)	Bu işlev, bir dizi sayısal değer için popülasyonun varyansını döndürür (NULL değerler göz ardı edilir)
VAR_SAMP (ifade)	Bu işlev, bir dizi sayısal değer için örnek varyansı döndürür (NULL değerler göz ardı edilir)
VARIANCE (ifade)	İfade için varyansı döndürür. Oracle Server, varyansı şu şekilde hesaplar: - Gruptaki satır sayısı \u003d 1 ise 0; Gruptaki satır sayısı 1'den fazlaysa -VAR_SAMP

Analitik fonksiyonlar 43

Oracle Server 26 analitik işlev sunar. Yeteneklerine göre dört ana sınıfa ayrılırlar.

Birinci sınıf, birinci N tipi sorguların oluşturulmasına izin veren çeşitli sıralama işlevleri tarafından oluşturulur. Önceki örnekte SEQ sütununu oluştururken bu sınıfın bir işlevini, SATIR NUMARASI'nı zaten kullandık. Bölümlerdeki çalışanları soyadına (ENAME) göre sıraladı. Benzer şekilde, maaş (MAAŞ) veya başka herhangi bir özelliğe göre sıralanabilirler.

İkinci sınıf, çeşitli toplamların hesaplanmasına izin veren pencere fonksiyonları tarafından oluşturulur. Bu bölümün ilk örneğinde, böyle bir fonksiyon gösterildi - farklı gruplar için TOPLA (SAL) hesapladık. TOPLA işlevi yerine COUNT, AVG, MIN, MAX vb. Gibi diğer toplama işlevleri kullanılabilir.

Üçüncü sınıf, çeşitli özet işlevleri içerir. Pencereli olanlara çok benzerler, bu yüzden aynı isimleri taşırlar: SUM, MIN, MAX, vb. Pencere işlevleri, önceki örnekteki hareketli toplam gibi veri pencereleriyle çalışmak için kullanılırken, özetleme işlevleri bir yığın veya gruptaki tüm satırlarda çalışır. Örneğin, ilk istek isabetleri kullandıysa:

toplam (sal) üzerinden () toplam maaş,

toplam (sal) üzerinden (bölüme göre) bölüm için toplam maaş

toplamları alt toplamlara göre değil, gruba göre alırdık. Son işlev ile pencere işlevi arasındaki temel fark, OVER işlecinde ORDER BY yan tümcesinin olmamasıdır. ORDER BY yan tümcesi yoksa, işlev gruptaki her satıra uygulanır. ORDER BY yan tümcesi varsa, işlev pencereye uygulanır (bununla ilgili daha fazla bilgi, pencere yapısını açıklayan bölümde).

Sonuç kümesinin önceki veya sonraki satırlarından değerler almanızı sağlayan LAG ve LEAD işlevleri de vardır. Bu, kendi kendine birleşen verileri önlemeye yardımcı olur. Örneğin, tablo hasta doktor ziyaretlerinin tarihlerini içeriyorsa ve her biri için ziyaretler arasındaki süreyi hesaplamanız gerekiyorsa, LAG işlevi çok kullanışlıdır. Verileri hastaya göre kolayca parçalayabilir ve tarihe göre sıralayabilirsiniz. LAG işlevi daha sonra hasta için önceki kayıt verilerini kolayca döndürebilir. Bir tarihten bir başkasını çıkarmak kalır. Analitik fonksiyonların ortaya çıkmasından önce, bu verileri elde etmek, tablonun masanın kendisine karmaşık bir bağlantısını organize etmek zorundaydı.

Son olarak, VAR POP, VAR SAMP, STDEV POP, bir dizi doğrusal regresyon işlevi vb. Gibi büyük bir istatistiksel işlev sınıfı vardır. Bu işlevler, herhangi bir sıralanmamış parça için istatistik değerleri hesaplamanıza izin verir.

Sözdizimi bölümünün sonunda, her bir analitik işlevin amacının kısa bir açıklamasını içeren bir tablo sağlanmıştır.

Parçalanma yapısı

PARTITION BY yan tümcesi, sonuç kümesini mantıksal olarak parçalama ifadeleri tarafından belirtilen kriterlere göre N gruba ayırır. Kelime parçası ve grup

bu bölümdeki ve Oracle dokümantasyonundaki nas birbirinin yerine kullanılmaktadır. Analitik fonksiyonlar her gruba bağımsız olarak uygulanır - her yeni grup için sıfırlanırlar. Örneğin, devam eden toplam maaşları hesaplayan işlevin gösterilmesinde daha önce, parçalama DEPTNO sütununda gerçekleştirildi. Sonuç kümesindeki DEPTNO sütunundaki değer değiştiğinde, değişen toplam sıfıra sıfırlandı ve toplama yeniden başlatıldı.

Bir parçalama yapısı belirtmezseniz, tüm sonuç kümesi tek bir grup olarak kabul edilir. İlk örnekte, tüm sonuç kümesi için değişen toplamları elde etmek için bir parçalanma yapısı olmadan TOPLA (SAL) işlevini kullandık.

Bir sorgudaki her analitik işlevin benzersiz bir parçalama yapısına sahip olabileceğine dikkat etmek ilginçtir; aslında, bu bölümün en basit örneğinde zaten yapılmıştır. ÇALIŞAN TOPLAM sütunu için parçalanma yapısı belirtilmedi, bu nedenle hedef grup tüm sonuç kümesiydi. BÖLÜMLERE GÖRE TOPLAM sütunu için, sonuç kümesi bölümlere bölünerek her bölüm için alt toplamların hesaplanmasına olanak sağlar.

Parçalanma sözdizimi basittir ve normal SQL sorgularındaki GROUP BY sözdizimine çok benzer:

PARTITION BY ifade [, ifade] [, ifade]

Yapı siparişi

ORDER BY yan tümcesi, her gruptaki (her bir parçadaki) verilerin sıralanması için ölçütü belirtir. Bu şüphesiz herhangi bir analitik işlevin sonucunu etkiler. ORDER BY yan tümcesinin varlığında (veya yokluğunda), analitik işlevler farklı şekilde değerlendirilir. Örnek olarak, ORDER BY ile ve olmadan AVG () işlevini kullandığınızda ne olacağını düşünün:

scott @ TKYTE816\u003e emp'den ename, sal, avg (sal) over () 2'yi seçin;

	SAL ORT. (SAL) ÜSTÜ ()

Analitik fonksiyonlar

scott @ TKYTE816\u003e seçin

Ename ile 3 sipariş

ename, sal, avg (sal) over (ENAME İLE SİPARİŞ EDİN)

SAL AVG (SAL) ÜSTÜ (ORDERBYENAME)

14 satır seçildi.

ORDER BY yan tümcesi olmadığında, ortalama değer tüm grup üzerinden hesaplanır ve her satır için aynı değer döndürülür (işlev, toplam olarak kullanılır). ORDER BY yan tümcesi ile AVG () işlevi kullanıldığında, her satırdaki ortalama değer, geçerli ve önceki tüm satırların ortalamasıdır (işlev bir pencere işlevi olarak kullanılır). Örneğin, ORDER BY yan tümcesi ile bir sorgu yürütme sonuçlarında ALLEN kullanıcısının ortalama maaşı 1350'dir (1100 ve 1600 değerleri için ortalama).

Biraz ileriye bakıldığında, pencere inşası ile ilgili bir sonraki bölümde, SİPARİŞ BU yapısının bir analitik işlev çağrısındaki varlığı, standart pencere yapısı-RANGE UNBOUNDEDPRECEDING ekler. Bu, hesaplamanın geçerli parçadaki tüm önceki ve geçerli satırları kullandığı anlamına gelir. SİPARİŞ'in yokluğunda, standart pencere parçanın tamamıdır.

...

Bu Oracle öğreticisi, Oracle / PLSQL'in nasıl kullanılacağını açıklar LAG işlevi sözdizimi ve örneklerle.

Açıklama

Oracle / PLSQL LAG işlevi, tabloyu kendisiyle birleştirmek zorunda kalmadan aynı anda bir tablodaki birden fazla satırı sorgulamanıza izin veren bir analitik işlevdir. Tablodaki önceki bir satırdan değerleri döndürür. Sonraki satırdan bir değer döndürmek için LEAD işlevini kullanmayı deneyin.

Sözdizimi

Oracle / PLSQL'de LAG işlevinin sözdizimi şöyledir:

LAG (ifade [, göreli konum [, varsayılan]]) AŞIRI ([sorgu_bölümü_karakası] sipariş_yönetim)

Parametreler veya Bağımsız Değişkenler

ifade Diğer yerleşik işlevleri içerebilen, ancak değil herhangi bir analitik işlev içerir. ofset İsteğe bağlı. Tablodaki geçerli satırın fiziksel ofsetidir. Bu parametre atlanırsa, varsayılan 1. varsayılan İsteğe bağlıdır. Bu, döndürülen değerdir. ofset tablonun sınırları dışına çıkar. Bu parametre atlanırsa, varsayılan değer null olur. query_partition_clause İsteğe bağlı. Sonuçları bir veya daha fazla ifadeye göre gruplara ayırmak için kullanılır. order_by_clause İsteğe bağlı. Her bölümdeki verileri sıralamak için kullanılır.

İadeler

LAG işlevi, tablodaki önceki bir satırdan değerleri döndürür.

Şunlar için geçerlidir:

LAG işlevi, Oracle / PLSQL'in aşağıdaki sürümlerinde kullanılabilir:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Misal

LAG işlevi Oracle / PLSQL'de kullanılabilir.

Bir örneğe bakalım. emirler aşağıdaki verileri içeren tablo:

SİPARİŞ TARİHİ	ÜRÜN KİMLİĞİ	MİKTAR
2007/09/25	1000	20
2007/09/26	2000	15
2007/09/27	1000	8
2007/09/28	2000	12
2007/09/29	2000	2
2007/09/30	1000	4

Ve aşağıdaki SQL ifadesini çalıştırdık:

Emirlerden prev_order_date olarak ürün_kimliği, sipariş_tarihi, LAG (sipariş_tarihi, 1) AŞIRI (sipariş_tarihine göre SİPARİŞ VER) SEÇİN;

ÜRÜN KİMLİĞİ	SİPARİŞ TARİHİ	PREV_ORDER_DATE
1000	2007/09/25	BOŞ
2000	2007/09/26	2007/09/25
1000	2007/09/27	2007/09/26
2000	2007/09/28	2007/09/27
2000	2007/09/29	2007/09/28
1000	2007/09/30	2007/09/29

Bu örnekte, LAG işlevi, aşağıdakilerin tümünü artan düzende sıralayacaktır: sipariş tarihi değerler emirler tablo ve sonra önceki sipariş tarihi kullandığımızdan beri ofset arasında 1.

Kullanmış olsaydık ofset 2 yerine, döndürürdü sipariş tarihi önceki 2 siparişten. Kullanmış olsaydık ofset 3, döndürürdü sipariş tarihi 3 siparişten önce .... vb.

Bölümleri Kullanma

Şimdi, öncekini döndürmek için bir sorgu bölümü cümlesini kullandığımız daha karmaşık bir örneğe bakalım. sipariş tarihi her biri için ürün kimliği.

Aşağıdaki SQL ifadesini girin:

Prev_order_date olarak sipariş_tarihine göre ürün_kimliği, sipariş_tarihi, LAG (sipariş_tarihi, 1) AŞIRI (ürün_kimliğine göre bölümlendirme) SEÇİN;

Aşağıdaki sonucu döndürecektir:

ÜRÜN KİMLİĞİ	SİPARİŞ TARİHİ	PREV_ORDER_DATE
1000	2007/09/25	BOŞ
1000	2007/09/27	2007/09/25
1000	2007/09/30	2007/09/27
2000	2007/09/26	BOŞ
2000	2007/09/28	2007/09/26
2000	2007/09/29	2007/09/28

Bu örnekte, LAG işlevi sonuçları şuna göre bölümleyecektir: ürün kimliği ve sonra sırala sipariş tarihi PARTITION BY product_id tarafından belirtildiği gibi ORDER BY order_date. Bu, LAG işlevinin yalnızca bir sipariş tarihi değer eğer ürün kimliği mevcut kayıtlarla eşleşir ürün kimliği... Ne zaman yeni ürün kimliği karşılaşıldığında, LAG işlevi hesaplamalarını yeniden başlatacak ve uygun ürün kimliği bölüm.

Gördüğünüz gibi, sonuç kümesindeki 1. kaydın değeri için NULL değeri var prev_order_date çünkü bu th için ilk kayıt ebölüm nerede ürün kimliği 1000'dir (sipariş_tarihine göre sıralanır) bu nedenle daha düşük bir değer yoktur sipariş tarihi değer. Bu aynı zamanda dördüncü kayıt için de geçerlidir. ürün kimliği 2000'dir.