PG中文社区 /
mdi-home
首页 社区新闻 中文文档 加入ACE {{ item.text }} 登录
mdi-home 首页 mdi-chat-processing 社区新闻 mdi-book-open-variant 中文文档 mdi-account-multiple-check 加入ACE mdi-file-multiple-outline 相关资料 mdi-blank {{item.text}} mdi-exit-to-app 退出账号
postgresql-窗口函数使用

原作者:阿弟  创作时间:2016-12-19 22:49:02+08  
doudou586 发布于2016-12-19 22:49:02           评论: 0   浏览: 14576   顶: 881  踩: 845 

postgresql-窗口函数使用

作者: 阿弟

联系: QQ:4893310 / Email:4893310@qq.com


背景

PostgreSQL作为世界上功能最强大的开源数据库,窗口函数作为业务过程中进行数据统计必不可少的功能,对PG自然是不在话下,PG内置了大量的窗口函数。

本文就是和大家一起在实际业务场景下如何使用窗口函数进行一探究竟。。。

一、os环境及postgresql版本

os环境

os:centos 6.4
[postgres@ test ~]$ uname -a
Linux db 2.6.32-358.el6.x86_64

[postgres@ test ~]$ gcc -v

gcc 版本 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC)

测试数据

create table bills 
(
  id serial not null,
  goodsdesc text not null,
  beginunit text not null,
  begincity text not null,
  pubtime timestamp not null,
  amount float8 not null default 0,
  primary key (id)
);

COMMENT ON TABLE bills is '运单记录';
COMMENT ON COLUMN bills.id IS 'id号';
COMMENT ON COLUMN bills.goodsdesc IS '货物名称';
COMMENT ON COLUMN bills.beginunit IS '启运省份';
COMMENT ON COLUMN bills.begincity IS '启运城市';
COMMENT ON COLUMN bills.pubtime IS '发布时间';
COMMENT ON COLUMN bills.amount IS '运费';

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'衣服','海南省','三亚市','2015-10-05 09:32:01',ROUND((random()*10000)::NUMERIC,2));

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'建筑设备','福建省','三明市','2015-10-05 07:21:22',ROUND((random()*10000)::NUMERIC,2)); 

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'设备','福建省','三明市','2015-10-05 11:21:54',ROUND((random()*10000)::NUMERIC,2));

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'普货','福建省','三明市','2015-10-05 15:19:17',ROUND((random()*10000)::NUMERIC,2));

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'5 0铲车,后八轮翻斗车','河南省','三门峡市','2015-10-05 07:53:13',ROUND((random()*10000)::NUMERIC,2));

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'鲜香菇2000斤','河南省','三门峡市','2015-10-05 10:38:29',ROUND((random()*10000)::NUMERIC,2));

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'旋挖附件38吨','河南省','三门峡市','2015-10-05 10:48:38',ROUND((random()*10000)::NUMERIC,2));

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'旋挖附件35吨','河南省','三门峡市','2015-10-05 10:48:38',ROUND((random()*10000)::NUMERIC,2));

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'旋挖附件39吨','河南省','三门峡市','2015-10-05 11:38:38',ROUND((random()*10000)::NUMERIC,2));

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'设备','上海市','上海市','2015-10-05 07:59:35',ROUND((random()*10000)::NUMERIC,2));

INSERT INTO bills(id,goodsdesc,beginunit,begincity,pubtime,amount) 
VALUES(default,'普货40吨需13米半挂一辆','上海市','上海市','2015-10-05 08:13:59',ROUND((random()*10000)::NUMERIC,2));

二、操作实践

row_number() --返回行号,对比值重复时行号不重复不间断,即返回1,2,3,4,5....,不返回1,2,2,4...

test=# select row_number() over(),* from bills limit 2;                                 
 row_number | id | goodsdesc | beginunit | begincity |       pubtime       | amount  
------------+----+-----------+-----------+-----------+---------------------+---------
          1 |  1 | 衣服      | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
          2 |  2 | 建筑设备  | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
(2 rows)

test=# select row_number() over(),* from bills limit 2 offset 2;       
 row_number | id | goodsdesc | beginunit | begincity |       pubtime       | amount  
------------+----+-----------+-----------+-----------+---------------------+---------
          3 |  3 | 设备      | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
          4 |  4 | 普货      | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
(2 rows)

--接amount排序

test=# select row_number() over(partition by tableoid order by amount),* from bills; 
 row_number | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
          1 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
          2 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
          3 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
          4 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
          5 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
          6 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
          7 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
          8 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
          9 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
         10 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
         11 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
(11 rows)

test=#

--按begincity分组,pubtime排序,注意红色记录行号不间断

test=# select row_number() over(partition by begincity order by pubtime),* from bills; 
 row_number | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
          1 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
          1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
          2 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
          3 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
          1 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
          2 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
          3 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
          4 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
          5 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
          1 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
          2 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=#

