SQL 数据聚合与条件记录检索教程

本教程详细阐述如何在sql中处理用户活动数据，特别是如何计算每个用户的累计距离，并根据是否达到特定阈值（例如1000单位）来检索记录。文章将指导您构建一个高效的查询，以获取用户的总累计距离（若超过阈值则显示阈值，否则显示实际总和）及其最近一次活动记录的日期，并进行适当的排序。

在数据分析和业务监控中，我们经常需要跟踪用户在一段时间内的累积行为，例如累计完成的任务量、累计消费金额或累计运动距离。本教程将以一个具体的场景为例：从用户的日常运动数据中，检索每个用户的累计骑行距离。具体要求是：如果用户累计距离超过1000单位，则显示1000；如果未达到1000，则显示其实际累计距离。同时，结果需要显示每个用户的最新活动日期，并按特定规则排序。

数据模型与示例数据

假设我们有一个名为 workouts_data 的表，用于存储用户的每日骑行距离数据。该表包含以下字段：

• Date: Unix时间戳，表示骑行日期。
• User: 用户ID。
• Distance: 当日骑行距离。
• id: 记录的唯一标识符（假设存在并用于识别最新记录）。

示例数据:

Date       User        Distance   id (假设存在)
1614944833   1           100        1
1614944232   2           100        2
1624944831   1           150        3
1615944832   3           250        4
1614644836   1           500        5
1614954835   2           100        6
1614344834   3           100        7
1614964831   1           260        8
1614944238   1           200        9

问题分析与解决方案策略

我们的目标是为每个用户计算其在指定日期范围内的总累计距离。对于已达到或超过1000单位的用户，我们将显示1000；对于未达到1000单位的用户，显示其实际总和。此外，我们还需要获取每个用户的最新活动日期。

直接使用 GROUP BY User 并计算 SUM(Distance) 可以得到每个用户的总距离。但要同时获取最新活动日期，并根据总距离进行条件判断，需要更复杂的联接和子查询。

本解决方案将采用以下策略：

1. 计算用户总距离： 使用子查询计算每个用户在指定日期范围内的总骑行距离。
2. 获取最新活动日期： 使用另一个子查询，为每个用户找出其最新一条记录的日期。这通常通过找到每个用户记录的最大 id (如果 id 是递增的) 或 Date 来实现。
3. 联接与条件处理： 将主表与这两个子查询的结果进行联接，然后应用 CASE 语句来根据总距离是否超过1000进行条件处理。
4. 排序： 按照总距离（降序）和日期（升序）进行排序。

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 (
    -- 子查询 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`
) AS t1 ON w1.user = t1.user
INNER JOIN (
    -- 子查询 t3: 获取每个用户的最新活动记录的日期
    SELECT
        `date`,
        id,
        `user` -- 包含 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.date = t3.date
ORDER BY
    distance_completed DESC,
    t3.date ASC;

查询详解

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`

   登录后复制

   这个子查询负责聚合每个用户在特定日期范围内的所有骑行距离，计算出他们的总距离 distance。

2. t3 子查询（获取最新活动日期）:

   

   FashionLabs

   AI服装模特、商品图，可商用，低价提升销量神器

   38

   查看详情

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

   登录后复制

   这个子查询的目的是为每个用户找到其最新一条记录的日期。它首先通过 SELECT MAX(id), user FROM workouts_data GROUP BY user 找出每个用户的最大 id（假设 id 是一个递增的唯一标识符，且最大 id 对应最新记录）。然后，外部的 WHERE (id, user) IN (...) 语句用于筛选出 workouts_data 表中与这些最大 id 对应的完整记录，从而获取到最新记录的 date。

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.date = t3.date

   登录后复制

   主查询将原始表 workouts_data (别名为 w1) 与 t1 (总距离) 和 t3 (最新日期) 进行 INNER JOIN。

   • INNER JOIN t1 ON w1.user = t1.user: 将每个用户的总距离信息与主表关联。
   • INNER JOIN t3 ON w1.user = t3.user AND w1.date = t3.date: 将每个用户的最新活动日期信息与主表关联。这里的 w1.date = t3.date 确保我们从 w1 中选择的行是与 t3 中最新日期相匹配的行。

4. 条件处理 (CASE 语句):

   CASE
       WHEN t1.distance >= 1000 THEN 1000
       ELSE t1.distance
   END AS distance_completed

   登录后复制

   这部分根据 t1 子查询计算出的用户总距离 t1.distance 来决定 distance_completed 的值。如果总距离大于或等于1000，则显示1000；否则，显示实际的总距离。

5. 排序 (ORDER BY):

   ORDER BY distance_completed DESC, t3.date ASC;

   登录后复制

   结果首先按照 distance_completed 降序排列，这样累计达到1000的用户会排在前面。然后，对于 distance_completed 相同（例如都是1000）的记录，再按 t3.date 升序排列，以显示更早达到阈值的用户。

预期结果

使用上述示例数据和查询，您将得到类似以下的结果：

user  distance_completed    date
1     1000                 1614944238  -- (注意：此日期是用户1的最新活动日期，而非恰好达到1000的日期)
3     350                  1614344834
2     200                  1614954835

结果解读:

• 用户1: 总距离超过1000 (实际为1210)，因此 distance_completed 显示为1000。日期显示为其最新活动记录的日期（1614944238）。
• 用户3: 总距离为350，未达到1000，因此 distance_completed 显示为350。日期显示为其最新活动记录的日期（1614344834）。
• 用户2: 总距离为200，未达到1000，因此 distance_completed 显示为200。日期显示为其最新活动记录的日期（1614954835）。

注意事项与扩展

• 日期格式： 示例中的 Date 字段是 Unix 时间戳。在实际应用中，您可能需要根据数据库类型和需求，使用 FROM_UNIXTIME() 或其他日期函数将其转换为可读的日期格式。
• 性能优化： 对于非常大的数据集，子查询的性能可能成为瓶颈。可以考虑为 user 和 date 字段

以上就是SQL 数据聚合与条件记录检索教程的详细内容，更多请关注php中文网其它相关文章！