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 I think `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 by projecting additional columns from the same table in the final
result
Something like ....
```
WITH maxResult AS (
SELECT
groupCol1,
groupCol2,
MAX(aggCol) AS M
FROM T
GROUP BY groupCol1, groupCol2
)
SELECT T.*, maxResult.M -> projecting extra columns
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]
