Início / dba / Perguntas / 1103
Accepted
jcolebrand
Asked: 2011-02-07 15:47:29 +0800 CST

Alinhamento de data e extração de par correspondente melhor feito com TSQL ou C #?

  • 772

Para começar: meu título é péssimo, então me ajude a descobrir um novo?

Não posso postar todo o SQL aqui (mais de 30 mil caracteres), então colei no pastebin.com

O problema:

Recebo um arquivo XML do qual extraí alguns registros e preciso extrair alguns dados dos registros e criar outra tabela a partir dele. Os registros são para um evento acontecendo e acontecendo, e incluí dados de amostra no pastebin para recriação. Sem ver os dados é meio difícil de explicar. Forneci todos os dados que tenho da minha amostra de importação, que devem ser suficientes para criar o aplicativo, mas não estou obtendo mais informações do que as mostradas nos dados.

Vou dar a você um momento para olhar os dados para que isso faça sentido.

Então, o que preciso fazer é o seguinte: para cada evento "desligado", preciso combiná-lo com o próximo evento "ligado" e preciso ter duas tabelas no final, uma tabela para "eventos históricos" e uma tabela para "eventos atuais". No entanto, se eu conseguir "eventos históricos" construídos corretamente, posso descobrir como obter "eventos atuais" disso.

Regras do negócio:

Se dois ou mais eventos "desligados" forem coletados antes de um evento "ligado", mantenha o evento "desligado" mais antigo. Se dois ou mais eventos "ligados" forem coletados antes de um evento "desligado", mantenha o evento "ligado" mais recente. Se houver um par completo, coloque-os na tabela histórica. Se houver um evento "off" e não um evento "on", coloque-o na tabela atual (portanto, se eu quiser continuar inserindo/excluindo desta tabela, tudo bem também). Se já houver um evento "desligado" na tabela atual, posso pegá-lo e movê-lo para a tabela histórica para que um evento "ligado" seja lido (isso precisará ser implementado posteriormente, mas se eu conseguir que os pares correspondam inicialmente eu poderei ir adiante por enquanto.

Eu acho que é basicamente isso para a lógica. Meus pensamentos são descobrir como fazer isso em SQL ou enviá-lo para um aplicativo escrito em C# e fazê-lo com algumas listas temporárias em C# e construir o que preciso usando para... a próxima lógica. Isso pode ser infinitamente mais fácil em C#, mas tenho a sensação de que o SQL pode fazer esse trabalho tão facilmente quanto o C#, então precisava da ajuda dos gurus do dba.

As consultas que já fiz não funcionam , mas foi aí que comecei antes de ir para casa na sexta-feira, e venho refletindo sobre isso desde então e construindo um exemplo de problema que posso postar online (e toda essa vida coisa também, você sabe). Os dados são dados ao vivo e exatos, exceto os IDs sendo anonimizados e os campos de texto alterados para algo simples de se trabalhar.

Aqui está uma planilha mostrando aproximadamente como quero que os dados apareçam no final e como estão agora. Existem os dados atuais (com uma linha espaçadora entre cada ID para maior clareza), os dados que estariam na tabela histórica (alinhados com o ID dos dados originais para entendimento) e a tabela atual (novamente alinhados). Espero que isso possa ajudar a esclarecer as regras de negócios. https://spreadsheets.google.com/ccc?key=0AuvCdeHuVU5ddHRCNkpuWHBUREpRajlmLU5VX2xsWnc&hl=en&authkey=COq7y50H

Portanto, o SQL completo, incluindo tabledefs e consultas atuais (muito incorretas), está em pastebin http://pastebin.com/k2f2CLnQ

sql-server-2008 query

2 respostas

  1. Aqui está algo que eu estava mexendo com base em algum trabalho que eu tinha por aí. Ele não lida bem com os eventos agrupados em um único momento. Poderia teoricamente ser útil de qualquer maneira ... :)

    ;WITH ordered_rows AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY EventTime) AS Row,
        Identifier, Type, EventTime, DiscoveredDate, FileId FROM #EventDataTemp
)
,filtered_rows AS
(
    SELECT Row, Identifier, Type, EventTime, DiscoveredDate, FileId, 
        CAST(CASE Type WHEN 'Went Off' THEN 1 ELSE NULL END AS INT) 
            AS OffEventRow
    FROM ordered_rows
    WHERE Row = 1
    UNION ALL
    SELECT o.Row, o.Identifier, o.Type, o.EventTime, o.DiscoveredDate, o.FileId,
        CAST(CASE WHEN o.Type = 'Went Off' AND f.Type = 'Went Off' 
            OR o.Type = 'Came On' THEN f.OffEventRow ELSE o.Row END AS INT)
    FROM ordered_rows o INNER JOIN filtered_rows f 
        ON o.Row = f.Row + 1 AND o.Identifier = f.Identifier
)
,on_events AS
(
   SELECT Identifier, OffEventRow, MAX(Row) AS OnRow
   FROM filtered_rows
   WHERE Type = 'Came On' AND OffEventRow IS NOT NULL
   GROUP BY OffEventRow, Identifier
)
SELECT f.Identifier, f.Type, f.EventTime, f.DiscoveredDate, f.FileId 
FROM filtered_rows f LEFT JOIN on_events o 
    ON f.Identifier = o.Identifier 
    AND f.Row = o.OnRow
WHERE f.Type = 'Went Off' AND f.Row = f.OffEventRow
OR f.Type = 'Came On' AND o.OnRow IS NOT NULL
ORDER BY f.Identifier, f.EventTime
    • 6
  2. Best Answer

    Portanto, para comentários contínuos e como provavelmente será a resposta:

    Acabei de exportá-lo para C# e processá-lo lá. Será mais fácil fazê-lo processualmente do que por conjuntos, e ainda preciso descobrir o que vem primeiro, desativado ou ativado (quando são simultâneos). Trabalhando com o PM para saber, mas tenho a sensação de que nem eles sabem o que acontece quando.

    De qualquer forma, para que toda a discussão seja mantida em um só lugar, veja esta transcrição também: (se você estiver realmente interessado) http://chat.stackexchange.com/rooms/179/conversation/date-alignment-and-pair- correspondência-extração-melhor-feito-com-tsql-ou-c , então é isso.

    • 2

relate perguntas