Início / dba / Perguntas / 23352
Accepted
spaghetticowboy
Asked: 2012-08-30 07:16:20 +0800 CST

selecionar várias instâncias de um registro com base na vida útil do registro ao longo dos anos

  • 772

Eu tenho uma tabela de itens que têm uma data de origem e uma vida útil. Desejo criar um procedimento que selecione todos os itens que precisarão ser substituídos em uma duração específica. Eu sinto que há uma maneira matemática de fazer isso na cláusula where de uma única seleção. A única maneira de pensar em fazer isso é iterar ao longo dos anos e anexar os itens que seriam substituídos ... isso parece super lento e ineficiente. Como devo abordar esta questão?

Detalhes por solicitações de comentários:

Mecanismo de banco de dados: SQL Server 2005

Tabela de origem:

CREATE TABLE Items
(
    ID int,
    ItemName varchar(255),
    InstallYear int,
    UsefullLife int
);

Formato de procedimento armazenado desejado:

GetReplacementsWithinYearRange(startyear int, endyear int)

Saída desejada:

ItemID - ItemName - ReplaceYear

Mais detalhes:

Registros de amostra:

1  item1  2010  5    
2  item2  2011  6

Resultados desejados para o intervalo entre 2010 e 2030:

1  item1  2010
1  item1  2015
1  item1  2020
1  item1  2025
1  item1  2030
2  item2  2011
2  item2  2017
2  item2  2023
2  item2  2029
sql-server sql-server-2005

3 respostas

  1. Estou simplificando demais aqui?

    CREATE TABLE Items (ItemID int, ItemName nvarchar(255), InstallYear int, Life int)

INSERT INTO Items VALUES (1,'test1',2010,2)
INSERT INTO Items VALUES (2,'test2',2011,2)
INSERT INTO Items VALUES (3,'test3',2012,5)
INSERT INTO Items VALUES (4,'test4',2013,3)

SELECT * FROM Items
GO

CREATE PROCEDURE GetItemLifetimeInfo
(
    @StartYear int,
    @EndYear int
)
AS
BEGIN
    SELECT *, InstallYear + Life AS ReplaceYear 
    FROM Items 
    WHERE InstallYear <= @EndYear AND (InstallYear + Life) >= @StartYear;
END
GO

EXEC GetItemLifetimeInfo 2011, 2022;

    Retorna:

    ItemID  ItemName    InstallYear Life    ReplaceYear
1       test1       2010        2       2012
2       test2       2011        2       2013
3       test3       2012        5       2017
4       test4       2013        3       2016

    Isso funciona:

    CREATE PROCEDURE GetItemLifetimeInfo
(
    @StartYear int,
    @EndYear int
)
AS
BEGIN
    WITH ReplaceYears(ItemID, [Year])
    AS (
        SELECT ItemID, Life
        FROM Items
        UNION ALL
        SELECT Items.ItemID, Items.Life + ReplaceYears.Year
        FROM Items
            INNER JOIN ReplaceYears ON Items.ItemID = ReplaceYears.ItemID
        WHERE ReplaceYears.Year <= (@EndYear - @StartYear)
    )
    SELECT Items.ItemID, InstallYear + ReplaceYears.Year AS ReplaceYear 
    FROM Items
        INNER JOIN ReplaceYears ON Items.ItemID = ReplaceYears.ItemID 
    WHERE InstallYear + ReplaceYears.Year <= @EndYear 
        AND (InstallYear + ReplaceYears.Year) >= @StartYear
    ORDER BY 1,2
END

EXEC GetItemLifetimeInfo 2011,2022;

ItemID  ReplaceYear
1   2012
1   2014
1   2016
1   2018
1   2020
1   2022
2   2013
2   2015
2   2017
2   2019
2   2021
3   2017
3   2022
4   2016
4   2019
4   2022
    • 5 
  2. CREATE PROCEDURE getreplacementswithinyearrang(@startyear int, @endyear int)
AS
BEGIN
    SELECT  itemID,
            itemName,
            InstallYear + life as replaceDtae
    FROM    dbo.Items

    WHERE @startyear <= (InstallYear+life)
    AND   @endyear > (InstallYear+Life)
END
    • 3
  3. Best Answer

    Isso não será fantástico dependendo dos índices na Itemstabela, mas deve ser muito mais eficiente do que o loop que você estava pensando. Em quase todos os casos, uma consulta baseada em conjunto terá um desempenho muito melhor do que qualquer tipo de iteração - há algumas exceções, mas você só deve terminar com um loop se for realmente necessário ou provar ter um desempenho melhor do que uma abordagem definida, nunca como primeira reação. NA MINHA HUMILDE OPINIÃO.

    Este procedimento tira proveito de uma exibição de catálogo para criar um conjunto de números dinamicamente que representa o maior número de substituições possíveis, dado o ano de início/fim de entrada, se o menor tempo de vida for um ano. Você pode reduzir isso se o menor período for de 2 anos, etc., mas isso não mudará realmente o perfil de desempenho. Em seguida, ele usa esses números para encontrar anos de substituição, com base no módulo, como sua abordagem faria - mas usa um conjunto. Provavelmente há uma maneira que eu poderia ter conseguido, UNIONmas JOINparecia mais fácil chamar essa parte da consulta separadamente.

    CREATE PROCEDURE dbo.GetReplacementsWithinYearRange
    @startyear int, 
    @endyear   int
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH n(n) AS 
    (
      SELECT TOP (@endyear - @startyear + 1) ROW_NUMBER() OVER 
      (ORDER BY [object_id]) FROM sys.all_columns
    )
    SELECT ID, ItemName, ReplaceYear FROM
    (
     SELECT i.ID, i.ItemName, ReplaceYear = n.n + i.InstallYear
     FROM n INNER JOIN dbo.Items AS i
     ON (n.n - 1) % i.UsefullLife = i.UsefullLife - 1
     AND n.n + i.InstallYear > @startyear
     AND n.n + i.InstallYear <= @endyear
    ) AS x
    UNION
      SELECT ID, ItemName, InstallYear
       FROM dbo.Items
       WHERE InstallYear BETWEEN @startyear AND @endyear
    ORDER BY ID, ReplaceYear;
END
GO

    Aqui está um sqlfiddle que demonstra: http://sqlfiddle.com/#!3/09824f/2

    • 3

relate perguntas