# name: test/sql/window/test_naive_aggregation.test
# description: Test naive aggregation implementation
# group: [window]

statement ok
SET default_null_order='nulls_first';

statement ok
PRAGMA enable_verification

statement ok
PRAGMA debug_window_mode=separate

statement ok
CREATE TABLE empsalary (depname varchar, empno bigint, salary int, enroll_date date)

statement ok
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')

# basic example from postgres' window.sql
query TIIR
SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname ORDER BY empno) FROM empsalary ORDER BY depname, empno
----
develop	7	4200	4200.000000
develop	8	6000	10200.000000
develop	9	4500	14700.000000
develop	10	5200	19900.000000
develop	11	5200	25100.000000
personnel	2	3900	3900.000000
personnel	5	3500	7400.000000
sales	1	5000	5000.000000
sales	3	4800	9800.000000
sales	4	4800	14600.000000

# sum
query R
SELECT sum(salary) OVER (PARTITION BY depname ORDER BY salary) ss FROM empsalary ORDER BY depname, ss
----
4200.000000
8700.000000
19100.000000
19100.000000
25100.000000
3500.000000
7400.000000
9600.000000
9600.000000
14600.000000

# min/max/avg
query TIIR
SELECT depname, min(salary) OVER (PARTITION BY depname ORDER BY salary, empno) m1, max(salary) OVER (PARTITION BY depname ORDER BY salary, empno) m2, AVG(salary) OVER (PARTITION BY depname ORDER BY salary, empno) m3 FROM empsalary ORDER BY depname, empno
----
develop	4200	4200	4200.000000
develop	4200	6000	5020.000000
develop	4200	4500	4350.000000
develop	4200	5200	4633.333333
develop	4200	5200	4775.000000
personnel	3500	3900	3700.000000
personnel	3500	3500	3500.000000
sales	4800	5000	4866.666667
sales	4800	4800	4800.000000
sales	4800	4800	4800.000000

# stddev_pop
query TR
SELECT depname, STDDEV_POP(salary) OVER (PARTITION BY depname ORDER BY salary, empno) s FROM empsalary ORDER BY depname, empno
----
develop	0.000000
develop	627.375486
develop	150.000000
develop	418.993503
develop	438.035387
personnel	200.000000
personnel	0.000000
sales	94.280904
sales	0.000000
sales	0.000000

# covar_pop
query TR
SELECT depname, COVAR_POP(salary, empno) OVER (PARTITION BY depname ORDER BY salary, empno) c FROM empsalary ORDER BY depname, empno
----
develop	0.000000
develop	240.000000
develop	150.000000
develop	477.777778
develop	606.250000
personnel	-300.000000
personnel	0.000000
sales	-111.111111
sales	0.000000
sales	0.000000

statement ok
CREATE TABLE filtering AS
	SELECT
		 x
		,round(x * 0.333,0) % 3 AS y
		,round(x * 0.333,0) % 3 AS z
	FROM generate_series(0,10) tbl(x);

# The x_filtered_window and z_filtered_window columns should return a different output than plain_window
query IIIIII
SELECT
	 x
	,y
	,z
	,avg(x) OVER (PARTITION BY y) AS plain_window
	,avg(x) FILTER (WHERE x = 1) OVER (PARTITION BY y) AS x_filtered_window
	,avg(x) FILTER (WHERE z = 0) OVER (PARTITION BY y) AS z_filtered_window
FROM filtering
ORDER BY y, x;
----
0	0.000000	0.000000	5.600000	1.000000	5.600000
1	0.000000	0.000000	5.600000	1.000000	5.600000
8	0.000000	0.000000	5.600000	1.000000	5.600000
9	0.000000	0.000000	5.600000	1.000000	5.600000
10	0.000000	0.000000	5.600000	1.000000	5.600000
2	1.000000	1.000000	3.000000	NULL	NULL
3	1.000000	1.000000	3.000000	NULL	NULL
4	1.000000	1.000000	3.000000	NULL	NULL
5	2.000000	2.000000	6.000000	NULL	NULL
6	2.000000	2.000000	6.000000	NULL	NULL
7	2.000000	2.000000	6.000000	NULL	NULL

# COUNT(*) coverage
query IIIIII
SELECT
	 x
	,y
	,z
	,count(*) OVER (PARTITION BY y) AS plain_window
	,count(*) FILTER (WHERE x = 1) OVER (PARTITION BY y) AS x_filtered_window
	,count(*) FILTER (WHERE z = 0) OVER (PARTITION BY y) AS z_filtered_window
FROM filtering
ORDER BY y, x;
----
0	0.000000	0.000000	5	1	5
1	0.000000	0.000000	5	1	5
8	0.000000	0.000000	5	1	5
9	0.000000	0.000000	5	1	5
10	0.000000	0.000000	5	1	5
2	1.000000	1.000000	3	0	0
3	1.000000	1.000000	3	0	0
4	1.000000	1.000000	3	0	0
5	2.000000	2.000000	3	0	0
6	2.000000	2.000000	3	0	0
7	2.000000	2.000000	3	0	0

# Holistic coverage
query IIIIII
SELECT
	 x
	,y
	,z
	,median(x) OVER (PARTITION BY y) AS plain_window
	,median(x) FILTER (WHERE x = 1) OVER (PARTITION BY y) AS x_filtered_window
	,median(x) FILTER (WHERE z = 0) OVER (PARTITION BY y) AS z_filtered_window
FROM filtering
ORDER BY y, x;
----
0	0.000000	0.000000	8.000000	1.000000	8.000000
1	0.000000	0.000000	8.000000	1.000000	8.000000
8	0.000000	0.000000	8.000000	1.000000	8.000000
9	0.000000	0.000000	8.000000	1.000000	8.000000
10	0.000000	0.000000	8.000000	1.000000	8.000000
2	1.000000	1.000000	3.000000	NULL	NULL
3	1.000000	1.000000	3.000000	NULL	NULL
4	1.000000	1.000000	3.000000	NULL	NULL
5	2.000000	2.000000	6.000000	NULL	NULL
6	2.000000	2.000000	6.000000	NULL	NULL
7	2.000000	2.000000	6.000000	NULL	NULL

# Filters do not affect framing.
query II
SELECT x, count(x) FILTER (WHERE x % 2 = 0) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
FROM generate_series(0,10) tbl(x);
----
0	2
1	2
2	3
3	2
4	3
5	2
6	3
7	2
8	3
9	2
10	2

# Test distinct hash table
statement ok
CREATE TABLE figure1 AS 
	SELECT * 
	FROM VALUES 
			(1, 'a'),
			(2, 'b'),
			(3, 'b'),
			(4, 'c'),
			(5, 'c'),
			(6, 'b'),
			(7, 'c'),
			(8, 'a')
		v(i, s);

query III
SELECT i
	, s
	, COUNT(DISTINCT s) OVER( ORDER BY i ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS c
FROM figure1
ORDER BY i
----
1	a	2
2	b	3
3	b	3
4	c	2
5	c	2
6	b	3
7	c	3
8	a	3

# Test distinct and exclude
query III
SELECT i
	, s
	, COUNT(DISTINCT s) OVER( ORDER BY i ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE TIES) AS c
FROM figure1
ORDER BY i
----
1	a	2
2	b	3
3	b	3
4	c	2
5	c	2
6	b	3
7	c	3
8	a	3

# Test ORDER BY arguments
# Multiple partitions => multiple threads.
query III
SELECT 
	i // 10 AS p,
	i,
	ANY_VALUE(i ORDER BY i DESC) OVER(
		PARTITION BY i // 10
		ORDER BY i
		ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING 
	) AS c
FROM range(20) tbl(i)
ORDER BY ALL
----
0	0	2
0	1	3
0	2	4
0	3	5
0	4	6
0	5	7
0	6	8
0	7	9
0	8	9
0	9	9
1	10	12
1	11	13
1	12	14
1	13	15
1	14	16
1	15	17
1	16	18
1	17	19
1	18	19
1	19	19

query III
SELECT 
	i // 10 AS p,
	i,
	LIST(i ORDER BY i DESC) OVER(
		PARTITION BY i // 10
		ORDER BY i
		ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING 
	) AS c
FROM range(20) tbl(i)
ORDER BY ALL
----
0	0	[2, 1, 0]
0	1	[3, 2, 1, 0]
0	2	[4, 3, 2, 1, 0]
0	3	[5, 4, 3, 2, 1]
0	4	[6, 5, 4, 3, 2]
0	5	[7, 6, 5, 4, 3]
0	6	[8, 7, 6, 5, 4]
0	7	[9, 8, 7, 6, 5]
0	8	[9, 8, 7, 6]
0	9	[9, 8, 7]
1	10	[12, 11, 10]
1	11	[13, 12, 11, 10]
1	12	[14, 13, 12, 11, 10]
1	13	[15, 14, 13, 12, 11]
1	14	[16, 15, 14, 13, 12]
1	15	[17, 16, 15, 14, 13]
1	16	[18, 17, 16, 15, 14]
1	17	[19, 18, 17, 16, 15]
1	18	[19, 18, 17, 16]
1	19	[19, 18, 17]

# Test DISTINCT + ORDER BY arguments
query III
SELECT 
	i // 10 AS p,
	i,
	LIST(DISTINCT i // 2 ORDER BY i DESC) OVER(
		PARTITION BY i // 10
		ORDER BY i
		ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING 
	) AS c
FROM range(20) tbl(i)
ORDER BY ALL
----
0	0	[1, 0]
0	1	[1, 0]
0	2	[2, 1, 0]
0	3	[2, 1, 0]
0	4	[3, 2, 1]
0	5	[3, 2, 1]
0	6	[4, 3, 2]
0	7	[4, 3, 2]
0	8	[4, 3]
0	9	[4, 3]
1	10	[6, 5]
1	11	[6, 5]
1	12	[7, 6, 5]
1	13	[7, 6, 5]
1	14	[8, 7, 6]
1	15	[8, 7, 6]
1	16	[9, 8, 7]
1	17	[9, 8, 7]
1	18	[9, 8]
1	19	[9, 8]

# Test DISTINCT + ORDER BY + FILTER arguments
query III
SELECT 
	i // 10 AS p,
	i,
	LIST(DISTINCT i // 2 ORDER BY i DESC) FILTER (i > 1) OVER(
		PARTITION BY i // 10
		ORDER BY i
		ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING 
	) AS c
FROM range(20) tbl(i)
ORDER BY ALL
----
0	0	[1]
0	1	[1]
0	2	[2, 1]
0	3	[2, 1]
0	4	[3, 2, 1]
0	5	[3, 2, 1]
0	6	[4, 3, 2]
0	7	[4, 3, 2]
0	8	[4, 3]
0	9	[4, 3]
1	10	[6, 5]
1	11	[6, 5]
1	12	[7, 6, 5]
1	13	[7, 6, 5]
1	14	[8, 7, 6]
1	15	[8, 7, 6]
1	16	[9, 8, 7]
1	17	[9, 8, 7]
1	18	[9, 8]
1	19	[9, 8]