# 結合テスト第一回

### PR60210(MC対象機種の抽出)

#### 機種コードが新旧機種マッピングに含まれる

select
	count(*)
from (
	select
		product_code
	from 
		data.d602100_mc_actual_sales_cleansed
	group by
		product_code
) t1

 count 
-------
 60469
(1 row)

drop table IFI0070_MC_PRODUCT_GROUP_MAPPING;
create table IFI0070_MC_PRODUCT_GROUP_MAPPING(group_code CHAR(35) NOT NULL,product_code CHAR(35) NOT NULL,mc_start_date char(6),mc_end_date char(6),mc_response_end_date char(6),PRIMARY KEY(group_code, product_code));
\copy IFI0070_MC_PRODUCT_GROUP_MAPPING from '/storage/mdf/interface/ifin/IFI0070_MC_PRODUCT_GROUP_MAPPING.csv' with csv header;
select
	count(*)
from (
	select
		product_code
	from 
		data.d602100_mc_actual_sales_cleansed
	group by
		product_code
) t1
inner join (
	select
		product_code
	from 
		IFI0070_MC_PRODUCT_GROUP_MAPPING
	group by
		product_code
) t2
using (
	product_code
)

 count 
-------
 60469
(1 row)

#### 実績データが存在する

drop table ifi0012_area_sales;
create table ifi0012_area_sales as (select * from ifi0012_area_sales_2022_it limit 0);
\copy ifi0012_area_sales from '/storage/mdf/interface/ifin/IFI0012_AREA_SALES.csv' with csv header;
select
	count(*)
from (
	select
		product_code, area_code, kick_date as ymd, qty as sales_units
	from 
		ifi0012_area_sales
	group by
		product_code, area_code, kick_date, qty
) t1
inner join (
	select
		product_code
	from 
		IFI0070_MC_PRODUCT_GROUP_MAPPING
	group by
		product_code
) t2
using (
	product_code
)

 count 
-------
 96255
(1 row)

select
	count(*)
from (
	select
		product_code, area_code, kick_date as ymd, qty as sales_units
	from 
		ifi0012_area_sales
	group by
		product_code, area_code, kick_date, qty
) t1
inner join (
	select
		product_code
	from 
		IFI0070_MC_PRODUCT_GROUP_MAPPING
	group by
		product_code
) t2
using (
	product_code
)
inner join (
	select
		product_code, area_code, to_char(ymd, 'YYYYMMDD') as ymd, sales_units
	from 
		data.d602100_mc_actual_sales_cleansed
	group by
		product_code, area_code, ymd, sales_units
) t3
using (
	product_code, area_code, ymd, sales_units
)

 count 
-------
 96255
(1 row)

### PR60210(差分追加)

select
	count(*)
from (
	select
		product_code,
		area_code,
		min(ymd),
		max_date,
		(extract(year from age(max_date, min(ymd))) * 12 + extract(month from age(max_date, min(ymd)))) * 3 + extract(day from age(max_date, min(ymd))) / 10 + 1 as expected_ymd_counts,
		count(*) as actual_ymd_counts
	from (
		select
			product_code, area_code
		from 
			ifi0012_area_sales
		group by
			product_code, area_code
	) t1
	inner join (
		select
			product_code
		from 
			IFI0070_MC_PRODUCT_GROUP_MAPPING
		group by
			product_code
	) t2
	using (
		product_code
	)
	inner join (
		select
			product_code, area_code, ymd, TO_DATE('2022-09-21', 'YYYY-MM-DD') as max_date
		from 
			data.d602100_mc_actual_sales_cleansed
		group by
			product_code, area_code, ymd
	) t3
	using (
		product_code, area_code
	)
	group by
		product_code,
		area_code,
		max_date
) t
where
	expected_ymd_counts != actual_ymd_counts

 count 
-------
     0
(1 row)

### PR60260(グループ×エリア別集計：D602500, 品種/機種/機種×エリア別)

#### D602000に含まれる

select
	count(*)
from (
	select
		sku_cd
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t

 count  
--------
 567532
(1 row)

select
	count(*)
from (
	select
		sku_cd
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t1
inner join	(
	select
		sku_cd
	from
		data.d602000_actual_sales_cleansed
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t2
using (
	sku_cd
)

 count  
--------
 567532
(1 row)

#### D601100に品種が含まれる

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 2) as category_cd
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		category_cd
) t1

 count 
-------
   952
(1 row)

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 2) as category_cd
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		category_cd
) t1
inner join	(
	select
		category_cd
	from
		sysinf.d601100_category_list
	group by
		category_cd
) t2
using (
	category_cd
)

 count 
-------
   952
(1 row)

#### B区分またはC区分

select
	count(*) as all_count,
	sum(case when class = 'A' then 1 else 0 end) as a_count,
	sum(case when class = 'B' or class = 'C' then 1 else 0 end) as bc_count
from (
	select
		sku_cd
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t1
inner join	(
	select
		sku_cd,
		class
	from
		sysinf.d602600_abc_class_list
	group by
		sku_cd,
		class
) t2
using (
	sku_cd
)

 all_count | a_count | bc_count 
-----------+---------+----------
    567532 |       0 |   567532
(1 row)

#### 全SKUについて格納

select
	count(*)
from (
	select
		sku_cd
	from
		data.d602000_actual_sales_cleansed
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t1

 count  
--------
 569218
(1 row)

select
	count(*)
from (
	select
		sku_cd
	from
		data.d602000_actual_sales_cleansed
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t1
inner join	(
	select
		sku_cd
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
	union 
	select
		sku_cd
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t2
using (
	sku_cd
)

 count  
--------
 569218
(1 row)

### PR60260(グループ×エリア別集計：D602520, 品種/機種/機種×エリア別)

#### D602000に含まれる

select
	count(*)
from (
	select
		sku_cd
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t

 count  
--------
 1686
(1 row)

select
	count(*)
from (
	select
		sku_cd
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t1
inner join	(
	select
		sku_cd
	from
		data.d602000_actual_sales_cleansed
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t2
using (
	sku_cd
)

 count  
--------
 1686
(1 row)

#### D601100に品種が含まれる

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 2) as category_cd
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		category_cd
) t1

 count 
-------
   105
(1 row)

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 2) as category_cd
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		category_cd
) t1
inner join	(
	select
		category_cd
	from
		sysinf.d601100_category_list
	group by
		category_cd
) t2
using (
	category_cd
)

 count 
-------
   105
(1 row)

#### A区分

select
	count(*) as all_count,
	sum(case when class = 'A' then 1 else 0 end) as a_count,
	sum(case when class = 'B' or class = 'C' then 1 else 0 end) as bc_count
from (
	select
		sku_cd
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '1%'
	or
		sku_cd like '2%'
	or
		sku_cd like '3%'
	group by
		sku_cd
) t1
inner join	(
	select
		sku_cd,
		class
	from
		sysinf.d602600_abc_class_list
	group by
		sku_cd,
		class
) t2
using (
	sku_cd
)

 all_count | a_count | bc_count 
-----------+---------+----------
      1686 |    1686 |        0
(1 row)

#### 全SKUについて格納
品種/機種/機種×エリア別集計：D602500で確認済みのため割愛

### PR60260(グループ×エリア別集計：D602500, グループ×エリア別)

#### グループ×機種マッピングに含まれる

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '4%'
	group by
		group_code
) t1

 count 
-------
  3951
(1 row)

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '4%'
	group by
		group_code
) t1
inner join	(
	select
		group_code
	from
		data.d601200_mc_product_group_mapping
	group by
		group_code
) t2
using (
	group_code
)

 count 
-------
  3951
(1 row)

#### 1機種でも実績が存在する

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '4%'
	group by
		group_code
) as t1

 count 
-------
  3951
(1 row)

#### 直近6ヶ月実績合計が機種別B区分最大値を下回る

select
	division,
	max(total_sales) as max_total_sales
from (
	select 
		sku_cd 
	from 
		sysinf.d602600_abc_class_list 
	where 
		class = 'B' 
	and 
		sku_cd like '2%'
	group by
		sku_cd
) t1
inner join (
	select 
		category_cd, 
		sku_cd, 
		sum(sales_units) as total_sales
	from 
		data.d602000_actual_sales_cleansed 
	where 
		sku_cd like '2%' 
	and 
		ymd < '2022-04-01' 
	and 
		ymd >= '2021-10-01'
	group by
		category_cd,
		sku_cd
) t2
using (
	sku_cd
)
inner join (
	select 
		category_cd, 
		division 
	from 
		sysinf.d601100_category_list
) t3
using (
	category_cd
)
group by
	division
order by
	division

   division   | max_total_sales 
--------------+-----------------
 インテリア   |           44100
 エクステリア |             858
 サッシドア   |           26545
(3 rows)

with group_total_sales as (
	select
		sku_cd,
		split_part(sku_cd, '__', 3) as group_code,
		split_part(sku_cd, '__', 4) as area_code,
		sum(sales_units) as total_sales
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '4%'
	and
		'2021-10-01' <= ymd
	and
		ymd < '2022-04-01'
	group by
		sku_cd
)

select
	division,
	sku_cd,
	total_sales as max_total_sales
