[MSSQL]: Настраиваемая группировка.

Сдрасте.

Тут меня ОП достал постоянными изменениями в ПО (ОП — оффисный планктон; ПО — программулина офигенная :-)). Надо им суммы считать, группируя по разным полям и значениям, и всё им не нравится как там условия выставляются: то тут то там им надо "немного подругому". Т.к. используются одни и те же поля, я решил как-то автоматизировать это дело. Нагуглить ничего похожего не удалось, так что занялся велосипедостроением. Ниже то, что я сейчас придумал: нечто вроде реляционного подхода, таблица с условиями группировки. Или думаю тупо генератор кода написать, который генерирует функцию, возвращающую код группы по переданным в параметрах значениям полей, и процедуру проверки целостности. Вопщим, если что хорошее подскажете, буду благодарен.

Тестовые данные -- таблица учёта терлкопов, в ней содержится имя терлкопа, идентификатор его места обитания и сколько у него фиртсапов. Терлкоп и фиртсап — продукт моего случайного бряцания по клаве, если что, все совпадения случайны :-). Надо считать суммы фиртсапов группируя терлкопов по имени и месту обитания, а так же общую сумму.

dbo.Terlkops

CREATE TABLE [dbo].[Terlkops] (
     [ID_Terlkop]  [int] NOT NULL
    ,[NameMy]      [varchar](32) NOT NULL
    ,[FirtsapCnt]  [int] NOT NULL
    ,[ID_Habitat]  [int] NOT NULL
    ,CONSTRAINT [PK_Terlkops] 
        PRIMARY KEY CLUSTERED
        ([ID_Terlkop] ASC) 
        ON [PRIMARY] 
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Terlkops] ([ID_Terlkop],[NameMy],[FirtsapCnt],[ID_Habitat]) VALUES (1, 'AAA', 55,0)
INSERT INTO [dbo].[Terlkops] ([ID_Terlkop],[NameMy],[FirtsapCnt],[ID_Habitat]) VALUES (2, 'BBB', 12,0)
INSERT INTO [dbo].[Terlkops] ([ID_Terlkop],[NameMy],[FirtsapCnt],[ID_Habitat]) VALUES (3, 'CCC', 1,1)
INSERT INTO [dbo].[Terlkops] ([ID_Terlkop],[NameMy],[FirtsapCnt],[ID_Habitat]) VALUES (4, 'AAB', 13,1)
INSERT INTO [dbo].[Terlkops] ([ID_Terlkop],[NameMy],[FirtsapCnt],[ID_Habitat]) VALUES (5, 'BBA', 4,0)
INSERT INTO [dbo].[Terlkops] ([ID_Terlkop],[NameMy],[FirtsapCnt],[ID_Habitat]) VALUES (6, 'AAC', 21,2)
INSERT INTO [dbo].[Terlkops] ([ID_Terlkop],[NameMy],[FirtsapCnt],[ID_Habitat]) VALUES (7, 'CCA', 9,2)
INSERT INTO [dbo].[Terlkops] ([ID_Terlkop],[NameMy],[FirtsapCnt],[ID_Habitat]) VALUES (8, 'CCB', 19,0)
INSERT INTO [dbo].[Terlkops] ([ID_Terlkop],[NameMy],[FirtsapCnt],[ID_Habitat]) VALUES (9, 'BBC', 3,1) 
GO

Вот такой велосипед у меня получился. Таблица с условиями по которым группировать

dbo.TerlkopGroups

CREATE TABLE [dbo].[TerlkopGroups] 
    ([CODE_TerlkopGroup]  [char](1) NOT NULL      /*Код группы*/
    ,[IsEnabled]          [bit] NOT NULL          /*Вкл / вкл*/
    ,[NamePttrn]          [varchar](128) NOT NULL /*Условие по имени: шаблон LIKE */
    ,[ID_Habitat_Min]     [int] NOT NULL          /*Условие по коду места обитания: min */
    ,[ID_Habitat_Max]     [int] NOT NULL          /*Условие по коду места обитания: max */
    ) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [TerlkopGroups_CODE_ix] 
    ON [dbo].[TerlkopGroups] 
    ([CODE_TerlkopGroup] ASC
    ) ON [PRIMARY]
GO

