MySQL – 月単位の集計~残高もある場合

例題設定

例題として個人の銀行口座の入出金記録の以下の構造のテーブルを考える。

ここでidは入出金記録の順番に付されていて、変更されないものとする。すなわち、日・月・年などの単位でグルーピングしたとき、idが最も大きいレコードのbalanceがその期の残高となっている。

合計計算のみの場合

月ごとの合計を計算するだけの場合、年月でグルーピングしてSUM関数を使う。

要点は以下の通り。

  • GROUP BY句で、yyyymm形式でフォーマットされた年月単位でグルーピング
  • グループごとにSUM関数で出金額と入金額を合計
  • 年月表示はGROUP BY句のフォーマットと異なってもよい
  • ただしグルーピングされた複数レコード中のどのレコードのdateを使うかを明示するため、MAX関数を使っている。
    • こうしないと(オプション指定なしでは)エラーが発生する

各月の残高も含める場合

月ごとの最終取引の抽出

各月の残高がデータに含まれていて、取引がidの昇順であることがわかっている場合、月ごとにグルーピングされたレコードのうちidが最大のレコードがその月の最後の取引になり、そのbalanceが月の残高になる。

そこで、まず各月の最終取引のidを取得する。

各月最終取引のレコード取得と残高表示

最終取引のidに一致するレコードのみ元のテーブルから取り出すため、INNER JOINを使う。

要点は以下の通り。

  • 最終取引を抽出したクエリーをサブクエリ―として、そのidと等しいレコードのみを元のテーブルから抽出している
  • サブクエリ―にはエイリアスが必須
  • 抽出された最終取引のbalanceを月末残高として表示
  • SELECT句のidは確認用で、表示目的としては不要

月ごとの入出金額を追加

上記のクエリーに、グルーピングされた月ごとの入出金額を追加する。

要点は以下の通り。

  • 月ごとにグルーピングしているサブクエリ―で、SUM関数によって月単位の出金・入金額を計算
  • それらの値をエイリアスを使って元のテーブルで表示

このクエリーのチェックは、前月残高に当月の入出金額を加減して当月残高となることで確認できる。

 

 

MySQL – CSVファイルのインポート

概要

MySQLでCSVファイルをインポートする手順。

要点は以下の通り。

  • CSVファイルはUTF-8(BOMなし)で準備する
  • 改行コードに注意
  • MySQLサーバーとクライアントの両方でローカルのファイル入力の許可が必要
    • クライアントはログイン時のオプションで、サーバー側はグローバル変数で設定する

CSVファイルの準備

CSVファイルを準備し、MySQLで読み込む場所に配置する。CSV作成時の型と文字コードに注意。

型に関する注意点

数字だが001のように表現したい場合は文字列として扱う。

  • Excelの列の形式で適切な型を設定する

日付・時刻形式の空白値に注意。

  • 日付・時刻値の列に空のデータがある場合にMySQLのdate/time/datetimeで読み込むと、NULL値ではなく'0000-00-00''0000-00-00 00:00:00'で埋められてしまう

文字コードの注意点

CSVファイルはUTF-8のBOMなしで準備する。

  • ExcelのシートをCSVファイルとして保存すると、UTF-8(BOMあり)になるので、メモ帳などで開いてBOMなしで保存し直す必要がある

MySQLログイン時のオプション

MySQLにログインするクライアントで、ローカルのファイル入力を許可する必要がある。

ログイン時に以下のオプションのいずれかを付ける

  • --enebale-local-infile
  • --local_infile=1またはon

MySQLログイン後の設定

グローバル変数の確認

SELECTSHOW GLOBAL VARIABLESlocal_infileの値を確認。この値が0/OFFの場合は許可されていない。以下のいずれかで確認できる。

SELECT @@local_infile;

SHOW GLOBAL VARIABLES LIKE 'local_infile';

グローバル変数の設定

SET GLOBALlocal_infileの値を1またはONにセットする。

SET GLOBAL local_infile=1またはON;

インポート実行

テーブルの準備

CSVの各カラムに対応した構造で、空のテーブルを準備しておく。

MySQLのtimestamp型の2038年問題に注意。

LOAD DATAの実行

SQLの例

CSVファイルをテーブルにインポートするSQLの例。

この例では、WindowsのようにCRLFで改行されたファイルを読み込み、区切り文字がカンマのCSVとして、先頭1行を飛ばして読み込む。

FIELDS

