在 SQL 中從 3 個視圖創建一個視圖 (Creating a view from 3 views in SQL)

問題描述

在 SQL 中從 3 個視圖創建一個視圖 (Creating a view from 3 views in SQL)

我有 3 個視圖：

v_sessions、v_clickouts 和 v_sales

我正在嘗試根據這些規則將所有內容加入一個視圖：

1. v_session.session_id = v_clickouts.clickout_id
2. v_clickouts.clickout_id = v_sales.clickout_id

我可以使用'LEFT JOIN'來做到這一點：

LEFT JOIN v_clickouts clickouts ON 
    sessions.session_id = clickouts.session_id 
    AND sessions.user_id = clickouts.user_id 
    AND sessions.click_type = clickouts.click_type 
    AND sessions.click_id = clickouts.click_id 
    AND sessions.cid = clickouts.cid
    AND sessions.project::text = clickouts.project::text 
    AND sessions.event_time::date <= clickouts.event_time::date
LEFT JOIN v_sales sales ON 
    clickouts.clickout_id = sales.clickout_id 
    AND sessions.user_id = sales.user_id
    AND clickouts.project::text = sales.project::text 
    AND clickouts.event_time::date <= sales.event_time::date
;

通過附加“AND”條件，由於某些條件不匹配，我會從右側丟失數據。我只想保留兩個條件：

LEFT JOIN v_clickouts clickouts ON 
    sessions.session_id = clickouts.session_id 
LEFT JOIN v_sales sales ON 
    clickouts.clickout_id = sales.clickout_id 
;

並將所有“AND”條件移動到“CASE”語句，該語句將為每個“AND”創建一個新列，因此視圖可以有其他列喜歡：

session_user_id_equals_clickouts_user_id    session_click_type_equals_clickouts_click_type  clickouts_event_time_is_less_than_sales_event_time
TRUE                                        TRUE                                            TRUE
FALSE                                       TRUE                                            FALSE
TRUE                                        TRUE                                            FALSE

我不' 不知道如何通過使用“案例”方法來實現這一目標。

感謝您的建議。

編輯

示例數據 ‑ 我在第一次加入 v_sessions 和 v_clickouts 後得到它：

session_event_time  session_id  user_id     cid     click_type_from_session click_id    clickout_event_time click_type_from_session     clickout_id       
2019‑12‑18 11:21    id_1        user_id_1   cid_1   type_1                  click_id_1  2019‑12‑18 11:21    type_1                      clickout_id_1     
2019‑12‑18 18:35    id_2        user_id_2   cid_2   type_1                  click_id_2  2019‑12‑18 18:38    type_1                      clickout_id_2     

這兩個視圖僅由session_id連接，我想有一個CASE方法來確定例如：

sessions.click_type = clickouts.click_type as 1
sessions.click_id = clickouts.click_id as 2
sessions.cid = clickouts.cid as 3
sessions.project::text = clickouts.project::text as 4

這將導致在最終視圖中有 4 個額外的列（我已將新列命名為 1、2、3、4 的較短名稱）：

session_event_time  session_id  user_id     cid     click_type_from_session click_id    clickout_event_time click_type_from_session     clickout_id       1       2       3       4
2019‑12‑18 11:21    id_1        user_id_1   cid_1   type_1                  click_id_1  2019‑12‑18 11:21    type_1                      clickout_id_1     TRUE    FALSE   TRUE    TRUE
2019‑12‑18 18:35    id_2        user_id_2   cid_2   type_1                  click_id_2  2019‑12‑18 18:38    type_1                      clickout_id_2     FALSE   TRUE    TRUE    FALSE

因為我不是顯示我正在檢查條件的所有參數，我只需要說明 TRUE 或 的列

LEFT JOIN v_clickouts clickouts ON 
    sessions.session_id = clickouts.session_id 
    AND sessions.user_id = clickouts.user_id 
    AND sessions.click_type = clickouts.click_type 
    AND sessions.click_id = clickouts.click_id 
    AND sessions.cid = clickouts.cid
    AND sessions.project::text = clickouts.project::text 
    AND sessions.event_time::date <= clickouts.event_time::date
LEFT JOIN v_sales sales ON 
    clickouts.clickout_id = sales.clickout_id 
    AND sessions.user_id = sales.user_id
    AND clickouts.project::text = sales.project::text 
    AND clickouts.event_time::date <= sales.event_time::date
;