This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 4c73755b40 [test](window-function) add regression test of window
function (#12529)
4c73755b40 is described below
commit 4c73755b40ee4ac7f14b21920fb91b93842cfb4c
Author: lsy3993 <[email protected]>
AuthorDate: Tue Sep 13 08:58:19 2022 +0800
[test](window-function) add regression test of window function (#12529)
---
.../window_functions/test_window_fn.out | 221 +++++++++++++++++++++
.../window_functions/test_window_fn.groovy | 128 ++++++++++++
2 files changed, 349 insertions(+)
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/test_window_fn.out
b/regression-test/data/query_p0/sql_functions/window_functions/test_window_fn.out
new file mode 100644
index 0000000000..efef9420e4
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/test_window_fn.out
@@ -0,0 +1,221 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+3500 0 6000
+3500 3900 3500
+3500 4200 3900
+3500 4500 4200
+3500 4800 4500
+3500 4800 4800
+3500 5000 4800
+3500 5200 5000
+3500 5200 5200
+3500 6000 5200
+
+-- !sql --
+6000 4500 3500 2007-12-10
+6000 4500 3900 2006-12-23
+6000 4500 4200 2008-01-01
+6000 4500 4500 2008-01-01
+6000 4500 4800 2007-08-01
+6000 4500 4800 2007-08-08
+6000 4500 5000 2006-10-01
+6000 4500 5200 2007-08-01
+6000 4500 5200 2007-08-15
+6000 4500 6000 2006-10-01
+
+-- !sql --
+6000 0 3500
+6000 3500 3900
+6000 3900 4200
+6000 4200 4500
+6000 4500 4800
+6000 4800 4800
+6000 4800 5000
+6000 5000 5200
+6000 5200 5200
+6000 5200 6000
+
+-- !sql --
+1 sales 5000 1000 200 1000 200
+2 personnel 3900 1000 200 1000 200
+3 sales 4800 500 \N 500 200
+4 sales 4800 500 \N 500 200
+5 personnel 3500 500 \N 500 200
+7 develop 4200 \N \N 500 200
+8 develop 6000 1000 200 500 200
+9 develop 4500 \N \N 500 200
+10 develop 5200 500 200 500 200
+11 develop 5200 500 200 500 200
+
+-- !sql --
+2008-01-01 3500 2007-12-10
+2008-01-01 3900 2006-12-23
+2008-01-01 4200 2008-01-01
+2008-01-01 4500 2008-01-01
+2008-01-01 4800 2007-08-01
+2008-01-01 4800 2007-08-08
+2008-01-01 5000 2006-10-01
+2008-01-01 5200 2007-08-01
+2008-01-01 5200 2007-08-15
+2008-01-01 6000 2006-10-01
+
+-- !sql --
+2008-01-01 3500 2007-12-10
+2008-01-01 3900 2006-12-23
+2008-01-01 4200 2008-01-01
+2008-01-01 4500 2008-01-01
+2008-01-01 4800 2007-08-01
+2008-01-01 4800 2007-08-08
+2008-01-01 5000 2006-10-01
+2008-01-01 5200 2007-08-01
+2008-01-01 5200 2007-08-15
+2008-01-01 6000 2006-10-01
+
+-- !sql --
+2008-01-01 3500 2007-12-10
+2008-01-01 3900 2006-12-23
+2008-01-01 4200 2008-01-01
+2008-01-01 4500 2008-01-01
+2008-01-01 4800 2007-08-01
+2008-01-01 4800 2007-08-08
+2008-01-01 5000 2006-10-01
+2008-01-01 5200 2007-08-01
+2008-01-01 5200 2007-08-15
+2008-01-01 6000 2006-10-01
+
+-- !sql --
+develop 7 4200 1
+develop 9 4500 2
+develop 10 5200 3
+develop 11 5200 3
+develop 8 6000 5
+personnel 5 3500 1
+personnel 2 3900 2
+sales 3 4800 1
+sales 4 4800 1
+sales 1 5000 3
+
+-- !sql --
+develop 7 4200 1
+personnel 5 3500 1
+sales 3 4800 1
+develop 9 4500 2
+personnel 2 3900 2
+sales 4 4800 2
+develop 10 5200 3
+sales 1 5000 3
+develop 11 5200 4
+develop 8 6000 5
+
+-- !sql --
+7400 2 22000
+14600 3 14600
+25100 1 47100
+
+-- !sql --
+3900 1
+5000 1
+6000 1
+7400 2
+14600 2
+14600 2
+16400 2
+16400 2
+20900 4
+25100 5
+
+-- !sql --
+personnel 5 2007-12-10 3500 1
+personnel 2 2006-12-23 3900 2
+develop 7 2008-01-01 4200 3
+develop 9 2008-01-01 4500 4
+sales 3 2007-08-01 4800 5
+sales 4 2007-08-08 4800 6
+sales 1 2006-10-01 5000 7
+develop 10 2007-08-01 5200 8
+develop 11 2007-08-15 5200 9
+
+-- !sql --
+develop 7 4200 25100
+develop 8 6000 25100
+develop 9 4500 25100
+develop 10 5200 25100
+develop 11 5200 25100
+personnel 2 3900 7400
+personnel 5 3500 7400
+sales 1 5000 14600
+sales 3 4800 14600
+sales 4 4800 14600
+
+-- !sql --
+3500 1
+7400 2
+11600 3
+16100 4
+25700 6
+25700 6
+30700 7
+41100 9
+41100 9
+47100 10
+
+-- !sql --
+47100 3500 2007-12-10
+47100 3900 2006-12-23
+47100 4200 2008-01-01
+47100 4500 2008-01-01
+47100 4800 2007-08-01
+47100 4800 2007-08-08
+47100 5000 2006-10-01
+47100 5200 2007-08-01
+47100 5200 2007-08-15
+47100 6000 2006-10-01
+
+-- !sql --
+47100 3500 2007-12-10
+47100 3900 2006-12-23
+47100 4200 2008-01-01
+47100 4500 2008-01-01
+47100 4800 2007-08-01
+47100 4800 2007-08-08
+47100 5000 2006-10-01
+47100 5200 2007-08-01
+47100 5200 2007-08-15
+47100 6000 2006-10-01
+
+-- !sql --
+8700 4200 2008-01-01
+8700 4500 2008-01-01
+12200 3500 2007-12-10
+17400 5200 2007-08-15
+22200 4800 2007-08-08
+32200 4800 2007-08-01
+32200 5200 2007-08-01
+36100 3900 2006-12-23
+47100 5000 2006-10-01
+47100 6000 2006-10-01
+
+-- !sql --
+47100 3500 2007-12-10
+47100 3900 2006-12-23
+47100 4200 2008-01-01
+47100 4500 2008-01-01
+47100 4800 2007-08-01
+47100 4800 2007-08-08
+47100 5000 2006-10-01
+47100 5200 2007-08-01
+47100 5200 2007-08-15
+47100 6000 2006-10-01
+
+-- !sql --
+47100 3500 2007-12-10
+47100 3900 2006-12-23
+47100 4200 2008-01-01
+47100 4500 2008-01-01
+47100 4800 2007-08-01
+47100 4800 2007-08-08
+47100 5000 2006-10-01
+47100 5200 2007-08-01
+47100 5200 2007-08-15
+47100 6000 2006-10-01
+
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/test_window_fn.groovy
b/regression-test/suites/query_p0/sql_functions/window_functions/test_window_fn.groovy
new file mode 100644
index 0000000000..3450e8ffe1
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/test_window_fn.groovy
@@ -0,0 +1,128 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+suite("test_window_fn") {
+ def tbName1 = "empsalary"
+ sql """ DROP TABLE IF EXISTS ${tbName1} """
+
+ sql """
+ CREATE TABLE ${tbName1}
+ (
+ `depname` varchar(20) NULL COMMENT "",
+ `empno` bigint NULL COMMENT "",
+ `enroll_date` date NULL COMMENT "",
+ `salary` int NULL COMMENT ""
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`depname`, `empno`, `enroll_date`)
+ COMMENT ""
+ DISTRIBUTED BY HASH(`depname`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql """
+ INSERT INTO ${tbName1} (depname, empno, enroll_date, salary) VALUES
+ ('develop', 10, '2007-08-01', 5200),
+ ('sales', 1, '2006-10-01', 5000),
+ ('personnel', 5, '2007-12-10', 3500),
+ ('sales', 4, '2007-08-08', 4800),
+ ('personnel', 2, '2006-12-23', 3900),
+ ('develop', 7, '2008-01-01', 4200),
+ ('develop', 9, '2008-01-01', 4500),
+ ('sales', 3, '2007-08-01', 4800),
+ ('develop', 8, '2006-10-01', 6000),
+ ('develop', 11, '2007-08-15', 5200);
+ """
+
+ // first_value
+ qt_sql """
+ select first_value(salary) over(order by salary range between
UNBOUNDED preceding and UNBOUNDED following), lead(salary, 1, 0) over(order by
salary) as l, salary from ${tbName1} order by l, salary;
+ """
+ qt_sql """
+ select first_value(salary) over(order by enroll_date range between
unbounded preceding and UNBOUNDED following), last_value(salary) over(order by
enroll_date range between unbounded preceding and UNBOUNDED following), salary,
enroll_date from ${tbName1} order by salary, enroll_date;
+ """
+
+ // last_value
+ qt_sql """
+ select last_value(salary) over(order by salary range between UNBOUNDED
preceding and UNBOUNDED following), lag(salary, 1, 0) over(order by salary) as
l, salary from ${tbName1} order by l, salary;
+ """
+
+ // min_max
+ qt_sql """
+ SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER
BY empno), MAX(depadj) OVER ()
+ FROM( SELECT *, CASE WHEN enroll_date < '2008-01-01' THEN 2008 -
extract(YEAR FROM enroll_date) END * 500 AS bonus,
+ CASE WHEN AVG(salary) OVER (PARTITION BY depname) < salary THEN 200
END AS depadj FROM ${tbName1})s order by empno;
+ """
+ qt_sql """
+ select max(enroll_date) over (order by enroll_date range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
+ """
+ qt_sql """
+ select max(enroll_date) over (order by salary range between UNBOUNDED
preceding and UNBOUNDED following ), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
+ """
+ qt_sql """
+ select max(enroll_date) over (order by enroll_date range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
+ """
+
+ // rank
+ qt_sql """
+ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER
BY salary) FROM ${tbName1} order by depname
+ """
+ qt_sql """
+ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER
BY salary, empno)
+ FROM ${tbName1} ORDER BY rank() OVER (PARTITION BY depname ORDER BY
salary, empno);
+ """
+ qt_sql """
+ SELECT sum(salary) as s, row_number() OVER (ORDER BY depname) as r,
sum(sum(salary)) OVER (ORDER BY depname DESC) as ss
+ FROM ${tbName1} GROUP BY depname order by s, r, ss;
+ """
+ qt_sql """
+ SELECT sum(salary) OVER (PARTITION BY depname ORDER BY salary DESC) as
s, rank() OVER (PARTITION BY depname ORDER BY salary DESC) as r
+ FROM ${tbName1} order by s, r;
+ """
+ qt_sql """
+ SELECT * FROM ( select *, row_number() OVER (ORDER BY salary) as a
from ${tbName1} ) as t where t.a < 10;
+ """
+
+ // sum_avg_count
+ qt_sql """
+ SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname)
FROM ${tbName1} order by depname,empno,salary;
+ """
+ qt_sql """
+ SELECT sum(salary) OVER (ORDER BY salary) as s, count(1) OVER (ORDER
BY salary) as c FROM ${tbName1} order by s, c;
+ """
+ qt_sql """
+ select sum(salary) over (order by enroll_date range between UNBOUNDED
preceding and UNBOUNDED following), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
+ """
+ qt_sql """
+ select sum(salary) over (order by enroll_date desc range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
+ """
+ qt_sql """
+ select sum(salary) over (order by enroll_date desc range between
UNBOUNDED preceding and current row) as s, salary, enroll_date from ${tbName1}
order by s, salary;
+ """
+ qt_sql """
+ select sum(salary) over (order by enroll_date, salary range between
UNBOUNDED preceding and UNBOUNDED following), salary, enroll_date from
${tbName1} order by salary, enroll_date;
+ """
+ qt_sql """
+ select sum(salary) over (order by depname range between UNBOUNDED
preceding and UNBOUNDED following ), salary, enroll_date from ${tbName1} order
by salary, enroll_date;
+ """
+
+ sql "DROP TABLE IF EXISTS ${tbName1};"
+
+}
+
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
