SQL学习之开窗函数

博客翻译自https://mode.com/resources/sql-tutorial/sql-window-functions

 

  • 开窗函数

    从这儿开始吗？这篇是使用SQL进行数据分析教程的一部分，查看教程开始。这片文章使用的数据来自于华商顿特区的首都共享单车项目。这个项目的网站上会发布详细的骑行历史数据。数据是在2014年2月份下载的，但是这些数据仅是收集的2012年第一季度的数据。每一行代表一次骑行。大部分的字段是见文知意的，除了  rider_type  ：“Registered”代表共享单车项目的月会员，“Casual“代表骑行者购买了3天的会员。 start_dt  和  end_dt  经过清洗从原始格式转成SQL日期格式--他们以时间戳的格式储存在这张表中。

  • 开窗函数介绍 

    PostgreSQL的文件很好的解释了开窗函数的定义：

        开窗函数横跨表的行集进行计算，这些行和当前行存在某种关联。这种计算可以和聚合函数的计算进行对比。但是，不像聚合函数，开窗函数不会把许多行聚合成一行输出，而是每一行         都会获得一个结果。背后的原理是，开窗函数不止可以获取查询的结果的当前行，而是整个行集。

实际应用中最常见的例子就是求合计：

SELECT duration,
       SUM（duration) OVER(ORDER BY start_dt) AS running_total
FROM bike_trip

 

start_dt duration running_total
20120101 00:04 475 475
20120101 00:10 1145 2782
20120101 00:10 1162 2782
20120101 00:15 485 3738
20120101 00:15 471 3738
20120101 00:17 358 4096
20120101 00:18 1754 5850
20120101 00:22 259 6109
20120101 00:24 516 6625
20120101 00:25 913 7538
20120101 00:29 1097 8635
20120101 00:30 490 9125
20120101 00:32 1045 11205
20120101 00:32 1035 11205
20120101 00:33 1060 14063
20120101 00:33 1039 14063
20120101 00:33 443 14063
20120101 00:33 316 14063
20120101 00:34 506 14569

 

上面的查询没有用 GROUP BY 而创建了一个聚合行 running_total .让我们分解上面查询语句的语法看看它是如何运行的。

  • 开窗函数基础语法

    上面聚合函数的第一部分 SUM(duration) ，看起来跟一般聚合函数没什么区别。增加 over 标明他是一个开窗函数。你可以将上面的聚合函数这样理解“按照 start_dt 的顺序，从整个行集的首行开始到尾行对 duration 进行 sum() 运算。
如果你想将窗口范围从整个结果数据集收窄到数据集内的独立的组，你可以使用 PARTITION BY 去实现：

SELECT start_station,
       duration,
       SUM（duration) OVER(PARTITION  BY start_station ORDER BY start_dt) 
       AS running_total
FROM biketrip
WHERE start_dt < '2012-01-08'

 

