MySQLの累積集計原理と使用例の分析

この記事では、例を使用して、MySQL 累積集計の原理と使用方法を説明します。ご参考までに、詳細は以下の通りです。

累積集計は、各従業員の毎月初めから現在までの累積注文数と平均注文数を返すなど、シーケンスの最初の要素から現在の要素までのデータを集計します。

行番号の問題には 2 つの解決策があります。1 つはサブクエリを使用すること、もう 1 つは結合を使用することです。通常、サブクエリ方式の方が直感的で読みやすくなります。ただし、集計が必要な場合、サブクエリは集計ごとにデータを 1 回スキャンする必要がありますが、接続方法では通常、結果を取得するために 1 回スキャンするだけで済みます。次のクエリは結合を使用して結果を取得します

選択
 a.empid、
 a.ordermonth、a.qty AS thismonth、
 SUM(b.qty) AS 合計、
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
empordersから
INNER JOIN emporders b
  オン a.empid=b.empid
  かつ、b.ordermonth <= a.ordermonth
GROUP BY a.empid、a.ordermonth、a.qty
ORDER BY a.empid,a.ordermonth

2015年の累計注文数のみを照会したい場合は、where条件を追加できます。

DATE_FORMAT(a.ordermonth,'%Y')='2015' かつ DATE_FORMAT(b.ordermonth,'%Y')='2015' の場合

結果は以下のとおりです

また、特定の目標が達成されるまで、各従業員の月間注文のみを返すなど、データをフィルタリングすることもできます。ここでは、各従業員の注文総数が 1,000 に達する前にカウントされると想定します。

ここでHAVINGフィルタを使用してクエリを完成させることができます

選択
 a.empid、
 a.ordermonth、a.qty AS thismonth、
 SUM(b.qty) AS 合計、
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
empordersから
INNER JOIN emporders b
  オン a.empid=b.empid
  かつ、b.ordermonth <= a.ordermonth
DATE_FORMAT(a.ordermonth,'%Y')='2015' かつ DATE_FORMAT(b.ordermonth,'%Y')='2015' の場合
GROUP BY a.empid、a.ordermonth、a.qty
合計が1000未満
ORDER BY a.empid,a.ordermonth

1000 に達した月の状況はここにはカウントされません。統計を取る場合は状況が少し複雑になります。合計 <= 1000 が指定された場合、その月の注文数がちょうど 1000 の場合にのみ統計が取得されます。それ以外の場合、その月の統計は取得されません。したがって、この問題のフィルタリングは別の側面から検討することができます。累計受注数が1000未満の場合、累計受注数と前月の受注数の差が1000未満となります。同時に、受注数が1000を超える最初の月もカウントできます。したがって、このソリューションのSQL文は次のようになります。

選択
 a.empid、
 a.ordermonth、a.qty AS thismonth、
 SUM(b.qty) AS 合計、
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
empordersから
INNER JOIN emporders b
  オン a.empid=b.empid
  かつ、b.ordermonth <= a.ordermonth
DATE_FORMAT(a.ordermonth,'%Y')='2015' かつ DATE_FORMAT(b.ordermonth,'%Y')='2015' の場合
GROUP BY a.empid、a.ordermonth、a.qty
合計数量が 1000 未満
ORDER BY a.empid,a.ordermonth

結果は以下のとおりです

累計注文数が 1000 の月のデータのみを返し、前の月は返さない場合は、上記の SQL ステートメントを変更できます。

さらにフィルタリングし、累計注文数量が 1000 以上という条件を追加します。この問題の SQL ステートメントは次のとおりです。

選択
 a.empid、
 a.ordermonth、a.qty AS thismonth、
 SUM(b.qty) AS 合計、
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
empordersから
INNER JOIN emporders b
  オン a.empid=b.empid
  かつ、b.ordermonth <= a.ordermonth
DATE_FORMAT(a.ordermonth,'%Y')='2015' かつ DATE_FORMAT(b.ordermonth,'%Y')='2015' の場合
GROUP BY a.empid、a.ordermonth、a.qty
合計数量 < 1000 かつ 合計 >= 1000
ORDER BY a.empid,a.ordermonth

結果は以下のとおりです

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアドプロシージャスキル」、および「MySQL データベースロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: