한 기사에서 SQL 창 기능에 대한 자세한 설명

이 기사에서는 주로 윈도우 기능과 관련된 문제를 정리하는 SQL에 대한 관련 지식을 제공합니다. SQL 윈도우 기능은 온라인 분석 처리(OLAP) 및 비즈니스 인텔리전스(BI) 기능에 대한 복잡한 분석 및 보고서 통계를 제공합니다. 판매통계, 카테고리 순위, 연/월별 분석 등 모두에게 도움이 되기를 바랍니다.

추천 학습: "SQL 튜토리얼"

윈도우 기능이란 무엇입니까?

SQL 창 기능은 온라인 분석 처리(OLAP) 및 비즈니스 인텔리전스(BI)를 위한 복잡한 분석 및 보고 통계 기능을 제공합니다. 상품 누적 판매 통계, 카테고리 순위, 전년/월별 분석 등 이러한 기능은 집계 함수 및 그룹화 작업으로 구현하기 어려운 경우가 많습니다.

윈도우 함수(Window Function)는 집계 함수처럼 데이터 집합을 분석하고 결과를 반환할 수 있습니다. 둘의 차이점은 윈도우 함수는 데이터 집합을 단일 결과로 집계하지 않고 대신 집합을 분석한다는 것입니다. 데이터의 각 행에 대한 데이터입니다. 결과를 반환합니다. 집계 함수와 창 함수의 차이점은 아래 그림과 같습니다.

두 함수의 차이점을 보여주기 위해 SUM 함수를 예로 들어 보겠습니다. 다음 명령문의 SUM()은 집계 함수입니다.

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

위 SUM 함수는 집계 함수로 사용될 수 있습니다. 모든 직원 데이터가 결과로 요약됩니다. 따라서 쿼리는 모든 직원의 월급 총액을 반환합니다. 다음 명령문의

SUM()은 창 함수입니다.

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

그 중 OVER 키워드는 SUM()이 창 함수임을 나타냅니다. 빈 괄호는 모든 데이터가 하나의 그룹으로 요약된다는 것을 나타냅니다. 이 쿼리에서 반환된 결과는 다음과 같습니다.

위 쿼리 결과는 모든 직원 이름을 반환하며, 집계 함수 SUM()을 통해 각 직원에 대해 동일한 요약 결과가 반환됩니다.

위의 예에서 볼 수 있듯이 윈도우 함수의 구문은 OVER 절을 포함한다는 점에서 집계 함수와 다릅니다. OVER 절은 데이터 분석을 위한 창을 지정하는 데 사용됩니다. 전체 창 함수 정의는 다음과 같습니다.

여기서 window_function은 창 함수의 이름이고, 표현식은 선택적 분석 개체(필드 이름 또는 표현식)입니다. OVER 절에는 파티션(PARTITION BY), 정렬(ORDER BY) 및 창 크기(frame_clause)의 세 가지 옵션이 포함되어 있습니다.

팁: 집계 함수는 동일한 그룹에 있는 여러 데이터 행을 단일 결과로 요약하는 반면 창 함수는 모든 원본 데이터를 유지합니다. 일부 데이터베이스에서는 창 기능을 OLAP(온라인 분석 처리) 기능 또는 분석 기능이라고도 합니다.

윈도우 함수 구성요소

1. 데이터 파티션 생성

윈도우 함수의 OVER 절에 있는 PARTITION BY 옵션은 파티션을 정의하는 데 사용되며, 그 역할은 쿼리문의 GROUP BY 절과 유사합니다. 파티션 옵션을 지정하면 창 기능이 각 파티션을 개별적으로 분석합니다.

예를 들어 다음 명령문은 부서별 직원의 월급 총액을 계산합니다.

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

그 중 PARTITION BY 옵션은 부서별 파티셔닝을 나타냅니다. 쿼리에 의해 반환된 결과는 다음과 같습니다.

쿼리 결과에 있는 데이터의 처음 세 행은 동일한 부서에 속하므로 해당 부서 전체 필드는 80000(30000+26000+24000)과 같습니다. 다른 부서의 직원도 같은 방식으로 계산됩니다.

Tips: 윈도우 함수 OVER 절에 PARTITION BY 옵션을 지정한 후 GROUP BY 절을 사용하지 않고도 그룹 통계 결과를 얻을 수 있습니다.

PARTITION BY 옵션을 지정하지 않으면 모든 데이터를 전체적으로 분석한다는 의미입니다.

2. 파티션 내 정렬

윈도우 함수의 OVER 절에 있는 ORDER BY 옵션은 파티션 내 데이터의 정렬 방법을 지정하는 데 사용되며 해당 기능은 쿼리문의 ORDER BY 절과 유사합니다. .

정렬 옵션은 일반적으로 데이터의 범주별 순위를 지정하는 데 사용됩니다. 예를 들어, 부서 내 직원의 월급 순위를 분석하려면 다음 명령문을 사용합니다.

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

그 중 RANK 함수를 사용하여 데이터의 순위를 계산하고 PARTITION BY 옵션은 부서별 파티셔닝을 나타내며, ORDER BY 옵션은 해당 부서 내 월급을 높은 순부터 낮은 순으로 표시합니다. 쿼리에서 반환된 결과는 다음과 같습니다.

查询结果中的前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教程》

위 내용은 한 기사에서 SQL 창 기능에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!