[
https://issues.apache.org/jira/browse/SPARK-29451?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16951622#comment-16951622
]
Yuming Wang commented on SPARK-29451:
-
It works for me.
{code:sql}
CREATE OR REPLACE TEMPORARY VIEW tenk1
(unique1 int, unique2 int, two int, four int, ten int, twenty int, hundred
int,
thousand int, twothousand int, fivethous int, tenthous int, odd int, even
int,
stringu1 string, stringu2 string, string4 string)
USING csv
OPTIONS (path
'/Users/yumwang/spark/SPARK-28216/sql/core/src/test/resources/test-data/postgresql/tenk.data',
header 'false', delimiter '\t');
SELECT four, ten/4 as two,
sum(ten/4) over (partition by four order by ten/4 rows between unbounded
preceding and current row),
last(ten/4) over (partition by four order by ten/4 rows between unbounded
preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
SELECT four, ten/4 as two,
sum(ten/4) over (partition by four order by ten/4 range between unbounded
preceding and current row),
last(ten/4) over (partition by four order by ten/4 range between unbounded
preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
{code}
{noformat}
LM-SHC-16502798:SPARK-28216 yumwang$ bin/beeline -u jdbc:hive2://localhost:1
log4j:WARN No appenders could be found for logger
(org.apache.hadoop.util.Shell).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more
info.
Connecting to jdbc:hive2://localhost:1
Connected to: Spark SQL (version 3.0.0-SNAPSHOT)
Driver: Hive JDBC (version 2.3.6)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 2.3.6 by Apache Hive
0: jdbc:hive2://localhost:1> CREATE OR REPLACE TEMPORARY VIEW tenk1
. . . . . . . . . . . . . . . .> (unique1 int, unique2 int, two int, four
int, ten int, twenty int, hundred int,
. . . . . . . . . . . . . . . .> thousand int, twothousand int, fivethous
int, tenthous int, odd int, even int,
. . . . . . . . . . . . . . . .> stringu1 string, stringu2 string, string4
string)
. . . . . . . . . . . . . . . .> USING csv
. . . . . . . . . . . . . . . .> OPTIONS (path
'/Users/yumwang/spark/SPARK-28216/sql/core/src/test/resources/test-data/postgresql/tenk.data',
. . . . . . . . . . . . . . . .> header 'false', delimiter '\t');
+-+
| Result |
+-+
+-+
No rows selected (0.499 seconds)
0: jdbc:hive2://localhost:1> SELECT four, ten/4 as two,
. . . . . . . . . . . . . . . .> sum(ten/4) over (partition by four order by
ten/4 rows between unbounded preceding and current row),
. . . . . . . . . . . . . . . .> last(ten/4) over (partition by four order by
ten/4 rows between unbounded preceding and current row)
. . . . . . . . . . . . . . . .> FROM (select distinct ten, four from tenk1) ss;
+---+---+++
| four | two | sum((CAST(ten AS DOUBLE) / CAST(4 AS DOUBLE))) OVER
(PARTITION BY four ORDER BY (CAST(ten AS DOUBLE) / CAST(4 AS DOUBLE)) ASC NULLS
FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) | last((CAST(ten AS
DOUBLE) / CAST(4 AS DOUBLE)), false) OVER (PARTITION BY four ORDER BY (CAST(ten
AS DOUBLE) / CAST(4 AS DOUBLE)) ASC NULLS FIRST ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) |
+---+---+++
| 1 | 0.25 | 0.25 | 0.25
|
| 1 | 0.75 | 1.0| 0.75
|
| 1 | 1.25 | 2.25 | 1.25
|
| 1 | 1.75 | 4.0| 1.75
|
| 1 | 2.25 | 6.25 | 2.25
|
| 3 | 0.25 | 0.25 | 0.25
|
| 3 | 0.75 | 1.0| 0.75
|
| 3 | 1.25 | 2.25 | 1.25
|
| 3 | 1.75 | 4.0| 1.75
|
| 3 | 2.25 | 6.25 | 2.25
|
| 2 | 0.0 | 0.0| 0.0
|
| 2 | 0.5 | 0.5