mysql 아래와 같이 쿼리 작성해서 돌리려고해요. select @rownum:=@rownum+1 as RNUM, (select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 4) scount, (select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 2) lcount, (select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 1) ccount, (select count(distinct ieee_address) from WL_SENSOR_LEAK_DATA where SITE_ID = a.SITE_ID and receive_date = '20190105' and leak_percent between 0 and 79) gpass from( select distinct e.SIDO_NAME, d.SITE_NAME, a.SITE_ID, c.VERSION from WL_DEVICE a left join WL_DEVICE_LOCATION b on a.SITE_ID = b.SITE_ID left join WL_DEVICE_ADDITONAL c on left(replace(ltrim(replace(a.IEEE_ADDRESS,'0',' ')),' ','0'),2) = c.IEEE_DIVISION and a.SITE_ID = c.SITE_ID left join WL_SITE d on a.SITE_ID = d.SITE_ID left join WL_SIDO e on d.SIDO_CODE = e.SIDO_ID where a.SITE_ID in (select SITE_ID from WL_SITE where VISIBLE = 'y' and LTE = 0) and a.DEVICE_TYPE = 1 and b.DEVICE_TYPE = 1 and c.VERSION is not null order by e.SIDO_NAME, d.SITE_NAME desc )a ,(SELECT @rownum :=2) as r 원래는 잘돌아갔는데.. 검색로우는 65건.. 그러나 gpass 컬럼을 추가하려고 넣은뒤로 속도가 어마어마 하게 느려져서 검색이안될정도.. WL_SENSOR_LEAK_DATA 에 로우수가 좀많아요 근데 아래처럼 따로 사이트아이디 를 입력하고 검색하는건 또 금방나오거든요. select count(distinct ieee_address) from WL_SENSOR_LEAK_DATA where site_id = '4776025021' and receive_date = '20190105' and leak_percent between 0 and 79 site_id 에 개별 인덱스는 걸려있고.. 검색이 가능하게 될방법이없을까요..ㅜ
site_id , receive_date, leak_percent 3개 동시에 인덱스 걸어보고있는데.. 이거걸면 좀 빨라질까요..
my sql 버전은 7.7.0.579 버전이에요
WL_DEVICE = 디바이스테이블 / 기본키 : SITE_ID , IEEE_ADDRESS
WL_DEVICE_LOCATION = 디바이스 위치테이블 / 기본키 : SITE_ID, IEEE_ADDRESS, DEVICE_TYPE
WL_SITE = 디바이스가 설치된 사이트 테이블 / 기본키 : SITE_ID
WL_SIDO = 시도 코드테이블 / 기본키 : SIDO_ID
WL_SENSOR_LEAK_DATA = 누수통신누적 테이블 / 기본키 : SITE_ID, IEEE_ADDRESS, RECEIVE_TIME
이에요 최종결과에는 포함되지않지만.. where 절에 사용되다보니.. 전부 조인걸어서 쓰고있어요..
IEEE_ADDRESS 가 수집기(디바이스) id ,즉 식별번호에요
Distinct 는.. WL_SENSOR_LEAK_DATA 테이블 조회의 결과가 receive_time 이 각각 달라서 같은 ieee_address 가 4건씩
나오게되서.. 중복된걸 없애주려고 사용했어요....;
WL_SENSOR_LEAK_DATA 에 인덱스 새로걸려고 시도했지만 로우수가 너무많아서 응답없음 뜨네요..;;ㅜㅜ
select @rownum:=@rownum+1 as RNUM, a.*, (select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 4) scount, (select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 2) lcount, (select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 1) ccount, (select count(distinct ieee_address) from WL_SENSOR_LEAK_DATA where site_id = a.SITE_ID and receive_time like '2019-01-05%' and leak_percent between 0 and 79) pcount, (select count(distinct ieee_address) from WL_SENSOR_LEAK_DATA where site_id = a.SITE_ID and receive_time like '2019-01-05%' and leak_percent between 80 and 100) rmiss, (select (select count(*) from WL_DEVICE where SITE_ID = a.SITE_ID and DEVICE_TYPE = 4) - count(distinct ieee_address) from WL_SENSOR_LEAK_DATA where site_id = a.SITE_ID and receive_time like '2019-01-05%') dmiss from( select distinct e.SIDO_NAME, d.SITE_NAME, a.SITE_ID, c.VERSION from WL_DEVICE a left join WL_DEVICE_LOCATION b on a.SITE_ID = b.SITE_ID left join WL_DEVICE_ADDITONAL c on left(replace(ltrim(replace(a.IEEE_ADDRESS,'0',' ')),' ','0'),2) = c.IEEE_DIVISION and a.SITE_ID = c.SITE_ID left join WL_SITE d on a.SITE_ID = d.SITE_ID left join WL_SIDO e on d.SIDO_CODE = e.SIDO_ID where a.SITE_ID in (select SITE_ID from WL_SITE where VISIBLE = 'y' and LTE = 0) and a.DEVICE_TYPE = 1 and b.DEVICE_TYPE = 1 and c.VERSION is not null order by e.SIDO_NAME, d.SITE_NAME desc )a ,(SELECT @rownum :=2) as r limit 5 아 위에 올린거중에 a.* 가 빠졌었네요.. ㅜ
아래와같은 테이블형태로 65로우까지 나오게되는 결과에요.. 지금 limit 걸어서 돌리니까 돌아가긴하는데... 엄청엄청오래걸려서 나오네요 .. 5건뿐인데두 ...;;
RNUM | SIDO_NAME | SITE_NAME | SITE_ID | VERSION | scount | lcount | ccount | pcount | rmiss | dmiss |
3 | 강원도 | 동해시(계약140401_) | 4217010100 | 구버전 | 548 | 93 | 4 | 133 | 9 | 406 |
4 | 경기도 | 하남시(계약170629_준공170920_AS만료190919) | 4145010200 | ver7이상 | 684 | 176 | 4 | 533 | 72 | 79 |
5 | 경상남도 | 함양군(계약170427_준공170627_AS만료190626) | 4887025027 | ver5~5.5 | 800 | 250 | 8 | 969 | 52 | 0 |
6 | 경상남도 | 통영시(계약140409_) | 4822010600 | ver5~5.5 | 595 | 80 | 3 | 547 | 34 | 14 |
7 | 경상남도 | 고성군(계약160623_준공161021_AS만료181020) | 4882025027 | ver5~5.5 | 1900 | 393 | 15 | 2150 | 80 | 0 |
SELECT b.sido_id , b.sido_name , a.site_id , a.site_name , c.version , d.scount , d.lcount , d.ccount , e.pcount , e.rmiss , d.scount - e.cnt AS dmiss FROM wl_site a INNER JOIN wl_sido b ON a.sido_code = b.sido_id INNER JOIN (SELECT site_id , LEFT(REPLACE(LTRIM(REPLACE(ieee_address,'0',' ')),' ','0'),2) ieee_division , COUNT(CASE WHEN device_type = 4 THEN 1 END) scount , COUNT(CASE WHEN device_type = 2 THEN 1 END) lcount , COUNT(CASE WHEN device_type = 1 THEN 1 END) ccount FROM wl_device GROUP BY site_id ) d ON a.site_id = d.site_id INNER JOIN (SELECT site_id , COUNT(DISTINCT ieee_address) cnt , COUNT(DISTINCT CASE WHEN leak_percent BETWEEN 0 AND 79 THEN ieee_address END) pcount , COUNT(DISTINCT CASE WHEN leak_percent BETWEEN 80 AND 100 THEN ieee_address END) rmiss FROM wl_sensor_leak_data WHERE receive_time LIKE '2019-01-05%' GROUP BY site_id ) e ON a.site_id = e.site_id INNER JOIN wl_device_additonal c ON d.site_id = c.site_id AND d.ieee_division = c.ieee_division WHERE a.visible = 'Y' AND a.lte = 0 AND c.version IS NOT NULL ;