SQL视图创建：从审计日志中提取订阅者新增与删除时间

本文深入探讨如何利用sql视图、子查询及条件聚合，从多行审计日志中高效提取和整合特定事件的时间戳。我们将以订阅者生命周期管理为例，演示如何将分散的“新增”和“删除”记录转换为单行数据，从而清晰展现订阅者的完整生命周期，并探讨如何识别当前活跃的订阅者。

数据源概览：订阅者审计日志

在许多业务场景中，我们需要记录用户或订阅者在其生命周期中的关键操作。一个典型的例子是订阅者审计日志，它可能记录了订阅者的创建、更新和删除等事件。假设我们有一个名为 audit_subscibers 的表，其结构如下：

该表记录了每个订阅者在不同时间点的操作，例如：

CREATE TABLE audit_subscibers (
    id INT,
    name VARCHAR(30),
    action VARCHAR(60),
    time DATE
);

INSERT INTO audit_subscibers VALUES
(0,'John','Insert a subscriber','2020-01-01'),
(1,'John','Deleted a subscriber','2020-03-01'),
(2,'Mark','Insert a subscriber','2020-04-05'),
(3,'Andrew','Insert a subscriber','2020-05-01'),
(4,'Andrew','Updated a subscriber','2020-05-15');

目标场景一：追踪已删除订阅者的完整生命周期

我们的首要目标是创建一个视图，该视图仅显示那些已被删除的订阅者。对于每个已删除的订阅者，我们需要在一行中同时显示其“新增时间”和“删除时间”。这意味着我们需要筛选出同时存在“Insert a subscriber”和“Deleted a subscriber”操作的订阅者，并将这两条记录中的时间信息合并到两列中。

实现策略：结合子查询与条件聚合

为了实现这一目标，我们可以采用以下步骤：

1. 识别目标订阅者： 首先，我们需要确定哪些订阅者既有“新增”记录又有“删除”记录。这可以通过子查询和 GROUP BY 子句结合 HAVING 条件来完成。我们查找那些 Action 包含 'Insert a subscriber' 和 'Deleted a subscriber' 且这些操作总计数为2的订阅者。
2. 行转列与时间提取： 一旦确定了目标订阅者，我们需要将他们的“新增时间”和“删除时间”从多行记录转换到单行的两列中。这可以通过 CASE WHEN 表达式配合聚合函数（如 MAX 或 MIN）来实现。MAX 函数在这里是合适的，因为它会从所有匹配的 CASE 条件中选择唯一非空的时间值。
3. 构建最终查询并创建视图： 将上述逻辑整合到一个 SELECT 语句中，并将其封装为一个 CREATE VIEW 语句。

示例代码：

以下是实现上述目标的SQL查询和视图创建语句：

CREATE VIEW deleted_subscribers_lifecycle AS
SELECT
    name,
    MAX(CASE WHEN action = 'Insert a subscriber' THEN time END) AS Date_added,
    MAX(CASE WHEN action = 'Deleted a subscriber' THEN time END) AS Date_deleted
FROM
    (
        SELECT
            name,
            time,
            action
        FROM
            audit_subscibers
        WHERE
            name IN (
                SELECT
                    name
                FROM
                    audit_subscibers
                WHERE
                    action IN ('Insert a subscriber', 'Deleted a subscriber')
                GROUP BY
                    name
                HAVING
                    COUNT(DISTINCT action) = 2 -- 确保同时有新增和删除两种操作
            )
    ) AS filtered_subscribers
GROUP BY
    name;

查询结果示例：

运行 SELECT * FROM deleted_subscribers_lifecycle; 将得到如下结果：

行者AI

行者AI绘图创作，唤醒新的灵感，创造更多可能

100

查看详情

这个视图准确地展示了所有被删除的订阅者（在本例中是John），以及他们被添加和删除的具体时间。

目标场景二：识别当前活跃（未删除）的订阅者

另一个常见的需求是识别那些当前仍然“活跃”的订阅者，即他们有“新增”记录，但没有后续的“删除”记录。

实现思路：

要实现此目标，我们可以从所有有“Insert a subscriber”记录的订阅者开始，然后排除那些也有“Deleted a subscriber”记录的订阅者。这可以通过 LEFT JOIN 结合 WHERE ... IS NULL 或使用 NOT EXISTS 子查询来实现。

例如，使用 NOT EXISTS 的方法：

CREATE VIEW active_subscribers AS
SELECT
    DISTINCT a.name,
    a.time AS Date_added
FROM
    audit_subscibers a
WHERE
    a.action = 'Insert a subscriber'
    AND NOT EXISTS (
        SELECT 1
        FROM audit_subscibers b
        WHERE b.name = a.name AND b.action = 'Deleted a subscriber'
    );

这个视图将显示所有被添加但从未被删除的订阅者。

总结与注意事项

通过上述示例，我们展示了如何利用SQL的强大功能，特别是子查询、条件聚合 (CASE WHEN 和 MAX) 以及 GROUP BY，来处理复杂的审计日志数据。这种技术在数据分析、报表生成和业务洞察方面非常有用。

注意事项：

• 性能优化： 对于大型审计表，子查询和聚合操作可能会影响性能。确保 name 和 action 列上有适当的索引可以显著提高查询速度。
• 操作类型多样性： 如果 Action 字段包含更多类型的操作（例如多次更新），COUNT(DISTINCT action) = 2 的条件可能需要调整，或者更精确地判断是否存在特定的“新增”和“删除”操作。
• 时间戳精度： 如果 Time 字段需要更精确的时间（例如 DATETIME 或 TIMESTAMP），则在 MAX() 或 MIN() 聚合时需要考虑时间的先后顺序。
• 视图的优势： 将这些复杂查询封装到视图中，可以简化后续的数据访问，提高代码的可读性和可维护性，同时允许底层表结构发生变化时，只要视图定义不变，上层应用无需修改。

以上就是SQL视图创建：从审计日志中提取订阅者新增与删除时间的详细内容，更多请关注php中文网其它相关文章！