Как создать две строки общего итога с помощью SQL — итоги и средние значения

Мне нужно создать две строки, содержащие итоги, в отличие от типичной строки итогов. Общие итоги и средние значения.

Я создаю отчет, используя базовый SQL, и я работаю с базой данных Oracle, но я не использую PL/SQL.

В настоящее время я использую Group By Grouping Sets для создания отчета, и одна строка — это строка, содержащая общие итоги. Эти итоги в настоящее время производятся с использованием SUM (столбец) с использованием сочетания агрегатных и аналитических функций для получения моей одной строки общих итогов. Мне нужна еще одна строка, которая выводит итоги по тому же набору данных. Каков наилучший способ добиться этого? Когда я говорю лучше всего, я думаю о нагрузке на мою базу данных, поскольку этот отчет будет работать с тоннами данных. Мои примеры чрезвычайно просты, но суть понятна.

Ниже приведены некоторые примеры данных, которые позволяют получить суммарные общие итоги с использованием наборов группировки. Чего не хватает, так это еще одной строки под общими итогами, которую я хотел бы получить в среднем.

WITH sample_data AS
(
  SELECT 1 AS client_key, 'NASA'   AS client, 8 AS SPACESHIPS_SOLD, 105585 AS REVENUE FROM DUAL UNION ALL
  SELECT 2 AS client_key, 'Origin' AS client, 3 AS SPACESHIPS_SOLD, 36581  AS REVENUE FROM DUAL UNION ALL
  SELECT 3 AS client_key, 'SpaceX' AS client, 7 AS SPACESHIPS_SOLD, 83851  AS REVENUE FROM DUAL
)

SELECT sd.client_key
  , CASE WHEN grouping(sd.client) = 0 THEN to_char(sd.client) ELSE 'Grand Totals -->' END AS client
  , SUM(sd.spaceships_sold) AS spaceships_sold
  , SUM(sd.revenue)         AS revenue
FROM sample_data sd
GROUP BY 
  GROUPING SETS (
                  (sd.client_key, sd.client),
                  ()
                )
;

Пример Изображение того, что я ищу.

введите здесь описание изображения

Ниже приведены мои мысли о том, как я могу получить эту дополнительную строку итогов, но не уверен, что это то, что я должен делать, чтобы получить это. Кажется запутанным, и я продолжаю думать, что это должно быть существующей функцией групповых наборов. В приведенном ниже подходе я использую CTE и UNION ALL, чтобы получить дополнительные средние значения в нижней части моего набора данных, как показано на снимке экрана ниже.

введите здесь описание изображения

SQL из приведенного выше снимка экрана.

WITH sample_data AS
(
  SELECT 1 AS client_key, 'NASA'   AS client, 8 AS SPACESHIPS_SOLD, 105585 AS REVENUE FROM DUAL UNION ALL
  SELECT 2 AS client_key, 'Origin' AS client, 3 AS SPACESHIPS_SOLD, 36581  AS REVENUE FROM DUAL UNION ALL
  SELECT 3 AS client_key, 'SpaceX' AS client, 7 AS SPACESHIPS_SOLD, 83851  AS REVENUE FROM DUAL
)

, data_Sum_totals AS
(
  SELECT sd.client_key
    , CASE WHEN grouping(sd.client) = 0 THEN to_char(sd.client) ELSE 'Grand Totals -->' END AS client
    , SUM(sd.spaceships_sold) AS spaceships_sold
    , SUM(sd.revenue)         AS revenue
  FROM sample_data sd
  GROUP BY 
    GROUPING SETS (
                    (sd.client_key, sd.client),
                    ()
                  )
)

, data_Avg_totals AS
(
  SELECT grouping(sd.client_key) AS row_group
    , sd.client_key
    , CASE WHEN grouping(sd.client) = 0 THEN to_char(sd.client) ELSE 'AVG Totals -->' END AS client
    , AVG(sd.spaceships_sold) AS spaceships_sold
    , AVG(sd.revenue)         AS revenue
  FROM sample_data sd
  GROUP BY 
    GROUPING SETS (
                    (sd.client_key, sd.client),
                    ()
                  )
  HAVING grouping(sd.client_key) = 1 /* This line restricts the output to only give me the Totals row */
)

SELECT client_key, client, spaceships_sold, revenue
FROM data_Sum_totals
  UNION ALL
SELECT client_key, client, spaceships_sold, revenue
FROM data_Avg_totals
;

sql oracle group-by oracle11g grouping-sets
person Code Novice    schedule 08.01.2019    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вы указали:

