Блог

Главная - Вопросы по программированию - сводная совокупная сумма по годам, затем выбираем top 5

сводная совокупная сумма по годам, затем выбираем top 5

#sql #sql-server #pivot

#sql #sql-сервер #сводная

Вопрос:

Я пытаюсь получить сводные данные за некоторый год, чтобы получить совокупный итог по мере увеличения лет, а затем получить top 5.

Я попытался использовать SUM для total для лет в столбце year, но, похоже, она не увеличивается. Проблема, я думаю, связана с некоторыми потенциально нулевыми значениями?

Данные в таблице в настоящее время выглядят как 

 Name | ApplesEaten | Year
Bob  |      2      | 2012
Bob  |      5      | 2016
Elvis|      1      | 2017
Elvis|      2      | 2012
Sam  |      8      | 2008
Elvis|      6      | 2004
Sam  |      24     | 2019
Sarah|      14     | 2015
Bob  |      6      | 2005
Rachel|      12     | 2010
Rachel|      10     | 2008
Bob  |      82     | 2006

Но я стремлюсь получить его как 

 Name| 2004 | 2005 | 2006 ..... 
Bob |   0  |  6   |  88

Следующая проблема — получить top 5 в целом после завершения pivot!

Ответ №1:

Это то, что вы хотите?

 select top (5) name,
       sum(case when year <= 2005 then ApplesEaten else 0 end) as apples_2005,
       sum(case when year <= 2006 then ApplesEaten else 0 end) as apples_2006,
       . . .
       sum(case when year <= 2019 then ApplesEaten else 0 end) as apples_2019
from t
group by name
order by sum(ApplesEaten) desc

Комментарии:

1. Есть ли способ сделать его динамичным? Вместо того, чтобы жестко указывать годы в?

2. и даст ли это мне top 5 в общей сложности после wards? или только первые 5?

3. @samsmith . . . Это дает top 5 на основе order by , который предположительно является общим. Если вы хотите динамический свод, вам нужно использовать динамический SQL. Это означает, что вам нужно сконструировать запрос в виде строки. Возможно, но намного сложнее.

4. хорошо, это делает в значительной степени то, что я хочу, кроме добавления предыдущего года к текущему году. Итак, в моем примере Боб съел 6 яблок в 2005 году, а затем 82 в 2006 году, поэтому в 2006 году показано 88.

5. @samsmith . . . Если они являются совокупными, используйте <= . Я изменил ответ.

Ответ №2:

вы можете использовать вариант, когда

 select top 5 name, max(case when year=2004  then ApplesEaten end ) [2004],
        max(case when year=2005  then ApplesEaten end  ) [2005],
        max(case when year=2006  then ApplesEaten end  ) [2006],
.......................
  from table_name group by name
  order by sum(ApplesEaten ) desc

Ответ №3:

Примерные данные

 IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp
;WITH CTE(Name , ApplesEaten , [Year])
AS
(
SELECT 'Bob'  ,  2      , 2012  UNION ALL
SELECT 'Bob'  ,  5      , 2016  UNION ALL
SELECT 'Elvis',  1      , 2017  UNION ALL
SELECT 'Elvis',  2      , 2012  UNION ALL
SELECT 'Sam'  ,  8      , 2008  UNION ALL
SELECT 'Elvis',  6      , 2004  UNION ALL
SELECT 'Sam'  ,  24     , 2019  UNION ALL
SELECT 'Sarah',  14     , 2015  UNION ALL
SELECT 'Bob'  ,  6      , 2005  UNION ALL
SELECT 'Rachel',  12     , 2010 UNION ALL
SELECT 'Rachel',  10     , 2008 UNION ALL
SELECT 'Bob'  ,   82     , 2006
)
SELECT Name , 
       ApplesEaten , 
       [Year]      
INTO #Temp
FROM CTE

Sql-скрипт, использующий динамический Sql

 DECLARE @Columns nvarchar(max),
        @IsnullColumns nvarchar(max),
        @Sql  nvarchar(max)


SELECT @Columns = STUFF((SELECT DISTINCT ', ' QUOTENAME([Year])   FROM #Temp FOR XML PATH ('')),1,1,'')


 SELECT @IsnullColumns = STUFF((SELECT DISTINCT ', ' 'ISNULL(MAX(' QUOTENAME([Year]) '),''0'') AS apples_'  CAST(([Year]) AS VARCHAR(20))
                            FROM #Temp FOR XML PATH ('')),1,1,'')

SET @Sql ='SELECT TOp 5 Name,' @IsnullColumns '
            FROM
            (
            SELECT *,SUM(ApplesEaten) OVER(PARTITION BY Name ORDER BY [Year]) AS SumApplesEaten FROM #Temp
            ) AS PVT
            PIVOT
            (
            MAX(SumApplesEaten) FOR [Year] IN (' @Columns ')
            ) AS PVT
            GROUP BY Name
            ORDER BY Name'
PRINT @Sql
EXEC (@Sql)