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 in outer query ?
Something like following
```
SELECT
strCol
sumVal AS agg1_sum,
avgVal AS agg2_avg,
sumVal + avgVal,
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
