现在sql最快返回5秒左右,表加了查询条件三个字段的复合索引,希望能提高到2秒左右,谢谢。
SELECT COALESCE(a1.visit1,
a2.visit2,
a3.visit3,
a4.visit4,
a5.visit5,
a6.visit6,
a7.visit7) as visit,
num1,
num2,
num3,
num4,
num5,
num6,
num7
from (SELECT TO_CHAR(VISIT_TIME, 'yyyy-mm-dd') as visit1,
count(1) as num1,
APP_TYPE
FROM SOFTWARE_VISIT_COUNT
WHERE APP_TYPE = 3
and APP_ID = 1
and VISIT_TYPE = 1
GROUP BY APP_TYPE, TO_CHAR(VISIT_TIME, 'yyyy-mm-dd')) a1
full join (SELECT TO_CHAR(VISIT_TIME, 'yyyy-mm-dd') as visit2,
count(1) as num2,
APP_TYPE
FROM SOFTWARE_VISIT_COUNT
WHERE APP_TYPE = 3
and APP_ID = 2
and VISIT_TYPE = 1
GROUP BY APP_TYPE, TO_CHAR(VISIT_TIME, 'yyyy-mm-dd')) a2
on A1.visit1 = A2.visit2
full join (SELECT TO_CHAR(VISIT_TIME, 'yyyy-mm-dd') as visit3,
count(1) as num3,
APP_TYPE
FROM SOFTWARE_VISIT_COUNT
WHERE APP_TYPE = 3
and APP_ID = 1
and VISIT_TYPE = 3
GROUP BY APP_TYPE, TO_CHAR(VISIT_TIME, 'yyyy-mm-dd')) a3
on a3.visit3 = a1.visit1
or a3.visit3 = a2.visit2
full join (SELECT TO_CHAR(VISIT_TIME, 'yyyy-mm-dd') as visit4,
count(1) as num4,
APP_TYPE
FROM SOFTWARE_VISIT_COUNT
WHERE APP_TYPE = 3
and APP_ID = 2
and VISIT_TYPE = 3
GROUP BY APP_TYPE, TO_CHAR(VISIT_TIME, 'yyyy-mm-dd')) a4
on a4.visit4 = a1.visit1
or a4.visit4 = a3.visit3
or a4.visit4 = a2.visit2
full join (SELECT TO_CHAR(VISIT_TIME, 'yyyy-mm-dd') as visit5,
count(1) as num5,
APP_TYPE
FROM SOFTWARE_VISIT_COUNT
WHERE APP_TYPE = 3
and APP_ID = 3
and VISIT_TYPE = 3
GROUP BY APP_TYPE, TO_CHAR(VISIT_TIME, 'yyyy-mm-dd')) a5
on a5.visit5 = a1.visit1
or a5.visit5 = a2.visit2
or a5.visit5 = a3.visit3
or a5.visit5 = a4.visit4
full join (SELECT TO_CHAR(VISIT_TIME, 'yyyy-mm-dd') as visit6,
count(1) as num6,
APP_TYPE
FROM SOFTWARE_VISIT_COUNT
WHERE APP_TYPE = 3
and APP_ID = 4
and VISIT_TYPE = 3
GROUP BY APP_TYPE, TO_CHAR(VISIT_TIME, 'yyyy-mm-dd')) a6
on a1.visit1 = a6.visit6
or a2.visit2 = a6.visit6
or a3.visit3 = a6.visit6
or a4.visit4 = a6.visit6
or a5.visit5 = a6.visit6
full join (SELECT TO_CHAR(VISIT_TIME, 'yyyy-mm-dd') as visit7,
count(1) as num7,
APP_TYPE
FROM SOFTWARE_VISIT_COUNT
WHERE APP_TYPE = 3
and APP_ID = 5
and VISIT_TYPE = 3
GROUP BY APP_TYPE, TO_CHAR(VISIT_TIME, 'yyyy-mm-dd')) a7
on a1.visit1 = a7.visit7
or a2.visit2 = a7.visit7
or a3.visit3 = a7.visit7
or a4.visit4 = a7.visit7
or a5.visit5 = a7.visit7
or a6.visit6 = a7.visit7
ORDER BY visit;
收起