请教一个累计的问题。

Question

在网上查了下好多都是用oracle的分析函数做的，我用的postgresql不知有什么好方法么，最好能用一句SQL，如果总累计数不好一起算，那只计算月累计也可以。:) 谢谢！数据表：       uid        |         registtime         |         canceltime         
------------------+----------------------------+----------------------------
 12p5COeROiZKNN9l | 2006-09-27 10:27:36.574495 | 2006-09-26 20:34:03.672676 
 12bIzopGLk4166x6 | 2006-09-27 12:21:11.889179 |                            
 12o0rXKyGyHZTE6w | 2006-09-28 09:22:08.504122 |                            
 72VZMsqucS2PLgxP | 2006-09-28 11:17:23.490599 |                            
 110StyZjllirXhb2 | 2006-09-29 23:57:03.303488 | 2006-09-30 08:30:15.973476 
 32VRyyrnmrLHojkS | 2006-09-30 13:29:14.287157 | 2006-09-30 13:33:36.332643 
 12HbbSmARPEzG1Jk | 2006-09-30 19:36:36.569362 |                            
 72ZiztrUUsDiY3va | 2006-09-28 23:37:58.11501  | 2006-09-30 22:28:11.402219 
 1289DwXS042OtBxO | 2006-09-30 03:12:52.713608 | 2006-10-06 14:15:29.221077 
 12rXaGcJoLt2qe8S | 2006-09-29 23:40:26.208599 | 2006-10-08 21:44:12.135341结果：
      date   | reg | reg_mon | reg_total | can | can_mon | can_total                                                               
------------+-----+---------+-----------+-----+---------+-----------                                                              
 2006-09-27 |   2 |     2   |      2    |   1 |     1   |     1                                                                    
 2006-09-28 |   3 |     5   |      5    |   0 |     1   |     1                                                                    
 2006-09-29 |   2 |     7   |      7    |   0 |     1   |     1                                                                    
 2006-09-30 |   3 |    10   |     10    |   3 |     4   |     4                                                                    
 2006-10-01 |   0 |     0   |     10    |   1 |     1   |     5                                                                    
 2006-10-02 |   0 |     0   |      0    |   1 |     2   |     6  reg: 注册人数
reg_mon: 当日为止当月注册人数
reg_total: 累计注册人数
can: 注销人数
can_mon: 当日为止当月注销人数
can_total: 累计注销人数

Answer 1

不好意思前面数据表的例子里面数据写错了两个。uid | registtime | canceltime
------------------+----------------------------+----------------------------
12p5COeROiZKNN9l | 2006-09-27 10:27:36.574495 | 2006-09-27 20:34:03.672676
12bIzopGLk4166x6 | 2006-09-27 12:21:11.889179 |
12o0rXKyGyHZTE6w | 2006-09-28 09:22:08.504122 |
72VZMsqucS2PLgxP | 2006-09-28 11:17:23.490599 |
110StyZjllirXhb2 | 2006-09-29 23:57:03.303488 | 2006-09-30 08:30:15.973476
32VRyyrnmrLHojkS | 2006-09-30 13:29:14.287157 | 2006-09-30 13:33:36.332643
12HbbSmARPEzG1Jk | 2006-09-30 19:36:36.569362 |
72ZiztrUUsDiY3va | 2006-09-28 23:37:58.11501 | 2006-09-30 22:28:11.402219
1289DwXS042OtBxO | 2006-09-30 03:12:52.713608 | 2006-10-01 14:15:29.221077
12rXaGcJoLt2qe8S | 2006-09-29 23:40:26.208599 | 2006-10-02 21:44:12.135341

Answer 2

oracle有一個比較好用的函數rollup
應該能滿足你的要求