mysqlは毎時間のデータと前時間のデータの差を調べて考えを実現します。

9538 ワード

mysql データ差

はじめに
必要なのは、ある時間範囲における毎時データと前時間データの差と比率を取得することです。もともとはとても簡単なsqlができると思っていましたが、2分間考えてみると簡単ではないです。ネットでも参考案が見つからないので、自分でゆっくりと分析するしかないです。
最初は構想がないので、DBAさんに聞いてみましたが、DBAさんはできないと言っていました。php脚本を書いて計算させてください。これはちょっとひどいです。データを調べたいだけです。信用しないで直接sqlで調べられないので、パッパを使ってみます。
ブロガーはここで愚かな方法で実現しました。皆さんはもっと簡単な方法があれば、ご指導をお願いします。
mysqlバージョン：


mysql> select version();
+---------------------+
| version()  |
+---------------------+
| 10.0.22-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

二、一時間と前時間の差を調べます。
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;
+-------+---------------+
| nums | 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）前時間のデータ量を取得する


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;
+-------+---------------+
| nums1 | days  |
+-------+---------------+
| 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（日付、interval-1 hour）は、取得日付パラメータの前の時間を表し、具体的な参照マニュアル：https://www.w3school.com.cn/sql/func_date_sub.asp
2）ここの最外層には、ダテがはまっています。formatはフォーマットと上の一致を維持するために、これを追加しないとdate uです。formatの場合、検索した日付フォーマットは2020-04-19 04:00で、比較に不便です。
2、この二つのデータを一緒に置いてみてください。


select nums ,nums1,days,days1 
from 
(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) as 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;

+-------+-------+---------------+---------------+
| nums | nums1 | days  | days1  |
+-------+-------+---------------+---------------+
| 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 as $k1=>$v1){

 }
}

それなら、私達は普段プログラムを書いているように、二つの循環配列の同じ値を見つけて差を求めることができますか？明らかにここの日付は完全に一致しています。比較の条件としてもいいです。
3、caseを使って…whenで差を計算する


select (case when days = days1 then (nums - nums1) else 0 end) as diff
from 
(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) as 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;

  ：
+------+
| diff |
+------+
| 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 as $k1=>$v1){
 if($k == $k1){
  //   
 }
 }
}

結果は0が大量にあり、一部計算された結果もありますが、これらの0を除いては、芝居があるように見えます。
4、フィルタリング結果が0の部分を最終データと比較する
ここでは、クエリーの結果をhavingでフィルタリングする。havingサブ句は、グループ化された各グループのデータを選別してもいいです。私たちのsqlは、一番後ろにgroup byを行っていませんが、二つのサブクエリーの中にgroup byがあります。理論的にはhavingを使ってデータを選別するのが一番いいです。試してみてください。


select (case when days = days1 then (nums1 - nums) else 0 end) as diff
from 
(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) as 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;

  ：
+------+
| diff |
+------+
| -328 |
| 212 |
| 576 |
| -279 |
| 165 |
| -622 |
| -221 |
| -48 |
| 261 |
| 191 |
| 347 |
| -254 |
+------+

      ここで計算結果を見ましたが、大体比較してみます。以下は手動で並べられたデータの一部です。
現在の時間と前の時間の差：現在の時間 -前の時間
この時間前の時間  差
15454 15114  -328
15230 15454  212
14624 15230  576
14933 14624  -279
14768 14933  165
      確かに差を得ることができました。差分値の比率を求めるなら、そのままcase when days = days1 then (nums1 - nums)/nums1 else 0 endでいいです。
5、本時間と前時間のデータの下落幅を取得し、各下降幅の個数を示す。
      もとのcase..whenに基づいて条件区分範囲を増加し続け、最後に降格範囲に従ってgroup byを合計すれば良いということを説明します。このsqlはちょっと面倒くさいです。必要があれば、必要に応じて修正してもいいです。実際のテストは使えます。


select case 
when days = days1 and (nums1 - nums)/nums1 < 0.1 then 0.1
when days = days1 and (nums1 - nums)/nums1 > 0.1 and (nums1 - nums)/nums1 < 0.2 then 0.2
when days = days1 and (nums1 - nums)/nums1 > 0.2 and (nums1 - nums)/nums1 < 0.3 then 0.3
when days = days1 and (nums1 - nums)/nums1 > 0.3 and (nums1 - nums)/nums1 < 0.4 then 0.4
when days = days1 and (nums1 - nums)/nums1 > 0.4 and (nums1 - nums)/nums1 < 0.5 then 0.5
when days = days1 and (nums1 - nums)/nums1 > 0.5 then 0.6
 else 0 end as diff,count(*) as diff_nums
from 
(select count(*) as nums,date_format(log_time,'%Y-%m-%d %h') as days from test where 1 and log_time >='2020-03-20 00:00:00' and log_time <= '2020-04-20 00:00:00' group by days) as 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;

結果:
+--------------++
|diff𞓜diff_nums 124
+--------------++
𞓜 0.1|       360|
𞓜 0.2 124        10|
𞓜 0.3|         1|
𞓜 0.4|         1|
+--------------++
三、まとめ
1、sqlは、実際にはプログラムコードとほぼ同じで、デマンドを分割して一歩ずつ組み合わせて、大部分の需要は実現できる。最初から扇動しましたが、それはもちろん書けません。
2、しかし複雑な計算は、普通はsqlで書くことを勧めないので、プログラムで書くのはもっと速くて、sqlで複雑になるほど、効率が低くなります。
3、DBAさんは時には頼りないです。やはり自分に頼ってください。
追加紹介：MySQLデータベース時間と実際時間の差は8時間です。
url=jdbc:mysql://127.0.0.1:3306/somedatabase?characterEncoding=utf-8&serverTimezone=GMT%2 B 8
データベース配置の後に&serverTimezone=GMT%2 B 8を追加します。
ここでは、mysqlについて、毎時のデータと前の時間のデータの差を調べた文章を紹介します。これに関連して、mysql毎時間のデータの差についてもっと多い内容を紹介します。以前の文章を検索したり、次の関連記事を見たりしてください。これからもよろしくお願いします。

vue+canvasパズルを実現します。

VSL 2008中国語の試用版を正式版の解読方法に変えます。