SQLite 窗口分析

介绍

在这个实验中，你将探索 SQLite 窗口分析（window analytics），重点关注对行进行排名和计算累计总计（running totals）。你将学习如何使用窗口函数（window functions）来执行与当前行相关的行集合的计算。

具体来说，你将使用 ROW_NUMBER() 函数根据销售额为每一行分配一个唯一的排名。你还将学习如何计算累计总计和划分数据以进行更高级的分析。这个实验提供了 SQLite 中窗口函数的实践性介绍。

Skills Graph

创建销售数据库和表

在第一步中，你将创建一个名为 sales.db 的 SQLite 数据库和一个名为 sales 的表来存储销售数据。该表将包含产品 ID（product ID）、产品名称（product name）和销售额（sales amount）的列。

在 LabEx VM 中打开你的终端。你的默认路径是 /home/labex/project。

首先，创建 sales.db 数据库并通过运行以下命令打开 SQLite 命令行工具：

sqlite3 sales.db

这个命令会创建数据库文件并打开 SQLite shell，你可以在其中执行 SQL 命令。你将看到如下提示：

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

接下来，创建包含以下列的 sales 表：product_id、product_name 和 sales_amount。在 sqlite> 提示符下输入以下 SQL 命令，然后按 Enter 键：

CREATE TABLE sales (
    product_id INTEGER,
    product_name TEXT,
    sales_amount INTEGER
);

这个命令会建立 sales 表，其中：

• product_id 是一个整数，表示每个产品的唯一标识符。
• product_name 是一个文本字段，用于存储产品的名称。
• sales_amount 是一个整数，表示产品的销售额。

如果命令成功运行，你将不会看到任何输出。

将示例数据插入到 Sales 表中

现在你已经创建了 sales 表，让我们向其中添加一些示例数据。我们将插入 6 条记录，代表不同的产品及其销售额。

通过在 sqlite> 提示符下逐个运行以下命令，将以下记录插入到 sales 表中：

INSERT INTO sales (product_id, product_name, sales_amount) VALUES
(1, 'Laptop', 1200),
(2, 'Keyboard', 75),
(3, 'Mouse', 25),
(4, 'Monitor', 300),
(5, 'Headphones', 100),
(6, 'Webcam', 50);

这些命令会将 6 行添加到 sales 表中。每一行代表一个产品，包含其 ID、名称和销售额。

• INSERT INTO sales (product_id, product_name, sales_amount) 指定你正在将数据插入到 sales 表的 product_id、product_name 和 sales_amount 列中。
• VALUES (1, 'Laptop', 1200) 提供了要为每条记录插入的值。

为了确认数据已正确添加，运行以下命令以查看表中的所有记录：

SELECT * FROM sales;

预期输出：

1|Laptop|1200
2|Keyboard|75
3|Mouse|25
4|Monitor|300
5|Headphones|100
6|Webcam|50

此输出显示了每条记录的 product_id、product_name 和 sales_amount。SELECT * 命令检索指定表中的所有列。

使用 ROW_NUMBER() 对行进行排序

在这一步中，你将学习如何使用 ROW_NUMBER() 窗口函数根据 sales_amount 为每一行分配一个唯一的排名（rank）。这对于识别最畅销的产品非常有用。

ROW_NUMBER() 函数在结果集的分区中为每一行分配一个唯一的整数。排名由 ORDER BY 子句中指定的顺序决定。

在 sqlite> 提示符下执行以下查询：

SELECT
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM
    sales;

这个查询根据每个产品的 sales_amount（降序排列）计算其排名。

• ROW_NUMBER() OVER (ORDER BY sales_amount DESC) 根据 sales_amount 为每一行分配一个排名，销售额最高的获得排名 1。
• ORDER BY sales_amount DESC 指定排名应基于 sales_amount 降序排列。

预期输出：

Laptop|1200|1
Monitor|300|2
Headphones|100|3
Keyboard|75|4
Webcam|50|5
Mouse|25|6

正如你所看到的，sales_rank 列现在包含每个产品基于其 sales_amount 的排名，销售额最高的（Laptop）获得排名 1。

计算累计总计（Running Totals）

在这一步中，你将学习如何使用窗口函数计算累计总计（running totals，累积和）。累计总计对于跟踪一段时间内或一组行中的值的总和非常有用。

