SQLでGROUPを二重に絞り込む

[海Sea]

いつもありがとうございます。
今回もまたSQLで質問があります

コード:

SELECT
SUM(test2.kosu)
,test.date
FROM
hoge AS hoge
LEFT JOIN
hoge2 AS hoge2
ON hoge2.code = hoge.code
LEFT JOIN
hoge3 AS hoge3
ON hoge3.code = hoge2.code
LEFT JOIN 
test2 AS test2
ON test2.id= hoge3.id
INNER JOIN
test AS test
ON test.code = test2.code
GROUP BY
test2.code

上記のようなSQLとして、
SELECTでSUMしていますが、
二つ目のSELECTの項目のtest.date、つまり日付のデータは一日ごとの日付となっています。
現在はGROUP BYでtest2.codeでグループ化している状態です

test.dateは2013/09/16や2013/09/20などの日付があり、
test2.kosuをさらに2013/09として一ヶ月分でSUMしたいのですが、
組み方を試行錯誤しているのですが、うまく行きません。
このような条件下でSUMしたい場合は、どのようにするべきでしょうか。
アドバイス等頂けるとうれしいです。よろしくお願いします。

[YuO]

月単位にするには，日付型を計算して月の初日を出して，その値をGROUP BYするのが簡単だと思います。

コード:

WITH test_with_month AS
(
    SELECT
        code,
        date - INTERVAL DAY(date) DAY + INTERVAL 1 DAY mon
    FROM
        test
)
SELECT
    t2.code code,
    SUM(t2.kosu) kosu,
    t.mon mon
FROM
    hoge AS h
INNER JOIN
    hoge2 AS h2 ON h2.code = hcode
INNER JOIN
    hoge3 AS h3 ON ON h3.code = h2.code
INNER JOIN 
    test2 AS t2 ON t2.id= h3.id
INNER JOIN
    test_with_month AS t ON t.code = t2.code
GROUP BY
    t2.code, t.mon

たぶん，標準準拠のはずですが……どれが標準でどれが標準でないのか，よくわからないのがSQLの世界……。
手元のMySQLはWITEHのCTEサポートしていないし，SQL ServerはINTERVALによる日付操作をサポートしていないという状況なので，試してはいません。
一応，日付の「日」の日数分減算して，1日足せば月の初日が出る，ということを使っています。
また，test.dateは標準のDATE型またはRDBMSの互換型であることを想定しています。
標準のTIMESTAMP型または互換型であるならば，DATE型にキャストする等で日付部分を対象にする必要があります。

オフトピック

最終的にINNER JOINで絞られるのでLEFT OUTER JOINではなく全てINNER JOINにしています。
外部キー制約と非ヌル制約がある場合などで，内部結合でも外部結合でも同じ結果が得られるような場合に，私は内部結合を好むのでそちらを選択しています。
SQL使う上で，NULL (というか，unknown) の可能性を減らすのはバグを減らしてパフォーマンスを上げることになるので。

[海Sea]

月単位にするには，日付型を計算して月の初日を出して，その値をGROUP BYするのが簡単だと思います。

コード:

WITH test_with_month AS
(
    SELECT
        code,
        date - INTERVAL DAY(date) DAY + INTERVAL 1 DAY mon
    FROM
        test
)
SELECT
    t2.code code,
    SUM(t2.kosu) kosu,
    t.mon mon
FROM
    hoge AS h
INNER JOIN
    hoge2 AS h2 ON h2.code = hcode
INNER JOIN
    hoge3 AS h3 ON ON h3.code = h2.code
INNER JOIN 
    test2 AS t2 ON t2.id= h3.id
INNER JOIN
    test_with_month AS t ON t.code = t2.code
GROUP BY
    t2.code, t.mon

たぶん，標準準拠のはずですが……どれが標準でどれが標準でないのか，よくわからないのがSQLの世界……。
手元のMySQLはWITEHのCTEサポートしていないし，SQL ServerはINTERVALによる日付操作をサポートしていないという状況なので，試してはいません。
一応，日付の「日」の日数分減算して，1日足せば月の初日が出る，ということを使っています。
また，test.dateは標準のDATE型またはRDBMSの互換型であることを想定しています。
標準のTIMESTAMP型または互換型であるならば，DATE型にキャストする等で日付部分を対象にする必要があります。

オフトピック

最終的にINNER JOINで絞られるのでLEFT OUTER JOINではなく全てINNER JOINにしています。
外部キー制約と非ヌル制約がある場合などで，内部結合でも外部結合でも同じ結果が得られるような場合に，私は内部結合を好むのでそちらを選択しています。
SQL使う上で，NULL (というか，unknown) の可能性を減らすのはバグを減らしてパフォーマンスを上げることになるので。

WITH句の存在を初めて知りました（＾＾；
SQLってちゃんとやるとほんとに奥が深いと感じるこの頃です。
今までDBチームがやってることがほとんどだったので、色々勉強になります。
明日試してみます！

[へにっくす]

SQL ServerではINTERVALがない・・・
しかもhoge3の行で「ON ON」と重なってますが・・・

もしSQLServer2012以降の場合は、FORMATが使えると思いますのでご参考に
(以下の例をYuOさんのWITHに適用すればよいかと)

コード:

    SELECT
        test.code as code,
        format(test.date, 'yyyyMM') as mon
    FROM
        test

[海Sea]

SQL ServerではINTERVALがない・・・
しかもhoge3の行で「ON ON」と重なってますが・・・

もしSQLServer2012以降の場合は、FORMATが使えると思いますのでご参考に
(以下の例をYuOさんのWITHに適用すればよいかと)

コード:

    SELECT
        test.code as code,
        format(test.date, 'yyyyMM') as mon
    FROM
        test

すいません、環境書いてなかったんですが、
DBはMySQLのため、WITH句非対応という残念な結果に・・・
ただ、WITHが対応しているDBの場合は、
Yuoさんとへにっくすさんの方法で行けることは理解できましたので、
今回の質問としては解決ということにさせて頂きます。
ありがとうございます！

[YuO]

今回の場合，MySQLならINTERVALは使えます。
で，WITHの部分は，test_with_month as tのtest_with_month部分をWITHの括弧部分にそのまま置き換えれば，おそらく動くはずです。

今回共通表式 (CTE) を使ったのは，単純に見やすくなるからだけなので，JOINされるテーブル部分をそのまま置き換えることができます。

オフトピック

再帰共通表式だった場合，置き換えは大変ですが……。
CTEはMySQLのロードマップに入っていない (in 2010) とか。

しかもhoge3の行で「ON ON」と重なってますが・・・

単純にミスりました……。

もしSQLServer2012以降の場合は、FORMATが使えると思いますのでご参考に

Transact-SQL (SQL Server)では，INTERVALのかわりにDATEADD関数が使えます。