高级 SQL 语句编写

最近更新时间：2022-09-27 15:08:51
我的收藏
窗口函数的使用
环境准备
drop table if  exists bills ;
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()  返回行号，不分组
postgres=# select row_number() over(),* from bills limit 2;  
 row_number | id | goodsdesc | beginunit | begincity |       pubtime       | amount  
------------+----+-----------+-----------+-----------+---------------------+---------
          1 |  1 | 衣服      | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 1915.86
          2 |  2 | 建筑设备  | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
(2 rows)
postgres=# select row_number() over(),* from bills limit 2 offset 2; 
 row_number | id |       goodsdesc       | beginunit | begincity |       pubtime       | amount  
------------+----+-----------------------+-----------+-----------+---------------------+---------
          3 |  6 | 5 0铲车，后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
          4 |  8 | 旋挖附件38吨          | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
(2 rows)
5.3.1.3、row_number() --返回行号，按amount排序
postgres=# select row_number() over(order by amount),* from bills;
 row_number | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
          1 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
          2 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
          3 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
          4 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 1915.86
          5 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
          6 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
          7 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
          8 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
          9 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
         10 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
         11 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
(11 rows)
row_number() 返回行号，按 begincity 分组，pubtime 排序
postgres=# 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 | 1915.86
          1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
          2 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
          3 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
          1 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
          2 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
          3 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
          4 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
          5 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
          1 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
          2 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
(11 rows)
rank() 返回行号，对比值重复时行号重复并间断，即返回1,2,2,4...
postgres=# 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 | 1915.86
    1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
    2 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
    3 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
    1 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
    2 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
    3 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
    3 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
    5 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
    1 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
    2 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
(11 rows)
dance_rank() 返回行号，对比值重复时行号重复但不间断，即返回1,2,2,3...
postgres=# 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 | 1915.86
          1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
          2 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
          3 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
          1 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
          2 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
          3 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
          3 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
          4 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
          1 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
          2 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
(11 rows)
percent_rank() 从当前开始，计算在分组中的比例 (行号 - 1) * (1 / (总记录数 - 1))
postgres=# 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 | 1915.86
            0 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
          0.5 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
            1 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
            0 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
         0.25 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
          0.5 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
         0.75 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
            1 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
            0 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
            1 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
cume_dist()  返回行数除以记录数值
postgres=# 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 | 1915.86
      0.33 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
      0.67 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
      1.00 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
      0.20 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
      0.40 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
      0.60 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
      0.80 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
      1.00 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
      0.50 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
      1.00 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
ntile(分组数量) 让所有记录尽可以的均匀分布
postgres=# 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 | 1915.86
     1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
     1 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
     2 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
     1 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
     1 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
     1 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
     2 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
     2 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
     1 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
     2 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
﻿
postgres=# 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 | 1915.86
     1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
     2 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
     3 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
     1 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
     1 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
     2 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
     2 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
     3 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
     1 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
     2 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
lag(value any [, offset integer [, default any]] ) 返回偏移量值
offset integer 是偏移值，正数时取前值，负数时取后值，没有取到值时用 default 代替。
postgres=# 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 | 1915.86
         |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
 2022.31 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
 8771.11 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
         |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
  1030.9 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
 4182.68 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
 5365.04 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
 9621.37 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
         |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
 9886.15 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
﻿
postgres=# 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 | 1915.86
       0 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
       0 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
 2022.31 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
       0 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
       0 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
  1030.9 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
 4182.68 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
 5365.04 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
       0 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
       0 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
﻿
postgres=# 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 | 1915.86
 1316.27 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
       0 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
       0 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
 5365.04 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
 9621.37 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
  8290.5 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
       0 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
       0 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
       0 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
       0 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
lead(value any [,offset integer [, default any]] ) 返回偏移量值
offset integer 是偏移值，正数时取后值，负数时取前值，没有取到值时用 default 代替。
postgres=# 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 | 1915.86
 1316.27 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
         |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
         |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
 5365.04 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
 9621.37 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
  8290.5 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
         |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
         | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
         |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
         | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
﻿
postgres=# 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 | 1915.86
         |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
         |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
 2022.31 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
         |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
         |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
  1030.9 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
 4182.68 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
 5365.04 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
         |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
         | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
first_value(value any) 返回第一值
postgres=# select first_value(amount) over(partition by begincity order by  id),* from bills;
 first_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
-------------+----+------------------------+-----------+-----------+---------------------+---------
     1915.86 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 1915.86
     2022.31 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
     2022.31 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
     2022.31 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
      1030.9 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
      1030.9 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
      1030.9 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
      1030.9 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
      1030.9 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
     9886.15 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
     9886.15 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
last_value(value any) 返回最后值
postgres=# select last_value(amount) over(partition by begincity order by pubtime),* FROM bills;  
 last_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
    1915.86 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 1915.86
    2022.31 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
    8771.11 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
    1316.27 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
     1030.9 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
    4182.68 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
    9621.37 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
    9621.37 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
     8290.5 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
     971.54 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
    9886.15 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
(11 rows)
﻿
postgres=# select last_value(amount) over(partition by begincity),* FROM bills;
 last_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
    1915.86 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 1915.86
    1316.27 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
    1316.27 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
    1316.27 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
    9621.37 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
    9621.37 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
    9621.37 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
    9621.37 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
    9621.37 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
     971.54 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
     971.54 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
注意不要加上 order by id，默认情况下，带了 order by 参数会从分组的起始值开始一直叠加，直到当前值（不是当前记录）不同为止，当忽略 order by 参数则是整个分组。n下面通过修改分组的统计范围就可以实现 order by 参数取最后值。
postgres=# 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  
------------+----+------------------------+-----------+-----------+---------------------+---------
    1915.86 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 1915.86
    1316.27 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
    1316.27 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
    1316.27 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
     8290.5 |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
     8290.5 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
     8290.5 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
     8290.5 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
     8290.5 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
     971.54 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
     971.54 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
nth_value(value any, nth integer)：返回窗口框架中的指定值
postgres=# 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 | 1915.86
           |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
   8771.11 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
   8771.11 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 1316.27
           |  6 | 5 0铲车，后八轮翻斗车   | 河南省    | 三门峡市  | 2015-10-05 07:53:13 |  1030.9
   4182.68 |  7 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 4182.68
   4182.68 |  8 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 5365.04
   4182.68 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
   4182.68 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
           |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
    971.54 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(11 rows)
统计各个城市的总运费及平均每单的运费
postgres=# select sum(amount) over(partition by begincity),avg(amount) over(partition by begincity),begincity,amount from bills;
   sum    |       avg        | begincity | amount  
----------+------------------+-----------+---------
  1915.86 |          1915.86 | 三亚市    | 1915.86
 12109.69 | 4036.56333333333 | 三明市    | 2022.31
 12109.69 | 4036.56333333333 | 三明市    | 8771.11
 12109.69 | 4036.56333333333 | 三明市    | 1316.27
 28490.49 |         5698.098 | 三门峡市  | 4182.68
 28490.49 |         5698.098 | 三门峡市  |  8290.5
 28490.49 |         5698.098 | 三门峡市  |  1030.9
 28490.49 |         5698.098 | 三门峡市  | 5365.04
 28490.49 |         5698.098 | 三门峡市  | 9621.37
 10857.69 |         5428.845 | 上海市    | 9886.15
 10857.69 |         5428.845 | 上海市    |  971.54
(11 rows)
窗口函数别名使用
postgres=# select sum(amount) over w,avg(amount) over w,begincity,amount from bills window w as (partition by begincity);
   sum    |       avg        | begincity | amount  
----------+------------------+-----------+---------
  1915.86 |          1915.86 | 三亚市    | 1915.86
 12109.69 | 4036.56333333333 | 三明市    | 2022.31
 12109.69 | 4036.56333333333 | 三明市    | 8771.11
 12109.69 | 4036.56333333333 | 三明市    | 1316.27
 28490.49 |         5698.098 | 三门峡市  | 4182.68
 28490.49 |         5698.098 | 三门峡市  |  8290.5
 28490.49 |         5698.098 | 三门峡市  |  1030.9
 28490.49 |         5698.098 | 三门峡市  | 5365.04
 28490.49 |         5698.098 | 三门峡市  | 9621.37
 10857.69 |         5428.845 | 上海市    | 9886.15
 10857.69 |         5428.845 | 上海市    |  971.54
(11 rows)
获取每个城市运费前两名订单
postgres=# select * from (select row_number() over(partition by begincity order by amount desc),* from bills) where row_number<3;     
 row_number | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
          1 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 1915.86
          1 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 8771.11
          2 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 2022.31
          1 |  9 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9621.37
          2 | 10 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 |  8290.5
          1 |  5 | 普货40吨需13米半挂一辆  | 上海市    | 上海市    | 2015-10-05 08:13:59 | 9886.15
          2 | 11 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 |  971.54
(7 rows)
上一篇: 开发规范下一篇: 应用程序语法介绍