Penjelasan terperinci tentang fungsi tetingkap SQL dalam satu artikel

Artikel ini membawakan anda pengetahuan yang berkaitan tentang SQL, yang terutamanya mengatur isu yang berkaitan dengan fungsi tetingkap SQL disediakan untuk pemprosesan analisis dalam talian (OLAP) dan risikan perniagaan (BI). dan fungsi statistik laporan, seperti statistik jualan terkumpul produk, kedudukan kategori, analisis tahun ke tahun/bulan ke bulan, dsb. Mari kita lihat bersama-sama. Saya harap ia akan membantu semua orang.

Kajian yang disyorkan: "Tutorial SQL"

Apakah fungsi tetingkap

Fungsi tetingkap SQL adalah untuk analisis dalam talian Pemprosesan (OLAP) dan risikan perniagaan (BI) menyediakan analisis kompleks dan fungsi statistik laporan, seperti statistik jualan terkumpul produk, kedudukan kategori, analisis tahun ke tahun/bulan ke bulan, dsb. Ciri ini selalunya sukar untuk dilaksanakan dengan fungsi agregat dan operasi kumpulan.

Fungsi Tetingkap boleh menganalisis set data dan mengembalikan hasil seperti fungsi agregat Perbezaan antara keduanya ialah fungsi tetingkap tidak mengagregatkan satu set data ke dalam satu hasil, sebaliknya Setiap baris data. mengembalikan hasil. Perbezaan antara fungsi agregat dan fungsi tetingkap ditunjukkan dalam rajah di bawah.

Ambil fungsi SUM sebagai contoh untuk menunjukkan perbezaan antara dua fungsi SUM() dalam pernyataan berikut ialah fungsi agregat:

SELECT SUM(salary) AS "所有员工月薪总和"
FROM employee

Di atas Fungsi SUM boleh digunakan sebagai fungsi agregat untuk meringkaskan semua data pekerja menjadi satu hasil. Oleh itu, pertanyaan mengembalikan jumlah gaji bulanan semua pekerja:

SUM() dalam penyata berikut ialah fungsi tetingkap:

SELECT emp_name AS "员工姓名",
       SUM(salary) OVER () AS "所有员工月薪总和"
FROM employee;

Di mana , kekunci Perkataan OVER menunjukkan bahawa SUM() ialah fungsi tetingkap. Tanda kurungan kosong menunjukkan bahawa semua data diringkaskan sebagai satu kumpulan. Keputusan yang dikembalikan oleh pertanyaan ini adalah seperti berikut:

Hasil pertanyaan di atas mengembalikan semua nama pekerja, dan hasil ringkasan yang sama dikembalikan untuk setiap pekerja melalui fungsi agregat SUM( ).

Seperti yang anda lihat daripada contoh di atas, sintaks fungsi tetingkap adalah berbeza daripada fungsi agregat kerana ia mengandungi klausa OVER. Klausa OVER digunakan untuk menentukan tetingkap untuk analisis data Fungsi tetingkap lengkap ditakrifkan seperti berikut:

di mana window_function ialah nama fungsi tetingkap dan ungkapan ialah objek analisis pilihan (nama medan atau ungkapan), klausa OVER termasuk tiga pilihan: partition (PARTITION BY), sorting (ORDER BY), dan saiz tetingkap (frame_clause).

Petua: Fungsi pengagregatan meringkaskan berbilang baris data dalam kumpulan yang sama menjadi satu hasil, manakala fungsi tetingkap mengekalkan semua data asal. Dalam sesetengah pangkalan data, fungsi tetingkap juga dipanggil fungsi pemprosesan analitikal dalam talian (OLAP), atau fungsi analitik.

Komponen fungsi tetingkap

1. Cipta partition data

Pilihan PARTITION BY dalam klausa OVER bagi fungsi tetingkap digunakan untuk mentakrifkan partition, dan peranannya adalah serupa dengan bahawa dalam pernyataan pertanyaan GROUP BY klausa. Jika kami menentukan pilihan partition, fungsi tetingkap akan menganalisis setiap partition secara berasingan.

Sebagai contoh, penyata berikut mengira jumlah gaji bulanan pekerja mengikut jabatan yang berbeza:

SELECT emp_name AS "员工姓名", salary "月薪", dept_id AS "部门编号",
       SUM(salary) OVER (
         PARTITION BY dept_id
       ) AS "部门合计"
FROM employee;

Antaranya, pilihan PARTITION BY menunjukkan pembahagian mengikut jabatan. Keputusan yang dikembalikan oleh pertanyaan adalah seperti berikut:

Tiga baris pertama data dalam hasil pertanyaan adalah milik jabatan yang sama, jadi jumlah medan jabatan yang sepadan adalah sama dengan 80000 (30000 26000 24000). Pekerja di jabatan lain dikira dengan cara yang sama.

Petua: Selepas menentukan pilihan PARTITION BY dalam fasal fungsi tetingkap OVER, kita boleh mendapatkan keputusan statistik kumpulan tanpa menggunakan klausa GROUP BY.

Jika pilihan PARTITION BY tidak dinyatakan, ini bermakna semua data akan dianalisis secara keseluruhan.

2. Isih dalam partition

Pilihan ORDER BY dalam klausa OVER bagi fungsi tetingkap digunakan untuk menentukan kaedah pengisihan data dalam partition, dan fungsinya adalah serupa dengan klausa ORDER BY dalam pernyataan pertanyaan.

Pilihan isihan biasanya digunakan untuk pemeringkatan kategori data. Sebagai contoh, penyata berikut digunakan untuk menganalisis kedudukan gaji bulanan pekerja dalam jabatan:

SELECT emp_name AS "员工姓名", salary "月薪", dept_id AS "部门编号",
       RANK() OVER (
         PARTITION BY dept_id
         ORDER BY salary DESC
       ) AS "部门内排名"
FROM employee;

Antaranya, fungsi RANK digunakan untuk mengira kedudukan data, pilihan PARTITION BY menunjukkan pembahagian mengikut jabatan, dan pilihan ORDER BY menunjukkan bahawa data dibahagikan mengikut jabatan Isih mengikut gaji bulanan dari tinggi ke rendah. Keputusan yang dikembalikan oleh pertanyaan adalah seperti berikut:

查询结果中的前3行数据属于同一个部门：“刘备”的月薪最高，在部门内排名第1；“关羽”排名第2；“张飞”排名第3。其他部门的员工采用同样的方式进行排名。

提示：窗口函数OVER子句中的ORDER BY选项和查询语句中的ORDER BY子句的使用方法相同。因此，也可以使用NULLS FIRST或者NULLS LAST选项指定空值的排序位置。

3.指定窗口大小

窗口函数OVER子句中的frame_clause选项用于指定一个移动的分析窗口，窗口总是位于分区的范围之内，是分区的一个子集。在指定了分析窗口之后，窗口函数不再基于分区进行分析，而是基于窗口内的数据进行分析。

窗口选项可以用于实现各种复杂的分析功能，例如计算累计到当前日期为止的销售额总和，每个月及其前后各N个月的平均销售额等。

指定窗口大小的具体选项如下：

其中，ROWS表示以数据行为单位计算窗口的偏移量，RANGE表示以数值（例如10天、5km等）为单位计算窗口的偏移量。

frame_start选项用于定义窗口的起始位置，可以指定以下内容之一：
●UNBOUNDED PRECEDING——表示窗口从分区的第一行开始。
●N PRECEDING——表示窗口从当前行之前的第N行开始。
●CURRENT ROW——表示窗口从当前行开始。

frame_end选项用于定义窗口的结束位置，可以指定以下内容之一：
●CURRENT ROW——表示窗口到当前行结束。
●M FOLLOWING——表示窗口到当前行之后的第M行结束。
●UNBOUNDED FOLLOWING——表示窗口到分区的最后一行结束。

下图说明了这些窗口大小选项的含义

下面语句表示分析窗口从当前分区的第一行开始，直到当前行结束，即对应到图中前面5行记录。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

窗口函数分类

1.聚合窗口函数

许多常见的聚合函数也可以作为窗口函数使用，包括AVG()、SUM()、COUNT()、MAX()以及MIN()等函数。

SQL窗口函数-聚合窗口函数

2.排名窗口函数

排名窗口函数用于对数据进行分组排名，包括ROW_NUMBER()、RANK()、DENSE_RANK()、PERCENT_RANK()、CUME_DIST()以及NTILE()等函数。

SQL窗口函数-排名窗口函数

3.取值窗口函数

取值窗口函数用于返回指定位置上的数据行，包括FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()、NTH_VALUE()等函数。

SQL窗口函数-取值窗口函数

示例表和脚本

--员工信息表
CREATE TABLE employee
    ( emp_id    NUMBER
    , emp_name  VARCHAR2(50) NOT NULL
    , sex       VARCHAR2(10) NOT NULL
    , dept_id   INTEGER NOT NULL
    , manager   INTEGER
    , hire_date DATE NOT NULL
    , job_id    INTEGER NOT NULL
    , salary    NUMERIC(8,2) NOT NULL
    , bonus     NUMERIC(8,2)
    , email     VARCHAR2(100) NOT NULL
  , comments  VARCHAR2(500)
  , create_by VARCHAR2(50) NOT NULL
  , create_ts TIMESTAMP NOT NULL
  , update_by VARCHAR2(50) 
  , update_ts TIMESTAMP
    ) ;
