这是我的问题的测试表:
CREATE TABLE document ( id integer NOT NULL, name character varying(120) NOT NULL, owner_id bigint DEFAULT 0 NOT NULL, doc_type_id smallint DEFAULT 1 NOT NULL, archived boolean DEFAULT false NOT NULL, insert_date timestamp without time zone DEFAULT now() NOT NULL, modify_date timestamp without time zone DEFAULT now() NOT NULL, last_writer_id bigint );
Modify_date确定有人编辑文档的最后时间.
为了进行一些统计,我需要在创建(insert_date)和modify_date之间获得时间.然后要显示一个条形图,我需要得到一个文档的计数,其中这个时间间隔是,例如,0到5天,6天和10天等.所以范围必须在查询中计算我猜.预期的结果(或......的种类)是:
Age Count 0-5 2 6-10 5 11-15 9 ... ...
当然,年龄可以是0-5 == 0,6-10 == 1的等级.我将准备数据以显示它们.
我发现一个帖子很相似,但我无法将它应用到我的案例中.(选择15分钟窗口的数据 - PostgreSQL)
谢谢你能带给我的任何答案.
编辑1:
范围需要从我从表中获得的最小和最大年龄动态生成.
with cte_ages as ( select extract(day from (modify_date - insert_date))::int as age from document ), cte_groups as ( select case when g.age = 1 then 0 else g.age end as gr_start, g.age + 4 as gr_end from generate_series(1, (select max(age) from cte_ages), 5) as g(age) ) select g.gr_start::text || '-' || g.gr_end::text, count(a.age) from cte_groups as g left outer join cte_ages as a on a.age between g.gr_start and g.gr_end group by g.gr_start, g.gr_end order by g.gr_start
sql fiddle demo