智能刊登gaussdb 慢sql top 10
2.解决方案
1.发现未使用`ORDER BY`的`GROUP BY`查询`GROUP BY a.customerid,a.marketId,a.sitecode`，该用法会导致无谓的排序产生，如果不需要排序建议添加`ORDER BY NULL`;
2.发现导致不确定结果的`GROUP BY`语句，SQL返回的列'sitecode,customerid,marketId,CreationTime'既不在聚合函数中也不是`GROUP BY`表达式的列中，因此这些值的结果将是不确定性的;
3.发现别名'date(CreationTime) AS CreationTime'与已存在的列名'CreationTime'相同，可能引起歧义，建议替换)
3.原sql
SELECT m.`Name` AS MarketName, n.SiteCode, n.未采集 AS NoCollectionCount, n.采集中 AS PendingCollectionCount, n.采集成功 AS SuccessCollectionCount
, n.采集失败 AS FailureCollectionCount, n.采集总数 AS CollectionCount, n.成功率 AS SuccessPercent
FROM (SELECT customerid, marketId, sitecode, SUM(CASE WHEN `Status` = 0 THEN 1 ELSE 0 END) AS "未采集", SUM(CASE WHEN `Status` = 1 THEN 1 ELSE 0 END) AS "采集中"
, SUM(CASE WHEN `Status` = 2 THEN 1 ELSE 0 END) AS "采集成功", SUM(CASE WHEN `Status` = 3 THEN 1 ELSE 0 END) AS "采集失败", COUNT(1) AS "采集总数", SUM(CASE WHEN `Status` = 2 THEN 1 ELSE 0 END) / SUM(CASE WHEN `Status` = 2
OR `Status` = 3 THEN 1 ELSE 0 END) AS "成功率", date(CreationTime) AS CreationTime
FROM p_market_product_category_collection a
WHERE a.CustomerId = 1
AND LastModificationTime > '2022-07-28 16:00:00'
AND MarketId = 57
GROUP BY a.customerid, a.marketId, a.sitecode
) n
INNER JOIN sys_market m ON m.CustomerId = n.CustomerId
AND m.MarketId = n.MarketId