Posted Updated database6 minutes read (About 858 words)

SQL(2) - 开窗函数

开窗函数（Window Functions）是SQL中一种强大的功能，用于在一组相关行（窗口）上执行聚合计算，同时保留每行的详细信息。与传统的聚合函数（如 SUM, AVG, COUNT 等）不同，开窗函数不会将结果聚合成单一的行，而是为每一行返回一个计算结果。

基本语法

开窗函数的基本语法如下：

1
2
3
4
5
function_name(expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC | DESC]]
    [ROWS BETWEEN start AND end]
)

主要组成部分

  1. 函数名：常见的开窗函数包括 ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM(), AVG(), MIN(), MAX() 等。
  2. OVER子句：定义窗口的范围。
    • PARTITION BY：将数据分成多个分区，每个分区独立计算。
    • ORDER BY：在每个分区内对数据进行排序。
    • ROWS BETWEEN：定义窗口的行范围。

常见的开窗函数

1. 行号函数

  • ROW_NUMBER()：为每个分区内的行分配一个唯一的行号。

    1
    ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)

  • RANK()：为每个分区内的行分配一个排名，如果有相同的值，排名会跳过。

    1
    RANK() OVER (PARTITION BY column1 ORDER BY column2)

  • DENSE_RANK()：为每个分区内的行分配一个排名，如果有相同的值，排名不会跳过。

    1
    DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2)

2. 前后行函数

  • LEAD()：获取当前行之后的某一行的值。

    1
    LEAD(column, offset, default) OVER (PARTITION BY column1 ORDER BY column2)

  • LAG()：获取当前行之前的某一行的值。

    1
    LAG(column, offset, default) OVER (PARTITION BY column1 ORDER BY column2)

3. 聚合函数

  • SUM()：计算窗口内的总和。

    1
    SUM(column) OVER (PARTITION BY column1 ORDER BY column2)

  • AVG()：计算窗口内的平均值。

    1
    AVG(column) OVER (PARTITION BY column1 ORDER BY column2)

  • MIN()：计算窗口内的最小值。

    1
    MIN(column) OVER (PARTITION BY column1 ORDER BY column2)

  • MAX()：计算窗口内的最大值。

    1
    MAX(column) OVER (PARTITION BY column1 ORDER BY column2)

示例

假设有一个销售数据表 sales，包含以下字段：id, product_id, sale_date, amount。

1. 行号函数示例

1
2
3
SELECT id, product_id, sale_date, amount,
       ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) as row_num
FROM sales;

这个查询为每个产品的销售记录按日期排序，并分配一个行号。

2. 排名函数示例

1
2
3
SELECT id, product_id, sale_date, amount,
       RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) as rank
FROM sales;

这个查询为每个产品的销售记录按金额降序排序，并分配一个排名。

3. 前后行函数示例

1
2
3
4
SELECT id, product_id, sale_date, amount,
       LAG(amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_amount,
       LEAD(amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) as next_amount
FROM sales;

这个查询为每个产品的销售记录按日期排序，并获取前一行和后一行的金额。

4. 聚合函数示例

1
2
3
SELECT id, product_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) as cumulative_sum
FROM sales;

这个查询为每个产品的销售记录按日期排序，并计算累计销售额。

窗口范围

  • ROWS BETWEEN：定义窗口的行范围。
    1
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
    这个查询计算每个产品销售记录按日期排序时，当前行及其前后两行的总和。
Posted Updated database7 minutes read (About 1030 words)

SQL(1)-基本用法

SQL（Structured Query Language）是用于管理和操作关系型数据库的标准语言。以下是SQL的一些常用知识点，涵盖了基本的查询、数据操作、数据定义和数据控制等方面：

1. 基本查询

  • SELECT语句：用于从数据库中检索数据。

    1
    2
    SELECT column1, column2, ...
    FROM table_name;

  • WHERE子句：用于过滤记录。

    1
    2
    3
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

  • AND / OR：用于组合多个条件。

    1
    2
    3
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition1 AND condition2;

  • IN子句：用于指定多个可能的值。

    1
    2
    3
    SELECT column1, column2, ...
    FROM table_name
    WHERE column_name IN (value1, value2, ...);

  • BETWEEN子句：用于选取介于两个值之间的数据。

    1
    2
    3
    SELECT column1, column2, ...
    FROM table_name
    WHERE column_name BETWEEN value1 AND value2;

  • LIKE子句：用于模式匹配。

    1
    2
    3
    SELECT column1, column2, ...
    FROM table_name
    WHERE column_name LIKE pattern;

2. 排序和分组

  • ORDER BY子句：用于对结果集进行排序。

    1
    2
    3
    SELECT column1, column2, ...
    FROM table_name
    ORDER BY column1 ASC/DESC;

  • GROUP BY子句：用于将数据分组。

    1
    2
    3
    SELECT column1, column2, AGGREGATE_FUNCTION(column3)
    FROM table_name
    GROUP BY column1, column2;

  • HAVING子句：用于过滤分组后的结果。

    1
    2
    3
    4
    SELECT column1, AGGREGATE_FUNCTION(column2)
    FROM table_name
    GROUP BY column1
    HAVING AGGREGATE_FUNCTION(column2) condition;