TERMINATED BY
CSV保存時に選んだ区切り文字を指定する。1文字である必要はない。デフォルトは'\t'
ENCLOSED BY
フィールドを囲み文字を1文字で指定する。デフォルトは''(空)で、フィールドが区切り文字で囲まれていることを期待しない。OPTIONALLYを付けると文字列のみ囲み文字で囲まれていることを期待する。
ESCAPED BY
エスケープ文字を1文字で指定する。エスケープ文字に続く文字がエスケープシーケンスとして解釈される。デフォルトは'\\''\'一文字を表している。

LINES

STARTING BY
指定したprefix以降のみ行として扱い、それ以外はスキップする。prefixは1文字でなくてもよい。デフォルトは''でprefixを設定していない(常に行頭から読み込む)。
TERMINATED BY
行末の文字列を指定する。デフォルトは'\n'

IGNORE

IGNORE 数値 lines/rowsで、先頭の指定した数の行/列を読み込まない。

 

 

 

Laravel – アクセス制限

ミドルウェアの適用

コントローラー単位でアクション→ビューにアクセス制限をかける場合。コントローラーのコンストラクターでアクセス制限のミドルウェアを適用する。

$this->middleware('auth')

特定のアクションのみに適用したり、逆に適用除外にするには、only()/except()を使う。

たとえばマーケットサイトのCartControllerへのルーティングで、ログインユーザーのみ処理させるには以下の様にコントローラーを書く。

こうすると、ログイン状態でアクセスするとindexなどのアクションが実行されてビューに遷移するが、ログイン状態でない場合はログインページに遷移する。

middleware(‘auth’)

コンストラクターの$this->midlleware('auth')は、コントローラーの全アクションに対して名前'auth'のミドルウェアを適用する。

'auth'Kernel.phpでルートミドルウェアAuthenticateに結び付けられている。

Authenticateミドルウェア

Authenticateミドルウェアは、Laravelのプロジェクト作成時にapp/Middlewareディレクトリーに生成されている。

  • このミドルウェアのhandle()メソッドはIlluminate\Auth\Middleware\Authenticateで定義されている
  • $request->expectsJson()でログイン状態にあるかどうかを判定しているようだが、その流れはよくわかっていない

この'login'へのルーティングを変更すると、未ログインの場合の遷移先を変更できる。

 

Laravel – ログインユーザーの取得

ログイン済みのユーザーは、staticメソッドAuth::user()で取得できる。

例えば以下は、ビューでログインユーザーを表示させる例。

ただし未ログイン状態のときはエラーになる。ログイン状態の時だけ実行する書き方として、以下の2通りがある。

または

 

Laravel – LoginController

概要

LoginControllerは、Laravelのプロジェクト開始時にapp/Http/Controllers/Authディレクトリー下に生成される。

ルーティングファイルにAuth:routes()を記述することで、このコントローラーの各アクションは以下のようにURLと結び付けられる。

method URL action name
GET /login showLoginForm login
POST /login login

ソースコード

内容

コントローラーの機能

ソースコード冒頭のコメントの要旨は以下の通り。

  • このコントローラーは、アプリケーションにおけるユーザー認証とホームページへの遷移を行う
  • コントローラーはトレイト(AuthrsenticatesUsers)を用いてこの機能を導入している。

AuthenticatesUsersトレイトの導入

AuthenticatesUsersはLaravelで準備されたトレイトで、showLoginFormアクション、loginアクションを実装している

登録後の遷移先

ログイン後の遷移先を設定している。ログイン後の遷移先を変更するには、ここでリダイレクト先を変更するか、HOME定数を変更する。

RouteServiceProviderはコントローラーの冒頭でuseされている。

RouteServiceProviderを見ると、public定数HOME'/home'に設定されている。

ミドルウェアの適用

コントローラーのコンストラクターでミドルウェアを適用している。

'guest'Kernel.phpでルートミドルウェアの名前として定義されていて、RedirectIfAuthenticatedミドルウェアを指している。

RedirectIfAuthenticatedミドルウェアはコントローラー実行前にAuth::guard()メソッドを呼び出している。

 

Laravel – middleware

概要

ミドルウェアを作成・登録する手順は以下のとおり

  1. artisanでミドルウェアを作成
  2. ミドルウェアの処理内容を記述
  3. ミドルウェアを登録
  4. ルーティングでミドルウェアの組み込みを設定

参考サイト:【Laravel】ルーティングのミドルウェアとは?作成手順と実例

準備

以下のルーティング、コントローラー、ビューを準備する。

ルーティング:

コントローラー:

ビュー:

なおコントローラーとビューで、以下のヘルパーコントローラーのconsole_log()メソッドで表示をさせていて、ミドルウェアでもこのメソッドを使っていく。

