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

1. はじめに

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

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

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

MySQLバージョン:

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

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

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 |
+-------+---------------+

（２）前１時間のデータ量を取得する

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 をよろしくお願いいたします。

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