from (
	select
		division,
		max(total_sales) as total_sales
	from group_total_sales t1
	inner join	(
		select
			group_code,
			product_code
		from
			data.d601200_mc_product_group_mapping
		group by
			group_code,
			product_code
	) t2
	using (
		group_code
	)
	inner join	(
		select
			split_part(sku_cd, '__', 2) as category_cd,
			split_part(sku_cd, '__', 3) as product_code
		from
			sysinf.d602600_abc_class_list
		where
			sku_cd like '1%'
		or
			sku_cd like '2%'
		or
			sku_cd like '3%'
		group by
			split_part(sku_cd, '__', 2),
			split_part(sku_cd, '__', 3)
	) t3
	using (
		product_code
	)
	inner join	(
		select 
			category_cd, 
			division 
		from 
			sysinf.d601100_category_list
	) t4
	using (
		category_cd
	)
	group by
		division
) t5
inner join 
	group_total_sales t6
using (
	total_sales
)
order by
	division

   division   |                     sku_cd                     | max_total_sales 
--------------+------------------------------------------------+-----------------
 インテリア   | 4__0__G_DD-YH065HJL-MDDH__C                    |           41649
 エクステリア | 4__0__G_H-C511-PVAA__C                         |             839
 サッシドア   | 4__0__G_ﾏ8ﾐ_ﾄｸﾁｭｳ__D                           |            1275
(3 rows)

#### 全グループ×エリアにつき実績が格納される

select
	count(*)
from (
	select
		group_code
	from
		data.d602100_mc_actual_sales_cleansed
	group by
		group_code
) t1

 count 
-------
  3952
(1 row)

select
	count(*)
from (
	select
		group_code
	from
		data.d602100_mc_actual_sales_cleansed
	group by
		group_code
) t1
inner join	(
	select
		split_part(sku_cd, '__', 3) as group_code
	from
		data.d602500_actual_sales_monthly
	where
		sku_cd like '4%'
	group by
		sku_cd
	union 
	select
		split_part(sku_cd, '__', 3) as group_code
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '4%'
	group by
		sku_cd
) t2
using (
	group_code
)

 count 
-------
  3952
(1 row)

### PR60260(グループ×エリア別集計：D602520, グループ×エリア別)

#### グループ×機種マッピングに含まれる

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '4%'
	group by
		group_code
) t1

 count 
-------
  13
(1 row)

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '4%'
	group by
		group_code
) t1
inner join	(
	select
		group_code
	from
		data.d601200_mc_product_group_mapping
	group by
		group_code
) t2
using (
	group_code
)

 count 
-------
  13
(1 row)

#### 1機種でも実績が存在する

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '4%'
	group by
		group_code
) as t1

 count 
-------
  13
(1 row)

#### 直近6ヶ月実績合計が機種別B区分最大値を上回る

select
	division,
	max(total_sales) as max_total_sales
from (
	select 
		sku_cd 
	from 
		sysinf.d602600_abc_class_list 
	where 
		class = 'B' 
	and 
		sku_cd like '2%'
	group by
		sku_cd
) t1
inner join (
	select 
		category_cd, 
		sku_cd, 
		sum(sales_units) as total_sales
	from 
		data.d602000_actual_sales_cleansed 
	where 
		sku_cd like '2%' 
	and 
		ymd < '2022-04-01' 
	and 
		ymd >= '2021-10-01'
	group by
		category_cd,
		sku_cd
) t2
using (
	sku_cd
)
inner join (
	select 
		category_cd, 
		division 
	from 
		sysinf.d601100_category_list
) t3
using (
	category_cd
)
group by
	division
order by
	division

   division   | max_total_sales 
--------------+-----------------
 インテリア   |           44100
 エクステリア |             858
 サッシドア   |           26545
(3 rows)

with group_total_sales as (
	select
		sku_cd,
		split_part(sku_cd, '__', 3) as group_code,
		split_part(sku_cd, '__', 4) as area_code,
		sum(sales_units) as total_sales
	from
		data.d602520_actual_sales_tendays
	where
		sku_cd like '4%'
	and
		'2021-10-01' <= true_ymd
	and
		true_ymd < '2022-04-01'
	group by
		sku_cd
)

select
	division,
	sku_cd,
	total_sales as min_total_sales
from (
	select
		division,
		min(total_sales) as total_sales
	from group_total_sales t1
	inner join	(
		select
			group_code,
			product_code
		from
			data.d601200_mc_product_group_mapping
		group by
			group_code,
			product_code
	) t2
	using (
		group_code
	)
	inner join	(
		select
			split_part(sku_cd, '__', 2) as category_cd,
			split_part(sku_cd, '__', 3) as product_code
		from
			sysinf.d602600_abc_class_list
		where
			sku_cd like '1%'
		or
			sku_cd like '2%'
		or
			sku_cd like '3%'
		group by
			split_part(sku_cd, '__', 2),
			split_part(sku_cd, '__', 3)
	) t3
	using (
		product_code
	)
	inner join	(
		select 
			category_cd, 
			division 
		from 
			sysinf.d601100_category_list
	) t4
	using (
		category_cd
	)
	group by
		division
) t5
inner join 
	group_total_sales t6
using (
	total_sales
)
order by
	division

   division   |                     sku_cd                     | min_total_sales 
--------------+------------------------------------------------+-----------------
 インテリア   | 4__0__G_EG-YA055Z-MWG5__C                      |           44630
 エクステリア | 4__0__G_H-C731-PVAA__D                         |             905
(2 rows)

#### 全グループ×エリアにつき実績が格納される
グループ×エリア別集計：D602500で確認済みのため割愛

### PR60260(グループ×エリア別集計：D602500, D602520)

#### (1)実績データの歯抜けがない

select
	count(*)
from (
	select
		sku_cd,
		min(ymd),
		max_date,
		(extract(year from age(max_date, min(ymd))) * 12 + extract(month from age(max_date, min(ymd)))) + 1 as expected_ymd_counts,
		count(*) as actual_ymd_counts
	from (
		select
			sku_cd, ymd, TO_DATE('2022-09-21', 'YYYY-MM-DD') as max_date
		from 
			data.d602500_actual_sales_monthly
		where
			sku_cd like '4%'
		group by
			sku_cd, ymd
	) t
	group by
		sku_cd,
		max_date
) t
where
	expected_ymd_counts != actual_ymd_counts

 count 
-------
     0
(1 row)

select
	count(*)
from (
	select
		sku_cd,
		min(ymd),
		max_date,
		(extract(year from age(max_date, min(ymd))) * 12 + extract(month from age(max_date, min(ymd)))) * 3 + extract(day from age(max_date, min(ymd))) / 10 + 1 as expected_ymd_counts,
		count(*) as actual_ymd_counts
	from (
		select
			sku_cd, true_ymd as ymd, TO_DATE('2022-09-21', 'YYYY-MM-DD') as max_date
		from 
			data.d602520_actual_sales_tendays
		where
			sku_cd like '4%'
		group by
			sku_cd, true_ymd
	) t
	group by
		sku_cd,
		max_date
) t
where
	expected_ymd_counts != actual_ymd_counts

 count 
-------
     0
(1 row)

#### (2)D602500/D602520にSKUコードの重複がない

select
	count(*)
from (
	select
		sku_cd
	from 
		data.d602500_actual_sales_monthly
	where
		sku_cd like '4%'
	group by
		sku_cd
) t1
inner join (
	select
		sku_cd
	from 
		data.d602520_actual_sales_tendays
	where
		sku_cd like '4%'
	group by
		sku_cd
) t2
using (
	sku_cd
)

 count 
-------
     0
(1 row)

#### (3)グループ×エリア別集計の正しさを確認

select
	*
from (
	select
		split_part(sku_cd, '__', 3) as product_code,
		split_part(sku_cd, '__', 4) as area_code,
		ymd,
		sales_units
	from 
		data.d602100_mc_actual_sales_cleansed
	where
		sku_cd like '3%'
	and
		'2022-03-01' <= ymd
	and
		ymd <= '2022-03-21'
	and
		sales_units > 0
) t1
inner join (
	select
		product_code
	from
		data.d601200_mc_product_group_mapping
	where
		group_code = 'G_BG-KA-MAFV'
	group by
		product_code
) t2
using (
	product_code
)
where
	area_code = 'A'
order by
	product_code,
	area_code,
	ymd

 product_code | area_code |    ymd     | sales_units 
