SQL查询:每月用户增长百分比

Question

我有这个简单的查询，计算每月的用户注册数量。

SELECT TO_CHAR(created_at, 'YYYY-MM') AS month, COUNT(user_id)
FROM users
GROUP BY month
ORDER BY month DESC 

我想要的是每个月与前一个月相比的百分比增长。

Answer 1

SQL Fiddle

select
    month, total,
    (total::float / lag(total) over (order by month) - 1) * 100 growth
from (
    select to_char(created_at, 'yyyy-mm') as month, count(user_id) total
    from users
    group by month
) s
order by month;
  month  | total |      growth      
---------+-------+------------------
 2013-01 |     2 |                 
 2013-02 |     3 |               50
 2013-03 |     5 | 66.6666666666667

Answer 2

SQL Fiddle

PostgreSQL 9.1.9架构安装

create table users (created_at date, user_id int);
insert into users select '20130101', 1;
insert into users select '20130102', 2;
insert into users select '20130203', 3;
insert into users select '20130204', 4;
insert into users select '20130201', 5;
insert into users select '20130302', 6;
insert into users select '20130303', 7;
insert into users select '20130302', 8;
insert into users select '20130303', 9;
insert into users select '20130303', 10;

查询1

select
  month,
  UserCount,
  100 - lag(UserCount) over (order by month asc)
        * 100.0 / UserCount Growth_Percentage
from
(
  SELECT TO_CHAR(created_at, 'YYYY-MM') AS month,
         COUNT(user_id) UserCount
  FROM users
  GROUP BY month
  ORDER BY month DESC
) sq

|   MONTH | USERCOUNT | GROWTH_PERCENTAGE |
-------------------------------------------
| 2013-01 |         2 |            (null) |
| 2013-02 |         3 |   33.333333333333 |
| 2013-03 |         5 |                40 |

Clodoaldo是对的，应该使用(Fiddle2)计算增长百分比

  100.0 * UserCount
        / lag(UserCount) over (order by month asc)
        - 100
        AS Growth_Percentage