SQL - порядок по промежуточному итогу в групповом наборе

Question

Это мое текущее заявление:

SELECT
  COLUMN1,
  COLUMN2,
  COLUMN3,
  COLUMN4,
  COLUMN5,
  COUNT(*) COUNTER
FROM
  TABLE
GROUP BY
  GROUPING SETS ((COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5), COLUMN1);

Я никогда раньше не использовал GROUPING SETS, поэтому я не уверен, правильно ли я их использую, но мой вывод выглядит правильно. Но чего мне не хватает, так это того, что я хочу заказать по счетчику промежуточных итогов, а затем по моей COLUMN1. Мой текущий вывод, как показано ниже:

COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COUNTER
TEST0   TEST1   TEST3   TEST3   TEST4   1
TEST0                                   1
TEST    TEST    TEST    TEST    TEST    1
TEST    TEST1   TEST1   TEST1   TEST1   1
TEST    TEST2   TEST2   TEST3   TEST4   1
TEST                                    3
TEST2   TEST3   TEST4   TEST5   TEST6   1
TEST2                                   1

Вывод, который я хотел бы получить:

COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COUNTER
TEST    TEST    TEST    TEST    TEST    1
TEST    TEST1   TEST1   TEST1   TEST1   1
TEST    TEST2   TEST2   TEST3   TEST4   1
TEST                                    3
TEST0   TEST1   TEST3   TEST3   TEST4   1
TEST0                                   1
TEST2   TEST3   TEST4   TEST5   TEST6   1
TEST2                                   1

Упорядочено по промежуточному счетчику от наименьшего к наибольшему, а затем по COLUMN1.

Answer 1

Вы можете использовать функцию MAX в качестве аналитической функции для упорядочения результатов.

SELECT
COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
COLUMN5,
COUNTER
FROM (
    SELECT
    COLUMN1,
    COLUMN2,
    COLUMN3,
    COLUMN4,
    COLUMN5,
    COUNTER,
    MAX(counter) KEEP (DENSE_RANK FIRST ORDER BY counter DESC) OVER (PARTITION BY first_name) AS maxcount
    FROM (
      SELECT
      COLUMN1,
      COLUMN2,
      COLUMN3,
      COLUMN4,
      COLUMN5,
      COUNT(*) AS COUNTER
      FROM
      TABLE
      GROUP BY GROUPING SETS ((COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5), COLUMN1)
    )
)
ORDER BY maxcount DESC, COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5;

Вы также можете изменить ORDER BY, чтобы настроить порядок упорядочения результатов.

На прошлой неделе я только что написал статью о GROUP BY и связанных с ней функциях, которая может оказаться полезной: Oracle GROUP BY - Полное руководство. Мне придется обновить его, чтобы добавить эту функциональность заказа.