如何使用Amazon redshift正确编写此查询？

Question

我想要编写一个更新查询来更新表中列的计数，但我不知道如何实现它。我已经将其缩小到三个选项，但我仍然会遇到一些或其他问题。哪种方式和对它的查询是正确的？

update fact_spv_commissioned_lot
set sn_count = fact_spv_commissioned_lot.sn_count + 
(
  SELECT COUNT(*) FROM staging_serials s
  JOIN dim_md_company c ON (c.lsc_company_id = s.companyid)
  JOIN staging_product p ON (s.compositeproductcode = p.compositeproductcode)
  JOIN dim_packaging_level l ON (l.unit_of_measure = p.packaginguom)
  JOIN fact_spv_commissioned_lot f ON (f.sk_company_id = s.companyid)
  WHERE c.sk_company_id = f.sk_company_id
  AND s.lotnumber = f.lot_number
  AND p.sk_product_id = f.sk_product_id
  AND l.sk_packaging_level_id = f.sk_packaging_level_id
)

或者这是写它的正确方式？

update fact_spv_commissioned_lot
set sn_count = fact_spv_commissioned_lot.sn_count + 
(
  SELECT COUNT(*) FROM staging_serials s
  JOIN dim_md_company c ON (c.lsc_company_id = s.companyid)
  JOIN staging_product p ON (s.compositeproductcode = p.compositeproductcode)
  JOIN dim_packaging_level l ON (l.unit_of_measure = p.packaginguom)
  JOIN fact_spv_commissioned_lot f ON (f.sk_company_id = s.companyid)
  WHERE c.sk_company_id = f.sk_company_id
  AND s.lotnumber = f.lot_number
  AND p.sk_product_id = f.sk_product_id
  AND l.sk_packaging_level_id = f.sk_packaging_level_id
)
FROM staging_serials s
  JOIN dim_md_company c ON (c.lsc_company_id = s.companyid)
  JOIN staging_product p ON (s.compositeproductcode = p.compositeproductcode)
  JOIN dim_packaging_level l ON (l.unit_of_measure = p.packaginguom)
  JOIN fact_spv_commissioned_lot f ON (f.sk_company_id = s.companyid)
  WHERE c.sk_company_id = f.sk_company_id
  AND s.lotnumber = f.lot_number
  AND p.sk_product_id = f.sk_product_id
  AND l.sk_packaging_level_id = f.sk_packaging_level_id

或者这是写它的正确方式？

update fact_spv_commissioned_lot
set sn_count = fact_spv_commissioned_lot.sn_count + 
(
  SELECT COUNT(*) FROM staging_serials s
  JOIN dim_md_company c ON (c.lsc_company_id = s.companyid)
  JOIN staging_product p ON (s.compositeproductcode = p.compositeproductcode)
  JOIN dim_packaging_level l ON (l.unit_of_measure = p.packaginguom)
  JOIN fact_spv_commissioned_lot f ON (f.sk_company_id = s.companyid)
)
  WHERE c.sk_company_id = f.sk_company_id
  AND s.lotnumber = f.lot_number
  AND p.sk_product_id = f.sk_product_id
  AND l.sk_packaging_level_id = f.sk_packaging_level_id

Answer 1

我个人很喜欢CTE's，但是您的第一个查询就快到了。

CTE版本如下(请将<pk-col>替换为实际的主键列)：

WITH
    agg_data (pk, count) AS (
        SELECT f.<pk-col>, COUNT(*)
        FROM staging_serials s
            JOIN dim_md_company c ON (c.lsc_company_id = s.companyid)
            JOIN staging_product p ON (s.compositeproductcode = p.compositeproductcode)
            JOIN dim_packaging_level l ON (l.unit_of_measure = p.packaginguom)
            JOIN fact_spv_commissioned_lot f ON (f.sk_company_id = s.companyid)
        WHERE c.sk_company_id = f.sk_company_id
            AND s.lotnumber = f.lot_number
            AND p.sk_product_id = f.sk_product_id
            AND l.sk_packaging_level_id = f.sk_packaging_level_id
        GROUP BY 1
    )
UPDATE fact_spv_commissioned_lot AS to_update
SET sn_count = sn_count + agg_data.count
FROM agg_data WHERE agg_data.pk = to_update.<pk-col>;

作为替代方法，您还可以使用子选择中与表fact_spv_commissioned_lot相关的原始联接列，以在删除JOIN (f)的情况下构成关联，例如：

WITH
    agg_data (sk_company_id, lot_number, sk_product_id, sk_packaging_level_id, count) AS (
        SELECT f.sk_company_id, f.lot_number, f.sk_product_id, f.sk_packaging_level_id, COUNT(*)
        FROM staging_serials s
            JOIN dim_md_company c ON (c.lsc_company_id = s.companyid)
            JOIN staging_product p ON (s.compositeproductcode = p.compositeproductcode)
            JOIN dim_packaging_level l ON (l.unit_of_measure = p.packaginguom)
            JOIN fact_spv_commissioned_lot f ON (f.sk_company_id = s.companyid)
        WHERE c.sk_company_id = f.sk_company_id
            AND s.lotnumber = f.lot_number
            AND p.sk_product_id = f.sk_product_id
            AND l.sk_packaging_level_id = f.sk_packaging_level_id
        GROUP BY 1, 2, 3, 4
    )
UPDATE fact_spv_commissioned_lot AS to_update
SET sn_count = sn_count + agg_data.count
FROM agg_data
WHERE agg_data.sk_company_id = to_update.sk_company_id
    AND agg_data.lot_number = to_update.lot_number
    AND agg_data.sk_product_id = to_update.sk_product_id
    AND agg_data.sk_packaging_level_id = to_update.sk_packaging_level_id
;

...or用子选择风格完全缩短了一点：

UPDATE fact_spv_commissioned_lot AS to_update
SET sn_count = sn_count + (
    SELECT COUNT(*)
    FROM staging_serials s
        JOIN dim_md_company c ON (c.lsc_company_id = s.companyid)
        JOIN staging_product p ON (s.compositeproductcode = p.compositeproductcode)
        JOIN dim_packaging_level l ON (l.unit_of_measure = p.packaginguom)
    WHERE s.companyid = to_update.sk_company_id
        AND s.lotnumber = to_update.lot_number
        AND c.sk_company_id = to_update.sk_company_id
        AND p.sk_product_id = to_update.sk_product_id
        AND l.sk_packaging_level_id = to_update.sk_packaging_level_id
);

如果您的表是中型到大型(数百万到数十亿行)，CTE版本也应该执行得更好(特别是使用主键列的第一个变体)，尽管在SQL中有点冗长。