3. 聚合函数

  • COUNT：用于计数。

    1
    2
    SELECT COUNT(column_name)
    FROM table_name;

  • SUM：用于求和。

    1
    2
    SELECT SUM(column_name)
    FROM table_name;

  • AVG：用于求平均值。

    1
    2
    SELECT AVG(column_name)
    FROM table_name;

  • MAX：用于求最大值。

    1
    2
    SELECT MAX(column_name)
    FROM table_name;

  • MIN：用于求最小值。

    1
    2
    SELECT MIN(column_name)
    FROM table_name;

4. 数据操作

  • INSERT语句：用于插入新记录。

    1
    2
    INSERT INTO table_name (column1, column2, ...)
    VALUES (value1, value2, ...);

  • UPDATE语句：用于更新现有记录。

    1
    2
    3
    UPDATE table_name
    SET column1 = value1, column2 = value2, ...
    WHERE condition;

  • DELETE语句：用于删除记录。

    1
    2
    DELETE FROM table_name
    WHERE condition;

5. 数据定义

  • CREATE TABLE语句：用于创建新表。

    1
    2
    3
    4
    5
    CREATE TABLE table_name (
        column1 datatype constraints,
        column2 datatype constraints,
        ...
    );

  • ALTER TABLE语句：用于修改表结构。

    1
    2
    ALTER TABLE table_name
    ADD column_name datatype;

  • DROP TABLE语句：用于删除表。

    1
    DROP TABLE table_name;

6. 约束

  • PRIMARY KEY：用于唯一标识表中的每条记录。

    1
    2
    3
    4
    5
    CREATE TABLE table_name (
        column1 datatype PRIMARY KEY,
        column2 datatype,
        ...
    );

  • FOREIGN KEY：用于建立表之间的关系。

    1
    2
    3
    4
    5
    CREATE TABLE table_name (
        column1 datatype,
        column2 datatype,
        FOREIGN KEY (column1) REFERENCES other_table_name(column1)
    );

  • UNIQUE：用于确保列中的所有值都是唯一的。

    1
    2
    3
    4
    5
    CREATE TABLE table_name (
        column1 datatype UNIQUE,
        column2 datatype,
        ...
    );

  • NOT NULL：用于确保列中的值不能为空。

    1
    2
    3
    4
    5
    CREATE TABLE table_name (
        column1 datatype NOT NULL,
        column2 datatype,
        ...
    );

7. 子查询

  • 子查询：在一个查询中嵌套另一个查询。
    1
    2
    3
    SELECT column1, column2, ...
    FROM table_name
    WHERE column1 = (SELECT column1 FROM another_table WHERE condition);

8. 联接（JOIN）

  • INNER JOIN：返回两个表中匹配的记录。

    1
    2
    3
    4
    SELECT table1.column1, table2.column2, ...
    FROM table1
    INNER JOIN table2
    ON table1.common_column = table2.common_column;

  • LEFT JOIN：返回左表中的所有记录，以及右表中匹配的记录。

    1
    2
    3
    4
    SELECT table1.column1, table2.column2, ...
    FROM table1
    LEFT JOIN table2
    ON table1.common_column = table2.common_column;

  • RIGHT JOIN：返回右表中的所有记录，以及左表中匹配的记录。

    1
    2
    3
    4
    SELECT table1.column1, table2.column2, ...
    FROM table1
    RIGHT JOIN table2
    ON table1.common_column = table2.common_column;

  • FULL OUTER JOIN：返回两个表中的所有记录，如果没有匹配，则返回 NULL。

    1
    2
    3
    4
    SELECT table1.column1, table2.column2, ...
    FROM table1
    FULL OUTER JOIN table2
    ON table1.common_column = table2.common_column;

9. 事务控制

  • BEGIN TRANSACTION：开始一个事务。

    1
    BEGIN TRANSACTION;

  • COMMIT：提交事务。

    1
    COMMIT;

  • ROLLBACK：回滚事务。

    1
    ROLLBACK;

10. 视图

  • CREATE VIEW：创建视图。

    1
    2
    3
    4
    CREATE VIEW view_name AS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

  • DROP VIEW：删除视图。

    1
    DROP VIEW view_name;

11. 索引

  • CREATE INDEX：创建索引。

    1
    2
    CREATE INDEX index_name
    ON table_name (column1, column2, ...);

  • DROP INDEX：删除索引。

    1
    DROP INDEX index_name;

12. 存储过程和函数

  • 存储过程：预编译的SQL代码块，可以多次调用。

    1
    2
    3
    4
    5
    CREATE PROCEDURE procedure_name
    AS
    BEGIN
        -- SQL statements
    END;

  • 函数：返回一个值的预编译的SQL代码块。

    1
    2
    3
    4
    5
    6
    7
    CREATE FUNCTION function_name (parameters)
    RETURNS datatype
    AS
    BEGIN
        -- SQL statements
        RETURN result;
    END;
Follow

Links

Categories

Recents

Archives

Tags

Algorithm1
C++2
Elasticsearch1
LLM2
Linux2
Machine Learning1
Markdown1
Math2
NLP12
Recommender System1
SQL2
algorithm8
big data13
computer network1
computer vision5
deep learning5
git3
graph1
hexo1
information retrieval1
java2
machine learning18
operating system2
python5
reinforcement learning1
speech2
tools1
web1

Subscribe for updates

You need to set client_id and slot_id to show this AD unit. Please set it in _config.yml.

follow.it

×