teratail
回答率
85.29%
質問するログイン新規登録
トップPostgreSQLに関する質問SQL:既存の条件に別の条件を抽出するフラグを追加したい

編集履歴

回答編集履歴

13

推敲

2018/03/23 08:23

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -28,13 +28,13 @@
28
28
  from param cross join point_rireki2 pnt
29
29
  )
30
30
  , point_agg as (--ポイント履歴属性集計
31
- select shisaku_flg, demogra_flg, count(distinct kihon_id) as patarn_count
31
+ select shisaku_flg, demogra_flg, count(distinct kihon_id) as pattern_count
32
32
  from point_attribute
33
33
  group by shisaku_flg, demogra_flg
34
34
  order by shisaku_flg, demogra_flg
35
35
  )
36
36
  -- パターン別集計表（組合せ表とポイント履歴属性集計を結合し件数を取得）
37
- select ptn.*, coalesce(pntagg.patarn_count,0)
37
+ select ptn.*, coalesce(pntagg.pattern_count,0)
38
38
  from pattern ptn left join point_agg pntagg
39
39
  on ptn.shisaku_flg=pntagg.shisaku_flg
40
40
  and ptn.demogra_flg=pntagg.demogra_flg

12

修正

2018/03/23 08:23

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -45,6 +45,5 @@
45
45
 
46
46
  追記
47
47
  --
48
- 「キャンペーン期間：1/26～2/9」を条件に含めると
48
+ 「キャンペーン期間：1/26～2/9」を条件に含めると、求めたい結果と異なるので含めていません。
49
- 求めたい結果と異なるので、含めていません。
50
49
  必要なら、point_attribute内で条件を直接追加すれば確認できます。

11

修正

2018/03/22 10:41

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -33,7 +33,7 @@
33
33
  group by shisaku_flg, demogra_flg
34
34
  order by shisaku_flg, demogra_flg
35
35
  )
36
- -- パターン別集計表（組合せ表とポイント属性履歴を結合し件数を取得）
36
+ -- パターン別集計表（組合せ表とポイント履歴属性集計を結合し件数を取得）
37
37
  select ptn.*, coalesce(pntagg.patarn_count,0)
38
38
  from pattern ptn left join point_agg pntagg
39
39
  on ptn.shisaku_flg=pntagg.shisaku_flg

10

追記

2018/03/22 10:40

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -5,16 +5,16 @@
5
5
 
6
6
  ```SQL
7
7
  with
8
- param as (
8
+ param as (-- パラメータ
9
9
  select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
10
10
  )
11
- , pattern as (
11
+ , pattern as (--組合せ表
12
12
  select shisaku_flg, demogra_flg1 || demogra_flg2 as demogra_flg
13
13
  from unnest(array['キャンペーンあり','キャンペーンなし']::text[]) as x(shisaku_flg)
14
14
  cross join unnest(array['M','F']::text[]) as y(demogra_flg1)
15
15
  cross join unnest(array['1','2','3']::text[]) as z(demogra_flg2)
16
16
  )
17
- , point_attribute as (
17
+ , point_attribute as (--ポイント履歴属性追加
18
18
  select kihon_id
19
19
  , case when exists(
20
20
  select 1 from campaign where apply_date < param.apply_date_limit and kihon_id=pnt.kihon_id and apply_date=pnt.p_date
@@ -27,12 +27,13 @@
27
27
  end as demogra_flg
28
28
  from param cross join point_rireki2 pnt
29
29
  )
30
- , point_agg as (
30
+ , point_agg as (--ポイント履歴属性集計
31
31
  select shisaku_flg, demogra_flg, count(distinct kihon_id) as patarn_count
32
32
  from point_attribute
33
33
  group by shisaku_flg, demogra_flg
34
34
  order by shisaku_flg, demogra_flg
35
35
  )
36
+ -- パターン別集計表（組合せ表とポイント属性履歴を結合し件数を取得）
36
37
  select ptn.*, coalesce(pntagg.patarn_count,0)
37
38
  from pattern ptn left join point_agg pntagg
38
39
  on ptn.shisaku_flg=pntagg.shisaku_flg

9

修正

2018/03/22 08:42

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -5,7 +5,10 @@
5
5
 
6
6
  ```SQL
7
7
  with
8
+ param as (
9
+ select '2018-02-07':: date apply_date_limit ,'2018-01-26':: date campaign_Start,'2018-02-09':: date campaign_End
10
+ )
8
- pattern as (
11
+ , pattern as (
9
12
  select shisaku_flg, demogra_flg1 || demogra_flg2 as demogra_flg
10
13
  from unnest(array['キャンペーンあり','キャンペーンなし']::text[]) as x(shisaku_flg)
11
14
  cross join unnest(array['M','F']::text[]) as y(demogra_flg1)
@@ -14,15 +17,15 @@
14
17
  , point_attribute as (
15
18
  select kihon_id
16
19
  , case when exists(
17
- select 1 from campaign where kihon_id=pnt.kihon_id and apply_date=pnt.p_date
20
+ select 1 from campaign where apply_date < param.apply_date_limit and kihon_id=pnt.kihon_id and apply_date=pnt.p_date
18
21
  )
19
22
  then 'キャンペーンあり' else 'キャンペーンなし' end as shisaku_flg
20
23
  , case gender when 1 then 'M' when 2 then 'F' end ||
21
24
  case when old between 20 and 34 then '1'
22
- when old between 35 and 49 then '2'
25
+ when old between 35 and 49 then '2'
23
- when old >= 50 then '3'
26
+ when old >= 50 then '3'
24
27
  end as demogra_flg
25
- from point_rireki2 pnt
28
+ from param cross join point_rireki2 pnt
26
29
  )
27
30
  , point_agg as (
28
31
  select shisaku_flg, demogra_flg, count(distinct kihon_id) as patarn_count
@@ -34,13 +37,13 @@
34
37
  from pattern ptn left join point_agg pntagg
35
38
  on ptn.shisaku_flg=pntagg.shisaku_flg
36
39
  and ptn.demogra_flg=pntagg.demogra_flg
37
- order by shisaku_flg,demogra_flg
40
+ order by shisaku_flg, demogra_flg
38
41
  ;
39
42
  ```
40
43
  ※テストデータではgenderに定義に無い'3'がありますけど、それは組合せ表により除外されています。
41
44
 
42
45
  追記
43
46
  --
44
- 「特定応募日(2/6)以前までの期間における」や「キャンペーン期間：1/26～2/9」を条件に含めると
47
+ 「キャンペーン期間：1/26～2/9」を条件に含めると
45
48
  求めたい結果と異なるので、含めていません。
46
49
  必要なら、point_attribute内で条件を直接追加すれば確認できます。

8

追記

2018/03/22 08:09

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -42,4 +42,5 @@
42
42
  追記
43
43
  --
44
44
  「特定応募日(2/6)以前までの期間における」や「キャンペーン期間：1/26～2/9」を条件に含めると
45
- 求めたい結果と異なるので、含めていません。
45
+ 求めたい結果と異なるので、含めていません。
46
+ 必要なら、point_attribute内で条件を直接追加すれば確認できます。

7

追記

2018/03/22 07:36

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -37,4 +37,9 @@
37
37
  order by shisaku_flg,demogra_flg
38
38
  ;
39
39
  ```
40
- ※テストデータではgenderに定義に無い'3'がありますけど、それは組合せ表により除外されています。
40
+ ※テストデータではgenderに定義に無い'3'がありますけど、それは組合せ表により除外されています。
41
+
42
+ 追記
43
+ --
44
+ 「特定応募日(2/6)以前までの期間における」や「キャンペーン期間：1/26～2/9」を条件に含めると
45
+ 求めたい結果と異なるので、含めていません。

6

推敲

2018/03/22 07:32

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -5,7 +5,7 @@
5
5
 
6
6
  ```SQL
7
7
  with
8
- patarn as (
8
+ pattern as (
9
9
  select shisaku_flg, demogra_flg1 || demogra_flg2 as demogra_flg
10
10
  from unnest(array['キャンペーンあり','キャンペーンなし']::text[]) as x(shisaku_flg)
11
11
  cross join unnest(array['M','F']::text[]) as y(demogra_flg1)
@@ -31,7 +31,7 @@
31
31
  order by shisaku_flg, demogra_flg
32
32
  )
33
33
  select ptn.*, coalesce(pntagg.patarn_count,0)
34
- from patarn ptn left join point_agg pntagg
34
+ from pattern ptn left join point_agg pntagg
35
35
  on ptn.shisaku_flg=pntagg.shisaku_flg
36
36
  and ptn.demogra_flg=pntagg.demogra_flg
37
37
  order by shisaku_flg,demogra_flg

5

修正

2018/03/22 05:33

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -11,7 +11,7 @@
11
11
  cross join unnest(array['M','F']::text[]) as y(demogra_flg1)
12
12
  cross join unnest(array['1','2','3']::text[]) as z(demogra_flg2)
13
13
  )
14
- , point_edit as (
14
+ , point_attribute as (
15
15
  select kihon_id
16
16
  , case when exists(
17
17
  select 1 from campaign where kihon_id=pnt.kihon_id and apply_date=pnt.p_date
@@ -26,7 +26,7 @@
26
26
  )
27
27
  , point_agg as (
28
28
  select shisaku_flg, demogra_flg, count(distinct kihon_id) as patarn_count
29
- from point_edit
29
+ from point_attribute
30
30
  group by shisaku_flg, demogra_flg
31
31
  order by shisaku_flg, demogra_flg
32
32
  )

4

修正

2018/03/22 05:31

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -5,35 +5,36 @@
5
5
 
6
6
  ```SQL
7
7
  with
8
- pattern as (
8
+ patarn as (
9
9
  select shisaku_flg, demogra_flg1 || demogra_flg2 as demogra_flg
10
10
  from unnest(array['キャンペーンあり','キャンペーンなし']::text[]) as x(shisaku_flg)
11
11
  cross join unnest(array['M','F']::text[]) as y(demogra_flg1)
12
12
  cross join unnest(array['1','2','3']::text[]) as z(demogra_flg2)
13
13
  )
14
+ , point_edit as (
15
+ select kihon_id
16
+ , case when exists(
17
+ select 1 from campaign where kihon_id=pnt.kihon_id and apply_date=pnt.p_date
18
+ )
19
+ then 'キャンペーンあり' else 'キャンペーンなし' end as shisaku_flg
20
+ , case gender when 1 then 'M' when 2 then 'F' end ||
21
+ case when old between 20 and 34 then '1'
22
+ when old between 35 and 49 then '2'
23
+ when old >= 50 then '3'
24
+ end as demogra_flg
25
+ from point_rireki2 pnt
26
+ )
14
27
  , point_agg as (
15
28
  select shisaku_flg, demogra_flg, count(distinct kihon_id) as patarn_count
16
- from (
17
- select *
18
- , case when exists(
19
- select 1 from campaign where kihon_id=pnt.kihon_id and apply_date=pnt.p_date
20
- )
21
- then 'キャンペーンあり' else 'キャンペーンなし' end as shisaku_flg
22
- , case gender when 1 then 'M' when 2 then 'F' end ||
23
- case when old between 20 and 34 then '1'
24
- when old between 35 and 49 then '2'
25
- when old >= 50 then '3'
26
- end as demogra_flg
27
- from point_rireki2 pnt
29
+ from point_edit
28
- ) step1
29
30
  group by shisaku_flg, demogra_flg
30
31
  order by shisaku_flg, demogra_flg
31
32
  )
32
33
  select ptn.*, coalesce(pntagg.patarn_count,0)
33
- from pattern ptn left join point_agg pntagg
34
+ from patarn ptn left join point_agg pntagg
34
35
  on ptn.shisaku_flg=pntagg.shisaku_flg
35
36
  and ptn.demogra_flg=pntagg.demogra_flg
36
- order by shisaku_flg,demogra_flg]
37
+ order by shisaku_flg,demogra_flg
37
38
  ;
38
39
  ```
39
40
  ※テストデータではgenderに定義に無い'3'がありますけど、それは組合せ表により除外されています。

3

推敲

2018/03/22 05:29

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -1,5 +1,5 @@
1
1
  先ず【検討中のフラグの仮案】ですが、case文中で完結しているので、相関問い合わせとなっていません。
2
- 次に、求める結果では0件も表示するのですから、ポイント履歴に求めても無いものは抽出されませんから、基準となるものが必要です。
2
+ 次に、求める結果では0件も表示するのですから、ポイント履歴に求めても無いものは抽出されませんので、基準となるものが必要です。
3
3
 
4
4
  以下は「pattern」として組合せ表を内部的に生成し、それとポイント履歴の集計表を結合して結果を求めるようにしています。
5
5
 

2

推敲

2018/03/22 05:20

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -36,4 +36,4 @@
36
36
  order by shisaku_flg,demogra_flg]
37
37
  ;
38
38
  ```
39
- ※テストデータではgenderに定義に無い'3'がありますけど、それは除外組合せ表により除外されています。
39
+ ※テストデータではgenderに定義に無い'3'がありますけど、それは組合せ表により除外されています。

1

追記

2018/03/22 05:20

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -1,6 +1,5 @@
1
1
  先ず【検討中のフラグの仮案】ですが、case文中で完結しているので、相関問い合わせとなっていません。
2
- 次に、求める結果では0件も表示するのですから、ポイント履歴に求めても無いものは抽出されませんから、
2
+ 次に、求める結果では0件も表示するのですから、ポイント履歴に求めても無いものは抽出されませんから、基準となるものが必要です。
3
- 基準となるものが必要です。
4
3
 
5
4
  以下は「pattern」として組合せ表を内部的に生成し、それとポイント履歴の集計表を結合して結果を求めるようにしています。
6
5
 
@@ -36,4 +35,5 @@
36
35
  and ptn.demogra_flg=pntagg.demogra_flg
37
36
  order by shisaku_flg,demogra_flg]
38
37
  ;
39
- ```
38
+ ```
39
+ ※テストデータではgenderに定義に無い'3'がありますけど、それは除外組合せ表により除外されています。