时间戳

首页 > 解决方案 > SQL 查询在行和列之间执行操作，按名称对结果进行分组

mysql - SQL 查询在行和列之间执行操作，按名称对结果进行分组

问题描述

我有一个 Mysql 数据库，其中包含一个用于存储来自已执行任务的数据的表。该表包含与任务状态（初始化、就绪、执行）相对应的日期。我需要执行允许我在这些日期获得结果的操作，问题是操作在不同的行和列之间。

这是我的数据表的示例。日期是 Unix TimeStamp (bigint) 中的值。由于时间是Unix（整数），下表我只放了一个说明性的数字（它们可以以相同的方式用于解决方案）

    +----------+-----+--------------+---------+---------+---------+
    | taskName |  Id |    State     |  Date1  |  Date2  |  Date3  |
    +----------+-----+--------------+---------+---------+---------+
    |   T10    |  1  | initializing |     1   |   2     |     3   |
    |   T10    |  1  |    ready     |     4   |   5     |     6   |  
    |   T10    |  1  |   executed   |     7   |   8     |     9   |
    |   T20    |  2  | initializing |     10  |   11    |     12  |
    |   T20    |  2  |    ready     |     13  |   14    |     15  | 
    |   T20    |  2  |   executed   |     16  |   17    |     18  |
    |   T10    |  3  | initializing |     72  |   74    |     76  |
    |   T10    |  3  |    ready     |     78  |   80    |     82  |  
    |   T10    |  3  |   executed   |     84  |   86    |     88  |
    |   T30    |  4  | initializing |     28  |   29    |     30  |
    |   T30    |  4  |    ready     |     31  |   32    |     33  | 
    |   T30    |  4  |   executed   |     34  |   35    |     36  |

                                     ... 
    +----------+-----+--------------+---------+---------+---------+

我需要在具有相同 ID 的任务日期之间以及按任务名称对结果进行分组之后执行操作。操作是（考虑具有相同 id 的任务）：

对于每个任务，对应相同的 id，必须执行这些操作，并且在结果之后必须将它们分组（执行结果的平均值）在包含相同名称的任务中。

对于示例表中的行，结果应为：

    +----------+-----------------+-----------------+------------------+
    | taskName |  avg_operation1 |  avg_operation2 |  avg_operation3  |
    +----------+-----------------+-----------------+------------------+
    |    T10   |         6       |         6       |         12       |
    |    T20   |         4       |         4       |          8       |
    |    T30   |         4       |         4       |          8       |
    +----------+-----------------+-----------------+------------------+

以我所拥有的知识，我想不出办法做到这一点。如果有人可以给我一些方法。

标签： mysqlsql

解决方案

试试这个 - 也不确定是否datediff会根据日期格式有所不同 请
记住以下几点：

Date1 = State_Reached  
Date2 = State_Loaded  
Date3 = State_Completed

询问

SELECT ID, Agg_Table.Task, AVG(Agg_Table.Op_1) as Avg_Op1, AVG(Agg_Table.Op_2) as Avg_Op2, AVG(Agg_Table.Op_3) as Avg_Op3
FROM
 (SELECT
   Task,
   DATEDIFF(IF(State='Ready', State_Loaded, NULL), IF(State='Initialized', State_Reached, NULL)) as Op_1,
   DATEDIFF(IF(State='Executed', State_Completed, NULL), IF(State='Ready', State_Loaded, NULL)) as Op_2,
   DATEDIFF(IF(State='Executed', State_Completed, NULL), IF(State='Initialized', State_Reached, NULL)) as Op_3
  FROM Table_Name 
 ) as Agg_Table
GROUP BY ID, Agg_Table.Task

同样对于未来，可能有助于规范化此表。祝你好运！

推荐阅读