Я продолжаю думать, что это должно быть существующей функцией группировки Sets. В приведенном ниже подходе я использую CTE и UNION ALL, чтобы получить дополнительные средние значения в нижней части моего набора данных, как показано на снимке экрана ниже

и как определяется [grouping-sets] тег :

Операция GROUPING SETS является расширением предложения GROUP BY. Он может генерировать тот же набор результатов, что и при использовании UNION ALL для объединения запросов с одной группировкой; однако использование оператора GROUPING SETS обычно более эффективно.

Поэтому у вас был такой хороший подход.

Я думаю, что использование GROUPING_ID лучше всего подходит для вашего случая, как в следующем операторе SQL:

SELECT client_key, 
       CASE WHEN flag = 3 THEN 'AVG Totals -.->' 
            WHEN flag = 2 THEN 'Grand Totals -.->'
            ELSE client 
        END AS client , 
       SUM(spaceships_sold)/ DECODE(flag,3,3,1) AS spaceships_sold, 
       SUM(revenue)/ DECODE(flag,3,3,1) AS revenue
  FROM
  (
    WITH sample_data AS
    (
     SELECT 1 AS client_key, 'NASA'   AS client, 8 AS SPACESHIPS_SOLD, 105585 AS REVENUE FROM DUAL 
     UNION ALL
     SELECT 2 AS client_key, 'Origin' AS client, 3 AS SPACESHIPS_SOLD, 36581  AS REVENUE FROM DUAL 
     UNION ALL
     SELECT 3 AS client_key, 'SpaceX' AS client, 7 AS SPACESHIPS_SOLD, 83851  AS REVENUE FROM DUAL
     )
      SELECT sd.client_key, 
             nvl2(sd.client_key,client,null) AS client
           , SUM(sd.spaceships_sold) AS spaceships_sold
           , SUM(sd.revenue)         AS revenue
           , GROUPING_ID(sd.client_key, sd.client) AS flag
        FROM sample_data sd
       GROUP BY 
      GROUPING SETS (
                      (sd.client_key, sd.client),
                       (sd.client),()
                      )
    )    
  GROUP BY client_key, flag, client
  ORDER BY client_key, revenue desc;



  CLIENT_KEY    CLIENT           SPACESHIPS_SOLD    REVENUE
  -----------   ---------------- ---------------   --------
       1        NASA                   8             105585
       2        Origin                 3              36581
       3        SpaceX                 7              83851
      NULL      Grand Totals -.->     18             226017
      NULL      AVG Totals -.->        6              75339

Демонстрация Rextester

Обновление до SQL для работы с любым числом или записями, также известными как клиенты.

SELECT client_key, 
     CASE WHEN flag = 3 THEN 'AVG Totals -->' 
          WHEN flag = 2 THEN 'Grand Totals -->'
          ELSE client 
      END AS client 
      , flag,
     SUM(spaceships_sold)/ DECODE(flag,3,tot_clients,1) AS spaceships_sold, 
     SUM(revenue)/ DECODE(flag,3,tot_clients,1) AS revenue
FROM
(
  WITH sample_data AS
  (
     SELECT 1 AS client_key, 'NASA'   AS client, 8  AS SPACESHIPS_SOLD, 105585  AS REVENUE FROM DUAL 
     UNION ALL
     SELECT 2 AS client_key, 'Origin' AS client, 3  AS SPACESHIPS_SOLD, 36581   AS REVENUE FROM DUAL 
     UNION ALL
     SELECT 3 AS client_key, 'SpaceX' AS client, 7  AS SPACESHIPS_SOLD, 83851   AS REVENUE FROM DUAL
     UNION ALL
     SELECT 4 AS client_key, 'Comp'   AS client, 4  AS SPACESHIPS_SOLD, 95823   AS REVENUE FROM DUAL
     UNION ALL
     SELECT 4 AS client_key, 'CNSA'   AS client, 11 AS SPACESHIPS_SOLD, 135851  AS REVENUE FROM DUAL
   )
    SELECT sd.client_key, 
           nvl2(sd.client_key,client,null) AS client
         , SUM(sd.spaceships_sold) AS spaceships_sold
         , SUM(sd.revenue)         AS revenue
         , COUNT(sd.client_key)    AS tot_clients
         , GROUPING_ID(sd.client_key, sd.client) AS flag
      FROM sample_data sd
     GROUP BY 
    GROUPING SETS (
                    (sd.client_key, sd.client),
                     (sd.client),()
                    )
  )    
GROUP BY client_key, flag, client, tot_clients
ORDER BY client_key, revenue desc
;
person Barbaros Özhan    schedule 09.01.2019
comment
Вау... мне потребовалось немного времени, чтобы понять, как он создает среднее значение. Очень изящный «трюк» с использованием DECODE, однако жесткое кодирование 3 в декодировании терпит неудачу для любого выбора, который меньше или больше 3 записей, или в отношении выборки данных количество клиентов. Ваш ответ был НАСТОЛЬКО близок, но он позволил мне найти лучшее решение моего вопроса. Вместо жесткого кодирования 3 в декодировании я создаю общее количество записей, используя COUNT(sd.client_key) AS tot_clients и использую tot_clients в качестве делителя, заменяя 3. DECODE(flag,3,tot_clients,1) AS доход. - person Code Novice; 10.01.2019
comment
@CodeNovice хороший улов. - person Barbaros Özhan; 10.01.2019
comment
Я пошел дальше и добавил свой новый SQL к вашему ответу, а затем пометил ваш пост как ответ. Спасибо за очень интересное решение. - person Code Novice; 10.01.2019

arrow_upward
1
arrow_downward

CTE — это оконные функции, поэтому их нельзя перенести так далеко, как вы ожидаете. Для этой проблемы, я думаю, у вас есть хорошая идея, но, вероятно, вы просто использовали бы пару временных таблиц для размещения определенных данных, а затем ОБЪЕДИНЯЙТЕ все вместе в конце.

Вот запрос, который я придумал:

-- Clear out temporary tables
IF OBJECT_ID('tempdb.dbo.#SampleData') IS NOT NULL DROP TABLE #SampleData
IF OBJECT_ID('tempdb.dbo.#TotTable') IS NOT NULL DROP TABLE #TotTable
IF OBJECT_ID('tempdb.dbo.#AvgTable') IS NOT NULL DROP TABLE #AvgTable

-- Create
DECLARE @_tot INT
DECLARE @_avg NUMERIC(18,2)
DECLARE @client_count INT

-- Sample Data
CREATE TABLE #SampleData (
    [CLIENT_KEY] INT,
    [CLIENT] NVARCHAR(10),
    [SPACESHIPS_SOLD] VARCHAR(10),
    [REVENUE] VARCHAR(25)
)

INSERT INTO #SampleData
VALUES (1,'NASA','8','105585'),
        (2,'Origin','3','36581'),
        (3,'SpaceX','7','83851')


-- Get our total numbers
SELECT 'Grand Totals' AS [Name],
SUM(CONVERT(INT, [REVENUE])) AS [Total_Rev],
SUM(CONVERT(INT, [SPACESHIPS_SOLD])) AS [Ships_Sold] 
INTO #TotTable
FROM #SampleData

-- Get our average numbers
SET @client_count = (SELECT COUNT([CLIENT]) FROM #SampleData)
SELECT 'AVG Totals' AS [Name],
SUM(CONVERT(INT, [REVENUE])) / COUNT(*) AS [Avg_Rev],
SUM(CONVERT(INT, [SPACESHIPS_SOLD])) / @client_count AS [Avg_Sold]
INTO #AvgTable
FROM #SampleData

-- Union it all together
SELECT
    [CLIENT_KEY],
    [CLIENT],
    [SPACESHIPS_SOLD],
    [REVENUE]
FROM #SampleData
UNION ALL
SELECT
    NULL AS [CLIENT_KEY],
    [Name] AS [CLIENT],
    [Ships_Sold]  [SPACESHIPS_SOLD],
    [Total_Rev] AS [REVENUE]
FROM #TotTable
UNION ALL
SELECT
    NULL AS [CLIENT_KEY],
    [Name] AS [CLIENT],
    [Avg_Sold]  [SPACESHIPS_SOLD],
    [Avg_Rev] AS [REVENUE]
FROM #AvgTable

--Clear out tables (not necessary, but nice to do)
IF OBJECT_ID('tempdb.dbo.#SampleData') IS NOT NULL DROP TABLE #SampleData
IF OBJECT_ID('tempdb.dbo.#TotTable') IS NOT NULL DROP TABLE #TotTable
IF OBJECT_ID('tempdb.dbo.#AvgTable') IS NOT NULL DROP TABLE #AvgTable
person Mark Moretto    schedule 08.01.2019
comment
Очень аккуратно, однако я работаю в оракуле и не имею доступа к временным таблицам так, как вы можете получить к ним доступ с помощью SQL Server. Я считаю, что то, что вы опубликовали, похоже, является MS SQL Server, судя по всему. Спасибо за ваше решение, несмотря ни на что. Я также ограничен использованием только старого простого Oracle SQL. Я могу использовать DML, но не могу использовать какие-либо функции DDL. - person Code Novice; 08.01.2019