时间戳

首页 > 解决方案 > MS SQL Server - 匹配表及其不相关的记录

sql - MS SQL Server - 匹配表及其不相关的记录

问题描述

这可能比我想象的更普遍，但几个月来我一直在努力解决它，但完全失败了。

两张表由完全不相关的系统提供关于相同事物的信息。但是他们对这些事情使用不同的标识符，我想找到一种方法将这些表合并为一个，其中 A 中的每条记录都将匹配其在 B 中的“最等效”记录。

两个数据库看起来像这样：

A.sessionID     A.itemSeq   A.dateSessionStart
1870            32          2017-12-20 16:00:00     
1871            55          2017-12-23 17:00:00
1871            56          2017-12-23 17:00:00
1871            57          2017-12-23 17:00:00
1873            24          2018-01-05 15:00:00
1873            25          2018-01-05 15:00:00
1878            36          2018-01-12 15:30:00



B.sessionID     B.itemID    B.dateItem
520             1           2017-12-20 16:04:32     
522             1           2017-12-23 17:15:04
522             2           2017-12-23 17:32:26

523             2           2018-01-05 15:41:06
523             3           2018-01-05 16:02:21
524             1           2018-01-12 15:37:55

这是我想获得的表/视图的一部分：

A.sessionID     B.sessionID     A.itemSeq       B.itemID        A.dateSessionStart      B.dateItem
1870            520             32              1               2017-12-20 16:00:00     2017-12-20 16:04:32 
1871            522             55              1               2017-12-23 17:00:00     2017-12-23 17:15:04
1871            522             56              2               2017-12-23 17:00:00     2017-12-23 17:32:04
1871            522             57              NULL            2017-12-23 17:00:00     NULL
1873            523             24              2               2018-01-05 15:00:00     2018-01-05 15:41:06
1873            523             25              3               2018-01-05 15:00:00     2018-01-05 16:02:21
1878            524             36              1               2018-01-12 15:30:00     2018-01-12 15:37:55

似乎每个表中的日期和顺序可能允许一种相似性关系。然而：

我会将我在 SQL 查询方面的知识描述为从基础到中级。我试图在 SELECT 下创建一个子查询，在两个表的日期时间之间通过 DATEDIFF 进行匹配，在我拥有的所有 SQL 书籍中寻找一个示例，在这里寻找一个类似的问题......但到目前为止我还没有成功。

谢谢！

法布里西奥·罗查

巴西利亚，巴西

标签： sqlsql-serversql-server-2008

解决方案

您遇到的问题是您已将所有这些数据加载到 RELATIONAL 数据库管理系统中；通过明确定义表格之间的关系来工作的设备

而且你没有明确定义的关系

尽我所能，您断言“每个系统中序列 ID 中的项目顺序大致相同，时间大致相同”

好的，所以让我们假设这些系统都注意到播客何时发生，并且其中一个系统的时钟有故障，只报告小时，但除此之外，它是一个保持时间的工作时钟

看起来 B 的时钟比 A 的时钟早 3 分钟，假设您断言 b 的 16:02 相当于 a 的 15:00，但 b 的 16:04 相当于 A 的 16:00

让我们把这些数据变成相关的东西：

SELECT * FROM
(
  SELECT
    dateSessionStart,
    ROW_NUMBER() OVER(PARTITION BY dateSessionStart ORDER BY sessionId, itemSeq) rn
  FROM a
) a
FULL OUTER JOIN
(
  SELECT
    DATE_ADD(hour, HOUR(adj), CAST(CAST(adj as DATE) as DATETIME)) as dateSessionStart,
    ROW_NUMBER() OVER(PARTITION BY CAST(adj as DATE), HOUR(adj) ORDER BY sessionid, itemid) rn
  FROM(SELECT *, DATEADD(minute, -3, dateItem) adj FROM b) b
) b
ON a.dateSessionStart = b.dateSessionStart and a.rn = b.rn

如果您想获得更多详细信息，请单独运行子查询，但基本上此查询将 b 记录的时间每条调整 3 分钟，然后通过将时间的小时添加到日期（午夜）来去除分钟和秒

单独但使用这些调整后的小时精度时间，使用 row_number 建立递增计数器。每次更改小时，计数器都会从 1 重新开始。计数器按其他顺序 ID 的顺序递增。因此，我们不关心序列 ID 是否不等价，我们只是使用它们来定义生成伪连接键的顺序。加入关系是建立在小时精确时间加上重新启动计数器上的

请注意，ghisb 是在未经测试的手机上编写的 - 可能存在小的拼写错误/语法错误、缺少括号等 - 让我知道您在运行它时遇到的任何错误，我可以提供帮助，但我今天很忙，所以感觉如果您修复它们，您可以随意尝试并建议对我的答案进行编辑

分区并不难；把它们想象成一个单独的分组查询，它会自动连接回主数据：

SELECT name, age, city, AVG(age) OVER(PARTITION BY city) FROM t

SELECT name, age, city, avgagecity 
FROM
  t
  INNER JOIN
  (SELECT city, AVG(age) as avgagecity FROM t GROUP BY city) c on c.city = t.city

上述两个查询在概念上是相同的；在其中，我们按城市分组并平均年龄，然后将其加入主表。瞧

另一个我们要求数据库按城市划分数据，平均年龄。数据库将自动加入它的 city:avgage 桶。当 t.city 是“San Fran”时，db 去它的城市桶，寻找 san fran，拉出平均年龄，把它贴在行上

因此，这里的 PARTITION BY 实际上是 GROUP BY x JOIN x

对于顺序很重要的事情，比如 row_number，一个 ORDER BY 是必要的，原因很明显。在这类查询中，PARTITION BY 用于划分结果，但请注意，没有任何分组，因为 row_number 不是聚合操作。这里在有序操作（根本不需要分区）中，任何分区都指定何时重新开始，就好像数据在一个新的计数组中一样。其他一些常见的有序操作是 RANK 和 DENSE_RANK。与行号类似，但它们指的是获胜位置，因此值相等（进行了一场比赛，1 人在 3 分 59 秒内赢得一英里，然后两个人在 4 分钟内跑完一英里。他们的排名相同。排名跳过如果人数并列，则第 1 名，等于第 2 名第 2 名，第 4 名（没有第 3 名，因为第 2 名并列而被跳过。如果 3 人并列，4 第四名将跳过等）。DENSE_RANK 不跳过

推荐阅读