質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.40%

  • Java

    16459questions

    Javaは、1995年にサン・マイクロシステムズが開発したプログラミング言語です。表記法はC言語に似ていますが、既存のプログラミング言語の短所を踏まえていちから設計されており、最初からオブジェクト指向性を備えてデザインされています。セキュリティ面が強力であることや、ネットワーク環境での利用に向いていることが特徴です。Javaで作られたソフトウェアは基本的にいかなるプラットフォームでも作動します。

  • PostgreSQL

    1426questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

  • MyBatis

    77questions

    MyBatisはJavaや.NET Frameworkでなどで使用できる、SQL文や、ストアドプロシージャをオブジェクトと紐付けるO/Rマッピングフレームワークです。

条件で複数テーブルにアクセスする動的SQL

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 3,195

poyopi

score 97

最終的な解決?を最下部に追記しています。ご参考まで
ご回答いただいたiwamoto_takaakiさん、masuda_yuyaさん、ありがとうございます。

DB接続をするWEBアプリケーションを作っているのですが、掲題について悩んでいるので質問させてください。

具体的には、条件検索用のフォームとその結果の件数と表が表示されているWEBページです。MyBatisを使って動的SQLを生成しています。
今まではひとつのテーブルを見ていたのですが、対象がログテーブルであるために、毎日ローテートされてしまい、検索の条件にDatePickerを使っているため、昨日などの日付で指定しまうと当然ながら取得件数が0になってしまいます。そのため、改修しようと思っています。

ログテーブル名自体は、「table_yyyyMMdd」のように普段のテーブル名の末尾に日付情報がつくだけなので、ロジックでどうにかしようと思っているのですが、実際のSQLについて考えてみると、この書き方でよいのかと疑問に思ったため、よりよい書き方があればと思い質問に至りました。自分が考え付いたのは以下です:

SELECT * FROM
(SELECT * FROM table)AS table UNION ALL SELECT * FROM
(SELECT * FROM table_yyyyMMdd)AS past01 UNION ALL SELECT * FROM
(SELECT * FROM table_yyyyMMdd)AS past02 UNION ALL SELECT * FROM
(SELECT * FROM table_yyyyMMdd)AS past03
# 以下略


現状、MyBatisを使って下記のように書いているのを、複数テーブルを見るように変えようと思っているというところです:

