sql - 将时间戳舍入到 30 分钟间隔以进行分组

Question

问题

select currency,
       MAX (CASE WHEN type = 'Bank A' THEN rate ELSE null END) as bank_a_rate,
       MAX (CASE WHEN type = 'Bank B' THEN rate ELSE null END) as bank_b_rate
from rates
group by currency, created

我想按货币、时间戳对我的数据进行分组，并显示所有类型值，例如间隔为 30 分钟的比较表，现在我的创建时间相差 1 分钟或更短，所以如果我用创建时间分组，它仍然会显示 4 行原因不同的时间戳，有没有办法四舍五入时间戳？

数据源

类型	货币	速度	已创建
A银行	美元	3.4	2020-01-01 12:29:15
B银行	美元	3.34	2020-01-01 12:30:11
A银行	欧元	4.92	2020-01-01 12:31:01
B银行	欧元	5.03	2020-01-01 12:31:14

预期结果

时间戳	货币	银行A利率	B银行利率
2020-01-01 12:30:00	美元	3.4	3.34
2020-01-01 12:30:00	欧元	4.92	5.03

标签： sqlpostgresql

Answer 1

截断/舍created入到 30 分钟（ts表达式）并按它分组。您对此修正案的查询：

select date_trunc('hour', created) + 
       interval '1 minute' * (extract(minute from created)::integer/30)*30 AS ts, 
       currency,
       MAX (CASE WHEN "type" = 'Bank A' THEN rate ELSE null END) as bank_a_rate,
       MAX (CASE WHEN "type" = 'Bank B' THEN rate ELSE null END) as bank_b_rate
from rates
group by currency, ts;

SQL小提琴

'继承'以前的费率

select ts, currency, 
  coalesce(bank_a_rate, lag(bank_a_rate) over w) bank_a_rate,
  coalesce(bank_b_rate, lag(bank_b_rate) over w) bank_b_rate
from
(           
 select date_trunc('hour', created) + 
       interval '1 minute' * (extract(minute from created)::integer/30)*30 ts, 
       currency,
       MAX (CASE WHEN "type" = 'Bank A' THEN rate ELSE null END) as bank_a_rate,
       MAX (CASE WHEN "type" = 'Bank B' THEN rate ELSE null END) as bank_b_rate
 from rates
 group by currency, ts
) t
window w as (partition by currency order by ts);

SQL小提琴