start_station start_dt duration running_total
10th & Monroe St NE 20120101 13:01 933 933
10th & Monroe St NE 20120101 14:26 1032 1965
10th & Monroe St NE 20120101 16:41 1159 3124
10th & Monroe St NE 20120101 16:55 1138 4262
10th & Monroe St NE 20120101 21:14 670 4932
10th & Monroe St NE 20120102 12:19 1597 6529
10th & Monroe St NE 20120102 18:07 865 7394
10th & Monroe St NE 20120103 08:17 1517 8911
10th & Monroe St NE 20120103 08:26 1287 10198
10th & Monroe St NE 20120103 09:13 641 10839
10th & Monroe St NE 20120103 14:05 627 11466
10th & Monroe St NE 20120103 14:15 858 12324
10th & Monroe St NE 20120103 16:01 1225 13549
10th & Monroe St NE 20120104 08:38 1355 14904
10th & Monroe St NE 20120104 08:43 633 15537
10th & Monroe St NE 20120104 08:54 1393 16930
10th & Monroe St NE 20120104 15:49 5158 22088
10th & Monroe St NE 20120104 17:35 1266 23354
10th & Monroe St NE 20120105 01:14 1437 24791
10th & Monroe St NE 20120105 08:33 925 25716
10th & Monroe St NE 20120105 08:37 1379 27095
10th & Monroe St NE 20120105 08:52 1175 28270
10th & Monroe St NE 20120105 09:56 695 28965
10th & Monroe St NE 20120105 14:03 117 29082
10th & Monroe St NE 20120105 14:05 573 29655
10th & Monroe St NE 20120105 17:14 1099 30754
10th & Monroe St NE 20120106 08:22 1478 32232
10th & Monroe St NE 20120106 08:31 853 33085
10th & Monroe St NE 20120106 08:46 1230 34315
10th & Monroe St NE 20120106 08:58 898 35213
10th & Monroe St NE 20120106 09:12 768 35981
10th & Monroe St NE 20120106 11:06 1495 37476
10th & Monroe St NE 20120106 16:46 1226 38702
10th & Monroe St NE 20120106 17:34 1681 40383
10th & Monroe St NE 20120106 17:55 660 41043
10th & Monroe St NE 20120107 09:59 974 42017
10th & Monroe St NE 20120107 11:58 725 42742
10th & Monroe St NE 20120107 14:06 1111 43853
10th & Monroe St NE 20120107 14:08 1203 45056
10th & Monroe St NE 20120107 14:42 1573 46629
10th & Monroe St NE 20120107 14:58 328 46957
10th & Monroe St NE 20120107 15:08 4673 51630
10th & Monroe St NE 20120107 16:30 1625 58165
10th & Monroe St NE 20120107 16:30 1650 58165
10th & Monroe St NE 20120107 16:30 1630 58165
10th & Monroe St NE 20120107 16:30 1630 58165
10th & Monroe St NE 20120107 16:35 979 59144
10th & Monroe St NE 20120107 16:37 927 60071
10th & Monroe St NE 20120107 16:44 795 61964
10th & Monroe St NE 20120107 16:44 1098 61964
10th & Monroe St NE 20120107 18:55 1997 63961
10th & Monroe St NE 20120107 22:12 800 64761
10th & U St NW 20120101 00:57 817 817
10th & U St NW 20120101 01:25 1130 1947
10th & U St NW 20120101 02:26 605 2552
10th & U St NW 20120101 03:03 1839 4391
10th & U St NW 20120101 03:08 429 4820
10th & U St NW 20120101 03:09 273 5093
10th & U St NW 20120101 03:11 603 5696
10th & U St NW 20120101 03:14 1163 6859
10th & U St NW 20120101 03:25 337 7196
10th & U St NW 20120101 03:26 706 7902
10th & U St NW 20120101 03:42 618 8520
10th & U St NW 20120101 04:26 422 8942
10th & U St NW 20120101 12:15 485 9427
10th & U St NW 20120101 12:52 663 10751
10th & U St NW 20120101 12:52 661 10751
10th & U St NW 20120101 14:08 1086 12918
10th & U St NW 20120101 14:08 1081 12918
10th & U St NW 20120101 14:12 412 13330
10th & U St NW 20120101 15:03 606 13936
10th & U St NW 20120101 15:22 755 14691
10th & U St NW 20120101 15:31 629 15320
10th & U St NW 20120101 15:32 476 15796
10th & U St NW 20120101 15:50 512 16308
10th & U St NW 20120101 18:33 477 16785
10th & U St NW 20120101 19:29 399 17184
10th & U St NW 20120101 20:30 393 17577
10th & U St NW 20120101 22:01 521 18627
10th & U St NW 20120101 22:01 529 18627
10th & U St NW 20120101 22:07 335 18962
10th & U St NW 20120102 00:06 395 19357
10th & U St NW 20120102 00:27 548 20472
10th & U St NW 20120102 00:27 567 20472
10th & U St NW 20120102 00:52 299 20771
10th & U St NW 20120102 01:35 402 21173
10th & U St NW 20120102 11:44 232 21405
10th & U St NW 20120102 11:49 371 21776
10th & U St NW 20120102 12:12 1031 22807
10th & U St NW 20120102 13:16 628 23435
10th & U St NW 20120102 13:18 318 23753
10th & U St NW 20120102 13:53 740 24493
10th & U St NW 20120102 14:06 435 24928
10th & U St NW 20120102 14:36 417 25345
10th & U St NW 20120102 15:08 729 26074
10th & U St NW 20120102 15:12 1260 27334
10th & U St NW 20120102 15:16 646 27980
10th & U St NW 20120102 15:49 746 29430
10th & U St NW 20120102 15:49 704 29430
10th & U St NW 20120102 16:19 75 29505
10th & U St NW 20120102 16:21 395 29900
10th & U St NW 20120102 16:29 2559 32965
10th & U St NW 20120102 16:29 506 32965
10th & U St NW 20120102 16:36 443 33408
10th & U St NW 20120102 16:52 664 34072
10th & U St NW 20120102 17:19 323 34395
10th & U St NW 20120102 17:32 157 34552
10th & U St NW 20120102 17:35 280 34832
10th & U St NW 20120102 18:08 646 35478
10th & U St NW 20120102 19:31 401 35879
10th & U St NW 20120102 21:08 756 36635
10th & U St NW 20120102 22:15 515 37150
10th & U St NW 20120103 00:40 184 37334
10th & U St NW 20120103 06:43 746 38080
10th & U St NW 20120103 06:52 187 38267
10th & U St NW 20120103 08:09 1035 39302
10th & U St NW 20120103 08:12 581 39883
10th & U St NW 20120103 08:15 461 40344
10th & U St NW 20120103 08:19 789 41133
10th & U St NW 20120103 08:27 757 41890
10th & U St NW 20120103 08:38 933 42823
10th & U St NW 20120103 09:14 620 43443
10th & U St NW 20120103 09:17 619 44062
10th & U St NW 20120103 09:53 858 44920
10th & U St NW 20120103 10:03 421 45341
10th & U St NW 20120103 11:33 973 47299
10th & U St NW 20120103 11:33 985 47299
10th & U St NW 20120103 12:18 387 47686
10th & U St NW 20120103 13:03 521 48207
10th & U St NW 20120103 13:34 150 48357
10th & U St NW 20120103 13:56 312 48669
10th & U St NW 20120103 15:56 732 49401
10th & U St NW 20120103 16:26 365 49766
10th & U St NW 20120103 17:15 691 50457
10th & U St NW 20120103 18:15 382 50839
10th & U St NW 20120103 18:16 389 51228
10th & U St NW 20120103 18:32 384 51612
10th & U St NW 20120103 18:48 272 51884
10th & U St NW 20120103 19:06 610 52494
10th & U St NW 20120103 19:17 640 53134
10th & U St NW 20120103 20:13 358 53492
10th & U St NW 20120104 00:08 470 53962
10th & U St NW 20120104 06:44 734 54696
10th & U St NW 20120104 07:07 831 55527
10th & U St NW 20120104 07:53 535 56062
10th & U St NW 20120104 08:10 469 56531
10th & U St NW 20120104 08:16 688 57219
10th & U St NW 20120104 08:43 639 57858
10th & U St NW 20120104 09:03 365 58223
10th & U St NW 20120104 09:04 571 58794
10th & U St NW 20120104 09:17 1131 59925
10th & U St NW 20120104 09:29 318 60243
10th & U St NW 20120104 09:38 986 61229
10th & U St NW 20120104 12:03 592 61821
10th & U St NW 20120104 15:37 403 62224
10th & U St NW 20120104 16:34 599 62823
10th & U St NW 20120104 17:22 680 63503
10th & U St NW 20120104 17:33 1181 64684
10th & U St NW 20120104 17:35 328 65012
10th & U St NW 20120104 18:36 697 66046
10th & U St NW 20120104 18:36 337 66046
10th & U St NW 20120104 18:58 499 66545
10th & U St NW 20120104 19:31 373 66918
10th & U St NW 20120104 19:56 349 67267
10th & U St NW 20120104 20:14 301 67568
10th & U St NW 20120104 20:18 336 67904
10th & U St NW 20120104 20:59 304 68208
10th & U St NW 20120104 21:42 446 68654
10th & U St NW 20120104 23:12 190 68844
10th & U St NW 20120104 23:46 401 69245
10th & U St NW 20120105 01:25 512 69757
10th & U St NW 20120105 06:41 474 70231
10th & U St NW 20120105 06:44 785 71016
10th & U St NW 20120105 06:50 215 71231
10th & U St NW 20120105 07:36 719 71950
10th & U St NW 20120105 08:01 528 72478
10th & U St NW 20120105 08:11 467 73380
10th & U St NW 20120105 08:11 435 73380
10th & U St NW 20120105 08:21 314 73694
10th & U St NW 20120105 08:24 720 74414
10th & U St NW 20120105 08:32 775 75189
10th & U St NW 20120105 08:35 277 75466
10th & U St NW 20120105 08:57 524 75990
10th & U St NW 20120105 09:11 282 76272
10th & U St NW 20120105 09:23 1075 77347
10th & U St NW 20120105 10:15 469 77816
10th & U St NW 20120105 10:20 403 78219
10th & U St NW 20120105 11:41 474 78693
10th & U St NW 20120105 14:21 427 79120
10th & U St NW 20120105 15:15 308 79428
10th & U St NW 20120105 16:45 347 79775
10th & U St NW 20120105 17:17 823 80598
10th & U St NW 20120105 17:22 616 81214
10th & U St NW 20120105 17:33 576 81790
10th & U St NW 20120105 18:07 355 82145
10th & U St NW 20120105 18:28 301 82446
10th & U St NW 20120105 18:30 998 83444
10th & U St NW 20120105 18:38 348 83792
10th & U St NW 20120105 19:02 1173 84965
10th & U St NW 20120105 19:11 1154 86119
10th & U St NW 20120105 19:16 601 86720
10th & U St NW 20120105 19:26 377 87097
10th & U St NW 20120105 19:42 215 87312
10th & U St NW 20120105 19:54 813 88923
10th & U St NW 20120105 19:54 798 88923
10th & U St NW 20120105 19:55 752 89675
10th & U St NW 20120105 21:37 1048 90723
10th & U St NW 20120106 00:46 453 91622
10th & U St NW 20120106 00:46 446 91622
10th & U St NW 20120106 06:43 341 91963
10th & U St NW 20120106 06:45 680 92643
10th & U St NW 20120106 07:39 597 93240
10th & U St NW 20120106 07:48 221 93461
10th & U St NW 20120106 08:13 463 93924
10th & U St NW 20120106 08:14 310 94234
10th & U St NW 20120106 08:24 1520 95754
10th & U St NW 20120106 08:32 415 96169
10th & U St NW 20120106 08:44 801 96970
10th & U St NW 20120106 08:46 722 97692
10th & U St NW 20120106 08:49 292 97984
10th & U St NW 20120106 08:51 380 98874
10th & U St NW 20120106 08:51 510 98874
10th & U St NW 20120106 09:05 415 99289
10th & U St NW 20120106 09:07 843 100132
10th & U St NW 20120106 09:17 691 100823
10th & U St NW 20120106 09:20 4668 105491
10th & U St NW 20120106 09:22 947 106438
10th & U St NW 20120106 09:25 578 107016
10th & U St NW 20120106 09:26 504 107520
10th & U St NW 20120106 09:55 951 108471
10th & U St NW 20120106 10:35 846 109317
10th & U St NW 20120106 11:17 379 109696
10th & U St NW 20120106 11:53 336 110032
10th & U St NW 20120106 12:01 582 110614
10th & U St NW 20120106 14:58 758 111372
10th & U St NW 20120106 15:23 423 111795
10th & U St NW 20120106 15:37 903 112698
10th & U St NW 20120106 15:42 303 113156
10th & U St NW 20120106 15:42 155 113156
10th & U St NW 20120106 16:16 3653 116809
10th & U St NW 20120106 16:17 3636 120445
10th & U St NW 20120106 16:43 491 120936
10th & U St NW 20120106 17:15 1398 122334
10th & U St NW 20120106 17:19 450 123111
10th & U St NW 20120106 17:19 327 123111
10th & U St NW 20120106 17:42 377 123488
10th & U St NW 20120106 17:48 489 123977
10th & U St NW 20120106 17:49 476 124453
10th & U St NW 20120106 17:58 510 124963
10th & U St NW 20120106 18:08 398 125361
10th & U St NW 20120106 18:16 1222 126583
10th & U St NW 20120106 18:21 761 128120
10th & U St NW 20120106 18:21 776 128120
10th & U St NW 20120106 18:30 859 128979
10th & U St NW 20120106 18:37 450 129429
10th & U St NW 20120106 18:55 156 129585
10th & U St NW 20120106 19:09 380 129965
10th & U St NW 20120106 20:01 335 130300
10th & U St NW 20120106 20:14 338 130638
10th & U St NW 20120106 20:18 587 131225
10th & U St NW 20120106 20:39 1272 132497
10th & U St NW 20120106 21:27 417 132914
10th & U St NW 20120106 21:28 254 133168
10th & U St NW 20120106 21:29 627 133795
10th & U St NW 20120106 21:42 550 134345
10th & U St NW 20120106 21:54 193 134538
10th & U St NW 20120106 21:57 237 134775
10th & U St NW 20120106 22:02 451 135702
10th & U St NW 20120106 22:02 476 135702
10th & U St NW 20120106 22:54 605 136307
10th & U St NW 20120106 23:09 429 137162
10th & U St NW 20120106 23:09 426 137162
10th & U St NW 20120106 23:10 1143 138305
10th & U St NW 20120106 23:52 878 139183
10th & U St NW 20120107 00:10 313 139496
10th & U St NW 20120107 00:45 429 140364
10th & U St NW 20120107 00:45 439 140364
10th & U St NW 20120107 01:13 177 140541
10th & U St NW 20120107 01:30 227 140993
10th & U St NW 20120107 01:30 225 140993
10th & U St NW 20120107 01:53 297 141290
10th & U St NW 20120107 01:59 170 141460
10th & U St NW 20120107 02:05 576 142036
10th & U St NW 20120107 02:14 222 142258
10th & U St NW 20120107 02:54 976 143234
10th & U St NW 20120107 04:07 827 144061
10th & U St NW 20120107 08:28 637 144698
10th & U St NW 20120107 08:33 308 145006
10th & U St NW 20120107 08:52 144 145150
10th & U St NW 20120107 09:01 574 145724
10th & U St NW 20120107 09:16 160 145884
10th & U St NW 20120107 09:31 1096 146980
10th & U St NW 20120107 11:08 308 147568
10th & U St NW 20120107 11:08 280 147568
10th & U St NW 20120107 11:18 290 147858
10th & U St NW 20120107 11:21 354 148212
10th & U St NW 20120107 11:29 733 148945
10th & U St NW 20120107 11:50 540 149485
10th & U St NW 20120107 12:06 1025 151540
10th & U St NW 20120107 12:06 1030 151540
10th & U St NW 20120107 12:13 499 152039
10th & U St NW 20120107 12:28 716 152755
10th & U St NW 20120107 12:31 1116 155036
10th & U St NW 20120107 12:31 1165 155036
10th & U St NW 20120107 13:15 328 155364
10th & U St NW 20120107 13:44 7978 171326
10th & U St NW 20120107 13:44 7984 171326
10th & U St NW 20120107 13:58 638 171964
10th & U St NW 20120107 14:08 428 172392
10th & U St NW 20120107 14:16 3536 175928
10th & U St NW 20120107 14:56 473 176401
10th & U St NW 20120107 15:21 639 177040
10th & U St NW 20120107 15:40 490 178020
10th & U St NW 20120107 15:40 490 178020
10th & U St NW 20120107 15:53 297 178317
10th & U St NW 20120107 16:08 985 179302
10th & U St NW 20120107 16:19 3280 182582
10th & U St NW 20120107 16:21 357 182939
10th & U St NW 20120107 16:52 499 184003
10th & U St NW 20120107 16:52 565 184003
10th & U St NW 20120107 17:07 571 184574
10th & U St NW 20120107 17:14 811 185385
10th & U St NW 20120107 17:34 535 186465
10th & U St NW 20120107 17:34 545 186465
10th & U St NW 20120107 17:37 874 187339
10th & U St NW 20120107 18:05 534 188771
10th & U St NW 20120107 18:05 898 188771
10th & U St NW 20120107 18:09 420 189191
10th & U St NW 20120107 18:32 133 189324
10th & U St NW 20120107 18:33 216 189540
10th & U St NW 20120107 19:12 209 189936
10th & U St NW 20120107 19:12 187 189936
10th & U St NW 20120107 19:44 679 190615
10th & U St NW 20120107 20:39 917 191532
10th & U St NW 20120107 21:25 303 191835
10th & U St NW 20120107 21:28 596 192431
10th & U St NW 20120107 21:29 561 192992
10th & U St NW 20120107 22:06 428 193420
10th & U St NW 20120107 22:32 787 194207
10th & U St NW 20120107 22:38 227 194434
10th & U St NW 20120107 22:51 523 194957
10th & U St NW 20120107 23:29 615 195572
10th & U St NW 20120107 23:43 646 196218
10th St & Constitution Ave NW 20120101 13:41 1383 2775
10th St & Constitution Ave NW 20120101 13:41 1392 2775
10th St & Constitution Ave NW 20120101 13:49 2061 4836
10th St & Constitution Ave NW 20120101 13:50 2008 6844
10th St & Constitution Ave NW 20120101 13:57 1389 8233
10th St & Constitution Ave NW 20120101 14:08 1395 9628
10th St & Constitution Ave NW 20120101 14:10 620 10248
10th St & Constitution Ave NW 20120101 14:11 1810 13885
10th St & Constitution Ave NW 20120101 14:11 1827 13885
10th St & Constitution Ave NW 20120101 15:02 415 14300
10th St & Constitution Ave NW 20120101 15:41 844 15144
10th St & Constitution Ave NW 20120101 16:19 620 15764
10th St & Constitution Ave NW 20120101 16:25 686 16450
10th St & Constitution Ave NW 20120101 16:33 1057 18540
10th St & Constitution Ave NW 20120101 16:33 1033 18540
10th St & Constitution Ave NW 20120101 16:36 784 20125
10th St & Constitution Ave NW 20120101 16:36 801 20125
10th St & Constitution Ave NW 20120101 16:40 715 20840
10th St & Constitution Ave NW 20120101 17:23 1366 22206
10th St & Constitution Ave NW 20120101 17:42 815 23981
10th St & Constitution Ave NW 20120101 17:42 960 23981
10th St & Constitution Ave NW 20120101 17:43 774 24755
10th St & Constitution Ave NW 20120101 19:01 989 26735
10th St & Constitution Ave NW 20120101 19:01 991 26735
10th St & Constitution Ave NW 20120101 19:53 1279 29315
10th St & Constitution Ave NW 20120101 19:53 1301 29315
10th St & Constitution Ave NW 20120102 10:28 349 29664
10th St & Constitution Ave NW 20120102 12:31 1356 31020
10th St & Constitution Ave NW 20120102 12:34 1236 32256
10th St & Constitution Ave NW 20120102 14:05 2212 36682
10th St & Constitution Ave NW 20120102 14:05 2214 36682
10th St & Constitution Ave NW 20120102 16:06 498 37180
10th St & Constitution Ave NW 20120102 16:10 1731 38911
10th St & Constitution Ave NW 20120102 17:04 767 39678
10th St & Constitution Ave NW 20120102 17:08 1224 40902
10th St & Constitution Ave NW 20120102 18:08 1055 41957
10th St & Constitution Ave NW 20120103 06:26 366 42323
10th St & Constitution Ave NW 20120103 07:41 219 42542
10th St & Constitution Ave NW 20120103 07:51 269 42811
10th St & Constitution Ave NW 20120103 08:14 1293 44104
10th St & Constitution Ave NW 20120103 14:23 1706 47534
10th St & Constitution Ave NW 20120103 14:23 1724 47534
10th St & Constitution Ave NW 20120103 14:53 747 48281
10th St & Constitution Ave NW 20120103 16:22 342 48623
10th St & Constitution Ave NW 20120103 16:39 677 49300
10th St & Constitution Ave NW 20120103 17:20 728 50028
10th St & Constitution Ave NW 20120103 17:38 1835 51863
10th St & Constitution Ave NW 20120103 18:00 281 52144
10th St & Constitution Ave NW 20120104 08:09 1318 53462
10th St & Constitution Ave NW 20120104 08:28 412 53874
10th St & Constitution Ave NW 20120104 11:48 435 54309
10th St & Constitution Ave NW 20120104 13:45 1111 55420
10th St & Constitution Ave NW 20120104 16:22 317 55737
10th St & Constitution Ave NW 20120104 16:26 874 56611
10th St & Constitution Ave NW 20120104 17:00 476 57087
10th St & Constitution Ave NW 20120104 18:00 282 57369
10th St & Constitution Ave NW 20120105 08:18 1167 58536
10th St & Constitution Ave NW 20120105 08:34 275 58811
10th St & Constitution Ave NW 20120105 08:45 1030 59841
10th St & Constitution Ave NW 20120105 12:19 2779 62620
10th St & Constitution Ave NW 20120105 12:35 257 62877
10th St & Constitution Ave NW 20120105 14:03 987 63864
10th St & Constitution Ave NW 20120105 15:50 508 64372
10th St & Constitution Ave NW 20120105 16:21 2108 66480
10th St & Constitution Ave NW 20120105 16:36 1311 69109
10th St & Constitution Ave NW 20120105 16:36 1318 69109
10th St & Constitution Ave NW 20120105 17:12 1032 70141
10th St & Constitution Ave NW 20120105 17:13 694 70835
10th St & Constitution Ave NW 20120105 17:14 605 71440
10th St & Constitution Ave NW 20120105 17:23 1001 72441
10th St & Constitution Ave NW 20120105 17:47 430 72871
10th St & Constitution Ave NW 20120105 18:00 239 73110
10th St & Constitution Ave NW 20120105 18:09 1301 74411
10th St & Constitution Ave NW 20120105 18:12 1171 75582
10th St & Constitution Ave NW 20120105 18:40 599 76181
10th St & Constitution Ave NW 20120105 20:29 497 76678
10th St & Constitution Ave NW 20120106 08:19 1103 77781
10th St & Constitution Ave NW 20120106 08:34 329 78110
10th St & Constitution Ave NW 20120106 09:34 400 78510
10th St & Constitution Ave NW 20120106 09:50 617 79127
10th St & Constitution Ave NW 20120106 10:47 1728 80855
10th St & Constitution Ave NW 20120106 10:48 1675 82530
10th St & Constitution Ave NW 20120106 13:10 899 83429
10th St & Constitution Ave NW 20120106 13:45 258 83687
10th St & Constitution Ave NW 20120106 13:49 1227 84914
10th St & Constitution Ave NW 20120106 14:06 440 85354
10th St & Constitution Ave NW 20120106 16:08 4201 93737
10th St & Constitution Ave NW 20120106 16:08 4182 93737
10th St & Constitution Ave NW 20120106 16:16 1279 95016
10th St & Constitution Ave NW 20120106 16:43 3529 102067
10th St & Constitution Ave NW 20120106 16:43 3522 102067
10th St & Constitution Ave NW 20120106 16:57 486 102553
10th St & Constitution Ave NW 20120106 16:58 616 103169
10th St & Constitution Ave NW 20120106 17:28 958 104127
10th St & Constitution Ave NW 20120106 17:31 1399 105526
10th St & Constitution Ave NW 20120106 17:33 322 105848
10th St & Constitution Ave NW 20120106 17:45 1460 107308
10th St & Constitution Ave NW 20120106 17:52 2991 113289
10th St & Constitution Ave NW 20120106 17:52 2990 113289
10th St & Constitution Ave NW 20120106 18:36 244 113533
10th St & Constitution Ave NW 20120106 19:58 484 114017
10th St & Constitution Ave NW 20120106 21:18 1234 116488
10th St & Constitution Ave NW 20120106 21:18 1237 116488
10th St & Constitution Ave NW 20120106 22:33 1001 117489
10th St & Constitution Ave NW 20120106 22:43 1464 118953
10th St & Constitution Ave NW 20120107 09:51 403 119762
10th St & Constitution Ave NW 20120107 09:51 406 119762
10th St & Constitution Ave NW 20120107 12:06 7388 134563
10th St & Constitution Ave NW 20120107 12:06 7413 134563
10th St & Constitution Ave NW 20120107 12:30 760 135323
10th St & Constitution Ave NW 20120107 13:07 546 135869
10th St & Constitution Ave NW 20120107 13:10 3557 142998
10th St & Constitution Ave NW 20120107 13:10 3572 142998
10th St & Constitution Ave NW 20120107 13:17 416 148296
10th St & Constitution Ave NW 20120107 13:17 407 148296
10th St & Constitution Ave NW 20120107 13:17 4475 148296
10th St & Constitution Ave NW 20120107 13:18 4446 152742
10th St & Constitution Ave NW 20120107 13:19 4384 157126
10th St & Constitution Ave NW 20120107 13:21 1770 158896
10th St & Constitution Ave NW 20120107 13:22 1718 160614
10th St & Constitution Ave NW 20120107 14:09 568 161182
10th St & Constitution Ave NW 20120107 14:10 1473 162655
10th St & Constitution Ave NW 20120107 14:59 1473 164128
10th St & Constitution Ave NW 20120107 15:00 1466 165594
10th St & Constitution Ave NW 20120107 15:07 858 166452
10th St & Constitution Ave NW 20120107 15:15 700 167866
10th St & Constitution Ave NW 20120107 15:15 714 167866
10th St & Constitution Ave NW 20120107 15:36 1245 169111
10th St & Constitution Ave NW 20120107 16:12 530 169641
10th St & Constitution Ave NW 20120107 16:17 916 170557
10th St & Constitution Ave NW 20120107 16:24 689 171246
10th St & Constitution Ave NW 20120107 16:32 1458 172704
10th St & Constitution Ave NW 20120107 16:46 468 173172
10th St & Constitution Ave NW 20120107 16:53 5353 183886
10th St & Constitution Ave NW 20120107 16:53 5361 183886
10th St & Constitution Ave NW 20120107 17:37 307 184193
10th St & Constitution Ave NW 20120107 17:41 1164 189440
10th St & Constitution Ave NW 20120107 17:41 1180 189440
10th St & Constitution Ave NW 20120107 17:41 2903 189440
10th St & Constitution Ave NW 20120107 17:42 3198 195875
10th St & Constitution Ave NW 20120107 17:42 3237 195875
10th St & Constitution Ave NW 20120107 18:11 6341 202216
10th St & Constitution Ave NW 20120107 18:12 5471 207687
10th St & Constitution Ave NW 20120107 18:13 5485 213172
10th St & Constitution Ave NW 20120107 18:14 6207 219379
10th St & Constitution Ave NW 20120107 22:43 1097 221555
10th St & Constitution Ave NW 20120107 22:43 1079 221555
10th St & Constitution Ave NW 20120107 23:03 1226 222781
10th St & Constitution Ave NW 20120107 23:04 1207 223988
11th & H St NE 20120101 14:41 505 505
11th & H St NE 20120101 14:54 539 1044
11th & H St NE 20120101 15:43 625 1669
11th & H St NE 20120101 15:44 589 2258
11th & H St NE 20120101 16:07 789 3047
11th & H St NE 20120101 19:32 1191 4238
11th & H St NE 20120101 20:46 467 4705
11th & H St NE 20120101 21:29 1230 6542
11th & H St NE 20120101 21:29 607 6542
11th & H St NE 20120102 11:16 741 7283
11th & H St NE 20120102 12:19 917 8200
11th & H St NE 20120102 12:25 725 8925
11th & H St NE 20120102 12:48 1090 11101
11th & H St NE 20120102 12:48 1086 11101
11th & H St NE 20120102 13:10 751 11852
11th & H St NE 20120102 13:11 206 12058
11th & H St NE 20120102 13:17 571 12629
11th & H St NE 20120102 14:14 545 13174
11th & H St NE 20120102 14:27 895 14069
11th & H St NE 20120102 20:11 244 14313
11th & H St NE 20120103 07:00 1039 15352
11th & H St NE 20120103 08:10 967 16319
11th & H St NE 20120103 08:11 336 16655
11th & H St NE 20120103 08:12 1357 18758
11th & H St NE 20120103 08:12 746 18758
11th & H St NE 20120103 08:28 337 19095
11th & H St NE 20120103 08:41 326 19421
11th & H St NE 20120103 08:59 555 19976
11th & H St NE 20120103 09:12 446 20422
11th & H St NE 20120103 13:04 373 20795
11th & H St NE 20120103 13:46 356 21151
11th & H St NE 20120103 14:47 352 21503
11th & H St NE 20120103 18:23 347 21850
11th & H St NE 20120103 19:48 146 21996
11th & H St NE 20120103 22:15 1801 23797
11th & H St NE 20120104 06:05 1196 24993
11th & H St NE 20120104 08:08 937 25930
11th & H St NE 20120104 08:11 304 26234
11th & H St NE 20120104 08:18 851 27085
11th & H St NE 20120104 08:54 346 27431
11th & H St NE 20120104 12:56 304 27735
11th & H St NE 20120104 17:13 407 28142
11th & H St NE 20120104 18:19 584 28726
11th & H St NE 20120104 18:25 284 29010
11th & H St NE 20120104 18:32 425 29435
11th & H St NE 20120104 18:50 527 29962
11th & H St NE 20120104 19:10 562 30524
11th & H St NE 20120104 21:33 546 31070
11th & H St NE 20120104 21:34 508 31578
11th & H St NE 20120105 06:46 919 32497
11th & H St NE 20120105 07:47 921 33418
11th & H St NE 20120105 08:11 304 33722
11th & H St NE 20120105 08:24 865 34587
11th & H St NE 20120105 08:40 360 34947
11th & H St NE 20120105 08:44 520 35467
11th & H St NE 20120105 12:10 398 35865
11th & H St NE 20120105 14:55 350 36215
11th & H St NE 20120105 15:32 408 36623
11th & H St NE 20120105 17:37 234 36857
11th & H St NE 20120105 18:34 340 37197
11th & H St NE 20120105 20:26 280 37756
11th & H St NE 20120105 20:26 279 37756
11th & H St NE 20120105 22:08 526 38282
11th & H St NE 20120105 22:25 310 38592

     

    上面的查询通过 start_station 和 start_dt 进行分组和排序。在每一个 start_station 分组里面是按照 start_dt 进行排序的，求和函数是对当前行和之前所有行的 duration 进行求和。按照这样的规则向下滚动执行，直到分组字段 start_station 的值变化了，你会注意到分组字段值变化后，聚合函数 running_total 也会重新滚动计算。那就是你用 PARTITION BY 进行分组后的效果。担心你仍被 ORDER BY 所困惑，再做一下解释，它只是用被指定的排序列排序，就像 ORDER BY 表达式所实现的效果一样，除了它将每个分区（分组）当作一个独立的部分之外没其他区别。它也可以不带ORDER BY 去执行求和操作，那么每个结果值就仅是各自 start_station 分组中所有 duration 值的和。试着不带 ORDER BY 去运行上面的查询看看：

 

SELECT start_station_num,
       duration,
       SUM（duration) OVER(PARTITION  BY
        start_station_num) 
       AS running_total
FROM biketrip
WHERE start_dt < '2012-01-08'

 

start_station_num duration running_total
31000 277 12207
31000 414 12207
31000 447 12207
31000 424 12207
31000 74 12207
31000 387 12207
31000 291 12207
31000 393 12207
31000 412 12207
31000 2661 12207
31000 399 12207
31000 398 12207
31000 520 12207
31000 348 12207
31000 3340 12207
31000 1422 12207
31001 220 39505
31001 181 39505
31001 1099 39505
31001 304 39505
31001 191 39505
31001 303 39505
31001 269 39505
31001 180 39505
31001 256 39505
31001 261 39505
31001 679 39505
31001 245 39505
31001 2876 39505
31001 2804 39505
31001 2686 39505
31001 800 39505
31001 1157 39505
31001 738 39505
31001 160 39505
31001 173 39505
31001 179 39505
31001 317 39505
31001 204 39505
31001 186 39505
31001 162 39505
31001 1168 39505
31001 501 39505
31001 1740 39505
31001 395 39505
31001 3624 39505
31001 3598 39505
31001 294 39505
31001 155 39505
31001 214 39505
31001 156 39505
31001 331 39505
31001 1103 39505
31001 188 39505
31001 992 39505
31001 294 39505
31001 138 39505
31001 1264 39505
31001 120 39505
31001 245 39505
31001 138 39505
31001 1747 39505
31001 186 39505
31001 222 39505
31001 222 39505
31001 212 39505
31001 155 39505
31001 286 39505
31001 448 39505
31001 202 39505
31001 1426 39505
31001 773 39505
31001 338 39505
31002 94 87171
31002 2540 87171
31002 661 87171
31002 676 87171
31002 1512 87171
31002 5113 87171
31002 450 87171
31002 3794 87171
31002 974 87171
31002 1367 87171
31002 462 87171
31002 104 87171
31002 1585 87171
31002 1482 87171
31002 1359 87171
31002 2050 87171
31002 578 87171
31002 213 87171
31002 41427 87171
31002 170 87171
31002 1523 87171
31002 120 87171
31002 422 87171
31002 1462 87171
31002 70 87171
31002 305 87171
31002 1202 87171
31002 1478 87171
31002 2965 87171
31002 568 87171
31002 1441 87171
31002 252 87171
31002 297 87171
31002 1373 87171
31002 150 87171
31002 86 87171
31002 71 87171
31002 197 87171
31002 1015 87171
31002 90 87171
31002 2457 87171
31002 787 87171
31002 360 87171
31002 1491 87171
31002 378 87171
31003 134 33345
31003 3563 33345
31003 1262 33345
31003 2128 33345
31003 2063 33345
31003 2008 33345
31003 337 33345
31003 2034 33345
31003 268 33345
31003 147 33345
31003 82 33345
31003 308 33345
31003 191 33345
31003 290 33345
31003 1573 33345
31003 108 33345
31003 336 33345
31003 1204 33345
31003 598 33345
31003 190 33345
31003 351 33345
31003 250 33345
31003 96 33345
31003 1540 33345
31003 108 33345
31003 179 33345
31003 2858 33345
31003 494 33345
31003 243 33345
31003 276 33345
31003 4441 33345
31003 104 33345
31003 3581 33345
31004 589 6349
31004 123 6349
31004 264 6349
31004 1216 6349
31004 116 6349
31004 214 6349
31004 146 6349
31004 164 6349
31004 204 6349
31004 151 6349
31004 226 6349
31004 172 6349
31004 127 6349
31004 263 6349
31004 145 6349
31004 400 6349

 

    ORDER BY 和 PARTITION 定义了规则，依据这个规则形成了窗口——即数据的有序子集，从首行到末行函数滚动进行计算。 


注意：你不能在同一个查询中同时使用开窗函数和标准的聚合函数。更明确来说，你不能在 GROUP BY 表达式中使用开窗函数。

 

  • 通常的疑问：SUM，COUNT，AVG函数呢

    当要使用窗口函数时，你可以像正常情况下使用聚合函数那样应用同样的聚合函数——SUM,COUNT和AVG。理解这些最容易的方法就是使用更多的函数重跑上面的例子。

SELECT start_station_num,
       duration,
       SUM(duration) OVER(PARTITION  BY
        start_station_num) AS running_total
       COUNT(duration) OVER(PARTITION  BY
        start_station_num) AS running_count
       AVG(duration) OVER(PARTITION  BY
        start_station_num) AS running_avg
FROM bikestrip
WHERE start_dt < '2012-01-08'

 

start_station_num duration running_total running_count running_avg
31000 277 12207 16 762.9375
31000 414 12207 16 762.9375
31000 447 12207 16 762.9375
31000 424 12207 16 762.9375
31000 74 12207 16 762.9375
31000 387 12207 16 762.9375
31000 291 12207 16 762.9375
31000 393 12207 16 762.9375
31000 412 12207 16 762.9375
31000 2661 12207 16 762.9375
31000 399 12207 16 762.9375
31000 398 12207 16 762.9375
31000 520 12207 16 762.9375
31000 348 12207 16 762.9375
31000 3340 12207 16 762.9375
31000 1422 12207 16 762.9375
31001 220 39505 57 693.070175438596
31001 181 39505 57 693.070175438596
31001 1099 39505 57 693.070175438596
31001 304 39505 57 693.070175438596
31001 191 39505 57 693.070175438596
31001 303 39505 57 693.070175438596
31001 269 39505 57 693.070175438596
31001 180 39505 57 693.070175438596
31001 256 39505 57 693.070175438596
31001 261 39505 57 693.070175438596
31001 679 39505 57 693.070175438596
31001 245 39505 57 693.070175438596
31001 2876 39505 57 693.070175438596
31001 2804 39505 57 693.070175438596
31001 2686 39505 57 693.070175438596
31001 800 39505 57 693.070175438596
31001 1157 39505 57 693.070175438596
31001 738 39505 57 693.070175438596
31001 160 39505 57 693.070175438596
31001 173 39505 57 693.070175438596
31001 179 39505 57 693.070175438596
31001 317 39505 57 693.070175438596
31001 204 39505 57 693.070175438596
31001 186 39505 57 693.070175438596
31001 162 39505 57 693.070175438596
31001 1168 39505 57 693.070175438596
31001 501 39505 57 693.070175438596
31001 1740 39505 57 693.070175438596
31001 395 39505 57 693.070175438596
31001 3624 39505 57 693.070175438596
31001 3598 39505 57 693.070175438596
31001 294 39505 57 693.070175438596
31001 155 39505 57 693.070175438596
31001 214 39505 57 693.070175438596
31001 156 39505 57 693.070175438596
31001 331 39505 57 693.070175438596
31001 1103 39505 57 693.070175438596
31001 188 39505 57 693.070175438596
31001 992 39505 57 693.070175438596
31001 294 39505 57 693.070175438596
31001 138 39505 57 693.070175438596
31001 1264 39505 57 693.070175438596
31001 120 39505 57 693.070175438596
31001 245 39505 57 693.070175438596
31001 138 39505 57 693.070175438596
31001 1747 39505 57 693.070175438596
31001 186 39505 57 693.070175438596
31001 222 39505 57 693.070175438596
31001 222 39505 57 693.070175438596
31001 212 39505 57 693.070175438596
31001 155 39505 57 693.070175438596
31001 286 39505 57 693.070175438596
31001 448 39505 57 693.070175438596
31001 202 39505 57 693.070175438596
31001 1426 39505 57 693.070175438596
31001 773 39505 57 693.070175438596
31001 338 39505 57 693.070175438596
31002 94 87171 45 1937.13333333333
31002 2540 87171 45 1937.13333333333
31002 661 87171 45 1937.13333333333
31002 676 87171 45 1937.13333333333
31002 1512 87171 45 1937.13333333333
31002 5113 87171 45 1937.13333333333
31002 450 87171 45 1937.13333333333
31002 3794 87171 45 1937.13333333333
31002 974 87171 45 1937.13333333333
31002 1367 87171 45 1937.13333333333
31002 462 87171 45 1937.13333333333
31002 104 87171 45 1937.13333333333
31002 1585 87171 45 1937.13333333333
31002 1482 87171 45 1937.13333333333
31002 1359 87171 45 1937.13333333333
31002 2050 87171 45 1937.13333333333
31002 578 87171 45 1937.13333333333
31002 213 87171 45 1937.13333333333
31002 41427 87171 45 1937.13333333333
31002 170 87171 45 1937.13333333333
31002 1523 87171 45 1937.13333333333
31002 120 87171 45 1937.13333333333
31002 422 87171 45 1937.13333333333
31002 1462 87171 45 1937.13333333333
31002 70 87171 45 1937.13333333333
31002 305 87171 45 1937.13333333333
31002 1202 87171 45 1937.13333333333
31002 1478 87171 45 1937.13333333333
31002 2965 87171 45 1937.13333333333
31002 568 87171 45 1937.13333333333
31002 1441 87171 45 1937.13333333333
31002 252 87171 45 1937.13333333333
31002 297 87171 45 1937.13333333333
31002 1373 87171 45 1937.13333333333
31002 150 87171 45 1937.13333333333
31002 86 87171 45 1937.13333333333
31002 71 87171 45 1937.13333333333
31002 197 87171 45 1937.13333333333
31002 1015 87171 45 1937.13333333333
31002 90 87171 45 1937.13333333333
31002 2457 87171 45 1937.13333333333
31002 787 87171 45 1937.13333333333
31002 360 87171 45 1937.13333333333
31002 1491 87171 45 1937.13333333333
31002 378 87171 45 1937.13333333333
31003 134 33345 33 1010.45454545454
31003 3563 33345 33 1010.45454545454
31003 1262 33345 33 1010.45454545454
31003 2128 33345 33 1010.45454545454
31003 2063 33345 33 1010.45454545454
31003 2008 33345 33 1010.45454545454
31003 337 33345 33 1010.45454545454
31003 2034 33345 33 1010.45454545454
31003 268 33345 33 1010.45454545454
31003 147 33345 33 1010.45454545454
31003 82 33345 33 1010.45454545454
31003 308 33345 33 1010.45454545454
31003 191 33345 33 1010.45454545454
31003 290 33345 33 1010.45454545454
31003 1573 33345 33 1010.45454545454
31003 108 33345 33 1010.45454545454
31003 336 33345 33 1010.45454545454
31003 1204 33345 33 1010.45454545454
31003 598 33345 33 1010.45454545454
31003 190 33345 33 1010.45454545454
31003 351 33345 33 1010.45454545454
31003 250 33345 33 1010.45454545454
31003 96 33345 33 1010.45454545454
31003 1540 33345 33 1010.45454545454
31003 108 33345 33 1010.45454545454
31003 179 33345 33 1010.45454545454
31003 2858 33345 33 1010.45454545454
31003 494 33345 33 1010.45454545454
31003 243 33345 33 1010.45454545454
31003 276 33345 33 1010.45454545454
31003 4441 33345 33 1010.45454545454
31003 104 33345 33 1010.45454545454
31003 3581 33345 33 1010.45454545454
31004 589 6349 18 352.722222222222
31004 123 6349 18 352.722222222222
31004 264 6349 18 352.722222222222
31004 1216 6349 18 352.722222222222

     另一个，同样的函数加上 ORDER BY :

SELECT start_station_num,
       duration,
       SUM（duration) OVER(PARTITION  BY
        start_station_num ORDER BY start_dt) AS running_total
       COUNT（duration) OVER(PARTITION  BY
        start_station_num ORDER BY start_dt) AS running_count
       AVG（duration) OVER(PARTITION  BY
        start_station_num ORDER BY start_dt) AS running_avg
FROM biketrip
WHERE start_dt < '2012-01-08'

 

start_station_num duration running_total running_count running_avg
31000 74 74 1 74
31000 291 365 2 182.5
31000 520 885 3 295
31000 424 1756 5 351.2
31000 447 1756 5 351.2
31000 1422 3178 6 529.666666666666
31000 348 3526 7 503.714285714285
31000 277 3803 8 475.375
31000 3340 7143 9 793.666666666666
31000 414 7955 11 723.181818181818
31000 398 7955 11 723.181818181818
31000 412 8766 13 674.307692307692
31000 399 8766 13 674.307692307692
31000 2661 11427 14 816.214285714285
31000 393 12207 16 762.9375
31000 387 12207 16 762.9375
31001 162 162 1 162
31001 2876 3038 2 1519
31001 2804 5842 3 1947.33333333333
31001 2686 8528 4 2132
31001 3624 15750 6 2625
31001 3598 15750 6 2625
31001 1426 17176 7 2453.71428571428
31001 303 17479 8 2184.875
31001 269 17748 9 1972
31001 155 17903 10 1790.3
31001 304 18207 11 1655.18181818181
31001 245 18452 12 1537.66666666666
31001 173 18625 13 1432.6923076923
31001 220 19049 15 1269.93333333333
31001 204 19049 15 1269.93333333333
31001 395 19444 16 1215.25
31001 245 19689 17 1158.17647058823
31001 181 19870 18 1103.88888888888
31001 138 20008 19 1053.05263157894
31001 138 20146 20 1007.3
31001 214 20360 21 969.523809523809
31001 294 20654 22 938.818181818181
31001 1103 21757 23 945.95652173913
31001 501 22258 24 927.416666666666
31001 286 22544 25 901.76
31001 180 22724 26 874
31001 155 22879 27 847.37037037037
31001 294 23173 28 827.607142857142
31001 800 23973 29 826.655172413793
31001 191 24164 30 805.466666666666
31001 1740 27651 32 864.09375
31001 1747 27651 32 864.09375
31001 338 27989 33 848.151515151515
31001 1157 29146 34 857.235294117647
31001 331 29477 35 842.2
31001 188 29665 36 824.027777777777
31001 992 30657 37 828.567567567567
31001 120 30777 38 809.921052631578
31001 222 31221 40 780.525
31001 222 31221 40 780.525
31001 448 31669 41 772.414634146341
31001 156 31825 42 757.738095238095
31001 317 32142 43 747.488372093023
31001 738 32880 44 747.272727272727
31001 1264 34144 45 758.755555555555
31001 1168 35312 46 767.652173913043
31001 773 36085 47 767.765957446808
31001 186 36473 49 744.34693877551
31001 202 36473 49 744.34693877551
31001 212 36685 50 733.7
31001 186 36871 51 722.960784313725
31001 179 37050 52 712.5
31001 261 37567 54 695.685185185185
31001 256 37567 54 695.685185185185
31001 679 38246 55 695.381818181818
31001 1099 39345 56 702.589285714285
31001 160 39505 57 693.070175438596
31002 378 378 1 378
31002 305 683 2 341.5
31002 1202 43312 4 10828
31002 41427 43312 4 10828
31002 170 43482 5 8696.4
31002 213 43695 6 7282.5
31002 2050 45745 7 6535
31002 5113 50858 8 6357.25
31002 568 52004 10 5200.4
31002 578 52004 10 5200.4
31002 1015 53019 11 4819.90909090909
31002 1491 54510 12 4542.5
31002 197 54707 13 4208.23076923076
31002 1462 56169 14 4012.07142857142
31002 71 56240 15 3749.33333333333
31002 104 56344 16 3521.5
31002 1478 57822 17 3401.29411764705
31002 1585 59407 18 3300.38888888888
31002 297 59704 19 3142.31578947368
31002 1359 61063 20 3053.15
31002 86 61149 21 2911.85714285714
31002 90 61239 22 2783.5909090909
31002 150 61389 23 2669.08695652173
31002 974 62363 24 2598.45833333333
31002 422 62785 25 2511.4
31002 1441 64226 26 2470.23076923076
31002 252 64478 27 2388.07407407407
31002 70 64548 28 2305.28571428571
31002 94 64642 29 2229.03448275862
31002 120 64762 30 2158.73333333333
31002 1482 69279 33 2099.36363636363
31002 1512 69279 33 2099.36363636363
31002 1523 69279 33 2099.36363636363
31002 462 70191 35 2005.45714285714
31002 450 70191 35 2005.45714285714
31002 1373 71564 36 1987.88888888888
31002 661 72901 38 1918.44736842105
31002 676 72901 38 1918.44736842105
31002 3794 76695 39 1966.53846153846
31002 2457 81692 41 1992.48780487804
31002 2540 81692 41 1992.48780487804
31002 787 82479 42 1963.78571428571
31002 1367 83846 43 1949.90697674418
31002 2965 86811 44 1972.97727272727
31002 360 87171 45 1937.13333333333
31003 250 250 1 250
31003 3581 7394 3 2464.66666666666
31003 3563 7394 3 2464.66666666666
31003 2128 9522 4 2380.5
31003 2008 13593 6 2265.5
31003 2063 13593 6 2265.5
31003 2034 15627 7 2232.42857142857
31003 268 15895 8 1986.875
31003 1204 17099 9 1899.88888888888
31003 308 17407 10 1740.7
31003 337 17744 11 1613.0909090909
31003 96 17840 12 1486.66666666666
31003 243 18083 13 1391
31003 290 18373 14 1312.35714285714
31003 336 18709 15 1247.26666666666
31003 108 18817 16 1176.0625
31003 134 18951 17 1114.76470588235
31003 276 19227 18 1068.16666666666
31003 104 19331 19 1017.42105263157
31003 147 19478 20 973.9
31003 598 20076 21 956
31003 1262 21338 22 969.90909090909
31003 351 21689 23 943
31003 82 21771 24 907.125
31003 2858 24629 25 985.16
31003 108 24737 26 951.423076923076
31003 191 24928 27 923.259259259259
31003 179 25107 28 896.678571428571
31003 494 25601 29 882.793103448275
31003 1540 27141 30 904.7
31003 190 27331 31 881.645161290322
31003 1573 28904 32 903.25
31003 4441 33345 33 1010.45454545454
31004 225 225 1 225
31004 264 489 2 244.5
31004 1604 2093 3 697.666666666666
31004 400 2493 4 623.25
31004 204 2697 5 539.4
31004 226 2923 6 487.166666666666
31004 127 3050 7 435.714285714285
31004 116 3166 8 395.75
31004 151 3317 9 368.555555555555
31004 172 3489 10 348.9
31004 214 3703 11 336.636363636363
31004 164 3867 12 322.25
31004 1216 5083 13 391
31004 123 5206 14 371.857142857142
31004 145 5351 15 356.733333333333
31004 146 5497 16 343.5625
31004 263 5760 17 338.823529411764
31004 589 6349 18 352.722222222222

     确保你将前面两个查询输入了Mode（译者注：一个网站，可以连接数据库，如何连接请参考最后）并执行。接下来的练习题跟上面那些例子十分相似，所以不要重新写那些代码修改一下上面的就行。

  • ROW_NUMBER()

    ROW_NUMBER() 函数的作用可从它的字面意思看出——显示一个给定行的行号。从1开始，按照开窗函数表达式中 ORDER BY 的规则，给行标行号。 ROW_NUMBER() 函数不要求你在括号内添加参数。

SELECT duration,
       ROW_NUMBER() OVER(ORDER BY start_dt) AS row_number
FROM biketrip
WHERE start_dt < '2012-01-08'

 

duration row_number
475 1
1145 2
1162 3
485 4
471 5
358 6
1754 7
259 8
516 9
913 10
1097 11
490 12
1045 13
1035 14
1060 15
1039 16
443 17
316 18
506 19
956 20
244 21
319 22
157 23
511 24
199 25
499 26
460 27
931 28
398 29
558 30
606 31
144 32
886 33
241 34
468 35
460 36
438 37
444 38
198 39
179 40
378 41
305 42
817 43
107 44
494 45
1206 46
845 47
410 48

     使用 PARTITION BY 子句，在每个分组中将会重新从1开始计数。下面的查询中，对于每个起始站点都会从头开始计数：

 

SELECT start_station_num,
       duration,
       ROW_NUMBER() OVER(PARTITION  BY
       start_station_num ORDER BY start_dt) 
       AS row_number
FROM biketrip
WHERE start_dt < '2012-01-08'

 

start_station_num duration row_number
31000 74 1
31000 291 2
31000 520 3
31000 424 4
31000 447 5
31000 1422 6
31000 348 7
31000 277 8
31000 3340 9
31000 414 10
31000 398 11
31000 412 12
31000 399 13
31000 2661 14
31000 393 15
31000 387 16
31001 162 1
31001 2876 2
31001 2804 3
31001 2686 4
31001 3624 5
31001 3598 6
31001 1426 7
31001 303 8
31001 269 9
31001 155 10
31001 304 11
31001 245 12
31001 173 13
31001 220 14
31001 204 15
31001 395 16
31001 245 17
31001 181 18
31001 138 19
31001 138 20
31001 214 21
31001 294 22
31001 1103 23
31001 501 24
31001 286 25
31001 180 26
31001 155 27
31001 294 28
31001 800 29
31001 191 30
31001 1740 31
31001 1747 32
31001 338 33
31001 1157 34
31001 331 35
31001 188 36
31001 992 37
31001 120 38
31001 222 39
31001 222 40
31001 448 41
31001 156 42
31001 317 43
31001 738 44
31001 1264 45
31001 1168 46
31001 773 47
31001 186 48
31001 202 49
31001 212 50
31001 186 51
31001 179 52
31001 261 53
31001 256 54
31001 679 55
31001 1099 56
31001 160 57
31002 378 1
31002 305 2
31002 1202 3
31002 41427 4
31002 170 5
31002 213 6
31002 2050 7
31002 5113 8
31002 568 9
31002 578 10
31002 1015 11
31002 1491 12
31002 197 13
31002 1462 14
31002 71 15
31002 104 16
31002 1478 17
31002 1585 18
31002 297 19
31002 1359 20
31002 86 21
31002 90 22
31002 150 23
31002 974 24
31002 422 25
31002 1441 26
31002 252 27
31002 70 28
31002 94 29
31002 120 30
31002 1482 31
31002 1512 32
31002 1523 33
31002 462 34
31002 450 35
31002 1373 36
31002 661 37
31002 676 38
31002 3794 39
31002 2457 40
31002 2540 41
31002 787 42
31002 1367 43
31002 2965 44
31002 360 45
31003 250 1
31003 3581 2
31003 3563 3
31003 2128 4
31003 2008 5
31003 2063 6
31003 2034 7
31003 268 8
31003 1204 9

 

  • RANK()和DENSE_RANK()

    RANK() 跟  ROW_NUMBER() 有略微的差别。举个栗子，你如果按照 start_dt 排序，在一些站点很可能有起始时间相同的骑行（译者注： start_dt 相同的行）。在这种情况下，他们会有相同的排名，但是 ROW_NUMBER() 会赋予他们不同的行号。

下面查询的结果中，你留意一下 start_station_num 310000分组中的第4 ，5行数据——他们的排名都为4，他们的下面一行结果得到了6的排名：

 

SELECT start_station_num,
       duration,
       RANK() OVER(PARTITION  BY
        start_station_num ORDER BY start_dt) 
       AS rank
FROM biketrip
WHERE start_dt < '2012-01-08'

 

