MaxCompute window functions - syntax, parameters, examples - MaxCompute

Kullanım notları

Pencere işlevleri yalnızca SELECT ifadelerinde yer alabilir.
Bir pencere işlevi, diğer pencere işlevleri veya toplama işlevleriyle iç içe kullanılamaz.
Pencere işlevleri, aynı düzeydeki toplama işlevleriyle birlikte kullanılamaz.

Dizin

MaxCompute SQL aşağıdaki pencere işlevlerini destekler.

İşlev	Özellikler
AVG	Bir penceredeki verilerin ortalama değerini hesaplar.
CLUSTER_SAMPLE	Rastgele örnekleme yapar. Satırın örneklenmesi durumunda true döndürür.
COUNT	Bir penceredeki kayıt sayısını sayar.
CUME_DIST	Kümülatif dağılımı hesaplar.
DENSE_RANK	Sıralamayı hesaplar. Sıralamalar bitişiktir.
FIRST_VALUE	Geçerli satırın pencere çerçevesindeki ilk satırın değerini döndürür.
LAG	Bir bölüm içinde geçerli satırdan önceki N. satırın değerini döndürür.
LAST_VALUE	Geçerli satırın pencere çerçevesindeki son satırın değerini döndürür.
LEAD	Bir bölüm içinde geçerli satırdan sonraki N. satırın değerini döndürür.
MAX	Bir penceredeki maksimum değeri hesaplar.
MEDIAN	Bir penceredeki medyan değerini hesaplar.
MIN	Bir penceredeki minimum değeri hesaplar.
NTILE	Sıralı verileri N eşit boyutlu gruba böler ve her satır için grup numarasını (1'den N'ye kadar) döndürür.
NTH_VALUE	Geçerli satırın pencere çerçevesindeki N. satırın değerini döndürür.
PERCENT_RANK	Sıralamayı yüzdelik olarak hesaplar.
PERCENTILE_CONT	Tam yüzde dilimini hesaplar.
PERCENTILE_DISC	Belirtilen sütunu artan düzende sıralayarak belirli bir yüzde dilimi değerini hesaplar.
RANK	Sıralamayı hesaplar. Sıralamalar bitişik olmayabilir.
ROW_NUMBER	1'den başlayarak satır numarasını hesaplar.
STDDEV	Popülasyon standart sapmasını hesaplar. STDDEV_POP için bir takma addır.
STDDEV_SAMP	Örneklem standart sapmasını hesaplar.
SUM	Bir penceredeki verilerin toplamını hesaplar.

Pencere işlevi sözdizimi

Pencere işlevi sözdizimi:

<function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
<function_name>([distinct][<expression> [, ...]]) over <window_name>

function_name: Yerleşik bir pencere işlevi, bir toplama işlevi veya bir kullanıcı tanımlı toplama işlevi (UDAF).
expression: İşlevin biçimi; işlevin sözdizimine uygun olmalıdır.
windowing_definition: Pencere tanımı. Sözdizimi hakkında daha fazla bilgi için bkz. windowing_definition.
window_name: Pencerenin adı. Özel bir pencere tanımlamak ve windowing_definition öğesine bir ad atamak için window anahtar sözcüğünü kullanabilirsiniz. Adlandırılmış pencere tanımının (named_window_def) sözdizimi aşağıdaki gibidir:
```
window <window_name> as (<window_definition>)
```
Aşağıda, özel ifadelerin SQL içindeki konumları açıklanmaktadır:
```
select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]
```

windowing_definition

windowing_definition sözdizimi:

--partition_clause:
[partition by <expression> [, ...]]
--orderby_clause:
[order by <expression> [asc|desc][nulls {first|last}] [, ...]]
[<frame_clause>]

Bir SELECT ifadesine pencere işlevi eklediğinizde, veriler pencere tanımındaki partition by ve order by yan tümcelerine göre bölümlenir ve sıralanır. Bir partition by yan tümcesi belirtmezseniz tüm veriler tek bir bölüm olarak kabul edilir. Bir order by yan tümcesi belirtmezseniz bir bölüm içindeki veri sırası garanti edilmez. Geçerli satır olarak adlandırılan her satır için, bu satıra ait pencereyi oluşturmak üzere bölümden frame_clause temel alınarak bir veri segmenti çıkarılır. Ardından pencere işlevi, kendi penceresindeki verilere dayanarak geçerli satır için bir sonuç hesaplar.

partition by <expression> [, ...]: İsteğe bağlıdır. Bölümü belirtir. Aynı bölüm anahtarı sütun değerlerine sahip satırlar aynı bölümde yer alır. Biçim hakkında daha fazla bilgi için bkz. Tablo işlemleri.
order by <expression> [asc|desc][nulls {first|last}] [, ...]: İsteğe bağlıdır. Bir bölüm içinde verilerin nasıl sıralanacağını belirtir.

Not
Satırlar aynı order by değerlerine sahipse sıralama düzeni garanti edilmez. Tutarlı bir sıra sağlamak için order by değerlerinin mümkün olduğunca benzersiz olduğundan emin olun.
frame_clause: İsteğe bağlıdır. Pencerenin sınırlarını tanımlar. frame_clause hakkında daha fazla bilgi için bkz. frame_clause.

filter_clause

filter_clause sözdizimi:

FILTER (WHERE filter_condition)

filter_condition bir Boole ifadesidir ve bir select ... from ... where ifadesindeki WHERE yan tümcesiyle aynı şekilde kullanılır.

Bir FILTER yan tümcesi sağlarsanız, yalnızca filter_condition koşulunun true olarak değerlendirildiği satırlar pencere çerçevesine dahil edilir. Toplama pencere işlevleri (COUNT, SUM, AVG, MAX ve MIN gibi) için her satır için yine de bir değer döndürülür. Ancak, FILTER ifadesinin true olarak değerlendirilmediği (NULL veya false gibi) satırlar her satırın hesaplaması için pencere çerçevesine dahil edilmez. NULL, false olarak kabul edilir.

Örnek

Verileri hazırlayın

-- Create a table.
CREATE TABLE IF NOT EXISTS mf_window_fun(key BIGINT,value BIGINT) STORED AS ALIORC;

-- Insert data.
insert into mf_window_fun values (1,100),(2,200),(1,150),(2,250),(3,300),(4,400),(5,500),(6,600),(7,700);

-- Query data from the mf_window_fun table.
select * from mf_window_fun;

-- The following result is returned:
+------------+------------+
| key        | value      |
+------------+------------+
| 1          | 100        |
| 2          | 200        |
| 1          | 150        |
| 2          | 250        |
| 3          | 300        |
| 4          | 400        |
| 5          | 500        |
| 6          | 600        |
| 7          | 700        |
+------------+------------+

Pencere içinde değeri 100'den büyük olan satırların kümülatif toplamını sorgulayın.

select key,sum(value) filter(where value > 100) 
       over (partition by key order by key)  
       from mf_window_fun;

Aşağıdaki sonuç döndürülür:

+------------+------------+
| key        | _c1        |
+------------+------------+
| 1          | NULL       | -- Skipped
| 1          | 150        |
| 2          | 200        |
| 2          | 450        |
| 3          | 300        |
| 4          | 400        |
| 5          | 500        |
| 6          | 600        |
| 7          | 700        |
+------------+------------+

Not

FILTER yan tümcesi, filter_condition koşulunu karşılamayan satırları sorgu sonucundan kaldırmaz. Yalnızca bunları pencere işlevinin hesaplamasından hariç tutar. Bu satırları nihai çıktıda kaldırmak için bir select ... from ... where yan tümcesi kullanmanız gerekir. Hariç tutulan bir satır için pencere işlevinin değeri 0 veya NULL değildir. Bunun yerine, önceki satırdan gelen değeri devralır.
FILTER yan tümcesini yalnızca COUNT, SUM, AVG, MAX, MIN ve WM_CONCAT gibi toplama pencere işlevleriyle kullanabilirsiniz. FILTER yan tümcesini RANK, ROW_NUMBER veya NTILE gibi toplamaya dayalı olmayan işlevlerle kullanamazsınız. Aksi takdirde bir sözdizimi hatası oluşur.
Bir pencere işlevinde FILTER sözdizimini kullanmak için aşağıdaki oturum bayrağını etkinleştirmeniz gerekir: set odps.sql.window.function.newimpl=true;.

frame_clause

frame_clause sözdizimi:

