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

Question

最終的な解決？を最下部に追記しています。ご参考まで
ご回答いただいた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 - github、iBatis奮闘記-0013 ($を使用した動的パラメータ) - いろいろ備忘録日記

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

Answer 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でどう表現するかまでは踏み込めていませんが、参考になればと。

Comments

[poyopi]

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

[ka_ei]

補足です。
もし、上記の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')
...

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

[poyopi]

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

Answer 2

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

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

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

Comments

[poyopi]

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