優化具有許多排列的查詢（使用 Postgresql 11）

我試圖找出通過給定輸入標準的每個排列過濾結果集的最佳解決方案。我們在 AWS RDS 上使用 Postgresql 11

我在這裡創建了一個 sql fiddle ，概述了我正在處理的架構。以下是該問題中範例數據的副本。

在此範例中，我們使用“投資者”和某些“證券”中的“持股”（頭寸）。證券有我們關心的三個屬性：

1. 市值
2. 部門
3. 國家

我希望能夠根據這三個標準中的一個或多個篩選投資者。因此，例如，我想問一個問題，例如“向我展示專注於小型、加拿大、礦業公司的投資者”，並只獲得那些持有符合這些屬性的證券的投資者。或者，“向我展示專注於小盤股、中盤股和大盤股公司的投資者”

對於這些屬性中的每一個，我希望能夠通過一個或多個值進行查詢，並且我希望看到持有這些類型證券的所有排列的投資者。因此，“向我展示小型、中型、加拿大、美國、材料公司的投資者”的意思是：

向我展示投資者：

• 至少持有一家小型股，加拿大，材料和
• 至少持有加拿大中型股、材料股和
• 至少持有美國小盤股和材料股
• 至少持有中型美國材料股

我想出的天真的解決方案是：

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'    |

投資者

| 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

如果他們必須各有一個，那麼當區分僅基於這 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