mysql - 在SQL中计算每个测量值的成功百分比

Question

我有 3 个数据库表，示例数据如下

Meas_id - integer(Foreign keyed to Measurement.meas_id)    
Tool_id - integer(Foreign keyed to Events.Machine_id)    
Processdate- Timestamp with timezone (UTC)    
CreatedDate- Timestamp with timezone (UTC)

阅读

   Meas_id Tool_id    Status         Processdate
         1      13 Completed 2016-01-01 01:34:11
         1      28    Failed 2016-01-01 08:37:11
         1      54    Failed 2016-01-02 16:04:12
         1      32 Completed 2016-01-04 07:13:11
         1      39 Completed 2016-01-04 14:14:14
         1      12 Completed 2016-01-05 22:10:09
         1       9 Completed 2015-12-28 13:11:07
         1      17 Completed 2016-01-25 13:14:11
         1      27 Completed 2016-01-15 14:15:16
         1      31    Failed 2016-01-07 16:08:04
         2     113 Completed 2016-01-01 01:34:11
         2     128    Failed 2016-01-01 08:37:11
         2     154    Failed 2016-01-02 16:04:12
         2     132 Completed 2016-01-04 07:13:11
         2     139 Completed 2016-01-04 14:14:14
         2     112 Completed 2016-01-05 22:10:09
         2      90 Completed 2015-12-28 13:11:07
         2     117 Completed 2016-01-25 13:14:11
         2     127 Completed 2016-01-15 14:15:16
         2     131    Failed 2016-01-07 16:08:04

事件

   Meas_id Machine_id Event_Name         CreatedDate
         1         13    Success 2015-12-27 01:34:11
         1         17      Error 2015-12-27 08:37:11
         1         28    Success 2015-12-27 16:04:12
         1          9    Success 2015-12-28 07:13:11
         1         54    Success 2015-12-28 14:14:14
         1         31      Error 2015-12-28 22:10:09
         1         32    Success 2015-12-29 13:11:07
         1         39    Success 2015-12-29 13:14:11
         1         12    Success 2015-12-31 14:15:16
         1         27    Success 2016-01-01 16:08:04
         2        113    Success 2015-12-27 01:34:11
         2        117      Error 2015-12-27 08:37:11
         2        128    Success 2015-12-27 16:04:12
         2         90    Success 2015-12-28 07:13:11
         2        154    Success 2015-12-28 14:14:14
         2        131      Error 2015-12-28 22:10:09
         2        132    Success 2015-12-29 13:11:07
         2        139    Success 2015-12-29 13:14:11
         2        112    Success 2015-12-31 14:15:16
         2        127    Success 2016-01-01 16:08:04

测量

  Meas_id Meas_name
        1    Length
        2   Breadth

对于每个测量“长度”和“宽度”以及一周中的每一天，我尝试计算 2016 年第一周自创建日期起 168 小时内完成的所有工具/机器测量的成功百分比.

我想要的输出是

   Measurement DayofTheWeek PercentageSuccess
        Length            1                50
        Length            2                 0
        Length            3                 0
        Length            4               100
        Length            5               100
        Length            6                 0
        Length            7                 0
       Breadth            1                50
       Breadth            2                 0
       Breadth            3                 0
       Breadth            4               100
       Breadth            5               100
       Breadth            6                 0
       Breadth            7                 0

我尝试这样做，但肯定缺少一些逻辑并且它不起作用。

Select m.Meas_name, 
datepart(dd, Processdate) as DayofTheWeek,
(Count(m.Meas_name)* 100 / (Select Count(Event_Name) From Events where Event_Name = 'Success')) as PercentageSuccess

FROM Readings r JOIN
Measurements m
ON r.Meas_id = m.Meas_id
JOIN Events e
ON e.Meas_id = m.Meas_id
WHERE m.Meas_name IN ('Length', 'Breadth') AND
r.Status = 'Completed' AND
e.CreatedDate >= DATEADD(hh, -168, GETDATE())
GROUP BY m.Meas_name, datepart(dd, Processdate);

请提供有关实现该目标的优化方法的意见。

Answer 1

很好，我的正确答案被否决了，可能是因为我的答案不是很清楚，有点难以解释，所以这里是针对您的评论和否决者(我认为他们只是在报复)的编辑。

无论如何，您在有效时加入 3 个表会复制事件表中的数据。因此，您计算记录的方式总是会被夸大且不正确。您对百分比的计算也恰好是倒退的。

在连接上，您似乎缺少在连接中使用 Tool_id 。您可以尝试如下操作:

SELECT
    m.Meas_name
    ,DAYOFWEEK(r.ProcessDate) as DayOfTheWeek
    ,(COUNT(CASE WHEN e.Event_Name = 'Success' tHEN e.Meas_id END)/(COUNT(e.Meas_id) * 1.0)) * 100 as PercentageSuccess
FROM
    Measurements m
    INNER JOIN Events e
    ON m.Meas_id = e.Meas_id
    INNER JOIN Readings r
    ON e.Meas_id = r.Meas_id
    AND e.Machine_id = r.Tool_id
    AND r.Status = 'Completed'
    AND r.ProcessDate BETWEEN '2016-01-01' AND '2016-01-07'
WHERE
    m.Meas_name IN ('Length','Breadth')
GROUP BY
    m.Meas_name
    ,DAYOFWEEK(r.ProcessDate)

请注意，这是为 mysql 编写的，因为这是您帖子中标记的内容。如果您确实想要按照语法建议使用 sql-server，请告诉我。另外，我猜测您确实想按 processdate 进行过滤，但如果您想按 Event.CreateDate 进行过滤，则将其置于事件连接的 ON 条件中。