Строки данных плюс итоговая строка в одном сканировании

Question

Представьте, что у нас есть следующие данные в таблице:

groupName  volume  class  mark
---------- ------- ------ ----
group1     50      1      o
group1     50      1      o
group1     50      1      x
group1     25      2      o
group2     25      1      x
group2     17      3      x
group2     11      2      o
group3     11      1      o
group3     19      3      x

И есть необходимость добавить итоговую строку в конце (SUM для объема и NULL для остальных столбцов).

Я знаю, что то, что мне нужно, может быть достигнуто путем сложения union all как:

select 0 as isTotal, groupName, class, mark, volume
from dataTable
union all
select 1, NULL, NULL, NULL, sum(volume)
from dataTable
order by isTotal, groupName, class

но этот способ таблицы сканируется дважды.

Чтобы избежать сканирования данных дважды, я попытался использовать group by:

select grouping(groupName) as isTotal, groupName, class, mark, sum(volume) as volume
from dataTable
group by grouping sets ((), (groupName, class, mark, volume))
order by isTotal, groupName, class

таким образом, выполняется только одно сканирование таблицы, и я получаю почти то, что мне нужно, за исключением того, что две первые строки данных примера (которые являются дубликатами) объединены в одну, и мне нужно, чтобы дубликаты были сохранены в виде отдельных строк.

Вопрос: возможно ли получить данные таблицы с добавленной строкой итогов, чтобы данные таблицы сканировались один раз, а дубликаты сохранялись в виде отдельных строк?

Желаемый результат - это то, что возвращает union all запрос:

isTotal groupName   class  mark volume
------- ----------- ------ ---- -------
0       group 1     1      o    50
0       group 1     1      o    50
0       group 1     1      x    50
0       group 1     2      o    25
0       group 2     1      x    25
0       group 2     2      o    11
0       group 2     3      x    17
0       group 3     1      o    11
0       group 3     3      x    19
1       NULL        NULL   NULL 258

Результат, возвращаемый group by grouping sets запрос:

isTotal groupName  class  mark volume
------- ---------- ------ ---- -------
0       group 1    1      o    100
0       group 1    1      x    50
0       group 1    2      o    25
0       group 2    1      x    25
0       group 2    2      o    11
0       group 2    3      x    17
0       group 3    1      o    11
0       group 3    3      x    19
1       NULL       NULL   NULL 258

Answer 1

Даже если у вас есть дублирующиеся строки, вы можете сделать их уникальными и решить вашу проблему. Одним из способов сделать это является использование ROW_NUMBER функция.

Например:

DECLARE @DataSource TABLE
(
    [groupName] VARCHAR(6)
   ,[volume] TINYINT
   ,[class] TINYINT
   ,[mark] CHAR(1)
);

INSERT INTO @DataSource ([groupName], [volume], [class], [mark])
VALUES ('group1', '50', '1', 'x')
      ,('group1', '50', '1', 'x')
      ,('group1', '50', '1', 'o')
      ,('group1', '25', '2', 'o')
      ,('group2', '25', '1', 'x')
      ,('group2', '17', '3', 'x')
      ,('group2', '11', '2', 'o')
      ,('group3', '11', '1', 'o')
      ,('group3', '19', '3', 'x');

WITH DataSource ([rowID], [groupName], [volume], [class], [mark]) AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
          ,[groupName]
          ,[volume]
          ,[class]
          ,[mark]
    FROM @DataSource
)
SELECT GROUPING([groupName]) as [isTotal]
      ,[groupName]
      ,[class]
      ,[mark]
      ,SUM([volume]) AS [volume]
FROM DataSource
GROUP BY GROUPING SETS ((), ([rowID], [groupName], [volume], [class], [mark]))
ORDER BY [isTotal]
        ,[groupName]
        ,[class];

дам тебе:

точно так же, как ваш первоначальный запрос:

select 0 as isTotal, groupName, class, mark, volume
from @DataSource
union all
select 1, NULL, NULL, NULL, sum(volume)
from @DataSource
order by isTotal, groupName, class

Если вы сравните планы выполнения, вы увидите, что выполняется только одно сканирование таблицы: