1661. 每台机器的进程平均运行时间

1. 题解

# Write your MySQL query statement below
select machine_id,round(sum(case when activity_type = 'end' then timestamp else -timestamp end)/ count(distinct process_id), 3) as processing_time
from activity
group by machine_id

a. 整体查询概览

首先，让我们看看整个 SQL 查询的结构：

SELECTmachine_id,ROUND(SUM(CASE WHEN activity_type = 'end' THEN timestamp ELSE -timestamp END) / COUNT(DISTINCT process_id), 3) AS processing_time
FROM activity
GROUP BY machine_id;

这个查询的目标是：

1. 从 activity 表中获取数据。
2. 按 machine_id 分组。
3. 计算每个机器的 processing_time，具体方法是对处理时间进行特定的计算和汇总。
4. 将计算结果进行四舍五入，保留三位小数。
5. 将结果命名为 processing_time，并显示 machine_id 和对应的 processing_time。

接下来，我们将逐步解释关键部分，特别是你关注的那一行：

SUM(CASE WHEN activity_type = 'end' THEN timestamp ELSE -timestamp END
) / COUNT(DISTINCT process_id)

b. 逐步解析

1. SUM(...)

SUM 是一个聚合函数，用于计算指定列或表达式的总和。在这里，SUM 计算的是 CASE 表达式的结果总和。

示例：
如果有三个数 2, 3, 5，那么 SUM(2, 3, 5) 的结果是 10。

2. CASE WHEN activity_type = 'end' THEN timestamp ELSE -timestamp END

这是一个 CASE 表达式，用于根据条件对每一行的数据进行不同的处理。

结构解释：

• CASE WHEN condition THEN result1 ELSE result2 END

这意味着：

• • 如果满足 condition 条件，则取 result1。
  • 否则，取 result2。

在你的查询中：

• WHEN activity_type = 'end' THEN timestamp

• • 如果 activity_type 的值是 'end'，则返回 timestamp 的值。

• ELSE -timestamp

• • 如果 activity_type 不是 'end'，则返回 -timestamp（timestamp 的相反数）。

举例说明：

假设有以下几行数据：

应用 CASE 表达式后：

• 第一行 (start, 1000) => -1000
• 第二行 (end, 1500) => 1500
• 第三行 (start, 2000) => -2000
• 第四行 (end, 2500) => 2500

这样，SUM(CASE…) 将计算为：(-1000) + 1500 + (-2000) + 2500 = 1000

3. / COUNT(DISTINCT process_id)

这部分是将上一步 SUM 的结果除以某个值。具体来说：

• COUNT(DISTINCT process_id) 是一个聚合函数，用于计算唯一的 process_id 的数量。

解释：

• COUNT(*) 会计算表中的所有行数。
• COUNT(column_name) 会计算该列中非 NULL 值的数量。
• COUNT(DISTINCT column_name) 会计算该列中唯一（不重复）的非 NULL 值的数量。

举例说明：

假设在一个分组中，process_id 的值为：[1, 2, 2, 3]

• COUNT(process_id) 会是 4，因为有 4 个非 NULL 值。
• COUNT(DISTINCT process_id) 会是 3，因为唯一的 process_id 是 1、2、3。

4. 结合起来理解整个表达式

现在，我们将整个表达式结合起来解释：

SUM(CASE WHEN activity_type = 'end' THEN timestamp ELSE -timestamp END
) / COUNT(DISTINCT process_id)

步骤解析：

1. CASE 表达式为每一行生成一个值：

• • 如果 activity_type 是 'end'，则使用该行的 timestamp。
  • 否则，使用该行的 -timestamp（负值）。

1. SUM(...) 将所有这些值相加：

• • 这实际上是在计算每个 machine_id 的总处理时间。
  • 例如，针对开始 (start) 和结束 (end) 活动，将结束时间减去开始时间，得到每个过程的处理时间。

1. COUNT(DISTINCT process_id) 计算每个 machine_id 下的唯一过程数量：

• • 这可能代表不同的任务、作业或操作。

1. 将总处理时间除以过程数量：

• • 这计算了每个 machine_id 的平均处理时间。

进一步解释：

• 为何用 -timestamp？

假设 activity 表记录了机器的活动日志，每个过程有一个 start 和一个 end 活动。timestamp 代表活动发生的时间点。

通过将 start 的 timestamp 设为负值，end 的 timestamp 设为正值，当你对它们求和时，就得到：

[
\text{总处理时间} = (\text{end}_1 + \text{end}_2 + \dots) - (\text{start}_1 + \text{start}_2 + \dots)
]

这相当于计算所有过程的结束时间总和减去开始时间总和，进而得到总的处理时间。

• 为何除以 COUNT(DISTINCT process_id)？

这一步计算的是每个过程的平均处理时间。通过将总处理时间除以过程的数量，你得到每个 machine_id 平均每个过程花费的时间。

用具体数据举例：

假设 activity 表中有以下数据：

对于 machine_id = 1:

• CASE 结果：

• • (start, 1000) => -1000
  • (end, 1500) => 1500
  • (start, 2000) => -2000
  • (end, 2500) => 2500

• SUM(CASE...): (-1000) + 1500 + (-2000) + 2500 = 1000
• COUNT(DISTINCT process_id): 2 (process_id 100 和 101)
• processing_time: 1000 / 2 = 500

对于 machine_id = 2:

• CASE 结果：

• • (start, 3000) => -3000
  • (end, 3500) => 3500

• SUM(CASE...): (-3000) + 3500 = 500
• COUNT(DISTINCT process_id): 1 (process_id 200)
• processing_time: 500 / 1 = 500

最后，ROUND(..., 3) 将结果四舍五入到小数点后三位，比如 500 会变成 500.000。

c. 小结

这个表达式的目的是：

1. 计算每个 machine_id 的总处理时间：

• • 将结束时间相加，开始时间相减，得到总的运行时间。

1. 计算每个 machine_id 的平均每个过程的处理时间：

• • 将总处理时间除以该机器完成的唯一过程数量。

1. 最终结果是每台机器平均每个过程的处理时间，保留三位小数。