Support GROUPING SETS by novikd · Pull Request #33631 · ClickHouse/ClickHouse

novikd · 2022-01-14T13:24:23Z

Changelog category (leave one):

New Feature

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):

Add support of GROUPING SETS in GROUP BY clause. Follow up after #33186. This implementation supports a parallel processing of grouping sets.

…ter grouping sets

…nsformer

2. Update formatAST to support grouping sets query with distributed table 3. modify astExpression to support function in grouping sets

src/Interpreters/ExpressionAnalyzer.h

KochetovNicolai · 2022-04-29T11:03:20Z

src/Parsers/ASTExpressionList.cpp

+        if (frame.surround_each_list_element_with_parens)
+            settings.ostr << "(";
+
+        FormatStateStacked frame_nested = frame;
+        frame_nested.surround_each_list_element_with_parens = false;
+        (*it)->formatImpl(settings, state, frame_nested);
+
+        if (frame.surround_each_list_element_with_parens)
+            settings.ostr << ")";


For some reason, I have an extra space while formatting in client:

ELECT SUM(number) AS sum_value, count() AS count_value FROM numbers_mt(1000000) GROUP BY GROUPING SETS ( ( number % 10), ^ here ( number % 100)) ^ here ORDER BY sum_value ASC, count_value ASC

It's fixed in #32999

src/Interpreters/InterpreterSelectQuery.h

src/Processors/QueryPlan/AggregatingStep.cpp

src/Processors/Transforms/GroupingSetsTransform.cpp

src/Processors/Transforms/GroupingSetsTransform.h

src/QueryPipeline/Pipe.cpp

KochetovNicolai · 2022-04-29T11:37:32Z

Compare result for

SELECT
    k1,
    k2,
    SUM(number) AS sum_value,
    count() AS count_value
FROM numbers(6)
GROUP BY
    GROUPING SETS (
    ( number % 2 AS k1),
    ( number % 3 AS k2))
ORDER BY
    sum_value ASC,
    count_value ASC

Query id: 080ee3d9-1856-41af-9185-775663655c71

┌─k1─┬─k2─┬─sum_value─┬─count_value─┐
│  0 │  0 │         3 │           2 │
│  0 │  1 │         5 │           2 │
│  0 │  0 │         6 │           3 │
│  0 │  2 │         7 │           2 │
│  1 │  0 │         9 │           3 │
└────┴────┴───────────┴─────────────┘

and

SELECT
    k1,
    k2,
    SUM(number) AS sum_value,
    count() AS count_value
FROM remote('127.0.0.{2,3}', numbers(6))
GROUP BY
    GROUPING SETS (
    ( number % 2 AS k1),
    ( number % 3 AS k2))
ORDER BY
    sum_value ASC,
    count_value ASC

Query id: 5fc9bf53-4389-4e9b-b4ab-4cee8ba5e082

┌─k1─┬─k2─┬─sum_value─┬─count_value─┐
│  0 │  1 │        10 │           4 │
│  0 │  2 │        14 │           4 │
│  1 │  0 │        18 │           6 │
│  0 │  0 │        18 │          10 │
└────┴────┴───────────┴─────────────┘

The key (0, 0) was squashed.
That's why we also need __grouping column for intermediate header (and merging transform should use in as an additional key)

KochetovNicolai

Generally everything is ok.
Except it would be good to fix key squashing for the distributed case :)

And fix execution of the query with only one grouping set

novikd · 2022-05-09T14:01:08Z

@Mergifyio update

mergify · 2022-05-09T14:02:11Z

update

✅ Branch has been successfully updated

KochetovNicolai · 2022-05-11T19:28:42Z

ok, failed tests does not seem to be relevant to the PR, merging.

MaxTheHuman and others added 30 commits January 11, 2022 11:26

init commit with parsing and BAD realisation

a8f4f3d

rm unnessesary data committed by mistake

007969a

remove another unnessesary files

2c3c41d

revert changes made to cube transform

aa46ddc

fix typo

544bb57

erase blank line to restore initial state

209ce51

fixes

34d71ee

fix typos

e0548d6

feat grouping-sets: initial changes

3195d60

development

390c812

grouping sets development

abe0932

grouping sets development

4d1b354

grouping sets development

e6bd807

grouping sets cleanup

715d4c9

grouping sets: fix 'Port already connected' error

daea6fc

grouping sets dev: fix errors, something works

e22cbb7

grouping sets: make simple aggregation with grouping sets to work

c7c2093

grouping sets: fix

5978233

grouping sets: add tests, fix bug

233471d

grouping-sets: rearrange result columns so that resize is possible af…

fa32350

…ter grouping sets

make grouping sets work with total

a2d2868

grouping sets support multiple group

9123386

grouing sets transformer instead of fork processor in aggregating tra…

20d8614

…nsformer

fix bug when no data in grouping sets transformer

8d4d483

correct test case results

4b25d30

some changes

83e9e5d

refactor

a2c2317

1. refactor ExpressionAnalyzer

6c6d301

2. Update formatAST to support grouping sets query with distributed table 3. modify astExpression to support function in grouping sets

update test reference

b81a958

update test reference

668a708