Postgresql events per hour

I need to calculate events for working hours with different groupings (person, type of event, etc.).

Here is a simplified diagram for my problem.

create table person (
  id        integer PRIMARY KEY,
  name      text
);

create table occupation (
  id        integer PRIMARY KEY,
  name      text,
  start_date    date,
  end_date  date,
  person_id     integer
);

create table work_shift (
  shift     integer PRIMARY KEY,
  start_date    date,
  end_date  date,
  hours     integer,
  occupation_id integer
);

create table event ( 
  id        integer PRIMARY KEY,
  type      text,
  event_date    date,
  person_id     integer
);

      

        
        
        
      

    

Sample data:

insert into person values (1, 'first person');
insert into person values (2, 'second person');

insert into occupation values (1, 'MUSICIAN', to_date('2014-01-01', 'YYYY-MM-DD'), to_date('2014-12-31', 'YYYY-MM-DD'), 1);
insert into occupation values (2, 'MUSICIAN', to_date('2014-01-01', 'YYYY-MM-DD'), to_date('2014-12-31', 'YYYY-MM-DD'), 2);


delete from work_shift;
insert into work_shift values (1, to_date('2014-01-01', 'YYYY-MM-DD'), to_date('2014-01-01', 'YYYY-MM-DD'), 7, 1);
insert into work_shift values (2, to_date('2014-01-02', 'YYYY-MM-DD'), to_date('2014-01-02', 'YYYY-MM-DD'), 8, 1);
insert into work_shift values (3, to_date('2014-01-01', 'YYYY-MM-DD'), to_date('2014-01-01', 'YYYY-MM-DD'), 8, 2);
insert into work_shift values (4, to_date('2014-01-02', 'YYYY-MM-DD'), to_date('2014-01-02', 'YYYY-MM-DD'), 7, 2);

-- person 1, playing, day 1
insert into event values (1, 'PLAYING', to_date('2014-01-01', 'YYYY-MM-DD'), 1);
insert into event values (2, 'PLAYING', to_date('2014-01-01', 'YYYY-MM-DD'), 1);
insert into event values (3, 'PLAYING', to_date('2014-01-01', 'YYYY-MM-DD'), 1);
insert into event values (4, 'PLAYING', to_date('2014-01-01', 'YYYY-MM-DD'), 1);
insert into event values (5, 'PLAYING', to_date('2014-01-01', 'YYYY-MM-DD'), 1);

-- person 1, singing, day 1
insert into event values (6, 'SINGING', to_date('2014-01-01', 'YYYY-MM-DD'), 1);
insert into event values (7, 'SINGING', to_date('2014-01-01', 'YYYY-MM-DD'), 1);

-- person 1, playing, day 2
insert into event values (8, 'PLAYING', to_date('2014-01-02', 'YYYY-MM-DD'), 1);
insert into event values (9, 'PLAYING', to_date('2014-01-02', 'YYYY-MM-DD'), 1);
insert into event values (10, 'PLAYING', to_date('2014-01-02', 'YYYY-MM-DD'), 1);
insert into event values (11, 'PLAYING', to_date('2014-01-02', 'YYYY-MM-DD'), 1);

-- person 2, playing, day 1
insert into event values (12, 'PLAYING', to_date('2014-01-01', 'YYYY-MM-DD'), 2);
insert into event values (13, 'PLAYING', to_date('2014-01-01', 'YYYY-MM-DD'), 2);
insert into event values (14, 'PLAYING', to_date('2014-01-01', 'YYYY-MM-DD'), 2);

-- person 2, singing, day 1
insert into event values (15, 'SINGING', to_date('2014-01-01', 'YYYY-MM-DD'), 2);
insert into event values (16, 'SINGING', to_date('2014-01-01', 'YYYY-MM-DD'), 2);
insert into event values (17, 'SINGING', to_date('2014-01-01', 'YYYY-MM-DD'), 2);

-- person 2, singing, day 2
insert into event values (18, 'SINGING', to_date('2014-01-02', 'YYYY-MM-DD'), 2);
insert into event values (19, 'SINGING', to_date('2014-01-02', 'YYYY-MM-DD'), 2);
insert into event values (20, 'SINGING', to_date('2014-01-02', 'YYYY-MM-DD'), 2);

      

        
        
        
      

    

How can I calculate, for example, events for hours per day? The problem I'm running into is that my calculations are summing up the same hours of work multiple times. So I get a job change with id 1 computed multiple times.

The required result when grouped with lesson and month will be

occupation    day   ratio
MUSICIAN      1     0,93    ((3+2+2+2+5) / (7+8))
MUSICIAN      1     0,73    ((4+7) / (7+8))

      

        
        
        
      

    

In another example, I need to calculate hours per person per day.

My basic query is currently in the form

SELECT 
  month,
  some-group-by-term,
  some-aggregate-function
FROM table
GROUP BY group-by-term

      

        
        
        
      

    

Is it possible to create queries like this by calculating these events / hours (grouped by some member)?