window.out 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715
  1. --
  2. -- WINDOW FUNCTIONS
  3. --
  4. CREATE TEMPORARY TABLE empsalary (
  5. depname varchar,
  6. empno bigint,
  7. salary int,
  8. enroll_date date
  9. );
  10. INSERT INTO empsalary VALUES
  11. ('develop', 10, 5200, '2007-08-01'),
  12. ('sales', 1, 5000, '2006-10-01'),
  13. ('personnel', 5, 3500, '2007-12-10'),
  14. ('sales', 4, 4800, '2007-08-08'),
  15. ('personnel', 2, 3900, '2006-12-23'),
  16. ('develop', 7, 4200, '2008-01-01'),
  17. ('develop', 9, 4500, '2008-01-01'),
  18. ('sales', 3, 4800, '2007-08-01'),
  19. ('develop', 8, 6000, '2006-10-01'),
  20. ('develop', 11, 5200, '2007-08-15');
  21. -- empty window specification
  22. SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10;
  23. count
  24. -------
  25. 10
  26. 10
  27. 10
  28. 10
  29. 10
  30. 10
  31. 10
  32. 10
  33. 10
  34. 10
  35. (10 rows)
  36. SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS ();
  37. count
  38. -------
  39. 10
  40. 10
  41. 10
  42. 10
  43. 10
  44. 10
  45. 10
  46. 10
  47. 10
  48. 10
  49. (10 rows)
  50. -- no window operation
  51. SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten);
  52. four
  53. ------
  54. (0 rows)
  55. SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;
  56. row_number
  57. ------------
  58. 1
  59. 2
  60. 3
  61. 4
  62. 5
  63. 6
  64. 7
  65. 8
  66. 9
  67. 10
  68. (10 rows)
  69. SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
  70. count | four
  71. -------+------
  72. 4 | 1
  73. 4 | 1
  74. 4 | 1
  75. 4 | 1
  76. 2 | 3
  77. 2 | 3
  78. (6 rows)
  79. -- opexpr with different windows evaluation.
  80. SELECT * FROM(
  81. SELECT count(*) OVER (PARTITION BY four ORDER BY ten) +
  82. sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
  83. count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
  84. sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum
  85. FROM tenk1
  86. )sub
  87. WHERE total <> fourcount + twosum;
  88. total | fourcount | twosum
  89. -------+-----------+--------
  90. (0 rows)
  91. -- identical windows with different names
  92. SELECT sum(salary) OVER w1, count(*) OVER w2
  93. FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary);
  94. sum | count
  95. -------+-------
  96. 3500 | 1
  97. 7400 | 2
  98. 11600 | 3
  99. 16100 | 4
  100. 25700 | 6
  101. 25700 | 6
  102. 30700 | 7
  103. 41100 | 9
  104. 41100 | 9
  105. 47100 | 10
  106. (10 rows)
  107. -- empty table
  108. SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
  109. count
  110. -------
  111. (0 rows)
  112. -- window function with ORDER BY an expression involving aggregates (9.1 bug)
  113. select ten,
  114. sum(unique1) + sum(unique2) as res,
  115. rank() over (order by sum(unique1) + sum(unique2)) as rank
  116. from tenk1
  117. group by ten order by ten;
  118. ten | res | rank
  119. -----+----------+------
  120. 0 | 9976146 | 4
  121. 1 | 10114187 | 9
  122. 2 | 10059554 | 8
  123. 3 | 9878541 | 1
  124. 4 | 9881005 | 2
  125. 5 | 9981670 | 5
  126. 6 | 9947099 | 3
  127. 7 | 10120309 | 10
  128. 8 | 9991305 | 6
  129. 9 | 10040184 | 7
  130. (10 rows)
  131. SELECT sum(unique1) over (rows between current row and unbounded following),
  132. unique1, four
  133. FROM tenk1 WHERE unique1 < 10;
  134. sum | unique1 | four
  135. -----+---------+------
  136. 45 | 4 | 0
  137. 41 | 2 | 2
  138. 39 | 1 | 1
  139. 38 | 6 | 2
  140. 32 | 9 | 1
  141. 23 | 8 | 0
  142. 15 | 5 | 1
  143. 10 | 3 | 3
  144. 7 | 7 | 3
  145. 0 | 0 | 0
  146. (10 rows)
  147. SELECT sum(unique1) over (rows between 2 preceding and 2 following),
  148. unique1, four
  149. FROM tenk1 WHERE unique1 < 10;
  150. sum | unique1 | four
  151. -----+---------+------
  152. 7 | 4 | 0
  153. 13 | 2 | 2
  154. 22 | 1 | 1
  155. 26 | 6 | 2
  156. 29 | 9 | 1
  157. 31 | 8 | 0
  158. 32 | 5 | 1
  159. 23 | 3 | 3
  160. 15 | 7 | 3
  161. 10 | 0 | 0
  162. (10 rows)
  163. SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude no others),
  164. unique1, four
  165. FROM tenk1 WHERE unique1 < 10;
  166. sum | unique1 | four
  167. -----+---------+------
  168. 7 | 4 | 0
  169. 13 | 2 | 2
  170. 22 | 1 | 1
  171. 26 | 6 | 2
  172. 29 | 9 | 1
  173. 31 | 8 | 0
  174. 32 | 5 | 1
  175. 23 | 3 | 3
  176. 15 | 7 | 3
  177. 10 | 0 | 0
  178. (10 rows)
  179. SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
  180. unique1, four
  181. FROM tenk1 WHERE unique1 < 10;
  182. sum | unique1 | four
  183. -----+---------+------
  184. | 4 | 0
  185. 4 | 2 | 2
  186. 6 | 1 | 1
  187. 3 | 6 | 2
  188. 7 | 9 | 1
  189. 15 | 8 | 0
  190. 17 | 5 | 1
  191. 13 | 3 | 3
  192. 8 | 7 | 3
  193. 10 | 0 | 0
  194. (10 rows)
  195. SELECT sum(unique1) over (rows between 1 following and 3 following),
  196. unique1, four
  197. FROM tenk1 WHERE unique1 < 10;
  198. sum | unique1 | four
  199. -----+---------+------
  200. 9 | 4 | 0
  201. 16 | 2 | 2
  202. 23 | 1 | 1
  203. 22 | 6 | 2
  204. 16 | 9 | 1
  205. 15 | 8 | 0
  206. 10 | 5 | 1
  207. 7 | 3 | 3
  208. 0 | 7 | 3
  209. | 0 | 0
  210. (10 rows)
  211. SELECT sum(unique1) over (rows between unbounded preceding and 1 following),
  212. unique1, four
  213. FROM tenk1 WHERE unique1 < 10;
  214. sum | unique1 | four
  215. -----+---------+------
  216. 6 | 4 | 0
  217. 7 | 2 | 2
  218. 13 | 1 | 1
  219. 22 | 6 | 2
  220. 30 | 9 | 1
  221. 35 | 8 | 0
  222. 38 | 5 | 1
  223. 45 | 3 | 3
  224. 45 | 7 | 3
  225. 45 | 0 | 0
  226. (10 rows)
  227. -- Test in_range for other numeric datatypes
  228. create temp table numerics(
  229. id int,
  230. f_float4 float4,
  231. f_float8 float8,
  232. f_numeric numeric
  233. );
  234. insert into numerics values
  235. (0, '-infinity', '-infinity', '-infinity'),
  236. (1, -3, -3, -3),
  237. (2, -1, -1, -1),
  238. (3, 0, 0, 0),
  239. (4, 1.1, 1.1, 1.1),
  240. (5, 1.12, 1.12, 1.12),
  241. (6, 2, 2, 2),
  242. (7, 100, 100, 100),
  243. (8, 'infinity', 'infinity', 'infinity'),
  244. (9, 'NaN', 'NaN', 'NaN');
  245. -- Test in_range for other datetime datatypes
  246. create temp table datetimes(
  247. id int,
  248. f_time time,
  249. f_timetz timetz,
  250. f_interval interval,
  251. f_timestamptz timestamptz,
  252. f_timestamp timestamp
  253. );
  254. -- Show differences in offset interpretation between ROWS, RANGE, and GROUPS
  255. WITH cte (x) AS (
  256. SELECT * FROM generate_series(1, 35, 2)
  257. )
  258. SELECT x, (sum(x) over w)
  259. FROM cte
  260. WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following);
  261. x | sum
  262. ----+-----
  263. 1 | 4
  264. 3 | 9
  265. 5 | 15
  266. 7 | 21
  267. 9 | 27
  268. 11 | 33
  269. 13 | 39
  270. 15 | 45
  271. 17 | 51
  272. 19 | 57
  273. 21 | 63
  274. 23 | 69
  275. 25 | 75
  276. 27 | 81
  277. 29 | 87
  278. 31 | 93
  279. 33 | 99
  280. 35 | 68
  281. (18 rows)
  282. WITH cte (x) AS (
  283. select 1 union all select 1 union all select 1 union all
  284. SELECT * FROM generate_series(5, 49, 2)
  285. )
  286. SELECT x, (sum(x) over w)
  287. FROM cte
  288. WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following);
  289. x | sum
  290. ----+-----
  291. 1 | 2
  292. 1 | 3
  293. 1 | 7
  294. 5 | 13
  295. 7 | 21
  296. 9 | 27
  297. 11 | 33
  298. 13 | 39
  299. 15 | 45
  300. 17 | 51
  301. 19 | 57
  302. 21 | 63
  303. 23 | 69
  304. 25 | 75
  305. 27 | 81
  306. 29 | 87
  307. 31 | 93
  308. 33 | 99
  309. 35 | 105
  310. 37 | 111
  311. 39 | 117
  312. 41 | 123
  313. 43 | 129
  314. 45 | 135
  315. 47 | 141
  316. 49 | 96
  317. (26 rows)
  318. -- with UNION
  319. SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
  320. count
  321. -------
  322. (0 rows)
  323. -- check some degenerate cases
  324. create temp table t1 (f1 int, f2 int8);
  325. insert into t1 values (1,1),(1,2),(2,2);
  326. -- ordering by a non-integer constant is allowed
  327. SELECT rank() OVER (ORDER BY length('abc'));
  328. rank
  329. ------
  330. 1
  331. (1 row)
  332. SELECT * FROM rank() OVER (ORDER BY random());
  333. ERROR: syntax error at or near "ORDER"
  334. LINE 1: SELECT * FROM rank() OVER (ORDER BY random());
  335. ^
  336. SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
  337. ERROR: syntax error at or near "ORDER"
  338. LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te...
  339. ^
  340. -- cleanup
  341. DROP TABLE empsalary;
  342. --
  343. -- Test various built-in aggregates that have moving-aggregate support
  344. --
  345. -- test inverse transition functions handle NULLs properly
  346. SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  347. FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
  348. i | avg
  349. ---+--------------------
  350. 1 | 1.5000000000000000
  351. 2 | 2.0000000000000000
  352. 3 |
  353. 4 |
  354. (4 rows)
  355. SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  356. FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
  357. i | avg
  358. ---+--------------------
  359. 1 | 1.5000000000000000
  360. 2 | 2.0000000000000000
  361. 3 |
  362. 4 |
  363. (4 rows)
  364. SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  365. FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
  366. i | avg
  367. ---+--------------------
  368. 1 | 1.5000000000000000
  369. 2 | 2.0000000000000000
  370. 3 |
  371. 4 |
  372. (4 rows)
  373. SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  374. FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v);
  375. i | avg
  376. ---+--------------------
  377. 1 | 2.0000000000000000
  378. 2 | 2.5000000000000000
  379. 3 |
  380. 4 |
  381. (4 rows)
  382. SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  383. FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
  384. i | sum
  385. ---+-----
  386. 1 | 3
  387. 2 | 2
  388. 3 |
  389. 4 |
  390. (4 rows)
  391. SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  392. FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
  393. i | sum
  394. ---+-----
  395. 1 | 3
  396. 2 | 2
  397. 3 |
  398. 4 |
  399. (4 rows)
  400. SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  401. FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
  402. i | sum
  403. ---+-----
  404. 1 | 3
  405. 2 | 2
  406. 3 |
  407. 4 |
  408. (4 rows)
  409. SELECT i,SUM(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  410. FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v);
  411. i | sum
  412. ---+-----
  413. 1 | 3.3
  414. 2 | 2.2
  415. 3 |
  416. 4 |
  417. (4 rows)
  418. SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  419. FROM (VALUES(1,1.01),(2,2),(3,3)) v(i,n);
  420. sum
  421. ------
  422. 6.01
  423. 5
  424. 3
  425. (3 rows)
  426. SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  427. FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
  428. i | count
  429. ---+-------
  430. 1 | 4
  431. 2 | 3
  432. 3 | 2
  433. 4 | 1
  434. (4 rows)
  435. SELECT VAR_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  436. FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  437. var_samp
  438. -----------------------
  439. 27130.000000000000
  440. 18491.666666666667
  441. 16900.000000000000
  442. 8450.0000000000000000
  443. (5 rows)
  444. SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  445. FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  446. var_samp
  447. -----------------------
  448. 27130.000000000000
  449. 18491.666666666667
  450. 16900.000000000000
  451. 8450.0000000000000000
  452. (5 rows)
  453. SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  454. FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  455. var_samp
  456. -----------------------
  457. 27130.000000000000
  458. 18491.666666666667
  459. 16900.000000000000
  460. 8450.0000000000000000
  461. (5 rows)
  462. SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  463. FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  464. var_samp
  465. -----------------------
  466. 27130.000000000000
  467. 18491.666666666667
  468. 16900.000000000000
  469. 8450.0000000000000000
  470. (5 rows)
  471. SELECT VARIANCE(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  472. FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  473. variance
  474. -----------------------
  475. 27130.000000000000
  476. 18491.666666666667
  477. 16900.000000000000
  478. 8450.0000000000000000
  479. (5 rows)
  480. SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  481. FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  482. variance
  483. -----------------------
  484. 27130.000000000000
  485. 18491.666666666667
  486. 16900.000000000000
  487. 8450.0000000000000000
  488. (5 rows)
  489. SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  490. FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  491. variance
  492. -----------------------
  493. 27130.000000000000
  494. 18491.666666666667
  495. 16900.000000000000
  496. 8450.0000000000000000
  497. (5 rows)
  498. SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  499. FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  500. variance
  501. -----------------------
  502. 27130.000000000000
  503. 18491.666666666667
  504. 16900.000000000000
  505. 8450.0000000000000000
  506. (5 rows)
  507. SELECT STDDEV_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  508. FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
  509. stddev_samp
  510. ---------------------
  511. 164.711869639076
  512. 164.711869639076
  513. 135.984067694222
  514. 130.000000000000
  515. 91.9238815542511782
  516. (6 rows)
  517. SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  518. FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
  519. stddev_samp
  520. ---------------------
  521. 164.711869639076
  522. 164.711869639076
  523. 135.984067694222
  524. 130.000000000000
  525. 91.9238815542511782
  526. (6 rows)
  527. SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  528. FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
  529. stddev_samp
  530. ---------------------
  531. 164.711869639076
  532. 164.711869639076
  533. 135.984067694222
  534. 130.000000000000
  535. 91.9238815542511782
  536. (6 rows)
  537. SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  538. FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
  539. stddev_samp
  540. ---------------------
  541. 164.711869639076
  542. 164.711869639076
  543. 135.984067694222
  544. 130.000000000000
  545. 91.9238815542511782
  546. (6 rows)
  547. SELECT STDDEV(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  548. FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  549. stddev
  550. ---------------------
  551. 164.711869639076
  552. 164.711869639076
  553. 135.984067694222
  554. 130.000000000000
  555. 91.9238815542511782
  556. (6 rows)
  557. SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  558. FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  559. stddev
  560. ---------------------
  561. 164.711869639076
  562. 164.711869639076
  563. 135.984067694222
  564. 130.000000000000
  565. 91.9238815542511782
  566. (6 rows)
  567. SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  568. FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  569. stddev
  570. ---------------------
  571. 164.711869639076
  572. 164.711869639076
  573. 135.984067694222
  574. 130.000000000000
  575. 91.9238815542511782
  576. (6 rows)
  577. SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  578. FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
  579. stddev
  580. ---------------------
  581. 164.711869639076
  582. 164.711869639076
  583. 135.984067694222
  584. 130.000000000000
  585. 91.9238815542511782
  586. (6 rows)
  587. -- test that inverse transition functions work with various frame options
  588. SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
  589. FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
  590. i | sum
  591. ---+-----
  592. 1 | 1
  593. 2 | 2
  594. 3 |
  595. 4 |
  596. (4 rows)
  597. SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
  598. FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
  599. i | sum
  600. ---+-----
  601. 1 | 3
  602. 2 | 2
  603. 3 |
  604. 4 |
  605. (4 rows)
  606. SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  607. FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v);
  608. i | sum
  609. ---+-----
  610. 1 | 3
  611. 2 | 6
  612. 3 | 9
  613. 4 | 7
  614. (4 rows)
  615. -- ensure aggregate over numeric properly recovers from NaN values
  616. SELECT a, b,
  617. SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
  618. FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b);
  619. a | b | sum
  620. ---+-----+-----
  621. 1 | 1 | 1
  622. 2 | 2 | 3
  623. 3 | NaN | NaN
  624. 4 | 3 | NaN
  625. 5 | 4 | 7
  626. (5 rows)
  627. -- It might be tempting for someone to add an inverse trans function for
  628. -- float and double precision. This should not be done as it can give incorrect
  629. -- results. This test should fail if anyone ever does this without thinking too
  630. -- hard about it.
  631. SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),'999999999999999999999D9')
  632. FROM (VALUES(1,1e20),(2,1)) n(i,n);
  633. to_char
  634. --------------------------
  635. 100000000000000000000
  636. 1.0
  637. (2 rows)
  638. SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
  639. FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b)
  640. WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);
  641. i | b | bool_and | bool_or
  642. ---+---+----------+---------
  643. 1 | t | t | t
  644. 2 | t | f | t
  645. 3 | f | f | f
  646. 4 | f | f | t
  647. 5 | t | t | t
  648. (5 rows)