一定期間の日付で集計するSQLの例(例えば「現在値と過去25日間の平均値や標準偏差と比べた結果がほしい」のような場合)

仕事上で一定期間ごとの集計をするSQLを作る必要があったのでご紹介。お役に立てれば。

たとえば 日時(time)、と値(value)という2フィールドを持つテーブルがあったとする。これを当該日付の値と、当該日付から過去25日の平均値を同時に出したい場合(過去25日間の平均値と比べられる結果がほしい)、以下のようにすれば求められる。

SELECT
    A.time --日時
    A.value, -- 現在値
    AVG(B.value), -- 平均値
    STDDEV(B.value) -- 標準偏差値
FROM table_name A
INNER JOIN table_name B -- 同じテーブルをJOINする
ON
    --過去の日時のものを範囲を持たせてJOINの条件とする
    B.time BETWEEN (A.time - INTERVAL 25 DAY) AND A.time
;

まぁSQLを見てもらえればわかると思うけど簡単に解説すると、同じテーブルを結合していて、結合の際に結合条件であるtimeフィールドに幅を持たせて結合させている点がポイントだ。

これを使えば、わざわざスクリプトなどでループをまわしながら計算させなくとも、SQL一発で結果が得られる。

なお、このSQLはMySQL用なのでほかのDBMSでどうやるかは自分でしらべてくれろ。INTERVALのやり方だったりJOIN句の書き方だったりに違いがあるだけで基本的なアイディアは使えると思う。

月単位で集計、とか、年単位で集計、などはほかにいろいろ紹介しているページはあったものの、過去25日間の平均値(や標準偏差など)と現在値の比較を一発のSQLで実行する方法が探しても無かったので書いてみた。

投稿日:
カテゴリー: MySQLSQL

Windows版 MySQL5.7 64bitのインストーラは無いが32bit版インストーラに64bit版も含まれている。

Windows版のMySQL5.7 64bit専用インストーラはないのだけど、MySQL5.7 32bitインストーラに64bit版も含まれているっぽい。

MySQL5.7 32bit版インストーラダウンロードサイト
http://dev.mysql.com/downloads/mysql/

※オラクルのユーザ登録(無料)をしてからログインが必須

MySQL 5.7 32bit版インストーラをたたいて「custom」を選ぶとインストールするものを選べるので、そこから64bit版を選べばおk。

あといままでだとZIP版(ZIP Archive)で適当にINIファイル設定するだけで動いたんだけど、なぜか失敗。。。ちゃんと調べれば動かせるんだろうけど、メンドクサイからインストーラで入れちゃった。

蛇足だけど、MySQL Workbench 6.3.6 をインストールする際には以下の2つが必要になるのであらかじめ入れておこう。

MySQL Workbench 6.3.6 ダウンロードサイト
http://dev.mysql.com/downloads/workbench/

※こちらも要ログイン(無料)

MySQLのAuto Increment値をリセットする

もうすでにググってるから知っているだろうけど、

ALTER TABLE [テーブル名] AUTO_INCREMENT = 1;

のSQL文でAuto Increment値の設定はリセットされる。

ただし、他のブログ情報では、「先に全レコードを削除しないと再設定できない」とか「MyISAMでは最大値+1に変更され、InnoDBでは変更されない」など書いてある。

しかし、オレの手元にある MySQL Ver 5.5.18 でテストした限り、InnoDBでもAuto Increment値は最大値+1に再設定された

どのバージョンからこれに対応したんかね?

無事にAuto Increment値のリセットができてるからまぁいいや。

Gladinet Cloud Desktop が Dropbox的でイイ感じかもしれない。

窓の杜なんかで紹介されていたクラウドスペースやオンラインディスク、FTPなどを自分PCのローカルドライブとしてマウントできちゃうソフトです。

これが意外とDorpbox的でなかなかヨサゲ。

というのは、

まず一つ目は、PCを起動したら自動的にマウントしてくれる。

二つ目は、ファイルを変更したら自動的に同期をとってくれる。

三つ目は、FTPを含め色々なサービスをマウントできる。

インターネット環境には常時つながっているけど、WEBアプリの開発環境を用意できない、とかそういう場合にかなり便利。格安のレンタルサーバとか普通にapache+MySQL+PHPがインストールされているし、FTPも解放されている。だからGladinet Cloud DesktopでFTP接続してマウントしてしまえば、開発環境の出来上がり。デバッグしたければ xdebug をONにすればいい(ロリポップにはxdebugが入ってる)。わざわざコーディングしたファイルをFTPでアップする、なんて作業しなくていい。

