主页 / dba / 问题 / 1103
Accepted
jcolebrand
Asked: 2011-02-07 15:47:29 +0800 CST

日期对齐和对匹配提取最好用 TSQL 或 C# 完成？

  • 772

首先是：我的头衔很糟糕，所以帮我想出一个新的？

我不能在这里发布所有的 SQL（很多超过 30k 个字符），所以我把它贴在pastebin.com上

问题：

我得到一个 XML 文件，我从中抓取了一些记录，我需要从记录中提取一些数据并根据它构建另一个表。这些记录是关于一个事件发生和发生的，我已经在 pastebin 中包含了示例数据以供重新创建。没有看到数据，这有点难以解释。我已经提供了我从样本导入中获得的所有数据，这些数据应该足以构建应用程序，但我没有得到比数据中显示的更多的信息。

我会给你一点时间看一下数据，这样就可以理解了。

所以我需要做的是：对于每个“关闭”事件，我需要将它与下一个“打开”事件匹配，并且我需要在最后有两个表，一个表用于“历史事件”，一个表用于“历史事件”对于“时事”。但是，如果我能正确构建“历史事件”，我就能弄清楚如何从中获取“当前事件”。

商业规则：

如果在“开启”事件之前收集到两个或更多“关闭”事件，则保留最旧的“关闭”事件。如果在“关闭”事件之前收集到两个或更多“打开”事件，则保留最新的“打开”事件。如果有完整的一对，则将它们放入历史表中。如果有一个“关闭”事件而不是“打开”事件，请将其放入当前表中（因此，如果我想继续从该表中插入/删除，那也很好）。如果当前表中已经有一个“关闭”事件，我可以将它移动到历史表中以读取一个“打开”事件（这将需要稍后实现，但如果我可以匹配配对最初我现在可以继续前进。

我认为这几乎就是逻辑。我的想法是要么弄清楚如何在 SQL 中执行此操作，要么将其推送到用 C# 编写的应用程序，并使用 C# 中的一些临时列表来执行此操作，并构建我需要使用的 for...next 逻辑。这在 C# 中可能要容易得多，但我感觉 SQL 可以像 C# 一样轻松地完成这项工作，所以我需要 dba 专家的一些帮助。

我的查询已经不起作用，但这就是我在周五回家之前开始的地方，从那以后我一直在考虑它，并构建一个我可以在线发布的示例问题（以及整个生活你也知道）。数据是实时数据并且是准确的，除了 ID 被匿名化并且文本字段更改为易于使用的内容。

这是一个电子表格，大致显示了我希望数据在结束时的样子以及现在的样子。有当前数据（为清楚起见，每个 ID 之间有一个间隔行）、历史表中的数据（与原始数据的 ID 对齐以便理解）和当前表（再次对齐）。我希望这可以帮助澄清业务规则。 https://spreadsheets.google.com/ccc?key=0AuvCdeHuVU5ddHRCNkpuWHBUREpRajlmLU5VX2xsWnc&hl=en&authkey=COq7y50H

所以完整的 SQL 包括 tabledefs 和当前（非常不正确的）查询在 pastebin http://pastebin.com/k2f2CLnQ

sql-server-2008 query

2 个回答

  1. 这是我根据自己的一些工作摆弄的东西。它不能很好地处理聚集在同一时间的事件。无论如何，理论上它可能会有帮助......:)

    ;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

    因此，对于继续评论，以及可能的答案：

    我刚刚将它导出到 C# 并在那里进行处理。按程序进行比按组进行更容易，而且我仍然需要弄清楚哪个先出现，关闭或打开（当它们并发时）。与他们的 PM 合作知道，但我有一种感觉，即使他们不知道什么时候会发生。

    无论如何，为了让所有讨论都集中在一个地方，也请参阅此记录：（如果您真的感兴趣）http://chat.stackexchange.com/rooms/179/conversation/date-alignment-and-pair- matching-extraction-best-done-with-tsql-or-c所以就是这样。

    • 2

相关问题