title: Query to perform operations between lines considering the state of a service
body: <p>I need a query to return the average value of three operations on the values of a service, the values must be grouped by service. The problem is that the return operations for each of the values are organized in the table in different lines related to the state in which the service is (started, running, ready). Values are in INT format and are representative only.</p>
  <p>Below is a sample of my MySQL data table.</p>
  <pre><code>
  +---------+------+---------------+------------+----------+----------+
  | service |  Id  |    State      |   value_1  |  value_2 | value_3  | 
  +---------+------+---------------+------------+----------+----------+
  |  Civil  |  01  |   started     |      12    |    16    |     28   |
  |  Civil  |  01  |   running     |      32    |    40    |     42   |
  |  Civil  |  01  |   ready       |      59    |    61    |     64   |
  |  Public |  02  |   started     |      02    |    11    |     12   |
  |  Public |  02  |   running     |      13    |    15    |     22   |
  |  Public |  02  |   ready       |      29    |    31    |     35   |
  | Military|  03  |   started     |      09    |    11    |     18   |
  | Military|  03  |   running     |      33    |    41    |     44   |
  | Military|  03  |   ready       |      49    |    72    |     74   |
  |  Public |  04  |   started     |      12    |    17    |     18   |
  |  Public |  04  |   running     |      22    |    23    |     41   |
  |  Public |  04  |   ready       |      51    |    53    |     54   |
  +---------+------+---------------+------------+----------+----------+
  
  </code></pre>
  <p>The operations I want to do, considering for example the 'Civil' service with Id equal to 01 is:</p>
  <ul>
  <li>(value_3 from the 'running' status line) - (value_1 from the 'started' status line), this would be: 42 - 12 = 30.</li>
  <li>(value_2 of the 'ready' state line) - (value_3 of the 'running' state line), this would be: 61 - 42 = 19.</li>
  <li>(value_1 from the 'ready' state line) - (value_3 from the 'started' state line), this would be: 59 - 28 = 31.</li>
  </ul>
  <p>These operations must be done for each set of Id.</p>
  <p>The results of the operations must be grouped by service name, returning the average value of the operations.</p>
  <pre><code>+---------+---------+---------+---------+
  | service | avg_op1 | avg_op2 | avg_op3 |      
  +---------+---------+---------+---------+
  |  Civil  |   30    |    19   |    31   |  
  |  Public |   24.5  |    10.5 |    25   | 
  | Military|   35    |    28   |    31   |
  +---------+---------+---------+---------+
  </code></pre>
  <p>My great difficulty is in making the operations between lines considering the states of the service for the same Id.</p>