SQL一発でカレンダー表示は可能ですか？

質問

お世話になっております。

とあるログテーブルからデータを抽出する際、
日付でグルーピングをかけて集計した上、
カレンダーのような抽出結果にする必要があり、悩んでいます。
悩みどころは
・『データがない日付の場合も表示する』
・『SQL1本で出さなくてはならない』
です。

【サンプル】ログテーブル
--------------------
ログ日時　｜　回数 ｜
--------------------
2006/07/01｜　　2　｜
2006/07/03｜　　1　｜
2006/07/05｜　　8　｜
--------------------

上記テーブルから
ログ日時が2006/07/01から2006/07/05までのデータを取得した場合、
結果をこういう形で抽出したいのです。

--------------------
ログ日時　｜　回数 ｜
--------------------
2006/07/01｜　　2　｜
2006/07/02｜　　 　｜→ログテーブルにデータ無し
2006/07/03｜　　1　｜
2006/07/04｜　　 　｜→ログテーブルにデータ無し
2006/07/05｜　　8　｜
--------------------

データベースはsybase IQです。
なにか良い方法をご存知の方がいらっしゃいましたら
ご教示いただけませんか？？？

回答No.3

chukenkenkou · ベストアンサー

Sybase IQでは、OUTER JOINはサポートされているのですよね？
事前準備として、カレンダー（7月なら、7月1日～31日）を管理するテーブルを作っておいて、ログの日付の情報とLEFT　JOINさせた上でGROUP BYすれば、簡単な検索SQLで実現できます。

以下の例では、カレンダーを管理するテーブルには日付しか入れていませんが、休日を管理したり用途はいろいろあると思います。

１．表定義＆データ例
create table user_calendar
(user_date date);
insert into user_calendar values('2006-7-1');
insert into user_calendar values('2006-7-2');
insert into user_calendar values('2006-7-3');
～中略～
insert into user_calendar values('2006-7-31');

２．ログ情報・・・既存のテーブル
create table log_table
(log_date date,
log_data varchar(100));
insert into log_table(log_date) values('2006-7-1');
insert into log_table(log_date) values('2006-7-3');
insert into log_table(log_date) values('2006-7-3');
insert into log_table(log_date) values('2006-7-3');
insert into log_table(log_date) values('2006-7-5');
insert into log_table(log_date) values('2006-7-5');
insert into log_table(log_date) values('2006-7-5');
insert into log_table(log_date) values('2006-7-5');
insert into log_table(log_date) values('2006-7-5');

３．検索SQL例
select user_date,count(log_date)
from user_calendar left join log_table
on user_date=log_date
group by user_date
order by user_date;

お礼

有難う御座います。

この方法で実装したところうまく行きました！

しかしその後、
クライアントから仕様変更の連絡があり、
データがある日だけ出せばOKということになりました。

それでも私の今後の開発にとても役立つお話でした。
有難う御座いました！

補足

OUTER JOIN(LEFT,RIGHT,FULL)は全てサポートしているようです。

回答No.4

CHRONOS_0

sybase IQにどのような関数があるのか知りませんので考え方だけ

レコードを作製するには種がいります
というこごで数字だけのテーブルを用意します

num
----
0
1
・
・
99　＜－必要十分な大きさ+余裕まで

このテーブルからクエリを作り
ログ日時最小+num日という式フィールドを作り
抽出条件に<=ログ日時最大
とすればログ期間の抜けのないカレンダーが得られます

お礼

有難うございます。

なるほど、思いつきもしなかった！
カレンダーテーブルそのものでなくても
軸となるものがあればイケますね！

回答No.2

galluda

がると申します。
ちぃと補足になるのですが。

表示部分(俗にviewって呼ばれる場所ですね)で、直接DBのデータをfetchしてるわけではないんですよね？(もし直接やってるんだと…ちぃとインタフェースいじくったほうがよいように思われます)

基本的には「表示部分に渡すデータ」を細工になるので、Dbのfetch部分とデータを渡す部分の間に「欠落してる日付のデータを突っ込む」処理をaddするとよろしいのではないかと愚考いたします。

以上、何かの参考にでもなれば幸いです。

お礼

有難うございます。

>>「欠落してる日付のデータを突っ込む」処理をaddする
インターフェース的にコレが難しいらしいのです。

回答No.1

galluda

がると申します。
んっと…基本的に「ないデータを抽出する」ってのは、DBの基本的意義に反する要求だと思うのですが。

無論「表示部分で出したい」という要求は割合によくありえる話ではあるのですが、
・SQLでそれを処理する
必要はあるのでしょうか？
素直に「SQLでデータを抽出、プログラムでデータを処理」ではなにかまずいでしょうか？

お礼

回答ありがとうございます。

表示側のアプリケーションの問題で厳しいらしいのです。
やはり基本『無理』ですよね・・・。
説得して表示部分で何とかしてもらうようにします。