【やりたいこと】
下記のようなポイント履歴のテーブルがあり、
・日付カラムの項目から遡って3カ月以内にポイントが使われていないユーザー ⇒新規ユーザー
・日付カラムの項目から遡って3カ月以内にポイントが使われているユーザー ⇒既存ユーザー
といったフラグをテーブルに追加したい。
〈ポイント履歴のテーブル〉
・日付カラム
・ユーザーiD
・ユーザーポイント ←日付カラム時点での付与されたポイントを示してます
・ユーザーの属性
(・作成したいフラグ)
また、実行計画としては、
・ポイント履歴からフラグを追記
・他のテーブルに、ユーザーiDやキャンペーンID、キャンペーンデバイスなどのキャンペーン周りのテーブルがあるので、ユーザーidにて紐づけて、キャンペーン内容、キャンペーン期間ごとのユーザーの抽出を行いたいと思ってます。
【困っている点】
各々の各行で日付項目を確認し、その日付範囲から3カ月以内のユーザー履歴を確認する、といったことの実現方法がわからず困ってます。
・年月日のカラム内の日時から3カ月以内をフラグ付与の期間としたいです。
⇒テーブルには同一ユーザーIDが複数あるため、その各々の"各行"の年月日で抽出したいと思っております。
・フラグの付与は、どのようにつけますか? & フラグ付けの要件について
⇒各Ptがすべて0orNULLでない場合 ⇒ 新規会員
各PtでどこかのPtカラムに値あり ⇒ 既存会員
といった新規/既存会員のカラムをテーブルに追加したいです
現状、下記のように、初回日付で必ず"新規"の扱いとなってしまっているため、原因を教えて頂きたいです。(初回日付であっても、ポイントがある場合は"既存"の扱いとしたいです)
date user_id pt flag
20171001 a 10 新規
20171007 a 20 既存
20171015 a '' 既存
20171025 a 40 既存
【実行環境】
Windows
Postgre(pgAdmin4内のクエリツールよりクエリを書いてます) を利用しています。
【現状】
皆様から頂いた回答より、
・serialをPKに設定したテーブルを作り直し
・インデックスをdateとuser_idに設定
・下記クエリを実行(処理時間はかかりますが、結果はでます)
↓
困っている点 が現在の状態になります。
〈現時点での実装内容〉
select *, case when exist_point>0 then '既存' else '新規' end as new_or_existing
from (
select *
,(select sum(coalesce(fuyo_pt_tj,0)+coalesce(kan_pt_tj,0)+coalesce(kan_pt_kg,0))
from point_rireki2
where user_id=main.user_id and "date" >= main."date" - interval '3 months' and "date" < main."date"
) as exist_point
from point_rireki2 main
) step1
order by user_id, date
【参照】
回答頂いた方から、このようなURLで共有できるものを教えていただきました。
まさに、このような感じのデータとなります。(属性は、細かく記載させていただくと年齢や性別などのデータです。userポイントは、その日付時点で付与されたポイントになります)
http://sqlfiddle.com/#!17/72bf4/1
【追記】
対象のオブジェクトとそのバイトですが、
・object name: point_rireki
・bytes text : 68,108,288
*下記参考に、上記情報を追記しました
http://dqn.sakusakutto.jp/2011/11/postgresql.html
回答4件
あなたの回答
tips
プレビュー