sql - 按更近的距离获取 MIN MAX 分组

Question

商店

id_shop | id_prod_ty | position

和

产品

id | price | id_prod_ty | distance

表 shops 包含每个 id_prod_ty(产品类型)和一个位置的 2 个商店。
表产品包含许多具有不同价格和距离的记录

基本上我需要有一个查询，为每个产品和每个商店选择低价和高价，这些商店按更接近的价格分组(并且不接近其他商店)

例如

商店

id_shop | id_prod_ty | position  1     |     1      |    3  2     |     1      |    7  3     |     2      |    8  4     |     2      |    4....

产品

id | price | id_prod_ty | distance1  |  10   |   1        |  1        2  |  04   |   1        |  2 3  |  02   |   1        |  4 4  |  44   |   1        |  2 5  |  09   |   1        |  1 6  |  13   |   1        |  77  |  15   |   1        |  88  |  09   |   2        |  5 9  |  12   |   2        |  810 |  17   |   2        |  111 |  32   |   2        | 1312 |  22   |   2        |  2...

结果喊成这样

id_prod_ty | id_price_low | id_price_high | id_shop1          |     3 (02)   |      4 (44)   |  11          |     6 (13)   |      7 (15)   |  22          |     8 (09)   |     12 (22)   |  42          |     9 (12)   |     11 (32)   |  3...

谢谢

Answer 1

我想我理解了这个问题。对于每个价格，您都试图根据距离和位置分配最近的商店。

该方法从连接位置和商店开始。然后它计算位置和距离之间的差异——这似乎是您使用的度量。每个价格 ID 将出现两次(每个商店一次)。它使用窗口函数计算每个 id 的最小差异。

有了这些信息，查询就可以确定哪个是更近的商店。此版本返回价格，而不是最低价格的 ID。这是最终的、未经测试的查询:

select id_prod_ty,
       MIN(case when diff = minDiff then price) as price_low,
       MAX(case when diff = minDiff then price) as price_high,
       s.id_shop
from (select p.id_prod_ty,
             s.id_shop,
             p.price,
             (p.distance - s.position) as diff,
             MIN(p.distance - s.position) over (partition by p.id) as minDiff
      from products p join
           shops s
           on p.id_prod_ty = s.id_prod_ty
    ) ps
group by id_prod_ty, id_shop

这是一个也获取 ID 的变体。它使用一个窗口函数来查找最低和最高价格，然后将价格与这些值进行比较以获得 ids:

select id_prod_ty,
       MIN(minPrice) as price_low,
       MIN(case when price = minPrice then id end) id_price_low,
       MAX(maxprice) as price_high,
       MIN(case when price = maxPrice then id end) id_price_high,
       id_shop
from (select *,
             MIN(IsThisShopPrice) over (partition by id_prod_ty, id_shop) as minPrice,
             MIN(IsThisShopPrice) over (partition by id_prod_ty, id_shop) as maxPrice,
      from (select p.id_prod_ty, s.id_shop, p.price, p.id,
                   (p.distance - s.position) as diff,
                   (case when (p.distance - s.position) = MIN(p.distance - s.position) over (partition by p.id)
                         then 'Y'
                         else 'N'
                    end) as IsThisShop
                   (case when (p.distance - s.position) = MIN(p.distance - s.position) over (partition by p.id)
                         then price
                    end) as IsThisShopPrice
            from products p join
                 shops s
                 on p.id_prod_ty = s.id_prod_ty
           ) ps
    ) ps
group by id_prod_ty, id_shop