Postgresql
在同一個表上使用多個 JOIN 查詢的替代方法？
我有一個 Postgresql 11 數據庫。假設我有一張名為house的桌子。它應該有數十萬條記錄。
CREATE TABLE houses ( pkid serial primary key, address varchar(255) NOT NULL, rent float NOT NULL );現在，我的房子有我想在數據庫中註冊的功能。由於可能的功能列表會很長（幾十個）並且會隨著時間的推移而發展，因為我不想在表格房屋中添加一長串列並使用“ALTER TABLE”不斷更改表格，我想到了對這些功能有一個單獨的表：
CREATE TABLE house_features ( pkid serial primary key, house_pkid integer NOT NULL, feature_name varchar(255) NOT NULL, feature_value varchar(255) ); CREATE INDEX ON house_features (feature_name, feature_value); ALTER TABLE house_features ADD CONSTRAINT features_fk FOREIGN KEY (house_pkid) REFERENCES houses (pkid) ON DELETE CASCADE;平均而言，每條房屋記錄在house_features表中將有 10-20 條記錄。
到目前為止，這似乎是一個簡單有效的模型：我可以添加盡可能多的不同功能，控制上層（應用層和/或 GUI）中的feature_name和*feature_value的可能值。*每次應用程序發展時我都不必更改數據庫並且我需要一種新的功能類型。
例如，假設我具有以下功能：
- feature_name : ‘rooftype’ 可能的 feature_value : ‘flat’ 或 ‘inclined’
- feature_name : ‘wallcolors’ 可能的 feature_value : ‘white’, ‘beige’, ‘blue’, ‘green’, etc..（15 種不同的可能值）
- feature_name : ‘has_basement’ 可能的 feature_value : ‘True’ 或 ‘False’。
- feature_name : ’number_of_doors’ 和可能的 feature_value 任何整數編碼為字元串（所以 ‘0’, ‘1’, ‘2’, …）。
- feature_name : ‘floor_surface’ 可能的 feature_value 任何給定的浮點編碼為字元串（例如：‘155.2’）
顯然，將布爾值、整數和浮點數儲存為字元串不是很有效，這也是我需要注意的事情。我正在考慮為每種 XXX 類型（字元串、布爾值、浮點數、整數）創建一個單獨的house_features_XXX表。
但這甚至不是我的問題。
我的問題是：如何搜尋具有某些功能的房屋？
例如，假設我要搜尋具有地下室、白色牆壁和傾斜屋頂類型的房屋。我可以在應用層動態創建一個查詢，如：
SELECT sq1.* FROM ( SELECT house_pkid FROM house_features WHERE feature_name = 'has_basement' AND feature_value = 'True' ) AS sq1 JOIN ( SELECT house_pkid FROM house_features WHERE feature_name = 'wallcolors' AND feature_value = 'white' ) AS sq2 ON sq1.house_pkid = sq2.house_pkid JOIN ( SELECT house_pkid FROM house_features WHERE feature_name = 'rooftype' AND feature_value = 'inclined' ) AS sq3 ON sq1.house_pkid = sq3.house_pkid ;但這似乎不是那麼有效，尤其是考慮到 house_features 上可能有幾十個條件。
有一個更好的方法嗎 ？
您可以嘗試將特徵聚合為 JSON 值，然後搜尋多個特徵的組合非常容易：
select h.*, hf.features from houses join ( select house_id, jsonb_object_agg(feature_name, feature_value) as features from house_features group by house_id ) hf on hf.house_pkid = h.pkid where hf.features @> '{"rooftype": "flat", "has_basement", "true", "wallcolors": "white"}';可以通過向重複功能名稱的子選擇添加 WHERE 子句來提高性能，例如：
where feature_name in ('rooftype', 'has_basement', 'wallcolors')甚至
where (feature_name, feature_value) in (('rooftype', 'flat') ('has_basement', 'true'), ('wallcolors', 'white'))外部條件仍然是必要的，因為內部
where將包括不具備所有功能的房屋。這也有一個優勢（在我看來），您只能獲得所有功能的一排，而不是每個功能的一排。
除非您非常頻繁地刪除、添加和更改房屋的功能，否則將它們作為單個 JSONB 列儲存在
house表 (features) 上並刪除house_features表，可能是一種替代方法。在這種情況下，您可以在列上創建索引以加快搜尋速度。
所以，我跟隨在 Postgresql 中使用 crosstab 函式。這就是我得到的地方：
交叉表函式使我能夠獲得一組記錄，其中每個房子都有一個記錄，每個feature_name都有一個帶有 feature_value 的列：
SELECT * FROM crosstab ( ' SELECT house_pkid, feature_name, feature_value FROM house_features WHERE feature_name IN (''rooftype'',''wallcolors'',''has_basement'',''number_of_doors'',''floor_surface'') ORDER BY house_pkid, feature_name, feature_value ' , $$VALUES ('rooftype'), ('wallcolors'), ('has_basement'), ('number_of_doors'), ('floor_surface') $$ ) AS ct (house_pkid int, "rooftype" varchar, "wallcolors" varchar, "has_basement" varchar, "number_of_doors" varchar, "floor_surface" varchar) ;此查詢使我們能夠獲得一組記錄，例如：
house_pkid | rooftype | wallcolors | has_basement | number_of_doors | floor_surface ------------------------------------------------------------------------------------- 232 | inclined | beige | False | 2 | 90 234 | flat | white | False | 1 | 70我可以在這組記錄上做 SELECT。
請注意兩點：
- 僅當我還有其他不能出現在最終搜尋條件中的feature_name值時，才需要 WHERE 子句（這是我的情況，儘管我在原始消息中沒有提到它）。
- 也就是說，除了house_pkid之外，所有其他列都作為 varchar 返回，因為feature_value是 varchar。
現在，如果這可行並且不是太慢，在優化方面，我意識到我仍然可以改進：
- 首先，當一個 ETL 過程為數據庫提供數據時，我的數據並沒有太大變化，一年只有 3-4 次。其餘時間，houses和house_features表中的數據保持不變。所以，我決定最好將查詢轉換為 Posgresql MATERIALIZED VIEW。這樣，每次通過 ETL 重新載入house和*house_features表時，我只需重建 MATERIALIZED VIEW（並呼叫交叉表函式）一次。*在兩個 ETL 之間，MATERIALIZED VIEW 可以訪問結果，而無需在每次呼叫時處理交叉表函式。我什至可以像任何正常表一樣向 MATERIALIZED VIEW 添加索引，以使 SELECT 查詢更快。
- crosstab 呼叫為所有內容返回 varchar 類型的列，除了 house_pkid，但可以對它們進行轉換，以便我們擁有更充分和更有效的數據類型：而不是使用字元串“True”或“False”一個布爾值；相反，如果有字元串“90”，則有一個值為 90 的整數。
- 列house_features.feature_name將隨時間變化的可能值列表，如我的初始消息中所述，但在我的情況下，僅當應用層的新版本傳遞時，即當我也有 ETL 並將重建物化視圖。因此，我在我的 Python 應用層（執行 ETL）中編寫了一個函式，該函式基於一個元組列表為 MATERIALIZED VIEW 創建 PSQL 程式碼，該列表包含feature_name可能採用的每個值的名稱和 PSQL 類型，並且是我的一個搜尋條件。
這給出了：
from collections import namedtuple hf_tuple = namedtuple('house_searchable_features', ['fieldname', 'fieldtype']) searchablefeatures = [ hf_tuple(fieldname='rooftype', fieldtype='varchar'), hf_tuple(fieldname='wallcolors', fieldtype='varchar'), hf_tuple(fieldname='has_basement', fieldtype='boolean'), hf_tuple(fieldname='number_of_doors', fieldtype='integer'), hf_tuple(fieldname='floor_surface', fieldtype='float'), ] def create_searchablefeatures_query(): """ Creates the SQL query for re-creating the MATERIALIZED VIEW. """ query_sourcesql = 'SELECT house_pkid, feature_name, feature_value FROM house_features WHERE feature_name IN ( \n' query_sourcesql += ",\n".join(f" \t''{sf.fieldname}'' " for sf in searchablefeatures) query_sourcesql += ')\n ORDER BY house_pkid, feature_name, feature_value' query_categories = "$$VALUES \n" query_categories += ",\n".join(f"\t('{sf.fieldname}')" for sf in searchablefeatures) query_categories += "\n$$" query_output = '' query_output += ",\n".join(f'\t"{sf.fieldname}" varchar' for sf in searchablefeatures) query_transtyping = '' for sf in searchablefeatures: if sf.fieldtype == 'boolean': query_transtyping += f',\n\t("{sf.fieldname}" IS NOT NULL AND "{sf.fieldname}" != \'False\') AS "{sf.fieldname}"' elif sf.fieldtype == 'int' or sf.fieldtype == 'float': query_transtyping += f',\n\t"{sf.fieldname}"::{sf.fieldtype}' elif sf.fieldtype == 'varchar': query_transtyping += f',\n\t"{sf.fieldname}"' else: raise ValueError(f"unknown PSQL data type: {sf.fieldname}, {sf.fieldtype}") sql_def = f""" DROP MATERIALIZED VIEW IF EXISTS house_searchablefeatures CASCADE ; CREATE MATERIALIZED VIEW house_searchablefeatures AS SELECT house_pkid {query_transtyping} FROM ( SELECT * FROM crosstab( '\n{query_sourcesql}',\n {query_categories} \n) AS ct ( house_pkid int, \n{query_output} \n) ) AS b4transtyping ; """ return sql_def請注意，在hf_tuple中，fieldtype是 MATERIALIZED VIEW 中需要的 Postgresql 數據類型，而不是 Python 數據類型。另請注意，您可能必鬚根據數據庫內容調整query_transtyping的邏輯。
這不是一件容易的事，一些測試將證實它執行良好，但它看起來很健壯和高效。在維護方面，只需更新列表可搜尋功能並在每個 ETL 執行一次查詢似乎是可以接受的。
該函式使用 Python 3.8 執行。