时间戳

首页 > 解决方案 > SQL Server：对部分为空的数据使用聚合

sql - SQL Server：对部分为空的数据使用聚合

问题描述

我遇到了一些需要帮助的代码问题。下面附上一张从 PowerApps 进入 SQL 数据库的表：


+-----------------------+-----------------+-------------+-----------+------------+------+-----------+
|          ID           | ProductionOrder | Workstation |  Status   |    Date    | Pass | TotalTime |
+-----------------------+-----------------+-------------+-----------+------------+------+-----------+
| 60127429_1000_1_S035  |          108100 | S035        | Completed | 05/13/2020 | No   |       121 |
| 60128523_1000_1_S120  |          884171 | S120        | Completed | 05/13/2020 | Yes  |        18 |
| 60128523_13000_1_S080 |          884172 | S080        | Completed | 05/13/2020 | No   |        26 |
| 60128523_13000_1_S090 |          884172 | S090        | Completed | 05/13/2020 | No   |        19 |
| 60128523_13000_1_S120 |          884172 | S120        | Completed | 05/13/2020 | No   |        16 |
| 60128523_28000_1_S070 |          884173 | S070        | Completed | 05/13/2020 | No   |        10 |
| 60128523_28000_1_S080 |          884173 | S080        | Completed | 05/13/2020 | No   |        16 |
| 60128523_28000_1_S090 |          884173 | S090        | Completed | 05/13/2020 | No   |        21 |
| 60128523_28000_1_S120 |          884173 | S120        | Completed | 05/13/2020 | No   |        17 |
| 60128523_47000_1_S080 |          884174 | S080        | Completed | 05/13/2020 | Yes  |        10 |
| 60128523_47000_1_S090 |          884174 | S090        | Completed | 05/13/2020 | Yes  |        50 |
| 60128523_47000_1_S120 |          884174 | S120        | Completed | 05/13/2020 | Yes  |        19 |
| 60128569_6000_1_S080  |          884698 | S080        | Completed | 05/13/2020 | Yes  |         9 |
| 60128569_6000_1_S090  |          884698 | S090        | Completed | 05/13/2020 | Yes  |        47 |
| 60128569_6000_1_S120  |          884698 | S120        | Completed | 05/13/2020 | Yes  |        54 |
| 60129071_1000_1_S070  |          105597 | S070        | Completed | 05/13/2020 | No   |       137 |
| 60129071_1000_1_S080  |          105597 | S080        | Completed | 05/13/2020 | No   |        12 |
| 60129071_1000_1_S090  |          105597 | S090        | Completed | 05/13/2020 | No   |        21 |
| 60129071_23000_1_S070 |          105598 | S070        | Completed | 05/13/2020 | Yes  |        50 |
| 60129071_23000_1_S080 |          105598 | S080        | Completed | 05/13/2020 | Yes  |        12 |
| 60129071_23000_1_S090 |          105598 | S090        | Completed | 05/13/2020 | Yes  |        29 |
| 60129071_23000_1_S120 |          105598 | S120        | Completed | 05/13/2020 | Yes  |        20 |
| 60129071_23025_1_S020 |          105600 | S020        | Completed | 05/13/2020 | Yes  |        21 |
| 60129151_2000_1_S090  |          104974 | S090        | Completed | 05/13/2020 | No   |        26 |
| 60129151_2000_1_S120  |          104974 | S120        | Completed | 05/13/2020 | No   |        24 |
+-----------------------+-----------------+-------------+-----------+------------+------+-----------+

接下来，我正在创建一个视图，通过对每个工作站进行分组来获取一些指标。这是该代码：

with cteNow AS 
(
    SELECT CASE WHEN 60 * DATEPART(HOUR, GETUTCDATE()) + DATEPART(MINUTE, GETUTCDATE()) > 60 * 21
            THEN CONVERT(float,(16 - 7) * 60) --minutes in (4 PM - 7 AM) * 60 minutes/hour
        ELSE
            CONVERT(float,60 * DATEPART(HOUR, GETUTCDATE()) + DATEPART(MINUTE, GETUTCDATE()) - ((7 + 5) * 60))
        END as MinutesToday
        , CONVERT(varchar(50), DATEADD(HOUR, -5, GETUTCDATE()), 101) as TargetDate 
)
SELECT dbs.Workstation, 

100.0 - ISNULL(SUM(CASE WHEN dbs.Pass = 'Yes' AND Status = 'Completed' THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN dbs.Status = 'Completed' THEN 1 ELSE 0 END), 0), 0) AS RFT

FROM [Booms DBS] AS dbs LEFT JOIN [Booms Delay Entry] AS del ON dbs.ID = del.ID INNER JOIN cteNow AS N ON N.TargetDate = dbs.[Date]
GROUP BY dbs.Workstation, MinutesToday

这样做的目的是测量每个站点的“第一次正确”。每次Pass列中出现“是”值时，都会对 RFT 造成打击。例如，如果给定站点中总共有 10 个构建，其中 5 个在Pass列中显示“是”，则为 50% RFT。

但是，这总是给我RFT列的“100”结果。我添加了所有这些NULLIF和ISNULL注意事项，因为有时该Pass列可能为空，并且在某些日子里，可能根本没有任何数据，尤其是在早上。我假设这就是问题所在。我没有正确计算这个吗？如果我不包括那些空检查功能，我想不出任何其他方法来不出错或得到除零错误。

提前致谢。

标签： sqlsql-servertsqlgroup-bypowerapps

解决方案

这样做的目的是测量每个站点的“第一次正确”。每次在 Pass 列中有一个“是”值时，它就是对 RFT 的一次命中。例如，如果给定站点中总共有 10 个构建，其中 5 个在 Pass 列中具有“是”，即 50% RFT。

看起来你想要条件聚合。我不明白您现有查询中日期算术的目的，但这会按照您的描述进行：

select 
    workstation, 
    avg(case when statys = 'Completed' and pass = 'Yes' then 1.0 else 0 end) rft_rate
from dbs.workstation
group by workstation

这为您提供了每条记录workstation，其数值介于 0 和 1 之间，表示'Yes'在 column中具有值的记录的比率pass。

推荐阅读