--------------+-----------+------------+-------------
 BD-AJ-MAFV   | A         | 2022-03-01 |           2
 BD-AJ-MAFV   | A         | 2022-03-11 |          10
 BD-AJ-MAFV   | A         | 2022-03-21 |          10
 BD-CJ-MAFV   | A         | 2022-03-01 |           8
 BD-CJ-MAFV   | A         | 2022-03-21 |           1
 BD-CL-MAFV   | A         | 2022-03-01 |         519
 BD-CL-MAFV   | A         | 2022-03-11 |         297
 BD-CL-MAFV   | A         | 2022-03-21 |         475
 BD-DB-MAFV   | A         | 2022-03-01 |         200
 BD-DB-MAFV   | A         | 2022-03-11 |         113
 BD-DB-MAFV   | A         | 2022-03-21 |         146
 BD-EA-MAFV   | A         | 2022-03-01 |           6
 BD-EA-MAFV   | A         | 2022-03-21 |           3
 BE-AG-MAFV   | A         | 2022-03-01 |         153
 BE-AG-MAFV   | A         | 2022-03-11 |          57
 BE-AG-MAFV   | A         | 2022-03-21 |         129
 BE-AJ-MAFV   | A         | 2022-03-01 |          67
 BE-AJ-MAFV   | A         | 2022-03-11 |          50
 BE-AJ-MAFV   | A         | 2022-03-21 |          68
 BE-CJ-MAFV   | A         | 2022-03-01 |           3
 BE-CJ-MAFV   | A         | 2022-03-21 |           7
 BE-CL-MAFV   | A         | 2022-03-01 |         153
 BE-CL-MAFV   | A         | 2022-03-11 |         100
 BE-CL-MAFV   | A         | 2022-03-21 |         144
 BE-EA-MAFV   | A         | 2022-03-11 |           2
 BE-EA-MAFV   | A         | 2022-03-21 |           3
 BF-AJ-MAFV   | A         | 2022-03-11 |           4
 BF-AJ-MAFV   | A         | 2022-03-21 |           7
 BF-CL-MAFV   | A         | 2022-03-01 |          34
 BF-CL-MAFV   | A         | 2022-03-11 |          40
 BF-CL-MAFV   | A         | 2022-03-21 |          51
 BF-EA-MAFV   | A         | 2022-03-01 |           4
 BF-GA-MAFV   | A         | 2022-03-01 |           4
 BF-GA-MAFV   | A         | 2022-03-11 |           1
 BF-GA-MAFV   | A         | 2022-03-21 |           3
 BG-CL-MAFV   | A         | 2022-03-01 |           5
 BG-CL-MAFV   | A         | 2022-03-21 |          11
 BL-AJ-MAFV   | A         | 2022-03-01 |          18
 BL-AJ-MAFV   | A         | 2022-03-11 |          14
 BL-AJ-MAFV   | A         | 2022-03-21 |          16
 BL-CL-MAFV   | A         | 2022-03-01 |           5
 BL-CL-MAFV   | A         | 2022-03-11 |           1
 BL-CL-MAFV   | A         | 2022-03-21 |           5
 BL-GA-MAFV   | A         | 2022-03-01 |           6
 BL-GA-MAFV   | A         | 2022-03-11 |           1
 BL-GA-MAFV   | A         | 2022-03-21 |           6
 YY-CJ-MAFV   | A         | 2022-03-01 |           3
 YY-CJ-MAFV   | A         | 2022-03-11 |           1
 YY-CL-MAFV   | A         | 2022-03-01 |           2
 YY-CL-MAFV   | A         | 2022-03-11 |           1
 YY-CL-MAFV   | A         | 2022-03-21 |          12
 YY-GA-MAFV   | A         | 2022-03-21 |           1
(52 rows)

select
	sku_cd,
	ymd,
	sales_units
from 
	data.d602500_actual_sales_monthly
where
	sku_cd like '4__0__G_BG-KA-MAFV__A%'
and
	ymd = '2022-03-01'

                     sku_cd                     |    ymd     | sales_units 
------------------------------------------------+------------+-------------
 4__0__G_BG-KA-MAFV__A                          | 2022-03-01 |        2982
(1 row)

select
	*
from (
	select
		split_part(sku_cd, '__', 3) as product_code,
		split_part(sku_cd, '__', 4) as area_code,
		ymd,
		sales_units
	from 
		data.d602100_mc_actual_sales_cleansed
	where
		sku_cd like '3%'
	and
		'2022-03-01' = ymd
	and
		sales_units > 0
) t1
inner join (
	select
		product_code
	from
		data.d601200_mc_product_group_mapping
	where
		group_code = 'G_8LHB02QP'
	group by
		product_code
) t2
using (
	product_code
)
where
	area_code = 'A'
order by
	product_code,
	area_code,
	ymd

 product_code | area_code |    ymd     | sales_units 
--------------+-----------+------------+-------------
 8LDA02RY     | A         | 2022-03-01 |           8
 8LDA08RY     | A         | 2022-03-01 |          12
 8LDA11RY     | A         | 2022-03-01 |          12
 HB-D163-PERD | A         | 2022-03-01 |           5
(4 rows)

select
	sku_cd,
	true_ymd,
	sales_units
from 
	data.d602520_actual_sales_tendays
where
	sku_cd like '4__0__G_8LHB02QP__A%'
and
	true_ymd = '2022-03-01'

                     sku_cd                     |  true_ymd  | sales_units 
------------------------------------------------+------------+-------------
 4__0__G_8LHB02QP__A                            | 2022-03-01 |          37
(1 row)

### PR60260(過去実績最大値の取得:D603020)

select
	count(*)
from (
	select
		sku_cd
	from
		data.d602520_actual_sales_tendays
	group by
		sku_cd
	union
	select
		sku_cd
	from
		data.d602500_actual_sales_monthly
	group by
		sku_cd
) as t

 count  
--------
 584783
(1 row)

select
	count(sku_cd)
from
	data.d603020_maximum_sales

 count  
--------
 584783
(1 row)

select
	sum(case when sales_units > max_sales_units then 1 else 0 end) as incorrect_count
from (
	select
		sku_cd,
		DATE_TRUNC('month', true_ymd) as ymd,
		sum(sales_units) as sales_units
	from
		data.d602520_actual_sales_tendays
	group by
		sku_cd,
		DATE_TRUNC('month', true_ymd)
	union
	select
		sku_cd,
		ymd,
		sales_units
	from
		data.d602500_actual_sales_monthly
) t1
inner join (
	select
		sku_cd,
		max_sales_units
	from
		data.d603020_maximum_sales
) t2
using (
	sku_cd
)

 incorrect_count 
-----------------
               0
(1 row)

### PR60300
#### D602500 / D602520いずれかに実績データの存在する全SKUのレコードが存在

select
	count(*)
from (
	select
		sku_cd
	from
		data.d602520_actual_sales_tendays
	group by
		sku_cd
	union
	select
		sku_cd
	from
		data.d602500_actual_sales_monthly
	group by
		sku_cd
) t1

 count  
--------
 584783
(1 row)

select
	count(*)
from (
	select
		sku_cd
	from
		data.d602520_actual_sales_tendays
	group by
		sku_cd
	union
	select
		sku_cd
	from
		data.d602500_actual_sales_monthly
	group by
		sku_cd
) t1
inner join (
	select
		sku_cd
	from
		sysinf.d603010_forecasting_methods
	group by
		sku_cd
) t2
using (
	sku_cd
)

 count  
--------
 584783
(1 row)

#### 期間6ヶ月以下or充填率10%以下のSKUが予測対象外と判定される。

select
	count(*)
from (
	select
		sku_cd
	from (
		select
			sku_cd,
			count(*) as ymd_count,
			sum(case when sales_units > 0 then 1 else 0 end) as positive_sales_records,
			count(*) as sales_records
		from
			DATA.D602500_ACTUAL_SALES_MONTHLY
		where
			ymd < '2022-04-01'
		group by
			sku_cd
	) as t
	where
		ymd_count <= 6
	or
		1.0 * positive_sales_records / sales_records <= 0.1
	union
	select
		sku_cd
	from (
		select
			sku_cd,
			count(*) as ymd_count,
			sum(case when sales_units > 0 then 1 else 0 end) as positive_sales_records,
			count(*) as sales_records
		from
			DATA.D602520_ACTUAL_SALES_TENDAYS
		where
			ymd < '2022-04-01'
		group by
			sku_cd
	) as t
	where
		ymd_count <= 6 * 3
	or
		1.0 * positive_sales_records / sales_records <= 0.1
) as t

 count  
--------
 311913
(1 row)

select
	count(*)
from (
	select
		sku_cd
	from (
		select
			sku_cd,
			count(*) as ymd_count,
			sum(case when sales_units > 0 then 1 else 0 end) as positive_sales_records,
			count(*) as sales_records
		from
			DATA.D602500_ACTUAL_SALES_MONTHLY
		where
			ymd < '2022-04-01'
		group by
			sku_cd
	) as t
	where
		ymd_count <= 6
	or
		1.0 * positive_sales_records / sales_records <= 0.1
	union
	select
		sku_cd
	from (
		select
			sku_cd,
			count(*) as ymd_count,
			sum(case when sales_units > 0 then 1 else 0 end) as positive_sales_records,
			count(*) as sales_records
		from
			DATA.D602520_ACTUAL_SALES_TENDAYS
		where
			ymd < '2022-04-01'
		group by
			sku_cd
	) as t
	where
		ymd_count <= 6 * 3
	or
		1.0 * positive_sales_records / sales_records <= 0.1
) t1
inner join (
	select
		sku_cd
	from
		sysinf.d603010_forecasting_methods
	where
		not forecast_target
	group by
		sku_cd
) t2
using (
	sku_cd
)

 count  
--------
 311913
(1 row)

### PR60600(予測算出, D606000)

#### D603010において予測対象外でない全SKUの予測値が6ヶ月分存在

select
	count(*)
from (
	select
		sku_cd
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	group by
		sku_cd
) t1

 count  
--------
 272870
(1 row)

select
	count(*)
from (
	select
		sku_cd
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	group by
		sku_cd
) t1
inner join (
	select
		sku_cd
	from
		forecast.d606000_sales_forecast
	group by
		sku_cd
) t2
using (
	sku_cd
)

 count  
--------
 272870
(1 row)

select
	count(*)
from (
	select
		sku_cd,
		min(ymd) as min_ymd,
		max(ymd) as max_ymd,
		count(*) as ymd_count
	from
		forecast.d606000_sales_forecast
	group by
		sku_cd
) as t
where
	min_ymd != '2022-04-01'
or
	max_ymd != '2022-09-01'
or
	ymd_count != 6

 count 
-------
     0
(1 row)

