sql - 具有可变列的 Postgres 数据聚合

Question

我有一个包含时间日志信息的数据表。

create table "time_records" (
    "id" serial NOT NULL PRIMARY KEY,
    "start" timestamp not null,
    "end" timestamp not null,
    "duration" double precision not null,
    "project" varchar(255) not null,
    "case" integer not null,
    "title" text not null,
    "user" varchar(255) not null
);

这里有几行数据:

"id","start","end","duration","project","case","title","user"
"1","2014-02-01 11:54:00","2014-02-01 12:20:00","26.18","Project A","933","Something done here","John Smith"
"2","2014-02-02 12:34:00","2014-02-02 15:00:00","146","Project B","990","Something else done","Joshua Kehn"
"3","2014-02-02 17:57:00","2014-02-02 18:39:00","41.38","Project A","933","Another thing done","Bob Frank"
"4","2014-02-03 09:30:00","2014-02-03 11:41:00","131","Project A","983","iOS work","Joshua Kehn"
"5","2014-02-03 10:22:00","2014-02-03 13:29:00","187.7","Project C","966","Created views for things","Alice Swiss"

我可以从中提取点点滴滴的信息。例如，在两个日期之间记录时间的每个项目或在两个日期之间工作的每个人的列表。

我想要的是能够生成一份报告，其中包含日期，然后是顶部的每个项目以及该项目记录的总时间。

SELECT
    start::date,
    sum(duration / 60) as "time logged",
    project
FROM
    time_records
WHERE
    project = 'Project A'
GROUP BY
    start::date, project
ORDER BY
    start::date, project;

但是我想在输出中包含多个列，因此以某种方式将 select distinct project 与此组合。

最终输出是这样的:

date, project a total, project b total, project c total,
2014-02-01,0.5, 0.3, 10,
2014-02-02,1.3, 20, 3,
2014-02-03,20, 10, 10
...

我可以获得每个项目的总日期:

SELECT
    start::date,
    sum(duration / 60) as "time logged",
    project
FROM
    time_records
GROUP BY
    start::date, project
ORDER BY
    start::date, project;

但是我在每个项目的行中有多个日期。我需要它是一个日期，每个项目的总计在不同的行上。

仅使用 SQL 而不是在查询后编写一些代码，这是否有意义/可能？

Answer 1

对于“数据透视表”或交叉表，使用 crosstab() function of the additional module tablefunc .

表定义

给定这个没有 reserved SQL key words 的净化表定义作为标识符(这是一个很大的禁忌，即使你可以用双引号强制它):

CREATE TEMP TABLE time_records (
    id serial PRIMARY KEY,
    t_start timestamp not null,
    t_end timestamp not null,
    duration double precision not null,
    project text not null,
    t_case integer not null,
    title text not null,
    t_user text not null
);

查询

请注意我如何使用带有两个参数的变体来正确处理结果中缺失的项目。

SELECT *
FROM  crosstab (
   $$
   SELECT t_start::date
         , project
         , round(sum(duration / 60)::numeric, 2) AS time_logged
   FROM    time_records
   GROUP   BY 1,2
   ORDER   BY 1,2
   $$
  ,$$VALUES ('Project A'), ('Project B'),('Project C')$$
  ) AS t (
      t_start   date
    , project_a text
    , project_b text
    , project_c text
  );

结果:

t_start    | project_a | project_b | project_c
-----------|-----------|-----------|----------
2014-02-01 | 0.44      |           |
2014-02-02 | 0.69      | 2.43      |
2014-02-03 | 2.18      |           | 3.13

使用 Postgres 9.3 测试。

此相关答案中的解释、详细信息和链接:
PostgreSQL Crosstab Query