INSERT INTO [dbo].[TerlkopGroups] ([CODE_TerlkopGroup],[IsEnabled],[NamePttrn],[ID_Habitat_Min],[ID_Habitat_Max]) VALUES (' ',1,'%',-2147483648,2147483647)
INSERT INTO [dbo].[TerlkopGroups] ([CODE_TerlkopGroup],[IsEnabled],[NamePttrn],[ID_Habitat_Min],[ID_Habitat_Max]) VALUES ('K',1,'A%',0,0)
INSERT INTO [dbo].[TerlkopGroups] ([CODE_TerlkopGroup],[IsEnabled],[NamePttrn],[ID_Habitat_Min],[ID_Habitat_Max]) VALUES ('L',1,'B%',0,0)
INSERT INTO [dbo].[TerlkopGroups] ([CODE_TerlkopGroup],[IsEnabled],[NamePttrn],[ID_Habitat_Min],[ID_Habitat_Max]) VALUES ('M',1,'C%',0,0)
INSERT INTO [dbo].[TerlkopGroups] ([CODE_TerlkopGroup],[IsEnabled],[NamePttrn],[ID_Habitat_Min],[ID_Habitat_Max]) VALUES ('N',1,'%A',0,0)
INSERT INTO [dbo].[TerlkopGroups] ([CODE_TerlkopGroup],[IsEnabled],[NamePttrn],[ID_Habitat_Min],[ID_Habitat_Max]) VALUES ('N',1,'%A',2,2)
INSERT INTO [dbo].[TerlkopGroups] ([CODE_TerlkopGroup],[IsEnabled],[NamePttrn],[ID_Habitat_Min],[ID_Habitat_Max]) VALUES ('K',1,'B%',1,1)
GO

Расписывать как это всё использовать я не буду, уверен всё понятно по SQL-коду выборки с группировкой.

SELECT
     tg.CODE_TerlkopGroup
    ,FirtsapCntTot = SUM(t.FirtsapCnt)
FROM    
    dbo.Terlkops AS t
INNER JOIN
    dbo.TerlkopGroups AS tg
ON    t.NameMy LIKE tg.NamePttrn ESCAPE '\'
AND    tg.ID_Habitat_Min <= t.ID_Habitat
AND    t.ID_Habitat <= tg.ID_Habitat_Max
WHERE    
    tg.IsEnabled = 1
GROUP BY    
    tg.CODE_TerlkopGroup

В конечном итоге таблица вышла аж на 151 строку (что немного пугает т.к. каждый новый столбец может удвоить это значение). Чтоб облегчить себе жизнь с этим жизнь я написал несколько хранимок для администрирования:

Хранимки

-- Все значения одного условия, по одной группе. 
CREATE PROCEDURE [dbo].[TerlkopGroupsTermValues_p]
     @CODE_TerlkopGroup CHAR(1)
    ,@FieldToGroupName  VARCHAR(128)
AS
SELECT DISTINCT
     NamePttrn 
        = CASE WHEN @FieldToGroupName = 'Name' 
            THEN tg.NamePttrn
            ELSE NULL
        END
    ,ID_Habitat_Min
        = CASE WHEN @FieldToGroupName = 'ID_Habitat' 
            THEN tg.ID_Habitat_Min
            ELSE NULL
        END
    ,ID_Habitat_Max
        = CASE WHEN @FieldToGroupName = 'ID_Habitat' 
            THEN tg.ID_Habitat_Max
            ELSE NULL
        END
FROM    dbo.TerlkopGroups AS tg
WHERE    CODE_TerlkopGroup = @CODE_TerlkopGroup
GO

-- Все условия одной группы, с заданым значением одного из условий.
CREATE PROCEDURE [dbo].[TerlkopGroupsByTermValue_p]
     @CODE_TerlkopGroup CHAR(1)
    ,@FieldToGroupName  VARCHAR(128)
    ,@FieldToGroupValue0 SQL_VARIANT
    ,@FieldToGroupValue1 SQL_VARIANT 
AS
SELECT
     NamePttrn 
    ,ID_Habitat_Min
    ,ID_Habitat_Max
