itholic commented on code in PR #39691:
URL: https://github.com/apache/spark/pull/39691#discussion_r1083550178
##########
sql/core/src/test/resources/sql-tests/results/window.sql.out:
##########
@@ -1342,3 +1342,139 @@ org.apache.spark.sql.AnalysisException
"windowName" : "w"
}
}
+
+
+-- !query
+SELECT val_long,
+ val_date,
+ max(val) OVER (partition BY val_date) AS m
+FROM testdata
+WHERE val_long > 2
+QUALIFY m > 2 AND m < 10
+-- !query schema
+struct<val_long:bigint,val_date:date,m:int>
+-- !query output
+2147483650 2020-12-31 3
+2147483650 2020-12-31 3
+
+
+-- !query
+SELECT val_long,
+ val_date,
+ val
+FROM testdata QUALIFY max(val) OVER (partition BY val_date) >= 3
+-- !query schema
+struct<val_long:bigint,val_date:date,val:int>
+-- !query output
+1 2017-08-01 1
+1 2017-08-01 3
+1 2017-08-01 NULL
+2147483650 2020-12-31 2
+2147483650 2020-12-31 3
+NULL 2017-08-01 1
+
+
+-- !query
+SELECT val_date,
+ val * sum(val) OVER (partition BY val_date) AS w
+FROM testdata
+QUALIFY w > 10
+-- !query schema
+struct<val_date:date,w:bigint>
+-- !query output
+2017-08-01 15
+2020-12-31 15
+
+
+-- !query
+SELECT w.val_date
+FROM testdata w
+JOIN testdata w2 ON w.val_date=w2.val_date
+QUALIFY row_number() OVER (partition BY w.val_date ORDER BY w.val) IN (2)
+-- !query schema
+struct<val_date:date>
+-- !query output
+2017-08-01
+2020-12-31
+
+
+-- !query
+SELECT val_date,
+ count(val_long) OVER (partition BY val_date) AS w
+FROM testdata
+GROUP BY val_date,
+ val_long
+HAVING Sum(val) > 1
+QUALIFY w = 1
+-- !query schema
+struct<val_date:date,w:bigint>
+-- !query output
+2017-08-01 1
+2017-08-03 1
+2020-12-31 1
+
+
+-- !query
+SELECT val_date,
+ val_long,
+ Sum(val)
+FROM testdata
+GROUP BY val_date,
+ val_long
+HAVING Sum(val) > 1
+QUALIFY count(val_long) OVER (partition BY val_date) IN(SELECT 1)
+-- !query schema
+struct<val_date:date,val_long:bigint,sum(val):bigint>
+-- !query output
+2017-08-01 1 4
+2017-08-03 3 2
+2020-12-31 2147483650 5
+
+
+-- !query
+SELECT val_date,
+ val_long
+FROM testdata
+QUALIFY count(val_long) OVER (partition BY val_date) > 1 AND val > 1
+-- !query schema
+struct<val_date:date,val_long:bigint>
+-- !query output
+2017-08-01 1
+2020-12-31 2147483650
+2020-12-31 2147483650
+
+
+-- !query
+SELECT val_date,
+ val_long
+FROM testdata
+QUALIFY val > 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "_LEGACY_ERROR_TEMP_1032",
Review Comment:
nit: Can we assign the name for `_LEGACY_ERROR_TEMP_1032` to
`MISSING_WINDOW_EXPR` or something while we here ?
But it's not a necessary, and I can help it as follow-up, tho.
##########
docs/sql-ref-syntax-qry-select-qualify.md:
##########
@@ -0,0 +1,98 @@
+---
+layout: global
+title: QUALIFY Clause
+displayTitle: QUALIFY Clause
+license: |
+ 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.
+---
+
+### Description
+
+The `QUALIFY` clause is used to filter the results of
+[window functions](sql-ref-syntax-qry-select-window.md). To use QUALIFY,
+at least one window function is required to be present in the SELECT list or
the QUALIFY clause.
Review Comment:
`SELECT` -> `[SELECT](sql-ref-syntax-qry-select.html)` ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
