本教程详细阐述如何在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) 可以得到每个用户的总距离。但要同时获取最新活动日期，并根据总距离进行条件判断，需要更复杂的联接和子查询。
本解决方案将采用以下策略：
- 计算用户总距离： 使用子查询计算每个用户在指定日期范围内的总骑行距离。
- 获取最新活动日期： 使用另一个子查询，为每个用户找出其最新一条记录的日期。这通常通过找到每个用户记录的最大 id (如果 id 是递增的) 或 Date 来实现。
- 联接与条件处理： 将主表与这两个子查询的结果进行联接，然后应用 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 (
-- 子查询 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;
查询详解
-
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。
-
t3 子查询（获取最新活动日期）:
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。
-
主查询与联接:
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 中最新日期相匹配的行。
-
条件处理 (CASE 语句):
CASE WHEN t1.distance >= 1000 THEN 1000 ELSE t1.distance END AS distance_completed登录后复制这部分根据 t1 子查询计算出的用户总距离 t1.distance 来决定 distance_completed 的值。如果总距离大于或等于1000，则显示1000；否则，显示实际的总距离。
-
排序 (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中文网其它相关文章！