Performing an operation within EACH GROUP of rows

Question

I have a table like below.

+------------+------------------+-------------------------------+-------------+
| day        | workflow_step_id | UNIX_TIMESTAMP(finished_time) | workflow_id |
+------------+------------------+-------------------------------+-------------+
| 2014-04-30 |                1 |                    1398852780 |           1 |
| 2014-04-30 |               17 |                    1398871213 |           2 |
| 2014-04-30 |                6 |                    1398872807 |           1 |
| 2014-04-30 |               22 |                    1398898676 |           2 |
| 2014-04-30 |               11 |                    1398898234 |           1 |
| 2014-04-30 |               16 |                    1398866813 |           2 |
| 2014-04-30 |                5 |                    1398869940 |           1 |
| 2014-04-30 |               21 |                    1398893419 |           2 |
| 2014-04-30 |               10 |                    1398894136 |           1 |
| 2014-04-30 |               15 |                    1398861385 |           2 |
| 2014-04-30 |                4 |                    1398860271 |           1 |
| 2014-04-30 |               20 |                    1398888252 |           2 |
| 2014-04-30 |                9 |                    1398886916 |           1 |
| 2014-04-30 |               14 |                    1398863922 |           2 |
| 2014-04-30 |                3 |                    1398865682 |           1 |
| 2014-04-30 |               19 |                    1398881994 |           2 |
| 2014-04-30 |                8 |                    1398882497 |           1 |
| 2014-04-30 |               13 |                    1398852785 |           2 |
| 2014-04-30 |                2 |                    1398856674 |           1 |
| 2014-04-30 |               18 |                    1398878836 |           2 |
| 2014-04-30 |                7 |                    1398878949 |           1 |
| 2014-04-30 |               12 |                    1398850920 |           2 |
| 2014-05-01 |               12 |                    1398932040 |           2 |
| 2014-05-01 |                1 |                    1398938880 |           1 |
| 2014-05-01 |               17 |                    1398957830 |           2 |
| 2014-05-01 |                6 |                    1398961385 |           1 |
| 2014-05-01 |               22 |                    1398996388 |           2 |
| 2014-05-01 |               11 |                    1398994543 |           1 |
| 2014-05-01 |               16 |                    1398946714 |           2 |
| 2014-05-01 |                5 |                    1398959182 |           1 |
| 2014-05-01 |               21 |                    1398989773 |           2 |
| 2014-05-01 |               10 |                    1398979568 |           1 |
| 2014-05-01 |               15 |                    1398949939 |           2 |
| 2014-05-01 |                4 |                    1398953732 |           1 |
| 2014-05-01 |               20 |                    1398980246 |           2 |
| 2014-05-01 |                9 |                    1398971281 |           1 |
| 2014-05-01 |               14 |                    1398940775 |           2 |
| 2014-05-01 |                3 |                    1398944207 |           1 |
| 2014-05-01 |               19 |                    1398970295 |           2 |
| 2014-05-01 |                8 |                    1398967778 |           1 |
| 2014-05-01 |               13 |                    1398940935 |           2 |
| 2014-05-01 |                2 |                    1398947433 |           1 |
| 2014-05-01 |               18 |                    1398966959 |           2 |
| 2014-05-01 |                7 |                    1398965931 |           1 |
-------------------------------------------------------------------------------

command:

SELECT day, 
       stats.workflow_step_id, 
       Max(Unix_timestamp(finished_time)) - Min(Unix_timestamp(finished_time)), 
       workflow_id 
FROM   modeling_dashboard_workflow_stats stats 
       INNER JOIN modeling_dashboard_workflow_step step 
               ON stats.workflow_step_id = step.workflow_step_id 
ORDER  BY day;

In this example only has two days and two workflow_ids, but it can have any number of days or workflow_ids.

I want to calculate the difference between maximum timestamp and minimum timestamp for each workflow_id for each day.

It should look something like this.

+------------+-------------------------------------------------------------+
| day        |                             MAX(timestamp) - MIN(timestamp) |
+------------+-------------------------------------------------------------+
| 2014-04-30 | difference bw max&min timestamp with workflow_id=1 in 04-30 |
| 2014-04-30 | difference bw max&min timestamp with workflow_id=2 in 04-30 |
| 2014-05-01 | difference bw max&min timestamp with workflow_id=1 in 05-01 |
| 2014-05-01 | difference bw max&min timestamp with workflow_id=2 in 05-01 |
----------------------------------------------------------------------------

How can I do this? Ideally it puts results for workflow_id = 1 and those for workflow_id = 2 in different columns, (day, MAX-MIN for id=1, MAX-MIN for id=2), but that's just my next step.

*Ideally it puts results for workflow_id = 1 and those for workflow_id = 2 in different columns* You can use `MAX(CASE workflow_id WHEN 1 THEN finished_time END) - MIN (CASE workflow_id WHEN 1 THEN finished_time END) AS workflow_id1, ...` for more see http://stackoverflow.com/questions/7674786/mysql-pivot-table — Conrad Frix, May 29 '14 at 21:04

score 1 · Answer 1 · answered May 29 '14 at 20:53

1

try this:

select day
   ,workflow_step_id
   ,MAX(UNIX_TIMESTAMP(finished_time)) - MIN(UNIX_TIMESTAMP(finished_time))
from modeling_dashboard_workflow_stats
group by day
   ,workflow_step_id

answered May 29 '14 at 20:53

Andreas

4,937
2
25
35

score 1 · Accepted Answer · answered May 29 '14 at 21:01

1

SELECT day, workflow_id
  , MAX(UNIX_TIMESTAMP(finished_time)) - MAX(UNIX_TIMESTAMP(finished_time))
FROM modeling_dashboard_workflow_stats
GROUP BY day, workflow_id

answered May 29 '14 at 21:01

Jaaz Cole

3,119
1
15
20

Performing an operation within EACH GROUP of rows

2 Answers2