-- Format 1
{ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
-- Format 2
{ROWS|RANGE|GROUPS} between <frame_start> and <frame_end> [<frame_exclusion>]

frame_clause, pencerenin sınırlarını tanımlayan kapalı bir aralıktır. frame_start ve frame_end konumundaki satırları içerir.

ROWS|RANGE|GROUPS: Gereklidir. frame_clause türü. frame_start ve frame_end için uygulama kuralları türe göre değişir.
- ROWS: Pencere sınırlarını satır sayısına göre tanımlar.
- RANGE: Pencere sınırlarını order by sütununun değerlerini karşılaştırarak tanımlar. Genellikle pencere tanımında bir order by yan tümcesi belirtilir. Bir order by yan tümcesi belirtilmezse bir bölümdeki tüm satırlar aynı order by sütun değerine sahip olur. NULL değerler eşit kabul edilir.
- GROUPS: Aynı order by sütun değerine sahip bir bölümdeki tüm satırlar bir GROUP (grup) oluşturur. Bir order by yan tümcesi belirtilmezse bölümdeki tüm satırlar tek bir GROUP oluşturur. NULL değerler eşit kabul edilir.

frame_start ve frame_end: Pencerenin başlangıç ve bitiş sınırlarını belirtir. frame_start gereklidir. frame_end isteğe bağlıdır. Atlanırsa varsayılan değer CURRENT ROW olur.

frame_start tarafından belirtilen konum, frame_end tarafından belirtilen konumdan önce gelmeli veya frame_end konumuyla eşleşmelidir. Başka bir deyişle, frame_start, frame_end konumuna kıyasla bölümün başına daha yakındır. Bölümün başı, veriler pencere tanımındaki order by ifadesine göre sıralandıktan sonra ilk satırın konumudur. Aşağıdaki tabloda, frame_clause türü ROWS, RANGE veya GROUPS olduğunda frame_start ve frame_end için geçerli değerler ve mantık açıklanmaktadır.

frame_clause türü	frame_start/frame_end değeri	Açıklama
ROWS, RANGE, GROUPS	UNBOUNDED PRECEDING	Bölümün ilk satırı. Sayım 1'den başlar.
ROWS, RANGE, GROUPS	UNBOUNDED FOLLOWING	Bölümün son satırı.
ROWS	CURRENT ROW	Geçerli satırın konumu. Her veri satırı bir pencere işlevi sonucuna karşılık gelir. Geçerli satır, pencere işlevi sonucunun hesaplandığı satırdır.
	offset PRECEDING	Geçerli satırdan, bölümün başına doğru `offset` satır önceki konum. Örneğin, `0 PRECEDING` geçerli satırı, `1 PRECEDING` ise önceki satırı ifade eder. `offset` negatif olmayan bir tamsayı olmalıdır.
	offset FOLLOWING	Geçerli satırdan, bölümün sonuna doğru `offset` satır sonraki konum. Örneğin, `0 FOLLOWING` geçerli satırı, `1 FOLLOWING` ise sonraki satırı ifade eder. `offset` negatif olmayan bir tamsayı olmalıdır.
RANGE	CURRENT ROW	frame_start olarak, geçerli satırla aynı `order by` sütun değerine sahip ilk satırın konumunu ifade eder. frame_end olarak, geçerli satırla aynı `order by` sütun değerine sahip son satırın konumunu ifade eder.
	offset PRECEDING	frame_start ve frame_end konumları `order by` dizisine bağlıdır. Pencerenin X'e göre sıralandığını varsayalım. Xi, i. satırın X değerini; Xc ise geçerli satırın X değerini temsil eder. Konumlar aşağıdaki gibi açıklanır: `order by` artan düzende olduğunda: frame_start: `Xc - Xi <= offset` koşulunu sağlayan ilk satırın konumu. frame_end: `Xc - Xi >= offset` koşulunu sağlayan son satırın konumu. `order by` azalan düzende olduğunda: frame_start: `Xi - Xc <= offset` koşulunu sağlayan ilk satırın konumu. frame_end: `Xi - Xc >= offset` koşulunu sağlayan son satırın konumu. `order by` sütunu için desteklenen veri türleri şunlardır: TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, DATETIME, DATE ve TIMESTAMP. Tarih türleri için `offset` sözdizimi aşağıdaki gibidir: `N`: N gün veya N saniyeyi temsil eder. Negatif olmayan bir tamsayı olmalıdır. DATETIME ve TIMESTAMP için N saniyeyi, DATE için N günü temsil eder. `interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}`: N yıl, ay, gün, saat, dakika veya saniyeyi temsil eder. Örneğin, `INTERVAL '3' YEAR` 3 yılı temsil eder. `INTERVAL 'N-M' YEAR TO MONTH`: N yıl ve M ayı temsil eder. Örneğin, `INTERVAL '1-3' YEAR TO MONTH` 1 yıl ve 3 ayı temsil eder. `INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND`: D gün, H saat, M dakika, S saniye ve N nanosaniyeyi temsil eder. Örneğin, `INTERVAL '1 2:3:4:5' DAY TO SECOND` 1 gün, 2 saat, 3 dakika, 4 saniye ve 5 nanosaniyeyi temsil eder.
	offset FOLLOWING	frame_start ve frame_end konumları `order by` dizisine bağlıdır. Pencerenin X'e göre sıralandığını varsayalım. Xi, i. satırın X değerini; Xc ise geçerli satırın X değerini temsil eder. Konumlar aşağıdaki gibi açıklanır: `order by` artan düzende olduğunda: frame_start: `Xi - Xc >= offset` koşulunu sağlayan ilk satırın konumu. frame_end: `Xi - Xc <= offset` koşulunu sağlayan son satırın konumu. `order by` azalan düzende olduğunda: frame_start: `Xc - Xi >= offset` koşulunu sağlayan ilk satırın konumu. frame_end: `Xc - Xi <= offset` koşulunu sağlayan son satırın konumu.
GROUPS	CURRENT ROW	frame_start olarak, geçerli satırın ait olduğu GROUP'un ilk satırını ifade eder. frame_end olarak, geçerli satırın ait olduğu GROUP'un son satırını ifade eder.
	offset PRECEDING	frame_start olarak, geçerli satırın GROUP'undan, bölümün başına doğru `offset` GROUP önceki GROUP'taki ilk satırın konumunu ifade eder. frame_end olarak, geçerli satırın GROUP'undan, bölümün başına doğru `offset` GROUP önceki GROUP'taki son satırın konumunu ifade eder. Not frame_start değerini UNBOUNDED FOLLOWING veya frame_end değerini UNBOUNDED PRECEDING olarak ayarlayamazsınız.
	offset FOLLOWING	frame_start olarak, geçerli satırın GROUP'undan, bölümün sonuna doğru `offset` GROUP sonraki GROUP'taki ilk satırın konumunu ifade eder. frame_end olarak, geçerli satırın GROUP'undan, bölümün sonuna doğru `offset` GROUP sonraki GROUP'taki son satırın konumunu ifade eder. Not frame_start değerini UNBOUNDED FOLLOWING veya frame_end değerini UNBOUNDED PRECEDING olarak ayarlayamazsınız.

frame_exclusion: İsteğe bağlıdır. Verilerin bir kısmını pencereden hariç tutmak için kullanılır. Geçerli değerler şunlardır:
- EXCLUDE NO OTHERS: Hiçbir veriyi hariç tutmaz.
- EXCLUDE CURRENT ROW: Geçerli satırı hariç tutar.
- EXCLUDE GROUP: Geçerli satırla aynı order by değerine sahip bölümdeki tüm verileri ifade eden tüm GROUP'u hariç tutar.
- EXCLUDE TIES: Geçerli satırın kendisi hariç, geçerli satırla aynı order by değerini paylaşan tüm satırları hariç tutar.

Varsayılan frame_clause

Bir frame_clause belirtmezseniz MaxCompute, pencereye dahil edilen verilerin sınırlarını belirlemek için varsayılan bir frame_clause kullanır. Varsayılan frame_clause şöyledir:

Hive uyumlu modu etkinleştirildiğinde (set odps.sql.hive.compatible=true;), varsayılan frame_clause aşağıdaki gibidir ve çoğu diğer SQL sistemiyle aynıdır.
```
RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```
Hive uyumlu modu devre dışı bırakıldığında (set odps.sql.hive.compatible=false;), bir order by yan tümcesi belirtilirse ve pencere işlevi AVG, COUNT, MAX, MIN, STDDEV, STDDEV_POP, STDDEV_SAMP veya SUM ise varsayılan frame_clause ROWS türündedir.
```
ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```

Pencere sınırı örnekleri

tbl tablosunun pid: bigint, oid: bigint, rid: bigint yapısına sahip olduğunu ve aşağıdaki verileri içerdiğini varsayalım:

+------------+------------+------------+
| pid        | oid        | rid        |
+------------+------------+------------+
| 1          | NULL       | 1          |
| 1          | NULL       | 2          |
| 1          | 1          | 3          |
| 1          | 1          | 4          |
| 1          | 2          | 5          |
| 1          | 4          | 6          |
| 1          | 7          | 7          |
| 1          | 11         | 8          |
| 2          | NULL       | 9          |
| 2          | NULL       | 10         |
+------------+------------+------------+

ROW türü pencere

Pencere tanımı 1

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1]    |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9]    |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Pencere tanımı 2

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as window from tbl;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 3          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 4          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Pencere tanımı 3

partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between 1 FOLLOWING and 3 FOLLOWING) as window from tbl;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [2, 3, 4] |
| 1          | NULL       | 2          | [3, 4, 5] |
| 1          | 1          | 3          | [4, 5, 6] |
| 1          | 1          | 4          | [5, 6, 7] |
| 1          | 2          | 5          | [6, 7, 8] |
| 1          | 4          | 6          | [7, 8] |
| 1          | 7          | 7          | [8]    |
| 1          | 11         | 8          | NULL   |
| 2          | NULL       | 9          | [10]   |
| 2          | NULL       | 10         | NULL   |
+------------+------------+------------+--------+

Pencere tanımı 4

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
--SQL statement is as follows.
select pid, 
oid, 
rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) as window from tbl;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | NULL   |
| 1          | NULL       | 2          | [1]    |
| 1          | 1          | 3          | [1, 2] |
| 1          | 1          | 4          | [1, 2, 3] |
| 1          | 2          | 5          | [1, 2, 3, 4] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
| 2          | NULL       | 9          | NULL   |
| 2          | NULL       | 10         | [9]    |
+------------+------------+------------+--------+

Pencere tanımı 5

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP) as window from tbl;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | NULL   |
| 1          | NULL       | 2          | NULL   |
| 1          | 1          | 3          | [1, 2] |
| 1          | 1          | 4          | [1, 2] |
| 1          | 2          | 5          | [1, 2, 3, 4] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
| 2          | NULL       | 9          | NULL   |
| 2          | NULL       | 10         | NULL   |
+------------+------------+------------+--------+

Pencere tanımı 6

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES) as window from tbl;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1]    |
| 1          | NULL       | 2          | [2]    |
| 1          | 1          | 3          | [1, 2, 3] |
| 1          | 1          | 4          | [1, 2, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9]    |
| 2          | NULL       | 10         | [10]   |
+------------+------------+------------+--------+

Bu örnekte ve önceki örnekte rid değeri 2, 4 ve 10 olan satırlar için window sonuçlarını karşılaştırarak EXCLUDE CURRENT ROW ile EXCLUDE GROUP arasındaki farkı gözlemleyebilirsiniz. EXCLUDE GROUP kullanıldığında, aynı bölümde (pid değerinin eşit olduğu durumlarda), geçerli satırla aynı oid değerine sahip tüm veriler dışlanır.

