현재 위치

홈 ›› Forums ›› 공부 ›› 프로그래밍 QnA

MySQL 에서 median 값 찾을 때 어떻게 찾는지 궁금합니다.

feelpassion의 이미지
글쓴이: feelpassion / 작성시간: 일, 2015/12/06 - 1:23오후

안녕하세요?

mysql로 데이터를 가공하다가 해결 안되는 문제가 있어서 문의드립니다.

TABLE1(ID INT, PRICE1 INT, PRICE2 INT, PRICE3 INT)

위와 같은 테이블에서 PRICE3를 기준으로 정렬하고 중간 값을 찾으려고 합니다.
그리고 PRICE3의 중간 값이 있는 레코드의 값들을 다른 table에 옮기려고 합니다.
예를 들면,

TABLE2(ID INT, PRICE1 INT, PRICE2 INT)

위와 같은 TABLE2에 TABLE1의 ID와 PRICE1, PRICE2를 옮기고 싶은 거죠.
그리고 TABLE1의 레코드 개수가 짝수 개일 때는 중간 값의 바로 위나 아래의 값으로 선택하고 싶습니다.

처음에 아래와 같은 방법을 써보았습니다.

DECLARE midval INT;
DECLARE p1 INT;
DECLARE p2 INT;
SELECT COUNT(*)/2 INTO midval FROM TABLE1;
SELECT PRICE1, PRICE2 INTO p1, p2 FROM TABLE1 ORDER BY PRICE3 LIMIT midval, 1;

안되더군요^^;

그래서 동적 쿼리를 어디서 주워듣고 아래와 같은 코드를 작성해 보았습니다만, 계속 syntax 오류만 나고 있습니다.

SET @midval = (SELECT COUNT(*)/2 FROM TABLE1);
SET @p1 = 0;
SET @p2 = 0;
 
SET @sql = N'SELECT PRICE1, PRICE2 INTO @p1, @p2 FROM TABLE1 ORDER BY PRICE3 LIMIT @midnum, 1';
EXEC sp_executesql @sql, N'@p1 INT OUTPUT, @p2 INT OUTPUT, @midval INT', @p1 OUTPUT, @p2 OUTPUT, @midval INT;

어떻게 하면 해결할 수 있을까요?ㅜㅜ

동적 쿼리를 한 번이라도 성공해보기 위해서 아래와 같은 아주 간단한 프로시저를 만들어서 실행해 보았습니다만,
동작하지 않네요... 

DELIMITER //
DROP PROCEDURE IF EXISTS MEDIAN//
CREATE PROCEDURE MEDIAN()
BEGIN
	DECLARE @midval VARCHAR(11);
	DECLARE @sql NVARCHAR(200);
	SET @midval  = '0';
	SET @sql=' SELECT PRICE1 FROM TABLE1 limit @midval, 1';
	EXECUTE sp_executesql @sql, N'@midval VARCHAR(11)', @midval;
	RETURN;
END
//
DELIMITER ;

끝까지 봐주셔서 감사드립니다.
천금 같은 조언 부탁드리겠습니다.

감사합니다.

Forums: 
프로그래밍 QnA
shint의 이미지

글쓴이: shint / 작성시간: 일, 2015/12/06 - 5:37오후

현재 상황을 정리해보면.
- 소수점이 출력되는 문제가 있습니다.
- 전역변수 ON 설정이 있습니다.
- CREATE PROCEDURE 가 있습니다.
PHPMyAdmin 과 MySQL 콘솔에서의 코드가 약간 다릅니다. DELIMITER ?? DELIMITER // DELIMITER;
- CREATE FUNCTION 이 있습니다. RETURNS 도 있습니다.
- TOP 이 있습니다.
- 변수 지정 및 사용 방법은 SET A=1; 과 SET @A=1; SET A:=1; 등등 다양하네요. 각각 출력 결과가 다르기도 합니다.

- 만약. index 가 있거나. 가상에 view에서 index번호를 생성할 수 있다면. select index from @중간위치; 로 검색될지도 모릅니다.

하지만. 이건 계산을 해야 하니. 느릴 수 있습니다.

데이터를 select 할때 몇번째 열인지 알수있나요?
http://kin.naver.com/qna/detail.nhn?d1id=1&dirId=1040203&docId=233598189&qb=TXlTUUwgaW5kZXggc2VsZWN0&enc=utf8&section=kin&rank=2&search_sort=0&spq=0

ROW_NUMBER 로 검색

이것도 카운트라 느릴거 같습니다.
RANK(), ROW_NUMBER(), DENSE_RANK() 함수 - 순위함수 partition by 까지 설명 Oracle
http://blog.naver.com/ssarmang/220253520600

