MySQLで時間別データと最後の時間別データの差をクエリするアイデアの詳細な説明

MySQLで時間別データと最後の時間別データの差をクエリするアイデアの詳細な説明

1. はじめに

要件は、特定の時間範囲内で、1 時間ごとのデータと前の 1 時間ごとのデータの差と比率を取得することです。最初はとても簡単なsqlだと思っていましたが、2分ほど考えた後、簡単ではないことがわかりました。インターネット上で参考になる解決策が見つからなかったので、自分でゆっくり分析することしかできませんでした。

最初は全くわからなかったので、 DBAクラスメイトに尋ねました。DBA DBAやり方がわからないと言って、計算を行うphpスクリプトを書くように言いました。これはちょっとやりすぎでした。一時的にデータを確認したかっただけで、 sqlを使って直接見つけられないとは信じられませんでした。よし、やってみることにします。

ここのブロガーは、愚かな方法を使ってそれを達成しています。もっと簡単な方法があれば、遠慮なくアドバイスをください。コメント欄でお待ちしています!

MySQLバージョン:

mysql> バージョンを選択します();
+---------------------+
| バージョン() |
+---------------------+
| 10.0.22-MariaDB ログ |
+---------------------+
セット内の 1 行 (0.00 秒)

2. 各時間と前の時間の差を照会する

1. 要件を分割する

後続の組み合わせを容易にするために、ここで個別にクエリを実行して、データがどれだけあるかを確認しましょう。

(1)時間別データ量の取得

表示の都合上、ここでは直接結合しており、 01-12時までのデータのみが表示されています。これはbugではありません。 。

select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days;
+-------+---------------+
| 数値 | 日数 |
+-------+---------------+
| 15442 | 2020-04-19 01 |
| 15230 | 2020-04-19 02 |
| 14654 | 2020-04-19 03 |
| 14933 | 2020-04-19 04 |
| 14768 | 2020-04-19 05 |
| 15390 | 2020-04-19 06 |
| 15611 | 2020-04-19 07 |
| 15659 | 2020-04-19 08 |
| 15398 | 2020-04-19 09 |
| 15207 | 2020-04-19 10 |
| 14860 | 2020-04-19 11 |
| 15114 | 2020-04-19 12 |
+-------+---------------+

(2)前1時間のデータ量を取得する

select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days;
+-------+---------------+
| 数値1 | 日数 |
+-------+---------------+
| 15114 | 2020-04-19 01 |
| 15442 | 2020-04-19 02 |
| 15230 | 2020-04-19 03 |
| 14654 | 2020-04-19 04 |
| 14933 | 2020-04-19 05 |
| 14768 | 2020-04-19 06 |
| 15390 | 2020-04-19 07 |
| 15611 | 2020-04-19 08 |
| 15659 | 2020-04-19 09 |
| 15398 | 2020-04-19 10 |
| 15207 | 2020-04-19 11 |
| 14860 | 2020-04-19 12 |
+-------+---------------+

知らせ:

1) 前の時間のデータを取得するには、date_sub() 関数を使用します。date_sub(date, interval -1 hour) は、日付パラメータの前の時間を取得することを意味します。詳細については、マニュアルを参照してください: https://www.w3school.com.cn/sql/func_date_sub.asp
2) ここで最も外側にネストされた date_format は、上記とフォーマットの一貫性を保つためのものです。この date_format を追加しないと、照会された日付フォーマットは 2020-04-19 04:00:00 となり、比較には不便です。

2. これら2つのデータを組み合わせて