ミドルウェアの作成・登録手順

ミドルウェアの作成

以下のコマンドでミドルウェアのファイルを作成。

php artisan make:middleware ミドルウェア名

ファイルはapp/Http/Middlewareディレクトリー下に作成される(middlewareはuncountableだが、状況によってはmiddlewaresという複数形もあり得るらしい)。

作成されたファイルの内容。

ミドルウェアの処理内容の記述

ミドルウェアの処理には、その実行タイミングに応じた書き方がある。

それらについては後述するが、ここでは仮にhandle()メソッドに別途準備したconsole_log()で表示をさせている。

ミドルウェアの登録

登録の種類と手順

作成したミドルウェアはapp/Http/Kernel.phpファイルで登録する。

登録方法により、グローバル登録、ルーティング登録、グループ登録の3種がある。

グローバル登録

Kernelクラスの$middlewareプロパティーに作成したミドルウェアを加えると、すべてのルーティングにミドルウェアが適用される。

以下の例では、デフォルトで生成・登録されるミドルウェアに加えてSampleBeforeMiddlewareを登録している。

ルート登録

Kernelクラスの$routeMiddlewareプロパティーに、名前とともにミドルウェアを登録する。

以下の例では、デフォルトで生成・登録されるミドルウェアに加えて、SampleBeforeMiddleware'routed_middleware'という名前で登録している。

グループ登録

Kernelクラスの$middlewareGroupsプロパティーに、グループ名をキーとし、ミドルウェアの配列を値として登録する。

以下の例では、デフォルトで登録される2つのグループ'web''api'に加えて’test'というグループを追加し、そのグループにSampleBeforeMiddlewareを登録している。

ミドルウェアの適用

グローバル登録の場合は全適用

グローバル登録されたミドルウェアは、ルーティング設定がなくても全てのコントローラーのアクションに適用される。

この結果、アプリケーションの任意のURLにアクセスすると、ブラウザーのコンソールに以下の様に表示される。

ルーティングファイルでの適用

ルート登録・グループ登録のいずれの場合も、ルーティングで特定のルートにミドルウェアを適用できる。

ルートミドルウェアの場合は登録した名前で適用。

グループミドルウェアの場合は登録したグループ名で適用。

結果はいずれも同じで、以下のようにブラウザーのコンソールに表示される。

コントローラーのコンストラクターでの適用

コントローラーのコンストラクターの中で、middleware()メソッドの引数にミドルウェアの登録名やグループ名を指定して適用できる。

この場合、ルーティングファイルで設定しなくても、当該コントローラーのアクションに対してミドルウェアが適用される。

以下はルートミドルウェア名で登録する場合。

以下はグループ名で適用する場合。

結果はいずれも同じで、以下のようにブラウザーのコンソールに表示される。

ミドルウェアの条件

before middleware

コントローラーが呼ばれる前に実行すべき文を、$next($request)の実行より前に書く。

このとき、$requestからパラメーターを取り出し、その条件に応じた処理をさせることができる。

after middleware

handle()メソッド内で$next($request)を実行した後に、その結果に応じた処理を記述すると、コントローラーの実行後・ビュー表示前にその内容が処理される。

terminate()メソッド内に処理を記述すると、それらはビュー表示後に実行される。

ここで、この2つのミドルウェアをKernelクラスでグループ登録する。

ルーティングへの適用はグループ名で。

実行すると、ブラウザーのコンソールに以下の様に表示される。

なお、上記ではルーティングファイルでミドルウェアを適用したが、コントローラーのコンストラクターで適用しても結果は同じ。

only/except~部分指定と除外

準備

コントローラーのコンストラクターでミドルウェアを適用する場合、コントローラーの特定のアクションのみに適用したり、適用除外にしたりすることができる。

まず、以下のようなルーティング定義、コントローラーを準備する。ミドルウェアとビューはこれまで使ってきたものと同じ。

ルーティング:

コントローラー:

ここで2つのURLをブラウザーで指定すると、それぞれ以下の様に表示される。

middleware/select

middleware/ignore

only()~部分指定

コンストラクター内のmiddleware()に対してonly()メソッドを実行すると、引数の配列で与えたアクション群に対してだけミドルウェアが適用される。

以下の例ではselect()メソッドのみ適用対象としていて、ignore()メソッドにはミドルウェアは適用されない。

この結果、middleware/selectにアクセスした結果は先の結果と変わらないが、middleware/ignoreにアクセスした場合はミドルウェアが実行されず、以下のようなコンソール表示になる。

except()~除外指定

