选择每月注册用户数

Question

使用一个非常简单的表:

CREATE TABLE users (
  name VARCHAR(50) NOT NULL,
  registered TIMESTAMP NOT NULL,
  CONSTRAINT users_pk PRIMARY KEY (name)
);

如何选择每个月的用户注册数，例如

Jan 2010 - 19,
Feb 2010 - 0,
Mar 2010 - 7

Answer 1

要获得这些零条目，您需要跨generate_series:

select 
  to_char(gen_month, 'Mon YYYY'),
  count(name)
FROM generate_series(DATE '2010-01-01', DATE '2010-04-01', INTERVAL '1' MONTH) m(gen_month)
LEFT OUTER JOIN users 
ON (registered BETWEEN gen_month AND gen_month + INTERVAL '1' MONTH - INTERVAL '1' DAY)
GROUP BY gen_month;

您可以通过使用date_trunc使它更漂亮，但是您不能在registered上使用常规b-树索引:

select 
  to_char(gen_month, 'Mon YYYY'),
  count(name)
FROM generate_series(DATE '2010-01-01', DATE '2010-04-01', INTERVAL '1' MONTH) m(gen_month)
LEFT OUTER JOIN users 
ON ( date_trunc('month', registered) = date_trunc('month', gen_month) )
GROUP BY gen_month;

如果您想完全按照您所写的打印输出，可以将SELECT子句替换为:

SELECT format('%s - %s', to_char(gen_month, 'Mon YYYY'), count(name))

Answer 2

SELECT MONTH(registered),YEAR(registered),COUNT(name),
FROM users
GROUP BY YEAR(registered), MONTH(registered)

Answer 3

您可以按月使用此脚本。

select
   to_char(date_trunc('month', registered ), 'MM') as month, -- or hour, day, week, month, year
   count(1) 
from
   member 
where
   registered >= '2010-01-01' 
   AND registered <= '2010-12-31' 
group by
   month 
order by
   month asc;