You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2022/09/13 00:58:25 UTC

[doris] branch master updated: [test](window-function) add regression test of window function (#12529)

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 <11...@users.noreply.github.com>
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: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org