none
Criar tabela única com dados de múltiplas queries RRS feed

  • Pergunta

  • Tenha um conjunto de queries que geram dados extraídos para os dias da semana das quatro semanas anteriores da data corrente.
    Estas queries calculam uma média de um determinado valor para as quatro segundas-feiras anteriores, para as quatro terças-feiras anteriores e assim sucessivamente.
    No momento estou criando 5 tabelas, com o resultado dos meus conjuntos de queries que geram os dados para cada dia da semana correspondente.
    Esta é a minha montagem (a criação das tabelas está em negrito):

    declare @seg datetime=dateadd(week, datediff(week, 0, dateadd(day, 6 -datepart(day, getdate()), getdate())), 0)
    declare @ter datetime=dateadd(week, datediff(week, 0, dateadd(day, 6 -datepart(day, getdate()), getdate())), 1)
    drop table Trend_Data_Seg
    SELECT 
    cpumem.SERVER,
    CAST(AVG(cpumem.CPU_AVG_PERC) as Numeric(10,2)) as CPU_AVG
    INTO Trend_Data_Seg
    FROM
    (SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @seg - 21
    and dateadd(second, -1, (@ter - 21))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @seg - 14
    and dateadd(second, -1, (@ter - 14))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @seg - 7
    and dateadd(second, -1, (@ter - 7))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @seg
    and dateadd(second, -1, @ter)
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source) cpumem
    group by cpumem.SERVER
    order by cpumem.SERVER

    GO

    declare @ter datetime=dateadd(week, datediff(week, 0, dateadd(day, 6 -datepart(day, getdate()), getdate())), 1)
    declare @qua datetime=dateadd(week, datediff(week, 0, dateadd(day, 6 -datepart(day, getdate()), getdate())), 2)
    drop table Trend_Data_Ter
    SELECT 
    cpumem.SERVER,
    CAST(AVG(cpumem.CPU_AVG_PERC) as Numeric(10,2)) as CPU_AVG
    INTO Trend_Data_Ter
    FROM
    (SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @ter - 21
    and dateadd(second, -1, (@qua - 21))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @ter - 14
    and dateadd(second, -1, (@qua - 14))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @ter - 7
    and dateadd(second, -1, (@qua - 7))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @ter
    and dateadd(second, -1, @qua)
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source) cpumem
    group by cpumem.SERVER
    order by cpumem.SERVER

    GO

    declare @qua datetime=dateadd(week, datediff(week, 0, dateadd(day, 6 -datepart(day, getdate()), getdate())), 2)
    declare @qui datetime=dateadd(week, datediff(week, 0, dateadd(day, 6 -datepart(day, getdate()), getdate())), 3)
    drop table Trend_Data_Qua
    SELECT 
    cpumem.SERVER,
    CAST(AVG(cpumem.CPU_AVG_PERC) as Numeric(10,2)) as CPU_AVG
    INTO Trend_Data_Qua
    FROM
    (SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @qua - 21
    and dateadd(second, -1, (@qui - 21))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @qua - 14
    and dateadd(second, -1, (@qui - 14))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @qua - 7
    and dateadd(second, -1, (@qui - 7))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @qua
    and dateadd(second, -1, @qui)
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source) cpumem
    group by cpumem.SERVER
    order by cpumem.SERVER

    GO

    declare @qui datetime=dateadd(week, datediff(week, 0, dateadd(day, 6 -datepart(day, getdate()), getdate())), 3)
    declare @sex datetime=dateadd(week, datediff(week, 0, dateadd(day, 6 -datepart(day, getdate()), getdate())), 4)
    drop table Trend_Data_Qui
    SELECT 
    cpumem.SERVER,
    CAST(AVG(cpumem.CPU_AVG_PERC) as Numeric(10,2)) as CPU_AVG
    INTO Trend_Data_Qui
    FROM
    (SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @qui - 21
    and dateadd(second, -1, (@sex - 21))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @qui - 14
    and dateadd(second, -1, (@sex - 14))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @qui - 7
    and dateadd(second, -1, (@sex - 7))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @qui
    and dateadd(second, -1, @sex)
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source) cpumem
    group by cpumem.SERVER
    order by cpumem.SERVER

    GO

    declare @sex datetime=dateadd(week, datediff(week, 0, dateadd(day, 6 -datepart(day, getdate()), getdate())), 4)
    declare @sab datetime=dateadd(week, datediff(week, 0, dateadd(day, 6 -datepart(day, getdate()), getdate())), 5)
    drop table Trend_Data_Sex
    SELECT 
    cpumem.SERVER,
    CAST(AVG(cpumem.CPU_AVG_PERC) as Numeric(10,2)) as CPU_AVG
    INTO Trend_Data_Sex
    FROM
    (SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @sex - 21
    and dateadd(second, -1, (@sab - 21))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @sex - 14
    and dateadd(second, -1, (@sab - 14))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @sex - 7
    and dateadd(second, -1, (@sab - 7))
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source
    UNION
    SELECT
    a.source as SERVER,
    CAST(AVG(a.samplevalue) as Numeric(10,2)) as CPU_AVG_PERC
    FROM
    (Select
    Q.source,
    Q.target,
    s.sampletime,
    s.samplevalue
    from S_QOS_DATA as Q inner join VN_QOS_DATA_0014 s
    on Q.table_id = s.table_id
    where s.sampletime between @sex
    and dateadd(second, -1, @sab)
    AND target in ('User' , 'System' , 'Idle' , 'Wait')
    group by Q.source, Q.target, s.sampletime, s.samplevalue
    ) a
    group by a.source) cpumem
    group by cpumem.SERVER
    order by cpumem.SERVER

    Como podem ver, crio 5 tabelas correspondentes aos dias da semana dos quais as médias são calculadas.
    Gostaria de saber se eu conseguiria criar uma única tabela de saída com a coluna do servidor e cinco colunas correspondentes as médias de cada dia da semana.


    Aloysio Coutinho

    sexta-feira, 10 de junho de 2016 21:14

