複数の集計(COUNTなど)を1回のSQLで行う方法

MySQL

たとえば「select count(*) from A」などでテーブルAに登録された件数が計数できる。同じようにテーブルBについても、あるいはwhere句を指定して計数したいなど、いろいろな集計を1回のSQL文の発行で行う方法だ。union句を使えばいいだけのことなのだが、ちょっと工夫するとプログラム上で結構、汎用的に書けそうだ。



select count(*) from A

union

select count(*) from A where NAME is not null

union

select count(*) from A where UPDATED < '2010/02/06'
通常なら3回のSQL発行が必要そうなところを1回の発行で済ませることができる。ここまでは大体の方がご存知だろう。ただ、上記のSQLだけでは正しく検索されない場合もある。これを以下のように補完してやる。


select '全件数' as K, count(*) as V from A

union

select '名前のある件数' as K, count(*) as V from A where NAME is not null

union

select '古い件数' as K, count(*) as V from A where UPDATED < '2010/02/06'
こうすると正しく検索され、しかもプログラムで扱いやすくなるのだ。K=KEY,V=VALUEの意味だがエリアスは何でもいい。1カラム目にはユニークになる値を設定する必要がある。エリアスは先頭のselect文だけでもいいがそれぞれのselect文に付けていても問題ない。勿論、count(*)以外の集計や、A以外のテーブルについての集計があってもいい。ここでは話を簡単にするために統一しているだけだ。原則、どんな集計でもunionでくっつけられるので、1回のSQL発行で済ませることができる。
ポイントは、1カラム目にダミーのカラムを加えて、集計値を2カラム目にエリアス付きで並べるということだけだ。1カラム目にダミーのカラムを設定するのは、UNIONによって集計結果を失わないためだ。2カラム目のエリアスは、ResultSetから明示的に値を取得するためだ。すると、たとえば、以下のような感じで、若干ではあるが汎用性のある計数のメソッドが書けることがわかる。

public int[] count(Connection conn, String tablename, String[] wheres){
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
    String sql = "";
    int n = wheres.length;
    for (int i = 0; i < n; i++){
        if (n > 1 && i > 0) sql += " UNION ";
        sql += "SELECT '" + i + "' AS K, COUNT(*) AS V FROM " + tablename;
        sql += " " + wheres[i];
    }
    ResultSet rs = null;
    int[] counts = null;
    try {
        rs = stmt.executeQuery(sql);
        counts = new int[n];
        for (int i = 0; i < n; i++){
            rs.next();
            counts[i] = rs.getInt("V");
        }
    } catch (SQLException e){
        return null;
    }
    return counts;
}

これを発展させれば、count(*)以外の集計や複数のテーブルに対する任意の集計も1発のSQLで可能になる。なお、平均値など集計値が実数を扱う場合には、集計値のカラムが実数で揃えられるので、int配列ではなくdouble配列を用意する必要がある。ちなみに上記のメソッドは、以下の感じで呼び出せる。

    // Aテーブルについて3つの計数を同時に行う。
    Connection conn = Xxx.getConnection();      // ここは任意
    String tablename = "A";
    String[] wheres = new String[3];
    wheres[0] = "";
    wheres[1] = "WHERE NAME IS NOT NULL";
    wheres[2] = "WHERE UPDATED < '2010/02/06'";

    int[] counts = count(conn, tablename, wheres);