COMMENT ON TABLE employee IS '员工信息表';
COMMENT ON COLUMN employee.emp_id IS '员工编号，自增主键';
COMMENT ON COLUMN employee.emp_name IS '员工姓名';
COMMENT ON COLUMN employee.sex IS '性别';
COMMENT ON COLUMN employee.dept_id IS '部门编号';
COMMENT ON COLUMN employee.manager IS '上级经理';
COMMENT ON COLUMN employee.hire_date IS '入职日期';
COMMENT ON COLUMN employee.job_id IS '职位编号';
COMMENT ON COLUMN employee.salary IS '月薪';
COMMENT ON COLUMN employee.bonus IS '年终奖金';
COMMENT ON COLUMN employee.email IS '电子邮箱';
COMMENT ON COLUMN employee.comments IS '备注信息';
COMMENT ON COLUMN employee.create_by IS '创建者';
COMMENT ON COLUMN employee.create_ts IS '创建时间';
COMMENT ON COLUMN employee.update_by IS '修改者';
COMMENT ON COLUMN employee.update_ts IS '修改时间';
 
 
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (1,'刘备', '男', 1, NULL, DATE '2000-01-01', 1, 30000, 10000, 'liubei@shuguo.com', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (2,'关羽', '男', 1, 1, DATE '2000-01-01', 2, 26000, 10000, 'guanyu@shuguo.com', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (3,'张飞', '男', 1, 1, DATE '2000-01-01', 2, 24000, 10000, 'zhangfei@shuguo.com', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (4,'诸葛亮', '男', 2, 1, DATE '2006-03-15', 3, 24000, 8000, 'zhugeliang@shuguo.com', NULL, 'Admin', TIMESTAMP '2006-03-15 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (5,'黄忠', '男', 2, 4, DATE '2008-10-25', 4, 8000, NULL, 'huangzhong@shuguo.com', NULL, 'Admin', TIMESTAMP '2008-10-25 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (6,'魏延', '男', 2, 4, DATE '2007-04-01', 4, 7500, NULL, 'weiyan@shuguo.com', NULL, 'Admin', TIMESTAMP '2007-04-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (7,'孙尚香', '女', 3, 1, DATE '2002-08-08', 5, 12000, 5000, 'sunshangxiang@shuguo.com', NULL, 'Admin', TIMESTAMP '2002-08-08 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (8,'孙丫鬟', '女', 3, 7, DATE '2002-08-08', 6, 6000, NULL, 'sunyahuan@shuguo.com', NULL, 'Admin', TIMESTAMP '2002-08-08 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (9,'赵云', '男', 4, 1, DATE '2005-12-19', 7, 15000, 6000, 'zhaoyun@shuguo.com', NULL, 'Admin', TIMESTAMP '2005-12-19 10:00:00', 'Admin', TIMESTAMP '2006-12-31 10:00:00');
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (10,'廖化', '男', 4, 9, DATE '2009-02-17', 8, 6500, NULL, 'liaohua@shuguo.com', NULL, 'Admin', TIMESTAMP '2009-02-17 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (11,'关平', '男', 4, 9, DATE '2011-07-24', 8, 6800, NULL, 'guanping@shuguo.com', NULL, 'Admin', TIMESTAMP '2011-07-24 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (12,'赵氏', '女', 4, 9, DATE '2011-11-10', 8, 6600, NULL, 'zhaoshi@shuguo.com', NULL, 'Admin', TIMESTAMP '2011-11-10 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (13,'关兴', '男', 4, 9, DATE '2011-07-30', 8, 7000, NULL, 'guanxing@shuguo.com', NULL, 'Admin', TIMESTAMP '2011-07-30 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (14,'张苞', '男', 4, 9, DATE '2012-05-31', 8, 6500, NULL, 'zhangbao@shuguo.com', NULL, 'Admin', TIMESTAMP '2012-05-31 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (15,'赵统', '男', 4, 9, DATE '2012-05-03', 8, 6000, NULL, 'zhaotong@shuguo.com', NULL, 'Admin', TIMESTAMP '2012-05-03 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (16,'周仓', '男', 4, 9, DATE '2010-02-20', 8, 8000, NULL, 'zhoucang@shuguo.com', NULL, 'Admin', TIMESTAMP '2010-02-20 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (17,'马岱', '男', 4, 9, DATE '2014-09-16', 8, 5800, NULL, 'madai@shuguo.com', NULL, 'Admin', TIMESTAMP '2014-09-16 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (18,'法正', '男', 5, 2, DATE '2017-04-09', 9, 10000, 5000, 'fazheng@shuguo.com', NULL, 'Admin', TIMESTAMP '2017-04-09 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (19,'庞统', '男', 5, 18, DATE '2017-06-06', 10, 4100, 2000, 'pangtong@shuguo.com', NULL, 'Admin', TIMESTAMP '2017-06-06 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (20,'蒋琬', '男', 5, 18, DATE '2018-01-28', 10, 4000, 1500, 'jiangwan@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-01-28 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (21,'黄权', '男', 5, 18, DATE '2018-03-14', 10, 4200, NULL, 'huangquan@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-03-14 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (22,'糜竺', '男', 5, 18, DATE '2018-03-27', 10, 4300, NULL, 'mizhu@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-03-27 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (23,'邓芝', '男', 5, 18, DATE '2018-11-11', 10, 4000, NULL, 'dengzhi@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-11-11 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (24,'简雍', '男', 5, 18, DATE '2019-05-11', 10, 4800, NULL, 'jianyong@shuguo.com', NULL, 'Admin', TIMESTAMP '2019-05-11 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (25,'孙乾', '男', 5, 18, DATE '2018-10-09', 10, 4700, NULL, 'sunqian@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-10-09 10:00:00', NULL, NULL);

推荐学习：《SQL教程》

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