SQL 수: 불규칙한 동작

SQL 수: 불규칙한 동작

제가 작성한 SQL이 의도한 대로 작동하지 않습니다.중요한 논리 중 하나는 VIP가 몇 명인지 세는 것이지만 SQL은 계속해서 잘못된 답을 얻는 것 같습니다.

다음 데이터베이스에는 6명의 게스트가 있으며, 그 중 3명은 VIP입니다.

CREATE TABLE `guest` (
  `GuestID` int(11) NOT NULL DEFAULT '0',
  `fullname` varchar(255) DEFAULT NULL,
  `vip` tinyint(1) DEFAULT '0',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `guest`
--

INSERT INTO `guest` (`GuestID`, `fullname`, `vip`) VALUES
(912, 'Sam',  0),
(321, 'Sev', 0),
(629, 'Joe', 0),
(103, 'Tom', 1),
(331, 'Cao', 1),
(526, 'Conor', 1);

처음에 SQL에서 VIP가 5개라고 하는 값을 반환했는데 VIP가 3개뿐이므로 잘못된 값입니다.이것은 상당히 복잡한 데이터베이스이며, 이 질문에 대한 최소한의 실행 가능한 예를 생성할 때(재생산 가능한 오류가 있음) 스크립트는 이제 VIP가 2개뿐이라고 말합니다.다시 말하지만, 이것은 올바르지 않습니다.

문제의 SQL은

SELECT slotguest.FK_SlotNo, Count(CASE WHEN guest.vip = 1 THEN 1 END) AS guest_count 
FROM guest 
INNER JOIN slotguest ON guest.GuestID = slotguest.FK_guest 
GROUP BY slotguest.FK_SlotNo;

슬롯 게스트 구조 및 내용은 다음과 같습니다.

CREATE TABLE `slotguest` (
  `FK_SlotNo` int(11) NOT NULL,
  `FK_guest` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `slotguest`
--

INSERT INTO `slotguest` (`FK_SlotNo`, `FK_guest`) VALUES
(396, 912),
(396, 321),
(396, 629),
(396, 103),
(396, 331),
(396, 526);

무엇이 카운트가 계속해서 잘못된 답을 생각하게 만드는 원인입니까?

댓글(사용자 @Fábio Amorim, @Rajat의 확인)에서 확인된 바와 같이, 당신의 질문은 의도한 대로 작동하는 것 같습니다.다음을 사용하여 값을 설정했습니다.CASE WHEN사용하는 것이 더 나을 수 있습니다.SUM.

데이터 누출이 발생할 수 있는 지점을 찾기 위해 여러 VIP 범주의 카운트를 가져오면 더 잘 보일 수 있습니다.

SELECT guest.vip, slotguest.FK_SlotNo, COUNT(*) AS guest_per_category
FROM guest 
INNER JOIN slotguest ON guest.GuestID = slotguest.FK_guest
GROUP BY guest.vip,slotguest.FK_SlotNo;

"폭발-침입" 같은 냄새가 나요.정해진

SELECT ... COUNT(*)
    FROM a JOIN b ...
    GROUP BY ...

쿼리는 다음과 같이 수행됩니다.

1. JOIN탁자들테이블이 사소한 1:1이 아니라고 가정하면 두 테이블보다 행이 더 많습니다.
2. Aggregate 실행(예:COUNT) 그 임시 테이블에 기대어 있습니다.
3. 그래야만 그 일이GROUP BY원래 원하는 행 수로 축소합니다.

솔루션은 카운트/합계되는 데이터가 포함된 테이블이 둘 이상인 경우 집계를 수행하지 않도록 하는 것입니다.때때로 그 패턴은

 SELECT ...
     FROM ( SELECT x, COUNT(*) AS ct FROM a GROUP BY x ) AS b
     JOIN c ON ...

뭐가 문제인지 설명하고, O.P.의 질문에 더 가까운 대답을 하는 것은...

(저는 O.P.가 무엇이 잘못되고 있는지에 대한 간략한 예라고 생각합니다. 그리고 실제 질문은 더 복잡합니다.만약 우리가 더 큰 그림을 알았다면, 나는 그렇게 코딩하지 않았을 것이라고 의심합니다.)

O.P. 쿼리에서CASE WHEN guest.vip = 1 THEN 1 END형식이 불량합니다.이것은 조건식입니다. 쿼리에 의해 검색된 모든 행에 대한 특정 값을 반환해야 합니다. 즉, 다음 행에 대한guest.vip <> 1.

현재 상태로는 행동이 정의되지 않았습니다. 의견이 말해주듯이 일부 DBMS에서 예상되는 답변을 생성합니다. O.P.에 따르면 다른 DBMS에서는 그렇지 않습니다.저는 DBMS가 예상된 답을 만들어내고 있다고 생각합니다.CASENull 다음에 그음에다.Count( )Null을 무시하고 있습니다.이것은 더 끔찍한 결과 중 하나입니다.NullSQL.

@ Amorim의 @ Fábio Amorim의 논평따르면에그래서,따르▁per,CASE 합니다.ELSECount( )도움이 되지 않는 결과를 제공하므로,ELSE0그리고.Sum( )그자리의 1또는0:

SELECT slotguest.FK_SlotNo, Sum(CASE WHEN guest.vip = 1 THEN 1 ELSE 0 END) AS guest_count 
FROM guest 
INNER JOIN slotguest ON guest.GuestID = slotguest.FK_guest 
GROUP BY slotguest.FK_SlotNo;

언급URL : https://stackoverflow.com/questions/62757262/sql-count-erratic-behaviour