RANGE türü pencere

Pencere tanımı 1

partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3, 4] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

CURRENT ROW, frame_end olarak kullanıldığında, geçerli satırla aynı order by değerine (oid) sahip son satıra kadar olan tüm satırları kapsar. Bu nedenle, rid değeri 1 olan kayıt için window sonucu [1, 2] olarak döner.

Pencere tanımı 2

partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING) as window from tbl;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 3          | [3, 4, 5, 6, 7, 8] |
| 1          | 1          | 4          | [3, 4, 5, 6, 7, 8] |
| 1          | 2          | 5          | [5, 6, 7, 8] |
| 1          | 4          | 6          | [6, 7, 8] |
| 1          | 7          | 7          | [7, 8] |
| 1          | 11         | 8          | [8]    |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Pencere tanımı 3

partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
--SQL statement is as follows.

select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between 3 PRECEDING and 1 PRECEDING) as window from tbl;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | NULL   |
| 1          | 1          | 4          | NULL   |
| 1          | 2          | 5          | [3, 4] |
| 1          | 4          | 6          | [3, 4, 5] |
| 1          | 7          | 7          | [6]    |
| 1          | 11         | 8          | NULL   |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

order by değeri (oid) NULL olan satırlar için, offset {PRECEDING|FOLLOWING} kullanıldığında ve offset değeri UNBOUNDED olmadığında sınır aşağıdaki şekilde belirlenir: frame_start olarak kullanıldığında, bölümdeki NULL order by değerine sahip ilk satırı işaret eder. frame_end olarak kullanıldığında ise NULL order by değerine sahip son satırı işaret eder.

GROUPS türü pencere

Pencere tanımı aşağıdaki gibidir:

partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid GROUPS between 2 PRECEDING and CURRENT ROW) as window from tbl;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3, 4] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [3, 4, 5, 6] |
| 1          | 7          | 7          | [5, 6, 7] |
| 1          | 11         | 8          | [6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Örnek veriler

Aşağıdaki örneklerde bu örnek veriler kullanılmaktadır. emp tablosunu oluşturun ve doldurun:

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp;

emp.txt dosyası aşağıdaki verileri içerir:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

AVG

Sözdizimi

double avg([distinct] double <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal avg([distinct] decimal <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Açıklama

Bir pencere içindeki expr ifadesinin ortalama değerini döndürür.
Parametreler
- expr: Zorunludur. Sonucu hesaplanacak ifadedir. DOUBLE veya DECIMAL türünde olmalıdır.
  - Giriş değeri STRING veya BIGINT türündeyse hesaplama için örtük olarak DOUBLE türüne dönüştürülür. Diğer veri türleri için hata döndürülür.
  - Giriş değeri NULL ise satır hesaplamaya dahil edilmez.
  - distinct anahtar sözcüğünü belirtirseniz işlev benzersiz değerlerin ortalamasını hesaplar.
- partition_clause, orderby_clause ve frame_clause: Daha fazla bilgi için bkz. pencerleme_tanımı.
Dönüş değeri

expr DECIMAL türündeyse DECIMAL değeri, aksi takdirde DOUBLE değeri döndürülür. expr değerlerinin tümü NULL ise NULL döndürülür.

Örnekler

Örnek 1: Departmana (deptno) göre bölümlendirin ve sıralama olmadan ortalama maaşı (sal) hesaplayın. İşlev, tüm bölüm için (aynı deptno değerine sahip tüm satırlar) ortalama maaşı hesaplar. Komut aşağıdaki gibidir:

select deptno, sal, avg(sal) over (partition by deptno) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2916.6666666666665 |   -- This is the first row of the window. The value is the cumulative average from the first to the sixth row.
| 10         | 2450       | 2916.6666666666665 |   -- The value is the cumulative average from the first to the sixth row.
| 10         | 5000       | 2916.6666666666665 |   -- The value is the cumulative average from the first to the sixth row.
| 10         | 1300       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 10         | 2450       | 2916.6666666666665 |
| 20         | 3000       | 2175.0     |
| 20         | 3000       | 2175.0     |
| 20         | 800        | 2175.0     |
| 20         | 1100       | 2175.0     |
| 20         | 2975       | 2175.0     |
| 30         | 1500       | 1566.6666666666667 |
| 30         | 950        | 1566.6666666666667 |
| 30         | 1600       | 1566.6666666666667 |
| 30         | 1250       | 1566.6666666666667 |
| 30         | 1250       | 1566.6666666666667 |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

Örnek 2: Hive uyumlu olmayan modda departmana (deptno) göre bölümlendirin, maaşa (sal) göre sıralayın ve ortalama maaşı hesaplayın. İşlev, bölümün ilk satırından geçerli satıra kadar olan çalışan sayısı için bir hareketli ortalama hesaplar. Komutlar aşağıdaki gibidir:

-- Disable Hive compatible mode.
set odps.sql.hive.compatible=false;
-- Execute the following SQL command.
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |           -- First row of the window.
| 10         | 1300       | 1300.0     |           -- Cumulative average from the first to the second row.
| 10         | 2450       | 1683.3333333333333 |   -- Cumulative average from the first to the third row.
| 10         | 2450       | 1875.0     |           -- Cumulative average from the first to the fourth row.
| 10         | 5000       | 2500.0     |           -- Cumulative average from the first to the fifth row.
| 10         | 5000       | 2916.6666666666665 |   -- Cumulative average from the first to the sixth row.
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 1968.75    |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1100.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

Örnek 3: Hive uyumlu modda departmana (deptno) göre bölümlendirin, maaşa (sal) göre sıralayın ve ortalama maaşı hesaplayın. İşlev, bölümün ilk satırından geçerli satırın son eş değerine (aynı sal değerine sahip satırlar aynı ortalamaya sahiptir) kadar olan çalışan sayısı için bir hareketli ortalama hesaplar. Komutlar aşağıdaki gibidir:

-- Enable Hive compatible mode.
set odps.sql.hive.compatible=true;
-- Execute the following SQL command.
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |          -- First row of the window. Since the sal of the first and second rows are the same, the average for the first row is the cumulative average of the first two rows.
| 10         | 1300       | 1300.0     |          -- Cumulative average from the first to the second row.
| 10         | 2450       | 1875.0     |          -- Since the sal of the third and fourth rows are the same, the average for the third row is the cumulative average of the first four rows.
| 10         | 2450       | 1875.0     |          -- Cumulative average from the first to the fourth row.
| 10         | 5000       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 2175.0     |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1150.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

CLUSTER_SAMPLE

Sözdizimi

boolean cluster_sample(bigint <N>) OVER ([partition_clause])
boolean cluster_sample(bigint <N>, bigint <M>) OVER ([partition_clause])

Açıklama
- cluster_sample(bigint <N>): Bölümden rastgele N satır örnekler.
- cluster_sample(bigint <N>, bigint <M>): Bölümden satırların bir kesrini (M/N) rastgele örnekler. Örneklelenen satır sayısı yaklaşık olarak partition_row_count × M / N kadardır; burada partition_row_count bölümdeki satır sayısını ifade eder.
Parametreler
- N: Zorunludur. BIGINT sabitidir. N NULL ise dönüş değeri NULL olur.
- M: Zorunludur. BIGINT sabitidir. M NULL ise dönüş değeri NULL olur.
- partition_clause: İsteğe bağlıdır. Daha fazla bilgi için bkz. pencerleme_tanımı.
Dönüş değeri

BOOLEAN değeri döndürür.

Örnek

Her gruptan satırların yaklaşık %20'sini örneklemek için aşağıdaki komutu kullanın:

select deptno, sal
    from (
        select deptno, sal, cluster_sample(5, 1) over (partition by deptno) as flag
        from emp
        ) sub
    where flag = true;

Aşağıdaki sonuç döndürülür:

+------------+------------+
| deptno     | sal        |
+------------+------------+
| 10         | 1300       |
| 20         | 3000       |
| 30         | 950        |
+------------+------------+

COUNT

Sözdizimi

-- Count the number of records.
BIGINT COUNT([DISTINCT|ALL] <colname>)

-- Count the number of records in the window.
BIGINT COUNT(*) OVER ([partition_clause] [orderby_clause] [frame_clause])
BIGINT COUNT([DISTINCT] <expr>[,...]) OVER ([partition_clause] [orderby_clause] [frame_clause])

Parametreler

Parametre	Zorunlu	Açıklama
`DISTINCT \| ALL`	Hayır	Yinelenen değerlerin nasıl işleneceğini kontrol eder. `ALL` (varsayılan) NULL olmayan tüm satırları sayar. `DISTINCT` yalnızca benzersiz NULL olmayan değerleri sayar.
`colname`	Evet	Sayılacak sütun. Herhangi bir veri türünü kabul eder. Sütun değeri NULL olan satırlar dahil tüm satırları saymak için `*` kullanın.
`expr`	Evet	Herhangi bir veri türünde ifade. NULL satırlar hariç tutulur. `DISTINCT` ile yalnızca benzersiz NULL olmayan değerler sayılır. `COUNT([DISTINCT] <expr>[,...]) OVER` tüm belirtilen ifadelerin NULL olmadığı satırları sayar.
`partition_clause`, `orderby_clause`, `frame_clause`	Hayır	Pencere tanımlama yan tümceleri. Bkz. Pencere İşlevlerine Genel Bakış.

Dönüş değeri

BIGINT döndürür. COUNT(*) kullanmadığınız sürece NULL satırlar hariç tutulur.

Örnekler

Test verilerini hazırlayın

Verileriniz zaten varsa bu adımı atlayabilirsiniz.

Test verilerini test_data.txt indirin.

Bir test tablosu oluşturun.

CREATE TABLE IF NOT EXISTS emp(
  empno BIGINT,
  ename STRING,
  job STRING,
  mgr BIGINT,
  hiredate DATETIME,
  sal BIGINT,
  comm BIGINT,
  deptno BIGINT
);

Verileri yükleyin.

FILE_PATH yerine veri dosyanızın gerçek yolunu ve adını yazın.
```
TUNNEL UPLOAD {{FILEPATH}} emp;
```

Örnek 1: Sıralama olmadan pencereyi bölümlendirme

Pencereyi sal ile bölümlendirin. ORDER BY olmadan her satır, kendi bölümündeki toplam satır sayısını döndürür.

SELECT sal, COUNT(sal) OVER (PARTITION BY sal) AS count
FROM emp;

Sonuç:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Two rows share sal=1250; both return 2.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

Örnek 2: Sıralama ile pencereyi bölümlendirme (Hive uyumlu olmayan mod)

Hive uyumlu olmayan modda ORDER BY eklemek bir hareketli sayım üretir. Her satır, bölümündeki ilk satırdan geçerli satıra kadar olan kümülatif sayımı döndürür.

-- Disable Hive compatible mode.
SET odps.sql.hive.compatible=false;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

Sonuç:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |  -- Running count starts at 1 for the first row in the partition.
| 1250       | 2          |  -- Increments to 2 for the second row.
| 1300       | 1          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 1          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 1          |
| 3000       | 2          |
| 5000       | 1          |
| 5000       | 2          |
+------------+------------+

Örnek 3: Sıralama ile pencereyi bölümlendirme (Hive uyumlu mod)

Hive uyumlu modda ORDER BY hareketli sayım üretmez. Bölümdeki her satır, ORDER BY öğesini atlamakla aynı şekilde toplam bölüm sayısını döndürür.

-- Enable Hive compatible mode.
SET odps.sql.hive.compatible=true;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

Sonuç:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Both rows in the partition return the full partition count.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

Örnek 4: Bir tablodaki tüm satırları sayma

SELECT COUNT(*) FROM emp;

Sonuç:

+------------+
| _c0        |
+------------+
| 17         |
+------------+

Örnek 5: Grup başına satır sayma

Departman başına çalışan sayısını almak için GROUP BY ile COUNT kullanın.

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

Sonuç:

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 20         | 5          |
| 30         | 6          |
| 10         | 6          |
+------------+------------+

Örnek 6: Benzersiz değerleri sayma

Farklı departmanların sayısını hesaplamak için DISTINCT kullanın.

SELECT COUNT(DISTINCT deptno) FROM emp;

Sonuç:

+------------+
| _c0        |
+------------+
| 3          |
+------------+

CUME_DIST

Sözdizimi

double cume_dist() over([partition_clause] [orderby_clause])

Açıklama

Bir değer grubu içindeki bir değerin kümülatif dağılımını hesaplar. Sonuç, değeri geçerli satırın değerinden küçük veya ona eşit olan satır sayısının, bölümdeki toplam satır sayısına bölünmesiyle elde edilir. Karşılaştırma, orderby_clause tarafından belirlenir.
Parametreler

partition_clause ve orderby_clause: Daha fazla bilgi için bkz. pencerleme_tanımı.
Dönüş değeri

DOUBLE değeri döndürür. Belirli dönüş değeri row_number_of_last_peer / partition_row_count değerine eşittir; burada row_number_of_last_peer geçerli satırın GRUBU'nun son satırı için ROW_NUMBER pencere işlevinin dönüş değeridir ve partition_row_count satırın ait olduğu bölümdeki satır sayısıdır.

Örnek

Departmana (deptno) göre bölümlendirin ve her departman içindeki maaşın (sal) kümülatif dağılımını hesaplayın. Komut aşağıdaki gibidir:

select deptno, ename, sal, concat(round(cume_dist() over (partition by deptno order by sal desc)*100,2),'%') as cume_dist from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | cume_dist  |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 33.33%     |
| 10         | KING       | 5000       | 33.33%     |
| 10         | CLARK      | 2450       | 66.67%     |
| 10         | WELAN      | 2450       | 66.67%     |
| 10         | TEBAGE     | 1300       | 100.0%     |
| 10         | MILLER     | 1300       | 100.0%     |
| 20         | SCOTT      | 3000       | 40.0%      |
| 20         | FORD       | 3000       | 40.0%      |
| 20         | JONES      | 2975       | 60.0%      |
| 20         | ADAMS      | 1100       | 80.0%      |
| 20         | SMITH      | 800        | 100.0%     |
| 30         | BLAKE      | 2850       | 16.67%     |
| 30         | ALLEN      | 1600       | 33.33%     |
| 30         | TURNER     | 1500       | 50.0%      |
| 30         | MARTIN     | 1250       | 83.33%     |
| 30         | WARD       | 1250       | 83.33%     |
| 30         | JAMES      | 950        | 100.0%     |
+------------+------------+------------+------------+

DENSE_RANK

Sözdizimi

bigint dense_rank() over ([partition_clause] [orderby_clause])

Açıklama

orderby_clause içinde belirtilen sıralama düzenine göre geçerli satırın bölüm içindeki sırasını hesaplar. Sıralama 1'den başlar. Bir bölümde aynı order by değerlerine sahip satırlara aynı sıra atanır. order by değeri her değiştiğinde sıra 1 artar.
Parametreler

partition_clause ve orderby_clause: Daha fazla bilgi için bkz. pencerleme_tanımı.
Dönüş değeri

BIGINT değeri döndürür. orderby_clause belirtilmezse tüm satırlar 1 sırası alır.

Örnek

Departmana (deptno) göre bölümlendirin ve çalışanları her departman içinde maaşlarına (sal) göre azalan sırada sıralayın. Komut aşağıdaki gibidir:

select deptno, ename, sal, dense_rank() over (partition by deptno order by sal desc) as nums from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 2          |
| 10         | WELAN      | 2450       | 2          |
| 10         | TEBAGE     | 1300       | 3          |
| 10         | MILLER     | 1300       | 3          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 2          |
| 20         | ADAMS      | 1100       | 3          |
| 20         | SMITH      | 800        | 4          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 4          |
| 30         | JAMES      | 950        | 5          |
+------------+------------+------------+------------+

FIRST_VALUE

Sözdizimi

first_value(<expr>[, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

Açıklama

Pencere çerçevesinin ilk satırındaki expr ifadesinin değerini döndürür.
Parametreler
- expr: Zorunludur. Sonucunu hesaplamak istediğiniz ifade.
- ignore_nulls: İsteğe bağlıdır. NULL değerlerin yoksayılıp yoksayılmayacağını belirten bir BOOLEAN değeridir. Varsayılan değer False'tur. Bu parametre True olarak ayarlandığında, işlev pencere çerçevesindeki expr ifadesinin ilk NULL olmayan değerini döndürür.
- partition_clause, orderby_clause ve frame_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

Dönüş değeri, expr ile aynı veri türüne sahiptir.

Örnek

Aşağıdaki komut, tüm çalışanları departmana göre gruplar ve her gruptan ilk veri satırını döndürür:

order by belirtilmemiş durumda:

select deptno, ename, sal, first_value(sal) over (partition by deptno) as first_value from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | first_value |
+------------+------------+------------+-------------+
| 10         | TEBAGE     | 1300       | 1300        |   -- First row of the current window.
| 10         | CLARK      | 2450       | 1300        |
| 10         | KING       | 5000       | 1300        |
| 10         | MILLER     | 1300       | 1300        |
| 10         | JACCKA     | 5000       | 1300        |
| 10         | WELAN      | 2450       | 1300        |
| 20         | FORD       | 3000       | 3000        |   -- First row of the current window.
| 20         | SCOTT      | 3000       | 3000        |
| 20         | SMITH      | 800        | 3000        |
| 20         | ADAMS      | 1100       | 3000        |
| 20         | JONES      | 2975       | 3000        |
| 30         | TURNER     | 1500       | 1500        |   -- First row of the current window.
| 30         | JAMES      | 950        | 1500        |
| 30         | ALLEN      | 1600       | 1500        |
| 30         | WARD       | 1250       | 1500        |
| 30         | MARTIN     | 1250       | 1500        |
| 30         | BLAKE      | 2850       | 1500        |
+------------+------------+------------+-------------+

order by belirtildiğinde:

select deptno, ename, sal, first_value(sal) over (partition by deptno order by sal desc) as first_value from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | first_value |
+------------+------------+------------+-------------+
| 10         | JACCKA     | 5000       | 5000        |   -- First row of the current window.
| 10         | KING       | 5000       | 5000        |
| 10         | CLARK      | 2450       | 5000        |
| 10         | WELAN      | 2450       | 5000        |
| 10         | TEBAGE     | 1300       | 5000        |
| 10         | MILLER     | 1300       | 5000        |
| 20         | SCOTT      | 3000       | 3000        |   -- First row of the current window.
| 20         | FORD       | 3000       | 3000        |
| 20         | JONES      | 2975       | 3000        |
| 20         | ADAMS      | 1100       | 3000        |
| 20         | SMITH      | 800        | 3000        |
| 30         | BLAKE      | 2850       | 2850        |   -- First row of the current window.
| 30         | ALLEN      | 1600       | 2850        |
| 30         | TURNER     | 1500       | 2850        |
| 30         | MARTIN     | 1250       | 2850        |
| 30         | WARD       | 1250       | 2850        |
| 30         | JAMES      | 950        | 2850        |
+------------+------------+------------+-------------+

LAG

Sözdizimi

lag(<expr>[,bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

Açıklama

Mevcut satırdan offset satır önceki (bölümün başına doğru) satırdaki expr ifadesinin değerini döndürür. expr ifadesi bir sütun, sütun işlemi veya işlev işlemi olabilir.
Parametreler
- expr: Zorunludur. Hesaplanacak ifade.
- offset: İsteğe bağlıdır. 1'e eşit veya 1'den büyük bir BIGINT sabiti olan ofset değeri. 1 değeri önceki satırı gösterir. Varsayılan değer 1'dir. Giriş değeri STRING veya DOUBLE türündeyse, hesaplama için örtülü olarak BIGINT türüne dönüştürülür.
- default: İsteğe bağlıdır. offset sınırların dışına çıktığında döndürülecek varsayılan değeri belirtir. Bu parametre belirtilmezse varsayılan değer NULL olur. Değer, expr ile aynı veri türüne sahip bir sabit olmalıdır. expr bir sabit değilse bu değer mevcut satıra göre değerlendirilir.
- partition_clause ve orderby_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

Dönüş değeri, expr ile aynı veri türüne sahiptir.

Örnek

Departmana (deptno) göre bölümleme yapın ve her çalışan için önceki satırdaki maaşı (sal) alın. Komut aşağıdaki gibidir:

select deptno, ename, sal, lag(sal, 1) over (partition by deptno order by sal) as sal_new from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | NULL       |
| 10         | MILLER     | 1300       | 1300       |
| 10         | CLARK      | 2450       | 1300       |
| 10         | WELAN      | 2450       | 2450       |
| 10         | KING       | 5000       | 2450       |
| 10         | JACCKA     | 5000       | 5000       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | 800        |
| 20         | JONES      | 2975       | 1100       |
| 20         | SCOTT      | 3000       | 2975       |
| 20         | FORD       | 3000       | 3000       |
| 30         | JAMES      | 950        | NULL       |
| 30         | MARTIN     | 1250       | 950        |
| 30         | WARD       | 1250       | 1250       |
| 30         | TURNER     | 1500       | 1250       |
| 30         | ALLEN      | 1600       | 1500       |
| 30         | BLAKE      | 2850       | 1600       |
+------------+------------+------------+------------+

LAST_VALUE

Sözdizimi

last_value(<expr>[, <ignore_nulls>]) over([partition_clause] [orderby_clause] [frame_clause])

Açıklama

Pencere çerçevesinin son satırındaki expr ifadesinin değerini döndürür.

Parametreler

expr: Zorunludur. Hesaplanacak ifade.
ignore_nulls: İsteğe bağlıdır. NULL değerlerin yoksayılıp yoksayılmayacağını belirten bir BOOLEAN değeridir. Varsayılan değer False'tur. Bu parametre True olarak ayarlandığında, işlev pencere çerçevesindeki expr ifadesinin son NULL olmayan değerini döndürür.
partition_clause, orderby_clause ve frame_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

Dönüş değeri, expr ile aynı veri türüne sahiptir.

Örnek

Aşağıdaki komut, tüm çalışanları departmana göre gruplar ve her gruptan son veri satırını döndürür:

order by yan tümcesi olmadan pencere çerçevesi, bölümdeki tüm satırları içerir. İşlev, bölümdeki son satırdaki değeri döndürür.

select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | last_value |
+------------+------------+------------+-------------+
| 10         | TEBAGE     | 1300       | 2450        |
| 10         | CLARK      | 2450       | 2450        |
| 10         | KING       | 5000       | 2450        |
| 10         | MILLER     | 1300       | 2450        |
| 10         | JACCKA     | 5000       | 2450        |   
| 10         | WELAN      | 2450       | 2450        |   -- Last row of the current window.
| 20         | FORD       | 3000       | 2975        |
| 20         | SCOTT      | 3000       | 2975        |
| 20         | SMITH      | 800        | 2975        |
| 20         | ADAMS      | 1100       | 2975        |
| 20         | JONES      | 2975       | 2975        |   -- Last row of the current window.
| 30         | TURNER     | 1500       | 2850        |
| 30         | JAMES      | 950        | 2850        |
| 30         | ALLEN      | 1600       | 2850        |
| 30         | WARD       | 1250       | 2850        |
| 30         | MARTIN     | 1250       | 2850        |
| 30         | BLAKE      | 2850       | 2850        |   -- Last row of the current window.
+------------+------------+------------+-------------+

order by yan tümcesi ile birlikte kullanıldığında varsayılan pencere çerçevesi RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW olur. İşlev, mevcut satırdaki değeri döndürür.

select deptno, ename, sal, last_value(sal) over (partition by deptno order by sal desc) as last_value from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | last_value |
+------------+------------+------------+-------------+
| 10         | JACCKA     | 5000       | 5000        |   -- Current row of the current window.
| 10         | KING       | 5000       | 5000        |   -- Current row of the current window.
| 10         | CLARK      | 2450       | 2450        |   -- Current row of the current window.
| 10         | WELAN      | 2450       | 2450        |   -- Current row of the current window.
| 10         | TEBAGE     | 1300       | 1300        |   -- Current row of the current window.
| 10         | MILLER     | 1300       | 1300        |   -- Current row of the current window.
| 20         | SCOTT      | 3000       | 3000        |   -- Current row of the current window.
| 20         | FORD       | 3000       | 3000        |   -- Current row of the current window.
| 20         | JONES      | 2975       | 2975        |   -- Current row of the current window.
| 20         | ADAMS      | 1100       | 1100        |   -- Current row of the current window.
| 20         | SMITH      | 800        | 800         |   -- Current row of the current window.
| 30         | BLAKE      | 2850       | 2850        |   -- Current row of the current window.
| 30         | ALLEN      | 1600       | 1600        |   -- Current row of the current window.
| 30         | TURNER     | 1500       | 1500        |   -- Current row of the current window.
| 30         | MARTIN     | 1250       | 1250        |   -- Current row of the current window.
| 30         | WARD       | 1250       | 1250        |   -- Current row of the current window.
| 30         | JAMES      | 950        | 950         |   -- Current row of the current window.
+------------+------------+------------+-------------+

LEAD

Sözdizimi

lead(<expr>[, bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

Açıklama

Mevcut satırdan offset satır sonraki (bölümün sonuna doğru) satırdaki expr ifadesinin değerini döndürür. expr ifadesi bir sütun, sütun işlemi veya işlev işlemi olabilir.
Parametreler
- expr: Zorunludur. Hesaplanacak ifade.
- offset: İsteğe bağlıdır. 0'a eşit veya 0'dan büyük bir BIGINT sabiti olan ofset değeri. 0 değeri mevcut satırı, 1 değeri ise sonraki satırı gösterir. Varsayılan değer 1'dir. Giriş değeri STRING veya DOUBLE türündeyse, hesaplama için örtülü olarak BIGINT türüne dönüştürülür.
- default: İsteğe bağlıdır. offset sınırların dışına çıktığında döndürülecek değerdir. Bu değer, expr ile aynı veri türüne sahip bir sabit olmalıdır. Varsayılan değer NULL'dur. expr bir sabit değilse değer mevcut satıra göre değerlendirilir.
- partition_clause ve orderby_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

Dönüş değeri, expr ile aynı veri türüne sahiptir.

Örnek

Departmana (deptno) göre bölümleme yapın ve her çalışan için sonraki satırdaki maaşı (sal) alın. Komut aşağıdaki gibidir:

select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal) as sal_new from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | 1300       |
| 10         | MILLER     | 1300       | 2450       |
| 10         | CLARK      | 2450       | 2450       |
| 10         | WELAN      | 2450       | 5000       |
| 10         | KING       | 5000       | 5000       |
| 10         | JACCKA     | 5000       | NULL       |
| 20         | SMITH      | 800        | 1100       |
| 20         | ADAMS      | 1100       | 2975       |
| 20         | JONES      | 2975       | 3000       |
| 20         | SCOTT      | 3000       | 3000       |
| 20         | FORD       | 3000       | NULL       |
| 30         | JAMES      | 950        | 1250       |
| 30         | MARTIN     | 1250       | 1250       |
| 30         | WARD       | 1250       | 1500       |
| 30         | TURNER     | 1500       | 1600       |
| 30         | ALLEN      | 1600       | 2850       |
| 30         | BLAKE      | 2850       | NULL       |
+------------+------------+------------+------------+

MAX

Sözdizimi

max(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

Açıklama

Bir penceredeki expr ifadesinin maksimum değerini döndürür.
Parametreler
- expr: Zorunludur. Maksimum değeri hesaplamak için kullanılan ifade. BOOLEAN dışında herhangi bir veri türünde olabilir. Değer NULL ise satır hesaplamaya dahil edilmez.
- partition_clause, orderby_clause ve frame_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

Dönüş değeri, expr ile aynı veri türüne sahiptir.

Örnekler

Örnek 1: Departmana (deptno) göre bölümleme yaparak maksimum maaşı (sal) hesaplayın ve sıralama yapmayın. İşlev, mevcut bölümün (aynı deptno değerine sahip satırlar) maksimum değerini döndürür. Komut aşağıdaki gibidir:

select deptno, sal, max(sal) over (partition by deptno) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 5000       |   -- First row of the window. The value is the maximum from the first to the sixth row.
| 10         | 2450       | 5000       |   -- The value is the maximum from the first to the sixth row.
| 10         | 5000       | 5000       |   -- The value is the maximum from the first to the sixth row.
| 10         | 1300       | 5000       |
| 10         | 5000       | 5000       |
| 10         | 2450       | 5000       |
| 20         | 3000       | 3000       |
| 20         | 3000       | 3000       |
| 20         | 800        | 3000       |
| 20         | 1100       | 3000       |
| 20         | 2975       | 3000       |
| 30         | 1500       | 2850       |
| 30         | 950        | 2850       |
| 30         | 1600       | 2850       |
| 30         | 1250       | 2850       |
| 30         | 1250       | 2850       |
| 30         | 2850       | 2850       |
+------------+------------+------------+

Örnek 2: Departmana (deptno) göre bölümleme yaparak maksimum maaşı (sal) hesaplayın ve sonuçları sıralayın. İşlev, mevcut bölümün (aynı deptno değerine sahip satırlar) ilk satırından mevcut satıra kadar olan maksimum değeri döndürür. Komut aşağıdaki gibidir:

select deptno, sal, max(sal) over (partition by deptno order by sal) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- First row of the window.
| 10         | 1300       | 1300       |   -- Maximum value from the first to the second row.
| 10         | 2450       | 2450       |   -- Maximum value from the first to the third row.
| 10         | 2450       | 2450       |   -- Maximum value from the first to the fourth row.
| 10         | 5000       | 5000       |
| 10         | 5000       | 5000       |
| 20         | 800        | 800        |
| 20         | 1100       | 1100       |
| 20         | 2975       | 2975       |
| 20         | 3000       | 3000       |
| 20         | 3000       | 3000       |
| 30         | 950        | 950        |
| 30         | 1250       | 1250       |
| 30         | 1250       | 1250       |
| 30         | 1500       | 1500       |
| 30         | 1600       | 1600       |
| 30         | 2850       | 2850       |
+------------+------------+------------+

MEDIAN

Sözdizimi

median(<expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Açıklama

Bir penceredeki expr ifadesinin medyanını hesaplar.
Parametreler
- expr: Zorunludur. Medyanını hesaplamak istediğiniz ifade. DOUBLE veya DECIMAL türünde olmalıdır.
  - Giriş değeri STRING veya BIGINT türündeyse hesaplama için örtülü olarak DOUBLE türüne dönüştürülür. Diğer veri türleri için hata döndürülür.
  - Giriş NULL ise dönüş değeri NULL olur.
- partition_clause, orderby_clause ve frame_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

DOUBLE veya DECIMAL değeri döndürür. Tüm expr değerleri NULL ise NULL döndürülür.

Örnek

Departmana (deptno) göre bölümleme yaparak medyan maaşı (sal) hesaplayın. İşlev, tüm bölümün (aynı deptno değerine sahip tüm satırlar) medyanını döndürür. Komut aşağıdaki gibidir:

select deptno, sal, median(sal) over (partition by deptno) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2450.0     |   -- First row of the window. The value is the median from the first to the sixth row.
| 10         | 2450       | 2450.0     |
| 10         | 5000       | 2450.0     |
| 10         | 1300       | 2450.0     |
| 10         | 5000       | 2450.0     |
| 10         | 2450       | 2450.0     |
| 20         | 3000       | 2975.0     |
| 20         | 3000       | 2975.0     |
| 20         | 800        | 2975.0     |
| 20         | 1100       | 2975.0     |
| 20         | 2975       | 2975.0     |
| 30         | 1500       | 1375.0     |
| 30         | 950        | 1375.0     |
| 30         | 1600       | 1375.0     |
| 30         | 1250       | 1375.0     |
| 30         | 1250       | 1375.0     |
| 30         | 2850       | 1375.0     |
+------------+------------+------------+

MIN

Sözdizimi

min(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

Açıklama

Bir penceredeki expr ifadesinin minimum değerini döndürür.
Parametreler
- expr: Zorunludur. Minimum değeri hesaplamak için kullanılan ifade. BOOLEAN dışında herhangi bir veri türünde olabilir. Değer NULL ise satır hesaplamaya dahil edilmez.
- partition_clause, orderby_clause ve frame_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

Dönüş değeri, expr ile aynı veri türüne sahiptir.

Örnekler

Örnek 1: Departmana (deptno) göre bölümleme yaparak minimum maaşı (sal) hesaplayın ve sıralama yapmayın. İşlev, mevcut bölümün (aynı deptno değerine sahip satırlar) minimum değerini döndürür. Komut aşağıdaki gibidir:

select deptno, sal, min(sal) over (partition by deptno) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- First row of the window. The value is the minimum from the first to the sixth row.
| 10         | 2450       | 1300       |   -- The value is the minimum from the first to the sixth row.
| 10         | 5000       | 1300       |   -- The value is the minimum from the first to the sixth row.
| 10         | 1300       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 2450       | 1300       |
| 20         | 3000       | 800        |
| 20         | 3000       | 800        |
| 20         | 800        | 800        |
| 20         | 1100       | 800        |
| 20         | 2975       | 800        |
| 30         | 1500       | 950        |
| 30         | 950        | 950        |
| 30         | 1600       | 950        |
| 30         | 1250       | 950        |
| 30         | 1250       | 950        |
| 30         | 2850       | 950        |
+------------+------------+------------+

Örnek 2: Departmana (deptno) göre bölümleme yaparak minimum maaşı (sal) hesaplayın ve sonuçları sıralayın. İşlev, mevcut bölümün (aynı deptno değerine sahip satırlar) ilk satırından mevcut satıra kadar olan minimum değeri döndürür. Komut aşağıdaki gibidir:

select deptno, sal, min(sal) over (partition by deptno order by sal) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- First row of the window.
| 10         | 1300       | 1300       |   -- Minimum value from the first to the second row.
| 10         | 2450       | 1300       |   -- Minimum value from the first to the third row.
| 10         | 2450       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 5000       | 1300       |
| 20         | 800        | 800        |
| 20         | 1100       | 800        |
| 20         | 2975       | 800        |
| 20         | 3000       | 800        |
| 20         | 3000       | 800        |
| 30         | 950        | 950        |
| 30         | 1250       | 950        |
| 30         | 1250       | 950        |
| 30         | 1500       | 950        |
| 30         | 1600       | 950        |
| 30         | 2850       | 950        |
+------------+------------+------------+

NTILE

Sözdizimi

bigint ntile(bigint <N>) over ([partition_clause] [orderby_clause])

Açıklama

Bir bölümdeki sıralı satırları mümkün olduğunca eşit büyüklükte N gruba böler ve her satır için grup numarasını döndürür. Satır sayısı N'ye tam bölünmüyorsa ilk gruplar (daha küçük grup numaralarına sahip olanlar) bir ekstra satır içerir.
Parametreler
- N: Zorunludur. Grup sayısı. Bir BIGINT değeridir.
- partition_clause ve orderby_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

BIGINT değeri döndürür.

Örnek

Her departmandaki tüm çalışanları maaşa (sal) göre azalan sırada 3 gruba bölün ve her çalışan için grup numarasını döndürün. Komut aşağıdaki gibidir:

select deptno, ename, sal, ntile(3) over (partition by deptno order by sal desc) as nt3 from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nt3        |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 2          |
| 10         | WELAN      | 2450       | 2          |
| 10         | TEBAGE     | 1300       | 3          |
| 10         | MILLER     | 1300       | 3          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 2          |
| 20         | ADAMS      | 1100       | 2          |
| 20         | SMITH      | 800        | 3          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 1          |
| 30         | TURNER     | 1500       | 2          |
| 30         | MARTIN     | 1250       | 2          |
| 30         | WARD       | 1250       | 3          |
| 30         | JAMES      | 950        | 3          |
+------------+------------+------------+------------+

NTH_VALUE

Sözdizimi

nth_value(<expr>, <number> [, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

Açıklama

Pencere çerçevesinin N inci satırındaki expr ifadesinin değerini döndürür.
Parametreler
- expr: Zorunludur. Hesaplanacak ifade.
- number: Zorunludur. Bir BIGINT değeridir. 1'e eşit veya 1'den büyük bir tamsayıdır. Değer 1 ise bu işlev FIRST_VALUE işlevine eşdeğerdir.
- ignore_nulls: İsteğe bağlıdır. NULL değerlerin yoksayılıp yoksayılmayacağını belirten bir BOOLEAN değeridir. Varsayılan değer False'tur. Bu parametre True olarak ayarlandığında, işlev pencere çerçevesindeki expr ifadesinin N'inci NULL olmayan değerini döndürür.
- partition_clause, orderby_clause ve frame_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

Dönüş değeri, expr ile aynı veri türüne sahiptir.

Örnek

Aşağıdaki komut, tüm çalışanları departmana göre gruplar ve her gruptan 6. veri satırını döndürür:

order by yan tümcesi olmadan pencere çerçevesi, bölümdeki tüm satırları içerir. İşlev, bölümdeki 6. satırın değerini döndürür.

select deptno, ename, sal, nth_value(sal,6) over (partition by deptno) as nth_value from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nth_value  |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | 2450       |
| 10         | CLARK      | 2450       | 2450       |
| 10         | KING       | 5000       | 2450       |
| 10         | MILLER     | 1300       | 2450       |
| 10         | JACCKA     | 5000       | 2450       |
| 10         | WELAN      | 2450       | 2450       |   -- 6th row of the current window.
| 20         | FORD       | 3000       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |   -- The current window does not have a 6th row, so NULL is returned.
| 30         | TURNER     | 1500       | 2850       |
| 30         | JAMES      | 950        | 2850       |
| 30         | ALLEN      | 1600       | 2850       |
| 30         | WARD       | 1250       | 2850       |
| 30         | MARTIN     | 1250       | 2850       |
| 30         | BLAKE      | 2850       | 2850       |   -- 6th row of the current window.
+------------+------------+------------+------------+

order by yan tümcesi ile birlikte kullanıldığında varsayılan pencere çerçevesi RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW olur. İşlev, pencere çerçevesindeki 6. satırın değerini döndürür.

select deptno, ename, sal, nth_value(sal,6) over (partition by deptno order by sal) as nth_value from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nth_value  |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | NULL       |   
| 10         | MILLER     | 1300       | NULL       |   -- The current window has only 2 rows, so the 6th row exceeds the window length.
| 10         | CLARK      | 2450       | NULL       |
| 10         | WELAN      | 2450       | NULL       |
| 10         | KING       | 5000       | 5000       |  
| 10         | JACCKA     | 5000       | 5000       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | FORD       | 3000       | NULL       |
| 30         | JAMES      | 950        | NULL       |
| 30         | MARTIN     | 1250       | NULL       |
| 30         | WARD       | 1250       | NULL       |
| 30         | TURNER     | 1500       | NULL       |
| 30         | ALLEN      | 1600       | NULL       |
| 30         | BLAKE      | 2850       | 2850       |
+------------+------------+------------+------------+

PERCENT_RANK

Sözdizimi

double percent_rank() over([partition_clause] [orderby_clause])

Açıklama

orderby_clause tarafından belirtilen sıralama düzenine göre mevcut satırın kendi bölümü içindeki yüzde sırasını hesaplar.
Parametreler

partition_clause ve orderby_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

[0,0, 1,0] aralığında bir DOUBLE değeri döndürür. Belirli dönüş değeri “(rank - 1) / (partition_row_count - 1)” formülüne eşittir; burada rank ilgili satır için RANK pencere işlevinin sonucunu, partition_row_count ise satırın ait olduğu bölümdeki satır sayısını temsil eder. Bölüm yalnızca bir satır içeriyorsa çıktı 0,0 olur.

Örnek

Her çalışanın maaşının kendi departmanı içindeki yüzde sırasını hesaplayın. Komut aşağıdaki gibidir:

select deptno, ename, sal, percent_rank() over (partition by deptno order by sal desc) as sal_new from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 0.0        |
| 10         | KING       | 5000       | 0.0        |
| 10         | CLARK      | 2450       | 0.4        |
| 10         | WELAN      | 2450       | 0.4        |
| 10         | TEBAGE     | 1300       | 0.8        |
| 10         | MILLER     | 1300       | 0.8        |
| 20         | SCOTT      | 3000       | 0.0        |
| 20         | FORD       | 3000       | 0.0        |
| 20         | JONES      | 2975       | 0.5        |
| 20         | ADAMS      | 1100       | 0.75       |
| 20         | SMITH      | 800        | 1.0        |
| 30         | BLAKE      | 2850       | 0.0        |
| 30         | ALLEN      | 1600       | 0.2        |
| 30         | TURNER     | 1500       | 0.4        |
| 30         | MARTIN     | 1250       | 0.6        |
| 30         | WARD       | 1250       | 0.6        |
| 30         | JAMES      | 950        | 1.0        |
+------------+------------+------------+------------+

PERCENTILE_CONT

Sözdizimi

-- Calculate the exact percentile
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- Calculate the exact percentile in a window
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

Açıklama

Kesin yüzdelik dilim değerini hesaplar. İşlev, doğrusal enterpolasyon algoritması kullanır; belirtilen sütunu artan düzende sıralar ve belirtilen percentile konumundaki kesin değeri döndürür.
Parametreler
- col_name: Gereklidir. DOUBLE veya DECIMAL türünde bir sütun olmalıdır.
- percentile: Gereklidir. Hesaplanacak yüzdelik dilim değeridir. [0, 1] aralığında bir DOUBLE sabit olmalıdır.
- isIgnoreNull: İsteğe bağlıdır. NULL değerlerin yoksayılıp yoksayılmayacağını belirtir. BOOLEAN türünde bir sabittir. Varsayılan değer TRUE'dur. FALSE olarak ayarlandığında, NULL değerler sıralama sırasında minimum değer olarak işlenir.
- partition_clause ve orderby_clause: Daha fazla bilgi için bkz. windowing_definition..
Dönüş değeri

Hesaplanan yüzdelik dilim değerini DOUBLE türünde döndürür.

Örnekler

Örnek 1: NULL değerleri yoksayarak pencere içindeki kesin yüzdelik dilim değerini hesaplayın.

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- Return result
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| 0.0        | 0.03        | 1.5        | 2.7          | 3.0        | 
+------------+-------------+------------+--------------+------------+

Örnek 2: NULL değerleri yoksaymayın. NULL değerler sıralama sırasında minimum değer olarak işlenir. Pencere içindeki kesin yüzdelik dilim değerini hesaplayın.

SELECT
  PERCENTILE_CONT(x, 0, false) OVER() AS min,
  PERCENTILE_CONT(x, 0.01, false) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5, false) OVER() AS median,
  PERCENTILE_CONT(x, 0.9, false) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1, false) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- Return result
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| NULL       | 0.0         | 1.0        | 2.6          | 3.0        | 
+------------+-------------+------------+--------------+------------+

PERCENTILE_DISC

Sözdizimi

-- Calculate a given percentile value
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- Calculate the percentile value in a window
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

Açıklama

Belirtilen yüzdelik dilim değerini hesaplar. Önce belirtilen sütunu artan düzende sıralar, ardından kümülatif dağılımı belirtilen yüzdelik dilime eşit veya ondan büyük olan ilk değeri döndürür.
Parametreler
- col_name: Gereklidir. Sıralanabilir herhangi bir veri türüne sahip bir sütun olmalıdır.
- percentile: Gereklidir. Hesaplanacak yüzdelik dilim değeridir. [0, 1] aralığında bir DOUBLE sabit olmalıdır.
- isIgnoreNull: İsteğe bağlıdır. NULL değerlerin yoksayılıp yoksayılmayacağını belirtir. BOOLEAN türünde bir sabittir. Varsayılan değer TRUE'dur. FALSE olarak ayarlandığında, NULL değerler sıralama sırasında minimum değer olarak işlenir.
- partition_clause ve orderby_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

Hesaplanan yüzdelik dilim değerini döndürür. Veri türü, giriş col_name sütunuyla aynıdır.

Örnekler

Örnek 1: NULL değerleri yoksayarak pencere içindeki yüzdelik dilim değerini hesaplayın.

SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);

-- Return result
+------------+------------+------------+------------+
| x          | min        | median     | max        | 
+------------+------------+------------+------------+
| c          | a          | b          | c          | 
| NULL       | a          | b          | c          | 
| b          | a          | b          | c          | 
| a          | a          | b          | c          | 
+------------+------------+------------+------------+

Örnek 2: NULL değerleri yoksaymayın. NULL değerler sıralama sırasında minimum değer olarak işlenir. Pencere içindeki yüzdelik dilim değerini hesaplayın.

SELECT
  x,
  PERCENTILE_DISC(x, 0, false) OVER() AS min,
  PERCENTILE_DISC(x, 0.5, false) OVER() AS median,
  PERCENTILE_DISC(x, 1, false) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);

-- Return result
+------------+------------+------------+------------+
| x          | min        | median     | max        | 
+------------+------------+------------+------------+
| c          | NULL       | a          | c          | 
| NULL       | NULL       | a          | c          | 
| b          | NULL       | a          | c          | 
| a          | NULL       | a          | c          | 
+------------+------------+------------+------------+

RANK

Sözdizimi

bigint rank() over ([partition_clause] [orderby_clause])

Açıklama

orderby_clause tarafından belirtilen sıralama düzenine göre, mevcut satırın kendi bölümü içindeki sırasını hesaplar. Sayma 1'den başlar.
Parametreler

partition_clause ve orderby_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

BIGINT türünde bir değer döndürür. Dönüş değerleri tekrar edebilir ve ardışık olmayabilir. Belirli dönüş değeri, veri satırının ait olduğu GRUBUN ilk satırındaki ROW_NUMBER() değeridir. Bir orderby_clause belirtilmezse, tüm satırlara 1 sırası atanır.

Örnek

Departmana (deptno) göre bölümleyin ve her departman içindeki çalışanları maaşlarına (sal) göre azalan sırada sıralayın. Komut aşağıdaki gibidir:

select deptno, ename, sal, rank() over (partition by deptno order by sal desc) as nums from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 3          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 5          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 4          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

ROW_NUMBER

Sözdizimi

row_number() over([partition_clause] [orderby_clause])

Açıklama

Mevcut satırın kendi bölümü içindeki satır numarasını hesaplar. Sayma 1'den başlar.
Parametreler

Daha fazla bilgi için bkz. windowing_definition. frame_clause kullanımına izin verilmez.
Dönüş değeri

BIGINT türünde bir değer döndürür.

Örnek

Departmana (deptno) göre bölümleyin ve her departman içindeki çalışanlara maaşlarına (sal) göre azalan sırada benzersiz, ardışık bir numara atayın. Komut aşağıdaki gibidir:

select deptno, ename, sal, row_number() over (partition by deptno order by sal desc) as nums from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 2          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 4          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 6          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 2          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 5          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

STDDEV

Sözdizimi

double stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Açıklama

Popülasyon standart sapmasını hesaplar. Bu, STDDEV_POP işlevinin takma adıdır.
Parametreler
- expr: Gereklidir. Popülasyon standart sapmasının hesaplanacağı ifadedir. DOUBLE veya DECIMAL türünde olmalıdır.
  - Giriş değeri STRING veya BIGINT türündeyse, hesaplama için örtük olarak DOUBLE türüne dönüştürülür. Diğer veri türleri için hata döndürülür.
  - Giriş değeri NULL ise, satır hesaplamaya dahil edilmez.
  - distinct anahtar sözcüğünü belirtirseniz, işlev benzersiz değerlerin popülasyon standart sapmasını hesaplar.
- partition_clause, orderby_clause ve frame_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

Dönüş değeri, expr ile aynı veri türüne sahiptir. expr içindeki tüm değerler NULL ise NULL döndürülür.

Örnekler

Örnek 1: Departmana (deptno) göre bölümleyin, maaşın (sal) popülasyon standart sapmasını hesaplayın ve sıralama yapmayın. İşlev, mevcut bölümün (aynı deptno değerine sahip satırlar) kümülatif popülasyon standart sapmasını döndürür. Komut aşağıdaki gibidir:

select deptno, sal, stddev(sal) over (partition by deptno) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1546.1421524412158 |   -- First row of the window. The value is the cumulative population standard deviation from the first to the sixth row.
| 10         | 2450       | 1546.1421524412158 |   -- The value is the cumulative population standard deviation from the first to the sixth row.
| 10         | 5000       | 1546.1421524412158 |
| 10         | 1300       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 10         | 2450       | 1546.1421524412158 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 800        | 1004.7387720198718 |
| 20         | 1100       | 1004.7387720198718 |
| 20         | 2975       | 1004.7387720198718 |
| 30         | 1500       | 610.1001739241042 |
| 30         | 950        | 610.1001739241042 |
| 30         | 1600       | 610.1001739241042 |
| 30         | 1250       | 610.1001739241042 |
| 30         | 1250       | 610.1001739241042 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

Örnek 2: Hive uyumlu olmayan modda, departmana (deptno) göre bölümleyin, maaşın (sal) popülasyon standart sapmasını hesaplayın ve sonuçları sıralayın. İşlev, mevcut bölümün (aynı deptno değerine sahip satırlar) ilk satırından mevcut satıra kadar olan kümülatif popülasyon standart sapmasını döndürür. Komutlar aşağıdaki gibidir:

-- Disable Hive compatible mode.
set odps.sql.hive.compatible=false;
-- Execute the following SQL command.
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |           -- First row of the window.
| 10         | 1300       | 0.0        |           -- Cumulative population standard deviation from the first to the second row.
| 10         | 2450       | 542.1151989096865 |    -- Cumulative population standard deviation from the first to the third row.
| 10         | 2450       | 575.0      |           -- Cumulative population standard deviation from the first to the fourth row.
| 10         | 5000       | 1351.6656391282572 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1024.2947268730811 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 150.0      |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

Örnek 3: Hive uyumlu modda, departmana (deptno) göre bölümleyin, maaşın (sal) popülasyon standart sapmasını hesaplayın ve sonuçları sıralayın. İşlev, mevcut satırla aynı değere sahip satıra kadar olan kümülatif popülasyon standart sapmasını döndürür (aynı sal değerine sahip satırların popülasyon standart sapması aynıdır). Komutlar aşağıdaki gibidir:

-- Enable Hive compatible mode.
set odps.sql.hive.compatible=true;
-- Execute the following SQL command.
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |           -- First row of the window. Since the sal of the first and second rows are the same, the population standard deviation for the first row is the cumulative population standard deviation of the first two rows.
| 10         | 1300       | 0.0        |           -- Cumulative population standard deviation from the first to the second row.
| 10         | 2450       | 575.0      |           -- Since the sal of the third and fourth rows are the same, the population standard deviation for the third row is the cumulative population standard deviation of the first four rows.
| 10         | 2450       | 575.0      |           -- Cumulative population standard deviation from the first to the fourth row.
| 10         | 5000       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

STDDEV_SAMP

Sözdizimi

double stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])
decimal stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])

Açıklama

Örnek standart sapmayı hesaplar.
Parametreler
- expr: Gereklidir. Örnek standart sapmasının hesaplanacağı ifadedir. DOUBLE veya DECIMAL türünde olmalıdır.
  - Giriş değeri STRING veya BIGINT türündeyse, hesaplama için örtük olarak DOUBLE türüne dönüştürülür. Diğer veri türleri için hata döndürülür.
  - Giriş değeri NULL ise, satır hesaplamaya dahil edilmez.
  - distinct anahtar sözcüğünü belirtirseniz, işlev benzersiz değerlerin örnek standart sapmasını hesaplar.
- partition_clause, orderby_clause ve frame_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri

Dönüş değeri, expr ile aynı veri türüne sahiptir. expr içindeki tüm değerler NULL ise NULL döndürülür. Pencerede expr için yalnızca bir tane NULL olmayan değer varsa sonuç 0 olur.

Örnekler

Örnek 1: Departmana (deptno) göre bölümleyin, maaşın (sal) örnek standart sapmasını hesaplayın ve sıralama yapmayın. İşlev, mevcut bölümün (aynı deptno değerine sahip satırlar) kümülatif örnek standart sapmasını döndürür. Komut aşağıdaki gibidir:

select deptno, sal, stddev_samp(sal) over (partition by deptno) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1693.7138680032904 |   -- First row of the window. The value is the cumulative sample standard deviation from the first to the sixth row.
| 10         | 2450       | 1693.7138680032904 |   -- The value is the cumulative sample standard deviation from the first to the sixth row.
| 10         | 5000       | 1693.7138680032904 |   -- The value is the cumulative sample standard deviation from the first to the sixth row.
| 10         | 1300       | 1693.7138680032904 |     
| 10         | 5000       | 1693.7138680032904 |
| 10         | 2450       | 1693.7138680032904 |
| 20         | 3000       | 1123.3320969330487 |
| 20         | 3000       | 1123.3320969330487 |
| 20         | 800        | 1123.3320969330487 |
| 20         | 1100       | 1123.3320969330487 |
| 20         | 2975       | 1123.3320969330487 |
| 30         | 1500       | 668.331255192114 |
| 30         | 950        | 668.331255192114 |
| 30         | 1600       | 668.331255192114 |
| 30         | 1250       | 668.331255192114 |
| 30         | 1250       | 668.331255192114 |
| 30         | 2850       | 668.331255192114 |
+------------+------------+------------+

Örnek 2: Departmana (deptno) göre bölümleyin, maaşın (sal) örnek standart sapmasını hesaplayın ve sonuçları sıralayın. İşlev, mevcut bölümün (aynı deptno değerine sahip satırlar) ilk satırından mevcut satıra kadar olan kümülatif örnek standart sapmasını döndürür. Komut aşağıdaki gibidir:

select deptno, sal, stddev_samp(sal) over (partition by deptno order by sal) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |          -- First row of the window.
| 10         | 1300       | 0.0        |          -- Cumulative sample standard deviation from the first to the second row.
| 10         | 2450       | 663.9528095680697 |   -- Cumulative sample standard deviation from the first to the third row.
| 10         | 2450       | 663.9528095680696 |
| 10         | 5000       | 1511.2081259707413 |
| 10         | 5000       | 1693.7138680032904 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 212.13203435596427 |
| 20         | 2975       | 1178.7175234126282 |
| 20         | 3000       | 1182.7536725793752 |
| 20         | 3000       | 1123.3320969330487 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 212.13203435596427 |
| 30         | 1250       | 173.20508075688772 |
| 30         | 1500       | 225.0      |
| 30         | 1600       | 253.4758371127315 |
| 30         | 2850       | 668.331255192114 |
+------------+------------+------------+

SUM

Sözdizimi

sum([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Açıklama

Bir pencere içindeki expr toplamını döndürür.
Parametreler
- expr: Gereklidir. Toplamın hesaplanacağı sütundur. DOUBLE, DECIMAL veya BIGINT türünde olmalıdır.
  - Giriş değeri STRING türündeyse, hesaplama için örtük olarak DOUBLE türüne dönüştürülür. Diğer veri türleri için hata döndürülür.
  - Giriş değeri NULL ise, satır hesaplamaya dahil edilmez.
  - distinct anahtar sözcüğünü belirtirseniz, işlev benzersiz değerlerin toplamını hesaplar.
- partition_clause, orderby_clause ve frame_clause: Daha fazla bilgi için bkz. windowing_definition.
Dönüş değeri
- Giriş değeri BIGINT türündeyse BIGINT değeri döndürülür.
- Giriş değeri DECIMAL türündeyse DECIMAL değeri döndürülür.
- Giriş değeri DOUBLE veya STRING türündeyse DOUBLE değeri döndürülür.
- Tüm giriş değerleri NULL ise NULL döndürülür.

Örnekler

Örnek 1: Departmana (deptno) göre bölümleyin, maaşın (sal) toplamını hesaplayın ve sıralama yapmayın. İşlev, mevcut bölümün (aynı deptno değerine sahip satırlar) kümülatif toplamını döndürür. Komut aşağıdaki gibidir:

select deptno, sal, sum(sal) over (partition by deptno) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 17500      |   -- First row of the window. The value is the cumulative sum from the first to the sixth row.
| 10         | 2450       | 17500      |   -- The value is the cumulative sum from the first to the sixth row.
| 10         | 5000       | 17500      |   -- The value is the cumulative sum from the first to the sixth row.
| 10         | 1300       | 17500      |
| 10         | 5000       | 17500      |
| 10         | 2450       | 17500      |
| 20         | 3000       | 10875      |
| 20         | 3000       | 10875      |
| 20         | 800        | 10875      |
| 20         | 1100       | 10875      |
| 20         | 2975       | 10875      |
| 30         | 1500       | 9400       |
| 30         | 950        | 9400       |
| 30         | 1600       | 9400       |
| 30         | 1250       | 9400       |
| 30         | 1250       | 9400       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

Örnek 2: Hive uyumlu olmayan modda, departmana (deptno) göre bölümleyin, maaşın (sal) toplamını hesaplayın ve sonuçları sıralayın. İşlev, mevcut bölümün (aynı deptno değerine sahip satırlar) ilk satırından mevcut satıra kadar olan kümülatif toplamını döndürür. Komutlar aşağıdaki gibidir:

-- Disable Hive compatible mode.
set odps.sql.hive.compatible=false;
-- Execute the following SQL command.
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- First row of the window.
| 10         | 1300       | 2600       |   -- Cumulative sum from the first to the second row.
| 10         | 2450       | 5050       |   -- Cumulative sum from the first to the third row.
| 10         | 2450       | 7500       |
| 10         | 5000       | 12500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 7875       |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 2200       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

Örnek 3: Hive uyumlu modda, departmana (deptno) göre bölümleyin, maaşın (sal) toplamını hesaplayın ve sonuçları sıralayın. İşlev, mevcut satırla aynı değere sahip satıra kadar olan kümülatif toplamını döndürür (aynı sal değerine sahip satırların toplamı aynıdır). Komutlar aşağıdaki gibidir:

-- Enable Hive compatible mode.
set odps.sql.hive.compatible=true;
-- Execute the following SQL command.
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

Aşağıdaki sonuç döndürülür:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2600       |   -- First row of the window. Since the sal of the first and second rows are the same, the sum for the first row is the cumulative sum of the first two rows.
| 10         | 1300       | 2600       |   -- Cumulative sum from the first to the second row.
| 10         | 2450       | 7500       |   -- Since the sal of the third and fourth rows are the same, the sum for the third row is the cumulative sum of the firstfour rows.
| 10         | 5000       | 17500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 10875      |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 3450       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

Başvurular

Yerleşik işlevler ihtiyaçlarınızı karşılamazsa kullanıcı tanımlı işlevler oluşturabilirsiniz. MaxCompute UDF'lerine genel bakış.
Yaygın MaxCompute SQL sorunları:
Yerleşik işlevler için yaygın hata kodları:
- Yerleşik işlevler için yaygın hata kodları
- Yerleşik işlevlerle ilgili SSS