Respostas

  • Cara, essa foi a solução perfeita na verdade.
    Depois que testei a ideia do Marcelo em uma base com dados (o teste anterior foi em uma base semi vazia), percebi que a solução dele gerava múltiplas linhas por servidor (uma para cada dia da semana).
    Testando com a sua solução, ficou exatamente como eu preciso, apesar das tabelas intermediárias serem mantidas, mas acho que isso não será um problema.


    Aloysio Coutinho


    • Editado Aloysio domingo, 12 de junho de 2016 02:03
    • Marcado como Resposta Aloysio quarta-feira, 13 de julho de 2016 13:26
    domingo, 12 de junho de 2016 00:01
  • Deleted
    • Marcado como Resposta Aloysio domingo, 12 de junho de 2016 00:01
    sábado, 11 de junho de 2016 13:10

Todas as Respostas

  • Amigo, Sem mexer muito no seu código tente isso.

    Crie uma tabela

    CREATE TABLE TodosOsDias(
    	Servidor varchar(50),
    	Seg numeric(10,2),
    	Ter numeric(10,2),
    	Qua numeric(10,2),
    	Qui numeric(10,2),
    	Sex numeric(10,2)
    )
    

    Em seguida no seu código modifique a parte que você cria as tabelas.

    INSERT TodosOsDias
    SELECT 
    cpumem.SERVER,
    CAST(AVG(cpumem.CPU_AVG_PERC) as Numeric(10,2)) as CPU_AVG
    ,null
    ,null
    ,null
    ,null
    --INTO Trend_Data_Seg
    FROM
    ...
    INSERT TodosOsDias
    SELECT 
    cpumem.SERVER,
    ,null
    ,CAST(AVG(cpumem.CPU_AVG_PERC) as Numeric(10,2)) as CPU_AVG
    ,null
    ,null
    ,null
    --INTO Trend_Data_Ter
    FROM
    ...
    INSERT TodosOsDias
    SELECT 
    cpumem.SERVER,
    ,null
    ,null
    ,CAST(AVG(cpumem.CPU_AVG_PERC) as Numeric(10,2)) as CPU_AVG
    ,null
    ,null
    --INTO Trend_Data_Qua
    FROM
    ...
    INSERT TodosOsDias
    SELECT 
    cpumem.SERVER,
    ,null
    ,null
    ,null
    ,CAST(AVG(cpumem.CPU_AVG_PERC) as Numeric(10,2)) as CPU_AVG
    ,null
    --INTO Trend_Data_Qui
    FROM
    ...
    INSERT TodosOsDias
    SELECT 
    cpumem.SERVER,
    ,null
    ,null
    ,null
    ,null
    ,CAST(AVG(cpumem.CPU_AVG_PERC) as Numeric(10,2)) as CPU_AVG
    --INTO Trend_Data_Sex
    FROM

    Depois basta consultar a tabela; Select * from TodosOsDias

    • Marcado como Resposta Aloysio sábado, 11 de junho de 2016 22:49
    • Não Marcado como Resposta Aloysio sábado, 11 de junho de 2016 23:59
    sábado, 11 de junho de 2016 08:00
  • Deleted
    • Marcado como Resposta Aloysio domingo, 12 de junho de 2016 00:01
    sábado, 11 de junho de 2016 13:10
  • Perfeita sua ideia Marcelo.
    Desta forma elimino as múltiplas tabelas e consigo fazer o que eu queria.
    Funcionou perfeitamente.
    Muito obrigado.


    Aloysio Coutinho

    sábado, 11 de junho de 2016 22:49
  • Cara, essa foi a solução perfeita na verdade.
    Depois que testei a ideia do Marcelo em uma base com dados (o teste anterior foi em uma base semi vazia), percebi que a solução dele gerava múltiplas linhas por servidor (uma para cada dia da semana).
    Testando com a sua solução, ficou exatamente como eu preciso, apesar das tabelas intermediárias serem mantidas, mas acho que isso não será um problema.


    Aloysio Coutinho


    • Editado Aloysio domingo, 12 de junho de 2016 02:03
    • Marcado como Resposta Aloysio quarta-feira, 13 de julho de 2016 13:26
    domingo, 12 de junho de 2016 00:01