123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715 |
- --
- -- WINDOW FUNCTIONS
- --
- CREATE TEMPORARY TABLE empsalary (
- depname varchar,
- empno bigint,
- salary int,
- enroll_date date
- );
- INSERT INTO empsalary VALUES
- ('develop', 10, 5200, '2007-08-01'),
- ('sales', 1, 5000, '2006-10-01'),
- ('personnel', 5, 3500, '2007-12-10'),
- ('sales', 4, 4800, '2007-08-08'),
- ('personnel', 2, 3900, '2006-12-23'),
- ('develop', 7, 4200, '2008-01-01'),
- ('develop', 9, 4500, '2008-01-01'),
- ('sales', 3, 4800, '2007-08-01'),
- ('develop', 8, 6000, '2006-10-01'),
- ('develop', 11, 5200, '2007-08-15');
- -- empty window specification
- SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10;
- count
- -------
- 10
- 10
- 10
- 10
- 10
- 10
- 10
- 10
- 10
- 10
- (10 rows)
- SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS ();
- count
- -------
- 10
- 10
- 10
- 10
- 10
- 10
- 10
- 10
- 10
- 10
- (10 rows)
- -- no window operation
- SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten);
- four
- ------
- (0 rows)
- SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;
- row_number
- ------------
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- (10 rows)
- SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
- count | four
- -------+------
- 4 | 1
- 4 | 1
- 4 | 1
- 4 | 1
- 2 | 3
- 2 | 3
- (6 rows)
- -- opexpr with different windows evaluation.
- SELECT * FROM(
- SELECT count(*) OVER (PARTITION BY four ORDER BY ten) +
- sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
- count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
- sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum
- FROM tenk1
- )sub
- WHERE total <> fourcount + twosum;
- total | fourcount | twosum
- -------+-----------+--------
- (0 rows)
- -- identical windows with different names
- SELECT sum(salary) OVER w1, count(*) OVER w2
- FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary);
- sum | count
- -------+-------
- 3500 | 1
- 7400 | 2
- 11600 | 3
- 16100 | 4
- 25700 | 6
- 25700 | 6
- 30700 | 7
- 41100 | 9
- 41100 | 9
- 47100 | 10
- (10 rows)
- -- empty table
- SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
- count
- -------
- (0 rows)
- -- window function with ORDER BY an expression involving aggregates (9.1 bug)
- select ten,
- sum(unique1) + sum(unique2) as res,
- rank() over (order by sum(unique1) + sum(unique2)) as rank
- from tenk1
- group by ten order by ten;
- ten | res | rank
- -----+----------+------
- 0 | 9976146 | 4
- 1 | 10114187 | 9
- 2 | 10059554 | 8
- 3 | 9878541 | 1
- 4 | 9881005 | 2
- 5 | 9981670 | 5
- 6 | 9947099 | 3
- 7 | 10120309 | 10
- 8 | 9991305 | 6
- 9 | 10040184 | 7
- (10 rows)
- SELECT sum(unique1) over (rows between current row and unbounded following),
- unique1, four
- FROM tenk1 WHERE unique1 < 10;
- sum | unique1 | four
- -----+---------+------
- 45 | 4 | 0
- 41 | 2 | 2
- 39 | 1 | 1
- 38 | 6 | 2
- 32 | 9 | 1
- 23 | 8 | 0
- 15 | 5 | 1
- 10 | 3 | 3
- 7 | 7 | 3
- 0 | 0 | 0
- (10 rows)
- SELECT sum(unique1) over (rows between 2 preceding and 2 following),
- unique1, four
- FROM tenk1 WHERE unique1 < 10;
- sum | unique1 | four
- -----+---------+------
- 7 | 4 | 0
- 13 | 2 | 2
- 22 | 1 | 1
- 26 | 6 | 2
- 29 | 9 | 1
- 31 | 8 | 0
- 32 | 5 | 1
- 23 | 3 | 3
- 15 | 7 | 3
- 10 | 0 | 0
- (10 rows)
- SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude no others),
- unique1, four
- FROM tenk1 WHERE unique1 < 10;
- sum | unique1 | four
- -----+---------+------
- 7 | 4 | 0
- 13 | 2 | 2
- 22 | 1 | 1
- 26 | 6 | 2
- 29 | 9 | 1
- 31 | 8 | 0
- 32 | 5 | 1
- 23 | 3 | 3
- 15 | 7 | 3
- 10 | 0 | 0
- (10 rows)
- SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
- unique1, four
- FROM tenk1 WHERE unique1 < 10;
- sum | unique1 | four
- -----+---------+------
- | 4 | 0
- 4 | 2 | 2
- 6 | 1 | 1
- 3 | 6 | 2
- 7 | 9 | 1
- 15 | 8 | 0
- 17 | 5 | 1
- 13 | 3 | 3
- 8 | 7 | 3
- 10 | 0 | 0
- (10 rows)
- SELECT sum(unique1) over (rows between 1 following and 3 following),
- unique1, four
- FROM tenk1 WHERE unique1 < 10;
- sum | unique1 | four
- -----+---------+------
- 9 | 4 | 0
- 16 | 2 | 2
- 23 | 1 | 1
- 22 | 6 | 2
- 16 | 9 | 1
- 15 | 8 | 0
- 10 | 5 | 1
- 7 | 3 | 3
- 0 | 7 | 3
- | 0 | 0
- (10 rows)
- SELECT sum(unique1) over (rows between unbounded preceding and 1 following),
- unique1, four
- FROM tenk1 WHERE unique1 < 10;
- sum | unique1 | four
- -----+---------+------
- 6 | 4 | 0
- 7 | 2 | 2
- 13 | 1 | 1
- 22 | 6 | 2
- 30 | 9 | 1
- 35 | 8 | 0
- 38 | 5 | 1
- 45 | 3 | 3
- 45 | 7 | 3
- 45 | 0 | 0
- (10 rows)
- -- Test in_range for other numeric datatypes
- create temp table numerics(
- id int,
- f_float4 float4,
- f_float8 float8,
- f_numeric numeric
- );
- insert into numerics values
- (0, '-infinity', '-infinity', '-infinity'),
- (1, -3, -3, -3),
- (2, -1, -1, -1),
- (3, 0, 0, 0),
- (4, 1.1, 1.1, 1.1),
- (5, 1.12, 1.12, 1.12),
- (6, 2, 2, 2),
- (7, 100, 100, 100),
- (8, 'infinity', 'infinity', 'infinity'),
- (9, 'NaN', 'NaN', 'NaN');
- -- Test in_range for other datetime datatypes
- create temp table datetimes(
- id int,
- f_time time,
- f_timetz timetz,
- f_interval interval,
- f_timestamptz timestamptz,
- f_timestamp timestamp
- );
- -- Show differences in offset interpretation between ROWS, RANGE, and GROUPS
- WITH cte (x) AS (
- SELECT * FROM generate_series(1, 35, 2)
- )
- SELECT x, (sum(x) over w)
- FROM cte
- WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following);
- x | sum
- ----+-----
- 1 | 4
- 3 | 9
- 5 | 15
- 7 | 21
- 9 | 27
- 11 | 33
- 13 | 39
- 15 | 45
- 17 | 51
- 19 | 57
- 21 | 63
- 23 | 69
- 25 | 75
- 27 | 81
- 29 | 87
- 31 | 93
- 33 | 99
- 35 | 68
- (18 rows)
- WITH cte (x) AS (
- select 1 union all select 1 union all select 1 union all
- SELECT * FROM generate_series(5, 49, 2)
- )
- SELECT x, (sum(x) over w)
- FROM cte
- WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following);
- x | sum
- ----+-----
- 1 | 2
- 1 | 3
- 1 | 7
- 5 | 13
- 7 | 21
- 9 | 27
- 11 | 33
- 13 | 39
- 15 | 45
- 17 | 51
- 19 | 57
- 21 | 63
- 23 | 69
- 25 | 75
- 27 | 81
- 29 | 87
- 31 | 93
- 33 | 99
- 35 | 105
- 37 | 111
- 39 | 117
- 41 | 123
- 43 | 129
- 45 | 135
- 47 | 141
- 49 | 96
- (26 rows)
- -- with UNION
- SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
- count
- -------
- (0 rows)
- -- check some degenerate cases
- create temp table t1 (f1 int, f2 int8);
- insert into t1 values (1,1),(1,2),(2,2);
- -- ordering by a non-integer constant is allowed
- SELECT rank() OVER (ORDER BY length('abc'));
- rank
- ------
- 1
- (1 row)
- SELECT * FROM rank() OVER (ORDER BY random());
- ERROR: syntax error at or near "ORDER"
- LINE 1: SELECT * FROM rank() OVER (ORDER BY random());
- ^
- SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
- ERROR: syntax error at or near "ORDER"
- LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te...
- ^
- -- cleanup
- DROP TABLE empsalary;
- --
- -- Test various built-in aggregates that have moving-aggregate support
- --
- -- test inverse transition functions handle NULLs properly
- SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
- i | avg
- ---+--------------------
- 1 | 1.5000000000000000
- 2 | 2.0000000000000000
- 3 |
- 4 |
- (4 rows)
- SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
- i | avg
- ---+--------------------
- 1 | 1.5000000000000000
- 2 | 2.0000000000000000
- 3 |
- 4 |
- (4 rows)
- SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
- i | avg
- ---+--------------------
- 1 | 1.5000000000000000
- 2 | 2.0000000000000000
- 3 |
- 4 |
- (4 rows)
- SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v);
- i | avg
- ---+--------------------
- 1 | 2.0000000000000000
- 2 | 2.5000000000000000
- 3 |
- 4 |
- (4 rows)
- SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
- i | sum
- ---+-----
- 1 | 3
- 2 | 2
- 3 |
- 4 |
- (4 rows)
- SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
- i | sum
- ---+-----
- 1 | 3
- 2 | 2
- 3 |
- 4 |
- (4 rows)
- SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
- i | sum
- ---+-----
- 1 | 3
- 2 | 2
- 3 |
- 4 |
- (4 rows)
- SELECT i,SUM(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v);
- i | sum
- ---+-----
- 1 | 3.3
- 2 | 2.2
- 3 |
- 4 |
- (4 rows)
- SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,1.01),(2,2),(3,3)) v(i,n);
- sum
- ------
- 6.01
- 5
- 3
- (3 rows)
- SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
- i | count
- ---+-------
- 1 | 4
- 2 | 3
- 3 | 2
- 4 | 1
- (4 rows)
- SELECT VAR_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- var_samp
- -----------------------
- 27130.000000000000
- 18491.666666666667
- 16900.000000000000
- 8450.0000000000000000
-
- (5 rows)
- SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- var_samp
- -----------------------
- 27130.000000000000
- 18491.666666666667
- 16900.000000000000
- 8450.0000000000000000
-
- (5 rows)
- SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- var_samp
- -----------------------
- 27130.000000000000
- 18491.666666666667
- 16900.000000000000
- 8450.0000000000000000
-
- (5 rows)
- SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- var_samp
- -----------------------
- 27130.000000000000
- 18491.666666666667
- 16900.000000000000
- 8450.0000000000000000
-
- (5 rows)
- SELECT VARIANCE(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- variance
- -----------------------
- 27130.000000000000
- 18491.666666666667
- 16900.000000000000
- 8450.0000000000000000
-
- (5 rows)
- SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- variance
- -----------------------
- 27130.000000000000
- 18491.666666666667
- 16900.000000000000
- 8450.0000000000000000
-
- (5 rows)
- SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- variance
- -----------------------
- 27130.000000000000
- 18491.666666666667
- 16900.000000000000
- 8450.0000000000000000
-
- (5 rows)
- SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- variance
- -----------------------
- 27130.000000000000
- 18491.666666666667
- 16900.000000000000
- 8450.0000000000000000
-
- (5 rows)
- SELECT STDDEV_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
- stddev_samp
- ---------------------
- 164.711869639076
- 164.711869639076
- 135.984067694222
- 130.000000000000
- 91.9238815542511782
-
- (6 rows)
- SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
- stddev_samp
- ---------------------
- 164.711869639076
- 164.711869639076
- 135.984067694222
- 130.000000000000
- 91.9238815542511782
-
- (6 rows)
- SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
- stddev_samp
- ---------------------
- 164.711869639076
- 164.711869639076
- 135.984067694222
- 130.000000000000
- 91.9238815542511782
-
- (6 rows)
- SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
- stddev_samp
- ---------------------
- 164.711869639076
- 164.711869639076
- 135.984067694222
- 130.000000000000
- 91.9238815542511782
-
- (6 rows)
- SELECT STDDEV(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- stddev
- ---------------------
- 164.711869639076
- 164.711869639076
- 135.984067694222
- 130.000000000000
- 91.9238815542511782
-
- (6 rows)
- SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- stddev
- ---------------------
- 164.711869639076
- 164.711869639076
- 135.984067694222
- 130.000000000000
- 91.9238815542511782
-
- (6 rows)
- SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- stddev
- ---------------------
- 164.711869639076
- 164.711869639076
- 135.984067694222
- 130.000000000000
- 91.9238815542511782
-
- (6 rows)
- SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
- FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
- stddev
- ---------------------
- 164.711869639076
- 164.711869639076
- 135.984067694222
- 130.000000000000
- 91.9238815542511782
-
- (6 rows)
- -- test that inverse transition functions work with various frame options
- SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
- FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
- i | sum
- ---+-----
- 1 | 1
- 2 | 2
- 3 |
- 4 |
- (4 rows)
- SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
- FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
- i | sum
- ---+-----
- 1 | 3
- 2 | 2
- 3 |
- 4 |
- (4 rows)
- SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
- FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v);
- i | sum
- ---+-----
- 1 | 3
- 2 | 6
- 3 | 9
- 4 | 7
- (4 rows)
- -- ensure aggregate over numeric properly recovers from NaN values
- SELECT a, b,
- SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
- FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b);
- a | b | sum
- ---+-----+-----
- 1 | 1 | 1
- 2 | 2 | 3
- 3 | NaN | NaN
- 4 | 3 | NaN
- 5 | 4 | 7
- (5 rows)
- -- It might be tempting for someone to add an inverse trans function for
- -- float and double precision. This should not be done as it can give incorrect
- -- results. This test should fail if anyone ever does this without thinking too
- -- hard about it.
- SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),'999999999999999999999D9')
- FROM (VALUES(1,1e20),(2,1)) n(i,n);
- to_char
- --------------------------
- 100000000000000000000
- 1.0
- (2 rows)
- SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
- FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b)
- WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);
- i | b | bool_and | bool_or
- ---+---+----------+---------
- 1 | t | t | t
- 2 | t | f | t
- 3 | f | f | f
- 4 | f | f | t
- 5 | t | t | t
- (5 rows)
|