PostgreSQL -计算卖出计数的机构数量

Question

我们有一个名为" holdings "的表。它包含了每个季度金融机构持有公司股份的数量。

包含以下列:

tbody> <<tr>21

institutional _id	symbol	shares	quarter
1	apple	0	2020 _q4
	厂商	200000	2020 _q4
	apple	100000	2020 _q3

我会使用聚合。获取机构本身:

select symbol, institution, sum(shares) as q3_shares
from hodings
where quarter in ('2020_Q3', '2020_Q4') and
symbol = 'AAPL'
group by symbol, institution
having sum(shares) filter (where quarter = '2020_Q3') = sum(shares) and
sum(shares) > 0;

having子句的逻辑是:"所有股份都在'2020_Q3'中，并且至少有一个股份"。

如果你想要总计，第二级聚合:

select symbol, count(*), sum(q3_shares)
from (select symbol, institution, sum(shares) as q3_shares
from hodings
where quarter in ('2020_Q3', '2020_Q4') and
symbol = 'AAPL'
group by symbol, institution
having sum(shares) filter (where quarter = '2020_Q3') = sum(shares) and
sum(shares) > 0
) si;

通常EXISTS的性能优于LEFT:

SELECT COUNT(*) AS "soldCount",
SUM(q3.shares) AS "soldShares" 
FROM holdings q3
WHERE q3.quarter='2020_Q3' AND q3.symbol='AAPL' AND q3.shares>0
AND NOT EXISTS (
SELECT 1 
FROM holdings q4 
WHERE q4.institution_id=q3.institution_id
AND q4.quarter='2020_Q4' AND q4.symbol='AAPL'
AND q4.shares<>0
)

你必须以另一种方式使用窗口函数:

select * from (
select 
holdings.*,
-- use lead for retrieve previous quarter shares 
lead(shares) over (partition by institution_id, symbol order by quarter desc) previous_quarter
from holdings
where symbol = 'AAPL'
) copmarsion 
-- limit records where shares less when previous_quarter
where shares < coalesce(previous_quarter, 0);

PostgeSQL小提琴

SELECT COUNT(*) AS "soldCount",SUM(T1.shares) as "soldShares" FROM holdings T1
LEFT JOIN holdings T2 ON T1.institution_id = T2.institution_id
WHERE (T1.quarter = '2020_Q3' AND T1.symbol = 'AAPL') OR (T2.quarter = '2020_Q4' AND T2.symbol = 'AAPL')
AND T1.shares > 0  AND (T2.shares = 0 OR T2.shares IS NULL)