オラクルで自己結合をし、昨日売上と1年前の売上を表示するには？

Question

###前提・実現したいこと
オラクル11gを使用しているが、
売上テーブルで、昨日の売上と、そこから-364日前の売上とを同時に表示したい。

###発生している問題・エラーメッセージ

この場合、同テーブルのJOINになると思うのですが、
効率のよいSQLクエリがわかりません。。

とりあえず、以下のようにONをずらすような記載をしてみてたのですが、可視性が高いようにはみえません・・

恐れ入りますが、効率のよい方法や、可視性が高いクエリ案がありましたらご教示いただけますか？

また、Ditinctしないと重複するのですが、なぜ重複するのかが今いちイメージがわきません。
御手数ですが、ご教示いただけますか？

###該当のソースコード

select
distinct
 uri.id, --ショップ番号
 uri.businessdate, --日付
 preuri.businessdate as predate, --比較日付
 uri.amount, --税抜売上
 preuri.amount as presales --比較売上
from 
 tableA uri

--前比を出すためにもっかいJOIN
left join
  tableA preuri
on 
  (
   to_date(preuri.businessdate +364) = uri.businessdate
     and
   preuri.id = uri.id
  )
where 
 uri.businessdate >= to_date(sysdate-1)
 order by uri.businessdate desc ,id asc

以下追加しました。
■項目情報
"ID" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
"BUSINESSDATE" TIMESTAMP (6) NOT NULL ENABLE, 
"AMOUNT" NUMBER(10,0)

Comments

[Orlofsky]

SYSDATEはTO_DATEしないでください。businessdate列はDATE型でしたらTO_DATEしないでください。CREATE TABLE, CREATE INDEXも載せていただけますか？

[Orlofsky]

あと、閏日があるから -364 では前年同月同日にはなりません。

Answer 1

月日が同じで1年前ということであれば、
ADD_MONTHS - オラクル・Oracle SQL 関数リファレンス
を使えばうるう年であっても-12ヶ月で合わせられます。

ADD_MONTHS(BUSINESSDATE, -12)と比較すればOK

---

同じ週の同じ曜日ということであれば、
週番号を駆使するといいかもしれません。

Oracle ISO週番号 | ALT

Comments

[Shinno]

回答ありがとうございます！
-364は前年同曜日比較をしたく、この比較にしておりました。
うるう年でこれが使えない気がするので、もう少し考えてみたいと思っております。。。

[退会済みユーザー]

週番号と曜日を求めてみるのはいかがでしょうか。回答に加筆しました。

[Shinno]

ありがとうございます！こういうのがあるんですね！！
活用してみようと思います。

しかも、第一木曜日がWeek１扱いになるようなので、社内ルールと合致します。
別のところでも使えそうです。
ありがとうございます！！

Answer 2

なぜ、TIMESTAMP型でテーブル設計したのか解りませんが、m6uさんの回答のADD_MONTHSの説明にもあります。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS' ;
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY/MM/DD HH24:MI:SSXFF TZR' ;
SQL> SELECT SYSTIMESTAMP, ADD_MONTHS(SYSTIMESTAMP, -12) AS LAST_YEAR FROM DUAL ;

SYSTIMESTAMP                      LAST_YEAR
--------------------------------- -------------------
2017/03/03 20:34:07.767000 +09:00 2016/03/03 20:34:07

とADD_MONTHSの結果は秒未満が切り捨てられますから、ご注意を。
テーブルでTIMESTAMP型で設計しても秒未満のデータが入っていなければ大丈夫ですが。

元質問者はSQL入門書を一通り勉強された方が良いです。

Comments

[Shinno]

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

はじめから用意されていた環境がTIMESTAMPということと、ORACLEのSQLクエリを書くのは初めてということもあり、なかなか苦戦しております。

閏日のことはたしかにご指摘どおりでした。
そうすると-364ではいけないかもしれませんね。。

当時の想定は前年同曜日だったのですが、みなさまの教えていただいたADD_MONTHSのマイナス値でも考えて、仕様をもう一度もんでみます。。

また、Orlofskyさんのおすすめの書籍等あればご紹介いただけると嬉しいです。

[Orlofsky]

Oracle SQLは初めてということですが、TIMESTAMP型にTO_DATEしているし、CREATE TABLE, CREATE INDEXを提示してください、って書いても列の並びしか載せないし、この人はまったくSQLの基礎がないに違いない程度のことはわかるし、なんでDISTINCTが必要なんだろう？SUM... GROUP BY が必要じゃないのだろうか？きのうと前年同曜日との比較なら
SELECT TO_CHAR(SYSTIMESTAMP, 'IYYYIWD') AS YYYYWD FROM DUAL ;
http://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements004.htm#i34924
程度のSQLはわかっていないと難しいだろうとか、突っ込みどころ満載だし、TIMESTAMP型を使っているのは同じIDでBUSINESSDATEが同の一年月日時分秒のため秒未満のデータも含めることでUNIQUEにしているのだろうか？

職場に質問できる先輩などがいなければ、のんびりと入門書を読んでいたら納期に間に合わないでしょうから、SQL入門程度の有償研修に行かれた方が良いです。
http://www.oracle.com/jp/education/promotion/course-flow-database.html
テーブル設計からの見直しが必要かもしれませんから、それなりのお金をかけてきちんとした経験者を参入させた方が良さそうに思えます。

[Shinno]

私の知識不足とデータのマスク、一部テーブル情報の省略でたしかに頓珍漢な質問になってしまっていたと思います。

正直聞ける人がいないので、ここを活用したつもりだったのですが・・・
ちょっと厳しいですね。。

いろいろとご教示いただきありがとうございました。