• 例题1：求机器运作平均时间

示例 1:

输入：

解释：
一共有3台机器,每台机器运行着两个进程.
机器 0 的平均耗时: ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
机器 1 的平均耗时: ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995
机器 2 的平均耗时: ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456

关于这道题，首先看求均值的公式：
机器 0 的平均耗时: ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
我们发现这个是最平凡的求均值，也就是除数是由元组行数决定，所以初步判断可以使用avg()来完成求解
其次，当使用GROUP BY时，比如对id分组，我们可以想象GROUP BY之后id合并为只有1个，并且所有相同id的元组的其他属性> 均写成数组形式，用逗号相隔。
avg()则是将一列下数据相加再处于GROUP BY之前，比如对id分组，相同id行数
所以这道题我们只需要注意(1.520 - 0.712)和(4.120 - 3.140)，将其转换为表达式q.timestamp - p.timestamp，再> 使用avg()即可

解答：

SELECT
    p.machine_id machine_id,
    round( avg( q.timestamp - p.timestamp ), 3 ) processing_time 
FROM
    Activity p
    INNER JOIN Activity q ON p.machine_id = q.machine_id 
    AND p.process_id = q.process_id 
    AND p.activity_type = 'start' 
    AND q.activity_type = 'end' 
GROUP BY
    machine_id;

总体思路是使用连接将两行start和end的数据合并为一条之后计算平均值
round计算小数点后三位
GROUP BY之后会为相同machine_id的值重新计算一个平均值

==========================================================================================

• 例题2:平均售价

Table: Prices

(product_id，start_date，end_date) 是 Prices 表的主键。
Prices 表的每一行表示的是某个产品在一段时期内的价格。
每个产品的对应时间段是不会重叠的，这也意味着同一个产品的价格时段不会出现交叉。

Table: UnitsSold

UnitsSold 表没有主键，它可能包含重复项。
UnitsSold 表的每一行表示的是每种产品的出售日期，单位和产品 id。

编写SQL查询以查找每种产品的平均售价。
average_price 应该四舍五入到小数点后两位。
查询结果格式如下例所示：

平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 5)+(15 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 15)+(30 30) )/ 230 = 16.96

这道题对于均值处理思路与上面例题稍微不同，这道题的除数并不是通过行数决定,不能使用avg()了
首先可以肯定，这道题肯定需要GROUP BY product_id，那么先模拟一下group by之后的场景：

再根据平均售价求值公式，可以写表达式round(sum(p.price * u.units) / sum(u.units),2)

代码如下：

SELECT 
    p.product_id
    ,round(sum(p.price * u.units) / sum(u.units),2) as average_price
FROM 
    Prices p
INNER JOIN UnitsSold u  
    ON p.product_id = u.product_id 
    AND datediff(p.end_date,u.purchase_date) >= 0
    AND datediff(u.purchase_date,p.start_date) >= 0
GROUP BY
    p.product_id;

==========================================================================================

• 例题3:确认率

表: Signups

User_id是该表的主键。
每一行都包含ID为user_id的用户的注册时间信息。

表: Confirmations

(user_id, time_stamp)是该表的主键。
user_id是一个引用到注册表的外键。
action是类型为('confirmed'， 'timeout')的ENUM
该表的每一行都表示ID为user_id的用户在time_stamp请求了一条确认消息，该确认消息要么被确认('confirmed')，要么被过期('timeout')。

用户的 确认率 是 'confirmed' 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。

编写一个SQL查询来查找每个用户的 确认率 。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例1:

输入：

解释:
用户 6 没有请求任何确认消息。确认率为 0。
用户 3 进行了 2 次请求，都超时了。确认率为 0。
用户 7 提出了 3 个请求，所有请求都得到了确认。确认率为 1。
用户 2 做了 2 个请求，其中一个被确认，另一个超时。确认率为 1 / 2 = 0.5。

这道题首先分析，除数是与GROUP BY 之前的行数有关的，于是先准备动用avg()方法
接着模拟group by之后的情况，比如说id为7，它分组之后的action就是[confirmed，confirmed，confirmed]
那么只需要将confirmed换为1(使用if)，再根据avg()的将每一列值相加再除以GROUP BY 之前的行数的特性，可以得出结果> > 了
还要记得使用round求小数点后两位

代码如下:

select s.user_id,round(avg(if(c.action = 'confirmed',1,0)),2) confirmation_rate 
from Signups s
left join
Confirmations c
on s.user_id=c.user_id
group by s.user_id;

现在，你还不会这些题目吗?