### PR60600(ピックベスト, D606020)
#### D603010において予測対象外でない全SKUのレコードが存在

select
	count(*)
from (
	select
		sku_cd
	from
		model.D606020_method_selection
	group by
		sku_cd
) t1

 count  
--------
 272870
(1 row)

select
	count(*)
from (
	select
		sku_cd
	from
		model.D606020_method_selection
	group by
		sku_cd
) t1
inner join (
	select
		sku_cd
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	group by
		sku_cd
) t2
using (
	sku_cd
)

 count  
--------
 272870
(1 row)

#### 予測成否(judgement)がすべて1

select
	count(*) as count,
	sum(case when judgement then 1 else 0 end) as success_count
from
	model.D606020_method_selection

 count  | success_count 
--------+---------------
 272870 |        272870
(1 row)

#### 最終選択手法(final_method)にen, ts, es, lt, nvすべての手法が現れる。

select
	count(*) as count,
	sum(case when final_method = 'en' then 1 else 0 end) as en_count,
	sum(case when final_method = 'ts' then 1 else 0 end) as ts_count,
	sum(case when final_method = 'es' then 1 else 0 end) as es_count,
	sum(case when final_method = 'lt' then 1 else 0 end) as lt_count,
	sum(case when final_method = 'nv' then 1 else 0 end) as nv_count
from
	model.D606020_method_selection

 count  | en_count | ts_count | es_count | lt_count | nv_count 
--------+----------+----------+----------+----------+----------
 272870 |   133148 |     8499 |    13420 |   113463 |     4340
(1 row)

### PR60810(構成比率按分, D608210)
#### 新旧予測配分表(IFI0071)に存在する機種・エリアである

drop table IFI0071_MC_PRODUCT_FORECAST_ALLOCATION;
create table IFI0071_MC_PRODUCT_FORECAST_ALLOCATION(group_code CHAR(35) NOT NULL,product_code CHAR(35) NOT NULL,area_code char(1),ym char(6),component_ratio double precision,type char(1), PRIMARY KEY(group_code, product_code, area_code, ym));
\copy IFI0071_MC_PRODUCT_FORECAST_ALLOCATION from '/storage/mdf/interface/ifin/IFI0071_MC_PRODUCT_FORECAST_ALLOCATION.csv' with csv header;

select
	count(*)
from (
	select
		product_code,
		area_code
	from
		forecast.d608210_mc_area_forecast_adjusted
	group by
		product_code,
		area_code
) t1

 count  
--------
 343821
(1 row)

select
	count(*)
from (
	select
		product_code,
		area_code
	from
		forecast.d608210_mc_area_forecast_adjusted
	group by
		product_code,
		area_code
) t1
inner join (
	select
		product_code,
		area_code
	from
		ifi0071_mc_product_forecast_allocation
	group by
		product_code,
		area_code
) t2
using (
	product_code,
	area_code
)

 count  
--------
 343821
(1 row)

select
	count(*)
from (
	select
		product_code,
		area_code,
		count(*) as ymd_count
	from
		forecast.d608210_mc_area_forecast_adjusted
	group by
		product_code,
		area_code
) t
where
	ymd_count < 5

 count 
-------
     0
(1 row)

#### グループ別予測対象外でない全機種・エリアの予測値が存在する

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code,
		split_part(sku_cd, '__', 4) as area_code
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		sku_cd like '4%'
	group by
		group_code,
		area_code
) t1
inner join (
	select
		group_code,
		product_code,
		area_code
	from 
		ifi0071_mc_product_forecast_allocation
	group by
		group_code,
		product_code,
		area_code
) t2
using (
	group_code,
	area_code
)

 count  
--------
 343821
(1 row)

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code,
		split_part(sku_cd, '__', 4) as area_code
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		sku_cd like '4%'
	group by
		group_code,
		area_code
) t1
inner join (
	select
		group_code,
		product_code,
		area_code
	from 
		ifi0071_mc_product_forecast_allocation
	group by
		group_code,
		product_code,
		area_code
) t2
using (
	group_code,
	area_code
)
inner join (
	select
		product_code,
		area_code
	from
		forecast.d608210_mc_area_forecast_adjusted
	group by
		product_code,
		area_code
) t3
using (
	product_code,
	area_code
)

 count  
--------
 343821
(1 row)

### PR60811(構成比率按分, D608211)
#### 新旧予測配分表(IFI0071)に存在する機種・エリアである

select
	count(*)
from (
	select
		product_code,
		area_code
	from
		forecast.D608211_MC_AREA_FORECAST_ADJUSTED_ON_DATE
	group by
		product_code,
		area_code
) t1

 count  
--------
 343821
(1 row)

select
	count(*)
from (
	select
		product_code,
		area_code
	from
		forecast.D608211_MC_AREA_FORECAST_ADJUSTED_ON_DATE
	group by
		product_code,
		area_code
) t1
inner join (
	select
		product_code,
		area_code
	from
		ifi0071_mc_product_forecast_allocation
	group by
		product_code,
		area_code
) t2
using (
	product_code,
	area_code
)

 count  
--------
 343821
(1 row)

select
	count(*)
from (
	select
		product_code,
		area_code,
		count(*) as ymd_count
	from
		forecast.D608211_MC_AREA_FORECAST_ADJUSTED_ON_DATE
	group by
		product_code,
		area_code
) t
where
	ymd_count < 6

 count 
-------
     0
(1 row)

#### グループ別予測対象外でない全機種・エリアの予測値が存在する

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code,
		split_part(sku_cd, '__', 4) as area_code
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		sku_cd like '4%'
	group by
		group_code,
		area_code
) t1
inner join (
	select
		group_code,
		product_code,
		area_code
	from 
		ifi0071_mc_product_forecast_allocation
	group by
		group_code,
		product_code,
		area_code
) t2
using (
	group_code,
	area_code
)

 count  
--------
 343821
(1 row)

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code,
		split_part(sku_cd, '__', 4) as area_code
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		sku_cd like '4%'
	group by
		group_code,
		area_code
) t1
inner join (
	select
		group_code,
		product_code,
		area_code
	from 
		ifi0071_mc_product_forecast_allocation
	group by
		group_code,
		product_code,
		area_code
) t2
using (
	group_code,
	area_code
)
inner join (
	select
		product_code,
		area_code
	from
		forecast.D608211_MC_AREA_FORECAST_ADJUSTED_ON_DATE
	group by
		product_code,
		area_code
) t3
using (
	product_code,
	area_code
)

 count  
--------
 343821
(1 row)

### PR60830(機種エリア積み上げ, D608220)
#### 新旧予測配分表(IFI0071)に存在する機種である

select
	count(*)
from (
	select
		product_code
	from
		forecast.D608220_MC_PRODUCT_FORECAST_ADJUSTED
	group by
		product_code
) t1

 count  
--------
 62863
(1 row)

select
	count(*)
from (
	select
		product_code
	from
		forecast.D608220_MC_PRODUCT_FORECAST_ADJUSTED
	group by
		product_code
) t1
inner join (
	select
		product_code
	from
		ifi0071_mc_product_forecast_allocation
	group by
		product_code
) t2
using (
	product_code
)

 count  
--------
 62863
(1 row)

select
	count(*)
from (
	select
		product_code,
		count(*) as ymd_count
	from
		forecast.D608220_MC_PRODUCT_FORECAST_ADJUSTED
	group by
		product_code
) t
where
	ymd_count != 6

 count 
-------
     0
(1 row)

#### 「全エリアがグループ別予測対象外」でない全機種の予測値が存在する

with valid_product_codes as (
	/* product_code × area_codeとgroup_codeは1対多にならない(複数グループに振り分けられる機種はない)前提 */
	select
		product_code
	from (
		select
			product_code,
			sum(case when is_joined is null then 0 else 1 end) as join_count
		from (
			select
				group_code,
				product_code,
				area_code
			from 
				ifi0071_mc_product_forecast_allocation
			group by
				group_code,
				product_code,
				area_code
		) t1
		left outer join (
			select
				split_part(sku_cd, '__', 3) as group_code,
				split_part(sku_cd, '__', 4) as area_code,
				1 as is_joined
			from
				sysinf.d603010_forecasting_methods
			where
				forecast_target
			and
				sku_cd like '4%'
			group by
				group_code,
				area_code
		) t2
		using (
			group_code,
			area_code
		)
		group by
			product_code
	) t
	where
		join_count > 0
)

select
	count(*)
from 
	valid_product_codes

 count 
-------
 62863
(1 row)

with valid_product_codes as (
	/* product_code × area_codeとgroup_codeは1対多にならない(複数グループに振り分けられる機種はない)前提 */
	select
		product_code
	from (
		select
			product_code,
			sum(case when is_joined is null then 0 else 1 end) as join_count
		from (
			select
				group_code,
				product_code,
				area_code
			from 
				ifi0071_mc_product_forecast_allocation
			group by
				group_code,
				product_code,
				area_code
		) t1
		left outer join (
			select
				split_part(sku_cd, '__', 3) as group_code,
				split_part(sku_cd, '__', 4) as area_code,
				1 as is_joined
			from
				sysinf.d603010_forecasting_methods
			where
				forecast_target
			and
				sku_cd like '4%'
			group by
				group_code,
				area_code
		) t2
		using (
			group_code,
			area_code
		)
		group by
			product_code
	) t
	where
		join_count > 0
)

select
	count(*)
from 
	valid_product_codes
