SQL教程：查询用户累计数据，结合阈值与最新记录日期

在数据分析和业务报告中，经常需要对用户的行为数据进行累计统计，并根据特定阈值进行分类或展示。例如，在一个健身应用中，我们可能需要跟踪用户累计的骑行距离，并识别那些已经达到特定里程碑（如1000公里）的用户，同时也要展示其他用户的当前累计进度。本文将以一个具体的场景为例，详细讲解如何通过SQL实现这一复杂的查询需求。

问题背景与数据模型

假设我们有一个名为workouts_data的表，用于记录用户的日常骑行活动，其结构如下：

我们的目标是：

1. 计算每个用户在指定日期范围内的总骑行距离。
2. 如果用户的总距离达到或超过1000，则在结果中显示“1000”。
3. 如果用户的总距离未达到1000，则显示其实际的总距离。
4. 结果中需要包含每个用户的最新活动日期。
5. 最终结果应按累计距离降序排列。

示例数据:

解决方案分解

为了实现上述目标，我们需要分步进行查询：

1. 计算每个用户的总距离： 这是一个标准的聚合操作，通过SUM()函数和GROUP BY User可以实现。
2. 获取每个用户的最新活动记录： 由于我们需要在最终结果中显示用户的最新活动日期，因此需要找到每个用户对应的最新一条记录。这可以通过查找每个用户的最大id（假设id是递增的唯一标识符，代表记录的创建顺序）来实现。
3. 合并数据并应用阈值逻辑： 将上述两步的结果与原始表连接起来，然后使用CASE语句根据总距离应用1000的阈值逻辑。

SQL查询实现

以下是实现此需求的完整SQL查询：

SELECT
    w1.`user`,
    CASE
        WHEN t1.distance >= 1000 THEN 1000
        ELSE t1.distance
    END AS distance_completed,
    t3.date
FROM
    workouts_data w1
INNER JOIN (
    SELECT
        `user`,
        SUM(distance) AS `distance`
    FROM
        `workouts_data`
    WHERE
        `date` BETWEEN 1609372800 AND 1640995140
        AND `user` IN (1, 2, 3)
    GROUP BY
        `user`
) AS t1 ON w1.user = t1.user
INNER JOIN (
    SELECT
        `date`,
        id,
        `user`
    FROM
        workouts_data
    WHERE
        (id, `user`) IN (
            SELECT
                MAX(id),
                `user`
            FROM
                workouts_data
            GROUP BY
                `user`
        )
) AS t3 ON w1.user = t3.user AND w1.id = t3.id
ORDER BY
    t1.distance DESC;

查询解析

让我们逐一分析上述SQL查询的各个部分：

1. 子查询 t1 (计算用户总距离):

   SELECT
       `user`,
       SUM(distance) AS `distance`
   FROM
       `workouts_data`
   WHERE
       `date` BETWEEN 1609372800 AND 1640995140
       AND `user` IN (1, 2, 3)
   GROUP BY
       `user`

   登录后复制

   这个子查询的作用是计算每个指定用户在特定日期范围内的总骑行距离。

   • WHERE 子句用于过滤日期范围和用户ID。
   • GROUP BYuser`` 将结果按用户分组。
   • SUM(distance) 计算每个用户的总距离，并将其命名为 distance。

2. 子查询 t3 (获取用户最新活动记录):

   

   Ai好记

   强大的AI音视频转录与总结工具

   311

   查看详情

   SELECT
       `date`,
       id,
       `user`
   FROM
       workouts_data
   WHERE
       (id, `user`) IN (
           SELECT
               MAX(id),
               `user`
           FROM
               workouts_data
           GROUP BY
               `user`
       )

   登录后复制

   这个子查询的目的是为每个用户找到其最新的活动记录（即具有最大id的记录），从而获取对应的date。

   • 内层的 SELECT MAX(id),userFROM workouts_data GROUP BYuser`找出每个用户的最大id`。
   • 外层的 WHERE (id,user) IN (...) 使用这些最大id和对应的user来从 workouts_data 表中筛选出完整的最新记录。

