时间戳

首页 > 解决方案 > 在 postgres 中为市场编写查询需要帮助

sql - 在 postgres 中为市场编写查询需要帮助

问题描述

我有一个市场数据库，如下所示：

在此处输入图像描述

我有不同的供应商以不同的价格销售相同的产品，而且某些产品在给定位置比其他产品更受欢迎。例如，我们有产品 A 和产品 B，根据在该位置已售出的数量，产品 A 比产品 B 更受欢迎，对于产品 A，我们有 3 个供应商。我希望我的查询显示最便宜的卖家的产品 A，然后是最便宜的卖家的产品 B。我可以通过以下代码实现：

WITH tem_1 AS (SELECT product_id, MIN(price) AS price FROM product_supplier GROUP BY product_id) ,
     tem_2 AS (SELECT product_id, SUM(quantity) AS n_orders FROM orders Group by product_id)
SELECT products.product_id, suppliers.supplier_id, products.name, tem_1.price,
COALESCE(tem_2.n_orders,0) AS quant FROM products
  INNER JOIN product_supplier ON product_supplier.product_id = products.product_id
  INNER JOIN suppliers ON suppliers.supplier_id = product_supplier.supplier_id
  INNER JOIN product_code ON product_code.code_id = products.code_id
  INNER JOIN product_crop ON product_crop.product_id = products.product_id
  INNER JOIN crops ON crops.crops_id = product_crop.crop_id
  INNER JOIN product_tags ON product_tags.product_id = products.product_id
  INNER JOIN tags ON tags.tag_id = product_tags.tag_id
  INNER JOIN tem_1 ON tem_1.price = product_supplier.price AND tem_1.product_id = products.product_id
  LEFT JOIN tem_2 ON tem_2.product_id = products.product_id
WHERE crops.crops_id = 1 AND product_supplier.quantity >= 3 AND tags.tag = 'علف کش'
ORDER BY quant DESC
LIMIT 10;

在此处输入图像描述

问题是，如果我有两个来自不同位置的不同供应商，以相同的价格销售相同的产品，结果显示该产品两次，但我只想要离用户最近的供应商的结果，在这种情况下产品 101 来自供应商 3 而不是供应商 1。

我想我必须使用MIN(ST_Distance("geopoint from user", "geopoint from suppliers"))并LATERAL有一个距离归档，但是因为我正在使用聚合函数，为了GROUP BY删除重复的结果，我必须将所有字段添加product_id, supplier_id, name, price, ...到GROUP BY并且不会导致删除重复项。

关于如何实现这一目标的任何建议？

标签： sqlpostgresql

解决方案

您的查询很难理解。但是，distinct on解决你的问题。我不是 100% 确定你想要与众不同，但像这样：

select distinct on (product_id, price) . . .
from . . .
where . . .
order by product_id, price, ST_Distance("geopoint from user", "geopoint from suppliers");

这将根据最小距离为每个产品和价格返回一行。

如果您希望数据以不同的方式排序，则将其用作子查询或 CTE，order by然后在外部查询中再次使用。

推荐阅读