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           评论: 1   浏览: 15343   顶: 937  踩: 939 

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


评论:1   浏览: 15343                   顶: 937  踩: 939 

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

1# __ xiaowu 回答于 2024-04-22 09:32:29+08
党章学习心得:https://www.nanss.com/xuexi/2612.html 心灵鸡汤:https://www.nanss.com/yulu/2912.html 红岩读后感:https://www.nanss.com/xuexi/2609.html 让生活更美好:https://www.nanss.com/xuexi/2605.html 疫情作文800字最美逆行者:https://www.nanss.com/xuexi/2601.html 解除劳动合同证明:https://www.nanss.com/gongzuo/2652.html 辞职书模板:https://www.nanss.com/gongzuo/2658.html 入团申请书:https://www.nanss.com/xuexi/2664.html 家风作文:https://www.nanss.com/xuexi/2868.html 青春励志演讲稿:https://www.nanss.com/xuexi/2835.html 我的家乡作文:https://www.nanss.com/xuexi/2887.html 傲慢与偏见读后感:https://www.nanss.com/shenghuo/2886.html 长津湖观后感:https://www.nanss.com/xuexi/2796.html 形形色色的人作文:https://www.nanss.com/xuexi/2625.html 培训计划:https://www.nanss.com/gongzuo/2775.html 爱国故事:https://www.nanss.com/yuedu/2909.html 生活中的数学:https://www.nanss.com/shenghuo/2888.html 自我鉴定毕业生登记表:https://www.nanss.com/xuexi/2665.html 写人作文:https://www.nanss.com/xuexi/2876.html 关于成长的作文:https://www.nanss.com/xuexi/2743.html 学习心得体会:https://www.nanss.com/xuexi/2614.html 公务员年度考核总结:https://www.nanss.com/gongzuo/2573.html 党课学习心得:https://www.nanss.com/xuexi/2623.html 仿写繁星七一:https://www.nanss.com/xuexi/2098.html 爱情密语:https://www.nanss.com/yulu/2528.html 工作心得体会:https://www.nanss.com/gongzuo/2615.html 沙棘的功效与作用:https://www.nanss.com/yinshi/2929.html 比喻句:https://www.nanss.com/xuexi/2778.html 书籍是人类进步的阶梯:https://www.nanss.com/xuexi/2930.html 少先队员入队申请书:https://www.nanss.com/xuexi/2922.html



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