窗口函数的使用

环境准备

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)
) distribute by shard(id) to group default_group;

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()返回行号，不分组

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

teledb=# select row_number() over(),* from bills limit 2 offset 3;
 row_number | id |       goodsdesc       | beginunit | begincity |       pubtime       | amount  
------------+----+-----------------------+-----------+-----------+---------------------+---------
          3 |  5 | 5 0铲车，后八轮翻斗车 | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 6252.91
          4 |  6 | 鲜香菇2000斤          | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 1828.83
(2 rows)

row_number()--返回行号，按amount排序

teledb=# select row_number() over(order by amount),* from bills;
 row_number | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
          1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 |   35.93
          2 | 10 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 1715.46
          3 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 1926.81
          4 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 1988.18
          5 |  5 | 5 0铲车，后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 2787.06
          6 | 11 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 2807.08
          7 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 3346.33
          8 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 4714.07
          9 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 5238.68
         10 |  9 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 6888.82
         11 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 8370.24
(11 rows)

row_number()返回行号，按begincity分组，pubtime 排序

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

按begincity分组，按照pubtime进行排序，计算行号，每组的行号不中断

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

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

使用rank(), order by比较的pubtime相同的情况下，行号重复并且间断

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

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

使用dense_rank()，order by比较的pubtime相同的情况下，行号重复，但是下一个不中断

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

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

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

teledb=# 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  
-----------+----+---------------+----------+-----------+------------+---------
      0.20 |  5 | 5 0铲车，后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 6252.91
      0.40 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 1828.83
      0.60 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 |  9376.8
      0.80 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9885.95
      1.00 |  9 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 4971.79
      0.33 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 8195.98
      0.67 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6351.44
      1.00 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 7626.41
      1.00 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 3714.15
      0.50 | 10 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 1784.63
      1.00 | 11 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 1425.64
(11 rows)

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

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

调用ntile(k)，会将按照begincity分类后的记录，每一个类别平均分成k组，尽可能平均

lag(value any [, offset integer [, default any]] )返回偏移量值

offset integer是偏移值，正数时取前值，负数时取后值，没有取到值时用default代替

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

lag(amount, 1, null)，1为正数，取前一条记录的amount值。

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

取后面第二条记录amount的值。

lead(value any [,offset integer [, default any]] )返回偏移量值

与lag类似，offset integer是偏移值，但是正数时取后值，负数时取前值，没有取到值时用default代替。

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

first_value(value any) 返回第一值

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

返回每组的第一个值。

last_value(value any) 返回最后值

teledb=# select last_value(amount) over(partition by begincity order by pubtime),* from bills;
 last_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+----+-----------+-----------+---------------------+---------
    6252.91 |  5 | 5 0铲车，后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 6252.91
    1828.83 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 1828.83
     9376.8 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9885.95
     9376.8 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 |  9376.8
    4971.79 |  9 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 4971.79
    8195.98 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 8195.98
    6351.44 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6351.44
    7626.41 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 7626.41
    3714.15 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 3714.15
    1784.63 | 10 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 1784.63
    1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 1425.64
(11 rows)

teledb=# select last_value(amount) over(partition by begincity),* FROM bills;
 last_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
     9376.8 |  5 | 5 0铲车，后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 6252.91
     9376.8 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 1828.83
     9376.8 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9885.95
     9376.8 |  9 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 4971.79
     9376.8 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 |  9376.8
    8195.98 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6351.44
    8195.98 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 7626.41
    8195.98 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 8195.98
    3714.15 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 3714.15
    1425.64 | 10 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 1784.63
    1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 1425.64
(11 rows)
teledb=# select last_value(amount) over(partition by begincity order by pubtime),* from bills;
 last_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------------------------+-----------+-----------+---------------------+---------
    6252.91 |  5 | 5 0铲车，后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 6252.91
    1828.83 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 1828.83
     9376.8 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9885.95
     9376.8 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 |  9376.8
    4971.79 |  9 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 4971.79
    8195.98 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 8195.98
    6351.44 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6351.44
    7626.41 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 7626.41
    3714.15 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 3714.15
    1784.63 | 10 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 1784.63
    1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 1425.64
(11 rows)
teledb=# select last_value(amount) over(partition by begincity),* FROM bills;
 last_value | id |       goodsdesc        | beginunit | begincity |       pubtime       | amount  
------------+----+------+-----------+-----------+--------------+---------
     9376.8 |  5 | 5 0铲车，后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 6252.91
     9376.8 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 1828.83
     9376.8 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9885.95
     9376.8 |  9 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 4971.79
     9376.8 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 |  9376.8
    8195.98 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6351.44
    8195.98 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 7626.41
    8195.98 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 8195.98
    3714.15 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 3714.15
    1425.64 | 10 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 1784.63
    1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 1425.64
(11 rows)

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

teledb=# 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  
------------+----+------------------------+-----------+-----------+---------------------+---------
    4971.79 |  5 | 5 0铲车，后八轮翻斗车  | 河南省    | 三门峡市  | 2015-10-05 07:53:13 | 6252.91
    4971.79 |  6 | 鲜香菇2000斤           | 河南省    | 三门峡市  | 2015-10-05 10:38:29 | 1828.83
    4971.79 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 |  9376.8
    4971.79 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9885.95
    4971.79 |  9 | 旋挖附件39吨           | 河南省    | 三门峡市  | 2015-10-05 11:38:38 | 4971.79
    7626.41 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 8195.98
    7626.41 |  3 | 设备                   | 福建省    | 三明市    | 2015-10-05 11:21:54 | 6351.44
    7626.41 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 7626.41
    3714.15 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 3714.15
    1425.64 | 10 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 1784.63
    1425.64 | 11 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 1425.64
(11 rows)

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

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

返回每组的第2个amount值

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

teledb=# select sum(amount) over(partition by begincity),avg(amount) over(partition by begincity),begincity,amount from bills;
   sum    |       avg        | begincity | amount  
----------+------------------+-----------+---------
 32316.28 |         6463.256 | 三门峡市  | 6252.91
 32316.28 |         6463.256 | 三门峡市  | 1828.83
 32316.28 |         6463.256 | 三门峡市  | 9885.95
 32316.28 |         6463.256 | 三门峡市  | 4971.79
 32316.28 |         6463.256 | 三门峡市  |  9376.8
 22173.83 | 7391.27666666667 | 三明市    | 6351.44
 22173.83 | 7391.27666666667 | 三明市    | 7626.41
 22173.83 | 7391.27666666667 | 三明市    | 8195.98
  3714.15 |          3714.15 | 三亚市    | 3714.15
  3210.27 |         1605.135 | 上海市    | 1784.63
  3210.27 |         1605.135 | 上海市    | 1425.64
(11 rows)

窗口函数别名使用

teledb=# select sum(amount) over w,avg(amount) over w,begincity,amount from bills window w as (partition by begincity);
   sum    |       avg        | begincity | amount  
----------+------------------+-----------+---------
 32316.28 |         6463.256 | 三门峡市  | 6252.91
 32316.28 |         6463.256 | 三门峡市  | 1828.83
 32316.28 |         6463.256 | 三门峡市  | 9885.95
 32316.28 |         6463.256 | 三门峡市  | 4971.79
 32316.28 |         6463.256 | 三门峡市  |  9376.8
 22173.83 | 7391.27666666667 | 三明市    | 6351.44
 22173.83 | 7391.27666666667 | 三明市    | 7626.41
 22173.83 | 7391.27666666667 | 三明市    | 8195.98
  3714.15 |          3714.15 | 三亚市    | 3714.15
  3210.27 |         1605.135 | 上海市    | 1784.63
  3210.27 |         1605.135 | 上海市    | 1425.64
(11 rows)

获取每个城市运费前两名订单

teledb=# 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 |  8 | 旋挖附件35吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 | 9885.95
          2 |  7 | 旋挖附件38吨           | 河南省    | 三门峡市  | 2015-10-05 10:48:38 |  9376.8
          1 |  2 | 建筑设备               | 福建省    | 三明市    | 2015-10-05 07:21:22 | 8195.98
          2 |  4 | 普货                   | 福建省    | 三明市    | 2015-10-05 15:19:17 | 7626.41
          1 |  1 | 衣服                   | 海南省    | 三亚市    | 2015-10-05 09:32:01 | 3714.15
          1 | 10 | 设备                   | 上海市    | 上海市    | 2015-10-05 07:59:35 | 1784.63
          2 | 11 | 普货40吨需13米半挂一辆 | 上海市    | 上海市    | 2015-10-05 08:13:59 | 1425.64
(7 rows)

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

查看所有产品

分布式融合数据库HTAP

分布式融合数据库HTAP

窗口函数的使用

活动

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

查看所有产品

分布式融合数据库HTAP

分布式融合数据库HTAP

窗口函数的使用