inner join (
	select
		product_code
	from
		forecast.D608220_MC_PRODUCT_FORECAST_ADJUSTED
	group by
		product_code
) t3
using (
	product_code
)

 count 
-------
 62863
(1 row)

#### 集計結果の正しさを確認

select
	*
from
	forecast.D608211_MC_AREA_FORECAST_ADJUSTED_ON_DATE
where
	product_code = '4ﾄｱ_ﾄｸﾁｭｳ'
and
	ymd = '2022-04-01'

 store_cd |            product_code             | area_code |    ymd     |   sales_forecast   
----------+-------------------------------------+-----------+------------+--------------------
 0        | 4ﾄｱ_ﾄｸﾁｭｳ                           | A         | 2022-04-01 |      317.884313754
 0        | 4ﾄｱ_ﾄｸﾁｭｳ                           | B         | 2022-04-01 |      184.958823546
 0        | 4ﾄｱ_ﾄｸﾁｭｳ                           | C         | 2022-04-01 | 1810.5267975479999
 0        | 4ﾄｱ_ﾄｸﾁｭｳ                           | D         | 2022-04-01 |     1315.554248484
 0        | 4ﾄｱ_ﾄｸﾁｭｳ                           | E         | 2022-04-01 | 367.43986931399996
 0        | 4ﾄｱ_ﾄｸﾁｭｳ                           | F         | 2022-04-01 |      443.667973896
(6 rows)

select
	*
from
	forecast.D608220_MC_PRODUCT_FORECAST_ADJUSTED
where
	product_code = '4ﾄｱ_ﾄｸﾁｭｳ'
and
	ymd = '2022-04-01'

 store_cd |            product_code             |    ymd     |   sales_forecast   
----------+-------------------------------------+------------+--------------------
 0        | 4ﾄｱ_ﾄｸﾁｭｳ                           | 2022-04-01 | 4440.0320265420005
(1 row)

### PR60831(品種機種積み上げ, D608221)
#### 品種機種対応表(IFI0072)に存在するすべての品種の予測値が存在する

select
	count(*)
from (
	select
		product_category_3 as category_cd
	from
		ifi0072_mc_product_category_mapping
	group by
		product_category_3
) t1

 count 
-------
    54
(1 row)

select
	count(*)
from (
	select
		product_category_3 as category_cd
	from
		ifi0072_mc_product_category_mapping
	group by
		product_category_3
) t1
inner join (
	select
		category_cd
	from
		forecast.D608221_MC_CATEGORY_FORECAST_ADJUSTED
	group by
		category_cd
) t2
using (
	category_cd
)

 count 
-------
    54
(1 row)

select
	count(*)
from (
	select
		category_cd,
		count(*) as ymd_count
	from
		forecast.D608221_MC_CATEGORY_FORECAST_ADJUSTED
	group by
		category_cd
) t
where
	ymd_count != 6

 count 
-------
     0
(1 row)

#### 集計結果の正しさを確認

##### MC対象の機種として通常予測値が存在するケース

select
	*
from
	forecast.D608111_PRODUCT_FORECAST_ADJUSTED
where
	category_cd = '4ﾄﾆ'
and
	ymd = '2022-04-01'
and
	sales_forecast > 0

 store_cd |                     sku_cd                     | category_cd |            product_code             |    ymd     | sales_forecast 
----------+------------------------------------------------+-------------+-------------------------------------+------------+----------------
 0        | 2__4ﾄﾆ__4ﾄﾆ_ﾄｸﾁｭｳ__0                           | 4ﾄﾆ         | 4ﾄﾆ_ﾄｸﾁｭｳ                           | 2022-04-01 |           1070
 0        | 2__4ﾄﾆ__PP-AD08K-MEL9__0                       | 4ﾄﾆ         | PP-AD08K-MEL9                       | 2022-04-01 |           2916

select
	*
from (
	select
		*
	from
		forecast.D608220_MC_PRODUCT_FORECAST_ADJUSTED
) t1
inner join (
    select
        product_category_3 as category_cd,
        product_code
    from
        ifi0072_mc_product_category_mapping
) t2
using (
	product_code
)
where
	category_cd = '4ﾄﾆ'
and
	ymd = '2022-04-01'
and (
	sales_forecast > 0
or
	product_code in ('PP-AD08K-MEL9', '4ﾄﾆ_ﾄｸﾁｭｳ')
)

            product_code             | store_cd |    ymd     |   sales_forecast   | category_cd 
