서로 다른 행에서 서로 다른 조건을 만족하는 값 선택

서로 다른 행에서 서로 다른 조건을 만족하는 값 선택

예를 들어 제가 두 칸짜리 테이블을 가지고 있다고 가정해 보겠습니다.

userid  |  roleid
--------|--------
   1    |    1
   1    |    2
   1    |    3
   2    |    1

모든 다른 사용자 ID를 얻길 원합니다.roleids1, 2 AND 3. 위의 예시를 사용하여 제가 반환을 원하는 유일한 결과는userid1. 이거 어떻게 해요?

좋아요, 저는 이것에 대해 반대표를 받았기 때문에 시험해보기로 했습니다.

CREATE TABLE userrole (
  userid INT,
  roleid INT,
  PRIMARY KEY (userid, roleid)
);

CREATE INDEX ON userrole (roleid);

실행 내용:

<?php
ini_set('max_execution_time', 120); // takes over a minute to insert 500k+ records

$start = microtime(true);

echo "<pre>\n";
mysql_connect('localhost', 'scratch', 'scratch');
if (mysql_error()) {
    echo "Connect error: " . mysql_error() . "\n";
}
mysql_select_db('scratch');
if (mysql_error()) {
    echo "Selct DB error: " . mysql_error() . "\n";
}

$users = 200000;
$count = 0;
for ($i=1; $i<=$users; $i++) {
    $roles = rand(1, 4);
    $available = range(1, 5);
    for ($j=0; $j<$roles; $j++) {
        $extract = array_splice($available, rand(0, sizeof($available)-1), 1);
        $id = $extract[0];
        query("INSERT INTO userrole (userid, roleid) VALUES ($i, $id)");
        $count++;
    }
}

$stop = microtime(true);
$duration = $stop - $start;
$insert = $duration / $count;

echo "$count users added.\n";
echo "Program ran for $duration seconds.\n";
echo "Insert time $insert seconds.\n";
echo "</pre>\n";

function query($str) {
    mysql_query($str);
    if (mysql_error()) {
        echo "$str: " . mysql_error() . "\n";
    }
}
?>

출력:

499872 users added.
Program ran for 56.5513510704 seconds.
Insert time 0.000113131663847 seconds.

이를 통해 500,000개의 임의 사용자-역할 조합이 추가되고 선택한 기준과 일치하는 약 25,000개가 있습니다.

첫번째 쿼리:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

쿼리 시간: 0.312s

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

쿼리 시간: 0.016s

맞아요.제가 제안한 join 버전은 aggregate 버전보다 20배 빠릅니다.

미안하지만 나는 실세계에서 생활하고 일하기 위해 이 일을 합니다. 그리고 실세계에서 우리는 SQL을 테스트하고 그 결과를 스스로 말합니다.

이에 대한 이유는 꽤 분명할 것입니다.집계 쿼리는 테이블 크기에 따라 비용이 증가합니다.모든 행은 처리, 집계 및 필터링됩니다(또는 그렇지 않음).HAVING절조인 버전은 (인덱스를 사용하여) 주어진 역할을 기준으로 사용자의 하위 집합을 선택한 다음, 두 번째 역할과 비교하여 하위 집합을 확인하고 마지막으로 세 번째 역할과 비교하여 하위 집합을 확인합니다.(관계 대수 용어로) 각 선택은 점점 더 작은 부분 집합에서 작동합니다.이로써 다음과 같은 결론을 내릴 수 있습니다.

조인 버전의 성능은 매치 발생률이 낮을수록 더욱 향상됩니다.

만약 위의 50만 샘플 중에서 단지 500명의 사용자만이 세 가지 역할을 가지고 있다면, 가입 버전은 상당히 빨라질 것입니다.Aggregate 버전은 그렇지 않습니다(그리고 성능 향상은 가입 버전도 당연히 얻을 수 있는 25k가 아니라 500명의 사용자를 전송한 결과입니다).

또한 실제 데이터베이스(즉, Oracle)가 이 문제를 어떻게 처리할지 궁금했습니다.그래서 기본적으로 Oracle XE에서 동일한 연습을 반복했는데(이전 예의 MySQL과 동일한 Windows XP 데스크톱 머신에서 실행됨) 결과가 거의 일치합니다.

