You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by li...@apache.org on 2018/05/04 00:05:05 UTC
spark git commit: [SPARK-24035][SQL] SQL syntax for Pivot
Repository: spark
Updated Branches:
refs/heads/master 94641fe6c -> e3201e165
[SPARK-24035][SQL] SQL syntax for Pivot
## What changes were proposed in this pull request?
Add SQL support for Pivot according to Pivot grammar defined by Oracle (https://docs.oracle.com/database/121/SQLRF/img_text/pivot_clause.htm) with some simplifications, based on our existing functionality and limitations for Pivot at the backend:
1. For pivot_for_clause (https://docs.oracle.com/database/121/SQLRF/img_text/pivot_for_clause.htm), the column list form is not supported, which means the pivot column can only be one single column.
2. For pivot_in_clause (https://docs.oracle.com/database/121/SQLRF/img_text/pivot_in_clause.htm), the sub-query form and "ANY" is not supported (this is only supported by Oracle for XML anyway).
3. For pivot_in_clause, aliases for the constant values are not supported.
The code changes are:
1. Add parser support for Pivot. Note that according to https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#i2076542, Pivot cannot be used together with lateral views in the from clause. This restriction has been implemented in the Parser rule.
2. Infer group-by expressions: group-by expressions are not explicitly specified in SQL Pivot clause and need to be deduced based on this rule: https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#CHDFAFIE, so we have to post-fix it at query analysis stage.
3. Override Pivot.resolved as "false": for the reason mentioned in [2] and the fact that output attributes change after Pivot being replaced by Project or Aggregate, we avoid resolving parent references until after Pivot has been resolved and replaced.
4. Verify aggregate expressions: only aggregate expressions with or without aliases can appear in the first part of the Pivot clause, and this check is performed as analysis stage.
## How was this patch tested?
A new test suite PivotSuite is added.
Author: maryannxue <ma...@gmail.com>
Closes #21187 from maryannxue/spark-24035.
Project: http://git-wip-us.apache.org/repos/asf/spark/repo
Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/e3201e16
Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/e3201e16
Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/e3201e16
Branch: refs/heads/master
Commit: e3201e165e41f076ec72175af246d12c0da529cf
Parents: 94641fe
Author: maryannxue <ma...@gmail.com>
Authored: Thu May 3 17:05:02 2018 -0700
Committer: gatorsmile <ga...@gmail.com>
Committed: Thu May 3 17:05:02 2018 -0700
----------------------------------------------------------------------
.../apache/spark/sql/catalyst/parser/SqlBase.g4 | 12 +-
.../spark/sql/catalyst/analysis/Analyzer.scala | 35 +++-
.../spark/sql/catalyst/parser/AstBuilder.scala | 20 +-
.../plans/logical/basicLogicalOperators.scala | 27 ++-
.../parser/TableIdentifierParserSuite.scala | 6 +-
.../spark/sql/RelationalGroupedDataset.scala | 2 +-
.../test/resources/sql-tests/inputs/pivot.sql | 113 +++++++++++
.../resources/sql-tests/results/pivot.sql.out | 194 +++++++++++++++++++
8 files changed, 386 insertions(+), 23 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/spark/blob/e3201e16/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
----------------------------------------------------------------------
diff --git a/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 b/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
index 5fa75fe..f7f921e 100644
--- a/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
+++ b/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
@@ -398,7 +398,7 @@ hintStatement
;
fromClause
- : FROM relation (',' relation)* lateralView*
+ : FROM relation (',' relation)* (pivotClause | lateralView*)?
;
aggregation
@@ -413,6 +413,10 @@ groupingSet
| expression
;
+pivotClause
+ : PIVOT '(' aggregates=namedExpressionSeq FOR pivotColumn=identifier IN '(' pivotValues+=constant (',' pivotValues+=constant)* ')' ')'
+ ;
+
lateralView
: LATERAL VIEW (OUTER)? qualifiedName '(' (expression (',' expression)*)? ')' tblName=identifier (AS? colName+=identifier (',' colName+=identifier)*)?
;
@@ -725,7 +729,7 @@ nonReserved
| ADD
| OVER | PARTITION | RANGE | ROWS | PRECEDING | FOLLOWING | CURRENT | ROW | LAST | FIRST | AFTER
| MAP | ARRAY | STRUCT
- | LATERAL | WINDOW | REDUCE | TRANSFORM | SERDE | SERDEPROPERTIES | RECORDREADER
+ | PIVOT | LATERAL | WINDOW | REDUCE | TRANSFORM | SERDE | SERDEPROPERTIES | RECORDREADER
| DELIMITED | FIELDS | TERMINATED | COLLECTION | ITEMS | KEYS | ESCAPED | LINES | SEPARATED
| EXTENDED | REFRESH | CLEAR | CACHE | UNCACHE | LAZY | GLOBAL | TEMPORARY | OPTIONS
| GROUPING | CUBE | ROLLUP
@@ -745,7 +749,7 @@ nonReserved
| REVOKE | GRANT | LOCK | UNLOCK | MSCK | REPAIR | RECOVER | EXPORT | IMPORT | LOAD | VALUES | COMMENT | ROLE
| ROLES | COMPACTIONS | PRINCIPALS | TRANSACTIONS | INDEX | INDEXES | LOCKS | OPTION | LOCAL | INPATH
| ASC | DESC | LIMIT | RENAME | SETS
- | AT | NULLS | OVERWRITE | ALL | ALTER | AS | BETWEEN | BY | CREATE | DELETE
+ | AT | NULLS | OVERWRITE | ALL | ANY | ALTER | AS | BETWEEN | BY | CREATE | DELETE
| DESCRIBE | DROP | EXISTS | FALSE | FOR | GROUP | IN | INSERT | INTO | IS |LIKE
| NULL | ORDER | OUTER | TABLE | TRUE | WITH | RLIKE
| AND | CASE | CAST | DISTINCT | DIV | ELSE | END | FUNCTION | INTERVAL | MACRO | OR | STRATIFY | THEN
@@ -760,6 +764,7 @@ FROM: 'FROM';
ADD: 'ADD';
AS: 'AS';
ALL: 'ALL';
+ANY: 'ANY';
DISTINCT: 'DISTINCT';
WHERE: 'WHERE';
GROUP: 'GROUP';
@@ -805,6 +810,7 @@ RIGHT: 'RIGHT';
FULL: 'FULL';
NATURAL: 'NATURAL';
ON: 'ON';
+PIVOT: 'PIVOT';
LATERAL: 'LATERAL';
WINDOW: 'WINDOW';
OVER: 'OVER';
http://git-wip-us.apache.org/repos/asf/spark/blob/e3201e16/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
----------------------------------------------------------------------
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
index e821e96..dfdcdbc 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
@@ -275,9 +275,9 @@ class Analyzer(
case g: GroupingSets if g.child.resolved && hasUnresolvedAlias(g.aggregations) =>
g.copy(aggregations = assignAliases(g.aggregations))
- case Pivot(groupByExprs, pivotColumn, pivotValues, aggregates, child)
- if child.resolved && hasUnresolvedAlias(groupByExprs) =>
- Pivot(assignAliases(groupByExprs), pivotColumn, pivotValues, aggregates, child)
+ case Pivot(groupByOpt, pivotColumn, pivotValues, aggregates, child)
+ if child.resolved && groupByOpt.isDefined && hasUnresolvedAlias(groupByOpt.get) =>
+ Pivot(Some(assignAliases(groupByOpt.get)), pivotColumn, pivotValues, aggregates, child)
case Project(projectList, child) if child.resolved && hasUnresolvedAlias(projectList) =>
Project(assignAliases(projectList), child)
@@ -504,9 +504,20 @@ class Analyzer(
object ResolvePivot extends Rule[LogicalPlan] {
def apply(plan: LogicalPlan): LogicalPlan = plan transform {
- case p: Pivot if !p.childrenResolved | !p.aggregates.forall(_.resolved)
- | !p.groupByExprs.forall(_.resolved) | !p.pivotColumn.resolved => p
- case Pivot(groupByExprs, pivotColumn, pivotValues, aggregates, child) =>
+ case p: Pivot if !p.childrenResolved || !p.aggregates.forall(_.resolved)
+ || (p.groupByExprsOpt.isDefined && !p.groupByExprsOpt.get.forall(_.resolved))
+ || !p.pivotColumn.resolved => p
+ case Pivot(groupByExprsOpt, pivotColumn, pivotValues, aggregates, child) =>
+ // Check all aggregate expressions.
+ aggregates.foreach { e =>
+ if (!isAggregateExpression(e)) {
+ throw new AnalysisException(
+ s"Aggregate expression required for pivot, found '$e'")
+ }
+ }
+ // Group-by expressions coming from SQL are implicit and need to be deduced.
+ val groupByExprs = groupByExprsOpt.getOrElse(
+ (child.outputSet -- aggregates.flatMap(_.references) -- pivotColumn.references).toSeq)
val singleAgg = aggregates.size == 1
def outputName(value: Literal, aggregate: Expression): String = {
val utf8Value = Cast(value, StringType, Some(conf.sessionLocalTimeZone)).eval(EmptyRow)
@@ -568,16 +579,20 @@ class Analyzer(
// TODO: Don't construct the physical container until after analysis.
case ae: AggregateExpression => ae.copy(resultId = NamedExpression.newExprId)
}
- if (filteredAggregate.fastEquals(aggregate)) {
- throw new AnalysisException(
- s"Aggregate expression required for pivot, found '$aggregate'")
- }
Alias(filteredAggregate, outputName(value, aggregate))()
}
}
Aggregate(groupByExprs, groupByExprs ++ pivotAggregates, child)
}
}
+
+ private def isAggregateExpression(expr: Expression): Boolean = {
+ expr match {
+ case Alias(e, _) => isAggregateExpression(e)
+ case AggregateExpression(_, _, _, _) => true
+ case _ => false
+ }
+ }
}
/**
http://git-wip-us.apache.org/repos/asf/spark/blob/e3201e16/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
----------------------------------------------------------------------
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
index bdc357d..64eed23 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
@@ -503,7 +503,11 @@ class AstBuilder(conf: SQLConf) extends SqlBaseBaseVisitor[AnyRef] with Logging
val join = right.optionalMap(left)(Join(_, _, Inner, None))
withJoinRelations(join, relation)
}
- ctx.lateralView.asScala.foldLeft(from)(withGenerate)
+ if (ctx.pivotClause() != null) {
+ withPivot(ctx.pivotClause, from)
+ } else {
+ ctx.lateralView.asScala.foldLeft(from)(withGenerate)
+ }
}
/**
@@ -615,6 +619,20 @@ class AstBuilder(conf: SQLConf) extends SqlBaseBaseVisitor[AnyRef] with Logging
}
/**
+ * Add a [[Pivot]] to a logical plan.
+ */
+ private def withPivot(
+ ctx: PivotClauseContext,
+ query: LogicalPlan): LogicalPlan = withOrigin(ctx) {
+ val aggregates = Option(ctx.aggregates).toSeq
+ .flatMap(_.namedExpression.asScala)
+ .map(typedVisit[Expression])
+ val pivotColumn = UnresolvedAttribute.quoted(ctx.pivotColumn.getText)
+ val pivotValues = ctx.pivotValues.asScala.map(typedVisit[Expression]).map(Literal.apply)
+ Pivot(None, pivotColumn, pivotValues, aggregates, query)
+ }
+
+ /**
* Add a [[Generate]] (Lateral View) to a logical plan.
*/
private def withGenerate(
http://git-wip-us.apache.org/repos/asf/spark/blob/e3201e16/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala
----------------------------------------------------------------------
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala
index 10df504..3bf32ef 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala
@@ -686,17 +686,34 @@ case class GroupingSets(
override lazy val resolved: Boolean = false
}
+/**
+ * A constructor for creating a pivot, which will later be converted to a [[Project]]
+ * or an [[Aggregate]] during the query analysis.
+ *
+ * @param groupByExprsOpt A sequence of group by expressions. This field should be None if coming
+ * from SQL, in which group by expressions are not explicitly specified.
+ * @param pivotColumn The pivot column.
+ * @param pivotValues A sequence of values for the pivot column.
+ * @param aggregates The aggregation expressions, each with or without an alias.
+ * @param child Child operator
+ */
case class Pivot(
- groupByExprs: Seq[NamedExpression],
+ groupByExprsOpt: Option[Seq[NamedExpression]],
pivotColumn: Expression,
pivotValues: Seq[Literal],
aggregates: Seq[Expression],
child: LogicalPlan) extends UnaryNode {
- override def output: Seq[Attribute] = groupByExprs.map(_.toAttribute) ++ aggregates match {
- case agg :: Nil => pivotValues.map(value => AttributeReference(value.toString, agg.dataType)())
- case _ => pivotValues.flatMap{ value =>
- aggregates.map(agg => AttributeReference(value + "_" + agg.sql, agg.dataType)())
+ override lazy val resolved = false // Pivot will be replaced after being resolved.
+ override def output: Seq[Attribute] = {
+ val pivotAgg = aggregates match {
+ case agg :: Nil =>
+ pivotValues.map(value => AttributeReference(value.toString, agg.dataType)())
+ case _ =>
+ pivotValues.flatMap { value =>
+ aggregates.map(agg => AttributeReference(value + "_" + agg.sql, agg.dataType)())
+ }
}
+ groupByExprsOpt.getOrElse(Seq.empty).map(_.toAttribute) ++ pivotAgg
}
}
http://git-wip-us.apache.org/repos/asf/spark/blob/e3201e16/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/TableIdentifierParserSuite.scala
----------------------------------------------------------------------
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/TableIdentifierParserSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/TableIdentifierParserSuite.scala
index cc80a41..89903c2 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/TableIdentifierParserSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/TableIdentifierParserSuite.scala
@@ -41,12 +41,12 @@ class TableIdentifierParserSuite extends SparkFunSuite {
"sort", "sorted", "ssl", "statistics", "stored", "streamtable", "string", "struct", "tables",
"tblproperties", "temporary", "terminated", "tinyint", "touch", "transactions", "unarchive",
"undo", "uniontype", "unlock", "unset", "unsigned", "uri", "use", "utc", "utctimestamp",
- "view", "while", "year", "work", "transaction", "write", "isolation", "level",
- "snapshot", "autocommit", "all", "alter", "array", "as", "authorization", "between", "bigint",
+ "view", "while", "year", "work", "transaction", "write", "isolation", "level", "snapshot",
+ "autocommit", "all", "any", "alter", "array", "as", "authorization", "between", "bigint",
"binary", "boolean", "both", "by", "create", "cube", "current_date", "current_timestamp",
"cursor", "date", "decimal", "delete", "describe", "double", "drop", "exists", "external",
"false", "fetch", "float", "for", "grant", "group", "grouping", "import", "in",
- "insert", "int", "into", "is", "lateral", "like", "local", "none", "null",
+ "insert", "int", "into", "is", "pivot", "lateral", "like", "local", "none", "null",
"of", "order", "out", "outer", "partition", "percent", "procedure", "range", "reads", "revoke",
"rollup", "row", "rows", "set", "smallint", "table", "timestamp", "to", "trigger",
"true", "truncate", "update", "user", "values", "with", "regexp", "rlike",
http://git-wip-us.apache.org/repos/asf/spark/blob/e3201e16/sql/core/src/main/scala/org/apache/spark/sql/RelationalGroupedDataset.scala
----------------------------------------------------------------------
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/RelationalGroupedDataset.scala b/sql/core/src/main/scala/org/apache/spark/sql/RelationalGroupedDataset.scala
index 7147798..6c2be36 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/RelationalGroupedDataset.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/RelationalGroupedDataset.scala
@@ -73,7 +73,7 @@ class RelationalGroupedDataset protected[sql](
case RelationalGroupedDataset.PivotType(pivotCol, values) =>
val aliasedGrps = groupingExprs.map(alias)
Dataset.ofRows(
- df.sparkSession, Pivot(aliasedGrps, pivotCol, values, aggExprs, df.logicalPlan))
+ df.sparkSession, Pivot(Some(aliasedGrps), pivotCol, values, aggExprs, df.logicalPlan))
}
}
http://git-wip-us.apache.org/repos/asf/spark/blob/e3201e16/sql/core/src/test/resources/sql-tests/inputs/pivot.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/inputs/pivot.sql b/sql/core/src/test/resources/sql-tests/inputs/pivot.sql
new file mode 100644
index 0000000..01dea6c
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/pivot.sql
@@ -0,0 +1,113 @@
+create temporary view courseSales as select * from values
+ ("dotNET", 2012, 10000),
+ ("Java", 2012, 20000),
+ ("dotNET", 2012, 5000),
+ ("dotNET", 2013, 48000),
+ ("Java", 2013, 30000)
+ as courseSales(course, year, earnings);
+
+create temporary view years as select * from values
+ (2012, 1),
+ (2013, 2)
+ as years(y, s);
+
+-- pivot courses
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ sum(earnings)
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot years with no subquery
+SELECT * FROM courseSales
+PIVOT (
+ sum(earnings)
+ FOR year IN (2012, 2013)
+);
+
+-- pivot courses with multiple aggregations
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ sum(earnings), avg(earnings)
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot with no group by column
+SELECT * FROM (
+ SELECT course, earnings FROM courseSales
+)
+PIVOT (
+ sum(earnings)
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot with no group by column and with multiple aggregations on different columns
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ sum(earnings), min(year)
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot on join query with multiple group by columns
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ sum(earnings)
+ FOR s IN (1, 2)
+);
+
+-- pivot on join query with multiple aggregations on different columns
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ sum(earnings), min(s)
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot on join query with multiple columns in one aggregation
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ sum(earnings * s)
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot with aliases and projection
+SELECT 2012_s, 2013_s, 2012_a, 2013_a, c FROM (
+ SELECT year y, course c, earnings e FROM courseSales
+)
+PIVOT (
+ sum(e) s, avg(e) a
+ FOR y IN (2012, 2013)
+);
+
+-- pivot years with non-aggregate function
+SELECT * FROM courseSales
+PIVOT (
+ abs(earnings)
+ FOR year IN (2012, 2013)
+);
+
+-- pivot with unresolvable columns
+SELECT * FROM (
+ SELECT course, earnings FROM courseSales
+)
+PIVOT (
+ sum(earnings)
+ FOR year IN (2012, 2013)
+);
http://git-wip-us.apache.org/repos/asf/spark/blob/e3201e16/sql/core/src/test/resources/sql-tests/results/pivot.sql.out
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/results/pivot.sql.out b/sql/core/src/test/resources/sql-tests/results/pivot.sql.out
new file mode 100644
index 0000000..85e3488
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/pivot.sql.out
@@ -0,0 +1,194 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 13
+
+
+-- !query 0
+create temporary view courseSales as select * from values
+ ("dotNET", 2012, 10000),
+ ("Java", 2012, 20000),
+ ("dotNET", 2012, 5000),
+ ("dotNET", 2013, 48000),
+ ("Java", 2013, 30000)
+ as courseSales(course, year, earnings)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view years as select * from values
+ (2012, 1),
+ (2013, 2)
+ as years(y, s)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ sum(earnings)
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 2 schema
+struct<year:int,dotNET:bigint,Java:bigint>
+-- !query 2 output
+2012 15000 20000
+2013 48000 30000
+
+
+-- !query 3
+SELECT * FROM courseSales
+PIVOT (
+ sum(earnings)
+ FOR year IN (2012, 2013)
+)
+-- !query 3 schema
+struct<course:string,2012:bigint,2013:bigint>
+-- !query 3 output
+Java 20000 30000
+dotNET 15000 48000
+
+
+-- !query 4
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ sum(earnings), avg(earnings)
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 4 schema
+struct<year:int,dotNET_sum(CAST(earnings AS BIGINT)):bigint,dotNET_avg(CAST(earnings AS BIGINT)):double,Java_sum(CAST(earnings AS BIGINT)):bigint,Java_avg(CAST(earnings AS BIGINT)):double>
+-- !query 4 output
+2012 15000 7500.0 20000 20000.0
+2013 48000 48000.0 30000 30000.0
+
+
+-- !query 5
+SELECT * FROM (
+ SELECT course, earnings FROM courseSales
+)
+PIVOT (
+ sum(earnings)
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 5 schema
+struct<dotNET:bigint,Java:bigint>
+-- !query 5 output
+63000 50000
+
+
+-- !query 6
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ sum(earnings), min(year)
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 6 schema
+struct<dotNET_sum(CAST(earnings AS BIGINT)):bigint,dotNET_min(year):int,Java_sum(CAST(earnings AS BIGINT)):bigint,Java_min(year):int>
+-- !query 6 output
+63000 2012 50000 2012
+
+
+-- !query 7
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ sum(earnings)
+ FOR s IN (1, 2)
+)
+-- !query 7 schema
+struct<course:string,year:int,1:bigint,2:bigint>
+-- !query 7 output
+Java 2012 20000 NULL
+Java 2013 NULL 30000
+dotNET 2012 15000 NULL
+dotNET 2013 NULL 48000
+
+
+-- !query 8
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ sum(earnings), min(s)
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 8 schema
+struct<year:int,dotNET_sum(CAST(earnings AS BIGINT)):bigint,dotNET_min(s):int,Java_sum(CAST(earnings AS BIGINT)):bigint,Java_min(s):int>
+-- !query 8 output
+2012 15000 1 20000 1
+2013 48000 2 30000 2
+
+
+-- !query 9
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ sum(earnings * s)
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 9 schema
+struct<year:int,dotNET:bigint,Java:bigint>
+-- !query 9 output
+2012 15000 20000
+2013 96000 60000
+
+
+-- !query 10
+SELECT 2012_s, 2013_s, 2012_a, 2013_a, c FROM (
+ SELECT year y, course c, earnings e FROM courseSales
+)
+PIVOT (
+ sum(e) s, avg(e) a
+ FOR y IN (2012, 2013)
+)
+-- !query 10 schema
+struct<2012_s:bigint,2013_s:bigint,2012_a:double,2013_a:double,c:string>
+-- !query 10 output
+15000 48000 7500.0 48000.0 dotNET
+20000 30000 20000.0 30000.0 Java
+
+
+-- !query 11
+SELECT * FROM courseSales
+PIVOT (
+ abs(earnings)
+ FOR year IN (2012, 2013)
+)
+-- !query 11 schema
+struct<>
+-- !query 11 output
+org.apache.spark.sql.AnalysisException
+Aggregate expression required for pivot, found 'abs(earnings#x)';
+
+
+-- !query 12
+SELECT * FROM (
+ SELECT course, earnings FROM courseSales
+)
+PIVOT (
+ sum(earnings)
+ FOR year IN (2012, 2013)
+)
+-- !query 12 schema
+struct<>
+-- !query 12 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`year`' given input columns: [__auto_generated_subquery_name.course, __auto_generated_subquery_name.earnings]; line 4 pos 0
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org