-------------------------------------+----------+------------+--------------------+-------------
 4ﾄﾆ_ﾄｸﾁｭｳ                           | 0        | 2022-04-01 |      1024.16901504 | 4ﾄﾆ
 DB-AA08H-MEE9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DB-AA08H-MMT9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 DB-AA08K-MEM9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DC-AA08H-MEE9                       | 0        | 2022-04-01 | 4.9765270080000015 | 4ﾄﾆ
 DC-AA08K-MEM9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DC-AA08K-MEP9                       | 0        | 2022-04-01 | 4.9765270080000015 | 4ﾄﾆ
 DD-AA08H-MDY9                       | 0        | 2022-04-01 | 12.938967168000001 | 4ﾄﾆ
 DD-AA08H-MEN9                       | 0        | 2022-04-01 | 5.9718313919999995 | 4ﾄﾆ
 DD-AA08H-MMS9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 DD-AA08K-MEL9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DD-AA08K-MEN9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DD-AB08H-MDY9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DD-AB08H-MEN9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DD-AD08H-MDY9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DD-AD08K-MEL9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DD-AE08H-MDY9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DD-AP08H-MEN9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DD-AP08H-MMV9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 DD-GD08K-MENF                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DD-GF08KL-MDYD                      | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DE-AA08H-MMT9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 DD-GF08KR-MDYD                      | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DE-AA08K-MEE9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DK-AA08H-MEP9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DG-AA08K-MEE9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DG-AA08K-MEM9                       | 0        | 2022-04-01 | 4.9765270080000015 | 4ﾄﾆ
 DG-AA08K-MEP9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DG-AA08K-MMT9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 DG-AA08K-MMW9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DJ-AA08H-MEE9                       | 0        | 2022-04-01 |       24.882629952 | 4ﾄﾆ
 DJ-AA08H-MEP9                       | 0        | 2022-04-01 | 5.9718313919999995 | 4ﾄﾆ
 DE-AA08H-MEE9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DF-AA08K-MEE9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DG-AA08H-MEE9                       | 0        | 2022-04-01 |        9.953051472 | 4ﾄﾆ
 DG-AA08H-MEP9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DH-AA08H-MEE9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DJ-AA08K-MEM9                       | 0        | 2022-04-01 | 12.938967168000001 | 4ﾄﾆ
 DK-AA08K-MEE9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DK-AA08H-MEE9                       | 0        | 2022-04-01 |       10.948355856 | 4ﾄﾆ
 DK-AA08K-MEM9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DL-AA08K-MEM9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DL-AA08K-MEP9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DL-AA08K-MMW9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DM-AA08H-MEE9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DM-AA08H-MEP9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DP-GA08H-MEEF                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DN-AA08H-MEE9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DR-AA08H-MEE9                       | 0        | 2022-04-01 | 4.9765270080000015 | 4ﾄﾆ
 DS-AA08H-MEE9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DR-AA08K-MEE9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DR-AA08K-MEM9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 DR-AA08K-MEP9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DS-AA08K-MMT9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 DS-AA08K-MEE9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DS-AA08K-MEM9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 DS-AA08K-MEP9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DV-AA08H-MEE9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DT-AA08H-MEP9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DV-AA08H-MEP9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DV-AA08H-MMW9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 DX-GA08KL-MEMD                      | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DX-AA08H-MEE9                       | 0        | 2022-04-01 |       13.934271552 | 4ﾄﾆ
 DX-AA08K-MEE9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DX-AA08K-MEM9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DX-AA08K-MEP9                       | 0        | 2022-04-01 | 15.924882863999999 | 4ﾄﾆ
 DX-GA08K-MEPF                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DX-GA08KR-MEMD                      | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 LL-AB08H-MDY9                       | 0        | 2022-04-01 |        6.967135776 | 4ﾄﾆ
 DY-AA08H-MEP9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DY-AA08H-MMW9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 DY-AA08K-MEM9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 DY-AA08K-MEP9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 DZ-GA08K-MEMF                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 ED-AA08K-MEM9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 LL-AA08H-MDY9                       | 0        | 2022-04-01 |        22.89201864 | 4ﾄﾆ
 LL-AA08H-MEN9                       | 0        | 2022-04-01 |       36.826290192 | 4ﾄﾆ
 DY-AA08H-MEE9                       | 0        | 2022-04-01 | 4.9765270080000015 | 4ﾄﾆ
 DZ-AA08H-MEE9                       | 0        | 2022-04-01 | 4.9765270080000015 | 4ﾄﾆ
 DZ-AA08H-MEP9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 DZ-AA08K-MEE9                       | 0        | 2022-04-01 | 5.9718313919999995 | 4ﾄﾆ
 DZ-AA08K-MEP9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 EF-AA08H-MEP9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 EF-AA08K-MEM9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 LL-AA08H-MMS9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 LL-AA08H-MMV9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 LL-AA08K-MDY9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 LL-AA08K-MEL9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 LL-AA08K-MEN9                       | 0        | 2022-04-01 |       30.854461344 | 4ﾄﾆ
 LL-AA08K-MMV9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 LL-AB08K-MEL9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 LL-AB08H-MEN9                       | 0        | 2022-04-01 | 12.938967168000001 | 4ﾄﾆ
 LL-AB08H-MMS9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 LL-AB08K-MDY9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 LL-AD08K-MMV9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 LL-GA08HR-MDYD                      | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 LL-AF08H-MEN9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 LL-AF08K-MEN9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 LL-AD08K-MEL9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 LL-AD08K-MEN9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 LL-GA08H-MDYF                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 LL-GA08KL-MEND                      | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 LL-GA08KR-MDYD                      | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 MM-AC08H-MDY9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 MM-AA08H-MDY9                       | 0        | 2022-04-01 |       24.882629952 | 4ﾄﾆ
 MM-AA08H-MEN9                       | 0        | 2022-04-01 | 16.920187247999998 | 4ﾄﾆ
 MM-AA08H-MMS9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 MM-AA08H-MMV9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 MM-AA08K-MDY9                       | 0        | 2022-04-01 |  34.83568142400001 | 4ﾄﾆ
 LL-GB08HL-MDYD                      | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 MM-AA08K-MEL9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 MM-AA08K-MEN9                       | 0        | 2022-04-01 |        26.87323872 | 4ﾄﾆ
 MM-AA08K-MMS9                       | 0        | 2022-04-01 |       10.948355856 | 4ﾄﾆ
 MM-AB08H-MDY9                       | 0        | 2022-04-01 |  7.962440160000001 | 4ﾄﾆ
 MM-AB08H-MEN9                       | 0        | 2022-04-01 | 17.915494176000003 | 4ﾄﾆ
 MM-AB08H-MMS9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 MM-AB08K-MDY9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 MM-AB08K-MEL9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 MM-AB08K-MMS9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 MM-AD08H-MEN9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 MM-AD08K-MEN9                       | 0        | 2022-04-01 |        6.967135776 | 4ﾄﾆ
 MM-AF08H-MEN9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 MM-GA08HR-MEND                      | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 MM-AP08H-MDY9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 MM-AP08H-MEN9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 MM-AP08K-MEL9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 MM-GB08KL-MELD                      | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 MM-GA08K-MELF                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 MM-GB08KR-MELD                      | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 PP-AA08H-MDY9                       | 0        | 2022-04-01 |        45.78403728 | 4ﾄﾆ
 MM-GD08HR-MEND                      | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 MM-GD08KL-MDYD                      | 0        | 2022-04-01 | 5.9718313919999995 | 4ﾄﾆ
 MM-GD08KL-MELD                      | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 PP-AA08H-MEN9                       | 0        | 2022-04-01 |       32.845070112 | 4ﾄﾆ
 PP-AA08H-MMS9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 PP-AA08H-MMV9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 PP-AA08K-MDY9                       | 0        | 2022-04-01 | 20.901407327999998 | 4ﾄﾆ
 PP-AE08H-MDY9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 PP-AA08K-MEL9                       | 0        | 2022-04-01 | 5.9718313919999995 | 4ﾄﾆ
 PP-AA08K-MEN9                       | 0        | 2022-04-01 | 11.943662783999999 | 4ﾄﾆ
 PP-AA08K-MMS9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 PP-AA08K-MMV9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 PP-AD08K-MDY9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 PP-AD08K-MEL9                       | 0        | 2022-04-01 |                  0 | 4ﾄﾆ
 PP-AB08H-MDY9                       | 0        | 2022-04-01 |       24.882629952 | 4ﾄﾆ
 PP-AB08H-MEN9                       | 0        | 2022-04-01 |       10.948355856 | 4ﾄﾆ
 PP-AB08H-MMS9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 PP-AB08H-MMV9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 PP-AB08K-MEL9                       | 0        | 2022-04-01 | 5.9718313919999995 | 4ﾄﾆ
 PP-AC08H-MDY9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 PP-GD08HL-MEND                      | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 PP-AF08H-MDY9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 PP-GA08HR-MEND                      | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 PP-GA08KL-MELD                      | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 PP-GA08KL-MEND                      | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 PP-GC08HL-MDYD                      | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 WA-AB08H-MDY9                       | 0        | 2022-04-01 | 20.901407327999998 | 4ﾄﾆ
 WA-AA08H-MDY9                       | 0        | 2022-04-01 |       99.530517264 | 4ﾄﾆ
 WA-AA08H-MEN9                       | 0        | 2022-04-01 |        68.67605592 | 4ﾄﾆ
 WA-AA08H-MMS9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 WA-AA08H-MMV9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 WA-AA08K-MDY9                       | 0        | 2022-04-01 |       27.868545648 | 4ﾄﾆ
 PP-GD08HR-MEND                      | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 WA-AA08K-MEL9                       | 0        | 2022-04-01 | 15.924882863999999 | 4ﾄﾆ
 WA-AA08K-MEN9                       | 0        | 2022-04-01 | 51.755868672000005 | 4ﾄﾆ
 WA-AA08K-MMS9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 WA-AA08K-MMV9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 WA-AB08H-MEN9                       | 0        | 2022-04-01 | 3.9812200800000004 | 4ﾄﾆ
 WA-AB08H-MMS9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 WA-AB08H-MMV9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 WA-AB08K-MDY9                       | 0        | 2022-04-01 | 14.929578480000002 | 4ﾄﾆ
 WA-AB08K-MEL9                       | 0        | 2022-04-01 |       10.948355856 | 4ﾄﾆ
 WA-AB08K-MEN9                       | 0        | 2022-04-01 | 14.929578480000002 | 4ﾄﾆ
 WA-AB08K-MMS9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 WA-AB08K-MMV9                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 WA-AD08K-MDY9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 WA-AP08K-MEL9                       | 0        | 2022-04-01 |        1.990611312 | 4ﾄﾆ
 WA-GB08KL-MEND                      | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 WA-AP08H-MDY9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 WA-GB08K-MMVD                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 WA-GB08KR-MEND                      | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 YY-GE08HL-MDYD                      | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 YY-AA08H-MDY9                       | 0        | 2022-04-01 | 117.44600889600001 | 4ﾄﾆ
 YY-AA08H-MEN9                       | 0        | 2022-04-01 |  38.81690150400001 | 4ﾄﾆ
 YY-AA08H-MMS9                       | 0        | 2022-04-01 |       13.934271552 | 4ﾄﾆ
 YY-AA08H-MMV9                       | 0        | 2022-04-01 |  7.962440160000001 | 4ﾄﾆ
 YY-GC08KL-MELD                      | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 YY-AA08K-MDY9                       | 0        | 2022-04-01 | 58.723004448000005 | 4ﾄﾆ
 YY-AA08K-MEL9                       | 0        | 2022-04-01 |       39.812205888 | 4ﾄﾆ
 YY-AA08K-MEN9                       | 0        | 2022-04-01 | 25.877934336000003 | 4ﾄﾆ
 YY-AA08K-MMS9                       | 0        | 2022-04-01 | 5.9718313919999995 | 4ﾄﾆ
 YY-AA08K-MMV9                       | 0        | 2022-04-01 |  8.957747088000001 | 4ﾄﾆ
 YY-AC08H-MEN9                       | 0        | 2022-04-01 | 2.9859156959999997 | 4ﾄﾆ
 YY-GE08H-MMSD                       | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
 YY-GE08HR-MDYD                      | 0        | 2022-04-01 |        0.995304384 | 4ﾄﾆ
(195 rows)

select
	*
from
	forecast.D608221_MC_CATEGORY_FORECAST_ADJUSTED
where
	category_cd = '4ﾄﾆ'
and
	ymd = '2022-04-01'

 store_cd | category_cd |    ymd     |   sales_forecast   
----------+-------------+------------+--------------------
 0        | 4ﾄﾆ         | 2022-04-01 | 2544.0000203520035
(1 row)

##### MC対象外の機種として通常予測値を利用するケース

select
	product_code,
	sales_forecast
from
	forecast.D608111_PRODUCT_FORECAST_ADJUSTED
where
	category_cd = '4ﾄﾊ'
and
	ymd = '2022-04-01'
and
	sales_forecast > 0

            product_code             | sales_forecast 
-------------------------------------+----------------
 4ﾄﾊ_ﾄｸﾁｭｳ                           |             14
 LL-YB9996-MWBL                      |              1
(2 rows)

select
	product_code,
	sales_forecast
from (
	select
		*
	from
		forecast.D608220_MC_PRODUCT_FORECAST_ADJUSTED
) t1
inner join (
    select
        product_category_3 as category_cd,
        product_code
    from
        ifi0072_mc_product_category_mapping
) t2
using (
	product_code
)
where
	category_cd = '4ﾄﾊ'
and
	ymd = '2022-04-01'
and (
	sales_forecast > 0
or
	product_code in ('4ﾄﾊ_ﾄｸﾁｭｳ','LL-YB9996-MWBL')
)

            product_code             | sales_forecast 
-------------------------------------+----------------
 4ﾄﾊ_ﾄｸﾁｭｳ                           |             18
(1 row)

select
	*
from
	forecast.D608221_MC_CATEGORY_FORECAST_ADJUSTED
where
	category_cd = '4ﾄﾊ'
and
	ymd = '2022-04-01'

 store_cd | category_cd |    ymd     | sales_forecast 
----------+-------------+------------+----------------
 0        | 4ﾄﾊ         | 2022-04-01 |             19
(1 row)

### PR60010(品種別予測値出力)
#### D603010において予測対象外でない全品種の予測値が存在する

