我在snowflake写一个查询，从不同的表和视图中获取数据，然后想在一个视图中组合输出，以便其他人可以访问它

Question

CREATE TEMPORARY TABLE sales AS
SELECT
PRODUCT_SKU,
sum(GROSS_SALES - GROSS_RETURNS - COUPON_DISCOUNT) total_sales_ttm,
sum(GROSS_MARGIN) as gross_margin_ttm,
avg(COST_OF_GOODS_SOLD - COST_OF_RETURNS) as avg_cost_ttm,
sum(QUANTITY_SOLD - QUANTITY_RETURNED) as unit_sold_ttm
FROM TABLE1 
WHERE
TRAN_DATE BETWEEN (CURRENT_DATE -365) AND CURRENT_DATE
GROUP BY 1;
CREATE TEMPORARY TABLE q_sales AS
SELECT 
PRODUCT_SKU,
sum(GROSS_SALES_AMOUNT - GROSS_RETURNS_AMOUNT) q_total_sales_ttm,
sum(GROSS_MARGIN) as q_gross_margin_ttm,
avg(COST_OF_GOODS_SOLD - COST_OF_RETURNS) as q_avg_cost_ttm,
sum(QUANTITY_SOLD - QUANTITY_RETURNED) as q_unit_sold_ttm
FROM TABLE2 
WHERE
TRAN_DATE BETWEEN (CURRENT_DATE -365) AND CURRENT_DATE
GROUP BY 1;
CREATE TEMPORARY TABLE prices AS
SELECT 
rp.SKU, 
rp.PRICE AS RETAIL_PRICE,
bp.PRICE AS BASELINE_PRICE
FROM TABLE3 rp
LEFT JOIN TABLE4 bp 
ON rp.SKU=bp.SKU
WHERE
rp.history=1 AND bp.history=1;

//将临时表合并成一个查询

SELECT
sales.PRODUCT_SKU, 
total_sales_ttm, gross_margin_ttm, avg_cost_ttm,
q_total_sales_ttm, q_gross_margin_ttm, q_avg_cost_ttm,
retail_price,baseline_price
FROM
sales
LEFT JOIN q_sales
ON sales.PRODUCT_SKU = q_sales.PRODUCT_SKU
LEFT JOIN prices
ON sales.PRODUCT_SKU=prices.SKU

Answer 1

如果我创建一些测试数据表:

CREATE TABLE sales AS
SELECT
1 as PRODUCT_SKU,
10 as total_sales_ttm,
11 as gross_margin_ttm,
5 as avg_cost_ttm,
4 as unit_sold_ttm
;
CREATE TABLE q_sales AS
SELECT 
1 as PRODUCT_SKU,
12 as q_total_sales_ttm,
13 as q_gross_margin_ttm,
14 as q_avg_cost_ttm,
15 as q_unit_sold_ttm
;
CREATE TEMPORARY TABLE prices AS
SELECT 
1 as SKU, 
42 AS RETAIL_PRICE,
43 AS BASELINE_PRICE

你的选择:

SELECT
sales.PRODUCT_SKU, 
total_sales_ttm, gross_margin_ttm, avg_cost_ttm,
q_total_sales_ttm, q_gross_margin_ttm, q_avg_cost_ttm,
retail_price,baseline_price
FROM
sales
LEFT JOIN q_sales
ON sales.PRODUCT_SKU = q_sales.PRODUCT_SKU
LEFT JOIN prices
ON sales.PRODUCT_SKU=prices.SKU;

给:

tbody> <<tr>

PRODUCT_SKU	TOTAL_SALES_TTM	AVG_COST_TTM	Q_TOTAL_SALES_TTM	Q_GROSS_MARGIN_TTM	Q_AVG_COST_TTM	q_avg_margin_ttm	RETAIL_PRICE	BASELINE_PRICE
1	10	11	5	12	13	14	42	43

您可能希望使用临时表而不是临时表，因为临时表的作用域在会话内。

您可以将这些视图/表合并成一个视图，并拥有对最终视图具有SELECT访问权限的Role，这样具有角色的用户对视图具有只读访问权限。

如果你能进一步说明你的问题，可以帮助更好。