検査データベースからさまざまな集計をする

データベースの主な機能としては2つあると思います。

1つは検索機能。
膨大なデータの中からアクセスしたいデータをほぼ瞬時にして見ることができます。
かなり複雑な検索もクエリをある程度知っていれば、割と簡単にできます。

もう一つの機能は集計機能。
これもデータベースの重要な機能です。
きちんと使えればとても便利です。

Lapis（検査データベース） の統計に関しては、これまで検査アプリ作成会社のアプリによって一定のフォーマットで出力されたものを紙に印刷して、その印刷物からエクセルに落としてそれを集計するという方法を採用してきたようです。

とんでもなく面倒であり間違いの混入する可能性が高いので、これはあまりいい方法とは言えません。
データベースから直接集計して、それを確認する（verify）プロセスで何度か確認し、間違いがなければデータベースに集計させ、それを表示するプログラムを php で作成した方が遥かに便利です。

依頼科を集計

2017年1月1日から2021年12月31日までの5年間を集計します。
データベースは sqlite 3 です。

まずは依頼科を集計してみます。

SELECT IraikaName, count(kensa.Iraika) 
FROM kensa
INNER JOIN Iraimst ON kensa.Iraika = Iraimst.Iraika
GROUP BY kensa.Iraika;

結果は以下のようになります。

クエリ実行時間は 130ms でした。

1日あたりの検査数

5年間全体の 1 日当たりの検査数は、

SELECT count(hizuke)/count(DISTINCT(hizuke)) FROM kensa;

結果は 43。
1日あたり、43 件の検査が施行されていることになります。

日ごとの検査数の推移は、

SELECT hizuke, count(UketukeNo)
FROM kensa
GROUP BY hizuke;

結果は、

全部で 1,549 行あります。

1件当たりの検査数

1件あたりの検査数を求めます。

SELECT hizuke, count(koumokuNo) FROM kekka 
GROUP BY hizuke, uketukeNo
ORDER BY hizuke;

1検査当たりの検査項目数を日ごとに集計

1件あたりの検査項目数を日ごとに集計します。
検査件数は 1 日平均 43 件あるので、それらの検査項目数の平均を求めています。

SELECT hizuke, CAST (ROUND(AVG(cnt)) as INTEGER) FROM  (
    SELECT hizuke, COUNT(koumokuNo) as cnt FROM kekka as h1 
    GROUP BY hizuke, uketukeNo
    ORDER BY hizuke
)
GROUP BY hizuke;

前項の抽出結果を h1 という仮テーブルにして、そのテーブルを hizuke で group by して検査項目数の平均を出します。

round で丸めようとすると、浮動小数点になるようなのでそれを整数に cast しています。

検査項目名を月ごとに集計する

それぞれの検査項目を月ごとに集計します。

そのためには、hizuke を date にする必要があるようで、sqlite 3 から mysql へデータを移した後で以下のようなクエリを実行します。

SELECT
    DATE_FORMAT(hizuke, '%Y-%m') as hizuke,koumokuName,COUNT(*) as count
FROM
    kekka
GROUP BY
    DATE_FORMAT(hizuke, '%Y-%m'), koumokuNo, koumokuName
ORDER BY
    hizuke, koumokuNo ;

項目番号順に並べるには、koumokuNo をテキストから整数に変更する必要があります。

kekka テーブルは 186 万行ありますが、それを集計するのに 8 秒ちょっとかかります。