指定した日付のn年分、ｎか月ぶんのデータを取得したい

Question

こんにちわ
sql初心者です
DBはpostgres
VS2010でかいてます

指定した日付から1年後、2年後
または2か月後や5か月後、3日分や10日分のテーブルを取得したいです

テーブルの中身はこんなかんじです

たとえば

 SELECT '2014' as TRANSITION_YEAR, sum_date as TRANSITION_COUNT ,user_cnt as MEMBER_NO
  FROM tt_s_umlist
  WHERE sum_date = '2014-12-31';

これだと本当に2014-12-31のuser_cntのデータ1行のみ取得できます

これを2年間、5年間など指定した年数の同じ日付のuser_cntを取得して表示させたいです
変数xに「2」年間、「5」年間といった風に何年分取得したいのかの、数字のみの変数はあります

ちなみに今は具体的な数字を入れてますが'2014'と'2014-12-31'は変数になります

イメージとしては2014(変数)年（2014-12-31）から3(変数)年間取得したいってしたら
2014 2014-12-31 1050
2015 2015-12-31 2010(適当な数字です)
2016 2016-12-31 3020(適当な数字です)
と3行表示できればいいです

説明下手ですみません
できたら同じ要領で
2014-04-01から3か月取得したいとしたら
2014-04-01のuser_cnt
2014-05-01のuser_cnt
2014-06-01のuser_cnt
と3行取得したいです

アドバイスおねがいいたします

Answer 1

うん…年月日分かれてるし…解決したい課題がよくわからないのですが。

---

年単位の絞り込み

SELECT *
FROM tt_s_umlist
WHERE 
sum_year BETWEEN @TargetYear - @Offset AND @TargetYear
AND sum_month = @TargetMonth
AND sum_day = @TargetDay

もしも受け取ってくる変数の日付2014-12-31が変更できないなら
日付型から年月日を算出しましょう。

SELECT *
FROM tt_s_umlist
WHERE 
sum_year BETWEEN EXTRACT(year FROM @TargetDate) - @Offset AND EXTRACT(year FROM @TargetDate)
AND sum_month = EXTRACT(month FROM @TargetDate)
AND sum_day = EXTRACT(day FROM @TargetDate)

---

月単位の絞り込み

少し年またぎとかが面倒になりますね。
sum_dateのインターバルを定義して期間を作り絞り込み、日付だけ指定すればいいでしょう。

SELECT *
FROM tt_s_umlist
WHERE 
sum_date BETWEEN @TargetDate - interval '2 months' AND @TargetDate
AND sum_day = @Day

又は

SELECT *
FROM tt_s_umlist
WHERE 
sum_date BETWEEN @TargetDate - interval '2 months' AND @TargetDate
AND sum_day = EXTRACT(day FROM @TargetDate)

ただ、パラメータとしてのintervalの渡し方はちょっとわからないですね…うーん…文字列にすればいいのかな…
ちょっとクエリの実行条件がわからないので、それより先を考えても仕方がないのでいったんここまで。

---

蛇足ですが、SQL文上で日付を直書きしたい場合は以下で大丈夫です。

SELECT * FROM tt_s_umlist WHERE
sum_date BETWEEN date '2014/12/01' AND date '2017/12/01'
AND sum_month = 12
AND sum_day = 1;

Comments

[Zuishin]

between は使ってはいけないそうです。なぜなら、2017/12/01 0:00:00 は含まれるけれど 0:00:01 は含まれないために見つけにくいバグの原因になることがあるそうです。

[haru666]

postgresql で date は時間数の精度が含まれていないので問題ないはずです。
ので、便利に書いていいのではないかと。

サーバー依存なコードを書くなというなら、確かにそうですが…

以下間違えてました。忘れてください。タイムスタンプ型だと、00:00:01は切り上げられますね。。。
date型なら安全ですが。
---
仮に2017/12/01をタイムスタンプで評価する場合も2017/12/01 00:00:00 ～ 23:59:59 の範囲内なら2017/12/01に丸められます。

[haru666]

timestampからdateへの型変換 ⇒ 時分数は切り捨てられる
timestamp型でdateを評価 ⇒ 00:00:01以降は当日が含まれない

動作別のようです。
型の混同が起きるようなコードはダメそうですね…

[haru666]

ん…？

考えてみたらBETWEEN使わなくても timestampで比較したらダメですよね…？

すみません、BP教えてもらってもいいでしょうか。

[Zuishin]

私の回答も <= が使われていますね。< と書いたつもりで間違えていました。
時間の精度が含まれていないのでおっしゃる通り問題ありません。
問題ありませんが……心がけるよう言われたことです。

[haru666]

なるほど。

確かにタイムスタンプのデータとDate型で扱いが異なるため、それは重要なことかもしれませんね。
タイムスタンプ型の場合日付的には範囲が必要で、BETWEENで「開始日～終了日＋1日」とした場合、00:00:00時のみ翌日が含まれてしまうため、以上～未満で比較しなければなりません。

そういう意味では確かに常日頃のミスを避けるためにはBETWEENを使うべきではないですね。

私のチームは冗長ですが、日付とタイムスタンプ両方登録しています。
ほぼ全てのテーブル（サイズにピーキーでないもの）以外はルールとしてタイムスタンプを持っているので、日付や時間を目的とするテーブルには冗長ですが別の列を付加して、そちらにインデックスを張っています。

正しいかどうかはわかりませんが…。それによってタイムスタンプの比較文を無くしています。

Answer 2

sum_yearとsum_monthlyとsum_dayを使いました。

create temp table t_hoge (sum_year int2, sum_monthly int2, sum_day int2, sum_date date, user_cnt int);

-- 2年間、5年間など指定した年数の同じ日付のuser_cntを取得
-- 2014-12-31から３年間

select sum_year, sum_date, user_cnt from t_hoge
where sum_year >= substr('2014-12-31',1,4)::numeric and  sum_year < substr('2014-12-31',1,4)::numeric + 3
and   sum_day   = substr('2014-12-31',9,2)::numeric;

-- 2014-04-01から3か月取得

select sum_year, sum_date, user_cnt from t_hoge
where sum_year    between to_char('2014-04-01'::date,'YYYY')::numeric and to_char((date '2014-04-01' +interval '3 month'),'YYYY')::numeric
and   not (sum_year = to_char('2014-04-01'::date,'YYYY')::numeric and sum_monthly < substr('2014-04-01',6,2)::numeric)
and   not (sum_year = to_char((date '2014-04-01' +interval '3 month'),'YYYY')::numeric and sum_monthly >= to_char((date '2014-04-01' +interval '3 month'),'mm')::numeric)
and   sum_day = substr('2014-04-01',9,2)::numeric;

Answer 3

PostgreSQL による日付・時刻・時間の計算・演算のまとめ がわかりやすいと思います。

SELECT '2014' as TRANSITION_YEAR, sum_date as TRANSITION_COUNT ,user_cnt as MEMBER_NO
  FROM tt_s_umlist
  WHERE sum_date >= CAST('2014-12-31' AS DATE)
  AND sum_date <= CAST('2014-12-31' AS DATE) + CAST('2 months' AS INTERVAL);

追記

  AND sum_date < CAST('2014-12-31' AS DATE) + CAST('2 months' AS INTERVAL);

< と間違えて <= と書いていました。

Answer 4

C#を利用してよいのであれば、
例えば
2014(変数)年（2014-12-31）から3(変数)年間取得したい場合

SELECT '2014' as TRANSITION_YEAR, sum_date as TRANSITION_COUNT ,user_cnt as MEMBER_NO
  FROM tt_s_umlist
  WHERE sum_date = '2014-12-31'
  OR sum_date = '2015-12-31';
  OR sum_date = '2016-12-31';

というSQLとなるので、
このSQL文を作るC#の箇所をいじってやればよいと思われます。
基本的なSQLの部分

SELECT '2014' as TRANSITION_YEAR, sum_date as TRANSITION_COUNT ,user_cnt as MEMBER_NO
  FROM tt_s_umlist

はそのまま保持しておいて、
WHERE句を作る関数なんかを準備してやればよいと思います。

// ○年間を指定した場合
public string makeWhere(string from, string term){
    var where = "";
    var year = from.substring(0,4);
    var flg = true;
    for(int i=1; i<=int.Parse(term); i++){
        var yearInt = int.Parse(year) + i
        if(flg){
            // 初回の頭はWHERE
            where += " WHERE sum_date=" + yearInt.toString();
        }else{
            // 2回目からはOR
            where += " OR ";
        }
        flg = false;
    }
}

みたいな感じでしょうか・・・
検証してないので動かないと思いますが、
泥臭い書き方だと考え方はそんな感じです。

sum_dateの型がstringじゃなければもっとよい方法が・・・

Comments

[Zuishin]

sum_date のカラム名の下に date と書いてあるので日付型だと思いますよ。

[s.t.]

うおおお、ほんとうだ、超はずかしい・・・