时间戳

首页 > 解决方案 > Postgres查询问题与两个表的价格匹配

sql - Postgres查询问题与两个表的价格匹配

问题描述

我有两张桌子，一张是买桌，一张是卖桌。

购买表

id  price   qty  in_status  dt_added_at
1   10      15      1       2018-08-10 12:00:00+05:30
2   10      20      1       2018-08-10 13:00:00+05:30
3   12      15      1       2018-08-10 14:00:00+05:30
4   15      20      1       2018-08-10 15:00:00+05:30

卖表

id  price   qty  in_status  dt_added_at
1   15      20      1       2018-08-10 12:00:00+05:30
2   12      15      1       2018-08-10 13:00:00+05:30
3   10      15      1       2018-08-10 14:00:00+05:30
4   10      20      1       2018-08-10 15:00:00+05:30
5   10      15      1       2018-08-10 16:00:00+05:30

价格匹配表的应用查询

SELECT buy.*, sell.* FROM buy 
LEFT JOIN sell ON 
buy.price = sell.price AND buy.qty = sell.qty
WHERE buy.price = 10 AND buy.in_status = 1 AND sell.price = 10 AND 
sell.in_status = 1 
GROUP BY buy.id, sell.id, buy.dt_added_at, sell.dt_added_at 
ORDER BY buy.id, sell.id

实际输出

id  price   qty  in_status  dt_added_at                   id    price   qty  in_status  dt_added_at
1   10      15      1       2018-08-10 12:00:00+05:30     3     10      15      1       2018-08-10 14:00:00+05:30
1   10      15      1       2018-08-10 12:00:00+05:30     5     10      15      1       2018-08-10 16:00:00+05:30
2   10      20      1       2018-08-10 13:00:00+05:30     4     10      20      1       2018-08-10 15:00:00+05:30

所以这里买表的第 1 条记录与卖表的第 3 条和第 5 条记录相匹配，但第 5 条记录应该被省略，因为我需要实现 FIFO，并且一条购买记录仅与一个出售条目匹配，而购买表的第 2 条记录匹配与第 4 记录。

预期产出

id  price   qty  in_status  dt_added_at                   id    price   qty  in_status  dt_added_at
1   10      15      1       2018-08-10 12:00:00+05:30     3     10      15      1       2018-08-10 14:00:00+05:30
2   10      20      1       2018-08-10 13:00:00+05:30     4     10      20      1       2018-08-10 15:00:00+05:30

您可以在下图中看到。

在此处输入图像描述

编辑于 2018 年 8 月 18 日 - 尝试使用此数据但它不起作用， 因为重复了卖出表的条目。

CREATE TABLE Buy(
  id int,
  price int,
  qty int,
  in_status int,
  dt_added_at timestamp
);

insert into Buy values (1,10,15,1,'2018-08-10 12:00:00+05:30');
insert into Buy values (2,10,20,1,'2018-08-10 13:00:00+05:30');
insert into Buy values (3,10,15,1,'2018-08-10 14:00:00+05:30');
insert into Buy values (4,10,20,1,'2018-08-10 15:00:00+05:30');

CREATE TABLE sell(
  id int,
  price int,
  qty int,
  in_status int,
  dt_added_at timestamp
);

insert into sell values (1,15,20,1 ,'2018-08-10 12:00:00+05:30');
insert into sell values (2,12,15,1 ,'2018-08-10 13:00:00+05:30');
insert into sell values (3,10,15,1 ,'2018-08-10 14:00:00+05:30');
insert into sell values (4,10,20,1 ,'2018-08-10 15:00:00+05:30');
insert into sell values (5,10,15,1 ,'2018-08-10 16:00:00+05:30');
insert into sell values (6,10,20,1 ,'2018-08-10 17:00:00+05:30');

标签： sqlpostgresql

解决方案

您可以尝试使用Row_number编写一个子查询，window function 使 row_number 为priceand qty，然后您可以得到rn = 1较早购买时间的意思dt_added_at。

CREATE TABLE Buy(
  id int,
  price int,
  qty int,
  in_status int,
  dt_added_at timestamp
);


insert into Buy values (1,10,15,1,'2018-08-10 12:00:00+05:30');
insert into Buy values (2,10,20,1,'2018-08-10 13:00:00+05:30');
insert into Buy values (3,12,15,1,'2018-08-10 14:00:00+05:30');
insert into Buy values (4,15,20,1,'2018-08-10 15:00:00+05:30');

CREATE TABLE sell(
  id int,
  price int,
  qty int,
  in_status int,
  dt_added_at timestamp
);


insert into sell values (1,15,20,1 ,'2018-08-10 12:00:00+05:30');
insert into sell values (2,12,15,1 ,'2018-08-10 13:00:00+05:30');
insert into sell values (3,10,15,1 ,'2018-08-10 14:00:00+05:30');
insert into sell values (4,10,20,1 ,'2018-08-10 15:00:00+05:30');
insert into sell values (5,10,15,1 ,'2018-08-10 16:00:00+05:30');

查询 1：

SELECT buy.*, s.*
FROM buy 
LEFT JOIN (
    select s.*,Row_number() over(PARTITION BY price,qty ORDER BY dt_added_at) rn
    from sell s
) s ON 
buy.price = s.price AND buy.qty = s.qty and rn = 1
WHERE 
    buy.price = 10 
AND 
  buy.in_status = 1 
AND 
  s.price = 10 
AND 
  s.in_status = 1

结果：

| id | price | qty | in_status |          dt_added_at | id | price | qty | in_status |          dt_added_at | rn |
|----|-------|-----|-----------|----------------------|----|-------|-----|-----------|----------------------|----|
|  1 |    10 |  15 |         1 | 2018-08-10T12:00:00Z |  3 |    10 |  15 |         1 | 2018-08-10T14:00:00Z |  1 |
|  2 |    10 |  20 |         1 | 2018-08-10T13:00:00Z |  4 |    10 |  20 |         1 | 2018-08-10T15:00:00Z |  1 |

推荐阅读