コンストラクター内のmiddleware()に対してexcept()メソッドを実行すると、引数の配列で与えたアクション群に対してだけミドルウェアが適用されなくなる。

以下の例ではignore()メソッドのみ適用除外となり、ミドルウェアが適用されない。

結果はonly()の場合と同じになる。

 

PHP – 関数 – 無名関数/クロージャ―/コールバック

基本

無名関数(anonymous function)はfunction()で定義。無名関数はクロージャ―(closure)とも呼ばれる。

戻り値を返す例。

引数をとる例。

use~エンクロージャーの変数

クロージャ―の外側(エンクロージャー)のスコープの変数をクロージャ―内で使う場合は、useを加える。

useで持ち込まれるエンクロージャーの変数の値は、関数定義前に定義された内容。上の例では$greetに関数が定義される前の'Hello'が使われていて、実行前の'Hi'は使われない。

コールバック

無名関数を使ったコールバック

以下の関数は、引数$callbackを関数として想定し、その引数に$nameを与えた結果を表示する。

$nameに文字列'Jane'を与え、$callbackに無名関数を与えて実行。この無名関数は引数の$nameを使って"Hi, $name"とい文字列を返す。

$callbackに違う動作をする無名関数を与えて実行。この無名関数は引数の$nameを使って"Hello $name. How arey you?"という文字列を返す。

変数に定義した無名関数のコールバック

コールバックを都度無名関数で定義するのではなく、あらかじめ変数に定義しておいた関数を渡すことができる。

これによって、メッセージのディスパッチなどが柔軟に行えるようになる。

可変関数によるコールバック

コールバックを可変関数で実装した例が以下の通り。

なお、無名関数を変数に代入した場合の変数名とfunctionで定義した関数名は重複してもエラーとならない。

array_map()の例

コールバックを使った内部関数にarray_map()がある。

array_map($callback, $array);

コールバックで直接無名関数を定義し、配列の各要素の自乗を要素とする配列を計算させる。

もちろん引数のコールバックに、変数に定義した関数や可変関数を用いることもできる。

 

PHP – 関数 – 可変関数

基本

可変関数は、変数に格納された文字列を関数名として解釈して実行するもの。

変数の後に()がある場合、PHPは変数の値の文字列を関数名として実行しようとする。

文字列に対応する関数が存在しない場合や値が文字列でない場合にはエラーとなる。

言語構造の場合

echoevalといった言語構造については、可変関数として使えない。これらを使う場合にはラッパー関数を定義する。

ビルトイン関数

ビルトイン関数は使用可能。

メソッド

クラスのstaticメソッドやインスタンスメソッドに対しても使用可能。

 

PHP – 関数 – 可変長引数

…$args

関数の引数を...変数名のように指定すると、任意の個数の変数を渡すことができる。

受け取った関数では、配列$argsの各要素に引数の値がセットされている(要素数が0や1の場合も配列)。

型の混在

上の例ではstring型を指定して全ての引数を文字列として扱っていて、すべての値がstring型である(あるいはキャスト可能である)必要がある。

型を指定しない場合は、与えられたそれぞれの引数の型のままで配列に格納される。

単純引数との混在

最後尾は可能

通常の引数が並んだその後、最後尾に可変長引数を置くことができる。

可変長以外の引数はデフォルト値がなければ省略できないが、可変長引数は0個でもよい。

先頭や中間は不可

可変長引数を先頭や単純引数の間に置くことはできない。

逆の使い方

複数の引数を...$arrayで受けると、それらを要素とした$arrayが得られる。逆に$arrayが配列の時...$arrayは分解された各要素を返す

これらはコレクションでもないばらばらの値なので、echoで出力したりforeachで使うことはできない。

以下の例では、配列を1つ1つの要素に分解して、それらを引数に与えて最大値・最小値を得ている。

以下の例では、2つの引数を配列の要素として準備して関数に与えている。

 

PHP – 関数 – デフォルト引数

概要

  • 関数定義時に引数のデフォルト値を指定することができる
  • デフォルト値を指定した引数は、関数呼び出し時に省略することができる
  • 関数呼び出し時に指定された引数は、定義された引数の順番に充てられる
    • PHP8.0以降は名前付きを使うことができるため、必要な引数のみ値をセットできる

最後の関数呼び出しでは、int $dices2を、int $trials','をセットすることになるので、2つ目の引数の型が合わずにエラーとなる。

2つ目の引数$trialsの型指定をしていなければ、','は数値表現ではないので0とみなされ、以下のような結果になる(各試行の際に必ず1度サイコロが降られる)。