create table ifo0010_category_sales_2022_it_forecast as (select * from ifi0010_category_sales_2022_it limit 0);
\copy ifo0010_category_sales_2022_it_forecast from '/storage/mdf/interface/ifout/forecast/IFO0010_CATEGORY_SALES_FORECAST_202204_01.csv' with csv header;

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 2) as category_cd
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		split_part(sku_cd, '__', 1) = '1'
	group by
		category_cd
) t1

 count 
-------
   859
(1 row)

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 2) as category_cd
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		split_part(sku_cd, '__', 1) = '1'
	group by
		category_cd
) t1
inner join (
	select
		product_category_3 as category_cd
	from
		ifo0010_category_sales_2022_it_forecast
	group by
		category_cd
) t2
using (
	category_cd
)

 count 
-------
   859
(1 row)

### PR60010(機種別予測値出力)
#### D603010において予測対象外でない全機種の予測値が存在する

create table ifo0011_product_sales_2022_it_forecast as (select * from ifi0011_product_sales_2022_it limit 0);
\copy ifo0011_product_sales_2022_it_forecast from '/storage/mdf/interface/ifout/forecast/IFO0011_PRODUCT_SALES_FORECAST_202204_01.csv' with csv header;

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as product_code
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		split_part(sku_cd, '__', 1) = '2'
	group by
		product_code
) t1

 count 
-------
 78820
(1 row)

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as product_code
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		split_part(sku_cd, '__', 1) = '2'
	group by
		product_code
) t1
inner join (
	select
		product_code
	from
		ifo0011_product_sales_2022_it_forecast
	group by
		product_code
) t2
using (
	product_code
)

 count 
-------
 78820
(1 row)

### PR60010(エリア別予測値出力)
#### D603010において予測対象外でない全機種×エリアの予測値が存在する

create table ifo0012_area_sales_forecast as (select * from ifi0012_area_sales_2022_it limit 0);
\copy ifo0012_area_sales_forecast from '/storage/mdf/interface/ifout/forecast/IFO0012_AREA_SALES_FORECAST_202204_01.csv' with csv header;

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as product_code,
		split_part(sku_cd, '__', 4) as area_code
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		split_part(sku_cd, '__', 1) = '3'
	group by
		product_code,
		area_code
) t1

 count  
--------
 183895
(1 row)

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as product_code,
		split_part(sku_cd, '__', 4) as area_code
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		split_part(sku_cd, '__', 1) = '3'
	group by
		product_code,
		area_code
) t1
inner join (
	select
		product_code,
		area_code
	from
		ifo0012_area_sales_forecast
	group by
		product_code,
		area_code
) t2
using (
	product_code,
	area_code
)

 count  
--------
 183895
(1 row)

### PR60011(グループ×エリア別予測値出力)
#### D603010において予測対象外でない全グループ×エリアの予測値が存在する

create table ifo0070_group_area_sales_forecast (group_code CHAR(35) NOT NULL, area_code CHAR(1) NOT NULL, kick_date CHAR(6) NOT NULL, qty double precision, PRIMARY KEY(group_code, area_code, kick_date));
\copy ifo0070_group_area_sales_forecast from '/storage/mdf/interface/ifout/forecast/IFO0070_GROUP_AREA_SALES_FORECAST_202204.csv' with csv header;

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code,
		split_part(sku_cd, '__', 4) as area_code
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		split_part(sku_cd, '__', 1) = '4'
	group by
		group_code,
		area_code
) t1

 count  
--------
 9296
(1 row)

select
	count(*)
from (
	select
		split_part(sku_cd, '__', 3) as group_code,
		split_part(sku_cd, '__', 4) as area_code
	from
		sysinf.d603010_forecasting_methods
	where
		forecast_target
	and
		split_part(sku_cd, '__', 1) = '4'
	group by
		group_code,
		area_code
) t1
inner join (
	select
		group_code,
		area_code
	from
		ifo0070_group_area_sales_forecast
	group by
		group_code,
		area_code
) t2
using (
	group_code,
	area_code
)

 count  
--------
 9296
(1 row)

### PR60031(予測対象外品リスト出力)
#### D603010のsku_cdと品種別/機種別/エリア別のIFが一致する。

create table IFO0020_CATEGORY_NOT_FORECAST (product_category_3 CHAR(3) NOT NULL, period int, density double precision);
\copy IFO0020_CATEGORY_NOT_FORECAST from '/storage/mdf/interface/ifout/na_list/IFO0020_CATEGORY_NOT_FORECAST_202204_01.csv' with csv header;

create table IFO0021_PRODUCT_NOT_FORECAST (product_category_3 CHAR(3) NOT NULL, product_code CHAR(35) NOT NULL, period int, density double precision);
\copy IFO0021_PRODUCT_NOT_FORECAST from '/storage/mdf/interface/ifout/na_list/IFO0021_PRODUCT_NOT_FORECAST_202204_01.csv' with csv header;

create table IFO0022_AREA_NOT_FORECAST (product_category_3 CHAR(3) NOT NULL, product_code CHAR(35) NOT NULL, area_code CHAR(1) NOT NULL, period int, density double precision);
\copy IFO0022_AREA_NOT_FORECAST from '/storage/mdf/interface/ifout/na_list/IFO0022_AREA_NOT_FORECAST_202204_01.csv' with csv header;

select
    count(*)
from (
    select
        sku_cd
    from
        sysinf.d603010_forecasting_methods
    where
        split_part(sku_cd, '__', 1) != '4'
    group by
        sku_cd
) t1

 count  
--------
 569218
(1 row)

select
    count(*)
from (
    select
        sku_cd
    from
        sysinf.d603010_forecasting_methods
    where
        split_part(sku_cd, '__', 1) != '4'
    group by
        sku_cd
) t1
inner join (
    select
        '1' || '__' || product_category_3 || '__0__0' as sku_cd
    from
        ifo0010_category_sales_2022_it_forecast
    group by
        product_category_3
    union
    select
        '1' || '__' || product_category_3 || '__0__0' as sku_cd
    from
        IFO0020_CATEGORY_NOT_FORECAST
    group by
        product_category_3
    union
    select
        '2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd
    from
        ifo0011_product_sales_2022_it_forecast
    group by
        product_category_3,
        product_code
    union
    select
        '2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd
    from
        IFO0021_PRODUCT_NOT_FORECAST
    group by
        product_category_3,
        product_code
    union
    select
        '3' || '__' || product_category_3 || '__' || product_code || '__' || area_code as sku_cd
    from
        ifo0012_area_sales_forecast
    group by
        product_category_3,
        product_code,
        area_code
    union
    select
        '3' || '__' || product_category_3 || '__' || product_code || '__' || area_code as sku_cd
    from
        IFO0022_AREA_NOT_FORECAST
    group by
        product_category_3,
        product_code,
        area_code
) t2
using (
    sku_cd
)

 count  
--------
 569218
(1 row)

### PR60050(予測手法IF出力)
#### 品種別/機種別/エリア別予測対象のskuが存在する。

create table IFO0030_CATEGORY_SELECTED_METHOD (product_category_3 CHAR(3), forecast_success int, forecast_type int, forecast_method char(2), validation_error double precision, ABC_class char(1));
\copy IFO0030_CATEGORY_SELECTED_METHOD from '/storage/mdf/interface/ifout/status/IFO0030_CATEGORY_SELECTED_METHOD_202204_01.csv' with csv header;

create table IFO0031_PRODUCT_SELECTED_METHOD (product_category_3 CHAR(3), product_code CHAR(35), forecast_success int, forecast_type int, forecast_method char(2), validation_error double precision, ABC_class char(1));
\copy IFO0031_PRODUCT_SELECTED_METHOD from '/storage/mdf/interface/ifout/status/IFO0031_PRODUCT_SELECTED_METHOD_202204_01.csv' with csv header;

create table IFO0032_AREA_SELECTED_METHOD (product_category_3 CHAR(3), product_code CHAR(35), area_code CHAR(1), forecast_success int, forecast_type int, forecast_method char(2), validation_error double precision, ABC_class char(1));
\copy IFO0032_AREA_SELECTED_METHOD from '/storage/mdf/interface/ifout/status/IFO0032_AREA_SELECTED_METHOD_202204_01.csv' with csv header;

select
    count(*)
from (
    select
        sku_cd
    from
        sysinf.d603010_forecasting_methods
    where
        forecast_target
    and
        split_part(sku_cd, '__', 1) != '4'
    group by
        sku_cd
) t1

 count  
--------
 263574
(1 row)

select
    count(*)
from (
    select
        sku_cd
    from
        sysinf.d603010_forecasting_methods
    where
        forecast_target
    and
        split_part(sku_cd, '__', 1) != '4'
    group by
        sku_cd
) t1
inner join (
    select
        '1' || '__' || product_category_3 || '__0__0' as sku_cd
    from
        IFO0030_CATEGORY_SELECTED_METHOD
    group by
        product_category_3
    union
    select
        '2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd
    from
        IFO0031_PRODUCT_SELECTED_METHOD
    group by
        product_category_3,
        product_code
    union
    select
        '3' || '__' || product_category_3 || '__' || product_code || '__' || area_code as sku_cd
    from
        IFO0032_AREA_SELECTED_METHOD
    group by
        product_category_3,
        product_code,
        area_code
) t2
using (
    sku_cd
)

 count  
--------
 263574
(1 row)

#### 予測成否が成功となっている。

select
    count(*),
    sum(forecast_success) as forecast_success_count
