현재 위치

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

mssql 쿼리 작성 질문입니다.

doelnom의 이미지
글쓴이: doelnom / 작성시간: 월, 2012/09/10 - 9:48오후

--------
name val
--------
홍길동 10
아무개 5
김할매 13
원 빈 18

위와 같이 테이블 있을 때, val 값 높은 순으로 정렬 후,

위에서 부터 val값을 더하면서 그 합이 40보다 작을 때만 Select하고 싶다고 하면

--------
name val
--------
원 빈 18
김할매 13

만 셀렉이 되도록 쿼리를 고민 중인데 어렵습니다.

mysql 에서는

SET @v = 0

SELECT name, val
FROM 테이블
WHERE (@v := @v + val) AND @v < 40
ORDER BY val

과 같이 하면 가능한 것 같습니다. mssql에서는 구문 오류가 ㅜㅜㅜ

읽어주셔서 감사합니다 :]

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

글쓴이: winner / 작성시간: 월, 2012/09/10 - 11:26오후

제 생각에 이런 형태의 동작은 순차적으로 행들을 조회해야 할 것 같은데요. 물론 ORDER BY 가 들어가 있으니까 병렬처리가 될 가능성은 별로 없지만요.
하여간 순차적으로 조회한다면 간단하게는 CURSOR를 쓸 수 있겠네요.

academic의 이미지

글쓴이: academic / 작성시간: 화, 2012/09/11 - 1:08오후

ANSI SQL 표준에서는 over 절에 order by를 지원하니까 다음과 같이 할 수 있을지 모르지만(확인은 못해봤습니다.)

select name, val
from 테이블
where sum(val) over (order by val desc) < 40

mssql 2008까지는 over 절에 order by를 지원하지 않습니다. 그래서 이 방법을 쓸 수 없습니다.

일반적으로는 가급적 커서를 쓰지 않는 것을 권합니다만, 이런 경우에는 커서를 써서 구현하는 것이 좋습니다.

커서를 사용하지 않으면 데이터를 반복 재스캔해야 하지만 커서를 사용하면 커서를 선언할 때 데이터를 정렬할 수 있어서 한번만 스캔하기 때문에 빠릅니다.

----
academic은 제 고등학교 때 동아리 이름입니다.
academic, 아주 가끔은 저도 이랬으면 좋겠습니다.

addnull의 이미지

글쓴이: addnull / 작성시간: 화, 2012/09/11 - 2:46오후

academic 님 말처럼 cursor는 최후의 수단으로 쓰시길 바랍니다.
DB의 강력한 집합 연산을 쓸 수 없게 되어 성능이 많이 떨어지기 때문이죠.

저는 아래와 같은 JOIN 을 이용한 쿼리를 생각해보았는데요.

SELECT name, SUM(val2) AS sum_val
FROM
(
	SELECT t1.name, t1.val AS val1, t2.val AS val2
	FROM myTable AS t1 JOIN myTable AS t2
		ON t1.val <= t2.val
) t3
GROUP BY name
HAVING SUM(val2) < 40
ORDER BY SUM(val2)

academic 님이 알려주신 over 기능을 쓰면 아래와 같은 방법도 가능하군요.

SELECT name, sum_val
FROM
(	
	SELECT name, val, SUM(val) OVER (ORDER BY val DESC) AS sum_val
	FROM myTable
) t1
WHERE sum_val < 40

SQL server 2012에서 확인했습니다.

winner의 이미지

글쓴이: winner / 작성시간: 화, 2012/09/11 - 3:00오후

CURSOR 를 쓴다는 것은 순차적인 구조가 필요하다는 것이고, 이런 경우 병렬처리는 대부분 되지 않습니다.
CURSOR 가 느린 이유는 행단위 처리의 문제인데, 많은 경우 극복이 된다고 알고 있습니다.

그런데 이 경우의 성능 문제는... 어떨런지 좀 얘매하군요.

academic의 이미지

글쓴이: academic / 작성시간: 화, 2012/09/11 - 4:35오후

SQL Server 2012에서는 over 절에 order by를 지원하는군요. 좋은 정보 고맙습니다.

언제 기회되면 SQL Server 업그레이드를 건의해봐야겠습니다.

커서가 집합 기반보다 성능이 좋은 경우는 거의 없는데,

'순차적 누적값'을 구해야 하는 곳에서는

MS SQL Server 2008까지는 커서를 쓰는 것이 빨라서 부득이하게 커서를 썼습니다.

2012라면 커서를 안쓰고 over 절을 쓰는 것이 성능이 더 좋을 것 같습니다.

----
academic은 제 고등학교 때 동아리 이름입니다.
academic, 아주 가끔은 저도 이랬으면 좋겠습니다.

addnull의 이미지

글쓴이: addnull / 작성시간: 화, 2012/09/11 - 6:26오후

좀 오래됐지만, 순차적 누적값을 구하는 방법 3가지를 벤치마킹한 내용이 있네요.
http://www.sqlteam.com/article/calculating-running-totals

저기선 놀랍게도 cursor가 가장 빠른 결과를 보이네요.. ;;
아마 디스크 구성, DBMS 버전 등 여러 조건에 따라서 결과가 다른 것 같습니다.
실제 사용하시는 환경에서 여러가지 방법을 실험해보시는 게 좋을 것 같습니다 :)

winner의 이미지

글쓴이: winner / 작성시간: 수, 2012/09/12 - 9:08오전

MS SQL 에서는 CURSOR 를 만들 때 몇가지 선택사항이 있는데 그에 따라 많이 달라질 것 같네요.

doelnom의 이미지

글쓴이: doelnom / 작성시간: 화, 2012/09/11 - 6:34오후

답변 감사합니다. 많이 도움 되었습니다. :]

댓글 달기

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

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
이것은 자동으로 스팸을 올리는 것을 막기 위해서 제공됩니다.