sql - 每小时 postgresql 事件

Question

我需要计算不同分组(人员、事件类型等)的每个工作时间的事件数。

这是我的问题的简化模式。

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
);

示例数据:

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);

例如，我将如何计算每天每小时的事件数？我遇到的问题是我的计算多次对相同的工作轮类时间求和。所以我多次计算了 id 1 的工作类次。

按职业和月份分组时的预期结果是

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

我需要计算的另一个例子是每人每天的小时数。

我的基本查询目前的形式是

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

是否可以创建类似于计算这些事件/小时(按某个术语分组)的查询？

Answer 1

我应该在这些情况下进行双重分组:

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