う~ん、イイ感じ。

ダウンロードはここから↓。
http://www.gladinet.com/p/download_starter_direct.htm

インストールは・・・適当でおk・・不安ならここらへん↓みればおk。
http://freesoft-100.com/pasokon/gladinet-cloud-desktop.html

「Windoes+Apache+MySQL+PHP」でのインストール設定手順メモ

「Windows + Apache + MySQL + PHP」の環境を構築する際に、いつも複数のサイトを見ながら設定していたのでまとめてみた。ただし、最低限のことしかしていない、且つ、開発環境を想定した設定になっているので、運用環境を構築する際には厳に注意されたし。なお、以下の手順は「apache2.2」、「php5.3」におけるインストール設定手順を記載している。

apacheの設定(httpd.confの設定)

標準ポートの変更

Listen 8080
↓
Listen 80

ドキュメントルートの変更

DocumentRoot "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs"
↓
DocumentRoot "C:/www"

<Directory "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs">
↓
<Directory "C:/www">

日本語を優先的に表示

#Include conf/extra/httpd-languages.conf
↓
Include conf/extra/httpd-languages.conf

日本語を優先的に表示2

※C:¥[apacheインストールフォルダ]¥conf¥extra¥httpd-languages.confを修正

#DefaultLanguage nl
↓
DefaultLanguage ja

LanguagePriority en ca cs da de el eo es et fr he hr it ja ko ltz .....
↓
LanguagePriority ja en ca cs da de el eo es et fr he hr it ko ltz .....

AddDefaultCharset utf-8 (※行追加)

CGIおよびPHPの実行許可

#AddHandler cgi-script .cgi
↓
AddHandler cgi-script .cgi

PHPモジュールの追加(※以下の行をすべて追加)

LoadModule php5_module "C:¥php¥php5apache2_2.dll"
PHPIniDir "C:¥php"
AddType application/x-httpd-php .php

PHPの設定(php.iniの設定)

環境変数へパスを追加

PHPインストールフォルダをWindowsの環境変数へ登録する。

php.iniの作成

php.ini-developmentかphp.ini-productionをリネームしてphp.iniを作成。

インクルードパスの修正(PEARのパスを追加)

※PEAR等のライブラリを使用しない場合は不要

※PEARのインストールは【ここらへん】をみながらやる

;include_path = ".;c:¥php¥includes"
↓
include_path = ".;c:¥php¥includes;c:¥php¥pear"

ドキュメントルートの指定

doc_root =
↓
doc_root = "c:¥www"

extensionディレクトリの指定

extension_dir = "./"
↓
extension_dir = "c:¥php¥ext"

タイムゾーンの指定

;date.timezone =
↓
date.timezone = "Asia/Tokyo"

mbstringの設定

;extension=php_mbstring.dll
↓
extension=php_mbstring.dll

MySQLへの接続

※MySQLを使わない場合は必要なし。
※他のDBを使用する時は別のサイトを参照の事。

;extension=php_mysql.dll
↓
extension=php_mysql.dll

;extension=php_mysqli.dll
↓
extension=php_mysqli.dll

default_charsetの設定

;default_charset = "iso-8859-1"
↓
default_charset = "UTF-8"

エラーを表示するように変更

※デフォルトでONになってる模様。

display_errors = Off
↓
display_errors = On

mbstringの設定

;mbstring.language = Japanese
↓
mbstring.language = Japanese

;mbstring.internal_encoding = EUC-JP
↓
mbstring.internal_encoding = UTF-8

;mbstring.http_input = auto
↓
mbstring.http_input = pass

;mbstring.http_output = SJIS
↓
mbstring.http_output = pass

;mbstring.encoding_translation = Off
↓
mbstring.encoding_translation = Off

;mbstring.detect_order = auto
↓
mbstring.detect_order = UTF-8,SJIS-win,eucJP-win

;mbstring.substitute_character = none;
↓
mbstring.substitute_character = none

以上

MySQL-5.5.8-win32 のインストール時に「Error Nr. 1364」というエラーが出る。

 


↑のApply security settings の部分で以下のようなエラーが出る。

The security settings could not be applied to the database because the connection has failed with the following error.



