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

Откуда: glubinka
Сообщений: 4256
Есть такая страшная функция в коде для выпечки TOP10 и другие.

Как бы переделать это в inline функцию?

CREATE FUNCTION dbo.fn_make_percent_2dpie_chart( @tbl dbo.PieType readonly )   -- insline function to make a pie chart
RETURNS @result TABLE(RowNumber int, pie_name nvarchar(255), percent_value float, pages bigint, cost numeric(15,5), ffield1 float, elapsed bigint, ifield1 bigint)
WITH SCHEMABINDING
AS
BEGIN
    WITH cte ( pie_name, percent_value, pages, cost, ffield1, elapsed, ifield1, RowNumber )
    AS
    (
        SELECT pie_name, percent_value, pages, cost, ffield1, elapsed, ifield1
             , ROW_NUMBER() OVER (ORDER BY percent_value DESC) AS RowNumber FROM  
        (
           SELECT pie_name, value/(  SUM(NULLIF(value, 0)) over (partition by 1)) AS percent_value
                , COALESCE(pages, 0) AS pages, COALESCE(cost, 0) AS cost
                , COALESCE(ffield1, 0) AS ffield1, COALESCE(elapsed, 0) AS elapsed, COALESCE(ifield1, 0) AS ifield1 from @tbl
        ) t
    ) 
    INSERT INTO @result SELECT DISTINCT RowNumber, pie_name, percent_value, pages, cost, ffield1, elapsed, ifield1 FROM 
    (
        SELECT TOP 10 RowNumber, pie_name, percent_value, pages, cost, ffield1, elapsed, ifield1
            FROM cte ORDER BY RowNumber ASC
        UNION ALL  
        SELECT  11, '$Other$', SUM(COALESCE(percent_value, 0)), SUM(pages), SUM(cost), SUM(ffield1), SUM(elapsed), SUM(ifield1)
            FROM cte where RowNumber > 10
    ) pie
    DELETE FROM @result WHERE RowNumber = 11 AND 10 >= (SELECT COUNT(*) FROM @result )
    RETURN
END;
GO

usage:

declare @val PieType;

insert into @val values ('one',    24, 12, 13, 14, 15, 16), ('two', 2, 3, 4, 5, 6, 7), ('three', 3, 3, 3, 4, 5, 6), ('four', 4, 4, 4, 4, 4, 4), ('five', 5, 5, 5, 5, 5, 5)
                      , ('six',    16, 16, 16, 16, 16, 16), ('seven', 7, 7, 7, 7, 7, 7), ('eight', 8, 8, 8, 8, 8, 8), ('nine', 4, 4, 4, 4, 4, 4), ('ten', 4, 4, 4, 4, 4, 4)
                      , ('eleven', 1,   1,  1,   1, 1,  1), ('twelve', 1, 2, 1, 4, 1, 1)

select * from dbo.fn_make_percent_2dpie_chart( @val ) order by percent_value
25 ноя 15, 00:20    [18468343]     Ответить | Цитировать Сообщить модератору
 Re: пироговая функция  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
IF EXISTS ( SELECT * FROM sys.table_types WHERE name = 'PieType' )
  DROP TYPE dbo.PieType 
GO

CREATE TYPE dbo.PieType AS TABLE ( pie_name nvarchar(255), value float, pages bigint, cost numeric(15,5), ffield1 float, elapsed bigint, ifield1 bigint );
GO
25 ноя 15, 00:24    [18468356]     Ответить | Цитировать Сообщить модератору
 Re: пироговая функция  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Lepsik
Есть такая страшная функция в коде для выпечки TOP10 и другие.

Как бы переделать это в inline функцию?

Там и CTE не нужно.

CREATE FUNCTION dbo.fn_make_percent_2dpie_chart( @tbl dbo.PieType readonly )   -- insline function to make a pie chart
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT 
  CASE WHEN RowNumber>10 THEN 11 ELSE RowNumber END as RowNumber, 
  CASE WHEN RowNumber>10 THEN '$Other' ELSE pie_name END as pie_name,
  SUM(COALESCE(percent_value, 0)) as percent_value,
  SUM(pages) as pages, SUM(cost) as cost, SUM(ffield1) as ffield1, SUM(elapsed) as elapsed,
  SUM(ifield1) as ifield1
FROM (
    SELECT pie_name, percent_value, pages, cost, ffield1, elapsed, ifield1
         , ROW_NUMBER() OVER (ORDER BY percent_value DESC) AS RowNumber FROM  
    (
       SELECT pie_name, value/(  SUM(NULLIF(value, 0)) over (partition by 1)) AS percent_value
            , COALESCE(pages, 0) AS pages, COALESCE(cost, 0) AS cost
            , COALESCE(ffield1, 0) AS ffield1, COALESCE(elapsed, 0) AS elapsed, COALESCE(ifield1, 0) AS ifield1 from @tbl
    ) t
) t 
GROUP BY 
  CASE WHEN RowNumber>10 THEN 11 ELSE RowNumber END,
  CASE WHEN RowNumber>10 THEN '$Other' ELSE pie_name END
GO
25 ноя 15, 09:29    [18468886]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить