SQLでGROUPを二重に絞り込む

フォーラム(掲示板)ルール
フォーラム(掲示板)ルールはこちら  ※コードを貼り付ける場合は [code][/code] で囲って下さい。詳しくはこちら
アバター
海Sea
記事: 102
登録日時: 9年前
住所: 大阪
連絡を取る:

SQLでGROUPを二重に絞り込む

#1

投稿記事 by 海Sea » 5年前

いつもありがとうございます。
今回もまた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
記事: 941
登録日時: 9年前
住所: 東京都世田谷区

Re: SQLでGROUPを二重に絞り込む

#2

投稿記事 by YuO » 5年前

月単位にするには,日付型を計算して月の初日を出して,その値を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
記事: 102
登録日時: 9年前
住所: 大阪
連絡を取る:

Re: SQLでGROUPを二重に絞り込む

#3

投稿記事 by 海Sea » 5年前

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) の可能性を減らすのはバグを減らしてパフォーマンスを上げることになるので。

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

アバター
へにっくす
記事: 628
登録日時: 7年前
住所: 東京都

Re: SQLでGROUPを二重に絞り込む

#4

投稿記事 by へにっくす » 5年前

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

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

コード:

    SELECT
        test.code as code,
        format(test.date, 'yyyyMM') as mon
    FROM
        test
written by へにっくす

アバター
海Sea
記事: 102
登録日時: 9年前
住所: 大阪
連絡を取る:

Re: SQLでGROUPを二重に絞り込む

#5

投稿記事 by 海Sea » 5年前

へにっくす さんが書きました: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
記事: 941
登録日時: 9年前
住所: 東京都世田谷区

Re: SQLでGROUPを二重に絞り込む

#6

投稿記事 by YuO » 5年前

今回の場合,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関数が使えます。

閉鎖

“C言語何でも質問掲示板” へ戻る