雪花加入性能改进

Question

我需要在包含1300+列的表的顶部创建一个视图。每季度都会将新数据加载到表中(行数以百万计(。在创建视图时，我需要将其他表和基表连接起来。我还需要在视图中添加一个最近的行指示符。

CREATE OR REPLACE SECURE VIEW VIEW_NAME AS
SELECT lkp_tbl.col1,base_tbl.col1,base_tbl.col2,base_tbl.col3,........,
base_tbl.col1334, 1 as Is_Latest_Quarter 
FROM base_tbl full outer JOIN lkp_tbl
on base_tbl.CUST_ID = lkp_tbl.CUST_ID 
where snapshot_dt=(select max(snapshot_dt) from base_tbl)

union all

SELECT lkp_tbl.col1,base_tbl.col1,base_tbl.col2,base_tbl.col3,........,
base_tbl.col1334,0 as Is_Latest_Quarter 
FROM base_tbl full outer JOIN lkp_tbl 
on base_tbl.CUST_ID = lkp_tbl.CUST_ID 
where snapshot_dt!=(select max(snapshot_dt) from base_tbl);

创建此视图后，即使查询100行，查询的性能也太慢。有没有一种方法可以让我们以更有效的方式创建视图。如果没有，我该如何提高性能？

Answer 1

只需使用一个SELECT语句和一个CASE语句来计算Is_Latest_Quarter

用(几乎(实际SQL更新

CREATE OR REPLACE SECURE VIEW VIEW_NAME AS
SELECT {list of columns you want to include}
,CASE WHEN snapshot_dt=(select max(snapshot_dt) from base_tbl) THEN 1 
ELSE 0 END as Is_Latest_Quarter
FROM base_tbl 
full outer JOIN lkp_tbl on base_tbl.CUST_ID = lkp_tbl.CUST_ID 

或者，如果Snowflake不喜欢内联子查询，您可以使用CTE，比如：

CREATE OR REPLACE SECURE VIEW VIEW_NAME AS
WITH MAX_DATE AS (SELECT MAX(Ssnapshot_dt) AS max_snapshot_dt FROM base_tbl),
SELECT {list of columns you want to include}
,CASE WHEN max_date.max_snapshot_dt is not null  THEN 1 
ELSE 0 END as Is_Latest_Quarter
FROM base_tbl 
full outer JOIN lkp_tbl on base_tbl.CUST_ID = lkp_tbl.CUST_ID
LEFT OUTER JOIN MAX_DATE ON base_tbl.snapshot_dt = max_date.max_snapshot_dt