SELECT
COUNT(
DISTINCT ( CASE WHEN ( ( `IsFba` = 1 ) AND ( `Status` = 5 )) THEN `OrderSourceSku` ELSE NULL END )) AS `FbaListingCount`,
COUNT(
DISTINCT ( CASE WHEN ( NOT ( `IsFba` = 1 ) AND ( `Status` = 5 )) THEN `OrderSourceSku` ELSE NULL END )) AS `ListingCount`,
`OrderSourceId` AS `OrderSourceId`,
`PlanType` AS `PlanType`,
COUNT( `Id` ) AS `ExeCount`,
SUM((
CASE
WHEN ( `Status` = 5 ) THEN
1 ELSE 0
END
)) AS `ExeSuccessCount`
FROM
(
SELECT
*
FROM
(
SELECT
`Id` AS `Id`,
`IsFba` AS `IsFba`,
`Status` AS `Status`,
`OrderSourceSku` AS `OrderSourceSku`,
`OrderSourceId` AS `OrderSourceId`,
`PlanType` AS `PlanType`
FROM
`Apx_BasicPlan_LogDetail`
WHERE
(((((
`CustomerId` = 1
)
AND ( `OrderSourceId` = 17877 ))
AND ( `PlanType` = 3 ))
AND ( `CreationTime` < '2023-03-21 00:00:00' ))
AND ((
`ExecuteType` = 5
)
AND ( `PlanType` = 4 ))) UNION
SELECT
`Id` AS `Id`,
`IsFba` AS `IsFba`,
`Status` AS `Status`,
`OrderSourceSku` AS `OrderSourceSku`,
`OrderSourceId` AS `OrderSourceId`,
`PlanType` AS `PlanType`
FROM
`Apx_BasicPlan_LogDetail`
WHERE
(((((
`CustomerId` = 1
)
AND ( `OrderSourceId` = 17877 ))
AND ( `PlanType` = 3 ))
AND ( `CreationTime` < '2023-03-21 00:00:00' ))
AND ( `PlanType` <> 4 ))
) unionTable
) t;
| 执行次数:2517 | 平均时间：16.705 | 最大时间：61.460 |
建议优化为：
SELECT
COUNT(
DISTINCT ( CASE WHEN ( ( `IsFba` = 1 ) AND ( `Status` = 5 )) THEN `OrderSourceSku` ELSE NULL END )) AS `FbaListingCount`,
COUNT(
DISTINCT ( CASE WHEN ( NOT ( `IsFba` = 1 ) AND ( `Status` = 5 )) THEN `OrderSourceSku` ELSE NULL END )) AS `ListingCount`,
`OrderSourceId` AS `OrderSourceId`,
`PlanType` AS `PlanType`,
COUNT( `Id` ) AS `ExeCount`,
SUM((
CASE
WHEN ( `Status` = 5 ) THEN
1 ELSE 0
END
)) AS `ExeSuccessCount`
FROM
(
SELECT
*
FROM
(
SELECT
`Id` AS `Id`,
`IsFba` AS `IsFba`,
`Status` AS `Status`,
`OrderSourceSku` AS `OrderSourceSku`,
`OrderSourceId` AS `OrderSourceId`,
`PlanType` AS `PlanType`
FROM
`Apx_BasicPlan_LogDetail`
WHERE
(((((
`CustomerId` = 1
)
AND ( `OrderSourceId` = 17877 ))
AND ( `PlanType` = 3 ))
AND ( `CreationTime` < '2023-03-21 00:00:00' ))
)
) unionTable
) t;
