質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.83%

  • PHP

    18239questions

    PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

  • PostgreSQL

    911questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

postgreSQLで同じテーブル通しでjoinする

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 469

amaguri

score 211

PHPを利用してpostgreSQLを実行

前提条件
現在の日付が20170821133000
YYYYMMDDHHMMSS型として引数がPHPに渡される。

abcテーブル
カラム
card_no bytea型 
rank character (1)型
ymd timestamp p with timezone型
flg character(1)


以下

card_noは複合済みとして表記
ymdはYYYY-MM-DDのように省略させていただきます。
実際は2017-01-04 14:23.12.939117+09のような形で入っています。

card_no  rank     ymd         flg
 1020     1     2017-02-12   0 
 1020     2     2017-04-21   0 
 1020     3     2016-12-23   1
 1050     1     2017-05-21   0
 1050     2     2016-04-11   1
 1050     3     2016-06-23   1
 1050     3     2016-04-21   1
 1040     1     2017-07-21   0
 1040     5     2016-04-04   1

こういうデータがあり
①idが重複せず 最新の日付のflgが0のデータを取得。
②idが重複せず 現在の西暦月から-15ヶ月引いた西暦の4月1日から4月11日までの間で日付が12日に一番近いflgが1のデータを取得。

2017年8月の場合
2016年の3月になるので
2016年とする。

2017年1月の場合
2015年10月なので
2015年とする。

③ ①と②を①基準でjoinする。

完成イメージ

card_no   rank    ymd        flg    rank     ymd        flg 
 1020     2   2017-04-21   0   
 1050     1   2017-05-21   0     2   2016-04-11    1
 1040     1   2017-07-21   0     5   2016-04-04    1


のようなデータが
取得できるためにはどのようにクエリを書けばいいでしょうか?

①は最新のものをwhere で最新のflg=0のもの
②はwhereの条件はPHPで現在の西暦月をYYYYMMDDHHMMSS形式で引数から取得してYYYYMMから15ヶ月引いた西暦+4月1日〜11日からの間でflg=1のもの
③それらをjoinすればいいのわかるのですがこれを組み合わせて書くにはどう書けばよろしいでしょうか?

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • m6u

    2017/01/06 19:11 編集

    aaaとかbbbとか特に意味のないカラム名にするとかえって捉えにくい(思考が巡りにくい)ので、そこそこ意味がありそうな英単語を活用したほうが良いと思います。さらに、cccは年月日らしいのですが、数字ですが、文字列ですか、DATE型ですか、TIMESTAMP型ですか、タイムゾーンはつきますか。

    キャンセル

  • amaguri

    2017/01/06 19:20

    見やすいように修正させていただきます

    キャンセル

  • 退会済みユーザー

    退会済みユーザー

    2017/01/06 19:39

    データはテーブルのマークダウンで

    キャンセル

  • amaguri

    2017/01/06 19:47

    見にくくて申し訳有りません。修正させていただきました

    キャンセル

回答 1

checkベストアンサー

+1

少し長くなってしまいました。

select t3.card_no,  t3.rank,  t3.ymd, t3.flg,
t6.rank,  t6.ymd, t6.flg from
(select t1.* from abc t1 join (select card_no, max(ymd) ymd from abc where flg='0' group by 1) t2
 on t1.card_no=t2.card_no and t1.ymd=t2.ymd and t1.flg='0') t3
left join
(select t5.* from abc t5 join
  (select card_no, max(ymd) ymd from abc where flg='1' and
   ymd between (substr('20170821133000',1,6)||'01')::date - interval '16 month'
        and (substr('20170821133000',1,6)||'12')::date - interval '16 month'
   group by 1) t4
   on t5.card_no=t4.card_no and t5.ymd=t4.ymd and t5. flg='1') t6
using (card_no);


 card_no | rank |         ymd         | flg | rank |         ymd         | flg
---------+------+---------------------+-----+------+---------------------+-----
    1020 | 2    | 2017-04-21 00:00:00 | 0   |      |                     |
    1050 | 1    | 2017-05-21 00:00:00 | 0   | 2    | 2016-04-11 00:00:00 | 1
    1040 | 1    | 2017-07-21 00:00:00 | 0   | 5    | 2016-04-04 00:00:00 | 1
(3 rows)

変更しました,修正しました2

select t3.card_no,  t3.rank,  t3.ymd, t3.flg,
t6.rank,  t6.ymd, t6.flg from
(select t1.* from abc t1 join (select card_no, max(ymd) ymd from abc where flg='0' group by 1) t2
 on t1.card_no=t2.card_no and t1.ymd=t2.ymd and t1.flg='0') t3
left join
(select t5.* from abc t5 join
  (select card_no, max(ymd) ymd from abc where flg='1' and
   ymd between (to_char((substr('20170821133000',1,8))::date - interval '15 month','YYYY')||'0401')::date
        and ( to_char((substr('20170821133000',1,8))::date - interval '15 month','YYYY')||'0411')::date
   group by 1) t4
   on t5.card_no=t4.card_no and t5.ymd=t4.ymd and t5. flg='1') t6
using (card_no);

要件をどのように考えたかをご説明します。下記の解釈に従って作成しました。

①idが重複せず 最新の日付のflgが0のデータを取得。
card_noは、重複していて、その中より最新で、かつフラグが0のデータを抽出します。
select t1.* from abc t1 join (select card_no, max(ymd) ymd from abc where flg='0' group by 1) t2
on t1.card_no=t2.card_no and t1.ymd=t2.ymd and t1.flg='0') t3

②idが重複せず 現在の西暦月から-15ヶ月引いた西暦の4月1日から4月11日までの間で日付が12日に一番近いflgが1のデータを取得

現在の西暦月は、プログラムで設定する値と想像して、SQL中はコンスタントの値として'20170821133000'を使っています。
15ヶ月引いた西暦を求めて、その年の4月1日から11日のものを抽出します。
※日付の場合、CAST(::date)して数字を日付型に変換してから扱います。
・15ヶ月引いた西暦
to_char((substr('20170821133000',1,8))::date - interval '15 month','YYYY')
・上記西暦の4月1日
(to_char(…)||'0401')::date
・同様に
(to_char(…)||'0411')::date
・4月1日から11日中で同じcard_noで一番大きい日付を抽出するします( flg='1') 
 
③ ①と②を①基準でjoinする。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/01/06 23:22 編集

    回答していただきありがとうございます!
    16月にしているのは何故なのでしょうか?

    キャンセル

  • 2017/01/06 23:37

    2017年08月の15か月前が5月なので16としました。

    キャンセル

  • 2017/01/07 08:37

    説明が見にくくてすみません。

    毎回引数で渡された月日からの15ヶ月前の西暦を撮りたいのですが
    その場合は16のところを15にしたらよろしいでしょうか?

    4月1日〜11日の月日間は変数で持ちますので

    キャンセル

  • 2017/01/08 10:30

    ありがとうございます

    質問なのですがこれは
    whereが見当たらず
    どうやって
    4月1日から4月11日の間を取っているのかが理解できなくて
    どのように今回はソートしているのか教えていただけるととても助かります。

    キャンセル

  • 2017/01/10 10:17

    ご丁寧にありがとうございます!
    一度試してみてまた何かありましたら質問させていただきます!

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.83%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • PHP

    18239questions

    PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

  • PostgreSQL

    911questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。