where substr(lot_id,1,2) not in('CA','CW','ES','E0','EM') and lot_type = 'Production' AND LOT_ID NOT LIKE 'H%' and substr(lot_id,7,4)='.00F' and ope_category in ('Ship','Unship')请优化下
时间: 2024-01-14 11:02:29 浏览: 29
根据给出的条件,可以考虑对表进行索引优化。
可以创建以下索引来优化查询:
1. (lot_type, ope_category, lot_id)
2. (ope_category, lot_type, lot_id)
3. (substr(lot_id, 1, 2), lot_type, lot_id, ope_category)
4. (substr(lot_id, 7, 4), lot_type, ope_category, lot_id)
通过以上索引的创建,可以提高查询效率。同时,也可以考虑对表进行分区操作,根据不同的 lot_type 进行分区,这样可以进一步提高查询效率。
相关问题
where substr(lot_id,1,2) not in('CA','CW','ES','E0','EM') and lot_type = 'Production' AND LOT_ID NOT LIKE 'H%' and substr(lot_id,7,4)='.00F' and ope_category in ('Ship','Unship')请优化下
该 SQL 语句可以优化如下:
1. 将 WHERE 子句中的函数调用改为使用列范围,例如:
```
WHERE lot_id >= 'F1000000.00F' AND lot_id < 'Z9999999.00G'
```
2. 创建索引以覆盖 WHERE 子句中的所有列,例如:
```
CREATE INDEX idx_lot_id ON table_name (lot_id);
CREATE INDEX idx_lot_type ON table_name (lot_type);
CREATE INDEX idx_ope_category ON table_name (ope_category);
```
3. 如果表比较大,可以考虑将这个表分区,例如按照 lot_type 进行分区,这样可以进一步提高查询性能。
4. 如果查询经常使用,则可以将结果缓存起来,以避免每次查询都进行全表扫描。
综上所述,这些优化措施可以显著提高 SQL 查询的性能。
select lot_id, max(claim_time)claim_time, count(case when(ope_category='Ship')then lot_id else null end) as LS, count(case when(ope_category='Unship') then lot_id else null end) as LUS from f3rpt.F3_TB_DAILY_FABOUT where substr(lot_id,1,2) not in('CA','CW','ES','E0','EM') and lot_type = 'Production' AND LOT_ID NOT LIKE 'H%' and substr(lot_id,7,4)='.00F' and ope_category in ('Ship','Unship') and year(claim_time) = year(current date - 1 days) and month(claim_time) = month(current date - 1 days) group by lot_id) as a where LS - LUS > 0 ,以上sql如何优化
这个SQL语句中,可以优化的地方如下:
1. 在WHERE子句中,使用函数year()和month()可能导致索引失效,可以考虑将其改为claim_time的范围查询,如下所示:
```
AND claim_time >= DATE(current date - 1 month) AND claim_time < DATE(current date)
```
2. 在COUNT函数中,使用CASE语句进行条件统计,可以改为使用SUM函数,如下所示:
```
SUM(CASE WHEN ope_category='Ship' THEN 1 ELSE 0 END) AS LS
SUM(CASE WHEN ope_category='Unship' THEN 1 ELSE 0 END) AS LUS
```
3. 在WHERE子句中,使用了聚合函数,将查询结果限制在LS-LUS>0的记录,这种方式不利于索引的使用,可以考虑使用HAVING子句,如下所示:
```
HAVING LS > LUS
```
4. 在子查询中,只需要查询claim_time的最大值,可以使用MAX函数来代替ORDER BY和LIMIT 1子句,如下所示:
```
MAX(claim_time) AS claim_time
```
综上所述,优化后的SQL语句如下:
```
SELECT lot_id, MAX(claim_time) AS claim_time,
SUM(CASE WHEN ope_category='Ship' THEN 1 ELSE 0 END) AS LS,
SUM(CASE WHEN ope_category='Unship' THEN 1 ELSE 0 END) AS LUS
FROM f3rpt.F3_TB_DAILY_FABOUT
WHERE substr(lot_id,1,2) NOT IN ('CA','CW','ES','E0','EM')
AND lot_type = 'Production'
AND LOT_ID NOT LIKE 'H%'
AND substr(lot_id,7,4)='.00F'
AND ope_category IN ('Ship','Unship')
AND claim_time >= DATE(current date - 1 month) AND claim_time < DATE(current date)
GROUP BY lot_id
HAVING LS > LUS;
```
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)