Fahami fungsi windowing dalam SQL dalam satu artikel

Artikel ini membawa anda pengetahuan yang berkaitan tentang SQL server Terdapat dua jenis fungsi tetingkap, juga dipanggil fungsi analitik, satu ialah fungsi tetingkap pengagregatan, dan satu lagi adalah fungsi pengisihan tetingkap , Artikel berikut terutamanya memperkenalkan anda kepada maklumat yang berkaitan tentang fungsi tetingkap dalam SQL Artikel memperkenalkannya secara terperinci melalui kod contoh Rakan yang memerlukan boleh merujuknya.

Kajian yang disyorkan: "Tutorial SQL"

Definisi OVER

OVER digunakan untuk menentukan baris Tetingkap, yang beroperasi pada set nilai, tidak memerlukan penggunaan klausa GROUP BY untuk mengumpulkan data dan dapat mengembalikan kedua-dua lajur baris asas dan lajur agregat dalam baris yang sama.

SINTAKSIS LEBIH

LEBIH ( [ PARTITION BY column ] [ ORDER BY culumn ] )

PARTITION BY clause for grouping; >URUSAN MENGIKUT klausa untuk mengisih.

Fungsi tetingkap OVER() menentukan set baris dan fungsi tetingkap mengira nilai setiap baris dalam output set hasil daripada fungsi tetingkap.

Fungsi tetingkap boleh mengumpulkan data tanpa menggunakan GROUP BY, dan juga boleh mengembalikan lajur baris asas dan lajur agregat pada masa yang sama.

Penggunaan OVER

Fungsi tetingkap OVER mesti digunakan bersama-sama dengan fungsi agregat atau fungsi pengisihan secara amnya merujuk kepada SUM(), MAX(), MIN, COUNT(), AVG(. ) dan fungsi biasa yang lain. Fungsi pengisihan biasanya merujuk kepada RANK(), ROW_NUMBER(), DENSE_RANK(), NTILE(), dsb.

Contoh penggunaan OVER dalam fungsi agregat

Kami menggunakan fungsi SUM dan COUNT sebagai contoh untuk menunjukkan kepada anda.

Fungsi tetingkap selepas SUM

--建立测试表和测试数据
CREATE TABLE Employee
(
ID INT  PRIMARY KEY,
Name VARCHAR(20),
GroupName VARCHAR(20),
Salary INT
)
INSERT INTO  Employee
VALUES(1,'小明','开发部',8000),
      (4,'小张','开发部',7600),
      (5,'小白','开发部',7000),
      (8,'小王','财务部',5000),
      (9, null,'财务部',NULL),
      (15,'小刘','财务部',6000),
      (16,'小高','行政部',4500),
      (18,'小王','行政部',4000),
      (23,'小李','行政部',4500),
      (29,'小吴','行政部',4700);

(Petua: Anda boleh slaid kod ke kiri atau kanan)

SELECT *,
     SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资,
     SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资,
     SUM(Salary) OVER(ORDER BY ID) 累计工资,
     SUM(Salary) OVER() 总工资
from Employee

Hasilnya adalah seperti berikut:

< . >

Hanya lajur Nama Kumpulan selepas PARTITION BY dikumpulkan, dan jumlah Gaji dikira selepas pengumpulan.

JUMLAH(Gaji)

LEBIH (PARTITION OLEH

Nama Kumpulan

ORDER BY ID)

Untuk lajur Nama Kumpulan selepas PARTITION BY Kumpulan, kemudian susun mengikut ID selepas ORDER BY, dan kemudian kumpulkan Gaji dalam kumpulan.

JUMLAH(Gaji) LEBIH (PESAN OLEH ID)

Hanya untuk ORDER OLEH Isih kandungan ID selepas mengisih, dan kumpulkan Gaji yang diisih.

JUMLAH(Gaji) LEBIH ()

Selepas meringkaskan Gaji

KIRA Keputusan dikembalikan oleh fungsi tetingkap

adalah seperti berikut:

Setiap fungsi tetingkap berikutnya tidak lagi akan ditafsirkan satu per satu dengan fungsi windowing selepas SUM di atas.

Contoh penggunaan OVER dalam fungsi pengisihan

Kami menunjukkan 4 fungsi pengisihan satu demi satu

SELECT *,
       COUNT(*) OVER(PARTITION BY Groupname ) 每个组的个数,
       COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) 每个组的累积个数,
       COUNT(*) OVER(ORDER BY ID) 累积个数 ,
       COUNT(*) OVER() 总个数
from Employee

ROW_NUMBER()

Definisi

: Fungsi ROW_NUMBER() adalah untuk mengisih data yang ditanya oleh SELECT Setiap bahagian data ditambah dengan nombor siri Ia biasanya digunakan untuk pertanyaan halaman sebagai menyoal 10-100 pelajar teratas. ROW_NUMBER() mesti digunakan bersama-sama dengan ORDER BY, jika tidak, ralat akan dilaporkan.

Isih markah pelajar

Keputusan adalah seperti berikut:

--先建立测试表和测试数据
WITH t AS
(SELECT 1 StuID,'一班' ClassName,70 Score
UNION ALL
SELECT 2,'一班',85
UNION ALL
SELECT 3,'一班',85
UNION ALL
SELECT 4,'二班',80
UNION ALL
SELECT 5,'二班',74
UNION ALL
SELECT 6,'二班',80
)
SELECT * INTO Scores FROM t;
SELECT * FROM Scores

Fungsi PARTITION BY dan ORDER BY di sini ialah sama seperti yang kami lakukan di atas Fungsi agregat yang anda lihat mempunyai fungsi yang sama, semuanya digunakan untuk mengumpulkan dan menyusun.

Selain itu, fungsi ROW_NUMBER() juga boleh mengambil data dalam susunan yang ditentukan.

SELECT *,
ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班内排序,
ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序
FROM Scores;

Hasilnya adalah seperti berikut:

RANK()

Definisi: RANK() fungsi, seperti namanya , bolehkah kedudukan medan A tertentu digunakan untuk kedudukan Apakah perbezaan antara ini dan ROW_NUMBER()? ROW_NUMBER() sedang mengisih apabila terdapat pelajar dengan gred yang sama, ROW_NUMBER() akan mengisih mereka mengikut urutan, tetapi Rank() adalah berbeza. Jika mereka kelihatan sama, kedudukan mereka adalah sama. Mari lihat contoh di bawah:

Contoh

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序
FROM Scores
) t WHERE t.总排序=2;

Hasil:

The gambar di atas ialah Hasil ROW_NUMBER(), angka berikut ialah hasil RANK(). Apabila dua pelajar mempunyai gred yang sama, terdapat perubahan. RANK() ialah 1-1-3-3-5-6, manakala ROW_NUMBER() masih 1-2-3-4-5-6 Ini ialah perbezaan antara RANK() dan ROW_NUMBER().

DENSE_RANK()

定义：DENSE_RANK()函数也是排名函数，和RANK()功能相似，也是对字段进行排名，那它和RANK()到底有什么不同那？特别是对于有成绩相同的情况，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，一般情况下用的排名函数就是RANK() 我们看例子：

示例

SELECT 
RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;
 
SELECT 
DENSE_RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*
FROM Scores;

结果如下：

上面是RANK()的结果，下面是DENSE_RANK()的结果

NTILE()

定义：NTILE()函数是将有序分区中的行分发到指定数目的组中，各个组有编号，编号从1开始，就像我们说的'分区'一样 ，分为几个区，一个区会有多少个。

SELECT *,NTILE(1) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;
SELECT *,NTILE(2) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;
SELECT *,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分区后排序 FROM Scores;

结果如下：

就是将查询出来的记录根据NTILE函数里的参数进行平分分区。

总结

OVER开窗函数是我们工作中经常要使用到的，特别是在做数据分析计算的时候，经常要对数据进行分组排序。上面我们额外介绍了聚合函数和排序函数的与OVER结合的使用方法，此外还有很多与OVER一起使用的函数，比如LEAD函数，LAG函数，STRING_AGG函数等等都会使用到开窗函数OVER，其使用方法也要务必掌握。

推荐学习：《SQL教程》

Atas ialah kandungan terperinci Fahami fungsi windowing dalam SQL dalam satu artikel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!