使用多个排列优化查询(使用Postgresql 11)
我试图找出通过给定输入标准的每一个排列过滤结果集的最优解。我们在AWS RDS上使用Postgresql 11
我创建了一个sql 这里，它概述了我正在处理的模式。下面是该问题中示例数据的副本。
在本例中，我们在某些“证券”中使用“投资者”和“持有”(头寸)。证券有三个我们关心的属性：
- 市场帽
- 扇区
- 国家/地区
我希望能够过滤投资者在这三个标准中的一个或多个。例如，我想问这样一个问题：“告诉我那些专注于小型股、加拿大公司、矿业公司的投资者”，而只有那些持有与这些属性相匹配的证券的投资者。或者，“让我看看那些专注于小盘股、中型股和大盘股的投资者”。
对于这些属性，我希望能够通过一个或多个值来查询，我希望看到持有这些类型证券的所有排列的投资者。所以，“给我看看小盘股、中型股、加拿大公司、美国公司、材料公司的投资者”意味着：
让我看看投资者：
- 至少持有小盘，加拿大，材料和
- 至少持有中盖，加拿大，材料和
- 至少有一家公司持有小型股，美国公司，材料公司和
- 至少有一次持有中盖，美国，材料
我想出的天真的解决方案是这样的：
SELECT * from investors i
-- small-cap, canadian, materials holdings
WHERE EXISTS (
SELECT 1 FROM holdings h
JOIN securities s on h.security_id = s.id
WHERE s.market_cap = 'SM'
AND s.country = 'CA'
AND s.sector = 'materials'
AND h.investor_id = i.id
)
-- mid-cap, canadian, materials holdings
AND EXISTS (...)
-- and so-on for each permutation of the criteria虽然这是可行的，但它绝对不是可伸缩的。我很肯定有办法改善这种情况，所以它的成本不是指数，但这个公式在这一刻我不知道。
我们的系统拥有数以亿计的股份给成千上万的投资者，所以这个天真的解决方案根本不会扩大规模。
能不能在这里指出正确的方向，从而得到一个最优解，并避免这个天真的解决方案会导致？
的指数子选择。
样本数据
证券
| id | name | sector | market_cap | country |
|----+--------------+-------------+------------+---------|
| 1 | 'Mining ABC' | 'materials' | 'SM' | 'CA' |
| 2 | 'SilverFox' | 'materials' | 'MD' | 'CA' |
| 3 | 'Big Coppa' | 'materials' | 'LG' | 'CA' |
| 4 | 'Golds R Us' | 'materials' | 'LG' | 'US' |
| 5 | 'Weedly' | 'pharma' | 'SM' | 'CA' |
| 6 | 'HazeMaker' | 'pharma' | 'MD' | 'US' |
| 7 | 'StickyIcky' | 'pharma' | 'LG' | 'US' |Investors
| id | name |
|----+--------|
| 11 | 'john' |
| 22 | 'bill' |
| 33 | 'susan'|
| 44 | 'jill' |控股
| security_id | investor_id | shares |
|-------------+-------------+--------|
| 5 | 11 | 1 | -- john, small-cap canadian pharma
| 7 | 11 | 12 | -- john, large-cap american pharma
| 2 | 11 | 13 | -- john, mid-cap canadian materials
| 3 | 11 | 514 | -- john, large-cap canadian materials
| 7 | 22 | 15 | -- bill, large-cap american pharma
| 5 | 22 | 16 | -- bill, small-cap canadian pharma
| 1 | 22 | 117 | -- bill, small-cap canadian materials
| 2 | 33 | 18 | -- susan, mid-cap canadian materials
| 3 | 33 | 919 | -- susan, large-cap canadian materials
| 4 | 33 | 20 | -- susan, large-cap american materials
| 1 | 44 | 21 | -- jill, small-cap canadian materials
| 3 | 44 | 22 | -- jill, large-cap canadian materials
| 4 | 44 | 123 | -- jill, large-cap american materials
| 5 | 44 | 456 | -- jill, small-cap canadian pharma
| 6 | 44 | 20 | -- jill, mid-cap american pharma
| 7 | 44 | 3 | -- jill, large-cap american pharma回答 1
Database Administration用户
发布于 2019-10-31 01:21:23
如果它们必须各自拥有一个，那么当仅根据这3种属性进行区分时，它们拥有的不同资产的总数必须等于存在的“排列”数：
select investor_id from holdings h join securities s on (h.security_id = s.id)
where market_cap in ('SM', 'MD') and country in ('CA','US') and sector in ('materials')
group by investor_id
having count(distinct (market_cap, country, sector)) = 2*2*1这可能不是很棒，但可能比你最初的想法更好。
https://dba.stackexchange.com/questions/252251
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号