Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Агрегация вверх по уровню  [new]
НиколайСН
Member

Откуда:
Сообщений: 120
И так.
Имеются таблицы Банки (справочник) (t_bank) и Фактовая таблица (T_FACT):
KEY NAME PARENT_KEY
1 Итого банки null
2 Банк_1 1
3 Банк_2 1
4 Банк_3 1
5 Филиал №1 Банка_1 2
6 Филиал №2 Банка_1 2
7 Филиал №3 Банка_1 2
8 Филиал №1 Банка_1 4
9 Филиал №2 Банка_1 4
10 Филиал №3 Банка_1 4


Визуально имеет представление следующее:
Визуально имеет представление следующее:
Итого банки
--Банк_1
----Филиал №1 Банка_1
----Филиал №2 Банка_1
----Филиал №3 Банка_1
--Банк_2
--Банк_3
----Филиал №1 Банка_3
----Филиал №2 Банка_3
----Филиал №3 Банка_3


Ну и фактовая таблица Куба, которая имеет вид:
DIM_BANK DIM_OTHER_FIELDS VALUE_NUMBER
5 234 11455.67
6 341 84756.3214
7 452 10347
8 563 23424.5345
9 678 224678
10 789 2017364


Необходимо написать скрипт, который будет из нижнего уровня банков агрегировать данные вверх, т.е. чтобы заполнить поля для банков:
  • Банк_3
  • Банк_1
  • Итого банки

    Большая просьба помочь в написании скрипта, функцию merge знаю. Руками писать вверх на каждый уровень - не айс - задротство полное - в детстве пробовал. Да и у меня Банки - на view построены, так надо.
  • 28 июл 14, 18:02    [16367808]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    iiyama
    Member

    Откуда:
    Сообщений: 642
    НиколайСН,

    join + sum() over(partition by PARENT_KEY) ?
    28 июл 14, 18:10    [16367855]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    НиколайСН
    Member

    Откуда:
    Сообщений: 120
    iiyama,
    написал запрос типа:
    select 
       parent_key,
       sum(VALUE_NUMBER) over(partition by PARENT_KEY)
    

    Запрос-то мне и выдал только для Банк_1 и Банк_2. А где Итого Банки... и то - в примере у меня только 3 level, а на самом то деле уровней может быть много - они динамические и их число постоянно меняется!
    29 июл 14, 10:54    [16369846]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    virtuOS
    Member

    Откуда: большая деревня
    Сообщений: 265
    Тут надо сверху идти, а не снизу.

    ;with cte ([key], [Name], [PARENT_KEY], KEY_Top) as
    (
        select [key],
                [Name],
                [PARENT_KEY],
    			[key] as KEY_Top
        from [Банки]
        where [KEY] = 1
    
        UNION ALL
    
        select [Банки].[key],
                [Банки].[Name],
                [Банки].[PARENT_KEY],
    			C.KEY_Top
        from [Банки]
        inner join cte C
            on [Банки].parent = C.id
    
    )
    
    select *
    from cte
    

    А дальше агрегация по KEY_Top
    29 июл 14, 11:16    [16369957]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    virtuOS
    Member

    Откуда: большая деревня
    Сообщений: 265
    то есть соединение по
    on [Банки].[PARENT_KEY] = C.[key]
    
    29 июл 14, 11:18    [16369972]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    НиколайСН
    Member

    Откуда:
    Сообщений: 120
    virtuOS,

    спасибо, постараюсь разобраться в архитектуре запроса и построить.
    29 июл 14, 11:19    [16369979]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7868
    Вообще-то "Итого банки" должен формировать клиент.
    29 июл 14, 11:25    [16370018]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    НиколайСН
    Member

    Откуда:
    Сообщений: 120
    virtuOS, наверное еще level надо задействовать с WHILE level >=0. Сейчас отпишу запрос - скину. За идею громадное спасибо.

    Владислав Колосов,
    Идея хорошая, но у меня BI-система и сказать своему руководству, что пусть свой Кредитный порфель в разрезе Банков считают сами - не айс.
    Имеются кредиты по всем банкам, сколько, кому и когда платить основного долга, процентов и т.п. А начальство должно посмотреть: а сколько же всего мы должны по состоянию на сегодня. Ну или еще в разрезе других измерений куба.
    29 июл 14, 11:44    [16370189]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    НиколайСН
    Member

    Откуда:
    Сообщений: 120
    Владислав Колосов,

    мы и есть клиенты... А система считать налету не умеет Сумму, т.е. умеет, но через одно место, т.е. надо выбрать подчиненных и тогда посчитает. Такой вариант не устраивает нас. По-этому приходится хранить физически данные в таблах.
    29 июл 14, 11:47    [16370220]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    iiyama
    Member

    Откуда:
    Сообщений: 642
    НиколайСН,
    Клиент - это не заказчик, а программа. Т.е элемент сетевой архитектуры, в которой задания или сетевая нагрузка распределены между поставщиками услуг, называемыми серверами, и заказчиками услуг, называемыми клиентами.
    Т.е сервер выдает Вам данные и ваш "клиент" - построитель отчетов, в свою очередь, выводит данные в том виде как удобно для проведения анализа данных. Суммирует по вашим бизнес-правилам, добавляет всякие знаки рубля, выводит правильное для Вас кол-во знаков после запятой с нужным разделителем, выводит строковое отображение даты в нужном Вам формате и тд и тп
    29 июл 14, 16:32    [16372208]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    Konst_One
    Member

    Откуда:
    Сообщений: 11538
    если у вас BI-система, то не проще это делать средствами OLAP?
    29 июл 14, 16:46    [16372305]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    НиколайСН
    Member

    Откуда:
    Сообщений: 120
    Konst_One,
    Дело в том, что не каждая облачная система BI способна считать "налету". Да, БД у нас MS-SQL, а вот BI - нет!
    По нашей специфике, наша BI-система не умеет проводить агрегацию по измерению уровнями вверх, пока в Отчетности в данном срезе не будут отмечены более нижние уровни.

    А запрос я все-таки написал!
    30 июл 14, 09:31    [16374886]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    НиколайСН
    Member

    Откуда:
    Сообщений: 120
    iiyama,
    Имеется только "тонкий клиент" и Сервер, с которым он работает, на котором стоит и развернут сам BI.
    Да, у меня стоит "толстый".
    30 июл 14, 09:34    [16374897]     Ответить | Цитировать Сообщить модератору
     Re: Агрегация вверх по уровню  [new]
    НиколайСН
    Member

    Откуда:
    Сообщений: 120
    А вот и сам запрос!
    Аналогичный только, ибо переписывать на примере не хоца!
    -- Витрина 
    ---------------------------------------
    ALTER PROCEDURE [dbo].[]
    AS
    BEGIN
    
    --ПЕРЕНОС ДАННЫХ ИЗ ОСНОВНОЙ ТАБЛИЦЫ--
    ----------------------------------------------------------
    declare @IND int;
    	-- определяем КЛЮЧ показателя из справочника показателей Кредитного портфеля. 
    	SELECT @IND = [KEY]
    	FROM T289030
    	WHERE LTRIM(RTRIM(EXTERNAL_KEY)) = 'id_lp1';
    
    merge into C_LP_STRUCTURE_LP_CURRENCY dst
    using
    (  select x.DL
    		, x.DT
    		, x.DIM_BE
    		, x.DIM_CURRENCY
    		, sum(isnull(x.FACT_VALUE_NUMBER, 0)) as FACT_VALUE_NUMBER
    from  C_CREDIT_PORTFOLIO x WITH (NOLOCK) 
    where x.DIM_INDICATORS = 1
    group by x.DL
    		, x.DT
    		, x.DIM_BE
    		, x.DIM_CURRENCY
    	) src
    on  (
        dst.[DL]                = src.[DL] AND   
    	dst.[DT]                = src.[DT] AND    
    	dst.[DIM_BE]            = src.[DIM_BE] AND    
    	dst.[DIM_CURRENCY]		= src.[DIM_CURRENCY]
    	)	
    
    when matched then update set
    	dst.[FACT_VALUE_NUMBER]     = src.[FACT_VALUE_NUMBER]
    
    when not matched then insert
     (
    	  DL
    	, DT
    	, DIM_BE
    	, DIM_CURRENCY
    	, FACT_VALUE_NUMBER
    	)
    values
        (
    	  src.DL
    	, src.DT
    	, src.DIM_BE
    	, src.DIM_CURRENCY
    	, src.FACT_VALUE_NUMBER
    	);
    
    
    
    ---------------------------------------------------
    ------Агрегация по Бизнес-единицам (на view)-------
    ---------------------------------------------------
    declare @Level int = 1;
    
    With Rec ([KEY], NAME, [PARENT_KEY], ORD, level)
    As (  
        SELECT [KEY], NAME, [PARENT_KEY], ORD, 0 as level 
    	FROM T366 
    	where [PARENT_KEY] is null and getdate() between indate and outdate -1  
          UNION ALL  
    	SELECT t366.[KEY], t366.[NAME], t366.[PARENT_KEY], t366.[ORD], Rec.level + 1 as level   
    	FROM Rec, t366 
    	WHERE Rec.[KEY] = t366.[PARENT_KEY]  
          and getdate() between T366.indate and t366.outdate -1  
       )
    SELECT @Level = MAX(level-1) FROM Rec;
    
    
    WHILE @Level >= 0 
    BEGIN
    
    With Rec ([KEY], NAME, [PARENT_KEY], ORD, [level])
    As (  
    SELECT [KEY], NAME, [PARENT_KEY], ORD, 0 as [level] FROM t366  WITH (NOLOCK) where [PARENT_KEY] is null and getdate() between indate and outdate -1  
    UNION ALL  
    SELECT t366.[KEY], t366.NAME, t366.[PARENT_KEY], t366.ORD, Rec.level + 1 as level   
          FROM Rec, t366 
          WHERE Rec.[KEY] = t366.[PARENT_KEY]  
            and getdate() between t366.indate and t366.outdate -1  
    )
    -- Бизнес-единицы первого уровня 
    , BU_PARENTS as (SELECT [KEY] as PARENT_BU FROM Rec WHERE level = @Level) /* уровень */
    
    MERGE INTO C_LP_STRUCTURE_LP_CURRENCY
    USING  
    (
     SELECT [DL]
           ,[DT]
           ,b.[PARENT_KEY] as [DIM_BE]-- Внимание!! Смотри GROUP BY
           ,[DIM_CURRENCY]
           ,sum(isnull(FACT_VALUE_NUMBER, 0)) as [FACT_VALUE_NUMBER]
      FROM [C_LP_STRUCTURE_LP_CURRENCY] T 
      JOIN t366 b  WITH (NOLOCK)  ON ( b.[KEY] = T.[DIM_BE] AND T.[DT] BETWEEN b.[INDATE] and b.[OUTDATE] - 1)  
      JOIN BU_PARENTS bb ON (b.[PARENT_KEY] = bb.[PARENT_BU])  
     group by 
    		  [DL]
    		, [DT]
    /*-------------Внимание!!!!-----------------------------------------------------------------*/
            , b.[PARENT_KEY]/*Если поставить просто "[KEY]", то не сагрегируются вверх по уровню*/
    /*------------------------------------------------------------------------------------------*/
            , [DIM_CURRENCY]
    ) AS T 
         ON  T.[DL]				= C_LP_STRUCTURE_LP_CURRENCY.[DL] 
    	 and T.[DT]			    = C_LP_STRUCTURE_LP_CURRENCY.[DT] 
    	 and T.[DIM_BE]			= C_LP_STRUCTURE_LP_CURRENCY.[DIM_BE] 
    	 and T.[DIM_CURRENCY]   = C_LP_STRUCTURE_LP_CURRENCY.[DIM_CURRENCY]
    	
     WHEN MATCHED THEN  
    	UPDATE SET	[FACT_VALUE_NUMBER]    = T.[FACT_VALUE_NUMBER]
    
     WHEN NOT MATCHED THEN INSERT  
    				(   
    				    [DL] 
    				  , [DT] 
    				  , [DIM_BE] 
    				  , [DIM_CURRENCY] 
    				  , [FACT_VALUE_NUMBER]
    				)  
    			VALUES 
    				(
    				  T.[DL] 
    				, T.[DT] 
    				, T.[DIM_BE] 
    				, T.[DIM_CURRENCY] 
    				, T.[FACT_VALUE_NUMBER]
    				 );
    
    	SET @Level = @Level - 1
    
    END; /*Закрываем WHILE*/
    END /*Закрываем BEGIN*/
    
    30 июл 14, 09:41    [16374915]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить