SQL：按日期组和总和

Question

我在数据库中有下表：

date                 account_id  currency     balanceUSD
01-01-2022 17:17:25  1           USD          1000
01-01-2022 17:17:25  1           EUR          1200
01-01-2022 23:14:34  1           USD          1050
01-01-2022 23:14:34  1           EUR          1350
01-02-2022 15:14:42  1           USD          1040
01-02-2022 15:14:42  1           EUR          1460
01-02-2022 20:17:45  1           USD          1030
01-02-2022 20:17:45  1           EUR          1550
01-01-2022 17:17:25  2           USD          3000
01-01-2022 17:17:25  2           EUR          2300
01-01-2022 23:14:34  2           USD          3200
01-01-2022 23:14:34  2           EUR          1450
01-02-2022 15:14:42  2           USD          3350
01-02-2022 15:14:42  2           EUR          1850
01-02-2022 20:17:45  2           USD          3400
01-02-2022 20:17:45  2           EUR          1900

我想做的是按（年，月，日）和account_id组组，然后总和balanceusd。即

date         account_id     balanceUSD
01-01-2022   1              4600
01-02-2022   1              5080
01-01-2022   2              9950
01-02-2022   2              10500

如何做到这一点？

Answer 1

我们可以使用函数date_trunc('day',rental_date)从时间戳中提取日期。

SELECT 
  date_trunc('day', date) as "date",
  account_id,
  sum(balanceUSD) as "balanceUSD"
FROM 
  account_id,
  table_name
GROUP BY 
  account_id
  date_trunc('day', date) 
ORDER BY 
  account_id,
  date_trunc('day', date) ;