3. 主查询与连接 (结合数据并应用逻辑):

   SELECT
       w1.`user`,
       CASE
           WHEN t1.distance >= 1000 THEN 1000
           ELSE t1.distance
       END AS distance_completed,
       t3.date
   FROM
       workouts_data w1
   INNER JOIN t1 ON w1.user = t1.user
   INNER JOIN t3 ON w1.user = t3.user AND w1.id = t3.id
   ORDER BY
       t1.distance DESC;

   登录后复制
   • 主查询从 workouts_data 表（别名为 w1）开始。
   • INNER JOIN t1 ON w1.user = t1.user 将 w1 与 t1 子查询的结果连接起来，基于 user 字段匹配，以便获取每个用户的总距离。
   • INNER JOIN t3 ON w1.user = t3.user AND w1.id = t3.id 将 w1 与 t3 子查询的结果连接起来，基于 user 和 id 字段匹配，确保我们取到的是每个用户的最新记录的日期。
   • CASE WHEN t1.distance >= 1000 THEN 1000 ELSE t1.distance END AS distance_completed 是核心逻辑，它根据 t1 中计算出的总距离来决定 distance_completed 的值。
   • ORDER BY t1.distance DESC 对最终结果按 distance_completed（即总距离，未被1000截断前的实际总距离）降序排序。

预期输出

根据示例数据和上述查询，最终结果将如下所示：

• 用户1的总距离超过1000（实际为1210），因此显示为1000，并显示其最新活动日期。
• 用户3的总距离为350，未达到1000，因此显示350，并显示其最新活动日期。
• 用户2的总距离为200，未达到1000，因此显示200，并显示其最新活动日期。

注意事项与最佳实践

1. id 列的依赖： 本解决方案中，t3 子查询依赖于 id 列作为记录的唯一且递增的标识符来确定“最新”记录。如果表中没有这样的 id 列，或者 id 不保证是递增的，您可以改用 MAX(date) 来获取最新日期。但请注意，如果同一用户在同一日期有多个记录，MAX(date) 可能不足以唯一确定一条记录，可能需要结合其他列（如时间戳更精确的部分）或使用窗口函数。

2. 累计总和与首次达到阈值： 本文的解决方案计算的是用户在指定日期范围内的 总和，并在此总和上应用1000的阈值。它并没有找出用户 首次 累计达到1000时的具体记录。如果需要找出首次达到阈值的记录，则需要更复杂的窗口函数（如 SUM() OVER (PARTITION BY User ORDER BY Date)）来计算逐行累计和，然后筛选出满足条件的第一个记录。根据原始问题描述及提供的答案，当前方案是更符合实际需求的。

3. 日期范围过滤： WHERE date BETWEEN ... AND ... 语句对于控制数据量至关重要。确保日期戳的准确性，并且根据实际需求调整时间范围。

4. 性能考虑： 对于非常大的数据集，嵌套子查询可能会影响查询性能。确保 workouts_data 表在 user, date, id 列上建立了合适的索引，这将显著提高查询效率。在某些数据库系统中，使用通用表表达式（CTE，WITH 子句）来组织子查询有时可以提高可读性，并且在某些情况下数据库优化器能更好地处理。

总结

通过结合使用子查询、INNER JOIN 和 CASE 语句，我们成功地解决了在SQL中处理用户累计数据、应用阈值逻辑并获取最新相关记录的复杂问题。这种模式在处理各种业务场景中具有广泛的应用价值，例如用户积分、里程统计、销售目标达成等。理解并灵活运用这些SQL技巧，能够有效提升数据处理和分析的能力。

以上就是SQL教程：查询用户累计数据，结合阈值与最新记录日期的详细内容，更多请关注php中文网其它相关文章！