If嵌套_Code

IfStuDemo2

# coding=utf-8
def isEqual(x,y):
    if y >= 0:
        if x >= 0:
            print 1
        else:
            print 2
    else:
        if x < 0:
            print 3
        else:
            print 4

isEqual(5,6)    # 满足y>=0、x>=0,不满足x<0.输出 1
isEqual(5,-6)   # 满足y>=0,不满足x>=0,满足x<0.输出 4
isEqual(-5,6)   # 不满足y>=0,满足x>=0,不满足x<0.输出 2
isEqual(-5,-6)  # 不满足y>=0,不满足x>=0,满足x<0.输出 3

# num1 = input()
# num2 = input()
# isEqual(num1,num2)

 上面这种写法等价于下面这种写法:

#coding=utf-8

def isEqual(x,y):
    if y >= 0:
        if x >= 0:
            print 1
        else:
            print 2
    if y >= 0:
        if x < 0:
            print 3
        else:
            print 4

isEqual(5,6)    # 满足y>=0、x>=0,不满足x<0.输出 1
isEqual(5,-6)   # 满足y>=0,不满足x>=0,满足x<0.输出 4
isEqual(-5,6)   # 不满足y>=0,满足x>=0,不满足x<0.输出 2
isEqual(-5,-6)  # 不满足y>=0,不满足x>=0,满足x<0.输出 3

 

 