from (
    select
        sku_cd
    from
        sysinf.d603010_forecasting_methods
    where
        forecast_target
    and
        split_part(sku_cd, '__', 1) != '4'
    group by
        sku_cd
) t1
inner join (
    select
        '1' || '__' || product_category_3 || '__0__0' as sku_cd,
        forecast_success
    from
        IFO0030_CATEGORY_SELECTED_METHOD
    group by
        product_category_3,
        forecast_success
    union
    select
        '2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd,
        forecast_success
    from
        IFO0031_PRODUCT_SELECTED_METHOD
    group by
        product_category_3,
        product_code,
        forecast_success
    union
    select
        '3' || '__' || product_category_3 || '__' || product_code || '__' || area_code as sku_cd,
        forecast_success
    from
        IFO0032_AREA_SELECTED_METHOD
    group by
        product_category_3,
        product_code,
        area_code,
        forecast_success
) t2
using (
    sku_cd
)

 count  | forecast_success_count 
--------+------------------------
 263574 |                 263574
(1 row)

#### 通常/MC対応予測比率が概ね予想通りになっている。

select
    count(*),
    sum(case when forecast_type = 1 then 1 else 0 end) as normal_forecast_count,
    sum(case when forecast_type = 2 then 1 else 0 end) as mc_forecast_count
from (
    select
        sku_cd
    from
        sysinf.d603010_forecasting_methods
    where
        forecast_target
    and
        split_part(sku_cd, '__', 1) != '4'
    group by
        sku_cd
) t1
inner join (
    select
        '1' || '__' || product_category_3 || '__0__0' as sku_cd,
        forecast_success,
        forecast_type
    from
        IFO0030_CATEGORY_SELECTED_METHOD
    group by
        product_category_3,
        forecast_success,
        forecast_type
    union
    select
        '2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd,
        forecast_success,
        forecast_type
    from
        IFO0031_PRODUCT_SELECTED_METHOD
    group by
        product_category_3,
        product_code,
        forecast_success,
        forecast_type
    union
    select
        '3' || '__' || product_category_3 || '__' || product_code || '__' || area_code as sku_cd,
        forecast_success,
        forecast_type
    from
        IFO0032_AREA_SELECTED_METHOD
    group by
        product_category_3,
        product_code,
        area_code,
        forecast_success,
        forecast_type
) t2
using (
    sku_cd
)

 count  | normal_forecast_count | mc_forecast_count 
--------+-----------------------+-------------------
 263574 |                137616 |            125958
(1 row)

### PR60070(予測誤差IF出力)
#### 品種別/機種別/エリア別予測対象のskuが存在する。

product_category_3 CHAR(3), forecast_success int, forecast_type int, forecast_method char(2), validation_error double precision,ABC_class char(1)

create table IFO0040_CATEGORY_FORECAST_ERROR (
    product_category_3 CHAR(3),
    forecast_3month_total double precision,
    qty_3month_total double precision,
    error_3month_total double precision,
    confidence_interval_upper double precision,
    confidence_interval_lower double precision,
    error_distribution_median double precision,
    error_distribution_mean double precision,
    error_distribution_std double precision,
    error_distribution_n int,
    forecast_date char(6)
);
\copy IFO0040_CATEGORY_FORECAST_ERROR from '/storage/mdf/interface/ifout/accuracy/IFO0040_CATEGORY_FORECAST_ERROR_202204_01.csv' with csv header;

create table IFO0041_PRODUCT_FORECAST_ERROR (
    product_category_3 CHAR(3),
    product_code CHAR(35),
    forecast_3month_total double precision,
    qty_3month_total double precision,
    error_3month_total double precision,
    confidence_interval_upper double precision,
    confidence_interval_lower double precision,
    error_distribution_median double precision,
    error_distribution_mean double precision,
    error_distribution_std double precision,
    error_distribution_n int,
    forecast_date char(6)
);
\copy IFO0041_PRODUCT_FORECAST_ERROR from '/storage/mdf/interface/ifout/accuracy/IFO0041_PRODUCT_FORECAST_ERROR_202204_01.csv' with csv header;

create table IFO0042_AREA_FORECAST_ERROR (
    product_category_3 CHAR(3),
    product_code CHAR(35),
    area_code CHAR(1),
    forecast_3month_total double precision,
    qty_3month_total double precision,
    error_3month_total double precision,
    confidence_interval_upper double precision,
    confidence_interval_lower double precision,
    error_distribution_median double precision,
    error_distribution_mean double precision,
    error_distribution_std double precision,
    error_distribution_n int,
    forecast_date char(6)
);
\copy IFO0042_AREA_FORECAST_ERROR from '/storage/mdf/interface/ifout/accuracy/IFO0042_AREA_FORECAST_ERROR_202204_01.csv' with csv header;

select
    count(*)
from (
    select
        sku_cd
    from
        sysinf.d603010_forecasting_methods
    where
        forecast_target
    and
        split_part(sku_cd, '__', 1) != '4'
    group by
        sku_cd
) t1

 count  
--------
 263574
(1 row)

select
    count(*)
from (
    select
        sku_cd
    from
        sysinf.d603010_forecasting_methods
    where
        forecast_target
    and
        split_part(sku_cd, '__', 1) != '4'
    group by
        sku_cd
) t1
inner join (
    select
        '1' || '__' || product_category_3 || '__0__0' as sku_cd
    from
        IFO0040_CATEGORY_FORECAST_ERROR
    group by
        product_category_3
    union
    select
        '2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd
    from
        IFO0041_PRODUCT_FORECAST_ERROR
    group by
        product_category_3,
        product_code
    union
    select
        '3' || '__' || product_category_3 || '__' || product_code || '__' || area_code as sku_cd
    from
        IFO0042_AREA_FORECAST_ERROR
    group by
        product_category_3,
        product_code,
        area_code
) t2
using (
    sku_cd
)

 count  
--------
 263574
(1 row)

#### 1月〜3月の実績値の合計を累積実績に格納

select
    count(*)
from (
    select
        sku_cd
    from
        sysinf.d603010_forecasting_methods
    where
        forecast_target
    and
        split_part(sku_cd, '__', 1) != '4'
    group by
        sku_cd
) t1
inner join 
    data.D602000_ACTUAL_SALES_CLEANSED_3MONTHS t2
using (
    sku_cd
)
inner join (
    select
        '1' || '__' || product_category_3 || '__0__0' as sku_cd,
        qty_3month_total,
        forecast_3month_total
    from
        IFO0040_CATEGORY_FORECAST_ERROR
    group by
        product_category_3,
        qty_3month_total,
        forecast_3month_total
    union
    select
        '2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd,
        qty_3month_total,
        forecast_3month_total
    from
        IFO0041_PRODUCT_FORECAST_ERROR
    group by
        product_category_3,
        product_code,
        qty_3month_total,
        forecast_3month_total
    union
    select
        '3' || '__' || product_category_3 || '__' || product_code || '__' || area_code as sku_cd,
        qty_3month_total,
        forecast_3month_total
    from
        IFO0042_AREA_FORECAST_ERROR
    group by
        product_category_3,
        product_code,
        area_code,
        qty_3month_total,
        forecast_3month_total
) t3
using (
    sku_cd,
    qty_3month_total
)

 count  
--------
 263574
(1 row)

#### 4月〜6月の予測値の合計を累積予測に格納

select
    count(*)
from (
    select
        sku_cd
    from
        sysinf.d603010_forecasting_methods
    where
        forecast_target
    and
        split_part(sku_cd, '__', 1) != '4'
    group by
        sku_cd
) t1
inner join (
    select
        sku_cd,
        round(sales_forecast) as sales_forecast
    from (
        select
            sku_cd,
            sum(sales_forecast) as sales_forecast
        from
            FORECAST.D608110_CATEGORY_FORECAST_ADJUSTED
        where
            '2022-04-01' <= ymd
        and
            ymd < '2022-07-01'
        group by
            sku_cd
        union
        select
            sku_cd,
            sum(sales_forecast) as sales_forecast
        from
            FORECAST.D608111_PRODUCT_FORECAST_ADJUSTED
        where
            '2022-04-01' <= ymd
        and
            ymd < '2022-07-01'
        group by
            sku_cd
        union
        select
            sku_cd,
            sum(sales_forecast) as sales_forecast
        from
            FORECAST.D608112_AREA_FORECAST_ADJUSTED
        where
            '2022-04-01' <= ymd
        and
            ymd < '2022-07-01'
        group by
            sku_cd
    ) t
) t2
using (
    sku_cd
)
inner join (
    select
        sku_cd,
        round(sales_forecast) as sales_forecast
    from (
        select
            sku_cd,
            sales_forecast
        from
            FORECAST.D607010_CATEGORY_FORECAST_ARCHIVE
        where
            '2022-04-01' = ymd
        group by
            sku_cd,
            sales_forecast
        union
        select
            sku_cd,
            sales_forecast
        from
            FORECAST.D607011_PRODUCT_FORECAST_ARCHIVE
        where
            '2022-04-01' = ymd
        group by
            sku_cd,
            sales_forecast
        union
        select
            sku_cd,
            sales_forecast
        from
            FORECAST.D607012_AREA_FORECAST_ARCHIVE
        where
            '2022-04-01' = ymd
        group by
            sku_cd,
            sales_forecast
    ) t
) t3
using (
    sku_cd,
    sales_forecast
)

 count  
--------
 263574
(1 row)