我有以下代码:
WITH
SO_Req_ AS
(
SELECT
SO_REQ.SALESMAN AS Sales_Person,
SO_REQ.SALES_CAT AS Sales_Category,
SO_REQ.SO_REQ_ID AS SO_Req__ID,
SO_REQ.SO_REQ_NO AS SO_Req__No_,
SO_REQ.ORDER_PLACED_BY AS Order_Placed_By
FROM
ITIMS.SO_REQ SO_REQ
LEFT OUTER JOIN ITIMS.APP_LINK APP_LINK
ON SO_REQ.APP_LINK_ID = APP_LINK.APP_LINK_ID
FULL OUTER JOIN ITIMS.TIMS_APP TIMS_APP
ON TIMS_APP.TIMS_APP_ID = APP_LINK.SRC_TIMS_APP_ID
),
PROJECT__CR_ AS
(
SELECT DISTINCT
PRJ.PROJECT_ID,
PRJ.PROJECT_CD,
PRJ.PROJECT_DESC,
'Y' AS ITIMS_PROJECT,
ACTIVE_TIMS_SITE_FLAG
FROM
ITIMS.PROJECT PRJ,
ITIMS.SITE_USER SU
WHERE
PRJ.PROJECT_ID = SU.PROJECT_ID
),
CR_Project AS
(
SELECT
PROJECT__CR_.PROJECT_CD AS Project_Code,
PROJECT__CR_.PROJECT_ID AS Project_ID
FROM
PROJECT__CR_
),
CR_OCTG_Hdr__Adjustment AS
(
SELECT
CR_ADJ.CR_ADJ_ID AS CR_Adjustment_ID,
CR_ADJ.TAX_FLAG AS Tax_Flag,
CR_ADJ.USD_RATE AS Credit_Rate__USD_,
CR_ADJ.UOM AS Quantity_UOM,
CR_ADJ.PROCESS_STATE AS CR_Adjustment_Status,
CR_ADJ.QTY AS Quantity,
CR_ADJ.ADJ_SIGN AS Adjustment_Sign,
CR_ADJ.COST_RATE AS Cost_Rate,
APP_LINK.SRC_REF_2 AS Source_Ref2
FROM
ITIMS.TIMS_APP TIMS_APP
LEFT OUTER JOIN
ITIMS.APP_LINK APP_LINK
INNER JOIN ITIMS.CR_ADJ CR_ADJ
ON APP_LINK.APP_LINK_ID = CR_ADJ.APP_LINK_ID
ON TIMS_APP.TIMS_APP_ID = APP_LINK.SRC_TIMS_APP_ID
LEFT OUTER JOIN ITIMS.UOM_CONV UOM_CONV__FT___CR_ADJ_
ON
CR_ADJ.UOM = UOM_CONV__FT___CR_ADJ_.UOM_FROM AND
UOM_CONV__FT___CR_ADJ_.UOM_TO = 'FT'
LEFT OUTER JOIN ITIMS.UOM_CONV UOM_CONV__M___CR_ADJ_
ON
CR_ADJ.UOM = UOM_CONV__M___CR_ADJ_.UOM_FROM AND
UOM_CONV__M___CR_ADJ_.UOM_TO = 'M'
WHERE
APP_LINK.SRC_TIMS_APP_ID = 743
),
CR_Req__Bill_To AS
(
SELECT
BIZENT.BIZENT_GRP_CD AS Bill_to_Bizent_Group_Code,
BIZENT.BIZENT_GRP_NAME AS Bill_to_Bizent_Group_Name,
BIZENT.BIZENT_CD AS Bill_to_Bizent_Code,
BIZENT.BIZENT_NAME AS Bill_to_Bizent_Name,
BIZENT.OUTSIDE_SALESMAN AS Outside_Salesman,
BIZENT.BIZENT_ID AS Bill_to_Bizent_ID
FROM
ITIMS.BIZENT_GRP BIZENT_GRP
LEFT OUTER JOIN ITIMS.BIZENT BIZENT
ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID
),
CR_Req__Customer AS
(
SELECT
BIZENT.BIZENT_CD AS Customer_Bizent_Code,
BIZENT.BIZENT_NAME AS Customer_Bizent_Name,
BIZENT.BIZENT_ID AS Customer_Bizent_ID
FROM
ITIMS.BIZENT_GRP BIZENT_GRP
LEFT OUTER JOIN ITIMS.BIZENT BIZENT
ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID
)
SELECT
'CR' AS SRC,
'ADJ - OCTG HDR' AS ITEM_CLASS,
CR_Req_.PROJECT_ID,
CR_Project.Project_Code,
CR_Req_.CR_ID,
CR_OCTG_Hdr__Adjustment.CR_Adjustment_ID,
CR_Req__Bill_To.Bill_to_Bizent_Group_Code,
CR_Req__Bill_To.Bill_to_Bizent_Group_Name,
CR_Req__Customer.Customer_Bizent_Code,
CR_Req__Customer.Customer_Bizent_Name,
CR_Req__Bill_To.Bill_to_Bizent_Code,
CR_Req__Bill_To.Bill_to_Bizent_Name,
SO_Req_.Sales_Person,
SO_Req_.Sales_Category,
CR_Req_.CR_NO,
CR_Req_.DATE_CREATED,
CR_Req_.ACCT_PERIOD,
SO_Req_.SO_Req__ID,
SO_Req_.SO_Req__No_,
SO_Req_.Order_Placed_By,
CR_Req_.DISC_TAKEN_FLAG,
CR_Req_.AFE_NO,
CR_Req__Bill_To.Outside_Salesman,
CR_Req_.RETURN_DATE,
CR_OCTG_Hdr__Adjustment.Tax_Flag,
CR_OCTG_Hdr__Adjustment.Credit_Rate__USD_,
CR_OCTG_Hdr__Adjustment.Quantity_UOM,
CR_OCTG_Hdr__Adjustment.Quantity,
CR_OCTG_Hdr__Adjustment.Adjustment_Sign,
ifnull((CR_OCTG_Hdr__Adjustment.Cost_Rate * CR_OCTG_Hdr__Adjustment.Quantity) * CASE
WHEN CR_OCTG_Hdr__Adjustment.Adjustment_Sign LIKE 'A' THEN -1
ELSE 1
END, 0) COST_USD
FROM
SO_Req_
LEFT OUTER JOIN ITIMS.INVC Invoice_Req_
ON SO_Req_.SO_Req__ID = Invoice_Req_.SO_REQ_ID
LEFT OUTER JOIN ITIMS.CR CR_Req_
ON Invoice_Req_.INVC_ID = CR_Req_.INVC_ID
FULL OUTER JOIN CR_Project
ON CR_Project.Project_ID = CR_Req_.PROJECT_ID
LEFT OUTER JOIN ITIMS.CR_OCTG_HDR CR_OCTG_Hdr_
ON CR_Req_.CR_ID = CR_OCTG_Hdr_.CR_ID
LEFT OUTER JOIN CR_OCTG_Hdr__Adjustment
ON CR_OCTG_Hdr_.CR_OCTG_HDR_ID = CR_OCTG_Hdr__Adjustment.Source_Ref2
FULL OUTER JOIN CR_Req__Bill_To
ON CR_Req__Bill_To.Bill_to_Bizent_ID = CR_Req_.BILLTO_BIZENT_ID
FULL OUTER JOIN CR_Req__Customer
ON CR_Req__Customer.Customer_Bizent_ID = CR_Req_.CUST_BIZENT_ID
WHERE
NOT ( CR_OCTG_Hdr__Adjustment.CR_Adjustment_ID IS NULL ) AND
NOT ( CR_OCTG_Hdr__Adjustment.CR_Adjustment_Status LIKE 'X' ) AND
NOT ( CR_Req_.PROCESS_STATE LIKE 'X' )
这里的问题是，如果有一种方法来摆脱使用WITH子句创建的所有cte(公共表表达式)，并获得相同的结果?此外，它会对性能产生一些有益的影响吗?
我一直在尝试做这个任务，但是当我试图打开这个cte时，我遇到了麻烦。
非常感谢你的帮助，真的很感激!
所以简单的答案是肯定的!CTE提供了两个函数来分割内容，并允许以表达式的形式选择公共表。
WITH cte_name AS (
SELECT struff
FROM tables
)
SELECT stuff
FROM tables
JOIN cte_name
等于:
SELECT stuff
FROM tables
JOIN (
SELECT struff
FROM tables
)
这只是一个剪切-粘贴。
现在如果您多次使用CTE表，那么在大多数DB中，该公共子表达式仅求值一次。在Snowflake中，如果读IO比完成CTE工作所需的CPU慢，那么这可能会减慢速度，两个并行读可能会读得更少，因此运行得更快。和所有事情一样，测试。
所以对于你的代码，你所有的CTE都可以展开，这对速度没有任何影响。我相信这会让你的代码更难以读懂。
所以从我将如何重构你现有的代码开始:
WITH so_req_ AS (
SELECT
s.salesman AS sales_person,
s.sales_cat AS sales_category,
s.so_req_id AS so_req__id,
s.so_req_no AS so_req__no_,
s.order_placed_by AS order_placed_by
FROM itims.so_req AS s
LEFT OUTER JOIN itims.app_link as a
ON s.app_link_id = a.app_link_id
FULL OUTER JOIN itims.tims_app AS t
ON t.tims_app_id = a.src_tims_app_id
), project__cr_ AS (
SELECT DISTINCT
prj.project_id,
prj.project_cd,
prj.project_desc,
'Y' AS itims_project,
active_tims_site_flag
FROM itims.project AS prj
CROSS JOIN itims.site_user AS su
WHERE prj.project_id = su.project_id
), cr_project AS (
SELECT
p.project_cd AS project_code,
p.project_id AS project_id
FROM project__cr_ AS p
), cr_octg_hdr__adjustment AS (
SELECT
c.cr_adj_id AS cr_adjustment_id,
c.tax_flag AS tax_flag,
c.usd_rate AS credit_rate__usd_,
c.uom AS quantity_uom,
c.process_state AS cr_adjustment_status,
c.qty AS quantity,
c.adj_sign AS adjustment_sign,
c.cost_rate AS cost_rate,
al.src_ref_2 AS source_ref2
FROM itims.tims_app AS t
LEFT OUTER JOIN itims.app_link AS al
INNER JOIN itims.cr_adj AS c
ON al.app_link_id = c.app_link_id
ON t.tims_app_id = al.src_tims_app_id
LEFT OUTER JOIN itims.uom_conv AS u1
ON c.uom = u1.uom_from
AND u1.uom_to = 'FT'
LEFT OUTER JOIN itims.uom_conv AS u2
ON c.uom = u2.uom_from
AND u2.uom_to = 'M'
WHERE
al.src_tims_app_id = 743
), cr_req__bill_to AS (
SELECT
BIZENT.BIZENT_GRP_CD AS Bill_to_Bizent_Group_Code,
BIZENT.BIZENT_GRP_NAME AS Bill_to_Bizent_Group_Name,
BIZENT.BIZENT_CD AS Bill_to_Bizent_Code,
BIZENT.BIZENT_NAME AS Bill_to_Bizent_Name,
BIZENT.OUTSIDE_SALESMAN AS Outside_Salesman,
BIZENT.BIZENT_ID AS Bill_to_Bizent_ID
FROM
ITIMS.BIZENT_GRP BIZENT_GRP
LEFT OUTER JOIN ITIMS.BIZENT BIZENT
ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID
), cr_req__customer AS (
SELECT
BIZENT.BIZENT_CD AS Customer_Bizent_Code,
BIZENT.BIZENT_NAME AS Customer_Bizent_Name,
BIZENT.BIZENT_ID AS Customer_Bizent_ID
FROM
ITIMS.BIZENT_GRP BIZENT_GRP
LEFT OUTER JOIN ITIMS.BIZENT BIZENT
ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID
)
SELECT
'CR' AS SRC,
'ADJ - OCTG HDR' AS ITEM_CLASS,
CR_Req_.PROJECT_ID,
CR_Project.Project_Code,
CR_Req_.CR_ID,
CR_OCTG_Hdr__Adjustment.CR_Adjustment_ID,
CR_Req__Bill_To.Bill_to_Bizent_Group_Code,
CR_Req__Bill_To.Bill_to_Bizent_Group_Name,
CR_Req__Customer.Customer_Bizent_Code,
CR_Req__Customer.Customer_Bizent_Name,
CR_Req__Bill_To.Bill_to_Bizent_Code,
CR_Req__Bill_To.Bill_to_Bizent_Name,
s.Sales_Person,
s.Sales_Category,
CR_Req_.CR_NO,
CR_Req_.DATE_CREATED,
CR_Req_.ACCT_PERIOD,
s.SO_Req__ID,
s.SO_Req__No_,
s.Order_Placed_By,
CR_Req_.DISC_TAKEN_FLAG,
CR_Req_.AFE_NO,
CR_Req__Bill_To.Outside_Salesman,
CR_Req_.RETURN_DATE,
CR_OCTG_Hdr__Adjustment.Tax_Flag,
CR_OCTG_Hdr__Adjustment.Credit_Rate__USD_,
CR_OCTG_Hdr__Adjustment.Quantity_UOM,
CR_OCTG_Hdr__Adjustment.Quantity,
CR_OCTG_Hdr__Adjustment.Adjustment_Sign,
ifnull((CR_OCTG_Hdr__Adjustment.Cost_Rate * CR_OCTG_Hdr__Adjustment.Quantity) * CASE
WHEN CR_OCTG_Hdr__Adjustment.Adjustment_Sign LIKE 'A' THEN -1
ELSE 1
END, 0) COST_USD
FROM so_req_ AS s
LEFT OUTER JOIN itims.invc AS invoice_req_
ON s.so_req__id = invoice_req_.so_req_id
LEFT OUTER JOIN itims.cr AS cr_req_
ON Invoice_Req_.INVC_ID = CR_Req_.INVC_ID
FULL OUTER JOIN CR_Project
ON CR_Project.Project_ID = CR_Req_.PROJECT_ID
LEFT OUTER JOIN ITIMS.CR_OCTG_HDR CR_OCTG_Hdr_
ON CR_Req_.CR_ID = CR_OCTG_Hdr_.CR_ID
LEFT OUTER JOIN CR_OCTG_Hdr__Adjustment
ON CR_OCTG_Hdr_.CR_OCTG_HDR_ID = CR_OCTG_Hdr__Adjustment.Source_Ref2
FULL OUTER JOIN CR_Req__Bill_To
ON CR_Req__Bill_To.Bill_to_Bizent_ID = CR_Req_.BILLTO_BIZENT_ID
FULL OUTER JOIN CR_Req__Customer
ON CR_Req__Customer.Customer_Bizent_ID = CR_Req_.CUST_BIZENT_ID
WHERE
NOT ( CR_OCTG_Hdr__Adjustment.CR_Adjustment_ID IS NULL ) AND
NOT ( CR_OCTG_Hdr__Adjustment.CR_Adjustment_Status LIKE 'X' ) AND
NOT ( CR_Req_.PROCESS_STATE LIKE 'X' )
变成了(我必须多混叠一些东西):
SELECT
'CR' AS SRC,
'ADJ - OCTG HDR' AS ITEM_CLASS,
CR_Req_.PROJECT_ID,
cp.Project_Code,
CR_Req_.CR_ID,
cha.CR_Adjustment_ID,
cbt.Bill_to_Bizent_Group_Code,
cbt.Bill_to_Bizent_Group_Name,
rc.Customer_Bizent_Code,
rc.Customer_Bizent_Name,
cbt.Bill_to_Bizent_Code,
cbt.Bill_to_Bizent_Name,
s.Sales_Person,
s.Sales_Category,
CR_Req_.CR_NO,
CR_Req_.DATE_CREATED,
CR_Req_.ACCT_PERIOD,
s.SO_Req__ID,
s.SO_Req__No_,
s.Order_Placed_By,
CR_Req_.DISC_TAKEN_FLAG,
CR_Req_.AFE_NO,
cbt.Outside_Salesman,
CR_Req_.RETURN_DATE,
cha.Tax_Flag,
cha.Credit_Rate__USD_,
cha.Quantity_UOM,
cha.Quantity,
cha.Adjustment_Sign,
ifnull((cha.Cost_Rate * cha.Quantity) * CASE
WHEN cha.Adjustment_Sign LIKE 'A' THEN -1
ELSE 1
END, 0) COST_USD
FROM (
SELECT
s.salesman AS sales_person,
s.sales_cat AS sales_category,
s.so_req_id AS so_req__id,
s.so_req_no AS so_req__no_,
s.order_placed_by AS order_placed_by
FROM itims.so_req AS s
LEFT OUTER JOIN itims.app_link as a
ON s.app_link_id = a.app_link_id
FULL OUTER JOIN itims.tims_app AS t
ON t.tims_app_id = a.src_tims_app_id
) AS s
LEFT OUTER JOIN itims.invc AS invoice_req_
ON s.so_req__id = invoice_req_.so_req_id
LEFT OUTER JOIN itims.cr AS cr_req_
ON Invoice_Req_.INVC_ID = CR_Req_.INVC_ID
FULL OUTER JOIN (
SELECT
p.project_cd AS project_code,
p.project_id AS project_id
FROM (
SELECT DISTINCT
prj.project_id,
prj.project_cd,
prj.project_desc,
'Y' AS itims_project,
active_tims_site_flag
FROM itims.project AS prj
CROSS JOIN itims.site_user AS su
WHERE prj.project_id = su.project_id
) AS p
) as cp
ON cp.Project_ID = CR_Req_.PROJECT_ID
LEFT OUTER JOIN ITIMS.CR_OCTG_HDR CR_OCTG_Hdr_
ON CR_Req_.CR_ID = CR_OCTG_Hdr_.CR_ID
LEFT OUTER JOIN (
SELECT
c.cr_adj_id AS cr_adjustment_id,
c.tax_flag AS tax_flag,
c.usd_rate AS credit_rate__usd_,
c.uom AS quantity_uom,
c.process_state AS cr_adjustment_status,
c.qty AS quantity,
c.adj_sign AS adjustment_sign,
c.cost_rate AS cost_rate,
al.src_ref_2 AS source_ref2
FROM itims.tims_app AS t
LEFT OUTER JOIN itims.app_link AS al
INNER JOIN itims.cr_adj AS c
ON al.app_link_id = c.app_link_id
ON t.tims_app_id = al.src_tims_app_id
LEFT OUTER JOIN itims.uom_conv AS u1
ON c.uom = u1.uom_from
AND u1.uom_to = 'FT'
LEFT OUTER JOIN itims.uom_conv AS u2
ON c.uom = u2.uom_from
AND u2.uom_to = 'M'
WHERE
al.src_tims_app_id = 743
) as cha
ON CR_OCTG_Hdr_.CR_OCTG_HDR_ID = cha.Source_Ref2
FULL OUTER JOIN (
SELECT
BIZENT.BIZENT_GRP_CD AS Bill_to_Bizent_Group_Code,
BIZENT.BIZENT_GRP_NAME AS Bill_to_Bizent_Group_Name,
BIZENT.BIZENT_CD AS Bill_to_Bizent_Code,
BIZENT.BIZENT_NAME AS Bill_to_Bizent_Name,
BIZENT.OUTSIDE_SALESMAN AS Outside_Salesman,
BIZENT.BIZENT_ID AS Bill_to_Bizent_ID
FROM
ITIMS.BIZENT_GRP BIZENT_GRP
LEFT OUTER JOIN ITIMS.BIZENT BIZENT
ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID
) as cbt
ON cbt.Bill_to_Bizent_ID = CR_Req_.BILLTO_BIZENT_ID
FULL OUTER JOIN (
SELECT
BIZENT.BIZENT_CD AS Customer_Bizent_Code,
BIZENT.BIZENT_NAME AS Customer_Bizent_Name,
BIZENT.BIZENT_ID AS Customer_Bizent_ID
FROM
ITIMS.BIZENT_GRP BIZENT_GRP
LEFT OUTER JOIN ITIMS.BIZENT BIZENT
ON BIZENT_GRP.BIZENT_GRP_ID = BIZENT.BIZENT_GRP_ID
) as rc
ON rc.Customer_Bizent_ID = CR_Req_.CUST_BIZENT_ID
WHERE
NOT ( cha.CR_Adjustment_ID IS NULL ) AND
NOT ( cha.CR_Adjustment_Status LIKE 'X' ) AND
NOT ( CR_Req_.PROCESS_STATE LIKE 'X' )
可读性差很多。并且会有完全相同的性能