集团通过isoweek - PostgreSQL的
问题描述:
这是一个基于前一个问题一个subquestion:集团通过isoweek - PostgreSQL的
Split values from an interval and group by isoweek - Postgresql
的问题是如何分组这个由isoweek
CREATE TABLE task
(id int4, start date, stop date, hr int4);
INSERT INTO task
(id, start, stop, hr)
VALUES
(1, '2017-01-01','2017-01-31', 80),
(2, '2017-01-01','2017-02-28', 120);
基于帕特里克答案我发现这解决方案:
SELECT id,to_char(iso, 'iyyy-iw'),(hr/weeks)::numeric (5,2) as hr_week
FROM (SELECT id,hr,generate_series(start,stop,interval '1 week') as iso,
(stop - start)/7 as weeks FROM task) as sub
http://sqlfiddle.com/#!15/93ee1/78
下一个步骤是“组群”是这样的:
2016-52 35
2017-01 35
2017-02 35
2017-03 35
2017-04 35
2017-05 15
2017-06 15
2017-07 15
2017-08 15
我无法弄清楚如何做到这一点。任何帮助赞赏。
TIA,
答
的PostgreSQL 9.3架构设置:
CREATE TABLE task
(id int4, start date, stop date, hr int4);
INSERT INTO task
(id, start, stop, hr)
VALUES
(1, '2017-01-01','2017-01-31', 80),
(2, '2017-01-01','2017-02-28', 120);
查询1:
SELECT
to_char(iso, 'iyyy-iw') as YYY_WK
, max(weeks) as weeks
, sum((hr/weeks)::numeric (5,2)) as hr_week
FROM (
SELECT
id
, hr
, generate_series(start,stop,interval '1 week') as iso
, (stop - start)/7 as weeks
FROM task
) as sub
group by
to_char(iso, 'iyyy-iw')
| yyy_wk | weeks | hr_week |
|---------|-------|---------|
| 2017-08 | 8 | 15 |
| 2017-06 | 8 | 15 |
| 2017-02 | 8 | 35 |
| 2017-03 | 8 | 35 |
| 2017-07 | 8 | 15 |
| 2016-52 | 8 | 35 |
| 2017-05 | 8 | 15 |
| 2017-01 | 8 | 35 |
| 2017-04 | 8 | 35 |
答
同时,我发现了另一个解决方案,基于CTE。然而,这不包括空的周,但也是一个解决方案。我会认为@已被用作更可靠的答案。
WITH list as (
SELECT id,to_char(iso, 'iyyy-iw'),(hr/weeks)::numeric (5,2) as hr_week
FROM (SELECT id,hr,generate_series(start,stop,interval '1 week') as iso,
(stop - start)/7 as weeks FROM task) as sub)
SELECT DISTINCT ON (week) week, sum(hr_week)
FROM list
GROUP BY 1
我希望这将涵盖空周为好。谢谢! – sibert