정렬된 View의 row 순서 알아내기 - ROW_NUMBER() MySQL / 컴퓨터이야기
http://blog.naver.com/2feelus?Redirect=Log&logNo=220422564250

★★★★★ 이거 쓸만해 보입니다. 아직 값은 안보이지만...
[MSSQL] MSSQL의 페이징 쿼리 고찰 - 1. Row_Number + bettwen
http://blog.danggun.net/2451

between 과 if 비교연산자
http://blog.naver.com/parkjy76?Redirect=Log&logNo=30132160802

현재. LIMIT 에서 첫번째 인자값으로 입력이 되지 않고 있습니다.
전역변수 문제인지. 아니면. SELECT 의 리턴값 처리를 못해서 인지. 는 모르겠습니다.
그냥. 편하게. PHP 를 사용하시면 될것 같습니다.

전역 변수 ON
http://blog.junghun.com/220225164157
show global variables like 'log_bin_trust_function_creators';
SET GLOBAL log_bin_trust_function_creators = 1; # ON
SET GLOBAL log_bin_trust_function_creators = 2; # OFF

TOP
http://smilennv.blog.me/220243719012

CREATE FUNCTION f() RETURNS INT - 펑션이 있네? 리턴값
http://mysqlserverteam.com/server-side-select-statement-timeouts/

LIMIT 설명
http://www.mysqltutorial.org/mysql-limit.aspx

--------------------------------------------------------------
DB 를 선택하는 방법 (DB를 선택해야 삭제가 된다.)
--------------------------------------------------------------
use 디비이름;
 
--------------------------------------------------------------
MySQL 에서 MEDIAN이라는 이름으로 함수 프로시져를 생성하는 방법
함수 프로시져는 DB를 선택하지 않아도 생성이 된다.
--------------------------------------------------------------
DELIMITER ??
CREATE PROCEDURE MEDIAN()
BEGIN
END;
 
--------------------------------------------------------------
함수 프로시져를 삭제하는 방법 (생성과 삭제가 동시에 실행안된다.)
--------------------------------------------------------------
DROP PROCEDURE IF EXISTS MEDIAN;
 
--------------------------------------------------------------
프로시져와 함수를 보는 방법
--------------------------------------------------------------
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

http://roresy.tistory.com/2587

이건 되긴 하지만. LIMIT 에 넣을 순 없습니다.
http://www.devpia.com/MAEUL/Contents/Detail.aspx?BoardID=50&MAEULNo=20&no=889913&ref=889913

DELIMITER $$
 
CREATE PROCEDURE LOADUSERDATA(
IN sid VARCHAR(20),
IN pwd VARCHAR(20),
OUT POINT INT)
BEGIN
 
    SELECT COUNT(*)/2 INTO POINT FROM game_point;
 
END
$$
DELIMITER ;
 
 
CALL LOADUSERDATA(1, 1, @num);
SELECT @num;

----------------------------------------------------------------------------
젊음'은 모든것을 가능하게 만든다.

매일 1억명이 사용하는 프로그램을 함께 만들어보고 싶습니다.
정규 근로 시간을 지키는. 야근 없는 회사와 거래합니다.

각 분야별. 좋은 책'이나 사이트' 블로그' 링크 소개 받습니다. shintx@naver.com

shint의 이미지

글쓴이: shint / 작성시간: 일, 2015/12/06 - 7:07오후


중간 값을 얻는 쿼리

하지만. 원하는 값에 대한건 아닙니다. 그냥 중간 카운터 얻는 쿼리가 구현됐습니다.

SET @var2 = 0;
SELECT TESTV from (SELECT (@var2:=@var2+1) AS TESTV from game_point group by point)as a where TESTV >= @var2/2 and TESTV <= @var2/2;

쿼리가 만들어진 과정

Select * From game_point as PageData Where point between ((@var2 - 1)) + 1 And @var2;
 
 
SELECT (@var2:=@var2+1) AS TEST from game_point;
 
select count(*) from (select count(*) as count from game_point group by point)as a;
 
select count(*)/2 from (SELECT (@var2:=@var2+1) AS TEST from game_point group by point)as a;
 
select (@var2:=@var2+1) AS TESTV from (select count(*)/2 as count from game_point group by point)as a;
 
SELECT TESTV from (SELECT (@var2:=@var2+1) AS TESTV from game_point group by point)as a;
 
SELECT TESTV from (SELECT (@var2:=@var2+1) AS TESTV from game_point group by point)as a where TESTV=10;
 
SELECT TESTV from (SELECT (@var2:=@var2+1) AS TESTV from game_point group by point)as a where TESTV > 0 and TESTV < 10;
SELECT TESTV from (SELECT (@var2:=@var2+1) AS TESTV from game_point group by point)as a where TESTV >= @var2/2 and TESTV <= @var2+1;

----------------------------------------------------------------------------
젊음'은 모든것을 가능하게 만든다.

매일 1억명이 사용하는 프로그램을 함께 만들어보고 싶습니다.
정규 근로 시간을 지키는. 야근 없는 회사와 거래합니다.

각 분야별. 좋은 책'이나 사이트' 블로그' 링크 소개 받습니다. shintx@naver.com

shint의 이미지

글쓴이: shint / 작성시간: 일, 2015/12/06 - 7:27오후


카운트를 증가시킨 결과를 조건문으로 /2 한 결과를 얻고. 그와 같은 카운터에 실제 값을 얻는 방법

SET @var2 = 0;
SELECT TESTV, point from (SELECT (@var2:=@var2+1) AS TESTV, point from game_point group by point)as a 
where TESTV >= @var2/2 and TESTV <= @var2/2;
+-------+-------+
| TESTV | point |
+-------+-------+
|     2 |    44 |
+-------+-------+
1 row in set (0.00 sec)

between 을 사용한 방법

SET @var2 = 0;
SELECT TESTV, point from (SELECT (@var2:=@var2+1) AS TESTV, point from game_point group by point)as a where TESTV between @var2/2 and @var2/2;

SET @var2 = 0;
SELECT TESTV, point from (SELECT (@var2:=@var2+1), point AS TESTV, point from game_point group by point)as a 
where TESTV >= @var2/2 and TESTV <= @var2/2;
 
 
select * from game_point;
 
 
SET @var2 = 0;
SELECT TESTV, point from (SELECT (@var2:=@var2+1) AS TESTV, point from game_point group by point)as a;

----------------------------------------------------------------------------
젊음'은 모든것을 가능하게 만든다.

매일 1억명이 사용하는 프로그램을 함께 만들어보고 싶습니다.
정규 근로 시간을 지키는. 야근 없는 회사와 거래합니다.

각 분야별. 좋은 책'이나 사이트' 블로그' 링크 소개 받습니다. shintx@naver.com

feelpassion의 이미지

글쓴이: feelpassion / 작성시간: 월, 2015/12/07 - 7:03오후

이 글을 보기 전에 문제를 해결했습니다.
그러나 성능은 아주 느릴 거 같으니 님의 코드를 보고 최적화를 시도해봐야겠습니다^^

제가 해결한 방법은 primary key column을 추가하고 key 숫자가 중간인 것을 찾는 방법입니다.

관심가져주셔서 너무너무 감사드립니다.

복 받으실 겁니다^^

감사합니다.

남으로 창을 내겠소.
밭이 한참갈이 괭이로 파고 호미론 김을 메지요.
구름이 꼬인다 갈리있소. 새들의 노래는 공으로 들으랴오.
강냉이가 익거든 와자셔도 좋소.
왜 사냐건 웃지요.

shint의 이미지

글쓴이: shint / 작성시간: 월, 2015/12/07 - 9:41오후

SELECT에서 카운터를 세야해서. 느릴 수 있습니다.

그냥. PHP에서 하셔야 빠를거 같습니다.

SELECT point FROM game_point LIMIT $카운터변수, 1;

----------------------------------------------------------------------------
젊음'은 모든것을 가능하게 만든다.

매일 1억명이 사용하는 프로그램을 함께 만들어보고 싶습니다.
정규 근로 시간을 지키는. 야근 없는 회사와 거래합니다.

각 분야별. 좋은 책'이나 사이트' 블로그' 링크 소개 받습니다. shintx@naver.com

댓글 달기

텍스트 포맷에 대한 자세한 정보

Filtered HTML

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

BBCode

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param>
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

Textile

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • You can use Textile markup to format text.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Markdown

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • Quick Tips:
    • Two or more spaces at a line's end = Line break
    • Double returns = Paragraph
    • *Single asterisks* or _single underscores_ = Emphasis
    • **Double** or __double__ = Strong
    • This is [a link](http://the.link.example.com "The optional title text")
    For complete details on the Markdown syntax, see the Markdown documentation and Markdown Extra documentation for tables, footnotes, and more.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Plain text

  • HTML 태그를 사용할 수 없습니다.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 줄과 단락은 자동으로 분리됩니다.
댓글 첨부 파일
이 댓글에 이미지나 파일을 업로드 합니다.
파일 크기는 8 MB보다 작아야 합니다.
허용할 파일 형식: txt pdf doc xls gif jpg jpeg mp3 png rar zip.
CAPTCHA
이것은 자동으로 스팸을 올리는 것을 막기 위해서 제공됩니다.