You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by se...@apache.org on 2015/09/09 04:18:11 UTC
[07/11] hive git commit: HIVE-11646: CBO: Calcite Operator To Hive
Operator (Calcite Return Path): fix multiple window spec for PTF operator
(Pengcheng Xiong, reviewed by Jesus Camacho Rodriguez)
HIVE-11646: CBO: Calcite Operator To Hive Operator (Calcite Return Path): fix multiple window spec for PTF operator (Pengcheng Xiong, reviewed by Jesus Camacho Rodriguez)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/2d3316b9
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/2d3316b9
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/2d3316b9
Branch: refs/heads/llap
Commit: 2d3316b9154cb175b6db15f2ec4551d2d54397d6
Parents: 7aec272
Author: Pengcheng Xiong <px...@hortonworks.com>
Authored: Tue Sep 8 11:33:44 2015 +0100
Committer: Jesus Camacho Rodriguez <jc...@apache.org>
Committed: Tue Sep 8 11:33:44 2015 +0100
----------------------------------------------------------------------
.../calcite/translator/ExprNodeConverter.java | 25 +-
.../calcite/translator/HiveOpConverter.java | 5 +-
.../queries/clientpositive/cbo_rp_windowing_2.q | 439 ++++
.../clientpositive/cbo_rp_windowing_2.q.out | 2338 ++++++++++++++++++
4 files changed, 2798 insertions(+), 9 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/2d3316b9/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ExprNodeConverter.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ExprNodeConverter.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ExprNodeConverter.java
index ec22f1a..42f1ab6 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ExprNodeConverter.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/ExprNodeConverter.java
@@ -20,6 +20,7 @@ package org.apache.hadoop.hive.ql.optimizer.calcite.translator;
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Timestamp;
+import java.util.ArrayList;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.List;
@@ -80,11 +81,11 @@ public class ExprNodeConverter extends RexVisitorImpl<ExprNodeDesc> {
private final String tabAlias;
private final String columnAlias;
private final RelDataType inputRowType;
- private final RelDataType outputRowType;
private final ImmutableSet<Integer> inputVCols;
- private WindowFunctionSpec wfs;
+ private List<WindowFunctionSpec> windowFunctionSpecs = new ArrayList<>();
private final RelDataTypeFactory dTFactory;
protected final Log LOG = LogFactory.getLog(this.getClass().getName());
+ private static long uniqueCounter = 0;
public ExprNodeConverter(String tabAlias, RelDataType inputRowType,
Set<Integer> vCols, RelDataTypeFactory dTFactory) {
@@ -97,13 +98,12 @@ public class ExprNodeConverter extends RexVisitorImpl<ExprNodeDesc> {
this.tabAlias = tabAlias;
this.columnAlias = columnAlias;
this.inputRowType = inputRowType;
- this.outputRowType = outputRowType;
this.inputVCols = ImmutableSet.copyOf(inputVCols);
this.dTFactory = dTFactory;
}
- public WindowFunctionSpec getWindowFunctionSpec() {
- return this.wfs;
+ public List<WindowFunctionSpec> getWindowFunctionSpec() {
+ return this.windowFunctionSpecs;
}
@Override
@@ -235,7 +235,7 @@ public class ExprNodeConverter extends RexVisitorImpl<ExprNodeDesc> {
final WindowFrameSpec windowFrameSpec = getWindowRange(window);
windowSpec.setWindowFrame(windowFrameSpec);
- wfs = new WindowFunctionSpec();
+ WindowFunctionSpec wfs = new WindowFunctionSpec();
wfs.setWindowSpec(windowSpec);
final Schema schema = new Schema(tabAlias, inputRowType.getFieldList());
final ASTNode wUDAFAst = new ASTConverter.RexVisitor(schema).visitOver(over);
@@ -246,10 +246,15 @@ public class ExprNodeConverter extends RexVisitorImpl<ExprNodeDesc> {
ASTNode child = (ASTNode) wUDAFAst.getChild(i);
wfs.addArg(child);
}
+ if (wUDAFAst.getText().equals("TOK_FUNCTIONSTAR")) {
+ wfs.setStar(true);
+ }
+ String columnAlias = getWindowColumnAlias();
wfs.setAlias(columnAlias);
- RelDataTypeField f = outputRowType.getField(columnAlias, false, false);
- return new ExprNodeColumnDesc(TypeConverter.convert(f.getType()), columnAlias, tabAlias,
+ this.windowFunctionSpecs.add(wfs);
+
+ return new ExprNodeColumnDesc(TypeConverter.convert(over.getType()), columnAlias, tabAlias,
false);
}
@@ -343,4 +348,8 @@ public class ExprNodeConverter extends RexVisitorImpl<ExprNodeDesc> {
return boundarySpec;
}
+ private String getWindowColumnAlias() {
+ return "$win$_col_" + (uniqueCounter++);
+ }
+
}
http://git-wip-us.apache.org/repos/asf/hive/blob/2d3316b9/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/HiveOpConverter.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/HiveOpConverter.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/HiveOpConverter.java
index 9391952..3f66893 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/HiveOpConverter.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/HiveOpConverter.java
@@ -81,6 +81,7 @@ import org.apache.hadoop.hive.ql.parse.SemanticException;
import org.apache.hadoop.hive.ql.parse.UnparseTranslator;
import org.apache.hadoop.hive.ql.parse.WindowingComponentizer;
import org.apache.hadoop.hive.ql.parse.WindowingSpec;
+import org.apache.hadoop.hive.ql.parse.WindowingSpec.WindowFunctionSpec;
import org.apache.hadoop.hive.ql.plan.ExprNodeColumnDesc;
import org.apache.hadoop.hive.ql.plan.ExprNodeDesc;
import org.apache.hadoop.hive.ql.plan.ExprNodeDescUtils;
@@ -284,7 +285,9 @@ public class HiveOpConverter {
exprCols.add(exprCol);
//TODO: Cols that come through PTF should it retain (VirtualColumness)?
if (converter.getWindowFunctionSpec() != null) {
- windowingSpec.addWindowFunction(converter.getWindowFunctionSpec());
+ for (WindowFunctionSpec wfs : converter.getWindowFunctionSpec()) {
+ windowingSpec.addWindowFunction(wfs);
+ }
}
}
if (windowingSpec.getWindowExpressions() != null
http://git-wip-us.apache.org/repos/asf/hive/blob/2d3316b9/ql/src/test/queries/clientpositive/cbo_rp_windowing_2.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/cbo_rp_windowing_2.q b/ql/src/test/queries/clientpositive/cbo_rp_windowing_2.q
new file mode 100644
index 0000000..d02c8be
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/cbo_rp_windowing_2.q
@@ -0,0 +1,439 @@
+set hive.cbo.enable=true;
+set hive.cbo.returnpath.hiveop=true;
+set hive.exec.check.crossproducts=false;
+set mapred.reduce.tasks=4;
+-- SORT_QUERY_RESULTS
+
+-- 1. testWindowing
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1
+from part
+;
+
+-- 2. testGroupByWithPartitioning
+select p_mfgr, p_name, p_size,
+min(p_retailprice),
+rank() over(distribute by p_mfgr sort by p_name)as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+p_size, p_size - lag(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from part
+group by p_mfgr, p_name, p_size
+;
+
+-- 3. testGroupByHavingWithSWQ
+select p_mfgr, p_name, p_size, min(p_retailprice),
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+p_size, p_size - lag(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from part
+group by p_mfgr, p_name, p_size
+having p_size > 0
+;
+
+-- 4. testCount
+select p_mfgr, p_name,
+count(p_size) over(distribute by p_mfgr sort by p_name) as cd
+from part
+;
+
+-- 5. testCountWithWindowingUDAF
+select p_mfgr, p_name,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+count(p_size) over(distribute by p_mfgr sort by p_name) as cd,
+p_retailprice, sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1,
+p_size, p_size - lag(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from part
+;
+
+-- 6. testCountInSubQ
+select sub1.r, sub1.dr, sub1.cd, sub1.s1, sub1.deltaSz
+from (select p_mfgr, p_name,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+count(p_size) over(distribute by p_mfgr sort by p_name) as cd,
+p_retailprice, sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1,
+p_size, p_size - lag(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from part
+) sub1;
+
+-- 7. testJoinWithWindowingAndPTF
+select abc.p_mfgr, abc.p_name,
+rank() over(distribute by abc.p_mfgr sort by abc.p_name) as r,
+dense_rank() over(distribute by abc.p_mfgr sort by abc.p_name) as dr,
+abc.p_retailprice, sum(abc.p_retailprice) over (distribute by abc.p_mfgr sort by abc.p_name rows between unbounded preceding and current row) as s1,
+abc.p_size, abc.p_size - lag(abc.p_size,1,abc.p_size) over(distribute by abc.p_mfgr sort by abc.p_name) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+) abc join part p1 on abc.p_partkey = p1.p_partkey
+;
+
+-- 8. testMixedCaseAlias
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name, p_size desc) as R
+from part
+;
+
+-- 9. testHavingWithWindowingNoGBY
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1
+from part
+;
+
+-- 10. testHavingWithWindowingCondRankNoGBY
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1
+from part
+;
+
+-- 11. testFirstLast
+select p_mfgr,p_name, p_size,
+sum(p_size) over (distribute by p_mfgr sort by p_name rows between current row and current row) as s2,
+first_value(p_size) over w1 as f,
+last_value(p_size, false) over w1 as l
+from part
+window w1 as (distribute by p_mfgr sort by p_name rows between 2 preceding and 2 following);
+
+-- 12. testFirstLastWithWhere
+select p_mfgr,p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+sum(p_size) over (distribute by p_mfgr sort by p_name rows between current row and current row) as s2,
+first_value(p_size) over w1 as f,
+last_value(p_size, false) over w1 as l
+from part
+where p_mfgr = 'Manufacturer#3'
+window w1 as (distribute by p_mfgr sort by p_name rows between 2 preceding and 2 following);
+
+-- 13. testSumWindow
+select p_mfgr,p_name, p_size,
+sum(p_size) over w1 as s1,
+sum(p_size) over (distribute by p_mfgr sort by p_name rows between current row and current row) as s2
+from part
+window w1 as (distribute by p_mfgr sort by p_name rows between 2 preceding and 2 following);
+
+-- 14. testNoSortClause
+select p_mfgr,p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r, dense_rank() over(distribute by p_mfgr sort by p_name) as dr
+from part
+window w1 as (distribute by p_mfgr sort by p_name rows between 2 preceding and 2 following);
+
+-- 15. testExpressions
+select p_mfgr,p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+cume_dist() over(distribute by p_mfgr sort by p_name) as cud,
+percent_rank() over(distribute by p_mfgr sort by p_name) as pr,
+ntile(3) over(distribute by p_mfgr sort by p_name) as nt,
+count(p_size) over(distribute by p_mfgr sort by p_name) as ca,
+avg(p_size) over(distribute by p_mfgr sort by p_name) as avg,
+stddev(p_size) over(distribute by p_mfgr sort by p_name) as st,
+first_value(p_size % 5) over(distribute by p_mfgr sort by p_name) as fv,
+last_value(p_size) over(distribute by p_mfgr sort by p_name) as lv,
+first_value(p_size) over w1 as fvW1
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 16. testMultipleWindows
+select p_mfgr,p_name, p_size,
+ rank() over(distribute by p_mfgr sort by p_name) as r,
+ dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+cume_dist() over(distribute by p_mfgr sort by p_name) as cud,
+sum(p_size) over (distribute by p_mfgr sort by p_name range between unbounded preceding and current row) as s1,
+sum(p_size) over (distribute by p_mfgr sort by p_size range between 5 preceding and current row) as s2,
+first_value(p_size) over w1 as fv1
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 17. testCountStar
+select p_mfgr,p_name, p_size,
+count(*) over(distribute by p_mfgr sort by p_name ) as c,
+count(p_size) over(distribute by p_mfgr sort by p_name) as ca,
+first_value(p_size) over w1 as fvW1
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 18. testUDAFs
+select p_mfgr,p_name, p_size,
+sum(p_retailprice) over w1 as s,
+min(p_retailprice) over w1 as mi,
+max(p_retailprice) over w1 as ma,
+avg(p_retailprice) over w1 as ag
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 19. testUDAFsWithGBY
+select p_mfgr,p_name, p_size, p_retailprice,
+sum(p_retailprice) over w1 as s,
+min(p_retailprice) as mi ,
+max(p_retailprice) as ma ,
+avg(p_retailprice) over w1 as ag
+from part
+group by p_mfgr,p_name, p_size, p_retailprice
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 20. testSTATs
+select p_mfgr,p_name, p_size,
+stddev(p_retailprice) over w1 as sdev,
+stddev_pop(p_retailprice) over w1 as sdev_pop,
+collect_set(p_size) over w1 as uniq_size,
+variance(p_retailprice) over w1 as var,
+corr(p_size, p_retailprice) over w1 as cor,
+covar_pop(p_size, p_retailprice) over w1 as covarp
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 21. testDISTs
+select p_mfgr,p_name, p_size,
+histogram_numeric(p_retailprice, 5) over w1 as hist,
+percentile(p_partkey, 0.5) over w1 as per,
+row_number() over(distribute by p_mfgr sort by p_mfgr, p_name) as rn
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 22. testViewAsTableInputWithWindowing
+create view IF NOT EXISTS mfgr_price_view as
+select p_mfgr, p_brand,
+round(sum(p_retailprice),2) as s
+from part
+group by p_mfgr, p_brand;
+
+select *
+from (
+select p_mfgr, p_brand, s,
+round(sum(s) over w1 , 2) as s1
+from mfgr_price_view
+window w1 as (distribute by p_mfgr sort by p_mfgr )
+) sq
+order by p_mfgr, p_brand;
+
+select p_mfgr, p_brand, s,
+round(sum(s) over w1 ,2) as s1
+from mfgr_price_view
+window w1 as (distribute by p_mfgr sort by p_brand rows between 2 preceding and current row);
+
+-- 23. testCreateViewWithWindowingQuery
+create view IF NOT EXISTS mfgr_brand_price_view as
+select p_mfgr, p_brand,
+sum(p_retailprice) over w1 as s
+from part
+window w1 as (distribute by p_mfgr sort by p_name rows between 2 preceding and current row);
+
+select * from mfgr_brand_price_view;
+
+-- 24. testLateralViews
+select p_mfgr, p_name,
+lv_col, p_size, sum(p_size) over w1 as s
+from (select p_mfgr, p_name, p_size, array(1,2,3) arr from part) p
+lateral view explode(arr) part_lv as lv_col
+window w1 as (distribute by p_mfgr sort by p_size, lv_col rows between 2 preceding and current row);
+
+-- 25. testMultipleInserts3SWQs
+CREATE TABLE part_1(
+p_mfgr STRING,
+p_name STRING,
+p_size INT,
+r INT,
+dr INT,
+s DOUBLE);
+
+CREATE TABLE part_2(
+p_mfgr STRING,
+p_name STRING,
+p_size INT,
+r INT,
+dr INT,
+cud INT,
+s2 DOUBLE,
+fv1 INT);
+
+CREATE TABLE part_3(
+p_mfgr STRING,
+p_name STRING,
+p_size INT,
+c INT,
+ca INT,
+fv INT);
+
+from part
+INSERT OVERWRITE TABLE part_1
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name ) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name ) as dr,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s
+INSERT OVERWRITE TABLE part_2
+select p_mfgr,p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+cume_dist() over(distribute by p_mfgr sort by p_name) as cud,
+round(sum(p_size) over (distribute by p_mfgr sort by p_size range between 5 preceding and current row),1) as s2,
+first_value(p_size) over w1 as fv1
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following)
+INSERT OVERWRITE TABLE part_3
+select p_mfgr,p_name, p_size,
+count(*) over(distribute by p_mfgr sort by p_name) as c,
+count(p_size) over(distribute by p_mfgr sort by p_name) as ca,
+first_value(p_size) over w1 as fv
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+select * from part_1;
+
+select * from part_2;
+
+select * from part_3;
+
+-- 26. testGroupByHavingWithSWQAndAlias
+select p_mfgr, p_name, p_size, min(p_retailprice) as mi,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+p_size, p_size - lag(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from part
+group by p_mfgr, p_name, p_size
+having p_size > 0
+;
+
+-- 27. testMultipleRangeWindows
+select p_mfgr,p_name, p_size,
+sum(p_size) over (distribute by p_mfgr sort by p_size range between 10 preceding and current row) as s2,
+sum(p_size) over (distribute by p_mfgr sort by p_size range between current row and 10 following ) as s1
+from part
+window w1 as (rows between 2 preceding and 2 following);
+
+-- 28. testPartOrderInUDAFInvoke
+select p_mfgr, p_name, p_size,
+sum(p_size) over (partition by p_mfgr order by p_name rows between 2 preceding and 2 following) as s
+from part;
+
+-- 29. testPartOrderInWdwDef
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s
+from part
+window w1 as (partition by p_mfgr order by p_name rows between 2 preceding and 2 following);
+
+-- 30. testDefaultPartitioningSpecRules
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s,
+sum(p_size) over w2 as s2
+from part
+window w1 as (distribute by p_mfgr sort by p_name rows between 2 preceding and 2 following),
+ w2 as (partition by p_mfgr order by p_name);
+
+-- 31. testWindowCrossReference
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s1,
+sum(p_size) over w2 as s2
+from part
+window w1 as (partition by p_mfgr order by p_name range between 2 preceding and 2 following),
+ w2 as w1;
+
+
+-- 32. testWindowInheritance
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s1,
+sum(p_size) over w2 as s2
+from part
+window w1 as (partition by p_mfgr order by p_name range between 2 preceding and 2 following),
+ w2 as (w1 rows between unbounded preceding and current row);
+
+
+-- 33. testWindowForwardReference
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s1,
+sum(p_size) over w2 as s2,
+sum(p_size) over w3 as s3
+from part
+window w1 as (distribute by p_mfgr sort by p_name range between 2 preceding and 2 following),
+ w2 as w3,
+ w3 as (distribute by p_mfgr sort by p_name range between unbounded preceding and current row);
+
+
+-- 34. testWindowDefinitionPropagation
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s1,
+sum(p_size) over w2 as s2,
+sum(p_size) over (w3 rows between 2 preceding and 2 following) as s3
+from part
+window w1 as (distribute by p_mfgr sort by p_name range between 2 preceding and 2 following),
+ w2 as w3,
+ w3 as (distribute by p_mfgr sort by p_name range between unbounded preceding and current row);
+
+-- 35. testDistinctWithWindowing
+select DISTINCT p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s
+from part
+window w1 as (distribute by p_mfgr sort by p_name rows between 2 preceding and 2 following);
+
+-- 36. testRankWithPartitioning
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name ) as r
+from part;
+
+-- 37. testPartitioningVariousForms
+select p_mfgr,
+round(sum(p_retailprice) over (partition by p_mfgr order by p_mfgr),2) as s1,
+min(p_retailprice) over (partition by p_mfgr) as s2,
+max(p_retailprice) over (distribute by p_mfgr sort by p_mfgr) as s3,
+round(avg(p_retailprice) over (distribute by p_mfgr),2) as s4,
+count(p_retailprice) over (cluster by p_mfgr ) as s5
+from part;
+
+-- 38. testPartitioningVariousForms2
+select p_mfgr, p_name, p_size,
+sum(p_retailprice) over (partition by p_mfgr, p_name order by p_mfgr, p_name rows between unbounded preceding and current row) as s1,
+min(p_retailprice) over (distribute by p_mfgr, p_name sort by p_mfgr, p_name rows between unbounded preceding and current row) as s2,
+max(p_retailprice) over (partition by p_mfgr, p_name order by p_name) as s3
+from part;
+
+-- 39. testUDFOnOrderCols
+select p_mfgr, p_type, substr(p_type, 2) as short_ptype,
+rank() over (partition by p_mfgr order by substr(p_type, 2)) as r
+from part;
+
+-- 40. testNoBetweenForRows
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows unbounded preceding) as s1
+ from part ;
+
+-- 41. testNoBetweenForRange
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) over (distribute by p_mfgr sort by p_size range unbounded preceding) as s1
+ from part ;
+
+-- 42. testUnboundedFollowingForRows
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between current row and unbounded following) as s1
+ from part ;
+
+-- 43. testUnboundedFollowingForRange
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) over (distribute by p_mfgr sort by p_size range between current row and unbounded following) as s1
+ from part ;
+
+-- 44. testOverNoPartitionSingleAggregate
+select p_name, p_retailprice,
+round(avg(p_retailprice) over(),2)
+from part
+order by p_name;
+
+-- 45. empty partition test
+select p_mfgr,
+ sum(p_size) over (partition by p_mfgr order by p_size rows between unbounded preceding and current row)
+from part
+where p_mfgr = 'Manufacturer#6'
+;
+
+-- 46. window sz is same as partition sz
+select p_retailprice, avg(p_retailprice) over (partition by p_mfgr order by p_name rows between current row and 6 following),
+sum(p_retailprice) over (partition by p_mfgr order by p_name rows between current row and 6 following)
+from part
+where p_mfgr='Manufacturer#1';
+
+-- 47. empty partition
+select sum(p_size) over (partition by p_mfgr )
+from part where p_mfgr = 'm1';