아래와 같이 site, uid 별로 그룹핑을해서 fused, mnfval 값을 구했습니다. 그런데 여기서 MIN(a.mnfval) 값당시의 rtime 을 구해야 하는데요.. 이리저리 몇시간동안 해봤지만 원하는 답이 얻어지지않습니다.. 1번쨰 이미지는 아래 쿼리실행했을떄의 결과구요 .. 2번쨰 이미지는 sf_flow_data 전체 데이터 검색을 실행했을떄의 결과입니다.. 어떻게 해야할까요.. SELECT a.site, a.uid, COALESCE(ROUND(MAX(a.fval)-MIN(a.fval),5),0.00000)fused, MIN(a.mnfval)mnfval FROM sf_flow_data a WHERE a.rdate = '20190315' GROUP BY a.site, a.uid
-- 1. (비표준) 그룹바이 특성 이용. (첫번째 값이 출력 됨) SELECT site , uid , COALESCE(ROUND(MAX(fval) - MIN(fval), 5), 0) fused , MIN(mnfval) mnfval , rtime FROM (SELECT site , uid , mnfval , fval , rtime FROM sf_flow_data WHERE rdate = '20190315' ORDER BY site, uid, mnfval DESC ) a GROUP BY site, uid ; -- 2. 셀프 조인 이용 SELECT a.site , a.uid , a.fused , a.mnfval , b.rtime FROM (SELECT site , uid , COALESCE(ROUND(MAX(fval) - MIN(fval), 5), 0) fused , MAX(mnfval) mnfval FROM sf_flow_data WHERE rdate = '20190315' GROUP BY site, uid ) a INNER JOIN sf_flow_data b ON a.rdate = b.rdate AND a.sid = b.sid AND a.uid = b.uid AND a.mnfval = b.mnfval ; -- 3. 분석함수 이용. MySQL 8.0 이상 가능 SELECT site , uid , COALESCE(ROUND(MAX(fval) - MIN(fval), 5), 0) fused , MIN(mnfval) mnfval , MIN(CASE WHEN rn = 1 THEN rtime END) rtime FROM (SELECT site , uid , mnfval , fval , rtime , ROW_NUMBER() OVER(PARTITION BY site, uid ORDER BY mnfval DESC) rn FROM sf_flow_data WHERE rdate = '20190315' ) a GROUP BY site, uid ;
답변 감사합니다..!
다만 올려주신 내용 확인해봤는데. 첫번째 비표준방법은 rtime 의 시간값이 MIN(mnfval) mnfval 일떄의 해당시간값과
다른값이 나오더라구요... 해당시간이 아닌 첫번쨰 값이라서 그런것같아요 ..
2019-03-15 오전 3:44:00 값이 나와야하는곳에 2019-03-15 오전 12:00:00 값이 나오게됩니다.
어... 그리고 두번쨰 셀프조인방식은
INNER JOIN sf_flow_data b ON a.rdate = b.rdate AND a.sid = b.sid
이부분에서 서브쿼리안에 rdate와 sid 가 존재하지않아 sql 에러가 생성되어
select 에 rdate와 sid 값을 넣고 group by 항목에도 포함시켰더니 의도하지않는 전체데이터 검색결과가 나오더라구요..
mysql 8.0 이상은 지금 사용을 못합니다 ... ㅜㅜ.. site, uid 의 그룹핑을 유지하면서 원하는 지점의 rtime 을 구하기는 불가능한걸까요 ..
아. 테스트 없이 쿼리만 작성한거라 오류가 잇었네요.
저는 MAX 인줄 알고 반대로 생각햇네요. MIN 이었군요.
DESC 부분 조정하고 없는 컬럼, 오타 컬럼 수정하면 되겠네요.
-- 1. (비표준) 그룹바이 특성 이용. (첫번째 값이 출력 됨) SELECT site , uid , COALESCE(ROUND(MAX(fval) - MIN(fval), 5), 0) fused , MIN(mnfval) mnfval , rtime FROM (SELECT site , uid , mnfval , fval , rtime FROM sf_flow_data WHERE rdate = '20190315' ORDER BY site, uid, mnfval ) a GROUP BY site, uid ; -- 2. 셀프 조인 이용 SELECT a.site , a.uid , a.fused , a.mnfval , b.rtime FROM (SELECT rdate , site , uid , COALESCE(ROUND(MAX(fval) - MIN(fval), 5), 0) fused , MIN(mnfval) mnfval FROM sf_flow_data WHERE rdate = '20190315' GROUP BY rdate, site, uid ) a INNER JOIN sf_flow_data b ON a.rdate = b.rdate AND a.site = b.site AND a.uid = b.uid AND a.mnfval = b.mnfval ; -- 3. 분석함수 이용. MySQL 8.0 이상 가능 SELECT site , uid , COALESCE(ROUND(MAX(fval) - MIN(fval), 5), 0) fused , MIN(mnfval) mnfval , MIN(CASE WHEN rn = 1 THEN rtime END) rtime FROM (SELECT site , uid , mnfval , fval , rtime , ROW_NUMBER() OVER(PARTITION BY site, uid ORDER BY mnfval) rn FROM sf_flow_data WHERE rdate = '20190315' ) a GROUP BY site, uid ;
* 2번쨰 쿼리를 사용했을경우 21만건의 전체데이터가 전부 노출됩니다
[site] [uid] [fused] [mnfval] [rtime]
3023010121 | 1551274257458 | 66.7376 | 2.87369 | 2019-03-15 ���� 3:44:00 |
3023010121 | 1551274257459 | 71.5105 | 10.0011 | 2019-03-15 ���� 8:22:30 |
3023010121 | 1551274257479 | 8261.6207 | 32.85941 | 2019-03-15 ���� 4:00:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:00:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:00:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:01:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:01:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:02:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:02:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:03:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:03:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:04:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:04:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:05:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:06:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:06:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:07:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:07:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:08:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:08:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:09:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:09:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:10:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:10:30 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:11:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:11:30 |
1번쨰 쿼리를 사용했을경우 원하는 1400여건의 데이터가 나오긴하지만 최소 mnfval 값에 해당하는 rtime 값이아닌 첫번째 값이 노출되어 최종데이터가 다르게 나타납니다
[site] [uid] [fused] [mnfval] [rtime]
3023010121 | 1551274257458 | 66.7376 | 2.87369 | 2019-03-15 ���� 12:00:00 |
3023010121 | 1551274257459 | 71.5105 | 10.0011 | 2019-03-15 ���� 12:00:00 |
3023010121 | 1551274257479 | 8261.6207 | 32.85941 | 2019-03-15 ���� 12:00:00 |
3023010121 | 1551274257480 | 0 | 0.0 | 2019-03-15 ���� 12:00:00 |
3023010121 | 1551274257481 | 204.5114 | 14.82062 | 2019-03-15 ���� 12:00:00 |
3023010121 | 1551274257482 | 87.0225 | 10.00807 | 2019-03-15 ���� 12:00:00 |
3023010121 | 1551274257483 | 54.4956 | 3.33207 | 2019-03-15 ���� 12:00:00 |
3023010121 | 1551274257484 | 275.1592 | 14.11686 | 2019-03-15 ���� 12:00:00 |
3023010121 | 1551274257485 | 44.6019 | 2.69119 | 2019-03-15 ���� 12:00:00 |
3023010121 | 1551274257486 | 0 | 0.0 | 2019-03-15 ���� 12:00:00 |
4148010201 | 1551274258428 | 151.7859 | 0.0 | 2019-03-15 ���� 12:00:00 |
4148010201 | 1551274258429 | 93.1908 | 0.0 | 2019-03-15 ���� 8:00:30 |
4273025045 | 1552579506467 | 2.7429 | 0.0437 | 2019-03-15 ���� 12:00:00 |
4273025045 | 1552601117929 | 9.2139 | 0.0 | 2019-03-15 ���� 3:00:00 |
4273025045 | 1552611921169 | 20.5298 | 0.0 | 2019-03-15 ���� 6:00:00 |
4273025045 | 1552622724098 | 23.7971 | 0.0 | 2019-03-15 ���� 9:00:00 |
4273025047 | 1551274258104 | 34.7542 | 0.964 | 2019-03-15 ���� 12:00:00 |
4273025047 | 1551274258105 | 62.2871 | 3.81429 | 2019-03-15 ���� 12:00:00 |
4273025047 | 1551274258106 | 217.4014 | 10.11348 | 2019-03-15 ���� 12:00:00 |
4273025047 | 1551274258418 | 331.1555 | 14.07449 | 2019-03-15 ���� 12:00:00 |
4273025047 | 1551274258419 | 37.2028 | 1.8642 | 2019-03-15 ���� 12:00:00 |
4380025023 | 1551274258073 | 28.1607 | 0.28556 | 2019-03-15 ���� 12:00:00 |
4380025023 | 1551274258074 | 68.5328 | 0.0 | 2019-03-15 ���� 12:00:00 |
4380025023 | 1551274258075 | 92.4757 | 0.0 | 2019-03-15 ���� 12:00:00 |
1번 쿼리 결과 오류 원인 - 인라인뷰 안의 ORDER BY 가 무시되는 것 같네요.
2번 쿼리 결과 오류 원인 - MIN(mnfval) = 0인 중복자료가 많은 듯 하네요.
한번 더 그룹바이 하세요.
SELECT a.site , a.uid , a.fused , a.mnfval , MIN(b.rtime) rtime FROM (SELECT rdate , site , uid , COALESCE(ROUND(MAX(fval) - MIN(fval), 5), 0) fused , MIN(mnfval) mnfval FROM sf_flow_data WHERE rdate = '20190315' GROUP BY rdate, site, uid ) a INNER JOIN sf_flow_data b ON a.rdate = b.rdate AND a.site = b.site AND a.uid = b.uid AND a.mnfval = b.mnfval GROUP BY a.site, a.uid, a.fused, a.mnfval ;