nums、nums1、days、days1 を選択 
から 
(count(*) を nums、date_format(log_time,'%Y-%m-%d %h') を days としてテストから選択し、1 かつ log_time >='2020-04-19 00:00:00' かつ log_time <= '2020-04-20 00:00:00' の場合、group by days) を m として選択します。
(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n;

+-------+-------+---------------+---------------+
| 数値 | 数値1 | 日数 | 日数1 |
+-------+-------+---------------+---------------+
| 15442 | 15114 | 2020-04-19 01 | 2020-04-19 01 |
| 15442 | 15442 | 2020-04-19 01 | 2020-04-19 02 |
| 15442 | 15230 | 2020-04-19 01 | 2020-04-19 03 |
| 15442 | 14654 | 2020-04-19 01 | 2020-04-19 04 |
| 15442 | 14933 | 2020-04-19 01 | 2020-04-19 05 |
| 15442 | 14768 | 2020-04-19 01 | 2020-04-19 06 |
| 15442 | 15390 | 2020-04-19 01 | 2020-04-19 07 |
| 15442 | 15611 | 2020-04-19 01 | 2020-04-19 08 |
| 15442 | 15659 | 2020-04-19 01 | 2020-04-19 09 |
| 15442 | 15398 | 2020-04-19 01 | 2020-04-19 10 |
| 15442 | 15207 | 2020-04-19 01 | 2020-04-19 11 |
| 15442 | 14860 | 2020-04-19 01 | 2020-04-19 12 |
| 15230 | 15114 | 2020-04-19 02 | 2020-04-19 01 |
| 15230 | 15442 | 2020-04-19 02 | 2020-04-19 02 |
| 15230 | 15230 | 2020-04-19 02 | 2020-04-19 03 |

この組み合わせは、プログラム内のネストされたループ効果に似ていることがわかります。これは、 numsが外側のループで、 nums1メモリ ループであることと同等です。ループするときは、まずnumsの値を使用して、 nums1のすべての値と一致させます。 phpプログラムと同様:

foreach($arr as $k=>$v){
 foreach($arr1 を $k1=>$v1 として){

 }
}

この場合、プログラムを書くときに通常行うように、2 つのループ配列で同じ値を見つけて、その差を計算できますか?ここでの日付はまったく同じであり、比較の条件として使用できることは明らかです。

3. 使用例…差を計算するタイミング

(case when days = days1 then (nums - nums1) else 0 end) を diff として選択します
から 
(count(*) を nums、date_format(log_time,'%Y-%m-%d %h') を days としてテストから選択し、1 かつ log_time >='2020-04-19 00:00:00' かつ log_time <= '2020-04-20 00:00:00' の場合、group by days) を m として選択します。
(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n;

効果:
+------+
| 差分 |
+------+
| 328 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
|-212 |
| 0 |
| 0

ここでは、 case..whenを使用して 2 つの日付が等しい場合の差を計算していることがわかります。これは、 phpプログラムに似ています。

	foreach($arr as $k=>$v){
 foreach($arr1 を $k1=>$v1 として){
 $k == $k1の場合{
  //違いを見つける}
 }
}

その結果、 0が多く、計算結果もいくつかあることがわかります。しかし、これらの 0 を除外すれば、希望はあるようです。

4. 結果が0の部分を除外し、最終データを比較する

ここで、 havingクエリ結果をフィルタリングするために使用されます。 having句を使用すると、データのグループをフィルターできます。SQL sql最後にはgroup by句がありませんが、両方のサブクエリにはgroup by句があります。理論上は、データをフィルターするにはhaving句を使用するのが最も適切です。試してみましょう。

(case when days = days1 then (nums1 - nums) else 0 end) を diff として選択します
から 
(count(*) を nums、date_format(log_time,'%Y-%m-%d %h') を days としてテストから選択し、1 かつ log_time >='2020-04-19 00:00:00' かつ log_time <= '2020-04-20 00:00:00' の場合、group by days) を m として選択します。
(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-04-19 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n having diff <>0;

結果:
+------+
| 差分 |
+------+
|-328 |
| 212 |
| 576 |
|-279 |
| 165 |
|-622 |
|-221 |
|-48 |
| 261 |
| 191 |
| 347 |
|-254 |
+------+

ここで計算結果を確認できるので、比較してみましょう。以下は手動でリストしたデータの一部です。

現在の時間と最後の時間の差: 現在の時間 - 最後の時間

この時間 最後の時間 差
15442 15114 -328
15230 15442 212
14654 15230 576
14933 14654 -279
14768 14933 165

確かに差分がうまく取得できていることがわかります。差の比率を取得したい場合は、 case when days = days1 then (nums1 - nums)/nums1 else 0 end

5. この時間と前時間のデータの減少を取得し、各減少範囲の数を表示します。

範囲を分割するための条件を追加することで元のcase..whenステートメントを拡張し、最後に減少する範囲に従って値を合計してgroup byを実行できます。このsql少し複雑です。必要に応じて変更してください。実際のテストで使用できます。

ケースを選択 
days = days1 かつ (nums1 - nums)/nums1 < 0.1 の場合、0.1
days = days1 かつ (nums1 - nums)/nums1 > 0.1 かつ (nums1 - nums)/nums1 < 0.2 の場合、0.2
days = days1 かつ (nums1 - nums)/nums1 > 0.2 かつ (nums1 - nums)/nums1 < 0.3 の場合、0.3
days = days1 かつ (nums1 - nums)/nums1 > 0.3 かつ (nums1 - nums)/nums1 < 0.4 の場合、0.4
days = days1 かつ (nums1 - nums)/nums1 > 0.4 かつ (nums1 - nums)/nums1 < 0.5 の場合、0.5
days = days1 かつ (nums1 - nums)/nums1 > 0.5 の場合、0.6
 そうでなければ 0 終了 diff、count(*) として diff_nums
から 
(count(*) を nums、date_format(log_time,'%Y-%m-%d %h') を days としてテストから選択し、1 かつ log_time >='2020-03-20 00:00:00' かつ log_time <= '2020-04-20 00:00:00' の場合、group by days) を m として選択します。
(select count(*) as nums1,date_format(date_sub(date_format(log_time,'%Y-%m-%d %h'),interval -1 hour),'%Y-%m-%d %h') as days1 from test where 1 and log_time >='2020-03-20 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days1) as n group by diff having diff >0;

結果:

+------+------------+
| 差分 | 差分番号 |
+------+------------+
| 0.1 | 360 |
| 0.2 | 10 |
| 0.3 | 1 |
| 0.4 | 1 |
+------+------------+

結論

1. sqlは実際にはプログラム コードに似ています。要件を分割し、段階的に組み合わせることで、ほとんどの要件を達成できます。初めに臆病になってしまえば、当然書けなくなります。
2.ただし、複雑な計算を記述するためにsqlを使用することは一般的に推奨されません。プログラムを使用して記述する方が高速です。SQL sql複雑になるほど、効率は低下します。
3. DBAクラスメイトは頼りにならないこともあるので、自分自身に頼らざるを得ない

補足紹介: MySQLデータベースの時間と実際の時間の差は8時間です

url=jdbc:mysql://127.0.0.1:3306/somedatabase?characterEncoding=utf-8&serverTimezone=GMT%2B8

データベース設定の後に&serverTimezone=GMT%2B8を追加します。

これで、MySQL で時間別データと以前の時間別データの差分をクエリする方法についての説明は終わりです。MySQL の時間別データ差分に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • 列の隣接する2つの行の差を計算するMySQLメソッド

<<:  ランダム点呼 Web ページを実装するための JavaScript

>>:  JavaScript におけるさまざまなバイナリオブジェクトの関係の詳細な説明

推薦する

VueのTodoListケースの詳しい説明

<テンプレート> <div id="ルート"> <...

JS配列ループ方式と効率分析の比較

配列メソッドJavaScript には多くの配列メソッドが用意されています。次の図は、ほとんどの配列...

favicon.ico についていくつか注意点があります (ルートディレクトリに置くのがベストです)

任意のウェブページを開きます。例: http://www.baidu.com/ ブラウザのタブのヘッ...

Tomcatのクラスロードメカニズムを説明する記事

目次- 序文 - - JVM クラスローダー - 1. JVMクラスローダー2. クラスローダーのソ...

MySQLにおけるテーブルインデックスの定義方法と導入

概要インデックスは、テーブル内の 1 つ以上の列に基づいて DBMS によって特定の順序で作成される...

UniappはBaidu Voiceを使用して録音をテキストに変換する機能を実現

3日間さまざまな困難に遭遇した後、ようやくこの機能を実現しました。正常に実装できる方法を見つける前に...

MySQLの比較演算子正規表現マッチングREGEXPの使用の詳細な説明

1. データを初期化する `test_01` が存在する場合はテーブルを削除します。 テーブル「te...

JS はデータ URL をどのように理解するのでしょうか?

目次概要データ URL の使用を開始するデータURL構文複数言語の文字列のBase64エンコードとデ...

Windows で mysql-8.0.18-winx64 をインストールするチュートリアル (画像とテキスト付き)

1. インストールパッケージをダウンロードするインストール パッケージは次の場所にあります:参考:...

Nginx でアンチホットリンクを設定するための手順を完了する

必要:通常、サイト側は、ウェブサイト上の動画や写真が盗まれるのを防ぎたいと考えています。結局のところ...

要素タイムラインの実装

目次コンポーネント - タイムラインカスタムノードスタイルカスタムタイムスタンプコンポーネント - ...

mysql-8.0.17-winx64 のデプロイメント方法

1. 公式サイトからmysql-8.0.17-winx64をダウンロードし、Zipファイル形式を選択...

Vue ボタンの権限制御の導入

目次1. 手順1. ボタンの権限を定義する2. ストアを定義する3. 権限指示を作成する4. パーミ...

自己終了XHTMLタグを書くときに注意すべきこと

XHTML の img タグはいわゆる自己終了タグであり、XML では完全に合法です。 XHTMLの...

Dockerデーモンのセキュリティ設定項目の詳細な説明

目次1. テスト環境1.1 CentOS 7をインストールする1.2 Docker CE 19.03...