Error Nr. 1364

Field ‘authentication_string’ doesn’t have a default value



If a personal firewall is running on your machine, please make sure you have opened the TCP port 3306 for connections. Otherwise no client application can connect to the server. After you have opened the port please press [Retry] to apply the security settings.



If you are re-installing after you just uninstalled the MySQL server please note that the data directory was not removed automatically. Therefore the old password from your last installation is still needed to connect to the server. In this case please select skip now and re-run the Configuration Wizard from the start menu.

文面通り、ファイヤウォールに正しく穴を開けても、ファイヤウォールを停止しても、だめ。

んでよくよくエラー文を調べてみると・・・

「authentication_string」なる場所にNULLデータを書き込もうとしてエラーが出ているんじゃねぇか?と気付くわけだ。

そこで、↓のサイトを参考に、「my.ini」を修正する。

「レコード挿入時の #1364 – Field doesn’t have a default value」

http://pentan.info/sql/mysql/strict_mode.html

sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

この部分の「STRICT_TRANS_TABLES」を消す。つまり・・・

sql-mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

とする。また、設定ファイルを変更したあとには必ずMySQLを再起動すること。

ただし気をつけないといけないことがある。



この「MySQL Instance Configuration Wizard」だが、どうやら「my.ini」を書き換える処理をしているらしい。

そこで、この↓のエラーダイアログを出したまま、「my.ini」を書き換えて、同じくエラーダイアログを出したまま、MySQLサービスを再起動してみる。



そうしたら問題解決。

だが、そもそもこのエラーがなぜでているのか?

‘authentication_string’ なるものに何を書き込む必要があるのか?

はっきりわかっていないので、継続調査が必要である。






追記 By 2011/3/20

arc殿曰く

http://awoni.net/personal-site/mysql

によると



注意:現在最新のバージョンである 5.5.8 では「Enable root access from remote machines」にチェックをするとインストールの最後で Error Nr. 1364 「Field ‘authentication-string’ doesn’t have a default value」というエラーが発生するので、チェックをしないようにしてください。



とのことです

5.5.9でも同様でした。

との事。
arc殿、感謝です。

MySQLの自動バックアップ(Windowsのタスクで自動バックアップ)

MySQLの自動バックアップ(Windowsのタスクで自動バックアップ)の覚書。

ちなみにWindowsでの話ね。

以下のようにバッチを書く。

パス部分は環境に合わせて適宜修正のこと。

として、タスクで自動実行させる。
っとすると、こんな感じにバックアップされる。

MySQL_BackUP




MySQL 再インストール時のエラー「Could not start the service MySQL」

MySQL 5.1.34 をインストールしていて、
インストール一番最後のサービス起動の部分でコケて、
再インストールを何回かしてハマッタのでうp。

MySQL-ERROR
↑この画面で

「Could not start the service MySQL」となってインストールが完了しなかった。

結論。

これしたら直った。

  • コマンドプロンプトからサービスの削除(”C:Program Files\MySQL\MySQL Server5.1\bin\”mysqld -remove [サービス名])
  • プログラムの追加と削除(アンインストール)
  • my.iniの検索(あれば削除)
  • 環境変数(MySQL)の削除
  • Cドライブのクリーンアップ
  • C:WINDOWS\Prefetch\の中のMYSQLD.EXE-15857137.pfとMYSQLINSTANCECONFIG.EXE-2EBFA1E3.pfを手動で削除
  • Cドライブの隠しファイルの中の(C:Document and Settings/AllUsers/Application Data/MySQL)を削除
  • 再起動

ん~どうやら、はじめはサービス名が重複していて、コケた。
つぎは「C:Document and Settings/AllUsers/Application Data/MySQL」にインストールのごみが残っていて、認証でコケタ様子。

なんとも・・・・アンニュイなアプリだこと・・・・

追記(2009/8/13):

上記に加えて↓もやったほうが良いとコメントをもらいました。

stranger 氏 感謝でございます。

  • アンチウイルスソフトを一旦オフ&XPのファイアウォールをオフしておく。
  • 改めてインストールするときに DATA files の場所を任意の場所に指定してインストールする。

(c:/documents and settings/all users/application data~じゃなくて、C:\MySQL\FileDATAなどに) これはDATA filesのところをクリックするとパスが変えられます。

元ネタ(英語):http://forums.mysql.com/read.php?11,75104,255118#msg-255118