SUM(Distinct)の問題
以前は、COUNT(Distinct)を使用して重複したテーブルの列をカウントできることを学びました。SUM(Distinct)はどう すべての重複ではなく、別個の送料の値を合計したいだけなので、それはトリックをするべきだと思われます。 それを試してみましょう:
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)
そして、そこにあります！ 私たちは問題を解決したようです: 私達の順序のテーブルに振り返ってみると、私達は顧客ごとのTotalShipping費用が今正しく見ることができることを見ることができます。
しかし、待ってください。.. それは実際には間違っています！
これは多くの人々が問題を抱えている場所です。 はい、データは正しいように見えます。 そして、この小さなサンプルのために、それだけでランダムに正しいことが起こる。 しかし、SUM(DISTINCT)はCOUNT(DISTINCT)とまったく同じように機能します：単純に合計される資格のあるすべての値を取得し、重複する値をすべて削除してから結果を合 しかし、いくつかの主キー列に基づいて行を重複させるのではなく、重複した値を排除しています！ 送料40がorderID＃1に属し、送料30がOrderID＃2に属していることは気にしません。
式SUM(Distinct ShippingCost)は基本的に次のように評価されます:
-
Customer ABC:40,40,30,30,30,25
Customer DEF:10 - DISTINCTが要求されたため、これらのリストから重複した値が削除されます。
Customer ABC:40,40,30,30,30,25
Customer DEF:10 - DISTINCTが要求されたため、これらのリストから重複した値が削除されます。
Customer DEF:40,40,30,30,30,25
Customer DEF: 10 - そして今、それは残りの値を合計することによってSUM()を評価することができます：
Customer ABC: 40+30+25 = 95
顧客DEF: 10 = 10
コンセプトを取得していない場合は、まだ問題が表示されない場合があります。 実際には、この時点で、多くの人が行うことはありません。 彼らはSUM（x）が正しいことができない膨大な数を返すことを知っているので、それを微調整してSUM（DISTINCT x）を試してみると、値ははるかに合理的に見え、最初は しかし、SQLは正しくありません; これは、現在、顧客のための二つの注文が同じ送料を持っていないという事実に依存しています。
別の順序を追加して実証しましょう:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
それを実行すると、単に顧客DEFのための別の注文、shipping10の送料、Order125のための一つのOrderDetailアイテムが追加されます。 次に、同じSELECTを再度実行して、この新しい順序が結果にどのように影響するかを確認しましょう:
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)
ItemCount、OrderAmount、OrderCountの各列は見栄えがよくなります。 しかし、DEFのTotalShippingコストはまだshows10を示しています！ どうしたんだ!?
SUM(Distinct)の仕組みを覚えておいてください！ 関数に渡された別個の値を取得し、重複を排除するだけです。 DEFの両方の注文にはshipping10の送料があり、SUM（Distinct ShippingCost）は2つのOrders10の値が異なる注文のためのものであることを気にせず、10が顧客のために複製されているこ したがって、10+10=20である必要がありますが、これらの2つの注文の合計送料として10の値を返します。 私たちの結果は今間違っています。 それの長いものと短いものはこれです：決してSUM（Distinct）を使用しないでください！ それは通常、ほとんどの状況で論理的な意味をなさない; それのための時間と場所があるかもしれませんが、それは間違いなくここにはありません。
派生テーブルの要約
だから、これをどのように修正するのですか？ すべてのテーブルを一緒に結合しようとせず、物事がうまくいくまでSUM()とGROUP BYとDISTINCTをほぼランダムに追加してください。
だから、顧客ごとの合計を心配する前に、戻って、注文ごとの合計を返すことに焦点を当ててみましょう。 最初に注文ごとの合計を返すことができれば、顧客ごとにそれらの注文の合計を単純に要約することができ、必要な結果が得られます。 Orderdetailsテーブルを要約して、ItemCountと合計注文金額を使用して、注文ごとに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)
素晴らしく、簡単、確認すること容易事はよく見ます。 OrderIDをグループ化しているため、これらの結果にはOrderIDの仮想主キーがあると言えます。 実際には、ここでは常に覚えておくべき別の基本的なルールです:
GROUP BY句を持つSELECTの仮想主キーは、常にGROUP BYに記載されている式になります。
これで、そのSQLステートメントとそれらの結果を取得し、それらを独自の派生テーブルにカプセル化することができます。 Ordersテーブルから派生テーブルとして前のSELECTに結合すると、次のようになります:
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)
それらの結果を調べてみましょう。 どこにも重複する行や値はありません。 これは、派生テーブルにOrderIDの仮想主キーがあるため、Ordersから派生テーブルに結合すると重複が生成されることがないためです。 これは、親テーブルを子テーブルに関連付けるときに重複を回避するための非常に便利で簡単な手法です。 親テーブルの行は複製されることはなく、正確に要約できます。
これで、注文ごとの合計ItemCountと注文ごとの合計OrderAmountが得られました。 そして、これらの結果を合計すると、ShippingCost列は複製されないため、正常になることがわかります。 明瞭のための必要性無し。 実際には、通常のCOUNT(*)式を使用して、顧客ごとの注文の合計数を取得することもできます。したがって、以前のSQLに「GROUP BY Customer」を追加し、集計関数で必要なものを計算し、要約しない列（OrderIDなど）を削除するだけです。 また、この時点で、Customerごとの合計ItemCountがCOUNT(*)式ではなくなったことに気付くこともあります; これは、派生テーブルから返されるItemCount値の単純なSUM()です。
:
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)
そして、そこにあなたはそれを持っています！ 私たちはデータを調べ、結合の意味を論理的に考慮し、問題をより小さな部分に分割し、迅速で効率的で正確であることがわかっている非常に単純な解
テーブルの追加要約SELECT
物事を終えるために、私たちのスキーマにも顧客のテーブルがあると仮定します:
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'
… そして私達はまた私達の前の結果の各顧客の名前、都市および州を戻したいと思います。 これを行う1つの方法は、既存の結合にCustomersテーブルを追加し、次にSELECT句にcustomer列を追加することです。 ただし、すべての顧客列をGROUP BYに追加しない限り、表示するすべての列をグループ化または要約する必要があることを示すエラーメッセージが表示され Name、City、StateのCOUNT（）またはSUM（）を計算しようとしていないため、これらの列を集約式でラップするのは意味がありません。 したがって、必要な結果を得るには、それらをすべてGROUP BY句に追加する必要があるようです:
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)
技術的には、それは機能しますが、GROUP BYのすべての顧客列を一覧表示するのはばかげているようです。.. 結局のところ、私たちは顧客の各属性ではなく、顧客にグループ化していますよね？
興味深いのは、解決策はすでに話したものであり、同じ手法が適用されるということです: CustomerはOrdersと一対多の関係を持つため、CustomersをOrdersに結合すると、Customerごとに行が重複するため、Customerテーブルのすべての列が結果に複製されることがわかります。 これは、OrderDetailsに注文を結合するときに適用されるシナリオとまったく同じであることに気付くかもしれません。 だから、私たちはこのような状況を同じように処理します！ 最初に顧客別の注文を派生テーブルで要約し、それらの結果を顧客テーブルに結合します。 つまり、Customerテーブルの列はまったく複製されず、それらをすべてGROUP BY式に追加する必要はありません。 これは私達のSQLをきれい、整頓されていて、論理的に健全保つ。
だから、私たちの最終結果は次のようになります:
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)
結論
この2つのパートシリーズが、GROUP BYクエリの理解に少し役立つことを願っています。 複数のテーブルを結合するときに、結果セットの仮想主キーが何であるかを特定して理解し、どの行が複製されているかを認識することが重要です。 さらに、COUNT(Distinct)は便利ですが、SUM(Distinct)は非常にまれにしか使用されないことに注意してください。一般に、SUM()に必要な値が重複していることがわかった場合は、それらの重複の原因となっているテーブルを別々に要約し、派生テーブルとして結合します。 これにより、問題をより小さなステップに分解し、各ステップの結果をテストして検証することもできます。 GROUP BYは非常に強力な機能ですが、誤解や悪用もされており、それを活用する最も簡単な方法は、SQLをより小さく、より単純な部分から、より大きく、より複雑