代码之家 › 专栏 › 技术社区 › PyRsquared

如何链接两个表,只取PostgreSQL中一个表的最大值?

postgres-9.6 greatest-n-per-group postgresql sql

PyRsquared · 技术社区 · 8 年前

我有两张桌子

exchange_rates
TIMESTAMP             curr1    curr2    rate
2018-04-01 00:00:00   EUR      GBP      0.89
2018-04-01 01:30:00   EUR      GBP      0.92
2018-04-01 01:20:00   USD      GBP      1.23

和

transactions
TIMESTAMP             user    curr    amount
2018-04-01 18:00:00   1       EUR     23.12
2018-04-01 14:00:00   1       USD     15.00
2018-04-01 01:00:00   2       EUR     55.00

我想把这两张表连在一号桌上。货币和2。时间戳如下:

curr 在里面 transactions 必须等于 curr1 在里面 exchange_rates
TIMESTAMP 在里面 汇率 必须小于或等于 时间戳 在里面 交易 (因此,我们只收取交易时相关的汇率)

我有这个:

SELECT
trans.TIMESTAMP, trans.user,
-- Multiply the amount in transactions by the corresponding rate in exchange_rates
trans.amount * er.rate AS "Converted Amount"
FROM transactions trans, exchange_rates er
WHERE trans.curr = er.curr1
AND er.TIMESTAMP <= trans.TIMESTAMP
ORDER BY trans.user

但由于输出的行数多于 交易 。

期望输出:

TIMESTAMP             user    Converted Amount
2018-04-01 18:00:00   1       21.27
2018-04-01 14:00:00   1       18.45
2018-04-01 01:00:00   2       48.95

背后的逻辑 Converted Amount :

第1行:用户在18:00花费,因此 rate 小于或等于 时间戳 在里面 汇率 即01:30欧元0.92
第2行:用户在14:00花费,因此 率 小于或等于 时间戳 在里面 汇率 即1:20美元兑换1.23
第3行:用户在01:00花费,因此 率 小于或等于 时间戳 在里面 汇率 即0:00欧元兑换0.89欧元

在PostgreSQL 9.6中,我如何做到这一点 ?

1 回复 | 直到 8 年前

McNets 8 年前

你可以用 LATERAL JOIN (交叉应用)并将结果限制在与条件匹配的第一行。

select t.dt, t.usr, t.amount * e.rate as conv_amount
from   transactions t
join lateral (select *
              from   exchange_rates er
              where  t.curr = er.curr1
              and    er.dt <= t.dt
              order by dt desc
              limit 1) e on true;

dt                  | usr | conv_amount
:------------------ | --: | ----------:
2018-04-01 18:00:00 |   1 |     21.2704
2018-04-01 14:00:00 |   1 |     18.4500
2018-04-01 01:00:00 |   2 |     48.9500

DB<gt;小提琴 here

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

1 年前

John D · 需要为NULL或NOT NULL的WHERE子句

1 年前

ojek · 如何对SQL结果进行分组和编号?

1 年前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

1 年前

Sax · 规范化Google表格(第一步)

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

1 年前