Как применить принцип СУХОЙ к операторам SQL, которые сводят месяцы

Мне интересно, как другие справляются с этой ситуацией... и как применять принцип "Не повторяйся" (СУХОЙ) в этой ситуации.

Я постоянно нахожу или пишу операторы CASE в T-SQL, чтобы представлять месяцы в виде столбцов. У меня обычно есть некоторые поля, которые будут включать (1) поле даты и (2) поле значения. Когда я представляю это обратно пользователю через страницу ASPX или Reporting Services, мне нужно иметь последние 14 правых столбцов, чтобы иметь этот шаблон:

[Год], [Ян] [Февраль] [Март], [апреля], [мая], [июня], [июля], [Август] [Сентябрь] [Октябрь], [ноября], [декабрь ],[Всего]

Где year - это год в виде целого числа, а каждое другое поле - это поле значения, суммируемое за этот месяц (за исключением [Total], которое является полем общего значения за год).

Я хотел бы найти один многоразовый способ справиться с этим. Открыто для всех предложений (T-SQL / ANSI SQL)

Источник

5 ответов

Решение

Это не совсем то, что вы ищете, но я много повторял UNPIVOTи, как правило, я хотел бы написать код этого с использованием некоторого стандартизированного наименования и интенсивно использовать CTE:

WITH P AS (
    SELECT Some Data
            ,[234] -- These are stats
            ,[235]
    FROM Whatever
     )
,FINAL_UNPIVOTED AS (
    SELECT Some Data
            ,[STAT]
    FROM P
    UNPIVOT (
        STAT FOR BASE IN ([234], [235]) 
    ) AS unpvt
    WHERE STAT <> 0
)
SELECT Some Data
              ,CONVERT(int, FINAL_UNPIVOTED.[BASE]) AS [BASE]
              ,FINAL_UNPIVOTED.[STAT]
FROM FINAL_UNPIVOTED

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

DECLARE @sql_unpivot AS varchar(MAX)
SELECT @sql_unpivot = COALESCE(@sql_unpivot + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'whatever'

И шаблонизирующий код:

SET @template = '
    WITH P AS (
        SELECT Some Data
                ,{@sql_unpivot}
                  FROM Whatever
         )
    ,FINAL_UNPIVOTED AS (
        SELECT Some Data
                ,[STAT]
        FROM P
        UNPIVOT (
            STAT FOR BASE IN ({@sql_unpivot}) 
        ) AS unpvt
        WHERE STAT <> 0
    )
    SELECT Some Data
                  ,CONVERT(int, FINAL_UNPIVOTED.[BASE]) AS [BASE]
                  ,FINAL_UNPIVOTED.[STAT]
    FROM FINAL_UNPIVOTED
'
SET @sql = REPLACE(@template, '{@sql_unpivot}', @sql_unpivot)

и т.п.

Конечно, можно выполнить этот код динамически или создать и SP, и вы можете временно поменять созданное вами представление или таблицу, просто чтобы получить метаданные для чего-то встроенного.

Смотрите комментарии относительно табличных функций и техники OUTER APPLY.

Источник

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

Источник 
/* I leave year and month separate so you can use "real" Months or Fiscal Months */

CREATE FUNCTION [dbo].[fn_MonthValueColumns] 
(   
    @year int,
    @month int, 
    @measure int 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT @year as [Year],
        CASE WHEN @month = 1 THEN @measure ELSE 0 END AS [Jan], 
        CASE WHEN @month = 2 THEN @measure ELSE 0 END AS [Feb], 
        CASE WHEN @month = 3 THEN @measure ELSE 0 END AS [Mar], 
        CASE WHEN @month = 4 THEN @measure ELSE 0 END AS [Apr], 
        CASE WHEN @month = 5 THEN @measure ELSE 0 END AS [May], 
        CASE WHEN @month = 6 THEN @measure ELSE 0 END AS [Jun], 
        CASE WHEN @month = 7 THEN @measure ELSE 0 END AS [Jul], 
        CASE WHEN @month = 8 THEN @measure ELSE 0 END AS [Aug], 
        CASE WHEN @month = 9 THEN @measure ELSE 0 END AS [Sep], 
        CASE WHEN @month = 10 THEN @measure ELSE 0 END AS [Oct], 
        CASE WHEN @month = 11 THEN @measure ELSE 0 END AS [Nov], 
        CASE WHEN @month = 12 THEN @measure ELSE 0 END AS [Dec], 
        @measure AS [Total]
)

  /* 
   use a group by after your own CROSS APPLY to roll-up SUMs for the last 13 fields. 

   this function and a CROSS APPLY against 100000 records ran in 3 seconds.
   for what I am doing, I can live with that performance.
  */
Источник

Как насчет использования представления?

Если вы всегда выбираете одну и ту же таблицу / группу таблиц, представление может иметь смысл. ПРЕДУПРЕЖДЕНИЕ. Остерегайтесь таких представлений при использовании небольших разделов больших таблиц. Представление может помешать оптимизатору выполнять свою работу.

Источник

Как упомянуто в комментариях yfeldblum, DRY обычно относится к повторно используемому коду, что гораздо проще на языке вашего клиента SQL, чем на SQL. Если вы открыты для этой опции (и по общему признанию, вы не можете), рассмотрите картограф данных как MyBatis. Извлечение в объекты может быть излишним для того, что вам нужно, но возможность создавать фрагменты SQL и повторно использовать их в различных запросах звучит так, как будто вы ищете.

Источник
Другие вопросы по тегам