Oracle Technology Network (OTN) Japan - 掲示板 » テクノロジー » プログラミング一般 スレッド: SQLによる日付リストの生成

ようこそ、ゲストさん ログイン 掲示板検索 / ランキング オンライン便利サービス ヘルプ / 利用規約

返信数: 18 - 最新投稿 : 2006/01/18 11:19 最新投稿者: kitkat - スレッド表示形式: フラット表示 スレッド表示 ツリー表示 BRON 投稿数: 34 登録日時: 01/03/05 SQLによる日付リストの生成 投稿時刻: 2006/01/16 17:57   返信 Oracle 8.1.6.3(10gにアップグレードの予定あり)、Solaris 5.8の環境です。 ある年の金曜日の日付全部のリストをSQL文一発で生成することは可能でしょ うか。例えば2006年なら、 2006-1-6 2006-1-13 2006-1-20 ... という具合です。 アプリケーションのLOVの値に反映させたいのでできるだけ速いレスポンスを めざしてはいるのですが、専用にテーブルを作成して値を持つのは避けたいの です。 よろしくおねがいします。 jiropochi 投稿数: 5,213 登録日時: 00/04/03 RE:SQLによる日付リストの生成 投稿時刻: 2006/01/16 18:16   BRON さんへの返信です。   返信 select d from ( select to_date('&nen' || '0101','yyyymmdd') + rownum d from all_catalog where rownum <= 366 ) where d < add_months(to_date('&nen','yyyy'),12) and to_char(d,'d') = 6 ; ていうのは？ ＠ ・ ｘ ・ ＠３ 投稿数: 1,230 登録日時: 02/10/03 RE[1]:SQLによる日付リストの生成 投稿時刻: 2006/01/16 18:28   jiropochi さんへの返信です。   返信 >select > d >from ( > select to_date('&nen' || '0101','yyyymmdd') + rownum d > from all_catalog > where rownum <= 366 >) >where d < add_months(to_date('&nen','yyyy'),12) >and to_char(d,'d') = 6 >; > >ていうのは？ 上記だと01/01が金曜日だとダメなので・・・ select d from ( select to_date('&nen' || '0101','yyyymmdd') + (rownum - 1) d from all_catalog where rownum <= 366 ) where d < add_months(to_date('&&nen','yyyy'),12) and to_char(d,'d') = 6 ; でしょうか・・・ jiropochi 投稿数: 5,213 登録日時: 00/04/03 RE[2]:SQLによる日付リストの生成 投稿時刻: 2006/01/16 18:35   ＠ ・ ｘ ・ ＠３ さんへの返信です。   返信 >上記だと01/01が金曜日だとダメなので・・・ あっ。確かに。フォローありがとうございます。 ＃ ぽじ BRON 投稿数: 34 登録日時: 01/03/05 RE[2]:SQLによる日付リストの生成 投稿時刻: 2006/01/16 19:51   ＠ ・ ｘ ・ ＠３ さんへの返信です。   返信 ありがとうございます。 ところで、all_catalogというのはdualみたいなものですか？ 検索してみましたがいまいちピンときませんでした。 ついでに教えていただければありがたいです。 koba_mac 投稿数: 1,273 登録日時: 00/05/21 RE[3]:SQLによる日付リストの生成 投稿時刻: 2006/01/16 20:17   BRON さんへの返信です。   返信 >ところで、all_catalogというのはdualみたいなものですか？ >検索してみましたがいまいちピンときませんでした。 >ついでに教えていただければありがたいです。 データ・ディクショナリの一部になります。 ALL_CATALOG にて、自分がアクセス可能な表、ビュー、順序、シノニムが 確認できます。 BRON 投稿数: 34 登録日時: 01/03/05 RE[4]:SQLによる日付リストの生成 投稿時刻: 2006/01/16 20:57   koba_mac さんへの返信です。   返信 >>ところで、all_catalogというのはdualみたいなものですか？ >>検索してみましたがいまいちピンときませんでした。 >>ついでに教えていただければありがたいです。 > >データ・ディクショナリの一部になります。 > >ALL_CATALOG にて、自分がアクセス可能な表、ビュー、順序、シノニムが >確認できます。 ありがとうございます。 しかし、恥ずかしながらどうしてこのSELECT文でall_catalogが使われるのか よくわかりません。ここはdualではだめなのですか。 (実行してみればよいのですが、いま手元に環境がないので) ＠ ・ ｘ ・ ＠３ 投稿数: 1,230 登録日時: 02/10/03 RE[5]:SQLによる日付リストの生成 投稿時刻: 2006/01/16 21:08   BRON さんへの返信です。   返信 >>>ところで、all_catalogというのはdualみたいなものですか？ >>>検索してみましたがいまいちピンときませんでした。 >>>ついでに教えていただければありがたいです。 >> >>データ・ディクショナリの一部になります。 >> >>ALL_CATALOG にて、自分がアクセス可能な表、ビュー、順序、シノニムが >>確認できます。 > >ありがとうございます。 >しかし、恥ずかしながらどうしてこのSELECT文でall_catalogが使われるのか >よくわかりません。ここはdualではだめなのですか。 >(実行してみればよいのですが、いま手元に環境がないので) 分解して考えてください。 select to_date('&nen' || '0101','yyyymmdd') + (rownum - 1) d from all_catalog where rownum <= 366 all_catalogはただの静的データ・ディクショナリ・ビューです。 構成カラムである OWNER / TABLE_NAME / TABLE_TYPE のどれも参照してません。抽出しているデータはrownumのみです。 ようは 1 から 366 までの１年の日数分のデータが取れれば良いわけです。 dualでやろうとすると思いっきりunionしないと上記のようにはいきません。 茶太郎 投稿数: 11,812 登録日時: 99/03/15 RE:SQLによる日付リストの生成 投稿時刻: 2006/01/16 21:14   BRON さんへの返信です。   返信 >ある年の金曜日の日付全部のリストをSQL文一発で生成することは可能でしょ >うか。例えば2006年なら、 > >2006-1-6 >2006-1-13 >2006-1-20 >... > >という具合です。 こちらのCodeTipsを応用させれば簡単です。 http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=234 ちなみにこちらのCodeTipsの説明にもあるように all_catalogである必要はありません。 366レコード以上あるテーブルならなんでも良いです。 BRON 投稿数: 34 登録日時: 01/03/05 ありがとうございました 投稿時刻: 2006/01/16 21:37   BRON さんへの返信です。   返信 みなさま、ありがとうございました。 SQLを書いて何年にもなるのに全く思いかない回答でした。 特にall_catalogをダミー的に使うというのは目からウロコが落ちた 思いです。 明智重蔵 投稿数: 1,883 登録日時: 03/10/30 別解です 投稿時刻: 2006/01/17 9:53   BRON さんへの返信です。   返信 select next_day(to_date('2006','yyyy')-1,'金') + 7*(RowNum-1) as 金曜 日 from (select 1 from all_catalog where RowNum <= ceil(366/7)) where to_char(next_day(to_date('2006','yyyy')-1,'金') + 7*(RowNum- 1),'yyyy') = to_char('2006'); 参考リンク http://www.benri.com/calendar/2006.html BRON 投稿数: 34 登録日時: 01/03/05 RE:別解です 投稿時刻: 2006/01/17 19:49   明智重蔵 さんへの返信です。   返信 ありがとうございます。 実は私も同じような考え方でしばらくいろいろ試していたのですが、 all_catalogのROWNUMを使うという技を知らなかったために行き詰った のでした。いろいろ勉強になりました。 kitkat 投稿数: 179 登録日時: 03/08/12 RE:SQLによる日付リストの生成 投稿時刻: 2006/01/17 10:17   BRON さんへの返信です。   返信 select to_date('&year'||'0101','yyyymmdd') - to_number(to_char(to_date('&year'||'0101','yyyymmdd'),'d')) + 6 + (n * 7) as "fri" from (select (rownum - 1) as n from all_catalog where ( to_number(to_char(to_date('&year'||'1231','yyyymmdd'),'d')) < 6 and rownum <= to_number(to_char(to_date('&year'||'1231','yyyymmdd'),'ww')) - 1) or ( to_number(to_char(to_date('&year'||'1231','yyyymmdd'),'d')) >= 6 and rownum <= to_number(to_char(to_date('&year'||'1231','yyyymmdd'),'ww')))) こんなのも、あり（面白い）かな？ BRON 投稿数: 34 登録日時: 01/03/05 RE[1]:SQLによる日付リストの生成 投稿時刻: 2006/01/17 19:53   kitkat さんへの返信です。   返信 ありがとうございます。 実はあまりよく理解できませんでした。ごめんなさい。 2005年で実行してみたら、2004/12/31が入ってしまいましたので直そうと 試みたのですが、どこをどういじってよいのやらわからなくて。 (面白い)とおっしゃっているということはきっとすごくユニークなんですね。 茶太郎 投稿数: 11,812 登録日時: 99/03/15 RE[2]:SQLによる日付リストの生成 投稿時刻: 2006/01/18 9:24   BRON さんへの返信です。   返信 >実はあまりよく理解できませんでした。ごめんなさい。 >2005年で実行してみたら、2004/12/31が入ってしまいましたので直そうと >試みたのですが、どこをどういじってよいのやらわからなくて。 その年の1週目に金曜日があるとは限らないということですね。 この場合下記のようにしたほうが無難な気がしますが。 select day from (select next_day(to_date('2005','yyyy')-1,6) + (rownum-1) * 7 day from all_catalog where rownum <= to_number(to_char(to_date('2005'||'1231','yyyymmdd'),'ww'))) where to_char(day,'yyyy') = '2005' order by day; でも#17129で提示したCodeTipsのようにカレンダーさえできれば あとはどうとでもなるのでそちらのほうが応用は効きそうですね。 kitkat 投稿数: 179 登録日時: 03/08/12 RE[2]:SQLによる日付リストの生成 投稿時刻: 2006/01/18 10:23   BRON さんへの返信です。   返信 >実はあまりよく理解できませんでした。ごめんなさい。 >2005年で実行してみたら、2004/12/31が入ってしまいましたので直そうと >試みたのですが、どこをどういじってよいのやらわからなくて。 う、失礼しました。 #指定年中の日数# to_date('&year'||'1231','yyyymmdd') - to_date('&year','yyyy') + 1 #指定年の最初の金曜日までの日数# 6 - to_number(to_char(to_date('&year','yyyy'),'ww')) #指定年中の金曜日の日数# trunc((#指定年中の日数# - #指定年の最初の金曜日までの日数#) / 7) = trunc(( to_date('&year'||'1231','yyyymmdd') - to_date('&year','yyyy') + to_number(to_char(to_date('&year','yyyy'),'ww')) - 5) / 7) select next_day((to_date('&year','yyyy') - 1),6) + (n * 7) as fri from (select (rownum - 1) as n from all_catalog where rownum <= (( to_date('&year'||'1231','yyyymmdd') - to_date('&year','yyyy') + to_number(to_char(to_date('&year','yyyy'),'ww')) - 5) / 7) これで、いけそう。（しつこくて、すいません^^;） 茶太郎 投稿数: 11,812 登録日時: 99/03/15 RE[3]:SQLによる日付リストの生成 投稿時刻: 2006/01/18 10:30   kitkat さんへの返信です。   返信 >これで、いけそう。（しつこくて、すいません^^;） SQLエラーになっちゃいますね。 括弧の数を合わせて2005年で実行してみると今度は 逆に最後の金曜日がなくなっちゃいますね。^^; 茶太郎 投稿数: 11,812 登録日時: 99/03/15 RE[4]:SQLによる日付リストの生成 投稿時刻: 2006/01/18 11:04   茶太郎 さんへの返信です。   返信 この方法ならこんな感じかな。 select next_day(to_date('2005','yyyy') - 1,6) + (rownum-1) * 7 as fri from all_catalog where rownum <= trunc((to_date('2005'||'1231','yyyymmdd') - to_date('2005','yyyy') + 1 - to_number(to_char(next_day(to_date('2005','yyyy')-1,6),'dd')))/7) + 1; kitkat 投稿数: 179 登録日時: 03/08/12 RE[4]:SQLによる日付リストの生成 投稿時刻: 2006/01/18 11:19   茶太郎 さんへの返信です。   返信 >SQLエラーになっちゃいますね。 >括弧の数を合わせて2005年で実行してみると今度は >逆に最後の金曜日がなくなっちゃいますね。^^; 最後の括弧忘れてるし、 > #指定年中の日数# > to_date('&year'||'1231','yyyymmdd') - to_date('&year','yyyy') + 1 > > #指定年の最初の金曜日までの日数# > 6 - to_number(to_char(to_date('&year','yyyy'),'ww')) next_day(to_date('&year','yyyy') - 1, 6) - to_date('&year','yyyy') じゃないと、ダメだし、 > #指定年中の金曜日の日数# > trunc((#指定年中の日数# - #指定年の最初の金曜日までの日数#) / 7) trunc((#指定年中の日数# - #指定年の最初の金曜日までの日数# + 6) / 7) = trunc(( to_date('&year'||'1231','yyyymmdd') - next_day(to_date('&year','yyyy') - 1, 6) + 7) / 7) 切り捨てじゃなくて、切り上げだし、 select next_day((to_date('&year','yyyy') - 1),6) + (n * 7) as fri from (select (rownum - 1) as n from all_catalog where rownum <= (( to_date('&year'||'1231','yyyymmdd') - next_day(to_date('&year','yyyy') - 1, 6) + 7) / 7)) ボロボロだぁ。 ＃風邪引いてる時は、大人しくしてた方が、良いみたい。 ＃仕事ほったらかして、帰って寝るかな。 スレッド一覧へ