합계(고유)문제
이전에 중복 된 테이블의 열을 계산하기 위해 카운트(고유)를 사용할 수 있다는 것을 배웠으므로 합계(고유)는 어떻습니까? 그 트릭을 할 것 같다,우리는 별개의 운송 비용 값을 합계하려면 이후,모든 중복. 의 그것을 시도를 제공 할 수 있습니다:
select o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, count(distinct o.OrderID) as OrderCount, sum(distinct o.ShippingCost) as TotalShippingfrom Orders oinner join OrderDetails od on o.OrderID = od.OrderIDgroup by o.CustomerCustomer ItemCount OrderAmount OrderCount TotalShipping ---------- ----------- --------------------- ----------- --------------------- ABC 6 725.0000 3 95.0000DEF 2 350.0000 1 10.0000(2 row(s) affected)
그리고 거기에 있습니다! 우리는 우리의 문제를 해결 한 것 같습니다: 우리의 주문 테이블로 돌아 보면,우리는 고객 당 총 배송 비용이 올바르게 보이는 것을 볼 수 있습니다.
하지만 기다려… 그것은 실제로 잘못!
이것은 많은 사람들이 문제가있는 곳입니다. 예,데이터가 정확 해 보입니다. 그리고,이 작은 샘플,그냥 무작위로 올바른 일이. 그러나 합계(고유)는 카운트(고유)와 정확히 동일하게 작동합니다:합산 할 수있는 모든 값을 얻고 모든 중복 값을 제거한 다음 결과를 합산합니다. 그러나 일부 기본 키 열을 기반으로 중복 된 행이 아닌 중복 값을 제거합니다! 그것은 운송 비용 40 이 주문#1 에 속하고 운송 비용 30 이 주문#2 에 속한다는 것을 신경 쓰지 않습니다.
표현식 합계(고유 배송 비용)는 기본적으로 다음과 같이 평가됩니다:
- 주문에서 주문 세부 정보로 가입 한 후 각 그룹에는 다음 배송비 값이 있습니다.
고객 데프:10 - 고유가 요청되었으므로 해당 목록에서 중복 값이 제거됩니다.
고객 데프:40,40,30,30,30,25
고객 데프:10 - : 10
- 이제 나머지 값을 추가하여 합계를 평가할 수 있습니다.: 40+30+25 = 95
고객 데프: 10 = 10
너가 개념을 얻지 않으면,너는 아직도 문제를 보지 않을지도 모르지 않는다. 사실,이 시점에서,많은 사람들이 결코하지 않습니다. 그들은 그 합계를 참조하십시오(엑스)오른쪽 수 없습니다 거대한 숫자를 반환,그래서 그들은 그것을 조정할 및 합계를 시도(별개의 엑스),그리고 값이 훨씬 더 합리적인 모양,그들은 심지어 처음에 완벽하게 묶어 수,그래서 생산에 떨어져 간다. 그러나,잘못된 것입니다.; 그것은 현재 고객에 대한 두 개의 주문이 동일한 운송 비용이 없다는 사실에 의존하고 있습니다.
다른 순서를 추가하여 시연해 보겠습니다.:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
그 실행은 단순히$125 하나 개의 주문 세부 사항 항목,$10 고객 데프,운송 비용에 대한 또 다른 순서를 추가합니다. 이제,이 새로운 질서가 우리의 결과에 어떤 영향을 주 었는지 확인하기 위해 동일한 선택을 다시 실행합시다:
select o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, count(distinct o.OrderID) as OrderCount, sum(distinct o.ShippingCost) as TotalShippingfrom Orders oinner join OrderDetails od on o.OrderID = od.OrderIDgroup by CustomerCustomer ItemCount OrderAmount OrderCount TotalShipping ---------- ----------- --------------------- ----------- --------------------- ABC 6 725.0000 3 95.0000DEF 3 475.0000 2 10.0000(2 row(s) affected)
항목 수,주문 수 및 주문 수 열이 멋지게 보입니다. 그러나 데프의 총 배송 비용은 여전히$10 를 보여줍니다! 무슨 일이야!?
당신은 그것을 알아낼 수 있습니까? 합계(고유)가 어떻게 작동하는지 기억하십시오! 그것은 단지 함수에 전달 된 고유 값을 취하고 중복을 제거합니다. 데프에 대한 두 주문 모두 배송비가$10 이고 합계(별개의 배송 비용)는 두$10 값이 다른 주문에 대한 것이 아니라 10 이 고객을 위해 중복된다는 것을 알고 있으므로 10 을 한 번만 사용하여 합계를 계산합니다. 따라서 10+10=20 이어야 함에도 불구하고 두 주문에 대한 총 배송 비용으로 10 의 값을 반환합니다. 우리의 결과는 이제 잘못되었습니다. 그것의 길고 짧은 것은 이것입니다:합계(별개의)를 사용하지 마십시오! 그것은 일반적으로 대부분의 상황에서 논리적 이해가되지 않습니다; 거기에 시간과 장소가있을 수 있습니다,하지만 여기에 확실히 아니다.
파생 테이블 요약
그렇다면 어떻게 해결합니까? 한 번에 한 단계 씩 수행하고 모든 테이블을 함께 조인하려고하지 말고 합계()및 그룹화 및 별개를 거의 무작위로 추가 할 때까지 작동합니다.
따라서 고객 당 합계에 대해 걱정하기 전에 한 걸음 뒤로 물러나서 주문 당 합계를 반환하는 데 중점을 둡니다. 우리가 순서 당 합계를 첫째로 돌려보낼 수 있는 경우에,우리는 단순히 고객에 의하여 그 순서 합계를 요약해서 좋 우리가 필요로 하는 결과가 있을 것이다. 항목 수 및 총 주문 금액으로 주문 당 1 행을 반환하는 주문 세부 정보 테이블을 요약해 보겠습니다:
select orderID, count(*) as ItemCount, sum(Amount) as OrderAmountfrom orderDetailsgroup by orderIDorderID ItemCount OrderAmount ----------- ----------- --------------------- 1 2 250.00002 3 375.00003 1 100.00004 2 350.00005 1 125.0000(5 row(s) affected)
니스와 간단한,확인하기 쉬운,상황이 좋아 보인다. 즉,동일한 순서에 대해 중복된 행이 없을 것입니다. 사실,항상 기억해야 할 또 다른 기본 규칙이 있습니다:
그룹별 절이 있는 선택 항목의 가상 기본 키는 항상 그룹별에 명시된 식입니다.
이제 그 결과를 가져 와서 자신의 파생 테이블에 캡슐화 할 수 있습니다. 우리는 파생 테이블로 이전 선택에 주문 테이블에서 조인 경우,우리는 얻을:
select o.orderID, o.Customer, o.ShippingCost, d.ItemCount, d.OrderAmountfrom orders oinner join( select orderID, count(*) as ItemCount, sum(Amount) as OrderAmount from orderDetails group by orderID) d on o.orderID = d.orderIDorderID Customer ShippingCost ItemCount OrderAmount ----------- ---------- --------------------- ----------- --------------------- 1 ABC 40.0000 2 250.00002 ABC 30.0000 3 375.00003 ABC 25.0000 1 100.00004 DEF 10.0000 2 350.00005 DEF 10.0000 1 125.0000(5 row(s) affected)
그 결과를 살펴 봅시다. 중복된 행이나 값은 어디에도 없으며 주문당 정확히 하나의 행이 있습니다. 이 때문에 우리의 파생된 테이블에는 가상 기본 키 주문,그래서 우리의 파생된 테이블에 주문에서 조인 중복 생성 하지 않습니다. 이것은 부모 테이블을 자식 테이블에 연결할 때 중복을 방지하는 매우 유용하고 간단한 기술입니다:파생 테이블에서 먼저 부모의 기본 키로 자식 테이블을 요약 한 다음 부모 테이블에 조인합니다. 그러면 부모 테이블의 행은 중복되지 않으며 정확하게 요약 될 수 있습니다.
이제 주문 당 총 항목 수와 주문 당 총 주문 금액이 있습니다. 그리고 우리는 이러한 결과를 요약하면 결코 중복되지 않기 때문에 배송 비용 열이 잘 될 것임을 알 수 있습니다. 구별 할 필요가 없습니다. 사실,우리는 심지어 고객 당 주문의 총 수를 얻기 위해 일반 카운트(*)식을 사용할 수 있습니다!
따라서 우리는 단순히”고객 별 그룹”을 이전 테이블에 추가하고,집계 함수로 필요한 것을 계산하고,요약하지 않을 열(주문과 같은)을 제거 할 수 있습니다. 또한 이 시점에서 고객당 총 항목 수는 더 이상 개수(*)식이 아니라는 것을 알 수 있습니다; 파생 테이블에서 반환된 항목 개수 값의 단순 합계()입니다.
결과는 다음과 같습니다.:
select o.Customer, count(*) as OrderCount, sum(o.ShippingCost) as ShippingTotal, sum(d.ItemCount) as ItemCount, sum(d.OrderAmount) as OrderAmountfrom orders oinner join( select orderID, count(*) as ItemCount, sum(Amount) as OrderAmount from orderDetails group by orderID) d on o.orderID = d.orderIDgroup by o.customerCustomer OrderCount ShippingTotal ItemCount OrderAmount ---------- ----------- --------------------- ----------- --------------------- ABC 3 95.0000 6 725.0000DEF 2 20.0000 3 475.0000(2 row(s) affected)
그리고 거기 당신은 그것이있다! 우리는 우리의 데이터를 조사,논리적으로 우리의 조인의 의미를 고려,작은 부분으로 아래로 문제를 파산,우리는 신속하고 효율적이며 정확한 것으로 알고 매우 간단한 솔루션으로 끝났다.
더 많은 테이블 추가 요약 선택
작업을 완료하려면 스키마에 고객 테이블이 있다고 가정합니다:
Create table Customers(Customer varchar(10) primary key,CustomerName varchar(100) not null,City varchar(100) not null,State varchar(2) not null)insert into Customersselect 'ABC','ABC Corporation','Boston','MA' union allselect 'DEF','The DEF Foundation','New York City','NY'
… 그리고 우리는 또한 우리의 이전 결과에 있는 각 고객의 이름,도시 및 국가를 돌려보내는 것을 바란다. 이를 수행하는 한 가지 방법은 기존 조인에 고객 테이블을 추가한 다음 선택 절에 고객 열을 추가하는 것입니다. 그러나 모든 고객 열을 그룹별로 추가하지 않으면 표시할 모든 열을 그룹화하거나 요약해야 한다는 오류 메시지가 표시됩니다. 우리는 이름,도시 및 국가의 개수()또는 합계()를 계산하려고하지 않으므로 집계 식에서 해당 열을 래핑하는 것은 의미가 없습니다. 그래서,그것은 우리가 해야 하는 결과 얻을 절에 의해 우리 그룹에 모두 추가 해야 나타납니다:
select o.Customer, c.customerName, c.City, c.State, count(*) as OrderCount, sum(o.ShippingCost) as ShippingTotal, sum(d.ItemCount) as ItemCount, sum(d.OrderAmount) as OrderAmountfrom orders oinner join( select orderID, count(*) as ItemCount, sum(Amount) as OrderAmount from orderDetails group by orderID) d on o.orderID = d.orderIDinner join customers c on o.customer = c.customergroup by o.customer, c.customerName, c.City, c.StateCustomer customerName City State OrderCount ShippingTotal ItemCount OrderAmount---------- -------------------- --------------- ----- ----------- ------------- --------- -----------ABC ABC Corporation Boston MA 3 95.0000 6 725.0000DEF The DEF Foundation New York City NY 2 20.0000 3 475.0000(2 row(s) affected)
기술적으로는 효과가 있지만 그룹의 모든 고객 열을 나열하는 것은 어리석은 것처럼 보입니다… 모든 후,우리는 단지 고객에 그룹화,하지 고객의 속성의 각,오른쪽?
흥미로운 것은 그 해결책이 우리가 이미 이야기 한 것이고 동일한 기술이 적용된다는 것입니다: 고객은 주문과 일대 다 관계가 있기 때문에 고객을 주문에 가입시키면 고객당 행이 중복되므로 고객 테이블의 모든 열이 결과에 복제됩니다. 이 시나리오는 주문 세부 정보에 주문을 참가할 때 적용되는 시나리오와 정확히 동일할 수 있습니다. 그래서,우리는이 상황을 같은 방식으로 처리합니다! 우리는 단순히 파생 테이블에 먼저 고객에 의해 우리의 주문을 요약 한 다음 우리는 고객 테이블에 그 결과를 가입 할 수 있습니다. 즉,고객 테이블의 열이 전혀 속지 않으며 식을 통해 해당 열을 모두 그룹에 추가할 필요가 없습니다. 이를 통해 우리는 깨끗하고 조직적이며 논리적으로 소리를 낼 수 있습니다.
이제 최종 결과는 다음과 같습니다:
select c.Customer, c.customerName, c.City, c.State, o.OrderCount, o.ShippingTotal, o.ItemCount, o.OrderAmountfrom( select o.customer, count(*) as OrderCount, sum(o.ShippingCost) as ShippingTotal, sum(d.ItemCount) as ItemCount, sum(d.OrderAmount) as OrderAmount from orders o inner join ( select orderID, count(*) as ItemCount, sum(Amount) as OrderAmount from orderDetails group by orderID ) d on o.orderID = d.orderID group by o.customer) oinner join customers c on o.customer = c.customerCustomer customerName City State OrderCount ShippingTotal ItemCount OrderAmount---------- -------------------- --------------- ----- ----------- ------------- --------- -----------ABC ABC Corporation Boston MA 3 95.0000 6 725.0000DEF The DEF Foundation New York City NY 2 20.0000 3 475.0000(2 row(s) affected)
결론
이 두 부분 시리즈가 쿼리별로 그룹을 이해하는 데 조금 도움이되기를 바랍니다. 여러 테이블을 조인할 때 결과 집합의 가상 기본 키가 무엇인지 파악하고 이해하고 중복된 행이 있는지 여부를 인식하는 것이 중요합니다. 또한 카운트(고유)가 유용 할 수 있지만 합계(고유)는 매우 드물게 사용되어야합니다.
일반적으로 합계해야 할 값이 중복되었음을 발견하면 해당 중복을 일으키는 테이블을 별도로 요약하고 파생 테이블로 조인합니다. 이렇게하면 문제를 더 작은 단계로 나누고 각 단계의 결과를 테스트하고 검증 할 수 있습니다.
그룹 바이 매우 강력한 기능이지만,또한 오해와 남용,그리고 그것을 활용하는 가장 쉬운 방법은 신중 크고 복잡한 솔루션으로 작은,간단한 부분에서 당신의 데이터베이스를 구축하는 것입니다.