<select id="search" resultType="package.DTOclass">
        SELECT * FROM table
        <where>
            <if test="startTime!=null and startTime!=''">
                AND access_time &gt;= to_timestamp(#{start}, 'YYYY/MM/DD HH24:MI')
            </if>
            <if test="endTime!=null and endTime=''">
                AND access_time &lt;= to_timestamp(#{end}, 'YYYY/MM/DD HH24:MI')
            </if>
        </where>
        ORDER BY time DESC LIMIT 100 OFFSET #{counter}
    </select>


上記の自分が思いついたSQLであてはめようとすると、<where>までの間に記述を追加することになると思っています。ですが、ローテートされて残っているテーブルは10件になるため、なんだかあまりきれいじゃないような気がしています。また、速度的にもどうなのかな? と思うところがあります。

postgresqlですが、Oracleだったらこう書けるよ、とかでもよいので、何かよりよくするためのご助言を頂ければと思います。


ひとまずプログラムの中に組み込む前にベタSQLで結果を試してみたところ、当初質問を立てた意図とは変わりますが、うまくいかない部分があったので解決するまでこの質問をあけておきます(解決後質問文に顛末を追記します)。
なお、明示的に回答を求めず自己解決の心づもりでおりますが、下記に目を通してお気づきの点ございましたらご助言頂けますと幸甚です。

問題:where句が効いてない

select*from table 
union all select*from table_20160626 
union all select*from table_20160625 
union all select*from table_20160624 
where access_time >= to_timestamp('2016/06/27 00:00','YYYY/MM/DD HH24:MI') and access_time <= to_timestamp('2016/06/27 07:00','YYYY/MM/DD HH24:MI') 
order by access_time desc limit 100 offset 1


↑6/27の0時から7時まで……の条件のつもりだが、普通に現在(17時とか)から最新順で100件になってしまう。条件を別の項目に変え(where hoge='piyo'みたいな)ても効いておらず、普通に全データの最新100件が返される。


06/28
masuda_yuyaさんのご指摘にてSQLの文法の誤りを認識。改修して、ベタSQLとしてはこういう感じになりました:

select*from (
select*from table 
union all select*from table_yyyyMMdd
union all select*from table_yyyyMMdd  
union all select*from table_yyyyMMdd 
) tables
where tables.access_time <= to_timestamp('2016/06/27 06:00','YYYY/MM/DD HH24:MI') 
and tables.access_time >= to_timestamp('2016/06/27 01:00','YYYY/MM/DD HH24:MI')
order by tables.access_date desc limit 100 offset 1


WHERE句の条件は適当でよく、正常に結果が抽出できるかどうかの確認となります。問題なさそうだったので、動的SQLにするためにjavaをいじります。

  • yyyyMMdd部分の生成
    メインのテーブルが「table」で、本日分のデータのみが載っている。日次でローテートされ、ローテートされるとテーブル名の末尾に「_yyyyMMdd」が付与される。ここでは過去10日分のみ保持。
    とりあえず、SQL組み立てでバインドのため利用している変数を宣言しているクラス内に、過去10日間のテーブル名を返すgetメソッドを作りました。
public List<String> getRotateTables(){
        SimpleDateFormat dateFormat=new SimpleDateFormat("yyyyMMdd");
        Calendar t =Calendar.getInstance();
        List<String> list=new ArrayList<>();
        String[] tmp = new String[10];
        for(int i=0;i<=9;i++){ 
            t.add(Calendar.DAY_OF_YEAR, -1);
            tmp[i]="table_"+dateFormat.format(t.getTime()).toString();
        }
        list.addAll(Arrays.asList(tmp));
        return list;
    }


きたないっぽいですが動作すること優先で進みます。

  • Mapperインタフェースに上記のパラメータ追加
  • Mapper使っているDAOに上記パラメータ追加
  • 検索やページングのActionクラスにも追加

最終的にSQLを組み立てているXMLを書きます。大体こういう感じです:

<select id="search" resultType="package.DTOclass">
    SELECT 
        * FROM
        (
          SELECT * FROM table 
        <foreach item="rotate" collection="rotateList" separator=" ">
        UNION ALL SELECT * FROM ${rotate} 
        </foreach>
        ) tables   
        <where>
        <if test="from!=null and from!=''">
        AND tables.access_time &gt;= to_timestamp(#{from},'YYYY/MM/DD HH24:MI')
        </if>
        <if test="to!=null and to!=''">
        AND tables.access_time &lt;= to_timestamp(#{to},'YYYY/MM/DD HH24:MI')
        </if>
        </where> 
        ORDER BY tables.access_time DESC LIMIT 100 OFFSET #{counter}
    </select>


最初はまったのは、テーブル名を普通にバインド(#{rotate}みたいに)しようとしたらException(org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1")を出されてしまったことです。テーブル名はバインドできなさそうです。参考:mybatis/old-google-code-issues - githubiBatis奮闘記-0013 ($を使用した動的パラメータ) - いろいろ備忘録日記

検索のために複数テーブルを含めて抽出という目的が達成されたのでクローズします。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

> fymartymさん

「問題:where句が効いてない」に記載しているSQLのwhere句は、
「select*from table_20160624」にしか効いていないように見受けられます。

下記の二つの対応が必要なのではないでしょうか。

  • 入力値から検索対象のテーブルをロジック側で生成する
    入力値:2016/06/25 01:00 ~ 2016/06/27 01:00
    →検索テーブル:table_20160625 、table_20160626、table(←当日用)

  • 適切なselect文にwhere句を組み込む

select*from table 
where access_time <= to_timestamp('2016/06/27 01:00','YYYY/MM/DD HH24:MI') 
union all select*from table_20160626 
union all select*from table_20160625
where access_time >= to_timestamp('2016/06/25 01:00','YYYY/MM/DD HH24:MI')
order by access_time desc limit 100 offset 1

mybatisでどう表現するかまでは踏み込めていませんが、参考になればと。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/06/27 18:30

    ご回答ありがとうございます。
    > 適切なselect文
    ご教示ありがとうございます。こちらを参考にSQLを走らせましたら意図した通りとなりました。where句の位置(unionの位置?)がおかしかったのですね。SQL実践本を読み直したいと思います。
    頂いたご助言をもとに実装して、実現できましたら質問文に手順を記載してクローズとしたいと思います。ありがとうございました。

    キャンセル

  • 2016/06/27 19:19 編集

    補足です。
    もし、上記のSQLの生成が難しいようでしたら、
    下記のような形にするとやりやすいかと思います。

    select * from
    (
    select*from table
    union all select*from table_20160626
    union all select*from table_20160625
    ) t
    where t.access_time <= to_timestamp('2016/06/27 01:00','YYYY/MM/DD HH24:MI')
    and t.access_time >= to_timestamp('2016/06/25 01:00','YYYY/MM/DD HH24:MI')
    ...

    大きい括弧の結果セットを生成した後に、日時条件で絞り込みするアクセスパスになる可能性が高いので、各テーブルはフルスキャンになってしまうと思いますが。

    キャンセル

  • 2016/06/28 18:02

    補足ありがとうございます。ご提示いただいた形を利用して意図する実装ができましたので、質問にて追記しました。
    > 各テーブルはフルスキャンになってしまうと思いますが。
    実行計画見ながら、文法に気を付けつつ、改善できそうな部分を今後チューニングしたいと思います。

    キャンセル

+1

私はソースコードがきれいで、何を意図したSQLであるか理解しやすいなら、実際のSQLがどんなに冗長で汚くても気にしません。

Webのシステムを作るときって、生成されるHTMLは気にしないので同じ感覚です。

まあ、処理時間に影響が出るほど長くなるならかんがえものですが・・・

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/06/27 16:55

    ご回答ありがとうございます。SQL自体は冗長でも問題視しないとのこと、参考になりました。
    立てた質問内容とは少々ずれる形でSQLの記述に難儀しているため(恥)、解決してから質問をクローズ致します。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.40%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • Java

    16459questions

    Javaは、1995年にサン・マイクロシステムズが開発したプログラミング言語です。表記法はC言語に似ていますが、既存のプログラミング言語の短所を踏まえていちから設計されており、最初からオブジェクト指向性を備えてデザインされています。セキュリティ面が強力であることや、ネットワーク環境での利用に向いていることが特徴です。Javaで作られたソフトウェアは基本的にいかなるプラットフォームでも作動します。

  • PostgreSQL

    1426questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

  • MyBatis

    77questions

    MyBatisはJavaや.NET Frameworkでなどで使用できる、SQL文や、ストアドプロシージャをオブジェクトと紐付けるO/Rマッピングフレームワークです。