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

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データベース設計に役立つことを願っています。

以下もご興味があるかもしれません:
  • MySQL の集計関数 count の使用法とパフォーマンスの最適化テクニック
  • MySQLでよく使われる集計関数の詳細な説明
  • MySql の集計関数に条件式を追加する方法
  • php+mysql オープンソース XNA 集計プログラムがダウンロード用にリリースされました
  • Mysql は非集計列を選択できません
  • MySQL クエリのソートとクエリ集計関数の使用法の分析
  • MySQL の単一テーブル クエリ操作例の詳細な説明 [構文、制約、グループ化、集計、フィルタリング、並べ替えなど]
  • MySQL 継続的集計の原理と使用法の分析
  • MySQL スライディング集計/年初来集計の原理と使用例の分析

<<:  Nginxでネットワーク分離を解決した実践記録を詳しく解説

>>:  HTMLフォーム送信方法のケーススタディ

推薦する

Linux がますます人気になっている 10 の理由

Linux はますます多くのユーザーに愛されてきました。なぜ Linux はこれほど急速に発展し、い...

ウェブデザインの教育または学習プログラム

セクションコース内容営業時間1 ウェブデザインの概要2 2 HTML 基本タグとフォーマットタグ 2...

リフレッシュリダイレクトを実現する HTML ヘッドタグメタ

コードをコピーコードは次のとおりです。 <html> <ヘッド> <m...

MySQL 8 の新機能: 非表示のインデックス

背景インデックスは諸刃の剣です。クエリ速度は向上しますが、DML 操作も遅くなります。結局のところ、...

LinuxソースコードからTIME_WAITの期間を分析する

目次1. はじめに2. まずLinux環境を紹介しましょう3. TIME_WAIT状態遷移図4. 継...

MySQLは数百万のシミュレーションデータ操作コードを自動的に挿入します

私はデータベースツールとして Navicat を使用しています。他のものも同様です。 1. Navi...

MySQL InnoDB インデックス拡張の詳細な説明

インデックス拡張: InnoDB は、プライマリ キー列をそのインデックスに追加することで、各セカン...

MySQL における in と exists の違いの詳細な説明

1. 事前に準備する便宜上、ここで 2 つのテーブルを作成し、そこにいくつかのデータを追加します。果...

MySQL 8.0.21 無料インストール版 設定方法 グラフィックチュートリアル

MySQLをインストールする6つの手順(インストールパッケージのみがインストール場所を選択できます)...

史上最も便利な Zookeeper サーバーの構築方法 (推奨)

ZooKeeperとはZooKeeper は、分散アプリケーションに効率的で可用性の高い分散調整サ...

XHTML 入門チュートリアル: XHTML Web ページ画像アプリケーション

<br />適度に画像を追加すると、Web ページがより美しくなります。 画像タグ &l...

Nginx ベースの HTTPS ウェブサイトを設定する手順

目次序文:暗号化アルゴリズム: 1. HTTPS の概要2. NginxはHTTPSウェブサイト設定...

Linuxで現在のスクリプトの実際のパスを取得する方法

1. 現在のスクリプトの実際のパスを取得します。 #!/bin/bash if [[ $0 =~ ^...

MySQLクエリの文字セットの不一致の問題を解決する方法

問題を見つける最近、仕事で問題が発生しました。MySQL データベースにテーブルを作成するときに、ラ...