siddharthteotia commented on code in PR #9826:
URL: https://github.com/apache/pinot/pull/9826#discussion_r1027718283
##########
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",
Review Comment:
Basically WITH can serve for decoration as well (using self-join) where we
want to enhance the result of a standard GROUP BY aggregation query on a table
with additional columns from the same table.
Something like ....
```
WITH maxResult AS (
SELECT
groupCol1,
groupCol2,
MAX(aggCol) AS M
FROM T
GROUP BY groupCol1, groupCol2
)
SELECT T.*, maxResult.M
FROM T
JOIN
maxResult
ON
T.groupCol1 = maxResult.groupCol1
AND
T.groupCol2 = maxResult.groupCol2
```
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
