그룹핑한 결과값의 min 값 당시의 시간값 구하기.. 0 14 226

by 마이닝 [MySQL] [2019.03.15 13:24:01]


1.png (16,903Bytes)
2.png (78,873Bytes)

아래와 같이 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

 

by 우리집아찌 [2019.03.15 14:03:38]

원래 데이터도 올려주세요.


by 마이닝 [2019.03.15 14:08:57]

원래데이터라면 어떤데이터를 말씀하시는건지.. 2번이미지에있는 데이터가 원래데이터에요 ㅠ


by 마농 [2019.03.15 14:46:03]

MySQL 버전이 어떻게 되나요?


by 마이닝 [2019.03.15 14:53:53]

지금 현재는 mysql 5.6으로 돌아가고 있어요... 


by 마농 [2019.03.15 15:04:01]
-- 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
;

 


by 마이닝 [2019.03.15 15:20:48]

답변 감사합니다..!

다만 올려주신 내용 확인해봤는데. 첫번째 비표준방법은 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 을 구하기는 불가능한걸까요 ..


by 마농 [2019.03.15 15:32:43]

아. 테스트 없이 쿼리만 작성한거라 오류가 잇었네요.
저는 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
;

 


by 마이닝 [2019.03.15 15:44:05]
그.. 2번째 방법으로 결과값이 아래 와같이 나오게 되는데요.. 모든로우가 다나왔을경우 21만건정도됩니다. 최종결과는 이녀석들을 sid 와 uid 로 그룹핑하여서 약 1400여건의 로우만 나오게 하고싶어요 그런데 아래 데이터가 sid 와 uid 로 그룹핑하고 fused , mnfval 를 쓴다고 가정해도. mnfval 의 최소값을 다시 min 으로 구해야하고 .. 그에 해당하는 rtime을 찾을수 없는 문제가 또다시생겨요 .. 전체데이터 검색과 같은결과값이 나옵니다.

by 마이닝 [2019.03.15 15:45:24]

 

 

* 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

by 마농 [2019.03.15 15:48:06]

뭔말인지 이해가 안가네요?
1. mnfval 의 최소값을 다시 min 으로 구해야하고? min 이 최소 아닌가요?
2. 그에 해당하는 rtime을 찾을수 없는 문제가? 왜 못찾는다고 생각하는지?
3. 전체데이터 검색과 같은결과값이 나옵니다? 그럴리가요?
제가 MIN(mnfval) 은 중복되지 않는다고 가정하고 문제를 푼 것은 맞지만.
전체 데이터가 그대로 나온다는 건 이상한데요?
잘 못 적용하신게 아닐 런지요?


by 마이닝 [2019.03.15 15:56:31]

아.. 제가 이야기를 잘못적었나봐요 . 설명능력이 부족해서 그래요 . ㅜㅜ 

총데이터 21만건중 site 와 uid 로 그룹핑하면 1400여건의 데이터가 나오고

이때 site 와 uid  별 mnfval  의 최소값과 ,   그 mnfval 와 같은 로우  에 해당하는 rtime 이필요한거에요..

최종결과는 1400이에요 

 

이리저리 해본결과로는 b.rtime 이부분이 값이 여러개라서 여러개가 나오는게 아닐까싶어요

 


by 마이닝 [2019.03.15 15:50:23]

 

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

by 마농 [2019.03.15 16:03:10]

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
;

 


by 마이닝 [2019.03.15 16:12:47]

아 ...고맙습니다... 

정말 하다가 멘붕에 빠져버려서 머리가 굳어버리니까 응용이 되지않네요...

진짜 뭐 라고 감사드려야할지 모르겠어요.  정말 쿼리관련해서 물어볼사람이 없어서 여기에 물어보는데 매번 너무 큰도움 받고갑니다..   ㅠㅠ

 

..인라인뷰가 무시되지않았다면 그안의 오더바이가 최소값을 가져오는거였군요 ... 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입