要计算累计总计，你需要将 SUM() 函数与 OVER() 子句和 ORDER BY 子句结合使用，以指定计算总和的顺序。

首先，让我们向 sales 表添加一个 sale_date 列，并用一些示例日期填充它。在 sqlite> 提示符下执行以下命令：

ALTER TABLE sales ADD COLUMN sale_date DATE;

UPDATE sales SET sale_date = '2023-01-01' WHERE product_name = 'Laptop';
UPDATE sales SET sale_date = '2023-01-05' WHERE product_name = 'Keyboard';
UPDATE sales SET sale_date = '2023-01-10' WHERE product_name = 'Mouse';
UPDATE sales SET sale_date = '2023-01-15' WHERE product_name = 'Monitor';
UPDATE sales SET sale_date = '2023-01-20' WHERE product_name = 'Headphones';
UPDATE sales SET sale_date = '2023-01-25' WHERE product_name = 'Webcam';

这些命令向 sales 表添加一个 sale_date 列，并使用每个产品的示例日期更新该表。

现在，让我们计算 sales_amount 随时间的累计总计，按 sale_date 排序。执行以下查询：

SELECT
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM
    sales;

这个查询计算 sales_amount 随时间的累计总计，按 sale_date 排序。

• SUM(sales_amount) OVER (ORDER BY sale_date) 计算截至每个 sale_date 的 sales_amount 的累积和。
• ORDER BY sale_date 指定应基于 sale_date 按升序计算累计总计。

预期输出：

2023-01-01|Laptop|1200|1200
2023-01-05|Keyboard|75|1275
2023-01-10|Mouse|25|1300
2023-01-15|Monitor|300|1600
2023-01-20|Headphones|100|1700
2023-01-25|Webcam|50|1750

running_total 列显示了截至每个 sale_date 的 sales_amount 的累积和。例如，'2023-01-15' 的累计总计为 1600，这是 '2023-01-01'、'2023-01-05'、'2023-01-10' 和 '2023-01-15' 的销售额之和。

对数据进行分区以进行分析

在这一步中，你将学习如何使用窗口函数中的 PARTITION BY 子句对数据进行分区。分区允许你将数据划分为逻辑组，然后在每个组中独立执行计算。

让我们向 sales 表添加一个 product_category 列。在 sqlite> 提示符下执行以下命令：

ALTER TABLE sales ADD COLUMN product_category TEXT;

UPDATE sales SET product_category = 'Electronics' WHERE product_name IN ('Laptop', 'Monitor', 'Headphones', 'Webcam');
UPDATE sales SET product_category = 'Accessories' WHERE product_name IN ('Keyboard', 'Mouse');

这些命令向 sales 表添加一个 product_category 列，并使用每个产品的类别更新该表。

现在，让我们使用 PARTITION BY 来计算每个 product_category 中 sales_amount 的累计总计。执行以下查询：

SELECT
    product_category,
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_total_by_category
FROM
    sales;

这个查询计算每个 product_category 中 sales_amount 的累计总计，按 sale_date 排序。

• PARTITION BY product_category 根据 product_category 将数据划分为分区。
• SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) 计算每个 product_category 中 sales_amount 的累积和，按 sale_date 排序。

预期输出：

Accessories|2023-01-05|Keyboard|75|75
Accessories|2023-01-10|Mouse|25|100
Electronics|2023-01-01|Laptop|1200|1200
Electronics|2023-01-15|Monitor|300|1500
Electronics|2023-01-20|Headphones|100|1600
Electronics|2023-01-25|Webcam|50|1650

running_total_by_category 列显示了每个 product_category 中 sales_amount 的累积和，按 sale_date 排序。请注意，每个类别的累计总计都会重新开始。

总结

在这个实验中，你学习了如何在 SQLite 中使用窗口函数来执行高级数据分析。你首先创建了一个 sales 表并插入了示例数据。然后，你使用 ROW_NUMBER() 函数根据产品的销售额对产品进行排名。你还学习了如何使用带有 OVER() 子句的 SUM() 函数来计算累计总计，以及如何使用 PARTITION BY 子句对数据进行分区，以便在逻辑组中执行计算。这些技能为你在 SQLite 中执行更复杂的数据分析任务奠定了基础。