包含用户信息位的历史sql表 - 使用1个条目和所有信息创建新表

Question

我有一个表(客户)，它有43列用户信息(名、姓、地址、城市、州、邮政、电话、电子邮件、visitDate、lastActive等)

每天晚上，我都从我们的客户那里得到当天拜访他们的客户的信息。这些访问被存储到customers表中，而不删除旧记录。旧记录被标记为lastActive = 0，新记录被标记为lastActive = 1。任何空字段存储为"Unknown"。

显然，这会导致一个非常大的表，需要一些时间来查询。因此，我计划创建一个新表，其中只包含不同的用户及其最完整的信息。

例如:如果Bob Smith在1月1日被导入，没有电话或电子邮件，然后在8月1日再次导入，带电话，但没有电子邮件，然后在9月1日再次导入，没有电话，但有电子邮件，我的客户表看起来像这样:

CustImportID  CustomerKey  FirstName  LastName  Phone      Email   visitDate   lastActive
1             1            Bob        Smith     Unknown    Unknown 2016-01-01  0
2             1            Bob        Smith     5551231234 Unknown 2016-08-01  0
3             1            Bob        Smith     Unknown    [e-mail]  2016-09-01  1

所以我的问题是，从客户表中获取不同的人并将他们插入到新表中的最佳方法是什么?其中Bob只有一个条目，但我将为每个字段提供值(例如，如果每个条目都有电话，我们将从最近的条目中提取电话)，结果如下所示:

CustomerKey  FirstName  LastName  Phone      Email  visitDate   
1            Bob        Smith     5551231234 [e-mail] 2016-09-01

Answer 1

您可以使用 FIRST_VALUE 与一个技巧来忽略'Uknown'值:

SELECT FirstName, LastName,
       FIRST_VALUE(Phone) OVER (ORDER BY CASE 
                                            WHEN Phone='Unknown' THEN 1 
                                            ELSE 0
                                         END,
                                         visitDate DESC) AS Phone,
       FIRST_VALUE(Email) OVER (ORDER BY CASE 
                                            WHEN Email='Unknown' THEN 1 
                                            ELSE 0
                                         END,
                                         visitDate DESC) AS Email
FROM mytable

FIRST_VALUE在SQL Server 2012中可用。它选择由OVER子句的ORDER BY指定的最新字段值。由于ORDER BY子句中的CASE, 'Unknown'值将具有最低的优先级。

Answer 2

您可以使用所有记录的最大值，这将导致以下结果:

select customerkey, max(firstname), max(lastname), max(phone), max(email), max(visitdate) from yourtablename

如果您有两个以上的有效条目，那么使用row_number并根据最近的值选择max