start_station_num duration rank
31000 74 1
31000 291 2
31000 520 3
31000 424 4
31000 447 4
31000 1422 6
31000 348 7
31000 277 8
31000 3340 9
31000 414 10
31000 398 10
31000 412 12
31000 399 12
31000 2661 14
31000 393 15
31000 387 15
31001 162 1
31001 2876 2
31001 2804 3
31001 2686 4
31001 3624 5
31001 3598 5
31001 1426 7
31001 303 8
31001 269 9
31001 155 10
31001 304 11
31001 245 12
31001 173 13
31001 220 14
31001 204 14
31001 395 16
31001 245 17
31001 181 18
31001 138 19
31001 138 20
31001 214 21
31001 294 22
31001 1103 23
31001 501 24
31001 286 25
31001 180 26
31001 155 27
31001 294 28
31001 800 29
31001 191 30
31001 1740 31
31001 1747 31
31001 338 33
31001 1157 34
31001 331 35
31001 188 36
31001 992 37
31001 120 38
31001 222 39
31001 222 39
31001 448 41
31001 156 42
31001 317 43
31001 738 44
31001 1264 45
31001 1168 46
31001 773 47
31001 186 48
31001 202 48
31001 212 50
31001 186 51
31001 179 52
31001 261 53
31001 256 53
31001 679 55
31001 1099 56
31001 160 57
31002 378 1
31002 305 2
31002 1202 3
31002 41427 3
31002 170 5
31002 213 6
31002 2050 7
31002 5113 8
31002 568 9
31002 578 9
31002 1015 11
31002 1491 12
31002 197 13
31002 1462 14
31002 71 15
31002 104 16
31002 1478 17
31002 1585 18
31002 297 19
31002 1359 20
31002 86 21
31002 90 22
31002 150 23
31002 974 24
31002 422 25
31002 1441 26
31002 252 27
31002 70 28
31002 94 29
31002 120 30
31002 1482 31
31002 1512 31
31002 1523 31
31002 462 34
31002 450 34
31002 1373 36
31002 661 37
31002 676 37
31002 3794 39
31002 2457 40
31002 2540 40
31002 787 42
31002 1367 43
31002 2965 44
31002 360 45
31003 250 1
31003 3581 2
31003 3563 2
31003 2128 4
31003 2008 5
31003 2063 5
31003 2034 7
31003 268 8
31003 1204 9
31003 308 10
31003 337 11
31003 96 12
31003 243 13
31003 290 14
31003 336 15
31003 108 16
31003 134 17
31003 276 18
31003 104 19
31003 147 20
31003 598 21
31003 1262 22
31003 351 23
31003 82 24
31003 2858 25
31003 108 26
31003 191 27
31003 179 28
31003 494 29
31003 1540 30
31003 190 31
31003 1573 32
31003 4441 33
31004 225 1
31004 264 2
31004 1604 3
31004 400 4
31004 204 5
31004 226 6
31004 127 7
31004 116 8
31004 151 9
31004 172 10
31004 214 11
31004 164 12
31004 1216 13
31004 123 14
31004 145 15
31004 146 16
31004 263 17
31004 589 18
31005 720 1
31005 2925 2
31005 1948 3
31005 2055 4
31005 1768 5
31005 261 6
31005 474 7
31005 711 8
31005 656 9
31005 370 10
31005 404 11
31005 166 12
31005 2038 13
31005 140 14
31005 128 14
31005 1761 16
31005 1922 16
31005 3534 18
31005 3242 19
31005 445 20
31005 369 21
31006 424 1
31006 1056 2
31006 271 3
31006 289 4
31006 336 5
31006 512 6
31006 1267 7
31006 362 8
31006 1320 9
31006 549 10
31006 1269 11
31006 512 12
31006 1169 13
31006 1244 14
31006 1499 15
31006 939 16
31006 853 17
31006 959 18
31006 1489 19

    根据你的应用你可以使用 DENSE_RANK() 代替RANK()。想象一个场景，三条数据有相同的一个字段值。使用哪个函数，他们都会得到一样的排名。让我们举个例子，假设排名值为‘2’。下面是这两个函数如何给下一条数据赋值的区别：

  1.  RANK() 函数会给相同的行排名为2，然后跳过3和4，所以下一个会是5.
  2.  DENSE_RANK()  函数仍然会给所有相同的行排名为2，但是下一行会是3，没有排名序号会被跳过。
  • NTILE

    你可以使用开窗函数识别一个给定的行落在哪个分位（四分位或者其他分割法。译者注：将结果集按顺序均匀装入给定数量的桶中，一个给定行所在的桶的序号）中。语法是 NTILE(*# of buckets*) 。这种用法中， ORDER BY  后面的排序列决定了一行所在哪个四分位置上（或者你规定的任何分法）。举个例子：

 

SELECT start_station_num,
       duration,
       NTILE(4) OVER(PARTITION  BY
       start_station_num ORDER BY duration) 
       AS quartile,
       NTILE(5) OVER(PARTITION  BY
       start_station_num ORDER BY duration) 
       AS quintile,
       NTILE(100) OVER(PARTITION  BY
       start_station_num ORDER BY duration) 
       AS percentile
FROM biketrip
WHERE start_dt < '2012-01-08'
ORDER BY start_station_num,duration

 

start_station_num duration quartile quintile percentile
31000 74 1 1 1
31000 277 1 1 2
31000 291 1 1 3
31000 348 1 1 4
31000 387 2 2 5
31000 393 2 2 6
31000 398 2 2 7
31000 399 2 3 8
31000 412 3 3 9
31000 414 3 3 10
31000 424 3 4 11
31000 447 3 4 12
31000 520 4 4 13
31000 1422 4 5 14
31000 2661 4 5 15
31000 3340 4 5 16
31001 120 1 1 1
31001 138 1 1 2
31001 138 1 1 3
31001 155 1 1 4
31001 155 1 1 5
31001 156 1 1 6
31001 160 1 1 7
31001 162 1 1 8
31001 173 1 1 9
31001 179 1 1 10
31001 180 1 1 11
31001 181 1 1 12
31001 186 1 2 13
31001 186 1 2 14
31001 188 1 2 15
31001 191 2 2 16
31001 202 2 2 17
31001 204 2 2 18
31001 212 2 2 19
31001 214 2 2 20
31001 220 2 2 21
31001 222 2 2 22
31001 222 2 2 23
31001 245 2 2 24
31001 245 2 3 25
31001 256 2 3 26
31001 261 2 3 27
31001 269 2 3 28
31001 286 2 3 29
31001 294 3 3 30
31001 294 3 3 31
31001 303 3 3 32
31001 304 3 3 33
31001 317 3 3 34
31001 331 3 3 35
31001 338 3 4 36
31001 395 3 4 37
31001 448 3 4 38
31001 501 3 4 39
31001 679 3 4 40
31001 738 3 4 41
31001 773 3 4 42
31001 800 3 4 43
31001 992 4 4 44
31001 1099 4 4 45
31001 1103 4 4 46
31001 1157 4 5 47
31001 1168 4 5 48
31001 1264 4 5 49
31001 1426 4 5 50
31001 1740 4 5 51
31001 1747 4 5 52
31001 2686 4 5 53
31001 2804 4 5 54
31001 2876 4 5 55
31001 3598 4 5 56
31001 3624 4 5 57
31002 70 1 1 1
31002 71 1 1 2
31002 86 1 1 3
31002 90 1 1 4
31002 94 1 1 5
31002 104 1 1 6
31002 120 1 1 7
31002 150 1 1 8
31002 170 1 1 9
31002 197 1 2 10
31002 213 1 2 11
31002 252 1 2 12
31002 297 2 2 13
31002 305 2 2 14
31002 360 2 2 15
31002 378 2 2 16
31002 422 2 2 17
31002 450 2 2 18
31002 462 2 3 19
31002 568 2 3 20
31002 578 2 3 21
31002 661 2 3 22
31002 676 2 3 23
31002 787 3 3 24
31002 974 3 3 25
31002 1015 3 3 26
31002 1202 3 3 27
31002 1359 3 4 28
31002 1367 3 4 29
31002 1373 3 4 30
31002 1441 3 4 31
31002 1462 3 4 32
31002 1478 3 4 33
31002 1482 3 4 34
31002 1491 4 4 35
31002 1512 4 4 36
31002 1523 4 5 37
31002 1585 4 5 38
31002 2050 4 5 39
31002 2457 4 5 40
31002 2540 4 5 41
31002 2965 4 5 42
31002 3794 4 5 43
31002 5113 4 5 44
31002 41427 4 5 45
31003 82 1 1 1
31003 96 1 1 2
31003 104 1 1 3
31003 108 1 1 4
31003 108 1 1 5
31003 134 1 1 6
31003 147 1 1 7
31003 179 1 2 8
31003 190 1 2 9
31003 191 2 2 10
31003 243 2 2 11
31003 250 2 2 12
31003 268 2 2 13
31003 276 2 2 14
31003 290 2 3 15
31003 308 2 3 16
31003 336 2 3 17
31003 337 3 3 18
31003 351 3 3 19
31003 494 3 3 20
31003 598 3 3 21
31003 1204 3 4 22
31003 1262 3 4 23
31003 1540 3 4 24
31003 1573 3 4 25
31003 2008 4 4 26
31003 2034 4 4 27
31003 2063 4 5 28
31003 2128 4 5 29
31003 2858 4 5 30
31003 3563 4 5 31
31003 3581 4 5 32
31003 4441 4 5 33
31004 116 1 1 1
31004 123 1 1 2
31004 127 1 1 3
31004 145 1 1 4
31004 146 1 2 5
31004 151 2 2 6
31004 164 2 2 7
31004 172 2 2 8
31004 204 2 3 9
31004 214 2 3 10
31004 225 3 3 11
31004 226 3 3 12
31004 263 3 4 13
31004 264 3 4 14
31004 400 4 4 15
31004 589 4 5 16
31004 1216 4 5 17
31004 1604 4 5 18
31005 128 1 1 1
31005 140 1 1 2
31005 166 1 1 3
31005 261 1 1 4
31005 369 1 1 5
31005 370 1 2 6
31005 404 2 2 7
31005 445 2 2 8
31005 474 2 2 9
31005 656 2 3 10
31005 711 2 3 11
31005 720 3 3 12
31005 1761 3 3 13
31005 1768 3 4 14
31005 1922 3 4 15
31005 1948 3 4 16
31005 2038 4 4 17
31005 2055 4 5 18
31005 2925 4 5 19
31005 3242 4 5 20
31005 3534 4 5 21
31006 271 1 1 1
31006 289 1 1 2
31006 336 1 1 3
31006 362 1 1 4
31006 424 1 1 5
31006 426 1 1 6
31006 433 1 2 7
31006 512 2 2 8
31006 512 2 2 9
31006 549 2 2 10
31006 626 2 2 11
31006 725 2 2 12
31006 853 2 3 13
31006 939 2 3 14
31006 959 3 3 15
31006 1056 3 3 16
31006 1169 3 3 17
31006 1244 3 4 18
31006 1267 3 4 19
31006 1269 3 4 20
31006 1320 3 4 21
31006 1468 4 4 22
31006 1489 4 5 23
31006 1499 4 5 24
31006 2463 4 5 25
31006 2472 4 5 26
31006 3009 4 5 27
31007 91 1 1 1
31007 103 1 1 1
31007 104 1 1 2
31007 109 1 1 2
31007 115 1 1 3
31007 117 1 1 3
31007 120 1 1 4
31007 121 1 1 4
31007 124 1 1 5
31007 130 1 1 5
31007 133 1 1 6
31007 133 1 1 6
31007 137 1 1 7
31007 138 1 1 7
31007 139 1 1 8
31007 140 1 1 8
31007 140 1 1 9
31007 141 1 1 9
31007 145 1 1 10
31007 145 1 1 10
31007 146 1 1 11
31007 146 1 1 11
31007 146 1 1 12
31007 151 1 1 12
31007 153 1 1 13
31007 155 1 1 13
31007 158 1 1 14
31007 160 1 1 14
31007 160 1 2 15
31007 162 1 2 15
31007 162 1 2 16
31007 162 1 2 16
31007 163 1 2 17
31007 163 1 2 17
31007 164 1 2 18
31007 164 2 2 18
31007 167 2 2 19
31007 168 2 2 19
31007 169 2 2 20
31007 171 2 2 20
31007 171 2 2 21
31007 172 2 2 21
31007 172 2 2 22
31007 172 2 2 22
31007 173 2 2 23
31007 174 2 2 23
31007 175 2 2 24
31007 177 2 2 24
31007 180 2 2 25
31007 181 2 2 25
31007 182 2 2 26
31007 183 2 2 26
31007 185 2 2 27
31007 186 2 2 27
31007 189 2 2 28
31007 189 2 2 28
31007 191 2 3 29
31007 193 2 3 29
31007 193 2 3 30
31007 195 2 3 30
31007 196 2 3 31
31007 200 2 3 31
31007 202 2 3 32
31007 207 2 3 32
31007 211 2 3 33
31007 221 2 3 33
31007 226 2 3 34
31007 230 2 3 34
31007 234 2 3 35
31007 238 2 3 35
31007 241 3 3 36
31007 251 3 3 36
31007 252 3 3 37
31007 252 3 3 37
31007 256 3 3 38
31007 259 3 3 38
31007 259 3 3 39
31007 262 3 3 40
31007 262 3 3 41
31007 263 3 3 42
31007 263 3 3 43
31007 268 3 3 44
31007 270 3 3 45
31007 271 3 3 46
31007 272 3 4 47
31007 275 3 4 48
31007 282 3 4 49
31007 283 3 4 50
31007 283 3 4 51
31007 286 3 4 52
31007 288 3 4 53
31007 294 3 4 54
31007 294 3 4 55
31007 307 3 4 56
31007 308 3 4 57
31007 325 3 4 58
31007 329 3 4 59
31007 339 3 4 60
31007 339 3 4 61
31007 347 3 4 62
31007 350 3 4 63
31007 351 3 4 64
31007 352 3 4 65
31007 354 3 4 66
31007 372 4 4 67
31007 382 4 4 68
31007 388 4 4 69
31007 392 4 4 70
31007 397 4 4 71
31007 415 4 4 72
31007 447 4 4 73
31007 462 4 5 74
31007 475 4 5 75
31007 507 4 5 76
31007 508 4 5 77
31007 518 4 5 78
31007 528 4 5 79
31007 531 4 5 80
31007 531 4 5 81
31007 625 4 5 82
31007 650 4 5 83
31007 782 4 5 84
31007 821 4 5 85
31007 980 4 5 86
31007 1200 4 5 87

     观察查询的结果，你可以看到 percentile  列并不像你期望的那样去计算。如果你只有两条记录，你测试每一行所占的百分位时，你可能期望一条数据定义第一个百分位，第二条数据定义第100个百分位。当使用 NTILE 函数时，你实际看到的是一条数据在第一个百分位，另一条数据在第二个百分位。在 start_station_num 31000的分组中的结果中你可以看到这种情况—— percentile 列看起来就像数字排名。如果你向下滚动到 start_station_num 31007，你会看到它恰当的计算了每一行的百分位，因为那个 terminal 分组中有超过100条的数据。如果你在处理较小的窗口，记住这点，考虑四分或者差不多小的范围。

  • LAG 和 LEAD

    比对当前行和前面或后面行的情况 上面的函数是经常用到的，尤其是你的数据是排过序的。你可以使用 LAG 和 LEAD 去生成列，列值从其他行中拉取——你所需做的就是输入你要从哪列拉取数据和你要从多少行远的位置拉取。 LAG :从前面的行中拉取， LEAD 从后面的行中拉取：

 

SELECT start_station_num,
       duration,
       LAG(duration,1) OVER(PARTITION  BY
        start_station_num ORDER BY duration) 
       AS lag,
       LEAD(duration,1) OVER(PARTITION  BY
        start_station_num ORDER BY duration) 
       AS  lead
FROM biketrip
WHERE start_dt < '2012-01-08'
ORDER BY start_station_num,duration

 

start_station_num duration lag lead
31000 74 NULL 277
31000 277 74 291
31000 291 277 348
31000 348 291 387
31000 387 348 393
31000 393 387 398
31000 398 393 399
31000 399 398 412
31000 412 399 414
31000 414 412 424
31000 424 414 447
31000 447 424 520
31000 520 447 1422
31000 1422 520 2661
31000 2661 1422 3340
31000 3340 2661 NULL
31001 120 NULL 138
31001 138 120 138
31001 138 138 155
31001 155 138 155
31001 155 155 156
31001 156 155 160
31001 160 156 162
31001 162 160 173
31001 173 162 179
31001 179 173 180
31001 180 179 181
31001 181 180 186
31001 186 181 186
31001 186 186 188
31001 188 186 191
31001 191 188 202
31001 202 191 204
31001 204 202 212
31001 212 204 214
31001 214 212 220
31001 220 214 222
31001 222 220 222
31001 222 222 245
31001 245 222 245
31001 245 245 256
31001 256 245 261
31001 261 256 269
31001 269 261 286
31001 286 269 294
31001 294 286 294
31001 294 294 303
31001 303 294 304
31001 304 303 317
31001 317 304 331
31001 331 317 338
31001 338 331 395
31001 395 338 448
31001 448 395 501
31001 501 448 679
31001 679 501 738
31001 738 679 773
31001 773 738 800
31001 800 773 992
31001 992 800 1099
31001 1099 992 1103
31001 1103 1099 1157
31001 1157 1103 1168
31001 1168 1157 1264
31001 1264 1168 1426
31001 1426 1264 1740
31001 1740 1426 1747
31001 1747 1740 2686
31001 2686 1747 2804
31001 2804 2686 2876
31001 2876 2804 3598
31001 3598 2876 3624
31001 3624 3598 NULL
31002 70 NULL 71
31002 71 70 86
31002 86 71 90
31002 90 86 94
31002 94 90 104
31002 104 94 120
31002 120 104 150
31002 150 120 170
31002 170 150 197
31002 197 170 213
31002 213 197 252
31002 252 213 297
31002 297 252 305
31002 305 297 360
31002 360 305 378
31002 378 360 422
31002 422 378 450
31002 450 422 462
31002 462 450 568
31002 568 462 578
31002 578 568 661
31002 661 578 676
31002 676 661 787
31002 787 676 974
31002 974 787 1015
31002 1015 974 1202
31002 1202 1015 1359
31002 1359 1202 1367
31002 1367 1359 1373
31002 1373 1367 1441
31002 1441 1373 1462
31002 1462 1441 1478
31002 1478 1462 1482
31002 1482 1478 1491
31002 1491 1482 1512
31002 1512 1491 1523
31002 1523 1512 1585
31002 1585 1523 2050
31002 2050 1585 2457
31002 2457 2050 2540
31002 2540 2457 2965
31002 2965 2540 3794
31002 3794 2965 5113
31002 5113 3794 41427
31002 41427 5113 NULL
31003 82 NULL 96
31003 96 82 104
31003 104 96 108
31003 108 104 108
31003 108 108 134
31003 134 108 147
31003 147 134 179
31003 179 147 190
31003 190 179 191
31003 191 190 243
31003 243 191 250
31003 250 243 268
31003 268 250 276
31003 276 268 290
31003 290 276 308
31003 308 290 336
31003 336 308 337
31003 337 336 351
31003 351 337 494
31003 494 351 598
31003 598 494 1204
31003 1204 598 1262
31003 1262 1204 1540
31003 1540 1262 1573
31003 1573 1540 2008
31003 2008 1573 2034
31003 2034 2008 2063
31003 2063 2034 2128
31003 2128 2063 2858
31003 2858 2128 3563
31003 3563 2858 3581
31003 3581 3563 4441
31003 4441 3581 NULL
31004 116 NULL 123
31004 123 116 127
31004 127 123 145
31004 145 127 146
31004 146 145 151
31004 151 146 164
31004 164 151 172
31004 172 164 204
31004 204 172 214
31004 214 204 225
31004 225 214 226
31004 226 225 263
31004 263 226 264
31004 264 263 400
31004 400 264 589
31004 589 400 1216
31004 1216 589 1604
31004 1604 1216 NULL
31005 128 NULL 140
31005 140 128 166
31005 166 140 261
31005 261 166 369
31005 369 261 370
31005 370 369 404
31005 404 370 445
31005 445 404 474
31005 474 445 656
31005 656 474 711
31005 711 656 720
31005 720 711 1761
31005 1761 720 1768
31005 1768 1761 1922
31005 1922 1768 1948
31005 1948 1922 2038
31005 2038 1948 2055
31005 2055 2038 2925
31005 2925 2055 3242
31005 3242 2925 3534
31005 3534 3242 NULL
31006 271 NULL 289
31006 289 271 336
31006 336 289 362
31006 362 336 424
31006 424 362 426
31006 426 424 433
31006 433 426 512
31006 512 433 512
31006 512 512 549
31006 549 512 626
31006 626 549 725
31006 725 626 853
31006 853 725 939
31006 939 853 959
31006 959 939 1056
31006 1056 959 1169
31006 1169 1056 1244
31006 1244 1169 1267
31006 1267 1244 1269
31006 1269 1267 1320
31006 1320 1269 1468
31006 1468 1320 1489
31006 1489 1468 1499
31006 1499 1489 2463
31006 2463 1499 2472
31006 2472 2463 3009
31006 3009 2472 NULL
31007 91 NULL 103
31007 103 91 104
31007 104 103 109
31007 109 104 115
31007 115 109 117
31007 117 115 120
31007 120 117 121
31007 121 120 124
31007 124 121 130
31007 130 124 133
31007 133 130 133
31007 133 133 137
31007 137 133 138
31007 138 137 139
31007 139 138 140
31007 140 139 140
31007 140 140 141
31007 141 140 145
31007 145 141 145
31007 145 145 146
31007 146 145 146
31007 146 146 146
31007 146 146 151
31007 151 146 153
31007 153 151 155
31007 155 153 158
31007 158 155 160
31007 160 158 160
31007 160 160 162
31007 162 160 162
31007 162 162 162
31007 162 162 163
31007 163 162 163
31007 163 163 164
31007 164 163 164
31007 164 164 167
31007 167 164 168
31007 168 167 169
31007 169 168 171
31007 171 169 171
31007 171 171 172
31007 172 171 172
31007 172 172 172
31007 172 172 173
31007 173 172 174
31007 174 173 175
31007 175 174 177
31007 177 175 180
31007 180 177 181
31007 181 180 182
31007 182 181 183
31007 183 182 185
31007 185 183 186
31007 186 185 189
31007 189 186 189
31007 189 189 191
31007 191 189 193
31007 193 191 193
31007 193 193 195
31007 195 193 196
31007 196 195 200
31007 200 196 202
31007 202 200 207
31007 207 202 211
31007 211 207 221
31007 221 211 226
31007 226 221 230
31007 230 226 234
31007 234 230 238
31007 238 234 241
31007 241 238 251
31007 251 241 252
31007 252 251 252
31007 252 252 256
31007 256 252 259
31007 259 256 259
31007 259 259 262
31007 262 259 262
31007 262 262 263
31007 263 262 263
31007 263 263 268
31007 268 263 270
31007 270 268 271
31007 271 270 272
31007 272 271 275
31007 275 272 282
31007 282 275 283
31007 283 282 283
31007 283 283 286
31007 286 283 288
31007 288 286 294
31007 294 288 294
31007 294 294 307
31007 307 294 308
31007 308 307 325
31007 325 308 329
31007 329 325 339
31007 339 329 339
31007 339 339 347
31007 347 339 350
31007 350 347 351
31007 351 350 352
31007 352 351 354
31007 354 352 372
31007 372 354 382
31007 382 372 388
31007 388 382 392
31007 392 388 397
31007 397 392 415
31007 415 397 447
31007 447 415 462
31007 462 447 475
31007 475 462 507
31007 507 475 508
31007 508 507 518
31007 518 508 528
31007 528 518 531
31007 531 528 531
31007 531 531 625
31007 625 531 650
31007 650 625 782
31007 782 650 821
31007 821 782 980
31007 980 821 1200
31007 1200 980 1222
31007 1222 1200 1243
31007 1243 1222 1251
31007 1251 1243 1272
31007 1272 1251 1379
31007 1379 1272 1390
31007 1390 1379 1401
31007 1401 1390 1403

 如果要想要计算行间的差值，这个函数是非常有用的：

 

SELECT start_station_num,
       duration,
       duration  - LAG(duration,1)  
       OVER(PARTITION  BY start_station_num ORDER BY  
       duration) AS difference
FROM biketrip
WHERE start_dt < '2012-01-08'
ORDER BY start_station_num,duration

 

start_station_num duration difference
31000 74 NULL
31000 277 203
31000 291 14
31000 348 57
31000 387 39
31000 393 6
31000 398 5
31000 399 1
31000 412 13
31000 414 2
31000 424 10
31000 447 23
31000 520 73
31000 1422 902
31000 2661 1239
31000 3340 679
31001 120 NULL
31001 138 18
31001 138 0
31001 155 17
31001 155 0
31001 156 1
31001 160 4
31001 162 2
31001 173 11
31001 179 6
31001 180 1
31001 181 1
31001 186 5
31001 186 0
31001 188 2
31001 191 3
31001 202 11
31001 204 2
31001 212 8
31001 214 2
31001 220 6
31001 222 2
31001 222 0
31001 245 23
31001 245 0
31001 256 11
31001 261 5
31001 269 8
31001 286 17
31001 294 8
31001 294 0
31001 303 9
31001 304 1
31001 317 13
31001 331 14
31001 338 7
31001 395 57
31001 448 53
31001 501 53
31001 679 178
31001 738 59
31001 773 35
31001 800 27
31001 992 192
31001 1099 107
31001 1103 4
31001 1157 54
31001 1168 11
31001 1264 96
31001 1426 162
31001 1740 314
31001 1747 7
31001 2686 939
31001 2804 118
31001 2876 72
31001 3598 722
31001 3624 26
31002 70 NULL
31002 71 1
31002 86 15
31002 90 4
31002 94 4
31002 104 10
31002 120 16
31002 150 30
31002 170 20
31002 197 27
31002 213 16
31002 252 39
31002 297 45
31002 305 8
31002 360 55
31002 378 18
31002 422 44
31002 450 28
31002 462 12
31002 568 106
31002 578 10
31002 661 83
31002 676 15
31002 787 111
31002 974 187
31002 1015 41
31002 1202 187
31002 1359 157
31002 1367 8
31002 1373 6
31002 1441 68
31002 1462 21
31002 1478 16
31002 1482 4
31002 1491 9
31002 1512 21
31002 1523 11
31002 1585 62
31002 2050 465
31002 2457 407
31002 2540 83
31002 2965 425
31002 3794 829
31002 5113 1319
31002 41427 36314
31003 82 NULL
31003 96 14
31003 104 8
31003 108 4
31003 108 0
31003 134 26
31003 147 13
31003 179 32
31003 190 11
31003 191 1
31003 243 52
31003 250 7
31003 268 18
31003 276 8
31003 290 14
31003 308 18
31003 336 28
31003 337 1
31003 351 14
31003 494 143
31003 598 104
31003 1204 606
31003 1262 58
31003 1540 278
31003 1573 33
31003 2008 435
31003 2034 26
31003 2063 29
31003 2128 65
31003 2858 730
31003 3563 705
31003 3581 18
31003 4441 860
31004 116 NULL
31004 123 7
31004 127 4
31004 145 18
31004 146 1
31004 151 5
31004 164 13
31004 172 8
31004 204 32
31004 214 10
31004 225 11
31004 226 1
31004 263 37
31004 264 1
31004 400 136
31004 589 189
31004 1216 627
31004 1604 388
31005 128 NULL
31005 140 12
31005 166 26
31005 261 95
31005 369 108
31005 370 1
31005 404 34
31005 445 41
31005 474 29
31005 656 182
31005 711 55
31005 720 9
31005 1761 1041
31005 1768 7
31005 1922 154
31005 1948 26
31005 2038 90
31005 2055 17
31005 2925 870
31005 3242 317
31005 3534 292
31006 271 NULL
31006 289 18
31006 336 47
31006 362 26
31006 424 62
31006 426 2
31006 433 7
31006 512 79
31006 512 0
31006 549 37
31006 626 77
31006 725 99
31006 853 128
31006 939 86
31006 959 20
31006 1056 97
31006 1169 113
31006 1244 75
31006 1267 23
31006 1269 2
31006 1320 51
31006 1468 148
31006 1489 21
31006 1499 10
31006 2463 964
31006 2472 9
31006 3009 537
31007 91 NULL
31007 103 12
31007 104 1
31007 109 5
31007 115 6
31007 117 2
31007 120 3
31007 121 1
31007 124 3
31007 130 6
31007 133 3
31007 133 0
31007 137 4
31007 138 1
31007 139 1
31007 140 1
31007 140 0
31007 141 1
31007 145 4
31007 145 0
31007 146 1
31007 146 0
31007 146 0
31007 151 5
31007 153 2
31007 155 2
31007 158 3
31007 160 2
31007 160 0
31007 162 2
31007 162 0
31007 162 0
31007 163 1
31007 163 0
31007 164 1
31007 164 0
31007 167 3

第一行的差值列的值是 null ，因为没有要拉取的前一行。同样的，如果使用 LEAD 会在数据集的最后产生 null 。如果你想使结果更清晰一点，你可以将上面的查询包裹在一个外部查询中以去除 null 值： 

SELECT * FROM 
(
SELECT start_station_num,
       duration,
       duration  - LAG(duration,1)  
       OVER(PARTITION  BY start_station_num ORDER BY  
       duration) AS difference
FROM biketrip
WHERE start_dt < '2012-01-08'
ORDER BY start_station_num,duration
) T
WHERE T.difference IS NOT NULL

 

start_station_num duration difference
31000 277 203
31000 291 14
31000 348 57
31000 387 39
31000 393 6
31000 398 5
31000 399 1
31000 412 13
31000 414 2
31000 424 10
31000 447 23
31000 520 73
31000 1422 902
31000 2661 1239
31000 3340 679
31001 138 18
31001 138 0
31001 155 17
31001 155 0
31001 156 1
31001 160 4
31001 162 2
31001 173 11
31001 179 6
31001 180 1
31001 181 1
31001 186 5
31001 186 0
31001 188 2
31001 191 3
31001 202 11
31001 204 2
31001 212 8
31001 214 2
31001 220 6
31001 222 2
31001 222 0
31001 245 23
31001 245 0
31001 256 11
31001 261 5
31001 269 8
31001 286 17
31001 294 8
31001 294 0
31001 303 9
31001 304 1
31001 317 13
31001 331 14
31001 338 7
31001 395 57
31001 448 53
31001 501 53
31001 679 178
31001 738 59
31001 773 35
31001 800 27
31001 992 192
31001 1099 107
31001 1103 4
31001 1157 54
31001 1168 11
31001 1264 96
31001 1426 162
31001 1740 314
31001 1747 7
31001 2686 939
31001 2804 118
31001 2876 72
31001 3598 722
31001 3624 26
31002 71 1
31002 86 15
31002 90 4
31002 94 4
31002 104 10
31002 120 16
31002 150 30
31002 170 20
31002 197 27
31002 213 16
31002 252 39
31002 297 45
31002 305 8
31002 360 55
31002 378 18
31002 422 44
31002 450 28
31002 462 12
31002 568 106
31002 578 10
31002 661 83
31002 676 15
31002 787 111
31002 974 187
31002 1015 41
31002 1202 187
31002 1359 157
31002 1367 8
31002 1373 6
31002 1441 68
31002 1462 21
31002 1478 16
31002 1482 4
31002 1491 9
31002 1512 21
31002 1523 11
31002 1585 62
31002 2050 465
31002 2457 407
31002 2540 83
31002 2965 425
31002 3794 829
31002 5113 1319
31002 41427 36314
31003 96 14
31003 104 8
31003 108 4
31003 108 0
31003 134 26
31003 147 13
31003 179 32
31003 190 11
31003 191 1
31003 243 52
31003 250 7
31003 268 18
31003 276 8
31003 290 14
31003 308 18
31003 336 28
31003 337 1
31003 351 14
31003 494 143
31003 598 104
31003 1204 606
31003 1262 58
31003 1540 278
31003 1573 33
31003 2008 435
31003 2034 26
31003 2063 29
31003 2128 65
31003 2858 730
31003 3563 705
31003 3581 18
31003 4441 860
31004 123 7
31004 127 4
31004 145 18
31004 146 1
31004 151 5
31004 164 13
31004 172 8
31004 204 32
31004 214 10
31004 225 11
31004 226 1
31004 263 37
31004 264 1
31004 400 136
31004 589 189
31004 1216 627
31004 1604 388
31005 140 12
31005 166 26
31005 261 95
31005 369 108
31005 370 1
31005 404 34
31005 445 41
31005 474 29
31005 656 182
31005 711 55
31005 720 9
31005 1761 1041
31005 1768 7
31005 1922 154
31005 1948 26
31005 2038 90
31005 2055 17
31005 2925 870
31005 3242 317
31005 3534 292
31006 289 18
31006 336 47
31006 362 26
31006 424 62
31006 426 2
31006 433 7
31006 512 79
31006 512 0
31006 549 37
31006 626 77
31006 725 99
31006 853 128
31006 939 86
31006 959 20
31006 1056 97
31006 1169 113
31006 1244 75
31006 1267 23
31006 1269 2
31006 1320 51
31006 1468 148
31006 1489 21
31006 1499 10
31006 2463 964
31006 2472 9
31006 3009 537
31007 103 12
31007 104 1
31007 109 5
31007 115 6
31007 117 2
31007 120 3
31007 121 1
31007 124 3
31007 130 6
31007 133 3
31007 133 0
31007 137 4
31007 138 1
31007 139 1
31007 140 1
31007 140 0
31007 141 1
31007 145 4
31007 145 0
31007 146 1
31007 146 0
31007 146 0
31007 151 5
31007 153 2
31007 155 2
31007 158 3
31007 160 2
31007 160 0
31007 162 2
31007 162 0
31007 162 0
31007 163 1
31007 163 0
31007 164 1
31007 164 0
31007 167 3
31007 168 1
31007 169 1
31007 171 2
31007 171 0
31007 172 1
31007 172 0
31007 172 0
31007 173 1
31007 174 1
31007 175 1
31007 177 2
31007 180 3
31007 181 1
31007 182 1
31007 183 1
31007 185 2
31007 186 1
31007 189 3
31007 189 0
31007 191 2
31007 193 2
31007 193 0
31007 195 2
31007 196 1
31007 200 4
31007 202 2
31007 207 5
31007 211 4
31007 221 10
31007 226 5
31007 230 4
31007 234 4
31007 238 4
31007 241 3
31007 251 10
31007 252 1
31007 252 0
31007 256 4
31007 259 3
31007 259 0
31007 262 3
31007 262 0
31007 263 1
31007 263 0
31007 268 5
31007 270 2
31007 271 1
31007 272 1
31007 275 3
31007 282 7
31007 283 1
31007 283 0
31007 286 3
31007 288 2
31007 294 6
31007 294 0
31007 307 13
31007 308 1
31007 325 17
31007 329 4
31007 339 10
31007 339 0
31007 347 8
31007 350 3
31007 351 1
31007 352 1
31007 354 2
31007 372 18
31007 382 10
31007 388 6
31007 392 4
31007 397 5
31007 415 18
31007 447 32
31007 462 15
31007 475 13
31007 507 32
31007 508 1
31007 518 10
31007 528 10
31007 531 3
31007 531 0
31007 625 94
31007 650 25
31007 782 132
31007 821 39
31007 980 159
31007 1200 220
31007 1222 22
31007 1243 21
31007 1251 8
31007 1272 21
31007 1379 107
31007 1390 11
31007 1401 11
31007 1403 2
31007 1506 103
31007 1518 12
31007 1617 99
31007 1693 76
31007 2405 712
31008 169 17
31008 170 1
31008 176 6
31008 179 3
31008 187 8
31008 188 1
31008 194 6
31008 234 40
31008 235 1
31008 243 8
31008 244 1
31008 258 14
31008 269 11
31008 273 4
31008 275 2
31008 287 12
31008 293 6
31008 304 11
31008 323 19
31008 329 6
31008 362 33
31008 377 15
31008 415 38
31008 427 12
31008 438 11 
  • 定义窗口别名

    如果你打算在同一查询中用相同的窗口写多个开窗函数，你可以为窗口创建一个别名。拿上面 NTILE 的例子示范：

SELECT start_station_num,
       duration,
       NTILE(4) OVER(PARTITION  BY
       start_station_num ORDER BY duration) 
       AS quartile,
       NTILE(5) OVER(PARTITION  BY
       start_station_num ORDER BY duration) 
       AS quintile,
       NTILE(100) OVER(PARTITION  BY
       start_station_num ORDER BY duration) 
       AS percentile
FROM biketrip
WHERE start_dt < '2012-01-08'
ORDER BY start_station_num,duration

这个查询可以重写成这样：

SELECT start_station_num,
       duration,
       NTILE(4) OVER ntile_window       AS quartile,
       NTILE(5) OVER ntile_window       AS quintile,
       NTILE(100) OVER ntile_window     AS percentile
FROM biketrip
WHERE start_dt < '2012-01-08'
WINDOW ntile_window AS 
        (PARTITON BY start_station_num ORDER BY duration)
ORDER BY start_station_num,duration

 

 如果包含 WINDOW 子句，应该总是在 WHERE 子句后面。

 

  • 高级窗口技术

你可以在 Postgres documentation（见我的另一篇博客） 查看在Postgres数据库(Mode所用的语法规则）中开窗函数的完整的清单。如果你在连接数据库（译者注：可以使用Mode连接你的数据库）上使用开窗函数，你应该看一下针对你的系统的语法教程。

 

 

posted @ 2019-09-01 16:11  Hansenburg  阅读(704)  评论(0)  编辑  收藏  举报