rank()--返回行号,对比值重复时行号重复并间断,即返回1,2,2,4...

test=# select rank() over(partition by begincity order by pubtime),* from bills;           
 rank | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------+----+------------------------+-----------+-----------+---------------------+---------
    1 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
    1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
    2 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
    3 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
    1 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
    2 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
    3 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
    3 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
    5 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
    1 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
    2 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=#

dance_rank()--返回行号,对比值重复时行号重复但不间断,即返回1,2,2,3...

test=# select dense_rank() over(partition by begincity order by pubtime),* from bills;    
 dense_rank | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
          1 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
          1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
          2 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
          3 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
          1 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
          2 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
          3 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
          3 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
          4 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
          1 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
          2 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=#

percent_rank()从当前开始,计算在分组中的比例 (行号-1)*(1/(总记录数-1))

test=# select percent_rank() over(partition by begincity order by id),* from bills;    
 percent_rank | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
--------------+----+------------------------+-----------+-----------+---------------------+---------
            0 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
            0 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
          0.5 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
            1 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
            0 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
         0.25 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
          0.5 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
         0.75 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
            1 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
            0 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
            1 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=# select percent_rank() over(partition by begincity order by pubtime),* from bills;      
 percent_rank | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
--------------+----+------------------------+-----------+-----------+---------------------+---------
            0 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
            0 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
          0.5 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
            1 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
            0 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
         0.25 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
          0.5 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
          0.5 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
            1 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
            0 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
            1 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

cume_dist() --返回行数除以记录数值

test=# select ROUND((cume_dist() over(partition by begincity order by id))::NUMERIC,2) AS cume_dist,* from bills;
 cume_dist | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
-----------+----+------------------------+-----------+-----------+---------------------+---------
      1.00 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
      0.33 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
      0.67 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
      1.00 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
      0.20 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
      0.40 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
      0.60 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
      0.80 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
      1.00 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
      0.50 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
      1.00 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=# 

ntile(分组数量)--让所有记录尽可以的均匀分布

test=# select ntile(3) over(partition by begincity order by id),* from bills;     
 ntile | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
-------+----+------------------------+-----------+-----------+---------------------+---------
     1 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
     1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
     2 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
     3 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
     1 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
     1 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
     2 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
     2 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
     3 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
     1 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
     2 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=# select ntile(2) over(partition by begincity order by id),* from bills;     
 ntile | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
-------+----+------------------------+-----------+-----------+---------------------+---------
     1 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
     1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
     1 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
     2 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
     1 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
     1 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
     1 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
     2 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
     2 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
     1 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
     2 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=#

lag(value any [, offset integer [, default any ]])

--返回偏移量值,offset integer是偏移值,正数时前值,负数时后值,没有取到值时用default代替

test=# select lag(amount,1,null) over(partition by begincity order by id),* from bills;  
   lag   | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
---------+----+------------------------+-----------+-----------+---------------------+---------
         |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
         |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
 9370.12 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
 6573.33 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
         |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
 2350.68 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
     549 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
 4089.25 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
 4766.76 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
         |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
 5094.08 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=# select lag(amount,1,0::float8) over(partition by begincity order by id),* from bills; 
   lag   | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
---------+----+------------------------+-----------+-----------+---------------------+---------
       0 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
       0 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
 9370.12 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
 6573.33 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
       0 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
 2350.68 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
     549 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
 4089.25 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
 4766.76 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
       0 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
 5094.08 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=# select lag(amount,2,0::float8) over(partition by begincity order by id),* from bills;  
   lag   | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
---------+----+------------------------+-----------+-----------+---------------------+---------
       0 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
       0 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
       0 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
 9370.12 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
       0 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
       0 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
 2350.68 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
     549 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
 4089.25 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
       0 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
       0 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=#  select lag(amount,-2,0::float8) over(partition by begincity order by id),* from bills; 
   lag   | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
---------+----+------------------------+-----------+-----------+---------------------+---------
       0 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
 1352.16 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
       0 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
       0 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
 4089.25 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
 4766.76 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
 7614.53 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
       0 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
       0 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
       0 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
       0 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

lead(value any [, offset integer [, default any ]])

--返回偏移量值,offset integer是偏移值,正数时取后值,负数时取前值,没有取到值时用default代替

test=#  select lead(amount,2,null) over(partition by begincity order by id),* from bills;
  lead   | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
---------+----+------------------------+-----------+-----------+---------------------+---------
         |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
 1352.16 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
         |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
         |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
 4089.25 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
 4766.76 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
 7614.53 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
         |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
         | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
         |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
         | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=#  select lead(amount,-2,null) over(partition by begincity order by id),* from bills;
  lead   | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
