时间戳

首页 > 解决方案 > 根据其他列中的条件选择值对 - PostgreSQL

sql - 根据其他列中的条件选择值对 - PostgreSQL

问题描述

过去几天我一直在尝试解决一个问题，但无法弄清楚解决方案是什么......

我有一张如下表：

+--------+-----------+-------+
| ShopID | ArticleID | Price |  
+--------+-----------+-------+
|      1 |         3 |   150 | 
|      1 |         2 |    80 |  
|      3 |         3 |   100 |  
|      4 |         2 |    95 |  
+--------+-----------+-------+

并且我想选择同一篇文章价格更高的商店ID对。Fe 这应该是这样的：

+----------+----------+---------+
| ShopID_1 | ShopID_2 |ArticleID|
+----------+----------+---------+
|        4 |        1 |       2 |
|        1 |        3 |       3 |
+----------+----------+---------+

...表明第 2 条在 ShopID 4 中比在 ShopID 2 中更贵。等等

到目前为止，我的代码如下所示：

SELECT ShopID AS ShopID_1, ShopID AS ShopID_2, ArticleID FROM table
WHERE table.ArticleID=table.ArticleID and table.Price > table.Price

但它没有给出我正在寻找的结果。

任何人都可以帮助我实现这个目标吗？非常感谢你。

标签： sqlpostgresql

解决方案

这里的问题是关于计算每个组的前 N ​​个项目。

假设您有以下数据，在表中sales。

# select * from sales;
 shopid | articleid | price 
--------+-----------+-------
      1 |         2 |    80
      3 |         3 |   100
      4 |         2 |    95
      1 |         3 |   150
      5 |         3 |    50

通过以下查询，我们可以为每个ArticleId

select 
  ArticleID, 
  ShopID, 
  Price, 
  row_number() over (partition by ArticleID order by Price desc) as Price_Rank from sales;

这将导致：

 articleid | shopid | price | price_rank 
-----------+--------+-------+------------
         2 |      4 |    95 |          1
         2 |      1 |    80 |          2
         3 |      1 |   150 |          1
         3 |      3 |   100 |          2
         3 |      5 |    50 |          3

然后我们只需为每个 AritcleId 选择 Top 2 项：

select 
  ArticleID,  
  ShopID, 
  Price
from (
  select 
    ArticleID, 
    ShopID, 
    Price, 
    row_number() over (partition by ArticleID order by Price desc) as Price_Rank 
  from sales) sales_rank
where Price_Rank <= 2;

这将导致：

 articleid | shopid | price 
-----------+--------+-------
         2 |      4 |    95
         2 |      1 |    80
         3 |      1 |   150
         3 |      3 |   100

最后，我们可以使用crosstab函数来获得预期的枢轴视图。

select * 
from crosstab(
  'select 
    ArticleID,  
    ShopID, 
    ShopID
  from (
    select 
      ArticleID, 
      ShopID, 
      Price, 
      row_number() over (partition by ArticleID order by Price desc) as Price_Rank 
    from sales) sales_rank
  where Price_Rank <= 2')
AS sales_top_2("ArticleID" INT, "ShopID_1" INT, "ShopID_2" INT);

结果：

 ArticleID | ShopID_1 | ShopID_2 
-----------+----------+----------
         2 |        4 |        1
         3 |        1 |        3

CREATE EXTENSION tablefunc;注意：如果遇到错误，您可能需要致电function crosstab(unknown) does not exist。

推荐阅读