siddharthteotia commented on code in PR #9826:
URL: https://github.com/apache/pinot/pull/9826#discussion_r1027784767
##########
pinot-query-runtime/src/test/resources/queries/WithStatements.json:
##########
@@ -0,0 +1,81 @@
+{
+ "with_statement_tests": {
+ "psql": "7.8.1",
+ "tables": {
+ "tbl1": {
+ "schema": [
+ {"name": "strCol", "type": "STRING"},
+ {"name": "intCol", "type": "INT"}
+ ],
+ "inputs": [
+ ["a", 3],
+ ["b", 2],
+ ["c", 5],
+ ["a", 1]
+ ]
+ },
+ "tbl2": {
+ "schema": [
+ {"name": "strCol1", "type": "STRING"},
+ {"name": "strCol2", "type": "STRING"},
+ {"name": "intCol", "type": "INT"}
+ ],
+ "inputs": [
+ ["a", "foo", 1],
+ ["a", "bar", 2],
+ ["b", "alice", 42],
+ ["b", "bob", 196883]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "single with with group by",
+ "sql": "WITH w AS ( SELECT strCol, SUM(intCol) AS sumRes FROM {tbl1}
GROUP BY strCol ) SELECT strCol, strCol2, sumRes FROM w JOIN {tbl2} ON w.strCol
= {tbl2}.strCol1",
+ "outputs": [
+ ["a", "foo", 4],
+ ["a", "bar", 4],
+ ["b", "alice", 2],
+ ["b", "bob", 2]
+ ]
+ },
+ {
+ "description": "multi with table",
+ "sql": "WITH agg1 AS ( SELECT strCol, sum(intCol) AS sumVal FROM
{tbl1} GROUP BY strCol), agg2 AS (SELECT strCol1, avg(intCol) AS avgVal FROM
{tbl2} GROUP BY strCol1) SELECT strCol, sumVal - avgVal FROM agg1, agg2 WHERE
agg1.strCol = agg2.strCol1",
Review Comment:
In the outer query, can we also project individual aggregations computed in
the respective CTEs and also try one more kind of a transform ? Something like
following
```
SELECT
strCol
sumVal AS agg1_sum, -> project from CTE
avgVal AS agg2_avg, -> project from CTE
sumVal + avgVal, -> + transform
sumVal - avgVal
FROM agg1, agg2
WHERE agg1.strCol = agg2.strCol1
```
Same or different query should be fine.
--
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: commits-unsubscr...@pinot.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
