sql-tests/inputs/group-by-filter.sql

0001 -- Test filter clause for aggregate expression.
0002
0003 -- Test data.
0004 CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES
0005 (1, 1), (1, 2), (2, 1), (2, 2), (3, 1), (3, 2), (null, 1), (3, null), (null, null)
0006 AS testData(a, b);
0007
0008 CREATE OR REPLACE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
0009   (100, "emp 1", date "2005-01-01", 100.00D, 10),
0010   (100, "emp 1", date "2005-01-01", 100.00D, 10),
0011   (200, "emp 2", date "2003-01-01", 200.00D, 10),
0012   (300, "emp 3", date "2002-01-01", 300.00D, 20),
0013   (400, "emp 4", date "2005-01-01", 400.00D, 30),
0014   (500, "emp 5", date "2001-01-01", 400.00D, NULL),
0015   (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
0016   (700, "emp 7", date "2010-01-01", 400.00D, 100),
0017   (800, "emp 8", date "2016-01-01", 150.00D, 70)
0018 AS EMP(id, emp_name, hiredate, salary, dept_id);
0019
0020 CREATE OR REPLACE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
0021   (10, "dept 1", "CA"),
0022   (20, "dept 2", "NY"),
0023   (30, "dept 3", "TX"),
0024   (40, "dept 4 - unassigned", "OR"),
0025   (50, "dept 5 - unassigned", "NJ"),
0026   (70, "dept 7", "FL")
0027 AS DEPT(dept_id, dept_name, state);
0028
0029 -- Aggregate with filter and empty GroupBy expressions.
0030 SELECT a, COUNT(b) FILTER (WHERE a >= 2) FROM testData;
0031 SELECT COUNT(a) FILTER (WHERE a = 1), COUNT(b) FILTER (WHERE a > 1) FROM testData;
0032 SELECT COUNT(id) FILTER (WHERE hiredate = date "2001-01-01") FROM emp;
0033 SELECT COUNT(id) FILTER (WHERE hiredate = to_date('2001-01-01 00:00:00')) FROM emp;
0034 SELECT COUNT(id) FILTER (WHERE hiredate = to_timestamp("2001-01-01 00:00:00")) FROM emp;
0035 SELECT COUNT(id) FILTER (WHERE date_format(hiredate, "yyyy-MM-dd") = "2001-01-01") FROM emp;
0036 -- [SPARK-30276] Support Filter expression allows simultaneous use of DISTINCT
0037 -- SELECT COUNT(DISTINCT id) FILTER (WHERE date_format(hiredate, "yyyy-MM-dd HH:mm:ss") = "2001-01-01 00:00:00") FROM emp;
0038
0039 -- Aggregate with filter and non-empty GroupBy expressions.
0040 SELECT a, COUNT(b) FILTER (WHERE a >= 2) FROM testData GROUP BY a;
0041 SELECT a, COUNT(b) FILTER (WHERE a != 2) FROM testData GROUP BY b;
0042 SELECT COUNT(a) FILTER (WHERE a >= 0), COUNT(b) FILTER (WHERE a >= 3) FROM testData GROUP BY a;
0043 SELECT dept_id, SUM(salary) FILTER (WHERE hiredate > date "2003-01-01") FROM emp GROUP BY dept_id;
0044 SELECT dept_id, SUM(salary) FILTER (WHERE hiredate > to_date("2003-01-01")) FROM emp GROUP BY dept_id;
0045 SELECT dept_id, SUM(salary) FILTER (WHERE hiredate > to_timestamp("2003-01-01 00:00:00")) FROM emp GROUP BY dept_id;
0046 SELECT dept_id, SUM(salary) FILTER (WHERE date_format(hiredate, "yyyy-MM-dd") > "2003-01-01") FROM emp GROUP BY dept_id;
0047 -- [SPARK-30276] Support Filter expression allows simultaneous use of DISTINCT
0048 -- SELECT dept_id, SUM(DISTINCT salary) FILTER (WHERE date_format(hiredate, "yyyy-MM-dd HH:mm:ss") > "2001-01-01 00:00:00") FROM emp GROUP BY dept_id;
0049
0050 -- Aggregate with filter and grouped by literals.
0051 SELECT 'foo', COUNT(a) FILTER (WHERE b <= 2) FROM testData GROUP BY 1;
0052 SELECT 'foo', SUM(salary) FILTER (WHERE hiredate >= date "2003-01-01") FROM emp GROUP BY 1;
0053 SELECT 'foo', SUM(salary) FILTER (WHERE hiredate >= to_date("2003-01-01")) FROM emp GROUP BY 1;
0054 SELECT 'foo', SUM(salary) FILTER (WHERE hiredate >= to_timestamp("2003-01-01")) FROM emp GROUP BY 1;
0055
0056 -- Aggregate with filter, more than one aggregate function goes with distinct.
0057 select dept_id, count(distinct emp_name), count(distinct hiredate), sum(salary), sum(salary) filter (where id > 200) from emp group by dept_id;
0058 select dept_id, count(distinct emp_name), count(distinct hiredate), sum(salary), sum(salary) filter (where id + dept_id > 500) from emp group by dept_id;
0059 select dept_id, count(distinct emp_name), count(distinct hiredate), sum(salary) filter (where salary < 400.00D), sum(salary) filter (where id > 200) from emp group by dept_id;
0060 select dept_id, count(distinct emp_name), count(distinct hiredate), sum(salary) filter (where salary < 400.00D), sum(salary) filter (where id + dept_id > 500) from emp group by dept_id;
0061 -- [SPARK-30276] Support Filter expression allows simultaneous use of DISTINCT
0062 -- select dept_id, count(distinct emp_name) filter (where id > 200), count(distinct hiredate), sum(salary) from emp group by dept_id;
0063 -- select dept_id, count(distinct emp_name) filter (where id > 200), count(distinct hiredate) filter (where hiredate > date "2003-01-01"), sum(salary) from emp group by dept_id;
0064 -- select dept_id, count(distinct emp_name) filter (where id > 200), count(distinct hiredate) filter (where hiredate > date "2003-01-01"), sum(salary) filter (where salary < 400.00D) from emp group by dept_id;
0065 -- select dept_id, count(distinct emp_name) filter (where id > 200), count(distinct hiredate) filter (where hiredate > date "2003-01-01"), sum(salary) filter (where salary < 400.00D), sum(salary) filter (where id > 200) from emp group by dept_id;
0066 -- select dept_id, count(distinct emp_name) filter (where id > 200), count(distinct emp_name), sum(salary) from emp group by dept_id;
0067 -- select dept_id, count(distinct emp_name) filter (where id > 200), count(distinct emp_name) filter (where hiredate > date "2003-01-01"), sum(salary) from emp group by dept_id;
0068
0069 -- Aggregate with filter and grouped by literals (hash aggregate), here the input table is filtered using WHERE.
0070 SELECT 'foo', APPROX_COUNT_DISTINCT(a) FILTER (WHERE b >= 0) FROM testData WHERE a = 0 GROUP BY 1;
0071
0072 -- Aggregate with filter and grouped by literals (sort aggregate), here the input table is filtered using WHERE.
0073 SELECT 'foo', MAX(STRUCT(a)) FILTER (WHERE b >= 1) FROM testData WHERE a = 0 GROUP BY 1;
0074
0075 -- Aggregate with filter and complex GroupBy expressions.
0076 SELECT a + b, COUNT(b) FILTER (WHERE b >= 2) FROM testData GROUP BY a + b;
0077 SELECT a + 2, COUNT(b) FILTER (WHERE b IN (1, 2)) FROM testData GROUP BY a + 1;
0078 SELECT a + 1 + 1, COUNT(b) FILTER (WHERE b > 0) FROM testData GROUP BY a + 1;
0079
0080 -- Aggregate with filter, foldable input and multiple distinct groups.
0081 -- [SPARK-30276] Support Filter expression allows simultaneous use of DISTINCT
0082 -- SELECT COUNT(DISTINCT b) FILTER (WHERE b > 0), COUNT(DISTINCT b, c) FILTER (WHERE b > 0 AND c > 2)
0083 -- FROM (SELECT 1 AS a, 2 AS b, 3 AS c) GROUP BY a;
0084
0085 -- Check analysis exceptions
0086 SELECT a AS k, COUNT(b) FILTER (WHERE b > 0) FROM testData GROUP BY k;
0087
0088 -- Aggregate with filter contains exists subquery
0089 SELECT emp.dept_id,
0090        avg(salary),
0091        avg(salary) FILTER (WHERE id > (SELECT 200))
0092 FROM emp
0093 GROUP BY dept_id;
0094
0095 SELECT emp.dept_id,
0096        avg(salary),
0097        avg(salary) FILTER (WHERE emp.dept_id = (SELECT dept_id FROM dept LIMIT 1))
0098 FROM emp
0099 GROUP BY dept_id;
0100
0101 -- [SPARK-30220] Support Filter expression uses IN/EXISTS predicate sub-queries
0102 SELECT emp.dept_id,
0103        avg(salary),
0104        avg(salary) FILTER (WHERE EXISTS (SELECT state
0105                FROM dept
0106                WHERE dept.dept_id = emp.dept_id))
0107 FROM emp
0108 GROUP BY dept_id;
0109
0110 SELECT emp.dept_id,
0111        Sum(salary),
0112        Sum(salary) FILTER (WHERE NOT EXISTS (SELECT state
0113                    FROM dept
0114                    WHERE dept.dept_id = emp.dept_id))
0115 FROM emp
0116 GROUP BY dept_id;
0117
0118 SELECT emp.dept_id,
0119        avg(salary),
0120        avg(salary) FILTER (WHERE emp.dept_id IN (SELECT DISTINCT dept_id
0121                FROM dept))
0122 FROM emp
0123 GROUP BY dept_id;
0124 SELECT emp.dept_id,
0125        Sum(salary),
0126        Sum(salary) FILTER (WHERE emp.dept_id NOT IN (SELECT DISTINCT dept_id
0127                FROM dept))
0128 FROM emp
0129 GROUP BY dept_id;
0130
0131 -- Aggregate with filter is subquery
0132 SELECT t1.b FROM (SELECT COUNT(b) FILTER (WHERE a >= 2) AS b FROM testData) t1;