PostgreSQL:随时间变化的行数

Question

我有一个简单的 mySQL sql 脚本，它输出特定表随时间变化的行数(基于表中的日期时间字段)

SELECT concat('M-', s.label)
     , s.cnt
     , @tot := @tot + s.cnt  AS running_subtotal
  FROM ( SELECT DATE_FORMAT(t.created,'%y-%m') AS `label`
              , COUNT(t.id) AS cnt
           FROM `templates` t
          GROUP BY `label`
          ORDER BY `label`
       ) s
 CROSS
  JOIN ( SELECT @tot := 0 ) i

现在我想将其迁移到 PostgreSQL，但不知道如何将变量迁移到基于 pg 的语法。

内部语句当然没问题:

SELECT TO_CHAR(t.created,'YYYY-MM') AS label
              , COUNT(t.id) AS cnt
           FROM templates t
          GROUP BY label
          ORDER BY label

这里有人可以帮我解决可变部分吗？

这是一个包含数据的简单表格:

create TABLE "templates" (
    "id" bigserial,
    "title" varchar(2048) default NULL::character varying,
    "created" timestamp,
    PRIMARY KEY ("id")
);

insert into templates(title, created) values('test', '2011-03-01');
insert into templates(title, created) values('test 2', '2011-03-02');
insert into templates(title, created) values('test 3', '2011-03-03');
insert into templates(title, created) values('test 4', '2011-03-04');
insert into templates(title, created) values('test 5', '2011-03-05');
insert into templates(title, created) values('test 6', '2011-04-01');
insert into templates(title, created) values('test 7', '2011-04-02');
insert into templates(title, created) values('test 8', '2011-04-03');
insert into templates(title, created) values('test 9', '2011-04-04');
insert into templates(title, created) values('test 10', '2011-04-05');
… // 300 more for 2011-05

此查询的示例输出(基于带有“created”列的记录)是:

M-11-03:   5   5M-11-04:   5  10 (5 + 5)M-11-05: 300 310 (5 + 5 + 300)

(这是 Table statistics (aka row count) over time 的副产品)

Answer 1

这个有效:

select month, hm, sum(hm) over(order by month)
from(
select to_char(created, 'M-YYYY-MM') as month, count(*) as hm
from templates
group by 1
) x
order by month

http://www.sqlfiddle.com/#!15/eb08a/14