SQL Server 3最接近位置

Question

我有2个表

用户包含一个用户ID和2个邮政编码（约100万个记录）

+--------+----------+----------+
| UserID | Zipcode1 | Zipcode2 |
+--------+----------+----------+
|      1 |    08003 |    10016 |
|      2 |    11780 |    48073 |
|      3 |    57106 |    33487 |
+--------+----------+----------+

位置包含一个位置ID和邮政编码（约1000个记录）

+------------+---------+
| LocationID | Zipcode |
+------------+---------+
|          1 |   33004 |
|          2 |   96818 |
|          3 |   08816 |
+------------+---------+

我的功能可以采用Zipcodes，连接到具有纬度/经度的表，计算用户zips和位置ZIP之间的距离，并返回较短的距离。

dbo.fnMinZipDistance(Location.Zipcode, User.Zipcode1, User.Zipcode2)
example from user 1 & location 1:
dbo.fnMinZipDistance('33004', '08003', '10016') returns 995.383 
because the distance from 33004 to 10016 is 995.383 
and the distance from 33004 to 08003 is 1067.802

对于每个用户ID，我需要最短的3个位置IDS到任何用户ZipCode。

。

我最初的攻击途径是获得每个位置的距离，按距离订购的用户分区，然后选择行号位于＆lt;4

WITH UserLocations as 
(
    SELECT
    U.UserID, 
    L.LocationID, 
    rowNum = row_number() Over(partition by U.UserID ORDER BY dbo.fnMinZipDistance(L.Zipcode, U.Zipcode1, U.Zipcode2))
    FROM
    USERS U
    JOIN LOCATIONS L ON 1 = 1
)
SELECT * FROM UserLocations WHERE rowNum < 4

这需要数天的时间才能运行，因为在我获得最接近的3个距离之前，我必须获得总计约10亿个记录的所有距离。

我的下一个想法是预先计算表格中的每一个可能的邮政编码距离，但这将约为18亿可能的组合（大约43000个活跃的美国邮政编码），我不确定这有多少会有所帮助。我现在正在运行它以进行比较，我在尝试的6个小时。

我需要大幅度减少此查询的运行时间。

任何建议将不胜感激。

Answer 1

预先计算从每个邮政编码到每个位置邮政编码的距离可能是最好的方法。

仅处理44,000个邮政编码，这已经是处理原始数据中的200万人的巨大胜利。您可以在摘要表中保留五个最接近的邮政编码。

您还可以简化搜索。我认为您可以假设最接近的邮政编码在给定的邮政编码的一定距离内（例如100英里）。这将使您可以在搜索范围内放在纬度和经度上。