MySQL スライディング集計/年初来集計の原理と使用例の分析

この記事では、例を使用して、MySQL スライディング集計/年初来集計の原理と使用方法を説明します。ご参考までに、詳細は以下の通りです。

スライディング集計は、スライディングウィンドウ範囲内のデータを順番に集計する操作です。累積集計とは異なり、スライディング集計では開始位置から現在の位置までのデータはカウントされません。

ここでは、過去 3 か月間の従業員の月間注文数をカウントする例を取り上げて、スライディング集計を紹介します。

スライディング集約ソリューションと累積集約ソリューションの主な違いは、結合条件が異なることにあります。スライディング集計条件は b.ordermonth <= a.ordermonth ではなくなりましたが、b.ordermonth は過去 3 か月間の月数より大きく、現在の月より小さくなります。したがって、スライディング集計ソリューションのSQL文は次のようになります。

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

結果は以下のとおりです

このソリューションは、3 か月間のスライディング集計を返しますが、各ユーザーには過去 2 か月と 3 か月未満の集計が含まれます。 3か月以上前の集計のみを返し、3か月未満の集計は返さない場合は、HAVINGフィルターを使用してフィルタリングできます。フィルタリング条件は、MIN(b.ordermonth)=DATE_ADD(a.ordermonth, INTERVAL -2 MONTH)です。たとえば、

選択
 a.empid、
 a.ordermonth AS ordermonth、
 今月のAS数量、
 SUM(b.qty) AS 合計、
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
empordersから
INNER JOIN emporders b
 オン a.empid=b.empid
 AND b.ordermonth > DATE_ADD(a.ordermonth, 間隔 -3 か月)
 かつ、b.ordermonth <= a.ordermonth
DATE_FORMAT(a.ordermonth,'%Y')='2015' かつ DATE_FORMAT(b.ordermonth,'%Y')='2015' かつ a.empid=1 の場合
GROUP BY a.empid、DATE_FORMAT(a.ordermonth、'%Y-%m')、a.qty
HAVING MIN(b.ordermonth)=DATE_ADD(a.ordermonth, INTERVAL-2 MONTH)
ORDER BY a.empid,a.ordermonth

結果は以下のとおりです

年初来集計は、現在の年の集計のみがカウントされることを除いて、スライディング集計に似ています。唯一の違いは、下限がどこから始まるかです。年初来問題では下限は年の最初の日ですが、スライディング集計の下限は N か月の最初の日です。したがって、年初来の問題の解決法は以下に示され、得られた結果は

選択
 a.empid、
 DATE_FORMAT(a.ordermonth, '%Y-%m') AS ordermonth,
 今月のAS数量、
 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 >= DATE_FORMAT(a.ordermonth, '%Y-01-01')
  かつ、b.ordermonth <= a.ordermonth
  AND DATE_FORMAT(b.ordermonth,'%Y')='2015'
GROUP BY a.empid、a.ordermonth、a.qty
ORDER BY a.empid,a.ordermonth

結果は以下のとおりです

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

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

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