CODEWITHSUNDEEP
sqlpostgresql
eerok
eerok

Reputation: 33

postgresql events per hours

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

Here is a simplified schema 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 would I calculate for example events per hours per day? The problem I'm running into is that my calculations sum same work shift hours multiple times. So I get work shift with id 1 calculated multiple times.

Wanted result when grouped with occupation and month would be 

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

Other example I need to calculate is hours per person per day.

My base query currently is in form of 

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

Is it possible to create queries similar to this calculating those events / hours (grouped by some term)?

Upvotes: 3

Views: 225

Answers (1)

Jayvee
Jayvee

Reputation: 10873

I double grouping should work in these cases:

select occupation, day, sum(events)*1.0/sum(hours) ratio  from 
(select occupation,day,work_shift_id, sum(events) events
from person_work 
group by occupation, day, work_shift_id) a
join
person_work_shift b on a.work_shift_id=b.shift_id
group by occupation, day

Upvotes: 2

Related Questions