안녕하세요? 아래 쿼리 속도가 1분정도 나옵니다.
속도 향상 방법좀 부탁드립니다.
with IDS AS
(
select
max(idx) as idx, ID_no as ids , date +' ' +substring(time,0,6) dt
from ID_TABLE WITH(NOLOCK)
WHERE date+time between '2019-04-0100:00:00' and '2019-04-3024:00:00'
AND ID_GROUP ='A'
AND ID_CLASS ='EVE'
group by ID_no , date +' ' +substring(time,0,6)
)
SELECT
C.SDT , (convert(NUMERIC(12,1),convert(float,COUNT(*))/144*100)) CNT2 , count(*) CNT
FROM(
SELECT substring(SDT,0,11) as SDT
FROM (
SELECT a.dt sdt
FROM IDS a
INNER JOIN IDS b ON b.dt >= a.dt AND b.dt < DATEADD(minute, 10, a.dt)
GROUP BY a.idx, a.ids, a.dt
HAVING COUNT(DISTINCT b.ids) >= 10
) A
GROUP BY SDT
) C
감사합니다. |