从多列值计算百分比

Question

我希望找到mysql查询，以便根据不同列的值计算完成工作的百分比，以前我使用countif在Microsoft中这样做，但现在我想在MySql上创建相同的表。

这是我现在的桌子

Parts Already Build : 
------------------------------------------------------------------
| id | Front   |  Rear   |  Left   | Right   |  Up     |  Down   |
__________________________________________________________________

| 01 | Done    |  Done   | Done    | Done    | Done    |  Done   |
------------------------------------------------------------------
| 02 | Done    |  Done   | Done    | Done    | No Need | No Need |
------------------------------------------------------------------
| 03 | Done    | Ongoing | Ongoing | Done    | Done    | Not Yet | 
------------------------------------------------------------------
| 04 | Not Yet | Not Yet | Not Yet | Not Yet | Not Yet | Not Yet |
------------------------------------------------------------------

预期结果

Parts Already Build : 
--------------------------------------------------------------------------------
| id | Front   |  Rear   |  Left   | Right   |  Up     |  Down   |   %   |  Status  |
________________________________________________________________________________

| 01 | Done    |  Done   | Done    | Done    | Done    |  Done   |  100  | Complete |
--------------------------------------------------------------------------------
| 02 | Done    |  Done   | Done    | Done    | No Need | No Need |  100  | Complete |
--------------------------------------------------------------------------------
| 03 | Done    | Ongoing | Ongoing | Done    | Done    | Not Yet |   50  | Ongoing  |
--------------------------------------------------------------------------------
| 04 | Not Yet | Not Yet | Not Yet | Not Yet | Not Yet | Not Yet |    0  | Not Yet  |
--------------------------------------------------------------------------------

注意：

1. 如果所有进程都已完成，或该6列中有6个“已完成”值，则%列= 100
2. 如果价值是“不需要”，它将被计算为“已完成”。
3. 如果不是所有进程都已完成/没有完成任何处理，则该表将只计算多少“已完成”或“不需要”值，并将其转换为%列中的百分比(3/6进程“已完成”= 50%)
4. 如果% is =100%，那么“状态”列=完全/ if =0% =还没有/如果有其他(1%-99% =正在进行)

当前查询：

SELECT
front,
rear,
left,
right,
up,
down,
_________________ AS %,
_________________ AS Status
FROM process

Answer 1

对于给定的表结构，需要将状态为Done或No Need的所有情况加起来，才能获得完整性值，然后可以用来生成%和status列：

SELECT `Front`, `Rear`, `Left`, `Right`, `Up`, `Down`,
        complete * 100 / 6 AS percent,
        CASE complete WHEN 6 THEN 'Complete'
                      WHEN 0 THEN 'Not Yet'
                      ELSE 'Ongoing'
        END AS status
FROM (SELECT *,
             0 + (Front = 'Done' OR Front = 'No Need')
               + (Rear  = 'Done' OR Rear = 'No Need')
               + (`Left`  = 'Done' OR `Left` = 'No Need')
               + (`Right` = 'Done' OR `Right` = 'No Need')
               + (Up    = 'Done' OR Up = 'No Need')
               + (Down  = 'Done' OR Down = 'No Need') AS complete
      FROM process) p

输出

Front       Rear        Left        Right       Up          Down        percent     status
Done        Done        Done        Done        Done        Done        100     Complete
Done        Done        Done        Done        No Need     No Need     100     Complete
Done        Ongoing     Ongoing     Done        Done        Not Yet     50      Ongoing
Not Yet     Not Yet     Not Yet     Not Yet     Not Yet     Not Yet     0       Not Yet

dbfiddle演示

Answer 2

老实说，您应该首先修复您的数据模型。与其为方向有六个不同的列，不如只有两个列，一个列表示方向，另一个列表示值。考虑一下您的数据的这个版本：

id | direction | status
1  | Front     | Done
1  | Rear      | Done
1  | Left      | Done
1  | Right     | Done
1  | Up        | Done
1  | Down      | Done
2  | Front     | Done
2  | Rear      | Done
2  | Left      | Done
2  | Right     | Done
2  | Up        | No Need
2  | Down      | No Need
...

使用这个规范化版本--您的数据--我们可以相当轻松地编写以下查询：

SELECT
    id,
    100.0 * AVG(status IN ('Done', 'No Need')) AS pct,
    CASE WHEN SUM(status NOT IN ('Done', 'No Need')) = 0 THEN 'Complete'
         WHEN SUM(status = 'Ongoing') > 0 THEN 'Ongoing'
         WHEN SUM(status = 'Not Yet') = COUNT(*) THEN 'Not Yet'
         ELSE 'Unknown' END AS status
FROM yourNewTable
GROUP BY
    id;

​

​

​

演示

​

Answer 3

你能试试：

SELECT front, rear, left_col, right_col, up, down,
FLOOR((IF(front = 'Done' OR front = 'No Need', 1, 0) + IF(rear= 'Done' OR rear = 'No Need', 1, 0) + IF(left_col = 'Done' OR left_col = 'No Need', 1, 0) + IF(right_col = 'Done' OR right_col = 'No Need', 1, 0) + IF(up = 'Done' OR up = 'No Need', 1, 0) + IF(down = 'Done' OR down = 'No Need', 1, 0))/6 * 100) AS Percent,
CASE WHEN FLOOR((IF(front = 'Done' OR front = 'No Need', 1, 0) + IF(rear= 'Done' OR rear = 'No Need', 1, 0) + IF(left_col = 'Done' OR left_col = 'No Need', 1, 0) + IF(right_col = 'Done' OR right_col = 'No Need', 1, 0) + IF(up = 'Done' OR up = 'No Need', 1, 0) + IF(down = 'Done' OR down = 'No Need', 1, 0))/6 * 100) = 100 THEN 'Complete'
     WHEN FLOOR((IF(front = 'Done' OR front = 'No Need', 1, 0) + IF(rear= 'Done' OR rear = 'No Need', 1, 0) + IF(left_col = 'Done' OR left_col = 'No Need', 1, 0) + IF(right_col = 'Done' OR right_col = 'No Need', 1, 0) + IF(up = 'Done' OR up = 'No Need', 1, 0) + IF(down = 'Done' OR down = 'No Need', 1, 0))/6 * 100) = 0 THEN 'Not Yet'
     ELSE 'Ongoing' END AS Status
     FROM Test

我是演示

​

​