lightdb/postgresql分析函数中不带参数OVER()选项的作用

　　有个开发写了不带参数的OVER()分析函数，类似如下：

SELECT categories_products.category_id,
  COUNT(*) OVER() AS all_products
FROM categories_products;

　　经查，其用途相当于不带GROUP BY子句的聚合操作，只不过所有行都包含该结果。分析函数本质性就像标量列的行为一样，可以和聚合函数一起使用。

SELECT DISTINCT
  categories.category_id,
  categories.name,
  COUNT(categories_products.product_id) OVER(PARTITION BY categories_products.category_id) AS products,
  COUNT(categories_products.product_id) OVER() AS all_products
FROM categories
INNER JOIN categories_products
ON categories.category_id = categories_products.category_id

　　就带PARTITION BY子句的分析函数而言，其性能通常远比单独计算好。那不带选项的OVER()呢？

findptdis=# explain analyze select sjno_id,count(1) over() from sjno_local1;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=0.00..142400.00 rows=640000 width=32) (actual time=779.573..995.977 rows=2560000 loops=1)
   ->  Seq Scan on sjno_local1  (cost=0.00..134400.00 rows=640000 width=24) (actual time=0.009..359.622 rows=2560000 loops=1)
 Planning Time: 0.047 ms
 Execution Time: 1055.498 ms
(4 rows)

findptdis=# explain analyze select sjno_id,x from sjno_local1,(select count(1) x from sjno_local1) f;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=136000.00..276800.02 rows=640000 width=32) (actual time=376.336..833.559 rows=2560000 loops=1)
   ->  Aggregate  (cost=136000.00..136000.01 rows=1 width=8) (actual time=376.325..376.327 rows=1 loops=1)
         ->  Seq Scan on sjno_local1 sjno_local1_1  (cost=0.00..134400.00 rows=640000 width=0) (actual time=0.006..302.627 rows=2560000 loops=1)
   ->  Seq Scan on sjno_local1  (cost=0.00..134400.00 rows=640000 width=24) (actual time=0.007..300.224 rows=2560000 loops=1)
 Planning Time: 0.105 ms
 Execution Time: 879.682 ms
(6 rows)

　　可见OVER()的性能是不如单独计算的。

　　https://www.mendelowski.com/docs/postgresql/aggregate-functions-over-partition/

 　　https://postgresql.itversity.com/mastering-sql-using-postgresql.html

　　http://blog.itpub.net/31490526/viewspace-2738607/