我有一个问题要为Postgresql解决
我首先生成一系列日期：
with t as(
SELECT
uuid_generate_v4() as generated_id,
date_trunc('day', dd):: date as generated_date,
'75e46430-2f19-c9fc-5c8a-1aeb423a7c84'::uuid as account_id
FROM generate_series
( '2019-04-01'::timestamp
, '2019-05-01'::timestamp
, '1 day'::interval) dd
)
然后我有另一个表[事务]，如下所示：
account_id | order_date | debit_amount | credit_amount
对于给定的帐户，transactions表每天可以有数千个条目。每笔交易要么是借方，要么是贷方，即如果是贷方，则贷方栏中会有一个值，对于特定交易，借方栏将为空。
我正试图设计一个超快速查询，它给出了以下结果：
generated_id | account_id | order_date | sum(total_of debits_for_order_date) | sumtotal_of_credits_for_order_date)
哪里：
order_date = generated_date
t.account_id = transations.account_id
如果某个日期的借方或贷方为空，我仍然需要返回一个空行。
例如
generated_id | account_id | generated_date | debit | credit
fjsda-klf... 75e46430... 2019-01-01 1.50 null
gassd-fsd... 75e46430... 2019-01-02 null null
最佳答案
查询！
with t as(
SELECT
uuid_generate_v4() as generated_id,
date_trunc('day', dd):: date as generated_date,
'75e46430-2f19-c9fc-5c8a-1aeb423a7c84'::uuid as account_id
FROM generate_series
( '2019-04-01'::timestamp
, '2019-05-01'::timestamp
, '1 day'::interval) dd
), tdata as (
select a.account_id, a.order_date, sum(debit_amount) as debit, sum(credit_amount) as credit
from transations a
where exists (
select 1 from t where t.generated_date = a.order_date and t.account_id = a.account_id
)
select t.generated_id, t.account_id, t.generated_date, m.debit, m.credit
from t left join tdata m on (t.generated_date = m.order_date and t.account_id = m.account_id)
关于sql - 需要超快速的Postgres SQL查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58176607/