CODEWITHSUNDEEP
mysqlsql
rhh
rhh

Reputation: 47

Creating view in mysql for a group of rows

I have a mysql table with the following columns

1. id - int, auto increasing;
2. loc_id - int;
3. group_id - int;
4. target_date - int (epoch timestamp for each date at 00:00);
5. required - int;
6. actual - int;
7. updated_on;

Records in the table will be inserted in blocks, where the number of inserted rows is equal to the number of groups (referenced by group_id). The number of groups is 3, i.e. each transaction will insert 3 rows:

loc_id + target_date + updated_on will be the same for each of the 3 rows;

the group_id will be 1 to 3 for each row; required and actual will be specific for each row.

My question is: how to create a view so each block of 3 rows is represented as a single row in a table with columns as follows:

1. loc_id,
2. target_date, 
3. required_for_group1,
4. actual_for_group1, 
5. required_for_group2,
6. actual_for_group2, 
7. required_for_group3,
8. actual_for_group3 
9. updated_on

Many thanks in advance for your help!

With the example above is it possible to find which record is the latest for each loc_id/target_date if the cut-off point is 03:00AM for each target_date (which is always epoch at 00:00)? Something like an extra column to say "latest" for the entry "closest" to (target_date+10800). i tried adding a column "case when min((target_date+10800)-updated_on) then "latest" else 0 end", but did not quite make it work..

Upvotes: 0

Views: 121

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can do get the results you want using conditional aggregation:

select loc_id, target_date,
       max(case when group_id = 1 then required end) as required_1,
       max(case when group_id = 1 then actual end) as actual_1,
       max(case when group_id = 2 then required end) as required_2,
       max(case when group_id = 2 then actual end) as actual_2,
       max(case when group_id = 3 then required end) as required_3,
       max(case when group_id = 3 then actual end) as actual_3,
       updated_on
from table t
group by loc_id, target_date, updated_on;

You can put this statement into a view by putting create view <viewname> as before it.

Upvotes: 2

Related Questions