时间戳

首页 > 解决方案 > SQL整合数据最佳策略

sql - SQL整合数据最佳策略

问题描述

我正在使用 SQL Server 2005。

我在同一个 SQL Server 中有 8 个数据库。每个数据库（属性）中有一个包含数千个客户的表。

为了简单

CustomerID      numeric(18,0)
PropertyID      int
CustomerSurname varchar(100)
CustomerName    varchar(50)
CustomerEmail   varchar(100)

到目前为止，每个物业都单独填充其客户。现在需要合并客户以进行报告。

我想在所有数据库中找到所有常见的客户

（条件= CustomerSurname + CustomerEmail + Customer Name 的首字母）

并填充一个新表（合并），其中包含每个普通客户的属性数据库的 PropertyID 和 CustomerID。

ConsolidationID numeric(18,0)
PropertyID      int
CustomerID      numeric(18,0)

想象一下：物业 1 上的客户

1000 1 Smith Adrian smith@jj.com

物业 2 的客户

9876 2 Smith A smith@jj.com

合并表

1 1 1000
1 2 9876

因此，在合并表中，Smith 的 ID=1，在 Database1（属性）中，本地 ID 为 1000，在 Database2（属性）中，本地 ID 为 9876

我很困惑如何使用 8 个数据库之间的标准找到共同的客户。实现它的策略。

标签： sqlsql-serverconsolidation

解决方案

在这种情况下，整合数据是一个非常简单的过程。

这是一个可以在 SSMS 中运行以帮助您入门的示例。请注意，我使用的是 TABLE 变量而不是单独的数据库，但概念保持不变。

声明表（代表数据库）：

DECLARE @database1 TABLE ( CustomerID NUMERIC(18,0), PropertyID INT, CustomerSurname VARCHAR(100), CustomerName VARCHAR(50), CustomerEmail VARCHAR(100) );
DECLARE @database2 TABLE ( CustomerID NUMERIC(18,0), PropertyID INT, CustomerSurname VARCHAR(100), CustomerName VARCHAR(50), CustomerEmail VARCHAR(100) );

插入您提供的示例数据：

INSERT INTO @database1 ( CustomerID, PropertyID, CustomerSurname, CustomerEmail, CustomerName ) 
VALUES ( 1, 1000, 'Smith', 'Adrian', 'smith@jj.com' );

INSERT INTO @database2 ( CustomerID, PropertyID, CustomerSurname, CustomerEmail, CustomerName ) 
VALUES ( 2, 9876, 'Smith', 'A', 'smith@jj.com' );

加入一些 SQL Server 魔法：

SELECT
    ROW_NUMBER() OVER ( PARTITION BY CustomerSurname, CustomerEmail, FirstInitial ORDER BY CustomerSurname, CustomerEmail, FirstInitial ) AS ConsolidationID
    , Consolidated.CustomerID
    , Consolidated.PropertyID
FROM (

    SELECT CustomerID, PropertyID, CustomerSurname, CustomerName, CustomerEmail, LEFT( CustomerName, 1 ) AS FirstInitial FROM @database1
    UNION
    SELECT CustomerID, PropertyID, CustomerSurname, CustomerName, CustomerEmail, LEFT( CustomerName, 1 ) AS FirstInitial FROM @database2

) AS Consolidated
ORDER BY
    CustomerID, CustomerSurname, CustomerEmail, FirstInitial;

返回合并的结果集：

+-----------------+------------+------------+
| ConsolidationID | CustomerID | PropertyID |
+-----------------+------------+------------+
|               1 |          1 |       1000 |
|               1 |          2 |       9876 |
+-----------------+------------+------------+

使用它：

要将它与您的八个数据库一起使用，您只需将表变量（@database1、@database2 等）替换为要引用的数据库和表的完全限定名称。

SELECT {column-list} FROM MyDatabase1.dbo.TableName...
UNION
SELECT {column-list} FROM MyDatabase2.dbo.TableName...

ETC...

ROW_NUMBER()是这里真正的“魔法”。通过使用其 PARTION BY 和 ORDER BY，我们可以为与分区条件匹配的每一行获取一个“ConsolidationID”，在本例中为 CustomerSurname、CustomerEmail 和 FirstInitial。需要 ORDER BY 以确保正确排序数据，以便分区按预期工作。

需要注意的一些重要事项：

  1. 所有表之间的列名必须准确且顺序相同。如果需要，您可以为列名取别名。
  2. 当所有比较的列都相同时，使用 UNION 将排除完全重复的内容。我希望这是您在这种情况下想要的行为，但如果不是，请将UNION替换为UNION ALL以返回所有行，包括完全匹配。
  3. SQL Server 的ROW_NUMBER()是一个非常巧妙的功能。你可以在这里阅读更多关于它的信息。

我希望这可以帮助您上路。

推荐阅读