Как применить принцип DRY к SQL-операторам, которые вращаются по месяцам

Мне интересно, как другие справляются с этой ситуацией ... и как применить к этой ситуации принцип «Не повторяйся» (DRY).

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

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

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

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


sql sql-server dry tsql pivot
person BuddyJoe    schedule 20.01.2009    source источник
comment
DRY означает «Не повторяйся, не повторяй».   -  person Samuel    schedule 21.01.2009
comment
РЖУ НЕ МОГУ. Хорошо поймал. Это была опечатка.   -  person BuddyJoe    schedule 21.01.2009
comment
У меня возникает соблазн попробовать тип данных XML в Microsoft SQL 2005. Таким образом, я смогу использовать скалярную функцию для выполнения части работы.   -  person BuddyJoe    schedule 21.01.2009
comment
Не повторяйся означает создание многоразовых абстракций. SQL, как и ассемблер, не лучший язык для абстракции.   -  person yfeldblum    schedule 21.01.2009
comment
Я согласен. Но я думаю, что это также означает быть продуктивным ... и я устал писать эти повороты снова и снова. Я думаю, что Кейд Ру направил меня на правильный путь (см. Комментарии к его ответу). Я максимально абстрагировался в T-SQL, и это обычно приводит вас к функциям.   -  person BuddyJoe    schedule 21.01.2009

Ответы (4)


arrow_upward
3
arrow_downward

Это не совсем то, что вы ищете, но я делал много повторяющихся 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.

person Cade Roux    schedule 20.01.2009
comment
+1 - Спасибо за код. Я собирался завтра взглянуть на динамический SQL, если ни у кого не было лучшего предложения. Мне все еще нужно разобраться в столбце [Всего] ... не уверен, что Pivot сделает это за меня. - person BuddyJoe; 21.01.2009
comment
Я думаю, что могу попробовать табличную функцию, которая принимает на входе дату и время и возвращает 14 столбцов. Затем ПРИСОЕДИНЯЙТЕСЬ к этим данным. Мне нужно будет проверить влияние скорости. - person BuddyJoe; 21.01.2009
comment
Табличные функции, возвращающие одну строку и затем использующие OUTER APPLY, могут работать, но могут быть медленными для больших (то есть от нескольких тысяч до миллионов строк) наборов данных, но все равно работают достаточно хорошо, чтобы я использовал их - если мне нужно. - person Cade Roux; 21.01.2009
comment
Сладкий! Мне никогда раньше не приходилось использовать синтаксис APPLY. Но это прекрасная возможность. Я, вероятно, буду использовать это с этого момента всякий раз, когда мне нужно добавить стандартные столбцы к выходным данным таблицы. У меня около 14 CASE-заявлений на 100000 записей. Можете ли вы добавить к своему ответу фразу СМОТРЕТЬ КОММЕНТАРИИ? - person BuddyJoe; 21.01.2009
comment
Я опубликовал свою табличную функцию в качестве еще одного ответа в этой теме. - person BuddyJoe; 21.01.2009

arrow_upward
1
arrow_downward

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

person gkrogers    schedule 20.01.2009
comment
упрощает группировку count () по месяцу - person Isaac Dealey; 21.01.2009
comment
С строкой на каждый месяц? или столбик? Мне не хватает того, что это делает ... У меня уже есть таблица месяцев. - person BuddyJoe; 21.01.2009

arrow_upward
0
arrow_downward

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

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

person lexu    schedule 21.01.2009
comment
Я использую Views. Проблема в том, что всякий раз, когда мне нужно это сделать, я обычно выбираю несколько представлений, а иногда и сохраненную процедуру. Пытаемся сократить беспорядок в базе данных. Думаю, сегодня утром я напишу динамический SQL и опубликую его. Посмотрите, может ли кто-нибудь еще сотрудничать и улучшить его. - person BuddyJoe; 21.01.2009

arrow_upward
0
arrow_downward

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

person Chadwick    schedule 07.07.2010