如何优化这个查询从两个列检索一个特定的结构?

Question

有两个表(发票、联系人)，如果发票小计+增值税的总金额大于3000，则该查询检索按季度分组的发票总额的联系人列表。

结果结构示例:

联系人姓名|联系人cif |合计(小计+增值税)|季度

然而，我认为这个查询没有优化，可以更短，所以我的问题是，有没有办法让它变得更好?

下面是SQL文件和查询

http://sqlfiddle.com/!17/29449/3

select 
tc.id, 
tc.name, 
tc.cif, 
sum(ti.subtotal) + sum(ti.vat) as Total, 
extract(
quarter 
from 
ti.date
) as quarter 
from 
(
select 
tc.id, 
tc.cif, 
sum(ti.subtotal) + sum(ti.vat) as total 
from 
invoice ti 
inner join contact tc on ti.contactId = tc.id 
where 
ti.isBlocked = FALSE 
and cast(ti.date as Date) between '2020-01-01' 
AND '2020-12-31' 
group by 
tc.id, 
tc.cif 
having 
sum(ti.subtotal) + sum(ti.vat) > 3000 
order by 
tc.cif asc
) as source 
inner join contact tc on tc.id = source.id 
inner join invoice ti on ti.contactId = tc.id 
where 
source.total > 3000 
and cast(ti.date as Date) between '2020-01-01' 
AND '2020-12-31' 
and ti.isBlocked = FALSE 
group by 
tc.id, 
tc.cif, 
tc.name, 
quarter 
having 
sum(ti.subtotal) + sum(ti.vat) > 0 
order by 
tc.cif asc

Answer 1

我倾向于将子查询写成CTE，因为这样更容易理解，如果在子查询中过度指定想要的内容，可能会导致重复读取。

http://sqlfiddle.com/!17/29449/35

WITH
Source
--Retrieve all Customers with NonBlocked Invoices with a Total of >3000
AS
(
select
tc.id
from
invoice ti
inner join contact tc on ti.contactId = tc.id
where 
ti.isBlocked = FALSE
and cast(ti.date as Date) between '2020-01-01' 
AND '2020-12-31'
group by 
tc.id, 
tc.cif
having 
sum(ti.subtotal) + sum(ti.vat) > 3000
)
--Retrieve all Related Invoices
select
tc.id,
tc.name,
tc.cif,
sum(ti.subtotal) + sum(ti.vat) as Total,
extract(
quarter 
from 
ti.date
) as quarter
from
source
inner join contact tc on tc.id = source.id
inner join invoice ti on ti.contactId = tc.id
where 
1=1
group by 
tc.id, 
tc.cif, 
tc.name, 
quarter
Having 
sum(ti.subtotal) + sum(ti.vat) > 0 
order by 
tc.cif asc