---------+----+------------------------+-----------+-----------+---------------------+---------
         |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
         |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
         |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
 9370.12 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
         |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
         |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
 2350.68 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
     549 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
 4089.25 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
         |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
         | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

first_value(value any)返回第一值

test=# select first_value(amount) over(partition by begincity order by  id),* from bills;
 first_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
-------------+----+------------------------+-----------+-----------+---------------------+---------
      1569.6 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
     9370.12 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
     9370.12 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
     9370.12 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
     2350.68 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
     2350.68 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
     2350.68 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
     2350.68 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
     2350.68 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
     5094.08 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
     5094.08 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=#

last_value(value any)返回最后值

test=# select last_value(amount) over(partition by begincity order by pubtime),* FROM bills;   
 last_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
     1569.6 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
    9370.12 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
    6573.33 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
    1352.16 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
    2350.68 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
        549 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
    4766.76 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
    4766.76 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
    7614.53 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
    5094.08 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
    5333.02 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=#

test=# select last_value(amount) over(partition by begincity),* FROM bills;            
 last_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
     1569.6 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
    9370.12 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
    9370.12 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
    9370.12 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
    4089.25 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
    4089.25 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
    4089.25 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
    4089.25 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
    4089.25 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
    5094.08 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
    5094.08 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
(11 rows)

注意不要加上order by id,默认情况下,带了order by 参数会从分组的起始值开始一直叠加,直到当前值(不是当前记录)不同为止,当忽略order by 参数则是整个分组。下面通过修改分组的统计范围就可以实现order by参数取最后值 

test=# select last_value(amount) over(partition by begincity order by id range between unbounded preceding and 
unbounded following),* FROM bills;
 last_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
     1569.6 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
    1352.16 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
    1352.16 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
    1352.16 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
    7614.53 |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
    7614.53 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
    7614.53 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
    7614.53 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
    7614.53 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
    5333.02 |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
    5333.02 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=#

nth_value(value any, nth integer):返回窗口框架中的指定值

test=# select nth_value(amount,2) over(partition by begincity order by id),* from bills;
 nth_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
-----------+----+------------------------+-----------+-----------+---------------------+---------
           |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 |  1569.6
           |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 9370.12
   6573.33 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6573.33
   6573.33 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1352.16
           |  5 | 5 0铲车,后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2350.68
       549 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 |     549
       549 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4089.25
       549 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 4766.76
       549 | 11 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 7614.53
           |  9 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 5094.08
   5333.02 | 10 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 5333.02
(11 rows)

test=#

统计各个城市的总运费及平均每单的运费

test=# select sum(amount) over(partition by begincity),avg(amount) over(partition by begincity),begincity,amount 
from bills;
   sum    |       avg        | begincity | amount  
----------+------------------+-----------+---------
   1569.6 |           1569.6 | 三亚市    |  1569.6
 17295.61 | 5765.20333333333 | 三明市    | 6573.33
 17295.61 | 5765.20333333333 | 三明市    | 1352.16
 17295.61 | 5765.20333333333 | 三明市    | 9370.12
 19370.22 |         3874.044 | 三门峡市  | 4766.76
 19370.22 |         3874.044 | 三门峡市  | 7614.53
 19370.22 |         3874.044 | 三门峡市  | 2350.68
 19370.22 |         3874.044 | 三门峡市  |     549
 19370.22 |         3874.044 | 三门峡市  | 4089.25
  10427.1 |          5213.55 | 上海市    | 5333.02
  10427.1 |          5213.55 | 上海市    | 5094.08
(11 rows)

test=#

窗口函数别名使用

test=# select sum(amount) over w,avg(amount) over w,begincity,amount from bills window w as (partition by begincity);
   sum    |       avg        | begincity | amount  
----------+------------------+-----------+---------
   1569.6 |           1569.6 | 三亚市    |  1569.6
 17295.61 | 5765.20333333333 | 三明市    | 6573.33
 17295.61 | 5765.20333333333 | 三明市    | 1352.16
 17295.61 | 5765.20333333333 | 三明市    | 9370.12
 19370.22 |         3874.044 | 三门峡市  | 4766.76
 19370.22 |         3874.044 | 三门峡市  | 7614.53
 19370.22 |         3874.044 | 三门峡市  | 2350.68
 19370.22 |         3874.044 | 三门峡市  |     549
 19370.22 |         3874.044 | 三门峡市  | 4089.25
  10427.1 |          5213.55 | 上海市    | 5333.02
  10427.1 |          5213.55 | 上海市    | 5094.08
(11 rows)

test=#

三、相关文档

pg_bot_banner.jpg


评论:0   浏览: 14576                   顶: 881  踩: 845 

请在登录后发表评论,否则无法保存。


发表评论:
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
© PostgreSQL中文社区 ... (自2010年起)