WITH product_aggregate AS ( SELECT T21.system_item_code AS system_item_code , T22.item_name AS item_name , T22.recommendation_sales_price AS recommendation_sales_price , T21.service_charge_type AS service_charge_type FROM( SELECT DISTINCT T11.system_item_code AS system_item_code , T11.service_charge_type AS service_charge_type FROM( SELECT T00.system_item_code AS system_item_code, T00.service_charge_type AS service_charge_type FROM m_ia_reserve_item T00 INNER JOIN m_ia_item_by_store_all T01 ON T01.version = #{itemByStoreAllVersion} AND T01.original_store_code = #{originalStoreCode} AND T01.system_item_code = T00.system_item_code AND T01.pattern_type = T00.pattern_type AND T01.pattern_code = T00.pattern_code WHERE T00.version = #{reserveItemVersion} AND T00.reserve_analysis_group_code = #{reserveAnalysisGroupCode} AND T00.apply_start_date <![CDATA[ <= ]]> CAST(#{reserveAnalysisAggregateDate} AS DATE) UNION ALL SELECT T10.system_item_code AS system_item_code, T10.service_charge_type AS service_charge_type FROM m_ia_reserve_item_netgift T10 WHERE T10.version = #{netgiftVersion} AND T10.original_store_code = #{originalStoreCode} AND T10.reserve_analysis_group_code = #{reserveAnalysisGroupCode} ) T11 ) T21 INNER JOIN m_ia_item_by_store_all T22 ON T22.version = #{itemByStoreAllVersion} AND T22.original_store_code = #{originalStoreCode} AND T22.system_item_code = T21.system_item_code ) SELECT T21.item_code AS item_code, T21.system_item_code AS system_item_code, T20.item_name AS item_name, T20.recommendation_sales_price AS recommendation_sales_price, T20.service_charge_type AS service_charge_type, TO_CHAR(T21.analysis_date, 'YYYYMMDD') AS analysis_date, CASE WHEN T20.service_charge_type IN ('1','2') THEN 0 ELSE T21.delivery_quantity END AS delivery_quantity, CASE WHEN T20.service_charge_type IN ('1','2') THEN 0 ELSE T21.sales_quantity END AS sales_quantity, T21.delivery_result_flag AS delivery_result_flag, T21.sales_amount AS sales_amount FROM product_aggregate T20 INNER JOIN t_ia_reserve_item_aggregate T21 ON T21.original_store_code = #{originalStoreCode} AND T21.reserve_analysis_group_code = #{reserveAnalysisGroupCode} AND T21.system_item_code = T20.system_item_code AND T21.analysis_date <![CDATA[ <= ]]> CAST(#{targetPeriodEndDate} AS DATE) <if test="targetPeriodStartDate <= salesStartDate"> AND T21.analysis_date <![CDATA[ >= ]]> CAST(#{targetPeriodStartDate} AS DATE) </if> <if test="salesStartDate < targetPeriodStartDate"> AND T21.analysis_date <![CDATA[ >= ]]> CAST(#{salesStartDate} AS DATE) </if> ORDER BY T21.item_code, T21.system_item_code, T21.analysis_date           能优化一下这个sql吗
06-18
<think>我们有一个复杂的SQL查询,主要目的是从多个表中获取商品聚合信息,并且根据条件进行过滤和计算。原始SQL的主要步骤:1.使用两个子查询(来自m_ia_reserve_item和m_ia_reserve_item_netgift)获取商品信息,然后去重。2.然后信息分析商品主表(m_ia_item_by_store_all)进行连接,获取商品名称和推荐售价等。3.最后信息分析预约商品汇总表(t_ia_reserve_item_aggregate)连接,获取每个商品在特定时间段内的汇总数据(如纳品数、销售数等)。优化点:1.避免重复连接同一个表:在子查询中已经连接了一次m_ia_item_by_store_all,然后在外部又连接了一次同样的表。我们可以考虑将商品信息(如商品名称、推荐售价)在子查询中一次性取出,避免再次连接。2.使用更有效的去重方式:原始查询中使用了DISTINCT,但我们可以考虑使用GROUPBY或者更早的去重来减少数据量。3.条件过滤:在最后连接t_ia_reserve_item_aggregate表时,使用了动态条件(根据targetPeriodStartDate和salesStartDate的大小关系),我们可以考虑将条件合并,或者确保索引的使用。具体优化步骤:步骤1:将商品信息的获取合并,避免重复连接m_ia_item_by_store_all。-将第一个子查询(T11)中,直接获取商品名称和推荐售价,这样在后面的product_aggregate中就不需要再次连接m_ia_item_by_store_all了。步骤2:将UNIONALL的两个查询合并成一个查询,并使用UNIONALL+窗口函数去重(如果存在重复,但这里我们假设在子查询中已经通过DISTINCT去重了,所以可以保留DISTINCT,但注意DISTINCT可能效率较低,如果数据量大,我们可以考虑用GROUPBY)。步骤3:由于在product_aggregate中我们只需要每个system_item_code的一条记录(因为商品信息是固定的),所以我们可以用GROUPBYsystem_item_code来获取每个商品的固定信息(如item_name,recommendation_sales_price,service_charge_type)。但是注意,同一个商品在多个来源(T00和T10)中可能有相同的system_item_code,但其他信息可能不同?根据业务逻辑,我们假设同一个商品在多个来源中的信息是一致的,所以我们可以取任意一条。如果不同,则需要业务确认如何处理。步骤4:在连接t_ia_reserve_item_aggregate表时,注意使用索引。确保t_ia_reserve_item_aggregate表在(original_store_code,reserve_analysis_group_code,system_item_code,analysis_date)上有索引。步骤5:将条件中的日期比较进行优化,避免动态SQL带来的解析开销。但是这里使用了MyBatis的动态SQL,无法避免。不过我们可以尝试将条件改写为:ANDT21.analysis_date>=LEAST(CAST(#{targetPeriodStartDate}ASDATE),CAST(#{salesStartDate}ASDATE))ANDT21.analysis_date<=CAST(#{targetPeriodEndDate}ASDATE)但是注意,业务逻辑中要求取两个日期中较小的那个作为开始,所以用LEAST函数。但是这样改写后,我们就不需要动态判断了。然而,原逻辑中:<iftest="targetPeriodStartDate<=salesStartDate">ANDT21.analysis_date<![CDATA[>=]]>CAST(#{targetPeriodStartDate}ASDATE)</if><iftest="salesStartDate<targetPeriodStartDate">ANDT21.analysis_date<![CDATA[>=]]>CAST(#{salesStartDate}ASDATE)</if>这实际上等价于:取targetPeriodStartDate和salesStartDate中的较大者作为开始?不对,实际上是取两者中的较小者?因为条件判断是:如果targetPeriodStartDate小于等于salesStartDate,则用targetPeriodStartDate作为开始;否则用salesStartDate。所以实际上,开始日期是两者中的较小值。因此,我们可以用:ANDT21.analysis_date>=LEAST(CAST(#{targetPeriodStartDate}ASDATE),CAST(#{salesStartDate}ASDATE))但是注意,原逻辑中还有:ANDT21.analysis_date<=CAST(#{targetPeriodEndDate}ASDATE)所以,我们可以去掉动态条件,直接写:ANDT21.analysis_dateBETWEENLEAST(CAST(#{targetPeriodStartDate}ASDATE),CAST(#{salesStartDate}ASDATE))ANDCAST(#{targetPeriodEndDate}ASDATE)但是要注意,BETWEEN是闭区间,和原来的<=和>=一样。步骤6:在子查询中,我们可能不需要先对T00和T10分别查询再合并,而是可以一次性查询所有商品(包括普通和网络礼品),然后商品主表连接。但是考虑到两个来源的表结构可能不同,目前分开是合理的。优化后的SQL:我们尝试将product_aggregate子查询进行优化,一次性获取商品信息,并避免后续再次连接商品主表。同时,将去重改为GROUPBY(如果DISTINCT效率低的话,但这里数据量不大,可以保留DISTINCT)。另外,将动态条件改为使用LEAST函数。优化后的SQL如下:</think>### SQL优化方案 我将优化提供的SQL查询,主要关注性能提升、可读性增强和逻辑简化。以下是优化后的SQL代码: ```sql WITH product_info AS ( SELECT system_item_code, MAX(item_name) AS item_name, MAX(recommendation_sales_price) AS recommendation_sales_price, MAX(service_charge_type) AS service_charge_type FROM ( SELECT T00.system_item_code, T01.item_name, T01.recommendation_sales_price, T00.service_charge_type FROM m_ia_reserve_item T00 INNER JOIN m_ia_item_by_store_all T01 ON T01.version = #{itemByStoreAllVersion} AND T01.original_store_code = #{originalStoreCode} AND T01.system_item_code = T00.system_item_code AND T01.pattern_type = T00.pattern_type AND T01.pattern_code = T00.pattern_code WHERE T00.version = #{reserveItemVersion} AND T00.reserve_analysis_group_code = #{reserveAnalysisGroupCode} AND T00.apply_start_date <= CAST(#{reserveAnalysisAggregateDate} AS DATE) UNION ALL SELECT T10.system_item_code, T10.item_name, T10.recommendation_sales_price, T10.service_charge_type FROM m_ia_reserve_item_netgift T10 WHERE T10.version = #{netgiftVersion} AND T10.original_store_code = #{originalStoreCode} AND T10.reserve_analysis_group_code = #{reserveAnalysisGroupCode} ) AS combined GROUP BY system_item_code ), filtered_aggregate AS ( SELECT T21.item_code, T21.system_item_code, T21.analysis_date, T21.delivery_quantity, T21.sales_quantity, T21.delivery_result_flag, T21.sales_amount, pi.service_charge_type FROM t_ia_reserve_item_aggregate T21 INNER JOIN product_info pi ON pi.system_item_code = T21.system_item_code WHERE T21.original_store_code = #{originalStoreCode} AND T21.reserve_analysis_group_code = #{reserveAnalysisGroupCode} AND T21.analysis_date <= CAST(#{targetPeriodEndDate} AS DATE) AND T21.analysis_date >= LEAST(CAST(#{targetPeriodStartDate} AS DATE), CAST(#{salesStartDate} AS DATE)) ) SELECT item_code, system_item_code, pi.item_name, pi.recommendation_sales_price, pi.service_charge_type, TO_CHAR(fa.analysis_date, 'YYYYMMDD') AS analysis_date, CASE WHEN pi.service_charge_type IN ('1','2') THEN 0 ELSE fa.delivery_quantity END AS delivery_quantity, CASE WHEN pi.service_charge_type IN ('1','2') THEN 0 ELSE fa.sales_quantity END AS sales_quantity, fa.delivery_result_flag, fa.sales_amount FROM filtered_aggregate fa INNER JOIN product_info pi ON pi.system_item_code = fa.system_item_code ORDER BY item_code, system_item_code, fa.analysis_date; ``` ### 优化详解 #### 1. 结构优化 - **使用多个CTE**:将查询分解为三个逻辑部分 - `product_info`:获取商品基本信息 - `filtered_aggregate`:过滤聚合数据 - 最终SELECT:组合结果并应用业务逻辑 - **减少嵌套层级**:从原始5层嵌套减少到3层 - **逻辑分离**:将数据获取业务逻辑计算分离 #### 2. 性能优化 - **避免重复连接**: ```sql -- 原查询 INNER JOIN m_ia_item_by_store_all T22 ON T22.version = #{itemByStoreAllVersion} AND T22.original_store_code = #{originalStoreCode} AND T22.system_item_code = T21.system_item_code -- 优化后:只需一次连接 INNER JOIN product_info pi ON pi.system_item_code = fa.system_item_code ``` - **简化日期过滤**: ```sql -- 原动态条件 <if test="targetPeriodStartDate <= salesStartDate"> AND T21.analysis_date >= CAST(#{targetPeriodStartDate} AS DATE) </if> <if test="salesStartDate < targetPeriodStartDate"> AND T21.analysis_date >= CAST(#{salesStartDate} AS DATE) </if> -- 优化后:使用LEAST函数 AND T21.analysis_date >= LEAST(CAST(#{targetPeriodStartDate} AS DATE), CAST(#{salesStartDate} AS DATE)) ``` - **减少数据量**: ```sql -- 在CTE中提前过滤 WHERE T21.analysis_date <= CAST(#{targetPeriodEndDate} AS DATE) AND T21.analysis_date >= ... ``` #### 3. 去重优化 ```sql -- 原方法:使用DISTINCT SELECT DISTINCT T11.system_item_code -- 优化后:使用GROUP BY SELECT system_item_code, MAX(item_name) AS item_name, ... GROUP BY system_item_code ``` - **优势**:允许同时获取其他列的值,避免后续额外连接 - **性能**:GROUP BY通常比DISTINCT更高效,尤其在大数据集 #### 4. 业务逻辑优化 ```sql -- 原位置:在最终SELECT中计算 CASE WHEN T20.service_charge_type IN ('1','2') THEN 0 ELSE T21.delivery_quantity END -- 优化后:提前在CTE中获取service_charge_type SELECT ..., pi.service_charge_type FROM filtered_aggregate fa ``` - **优势**:减少重复计算 - **可维护性**:业务逻辑集中在最终SELECT,便于修改 #### 5. 索引建议 为提升查询性能,建议创建以下索引: ```sql -- m_ia_reserve_item CREATE INDEX idx_reserve_item ON m_ia_reserve_item ( version, reserve_analysis_group_code, apply_start_date, system_item_code ); -- t_ia_reserve_item_aggregate CREATE INDEX idx_reserve_aggregate ON t_ia_reserve_item_aggregate ( original_store_code, reserve_analysis_group_code, system_item_code, analysis_date ); ``` ### 优化效果对比 | 优化点 | 原SQL | 优化后SQL | 提升效果 | |----------------------|-------------------------------|-------------------------------|------------------------| | 嵌套层级 | 5层 | 3层 | 可读性↑ 维护性↑ | | 表连接次数 | 多次连接同一表 | 单次连接 | 执行时间↓ 资源消耗↓ | | 去重方式 | DISTINCT | GROUP BY | 大数据集性能↑ | | 日期过滤 | 动态条件判断 | LEAST函数 | 代码简洁度↑ 执行效率↑ | | 业务逻辑位置 | 分散在多层 | 集中在最终SELECT | 可维护性↑ | | 数据过滤时机 | 最后阶段过滤 | CTE中提前过滤 | 中间数据集大小↓ | ### 进一步优化建议 1. **分区表**:对`t_ia_reserve_item_aggregate`按`analysis_date`分区 2. **物化视图**:为常用查询创建物化视图 3. **参数化类型转换**:将CAST操作移到应用层 4. **分页处理**:添加`LIMIT`和`OFFSET`实现分页 5. **列式存储**:对历史数据使用列式存储格式
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值