가입자들은 눈살을 찌푸리고 있는 것처럼 보이지만, 앞서 설명했듯이, 집계 쿼리는 훨씬 느린 순서가 될 수 있습니다.

업데이트: 몇 가지 광범위한 테스트를 거친 후 그림이 더 복잡해지고 데이터, 데이터베이스 및 기타 요인에 따라 정답이 달라집니다.그 이야기의 교훈은 시험, 시험, 시험입니다.

SELECT userid
FROM UserRole
WHERE roleid IN (1, 2, 3)
GROUP BY userid
HAVING COUNT(DISTINCT roleid) = 3;

---

큰 소리로 생각해 보면, 클레투스가 묘사한 자기 결합을 쓰는 또 다른 방법은 다음과 같습니다.

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid
JOIN userrole t3 ON t2.userid = t3.userid
WHERE (t1.roleid, t2.roleid, t3.roleid) = (1, 2, 3);

이것은 당신에게 읽기에 더 쉬울 수 있고, MySQL은 그런 튜플의 비교를 지원합니다.MySQL은 이 쿼리를 위해 커버링 인덱스를 지능적으로 사용하는 방법도 알고 있습니다..EXPLAIN그리고 세 개의 테이블 모두에 대한 노트에서 "인덱스 사용"을 참조하십시오. 즉, 인덱스를 읽고 있으므로 데이터 행을 터치할 필요도 없습니다.

이 쿼리를 MacBook에서 MySQL 5.1.48을 사용하여 210만 행 이상(포스트태그용 Stack Overflow 7월 데이터 덤프) 실행한 결과 1.08초 만에 결과를 반환했습니다.충분한 메모리가 할당된 괜찮은 서버에서innodb_buffer_pool_size

_{이 글을 읽는 모든 사람들에게: 제 대답은 간단하고 직설적이며, '받아들인' 지위를 얻었지만, 클레투스가 주는 대답을 꼭 읽어보세요.성능이 훨씬 좋습니다.}

이를 위한 전형적인 방법은 관계적 분열 문제로 다루는 것입니다.

영문: 원하는 역할 ID 값이 없는 사용자를 선택합니다.

UserRole 테이블이 참조하는 Users 테이블이 있다고 가정하고 원하는 roleid 값이 테이블에 있다고 가정합니다.

create table RoleGroup(
  roleid int not null,
  primary key(roleid)
)
insert into RoleGroup values (1);
insert into RoleGroup values (2);
insert into RoleGroup values (3);

또한 모든 관련 열이 NULLable이 아니므로, IN 또는 NOT에 놀라운 일이 없다고 가정하겠습니다.위의 영어를 표현하는 SQL 쿼리는 다음과 같습니다.

select userid from Users as U
where not exists (
  select * from RoleGroup as G
  where not exists (
    select R.roleid from UserRole as R
    where R.roleid = G.roleid
    and R.userid = U.userid
  )
);

또 다른 방법은 이것입니다.

select userid from Users as U
where not exists (
  select * from RoleGroup as G
  where G.roleid not in (
    select R.roleid from UserRole as R
    where R.userid = U.userid
  )
);

인덱스, 플랫폼, 데이터 등에 따라 효율적일 수도 있고 그렇지 않을 수도 있습니다.웹에서 "관계 분할"을 검색하면 많은 것을 발견할 수 있을 것입니다.

userid를 가정하면 roleid가 고유 인덱스에 포함됩니다(userid = x, roleid = 1인 경우 2개의 레코드가 있을 수 없음).

select count(*), userid from t
where roleid in (1,2,3)
group by userid
having count(*) = 3

select userid from userrole where userid = 1
intersect
select userid from userrole where userid = 2
intersect
select userid from userrole where userid = 3

그러면 문제가 해결되지 않을까요?일반적인 관계형 DB에서 얼마나 좋은 솔루션입니까?쿼리 최적화기가 자동으로 최적화됩니까?

여기에 일반성이 필요한 경우(다른 3역할 조합 또는 다른 n역할 조합)...역할에 비트 마스킹 시스템을 사용하고 쿼리를 수행할 때 비트 와이즈 연산자를 사용하는 것을 제안합니다.

언급URL : https://stackoverflow.com/questions/477006/select-values-that-meet-different-conditions-on-different-rows