Q&A
aaaとかbbbとか特に意味のないカラム名にするとかえって捉えにくい（思考が巡りにくい）ので、そこそこ意味がありそうな英単語を活用したほうが良いと思います。さらに、cccは年月日らしいのですが、数字ですが、文字列ですか、DATE型ですか、TIMESTAMP型ですか、タイムゾーンはつきますか。
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すればいいのわかるのですがこれを組み合わせて書くにはどう書けばよろしいでしょうか？
回答1件
0
ベストアンサー
少し長くなってしまいました。
sql
1select t3.card_no, t3.rank, t3.ymd, t3.flg, 2t6.rank, t6.ymd, t6.flg from 3(select t1.* from abc t1 join (select card_no, max(ymd) ymd from abc where flg='0' group by 1) t2 4 on t1.card_no=t2.card_no and t1.ymd=t2.ymd and t1.flg='0') t3 5left join 6(select t5.* from abc t5 join 7 (select card_no, max(ymd) ymd from abc where flg='1' and 8 ymd between (substr('20170821133000',1,6)||'01')::date - interval '16 month' 9 and (substr('20170821133000',1,6)||'12')::date - interval '16 month' 10 group by 1) t4 11 on t5.card_no=t4.card_no and t5.ymd=t4.ymd and t5. flg='1') t6 12using (card_no); 13 14 15 card_no | rank | ymd | flg | rank | ymd | flg 16---------+------+---------------------+-----+------+---------------------+----- 17 1020 | 2 | 2017-04-21 00:00:00 | 0 | | | 18 1050 | 1 | 2017-05-21 00:00:00 | 0 | 2 | 2016-04-11 00:00:00 | 1 19 1040 | 1 | 2017-07-21 00:00:00 | 0 | 5 | 2016-04-04 00:00:00 | 1 20(3 rows) 21 22
変更しました,修正しました２
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は、重複していて、その中より最新で、かつフラグが０のデータを抽出します。
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 11:24
編集2017/01/08 02:54
総合スコア4070
回答していただきありがとうございます！
16月にしているのは何故なのでしょうか？
2017年08月の１５か月前が５月なので１６としました。
説明が見にくくてすみません。
毎回引数で渡された月日からの15ヶ月前の西暦を撮りたいのですが
その場合は16のところを15にしたらよろしいでしょうか？
4月1日〜11日の月日間は変数で持ちますので
ありがとうございます
質問なのですがこれは
whereが見当たらず
どうやって
4月1日から4月11日の間を取っているのかが理解できなくて
どのように今回はソートしているのか教えていただけるととても助かります。
ご丁寧にありがとうございます！
一度試してみてまた何かありましたら質問させていただきます！
あなたの回答
tips
プレビュー
