Oracle分析问题

s3fp2yjn 于 2022-11-03 发布在 Oracle

关注(0)|答案(4)|浏览(126)

给定一个函数zipdistance（zipfrom，zipto），该函数计算两个邮政编码之间的距离（以英里为单位），并给出以下表格：

create table zips_required(
   zip varchar2(5)
);

create table zips_available(
   zip varchar2(5),
   locations number(100)
);

我如何构造一个查询，它将从zips_required表中返回每个邮政编码，并返回产生sum（locations）〉= n的最小距离。
到目前为止，我们只是运行了一个详尽的循环，查询每个半径，直到我们满足条件。

--Do this over and over incrementing the radius until the minimum requirement is met
select count(locations) 
from zips_required zr 
left join zips_available za on (zipdistance(zr.zip,za.zip)< 2) -- Where 2 is the radius

在一个大列表上，这可能需要一段时间。感觉这可以通过一个Oracle分析查询来完成，如下所示：

min() over (
  partition by zips_required.zip 
  order by zipdistance( zips_required.zip, zips_available.zip)
  --range stuff here?
)

我所做的唯一的分析查询是基于“row_number over（partition by order by）”的，而我正在进入未知的领域。非常感谢对此的任何指导。

oracle

来源：https://stackoverflow.com/questions/1033780/oracle-analytic-question

4条答案

按热度按时间

kpbpu0081#

这是我想到的：

SELECT zr, min_distance
  FROM (SELECT zr, min_distance, cnt, 
               row_number() over(PARTITION BY zr ORDER BY min_distance) rnk
           FROM (SELECT zr.zip zr, zipdistance(zr.zip, za.zip) min_distance,
                         COUNT(za.locations) over(
                             PARTITION BY zr.zip 
                             ORDER BY zipdistance(zr.zip, za.zip)
                         ) cnt
                    FROM zips_required zr
                   CROSS JOIN zips_available za)
          WHERE cnt >= :N)
 WHERE rnk = 1

1.对于每个zip_required，计算到zip_available的距离，并按距离对它们进行排序
1.对于每一个zip_required，将count与range相加，可以知道在该距离的半径内有多少个zip_availables。
1.筛选器（第一个COUNT（locations）〉N的位置）
我用来创建示例数据：

INSERT INTO zips_required
   SELECT to_char(10000 + 100 * ROWNUM) FROM dual CONNECT BY LEVEL <= 5;

INSERT INTO zips_available
   (SELECT to_number(zip) + 10 * r, 100 - 10 * r FROM zips_required, (SELECT ROWNUM r FROM dual CONNECT BY LEVEL <= 9));

CREATE OR REPLACE FUNCTION zipdistance(zipfrom VARCHAR2,zipto VARCHAR2) RETURN NUMBER IS
BEGIN
   RETURN abs(to_number(zipfrom) - to_number(zipto));
END zipdistance;
/

注意：* 您在问题中使用了COUNT（位置）和SUM（位置），我假设是COUNT（位置）*

赞(0）回复(0）举报 2022-11-03

x8diyxa72#

SELECT  *
FROM    (
        SELECT  zip, zd, ROW_NUMBER() OVER (PARTITION BY zip ORDER BY rn DESC) AS rn2
        FROM    (
                SELECT  zip, zd, ROW_NUMBER() OVER (PARTITION BY zip ORDER BY zd DESC) AS rn
                FROM    (
                        SELECT  zr.zip, zipdistance(zr.zip, za.zip) AS zd
                        FROM    zips_required zr
                        JOIN    zips_available za
                        )
                )
        WHERE   rn <= n
        )
WHERE   rn2 = 1

对于每个zip_required，这将选择适合Nzip_available的最小距离，或者如果zip_available的数目小于N，则选择最大距离。

赞(0）回复(0）举报 2022-11-03

0aydgbwb3#

我解决了同样的问题，方法是在给定的邮政编码的平方半径内创建一个邮政编码的子集（简单的数学：NSWE< or >半径），然后迭代子集中的每个条目，看看它是否在所需的半径内。工作起来像一个魅力，速度非常快。

赞(0）回复(0）举报 2022-11-03

nxagd54h4#

在我以前的一个项目中，我有部分类似的需求......计算美国两个邮政编码之间的距离。为了解决这个问题，我充分利用了美国空间数据。基本上，方法是获得源邮政编码（纬度、经度）和目的地邮政编码（纬度、经度）。现在，我已经应用了一个函数，以获得基于上述距离。following site中提供了有助于进行此计算的基本公式，我还通过参考this site验证了结果...
注意：然而，这将提供近似的距离，所以你可以相应地使用它。好处是一旦构造了它的超快获取结果。

赞(0）回复(0）举报 2022-11-03

我来回答

Oracle分析问题

4条答案

相关问题

热门标签

最新问答