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+echarts でプログレスバーのヒストグラムを実現

この記事では、プログレスバーヒストグラムを実現するためのvue+echartsの具体的なコードを参考...

ネイティブ js カスタム右クリック メニュー

この記事の例では、参考のためにjsカスタム右クリックメニューの具体的なコードを共有しています。具体的...

Vue3 における provide と inject の使用法と原則

序文:親コンポーネントと子コンポーネント間でデータを渡す場合、通常は props と emittin...

読めばわかるVueの8つのヒント

1. v-forでは常に:keyを使用するデータを操作する必要があるときにプログラムを一定かつ予測可...

Mac で Docker を使用して Oracle をデプロイする方法

Mac で Docker を使用して Oracle をデプロイする方法まずdockerをインストール...

Tomcatのデフォルトパスの設定によって発生するプロジェクトURLの競合を解決する方法の詳細な説明

序文Tomcat は優れた Java コンテナですが、避けられない小さな落とし穴もいくつかあります。...

CSS を使用して親コンテナ div を img 画像で埋め、コンテナのサイズを調整する方法

ページに複数の画像を導入すると、画像のサイズがばらつくことがあります。しかし、それらを一貫したサイズ...

Baidu Maps を Web ページに埋め込み、Baidu Maps API を使用してマップをカスタマイズする詳細な手順

ウェブページにBaiduマップを挿入するBaidu Maps を自分の Web ページに追加したい場...

JavaScript が重複したネットワークリクエストを防ぐ方法の例

序文開発中は、インターフェース要求の繰り返しによってさまざまな問題が発生することがよくあります。ネッ...

Linux で gdb を使用してコア ファイルをデバッグする方法

1.コアファイルプログラム実行中にセグメンテーション エラー (コア ダンプ) が発生すると、プログ...

Ubuntu 18.04 コマンドでタッチパッドを無効/有効にする

Ubuntu では、ショートカット キーでタッチパッドをオフにできない状況によく遭遇します。この問題...

MySQL 5.7.31 64 ビット無料インストール版チュートリアル図

1. ダウンロードダウンロードアドレス: https://dev.mysql.com/get/Dow...

完全なMySQL学習ノート

目次MyISAM と InnoDBパフォーマンスの低下と SQL の速度低下の理由: MySQL 実...

Ubuntu で nvidia グラフィック ドライバーをインストールする (簡単なインストール方法)

Ubuntu で nvidia グラフィック カード ドライバーをインストールします。同じ方法で ...

Videojs+swiper が Taobao の商品詳細カルーセルを実現

この記事では、Taobao商品詳細のカルーセルを実現するためのvideojs+swiperの具体的な...