FROM    dbo.TerlkopGroups AS tg
WHERE    
    CODE_TerlkopGroup = @CODE_TerlkopGroup
    AND    (    @FieldToGroupName <> 'Name' 
         OR tg.NamePttrn = @FieldToGroupValue0)
     AND    (    @FieldToGroupName <> 'ID_Habitat' 
         OR    tg.ID_Habitat_Min = @FieldToGroupValue0
         AND    tg.ID_Habitat_Max = @FieldToGroupValue1)
GO

-- Добавить условие.
CREATE PROCEDURE [dbo].[TerlkopGroupsTermValueAdd_p]
     @CODE_TerlkopGroup CHAR(1)
    ,@FieldToGroupName  VARCHAR(128)
    ,@ValueNew0         SQL_VARIANT
    ,@ValueNew1         SQL_VARIANT
AS
BEGIN

DECLARE    
     @NamePttrn AS VARCHAR(128)
    ,@ID_Habitat_Min AS INT
    ,@ID_Habitat_Max AS INT

IF    @FieldToGroupName = 'Name'
    SET @NamePttrn = CAST(@ValueNew0 AS VARCHAR(128))

IF    @FieldToGroupName = 'ID_Habitat'
    SELECT
         @ID_Habitat_Min = CAST(@ValueNew0 AS INT)
        ,@ID_Habitat_Max = CAST(@ValueNew1 AS INT)

INSERT INTO dbo.TerlkopGroups
    (CODE_TerlkopGroup 
    ,IsEnabled
    ,NamePttrn
    ,ID_Habitat_Min
    ,ID_Habitat_Max)
SELECT DISTINCT
     CODE_TerlkopGroup
    ,IsEnabled = 0
    ,NamePttrn 
        = CASE WHEN @FieldToGroupName = 'Name' 
            THEN @NamePttrn
            ELSE tg.NamePttrn
        END
    ,ID_Habitat_Min
        = CASE WHEN @FieldToGroupName = 'ID_Habitat' 
            THEN @ID_Habitat_Min
            ELSE tg.ID_Habitat_Min
        END
    ,ID_Habitat_Max
        = CASE WHEN @FieldToGroupName = 'ID_Habitat' 
            THEN @ID_Habitat_Min
            ELSE tg.ID_Habitat_Max
        END
FROM    dbo.TerlkopGroups AS tg
WHERE    CODE_TerlkopGroup = @CODE_TerlkopGroup

END
GO

-- Удалить условие
CREATE PROCEDURE [dbo].[TerlkopGroupsTermValueRem_p]
     @CODE_TerlkopGroup CHAR(1)
    ,@FieldToGroupName  VARCHAR(128)
    ,@Value0         SQL_VARIANT
    ,@Value1         SQL_VARIANT
AS
BEGIN

DECLARE    
     @NamePttrn AS VARCHAR(128)
    ,@ID_Habitat_Min AS INT
    ,@ID_Habitat_Max AS INT

IF    @FieldToGroupName = 'Name'
    SET @NamePttrn = CAST(@Value0 AS VARCHAR(128))

IF    @FieldToGroupName = 'ID_Habitat'
    SELECT
         @ID_Habitat_Min = CAST(@Value0 AS INT)
        ,@ID_Habitat_Max = CAST(@Value1 AS INT)

DELETE FROM    dbo.TerlkopGroups
WHERE    
    CODE_TerlkopGroup = @CODE_TerlkopGroup
    AND    (    @FieldToGroupName <> 'Name'
        OR    NamePttrn = @NamePttrn)
    AND    (    @FieldToGroupName <> 'ID_Habitat'
        OR    ID_Habitat_Min = @ID_Habitat_Min
        AND    ID_Habitat_Max = @ID_Habitat_Max)

END
GO

И какая-никакая проверка целлостности:

SELECT
     tg.CODE_TerlkopGroup
    ,t.ID_Terlkop
FROM    
    dbo.Terlkops AS t
INNER JOIN
    dbo.TerlkopGroups AS tg
ON    t.NameMy LIKE tg.NamePttrn ESCAPE '\'
AND    tg.ID_Habitat_Min <= t.ID_Habitat
AND    t.ID_Habitat <= tg.ID_Habitat_Max
GROUP BY    
     tg.CODE_TerlkopGroup
    ,t.ID_Terlkop
HAVING
    COUNT(*) > 1

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

От:	ZAMUNDA	для жалоб и предложений
Дата:	06.12.10 02:40
Оценка: