3 Replies Latest reply: Sep 13, 2012 7:17 PM by tmtsmic RSS

    「PARTITION BY」について

    961952
      はじめまして。

      いまORACLE10gを使用している者です。

      「PARTITION BY」について質問したいことがあります。

      いまたとえば『売上テーブル』があって、
      「売上年月日」、「売上金額」というカラムを持っているとします。

      売上年月日 売上金額
      ---------- ------------
      2012/07/10 500,000
      2012/07/20 3,000,000
      2012/07/30 1,000,000
      2012/08/10 2,500,000
      2012/08/20 5,000,000
      2012/08/30 8,000,000
      2012/09/10 300,000
      2012/09/20 1,200,000
      2012/09/30 3,000,000

      とあるとして、
      このテーブルからつぎのような結果を1行(!)で取得したいのです。

      売上年月_7月 売上年月_8月 売上年月_9月
      ------------ ------------ ------------
      4,500,000 15,500,000 4,500,000

      そこで下記のように試みたのですが「PARTITION BY」の箇所で文法エラーになってしまいます。

      select to_date(売上年月, 'yyyy/mm'),
      sum(売上金額) over (partition by to_date(売上年月, 'yyyy/mm') group by to_date(売上年月, 'yyyy/mm')
      range between to_date('2012/07', 'yyyy/mm') preceding and to_date('2012/07', 'yyyy/mm') following) 売上金額計_7月
      sum(売上金額) over (partition by to_date(売上年月, 'yyyy/mm') group by to_date(売上年月, 'yyyy/mm')
      range between to_date('2012/08', 'yyyy/mm') preceding and to_date('2012/08', 'yyyy/mm') following) 売上金額計_8月
      sum(売上金額) over (partition by to_date(売上年月, 'yyyy/mm') group by to_date(売上年月, 'yyyy/mm')
      range between to_date('2012/09', 'yyyy/mm') preceding and to_date('2012/09', 'yyyy/mm') following) 売上金額計_9月
      from 売上
      ;

      上記の結果をSQLで(PLSQLを使わずに)1行で取れる方法を教えてください。

      宜しくお願いします。
        • 1. Re: 「PARTITION BY」について
          weyk
          転記ミスではとは思いますが念のため。
          to_dateしているということは、売上年月日はcharかvarchar2型でしょうか。
          データと書式があってませんよ。

          もし、売上年月日がdate型だとしたら、to_dateではなく、to_charではないでしょうか。

          なお、「to_date('2012/07', 'yyyy/mm')」は正しいです。

          追記。
           date型を表示したり文字列と比較するため 等で文字列にしたいときは、to_char()関数を使います。
           charやvarchar型をdate型に変換するには、to_date()関数を使用します。
           date型を月単位で足し引きするには、add_month()関数を使います。
           date型の内容を、ある単位(月とか日とか)にて切りそろえるには、trunc()関数を使います。
           (第2引数により、月や年や時といった、精度を指定できます。省略すると精度は日です。)

           特に、date型が暗黙的に型変換されないよう注意してみてください。デフォルトの書式に
          依存してしまいます(to_date()の第1引数はcharかvarcharです。date型を渡すと、
          暗黙のうちにcharだかvarchar型変換されて、そのうえで処理されます)

          Edited by: weyk on 2012/09/14 8:32
          • 2. Re: 「PARTITION BY」について
            961952
            回答ありがとうございます。

            おっしゃる通り型変換は転記ミスです(^_^;)

            trunc関数はそんなふうにも使えるんですね。
            勉強になります。ありがとうございます。

            あとできればこのレスの本題の「partition by」の書き方でも
            ご享受頂けると幸いです。
            • 3. Re: 「PARTITION BY」について
              tmtsmic
              こんにちは。

              いきなりたくさんの列のSQLにチャレンジするのではなく
              まずは1列、きちんと結果を出せるようにしてみては
              いかがでしょうか?

              こういうのはPL/SQLや分析関数を使わなくても
              CASE文(CASE式)などでベタにやっても期待する結果を実現できたりします。
              なので八方塞にはならないと思うのでご安心を。

              あと、テーブルのCREATE文やデータのINSERT文などもきちんと提示すると
              回答してくれる人が増えるかもしれませんよ。
              # INSERT文を作るのが正直、面倒でした・・・

              SQLを見た限り、何かいろいろと解釈を間違っていたりしないかな、
              と感じたりしています。
              分析関数の使い方は調べてみましたか?
              例えば明智さんの記事とか。
              http://www.oracle.com/technetwork/jp/articles/index-308624-ja.html


              #まずはweykさんに指摘されたことをきちんと直しましょう。