亚马逊库存管理sql记录

WITH
  P AS (
    SELECT
      *
    FROM
      purchase_mapping
    WHERE
      platform = '亚马逊'
      AND country = '美国'
  ),
  product_data AS (
    SELECT
      sku,
      sum(ddc) ddc,
      sum(dcz) dcz,
      sum(fba) fba,
      sum(bfzt) bfzt,
      COUNT(*) AS sku_count
    FROM
      (
        SELECT
          s.product_name,
          s.asin,
          s.sku,
          s.total,
          s.ddc,
          s.dcz,
          s.FBA,
          s.bfzt,
          s.person,
          s.warehouse,
          p.platform,
          p.country,
          s.gxrq
        FROM
          product_sku s
          LEFT JOIN p ON s.person = p.buyer
        WHERE
          gxrq::date = (
            SELECT
              MAX(gxrq::date)
            FROM
              product_sku
          )
          AND s.warehouse NOT LIKE 'G%'
          AND s.warehouse NOT LIKE 'SA%'
          AND total != 0
        ORDER BY
          sku
      )
    WHERE
      sku NOT IN ('nan')
    GROUP BY
      sku
  ),
  base_data AS (
    SELECT
      k.*,
      p.platform,
      p.country
    FROM
      (
        SELECT
          c.*
        FROM
          (
            SELECT
              *,
              'put_instorage' AS SIGN
            FROM
              put_instorage
            UNION ALL
            SELECT
              *,
              'product_stock' AS SIGN
            FROM
              product_stock
          ) AS c
        WHERE
          gxrq::date = (
            SELECT
              MAX(gxrq::date)
            FROM
              put_instorage
          )
      ) k
      LEFT JOIN (
        SELECT
          *
        FROM
          purchase_mapping
        WHERE
          platform = '亚马逊'
          AND country = '美国'
      ) p ON k.buyer = p.buyer
    WHERE
      p.platform IS NOT NULL
  ),
  aggregated_data AS (
    SELECT
      product_name,
      platform,
      country,
      code,
      SUM(stocked) AS stocked,
      SUM(Unstocked_detail) AS Unstocked_detail,
      sum(mx) AS mx,
      STRING_AGG(
        CASE
          WHEN Unstocked_detail = 0 THEN PURCHASE_ORDER
        END,
        ', '
      ) AS shipped_orders,
      STRING_AGG(
        CASE
          WHEN Unstocked_detail > 0 THEN PURCHASE_ORDER
        END,
        ', '
      ) AS unshipped_orders
    FROM
      base_data
    GROUP BY
      product_name,
      platform,
      country,
      code
  ),
  t AS (
    SELECT
      s.product_name,
      s.platform,
      s.country,
      s.stocked,
      s.Unstocked_detail,
      s.mx,
      s.code,
      im.leading_sku,
      s.unshipped_orders,
      s.shipped_orders
    FROM
      aggregated_data s
      JOIN (
        SELECT
          *
        FROM
          inventory_mapping
        WHERE
          country = '美国'
      ) im ON im.sku = s.code
    ORDER BY
      s.code
  ),
  t1 AS (
    SELECT
      pd.sku,
      pd.sku_count,
      pd.FBA,
      pd.ddc,
      pd.dcz,
      pd.bfzt,
      t.code,
      t.stocked,
      t.Unstocked_detail,
      t.mx,
      t.unshipped_orders,
      t.shipped_orders,
      t.platform,
      t.country,
      t.product_name
    FROM
      product_data pd
      LEFT JOIN t ON pd.sku = t.leading_sku
    WHERE
      t.code IS NOT NULL
  )
SELECT
  t1.sku,
  t1.sku_count,
  t1.FBA + t1.ddc + t1.dcz + t1.bfzt + t1.mx AS totals,
  t1.FBA,
  t1.ddc,
  t1.dcz,
  t1.bfzt,
  t1.code,
  t1.stocked,
  t1.Unstocked_detail,
  t1.mx,
  '' "预计销售天数",
  '' "需备货量",
  t1.unshipped_orders,
  t1.shipped_orders,
  t1.platform,
  t1.country,
  t1.product_name,
  t2.Sale_7,
  t2.Sale_14,
  t2.Sale_30
FROM
  t1
  LEFT JOIN (
    SELECT
      sku,
      sum(sale_7) sale_7,
      sum(sale_14) sale_14,
      sum(sale_30) sale_30
    FROM
      (
        SELECT
          *
        FROM
          product_sale
        WHERE
          gxrq::date = (
            SELECT
              MAX(gxrq::date)
            FROM
              product_sku
          )
      )
    GROUP BY
      sku
  ) t2 ON t1.sku = t2.sku