需要超快的postgres SQL查询

Question

我正在尝试为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
)

然后我有另一个Transactions表，类似于：

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

Answer 1

查询。

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)