-- 成本异常
with tmp as (
select
id,
brand_name 品牌,
cat_no 货号,
package_value 规格,
normalize_package(package_value) mg,
round("cost",2) 成本,
price 市场价,
price_expiry_date 价格有效期,
lag(normalize_package(package_value), 1) OVER(w) 上一个mg,
lag(cost, 1) OVER(w) 上一个成本,
lead(normalize_package(package_value), 1) OVER(w) 下一个mg,
lead(cost, 1) OVER(w) 下一个成本,
case when lag(cost, 1) OVER(w) > cost then 1
else 0 end
成本异常

FROM t_product_package pkg

where pkg.brand_name ilike 'cato'
and cost is not null
and is_delete = 1
and price_expiry_date > now()

window w as (PARTITION BY brand_name, cat_no ORDER BY normalize_package(package_value))
),

tmp2 as (
SELECT
*,
max(成本异常) OVER(PARTITION BY 品牌, 货号) 产品成本异常
FROM tmp
)

SELECT
*
FROM tmp2

where 产品成本异常 = 1

ORDER BY 品牌, 货号, mg;

-- 价格异常
with tmp as (
select
id,
brand_name 品牌,
cat_no 货号,
package_value 规格,
normalize_package(package_value) mg,
round("cost",2) 成本,
price 市场价,
price_expiry_date 价格有效期,
lag(normalize_package(package_value), 1) OVER(w) 上一个mg,
lag(price, 1) OVER(w) 上一个价格,
lead(normalize_package(package_value), 1) OVER(w) 下一个mg,
lead(price, 1) OVER(w) 下一个价格,
case when lag(price, 1) OVER(w) > price then 1
else 0 end
市场价异常

FROM t_product_package pkg
where pkg.brand_name ilike 'cato'
and price is not null
and is_delete = 1
and price_expiry_date > now()

window w as (PARTITION BY brand_name, cat_no ORDER BY normalize_package(package_value))
),

tmp2 as (
SELECT
*,
max(市场价异常) OVER(PARTITION BY 品牌, 货号) 产品价格异常
FROM tmp
)

SELECT
*
FROM tmp2

where 产品价格异常 = 1

ORDER BY 品牌, 货号, mg;


-- 货期异常
with tmp as (
select
id,
brand_name 品牌,
cat_no 货号,
package_value 规格,
normalize_package(package_value) mg,
round("cost",2) 成本,
price 市场价,
delivery 货期,
substring(normalize_delivery(delivery) from '(\d+)天') "货期(天)",
price_expiry_date 价格有效期,
lag(normalize_package(package_value), 1) OVER(w) 上一个mg,
lag(substring(normalize_delivery(delivery) from '(\d+)天'), 1) OVER(w) 上一个货期,
lead(normalize_package(package_value), 1) OVER(w) 下一个mg,
lead(substring(normalize_delivery(delivery) from '(\d+)天'), 1) OVER(w) 下一个货期,
case when lag(substring(normalize_delivery(delivery) from '(\d+)天'), 1) OVER(w) > substring(normalize_delivery(delivery) from '(\d+)天') then 1
else 0 end
货期异常

FROM t_product_package pkg

where pkg.brand_name ilike 'cato'
and (cost is not null
or price is not null)
and is_delete = 1
AND price_expiry_date > now()

window w as (PARTITION BY brand_name, cat_no ORDER BY normalize_package(package_value))
),

tmp2 as (
SELECT
*,
max(货期异常) OVER(PARTITION BY 品牌, 货号) 产品货期异常
FROM tmp
)

SELECT
*
FROM tmp2

where 产品货期异常 = 1

ORDER BY 品牌, 货号, mg