帮我简化一下这个where条件
(
rule.start_price is null or
(case when (COALESCE(rule.performance_type,'') = '' or rule.performance_type = '1') then NVL(sku.promotion_price, 0) >= NVL(rule.start_price, 0)
when rule.performance_type in ('2','3') then NVL(sku.zq_promotion_price, 0) >= NVL(rule.start_price, 0)
else 1=1 end)
)
AND (
rule.end_price is null or
(case when (COALESCE(rule.performance_type,'') = '' or rule.performance_type = '1') then NVL(sku.promotion_price, 0) <= NVL(rule.end_price, 100000000)
when rule.performance_type in ('2','3') then NVL(sku.zq_promotion_price, 0) <= NVL(rule.end_price, 100000000)
else 1=1 end)
)
AND (
rule.start_discount is null or
(
case when (COALESCE(rule.performance_type,'') = '' or rule.performance_type = '1') then sku.sku_discount >= NVL(rule.start_discount / 10, 0)
when rule.performance_type in ('2','3') then sku.zq_sku_discount >= NVL(rule.start_discount / 10, 0)
else 1=1 end
)
)
AND (
rule.end_discount is null or
(
case when (COALESCE(rule.performance_type,'') = '' or rule.performance_type = '1') then sku.sku_discount <= NVL(rule.end_discount / 10, 0)
when rule.performance_type in ('2','3') then sku.zq_sku_discount <= NVL(rule.end_discount / 10, 0)
else 1=1 end
)
)
AND (rule.brand_ids is null or array_contains(split(rule.brand_ids, ','), sku.brand_code))
AND (rule.store_cate2s is null or array_contains(split(rule.store_cate2s, ','), cast(sku.store_cat2 as string)))
AND
(
NVL(rule.meddle_type, 0) <> 2
or (NVL(rule.meddle_type, 0) = 2
and (rule.vender_ids is null or array_contains(split(rule.vender_ids, ','), sku.vender_id))
and (rule.store_ids is null or array_contains(split(rule.store_ids, ','), cast(sku.store_id as string)))
and (rule.sku_ids is null or array_contains(split(rule.sku_ids, ','), sku.sku_id))
)
)
AND ((rule.keywords is null or rule.keywords = '') or keywordsMatch(rule.keywords, sku.sku_name) = 1)
AND (rule.store_business_type is null or sku.store_business_type = rule.store_business_type)
AND (COALESCE(rule.store_channel_type, 0) = 0 or sku.store_channel_type = rule.store_channel_type)
AND (COALESCE(rule.rule_sku_tags, '') = '' or tagContain(sku.sku_tags, rule.rule_sku_tags) = '1')
AND (rule.start_price_star2 is null or NVL(sku.pricestar_2, 0) >= NVL(rule.start_price_star2, 0))
AND (rule.end_price_star2 is null or NVL(sku.pricestar_2, 0) <= NVL(rule.end_price_star2, 100000000))
AND (rule.store_delivery is null or rule.store_delivery ='' or (
(array_contains(rule.array_store_delivery,'0') and coalesce(sku.basic_money,-1) = 0) or
(array_contains(rule.array_store_delivery,'1') and coalesce(sku.basic_money,-1) <> 0 and sku.free_ship_flag = 1 and coalesce(sku.need_money,-1) >= coalesce(rule.start_delivery_threshold,100000000) and coalesce(sku.need_money,100000000)<= coalesce(rule.end_delivery_threshold,-1)) or
(array_contains(rule.array_store_delivery,'2') and coalesce(sku.basic_money,-1) <> 0 and sku.is_free_basic_money = 1 and coalesce(sku.need_money,-1) >= coalesce(rule.start_base_threshold,100000000) and coalesce(sku.need_money,100000000)<= coalesce(rule.end_base_threshold,-1))
))
--20240416 增加令牌编号圈选规则
AND (
(LENGTH(REGEXP_REPLACE(rule.token_codes,' ','')) = 0 OR rule.array_token_code is NULL)
or
(
--size(array_intersect(rule.array_token_code,sku.sku_token_code_list)) > 0
case when (COALESCE(rule.performance_type,'') = '' or rule.performance_type = '1') then size(array_intersect(rule.array_token_code,sku.sku_token_code_list)) > 0
when rule.performance_type in ('2','3') then size(array_intersect(rule.array_token_code,sku.zq_sku_token_code_list)) > 0
ELSE 1=1 END
)
)
--20240123 增加门店起送价、新人爆品、百补、秒杀、爆品促销活动规则
AND (
(LENGTH(REGEXP_REPLACE(rule.promo_tag,' ','')) = 0 OR rule.array_promotion_type is NULL or size(rule.array_promotion_type) = 0) or
(
CASE WHEN (COALESCE(rule.performance_type,'') = '' or rule.performance_type = '1') THEN
(CASE WHEN array_contains(rule.array_promotion_type,'1') THEN baopin_info is NOT NULL AND baopin_info != '' ELSE 1=1 END) AND
(CASE WHEN array_contains(rule.array_promotion_type,'2') THEN miaosha_info is NOT NULL AND miaosha_info != '' ELSE 1=1 END) AND
(CASE WHEN array_contains(rule.array_promotion_type,'3') THEN baibu_info is NOT NULL AND baibu_info != '' ELSE 1=1 END) AND
(CASE WHEN array_contains(rule.array_promotion_type,'4') THEN xinrenbaopin_info is NOT NULL AND xinrenbaopin_info != '' ELSE 1=1 END) AND
(CASE WHEN array_contains(rule.array_promotion_type,'5') THEN sku.godprice_info is NOT NULL AND sku.godprice_info != '' ELSE 1=1 END)
WHEN rule.performance_type in ('2','3') THEN
(CASE WHEN array_contains(rule.array_promotion_type,'1') THEN sku.zq_baopin_info is NOT NULL AND sku.zq_baopin_info != '' ELSE 1=1 END) AND
(CASE WHEN array_contains(rule.array_promotion_type,'2') THEN sku.zq_miaosha_info is NOT NULL AND sku.zq_miaosha_info != '' ELSE 1=1 END) AND
(CASE WHEN array_contains(rule.array_promotion_type,'3') THEN sku.zq_baibu_info is NOT NULL AND sku.zq_baibu_info != '' ELSE 1=1 END) AND
(CASE WHEN array_contains(rule.array_promotion_type,'4') THEN sku.zq_xinrenbaopin_info is NOT NULL AND sku.zq_xinrenbaopin_info != '' ELSE 1=1 END) AND
(CASE WHEN array_contains(rule.array_promotion_type,'5') THEN sku.zq_godprice_info is NOT NULL AND sku.zq_godprice_info != '' ELSE 1=1 END)
ELSE 1=1 END
)
)
AND (
-- 商品价格和起送价对比关系，1:等于、2:小于、3:大于、4:小于或等于、5:大于或等于、6:大于或小于
case when store_start_price_relation = 1 then sku.promotion_price = sku.store_start_price
when store_start_price_relation = 2 then sku.promotion_price < sku.store_start_price
when store_start_price_relation = 3 then sku.promotion_price > sku.store_start_price
when store_start_price_relation = 4 then sku.promotion_price <= sku.store_start_price
when store_start_price_relation = 5 then sku.promotion_price >= sku.store_start_price
when store_start_price_relation = 6 then sku.promotion_price != sku.store_start_price
else 1=1 end
)
--支持仅圈选药急送门店商品
AND (
case when rule.origin_store_source = 12 THEN sku.store_business_type = 1 ELSE 1 = 1 END
)
--支持京东健康业务根据医保标签圈品
AND (
CASE WHEN rule.origin_store_source = 12 AND size(rule.array_jdh_tag_value)>0
THEN (size(sku.jdh_tag_value_list)>0
AND size(array_intersect(rule.array_jdh_tag_value,sku.jdh_tag_value_list)) = size(rule.array_jdh_tag_value)
)
ELSE 1=1 END
)
--过滤溢价商品
AND (
CASE WHEN rule.over_price_filter = 1 AND sku.store_business_type = 0 THEN sku.over_price_filter = 0 ELSE 1 = 1 END
)
AND (rule.upc_codes is null or array_contains(split(rule.upc_codes, ','), sku.upc_code))
AND (rule.spu_ids is null or array_contains(split(rule.spu_ids, ','), sku.spu_id))
--类目圈选满足其一即可 1、四级类目条件为空 2、只有四级类目条件 且符合 3、三级类目和四级类目条件都存在 其中一个满足就行
AND (
rule.item_fourth_cate_cds is null
OR
(rule.item_third_cate_cds is null AND rule.item_fourth_cate_cds is NOT NULL AND array_contains(split(rule.item_fourth_cate_cds, ','), sku.item_fourth_cate_cd))
OR
(
rule.item_third_cate_cds is NOT NULL AND rule.item_fourth_cate_cds is NOT NULL
AND (
array_contains(split(rule.item_fourth_cate_cds, ','), sku.item_fourth_cate_cd)
OR
array_contains(split(rule.item_third_cate_cds, ','), sku.item_third_cate_cd)
)
)
)
--如果没有通过sku干预 则同SPU下只取Top3的SKU
--AND (CASE WHEN rule.sku_ids is null THEN ((sku.sku_sort_no is NULL OR sku.sku_sort_no < 4)) ELSE 1=1 END)
AND (CASE WHEN COALESCE(rule.product_type,-99)=1 THEN sku.virtual_sku = 1 ELSE COALESCE(sku.virtual_sku,0) = 0 END)
--根据履约类型圈品(仅对秒送正常商品生效) 圈选兑换券商品时，需要同时关注商品和门店的履约方式
AND (
CASE WHEN COALESCE(rule.product_type,-99)=1 AND rule.dp = '1'
THEN array_contains(sku.store_delivery_types_array,performance_type) AND array_contains(sku.sku_performance_types_array,performance_type)
ELSE (
CASE WHEN performance_type IN ('1','2') AND rule.dp = '1' THEN array_contains(sku.store_delivery_types_array,performance_type)
WHEN performance_type = '3' AND rule.dp = '1' THEN
(array_contains(sku.store_delivery_types_array,performance_type) AND array_contains(sku.sku_performance_types_array,performance_type))
ELSE 1=1 END
) END
)
--对于loc圈品需求 需要区分业务线 并且支持过滤无划线价的商品
--选达达business_type = 1 选其他 business_type = 0 达达和其他都选 business_type[0,1] 达达和其他都不选 会默认business_type=1
AND (CASE WHEN (rule.dp = '13' AND rule.industry_type = '1') THEN sku.business_type = 1
WHEN (rule.dp = '13' AND rule.industry_type = '0') THEN sku.business_type = 0
ELSE 1=1 END)
AND (CASE WHEN rule.dp = '13' AND rule.line_price = 1 THEN sku.promotion_flag = 1 ELSE 1=1 END)
本文介绍了一个SQL查询案例，演示如何在特定条件下调整查询结果中的金钱数值。通过对原始数据进行条件判断和计算，实现了根据不同条件返回相应的金钱数值。
扫一扫
1179
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
