# 結合テスト第二回詳細確認結果
### PR60210(MC対象機種の抽出)
#### 機種コードが新旧機種マッピングに含まれる
```
select
count(*)
from (
select
product_code
from
data.d602100_mc_actual_sales_cleansed
group by
product_code
) t1
```
```
count
-------
61214
(1 row)
```
```
drop table IFI0070_MC_PRODUCT_GROUP_MAPPING_first;
drop table IFI0070_MC_PRODUCT_GROUP_MAPPING_second;
create table IFI0070_MC_PRODUCT_GROUP_MAPPING_first(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_first from '/storage/mdf/interface/ifin/IFI0070_MC_PRODUCT_GROUP_MAPPING_first.csv' with csv header;
create table IFI0070_MC_PRODUCT_GROUP_MAPPING_second(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_second from '/storage/mdf/interface/ifin/IFI0070_MC_PRODUCT_GROUP_MAPPING_second.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_first
group by
product_code
union
select
product_code
from
IFI0070_MC_PRODUCT_GROUP_MAPPING_second
group by
product_code
) t2
using (
product_code
)
```
```
count
-------
61214
(1 row)
```
#### 実績データが存在する
```
drop table ifi0012_area_sales;
create table ifi0012_area_sales(product_category_3 CHAR(3) NOT NULL,product_code CHAR(35) NOT NULL,area_code char(1),kick_date char(8),qty int,PRIMARY KEY(product_category_3, product_code, area_code, kick_date));
\copy ifi0012_area_sales from '/storage/mdf/interface/ifin/IFI0012_AREA_SALES.csv' with csv header;
create table
d602100_mc_actual_sales_cleansed_if_months
as (
select
product_code, area_code, to_char(ymd, 'YYYYMMDD') as ymd, sales_units
from
data.d602100_mc_actual_sales_cleansed
where
ymd >= '2022-03-01'
and
ymd < '2022-05-01'
group by
product_code, area_code, ymd, sales_units
);
```
```
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_second
group by
product_code
) t2
using (
product_code
)
```
```
count
-------
95015
(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_second
group by
product_code
) t2
using (
product_code
)
inner join (
select
product_code, area_code, ymd, sales_units
from
d602100_mc_actual_sales_cleansed_if_months
) t3
using (
product_code, area_code, ymd, sales_units
)
```
```
count
-------
95015
(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_second
group by
product_code
) t2
using (
product_code
)
inner join (
select
product_code, area_code, ymd, TO_DATE('2022-10-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, グループ×エリア別)
#### グループ×機種マッピングに含まれる
```
select
count(*)
from (
select
split_part(sku_cd, '__', 3) as group_code,
split_part(sku_cd, '__', 4) as area_code
from
data.d602500_actual_sales_monthly
where
sku_cd like '4%'
group by
group_code,
area_code
) t1
```
```
count
-------
15572
(1 row)
```
```
select
count(*)
from (
select
split_part(sku_cd, '__', 3) as group_code,
split_part(sku_cd, '__', 4) as area_code
from
data.d602500_actual_sales_monthly
where
sku_cd like '4%'
group by
group_code,
area_code
) t1
inner join (
select
group_code
from
data.d601200_mc_product_group_mapping
group by
group_code
) t2
using (
group_code
)
```
```
count
-------
15572
(1 row)
```
#### 1機種でも実績が存在する
```
select
count(*)
from (
select
split_part(sku_cd, '__', 3) as group_code,
split_part(sku_cd, '__', 4) as area_code
from
data.d602500_actual_sales_monthly
where
sku_cd like '4%'
group by
group_code,
area_code
) as t1
```
```
count
-------
15572
(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-05-01'
and
ymd >= '2021-11-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
--------------+-----------------
インテリア | 45060
エクステリア | 834
サッシドア | 26632
(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-11-01' <= ymd
and
ymd < '2022-05-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_EG-YA055Z-MWG5__C | 43931
エクステリア | 4__0__G_H-C511-PVAA__C | 818
サッシドア | 4__0__G_ﾏ8ﾐ_ﾄｸﾁｭｳ__D | 1224
(3 rows)
```
#### 全グループ×エリアにつき実績が格納される
```
select
count(*)
from (
select
group_code,
area_code
from
data.d602100_mc_actual_sales_cleansed
group by
group_code,
area_code
) t1
```
```
count
-------
15598
(1 row)
```
```
select
count(*)
from (
select
group_code,
area_code
from
data.d602100_mc_actual_sales_cleansed
group by
group_code,
area_code
) t1
inner join (
select
split_part(sku_cd, '__', 3) as group_code,
split_part(sku_cd, '__', 4) as area_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,
split_part(sku_cd, '__', 4) as area_code
from
data.d602520_actual_sales_tendays
where
sku_cd like '4%'
group by
sku_cd
) t2
using (
group_code,
area_code
)
```
```
count
-------
15598
(1 row)
```
### PR60260(グループ×エリア別集計：D602520, グループ×エリア別)
#### グループ×機種マッピングに含まれる
```
select
count(*)
from (
select
split_part(sku_cd, '__', 3) as group_code,
split_part(sku_cd, '__', 4) as area_code
from
data.d602520_actual_sales_tendays
where
sku_cd like '4%'
group by
group_code,
area_code
) t1
```
```
count
-------
26
(1 row)
```
```
select
count(*)
from (
select
split_part(sku_cd, '__', 3) as group_code,
split_part(sku_cd, '__', 4) as area_code
from
data.d602520_actual_sales_tendays
where
sku_cd like '4%'
group by
group_code,
area_code
) t1
inner join (
select
group_code
from
data.d601200_mc_product_group_mapping
group by
group_code
) t2
using (
group_code
)
```
```
count
-------
26
(1 row)
```
#### 1機種でも実績が存在する
```
select
count(*)
from (
select
split_part(sku_cd, '__', 3) as group_code,
split_part(sku_cd, '__', 4) as area_code
from
data.d602520_actual_sales_tendays
where
sku_cd like '4%'
group by
group_code,
area_code
) as t1
```
```
count
-------
26
(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-05-01'
and
ymd >= '2021-11-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
--------------+-----------------
インテリア | 45060
エクステリア | 834
サッシドア | 26632
(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-11-01' <= true_ymd
and
true_ymd < '2022-05-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_DD-YH055HJ-MBAD__D | 55592
エクステリア | 4__0__G_H-C731-PVAA__D | 840
(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-10-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-10-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
--------
590023
(1 row)
```
```
select
count(sku_cd)
from
data.d603020_maximum_sales
```
```
count
--------
590023
(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)
```
### PR60810(構成比率按分, D608210)
#### 新旧予測配分表(IFI0071)に存在する機種・エリアである
```
drop table IFI0071_MC_PRODUCT_FORECAST_ALLOCATION_second;
create table IFI0071_MC_PRODUCT_FORECAST_ALLOCATION_second(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_second from '/storage/mdf/interface/ifin/IFI0071_MC_PRODUCT_FORECAST_ALLOCATION_second.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
--------
343600
(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_second
group by
product_code,
area_code
) t2
using (
product_code,
area_code
)
```
```
count
--------
343600
(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
*
from
forecast.d608210_mc_area_forecast_adjusted
where
ymd < '2022-05-01'
) t
```
```
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_second
group by
group_code,
product_code,
area_code
) t2
using (
group_code,
area_code
)
```
```
count
--------
343600
(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_second
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
--------
343600
(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
--------
343600
(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_second
group by
product_code,
area_code
) t2
using (
product_code,
area_code
)
```
```
count
--------
343600
(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
*
from
forecast.D608211_MC_AREA_FORECAST_ADJUSTED_ON_DATE
where
ymd < '2022-05-01'
) t
```
```
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_second
group by
group_code,
product_code,
area_code
) t2
using (
group_code,
area_code
)
```
```
count
--------
343600
(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_second
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
--------
343600
(1 row)
```
### PR60830(機種エリア積み上げ, D608220)
#### 新旧予測配分表(IFI0071)に存在する機種である
```
select
count(*)
from (
select
product_code
from
forecast.D608220_MC_PRODUCT_FORECAST_ADJUSTED
group by
product_code
) t1
```
```
count
--------
62810
(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_second
group by
product_code
) t2
using (
product_code
)
```
```
count
--------
62810
(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)
```
```
select
count(*)
from (
select
*
from
forecast.D608220_MC_PRODUCT_FORECAST_ADJUSTED
where
ymd < '2022-05-01'
) t
```
```
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_second
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
-------
62810
(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_second
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
-------
62810
(1 row)
```
#### 集計結果の正しさを確認
```
select
*
from
forecast.D608211_MC_AREA_FORECAST_ADJUSTED_ON_DATE
where
product_code = '4ﾄｱ_ﾄｸﾁｭｳ'
and
ymd = '2022-05-01'
```
```
store_cd | product_code | area_code | ymd | sales_forecast
----------+-------------------------------------+-----------+------------+--------------------
0 | 4ﾄｱ_ﾄｸﾁｭｳ | A | 2022-05-01 | 264.684967344
0 | 4ﾄｱ_ﾄｸﾁｭｳ | B | 2022-05-01 | 208.42483661999998
0 | 4ﾄｱ_ﾄｸﾁｭｳ | C | 2022-05-01 | 1373.8549020839998
0 | 4ﾄｱ_ﾄｸﾁｭｳ | D | 2022-05-01 | 1177.6732027199998
0 | 4ﾄｱ_ﾄｸﾁｭｳ | E | 2022-05-01 | 268.18300655999997
0 | 4ﾄｱ_ﾄｸﾁｭｳ | F | 2022-05-01 | 409.999346442
(6 rows)
```
```
select
*
from
forecast.D608220_MC_PRODUCT_FORECAST_ADJUSTED
where
product_code = '4ﾄｱ_ﾄｸﾁｭｳ'
and
ymd = '2022-05-01'
```
```
store_cd | product_code | ymd | sales_forecast
----------+-------------------------------------+------------+--------------------
0 | 4ﾄｱ_ﾄｸﾁｭｳ | 2022-05-01 | 3702.8202617699994
(1 row)
```
### PR60831(品種機種積み上げ, D608221)
#### 品種機種対応表(IFI0072)に存在するすべての品種の予測値が存在する
```
create table IFI0072_MC_PRODUCT_CATEGORY_MAPPING_second(product_category_3 CHAR(3) NOT NULL,product_code CHAR(35) NOT NULL, PRIMARY KEY(product_category_3, product_code));
\copy IFI0072_MC_PRODUCT_CATEGORY_MAPPING_second from '/storage/mdf/interface/ifin/IFI0072_MC_PRODUCT_CATEGORY_MAPPING_second.csv' with csv header;
```
```
select
count(*)
from (
select
product_category_3 as category_cd
from
ifi0072_mc_product_category_mapping_second
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_second
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)
```
```
select
count(*)
from (
select
*
from
forecast.D608221_MC_CATEGORY_FORECAST_ADJUSTED
where
ymd < '2022-05-01'
) t
```
```
count
-------
0
(1 row)
```
#### 集計結果の正しさを確認
##### MC対象の機種として通常予測値が存在するケース
```
select
*
from
forecast.D608111_PRODUCT_FORECAST_ADJUSTED
where
category_cd = '4ﾄﾆ'
and
ymd = '2022-05-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ﾄﾆ_ﾄｸﾁｭｳ')
)
```
```
store_cd | sku_cd | category_cd | product_code | ymd | sales_forecast
----------+------------------------------------------------+-------------+-------------------------------------+------------+--------------------
0 | 2__4ﾄﾆ__YY-AA9996-MDY9__0 | 4ﾄﾆ | YY-AA9996-MDY9 | 2022-05-01 | 1
0 | 2__4ﾄﾆ__DB-AA08H-MEE9__0 | 4ﾄﾆ | DB-AA08H-MEE9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__4ﾄﾆ_ﾄｸﾁｭｳ__0 | 4ﾄﾆ | 4ﾄﾆ_ﾄｸﾁｭｳ | 2022-05-01 | 835.7605641600001
0 | 2__4ﾄﾆ__DB-AA08H-MMT9__0 | 4ﾄﾆ | DB-AA08H-MMT9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DB-AA08K-MEM9__0 | 4ﾄﾆ | DB-AA08K-MEM9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DC-AA08H-MEE9__0 | 4ﾄﾆ | DC-AA08H-MEE9 | 2022-05-01 | 4.061033831999999
0 | 2__4ﾄﾆ__DD-AA08K-MEN9__0 | 4ﾄﾆ | DD-AA08K-MEN9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DC-AA08K-MEM9__0 | 4ﾄﾆ | DC-AA08K-MEM9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DC-AA08K-MEP9__0 | 4ﾄﾆ | DC-AA08K-MEP9 | 2022-05-01 | 4.061033831999999
0 | 2__4ﾄﾆ__DD-AD08H-MDY9__0 | 4ﾄﾆ | DD-AD08H-MDY9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DD-AA08H-MDY9__0 | 4ﾄﾆ | DD-AA08H-MDY9 | 2022-05-01 | 10.558685471999999
0 | 2__4ﾄﾆ__DD-AB08H-MDY9__0 | 4ﾄﾆ | DD-AB08H-MDY9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DD-AB08H-MEN9__0 | 4ﾄﾆ | DD-AB08H-MEN9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DD-AD08K-MEL9__0 | 4ﾄﾆ | DD-AD08K-MEL9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DD-AA08H-MEN9__0 | 4ﾄﾆ | DD-AA08H-MEN9 | 2022-05-01 | 4.8732397679999995
0 | 2__4ﾄﾆ__DD-AA08H-MMS9__0 | 4ﾄﾆ | DD-AA08H-MMS9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DD-AA08K-MEL9__0 | 4ﾄﾆ | DD-AA08K-MEL9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DD-AE08H-MDY9__0 | 4ﾄﾆ | DD-AE08H-MDY9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DD-AP08H-MEN9__0 | 4ﾄﾆ | DD-AP08H-MEN9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DD-AP08H-MMV9__0 | 4ﾄﾆ | DD-AP08H-MMV9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DD-GD08K-MENF__0 | 4ﾄﾆ | DD-GD08K-MENF | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DD-GF08KL-MDYD__0 | 4ﾄﾆ | DD-GF08KL-MDYD | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DE-AA08H-MMT9__0 | 4ﾄﾆ | DE-AA08H-MMT9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DD-GF08KR-MDYD__0 | 4ﾄﾆ | DD-GF08KR-MDYD | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DE-AA08K-MEE9__0 | 4ﾄﾆ | DE-AA08K-MEE9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DF-AA08K-MEE9__0 | 4ﾄﾆ | DF-AA08K-MEE9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DE-AA08H-MEE9__0 | 4ﾄﾆ | DE-AA08H-MEE9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DG-AA08K-MEE9__0 | 4ﾄﾆ | DG-AA08K-MEE9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DG-AA08H-MEE9__0 | 4ﾄﾆ | DG-AA08H-MEE9 | 2022-05-01 | 8.122065588
0 | 2__4ﾄﾆ__DG-AA08H-MEP9__0 | 4ﾄﾆ | DG-AA08H-MEP9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DG-AA08K-MEM9__0 | 4ﾄﾆ | DG-AA08K-MEM9 | 2022-05-01 | 4.061033831999999
0 | 2__4ﾄﾆ__DG-AA08K-MEP9__0 | 4ﾄﾆ | DG-AA08K-MEP9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DG-AA08K-MMT9__0 | 4ﾄﾆ | DG-AA08K-MMT9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DG-AA08K-MMW9__0 | 4ﾄﾆ | DG-AA08K-MMW9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DH-AA08H-MEE9__0 | 4ﾄﾆ | DH-AA08H-MEE9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DJ-AA08H-MEE9__0 | 4ﾄﾆ | DJ-AA08H-MEE9 | 2022-05-01 | 20.305165008
0 | 2__4ﾄﾆ__DJ-AA08H-MEP9__0 | 4ﾄﾆ | DJ-AA08H-MEP9 | 2022-05-01 | 4.8732397679999995
0 | 2__4ﾄﾆ__DK-AA08H-MEE9__0 | 4ﾄﾆ | DK-AA08H-MEE9 | 2022-05-01 | 8.934271524
0 | 2__4ﾄﾆ__DJ-AA08K-MEM9__0 | 4ﾄﾆ | DJ-AA08K-MEM9 | 2022-05-01 | 10.558685471999999
0 | 2__4ﾄﾆ__DK-AA08K-MEM9__0 | 4ﾄﾆ | DK-AA08K-MEM9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DN-AA08H-MEE9__0 | 4ﾄﾆ | DN-AA08H-MEE9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DL-AA08K-MEM9__0 | 4ﾄﾆ | DL-AA08K-MEM9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DK-AA08H-MEP9__0 | 4ﾄﾆ | DK-AA08H-MEP9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DK-AA08K-MEE9__0 | 4ﾄﾆ | DK-AA08K-MEE9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DL-AA08K-MEP9__0 | 4ﾄﾆ | DL-AA08K-MEP9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DL-AA08K-MMW9__0 | 4ﾄﾆ | DL-AA08K-MMW9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DM-AA08H-MEE9__0 | 4ﾄﾆ | DM-AA08H-MEE9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DM-AA08H-MEP9__0 | 4ﾄﾆ | DM-AA08H-MEP9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DP-GA08H-MEEF__0 | 4ﾄﾆ | DP-GA08H-MEEF | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DR-AA08H-MEE9__0 | 4ﾄﾆ | DR-AA08H-MEE9 | 2022-05-01 | 4.061033831999999
0 | 2__4ﾄﾆ__DV-AA08H-MMW9__0 | 4ﾄﾆ | DV-AA08H-MMW9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DR-AA08K-MEE9__0 | 4ﾄﾆ | DR-AA08K-MEE9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DR-AA08K-MEM9__0 | 4ﾄﾆ | DR-AA08K-MEM9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DR-AA08K-MEP9__0 | 4ﾄﾆ | DR-AA08K-MEP9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DS-AA08K-MEE9__0 | 4ﾄﾆ | DS-AA08K-MEE9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DS-AA08K-MEM9__0 | 4ﾄﾆ | DS-AA08K-MEM9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DS-AA08K-MEP9__0 | 4ﾄﾆ | DS-AA08K-MEP9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DS-AA08K-MMT9__0 | 4ﾄﾆ | DS-AA08K-MMT9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DS-AA08H-MEE9__0 | 4ﾄﾆ | DS-AA08H-MEE9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DT-AA08H-MEP9__0 | 4ﾄﾆ | DT-AA08H-MEP9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DV-AA08H-MEE9__0 | 4ﾄﾆ | DV-AA08H-MEE9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DV-AA08H-MEP9__0 | 4ﾄﾆ | DV-AA08H-MEP9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DX-GA08KL-MEMD__0 | 4ﾄﾆ | DX-GA08KL-MEMD | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DX-AA08H-MEE9__0 | 4ﾄﾆ | DX-AA08H-MEE9 | 2022-05-01 | 11.370891408000002
0 | 2__4ﾄﾆ__DX-AA08K-MEE9__0 | 4ﾄﾆ | DX-AA08K-MEE9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DX-AA08K-MEM9__0 | 4ﾄﾆ | DX-AA08K-MEM9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DX-AA08K-MEP9__0 | 4ﾄﾆ | DX-AA08K-MEP9 | 2022-05-01 | 12.995305356
0 | 2__4ﾄﾆ__DX-GA08K-MEPF__0 | 4ﾄﾆ | DX-GA08K-MEPF | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__DX-GA08KR-MEMD__0 | 4ﾄﾆ | DX-GA08KR-MEMD | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DY-AA08H-MEE9__0 | 4ﾄﾆ | DY-AA08H-MEE9 | 2022-05-01 | 4.061033831999999
0 | 2__4ﾄﾆ__DY-AA08H-MEP9__0 | 4ﾄﾆ | DY-AA08H-MEP9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DY-AA08H-MMW9__0 | 4ﾄﾆ | DY-AA08H-MMW9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DZ-AA08K-MEE9__0 | 4ﾄﾆ | DZ-AA08K-MEE9 | 2022-05-01 | 4.8732397679999995
0 | 2__4ﾄﾆ__DY-AA08K-MEM9__0 | 4ﾄﾆ | DY-AA08K-MEM9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DY-AA08K-MEP9__0 | 4ﾄﾆ | DY-AA08K-MEP9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__DZ-AA08K-MEP9__0 | 4ﾄﾆ | DZ-AA08K-MEP9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__DZ-AA08H-MEE9__0 | 4ﾄﾆ | DZ-AA08H-MEE9 | 2022-05-01 | 4.061033831999999
0 | 2__4ﾄﾆ__DZ-GA08K-MEMF__0 | 4ﾄﾆ | DZ-GA08K-MEMF | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__DZ-AA08H-MEP9__0 | 4ﾄﾆ | DZ-AA08H-MEP9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__ED-AA08K-MEM9__0 | 4ﾄﾆ | ED-AA08K-MEM9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__LL-AA08H-MMS9__0 | 4ﾄﾆ | LL-AA08H-MMS9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__EF-AA08H-MEP9__0 | 4ﾄﾆ | EF-AA08H-MEP9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__EF-AA08K-MEM9__0 | 4ﾄﾆ | EF-AA08K-MEM9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__LL-AA08H-MMV9__0 | 4ﾄﾆ | LL-AA08H-MMV9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__LL-AA08H-MDY9__0 | 4ﾄﾆ | LL-AA08H-MDY9 | 2022-05-01 | 18.680751060000002
0 | 2__4ﾄﾆ__LL-AA08H-MEN9__0 | 4ﾄﾆ | LL-AA08H-MEN9 | 2022-05-01 | 30.051642468
0 | 2__4ﾄﾆ__LL-AA08K-MMV9__0 | 4ﾄﾆ | LL-AA08K-MMV9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__LL-AA08K-MDY9__0 | 4ﾄﾆ | LL-AA08K-MDY9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__LL-AA08K-MEL9__0 | 4ﾄﾆ | LL-AA08K-MEL9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__LL-AA08K-MEN9__0 | 4ﾄﾆ | LL-AA08K-MEN9 | 2022-05-01 | 25.178404776
0 | 2__4ﾄﾆ__LL-AB08H-MDY9__0 | 4ﾄﾆ | LL-AB08H-MDY9 | 2022-05-01 | 5.685445704000001
0 | 2__4ﾄﾆ__LL-AB08H-MEN9__0 | 4ﾄﾆ | LL-AB08H-MEN9 | 2022-05-01 | 10.558685471999999
0 | 2__4ﾄﾆ__LL-AD08K-MEL9__0 | 4ﾄﾆ | LL-AD08K-MEL9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__LL-AB08H-MMS9__0 | 4ﾄﾆ | LL-AB08H-MMS9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__LL-AB08K-MDY9__0 | 4ﾄﾆ | LL-AB08K-MDY9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__LL-AB08K-MEL9__0 | 4ﾄﾆ | LL-AB08K-MEL9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__LL-AD08K-MEN9__0 | 4ﾄﾆ | LL-AD08K-MEN9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__LL-AD08K-MMV9__0 | 4ﾄﾆ | LL-AD08K-MMV9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__LL-AF08K-MEN9__0 | 4ﾄﾆ | LL-AF08K-MEN9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__LL-GA08HR-MDYD__0 | 4ﾄﾆ | LL-GA08HR-MDYD | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__LL-AF08H-MEN9__0 | 4ﾄﾆ | LL-AF08H-MEN9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__LL-GA08H-MDYF__0 | 4ﾄﾆ | LL-GA08H-MDYF | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__LL-GB08HL-MDYD__0 | 4ﾄﾆ | LL-GB08HL-MDYD | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__LL-GA08KL-MEND__0 | 4ﾄﾆ | LL-GA08KL-MEND | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__LL-GA08KR-MDYD__0 | 4ﾄﾆ | LL-GA08KR-MDYD | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__MM-AB08H-MDY9__0 | 4ﾄﾆ | MM-AB08H-MDY9 | 2022-05-01 | 6.497651640000001
0 | 2__4ﾄﾆ__MM-AA08H-MDY9__0 | 4ﾄﾆ | MM-AA08H-MDY9 | 2022-05-01 | 20.305165008
0 | 2__4ﾄﾆ__MM-AA08H-MEN9__0 | 4ﾄﾆ | MM-AA08H-MEN9 | 2022-05-01 | 13.807511292000001
0 | 2__4ﾄﾆ__MM-AA08H-MMS9__0 | 4ﾄﾆ | MM-AA08H-MMS9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__MM-AA08H-MMV9__0 | 4ﾄﾆ | MM-AA08H-MMV9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__MM-AA08K-MDY9__0 | 4ﾄﾆ | MM-AA08K-MDY9 | 2022-05-01 | 28.427230596
0 | 2__4ﾄﾆ__MM-AA08K-MEL9__0 | 4ﾄﾆ | MM-AA08K-MEL9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__MM-AA08K-MEN9__0 | 4ﾄﾆ | MM-AA08K-MEN9 | 2022-05-01 | 21.929576880000003
0 | 2__4ﾄﾆ__MM-AA08K-MMS9__0 | 4ﾄﾆ | MM-AA08K-MMS9 | 2022-05-01 | 8.934271524
0 | 2__4ﾄﾆ__MM-AB08H-MMS9__0 | 4ﾄﾆ | MM-AB08H-MMS9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__MM-AB08K-MDY9__0 | 4ﾄﾆ | MM-AB08K-MDY9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__MM-AB08K-MEL9__0 | 4ﾄﾆ | MM-AB08K-MEL9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__MM-AB08K-MMS9__0 | 4ﾄﾆ | MM-AB08K-MMS9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__MM-AB08H-MEN9__0 | 4ﾄﾆ | MM-AB08H-MEN9 | 2022-05-01 | 14.619719304
0 | 2__4ﾄﾆ__MM-AC08H-MDY9__0 | 4ﾄﾆ | MM-AC08H-MDY9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__MM-AD08H-MEN9__0 | 4ﾄﾆ | MM-AD08H-MEN9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__MM-AD08K-MEN9__0 | 4ﾄﾆ | MM-AD08K-MEN9 | 2022-05-01 | 5.685445704000001
0 | 2__4ﾄﾆ__MM-AF08H-MEN9__0 | 4ﾄﾆ | MM-AF08H-MEN9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__MM-AP08H-MDY9__0 | 4ﾄﾆ | MM-AP08H-MDY9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__MM-AP08H-MEN9__0 | 4ﾄﾆ | MM-AP08H-MEN9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__MM-GA08K-MELF__0 | 4ﾄﾆ | MM-GA08K-MELF | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__MM-AP08K-MEL9__0 | 4ﾄﾆ | MM-AP08K-MEL9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__MM-GB08KR-MELD__0 | 4ﾄﾆ | MM-GB08KR-MELD | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__MM-GA08HR-MEND__0 | 4ﾄﾆ | MM-GA08HR-MEND | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__MM-GD08HR-MEND__0 | 4ﾄﾆ | MM-GD08HR-MEND | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__MM-GB08KL-MELD__0 | 4ﾄﾆ | MM-GB08KL-MELD | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__MM-GD08KL-MDYD__0 | 4ﾄﾆ | MM-GD08KL-MDYD | 2022-05-01 | 4.8732397679999995
0 | 2__4ﾄﾆ__MM-GD08KL-MELD__0 | 4ﾄﾆ | MM-GD08KL-MELD | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__PP-AA08H-MMV9__0 | 4ﾄﾆ | PP-AA08H-MMV9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__PP-AA08K-MDY9__0 | 4ﾄﾆ | PP-AA08K-MDY9 | 2022-05-01 | 17.056337112
0 | 2__4ﾄﾆ__PP-AA08H-MDY9__0 | 4ﾄﾆ | PP-AA08H-MDY9 | 2022-05-01 | 37.361502120000004
0 | 2__4ﾄﾆ__PP-AA08K-MEL9__0 | 4ﾄﾆ | PP-AA08K-MEL9 | 2022-05-01 | 4.8732397679999995
0 | 2__4ﾄﾆ__PP-AA08H-MEN9__0 | 4ﾄﾆ | PP-AA08H-MEN9 | 2022-05-01 | 26.802816648
0 | 2__4ﾄﾆ__PP-AA08K-MEN9__0 | 4ﾄﾆ | PP-AA08K-MEN9 | 2022-05-01 | 9.746479535999999
0 | 2__4ﾄﾆ__PP-AA08H-MMS9__0 | 4ﾄﾆ | PP-AA08H-MMS9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__PP-AA08K-MMS9__0 | 4ﾄﾆ | PP-AA08K-MMS9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__PP-AA08K-MMV9__0 | 4ﾄﾆ | PP-AA08K-MMV9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__PP-AD08K-MDY9__0 | 4ﾄﾆ | PP-AD08K-MDY9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__PP-AB08K-MEL9__0 | 4ﾄﾆ | PP-AB08K-MEL9 | 2022-05-01 | 4.8732397679999995
0 | 2__4ﾄﾆ__PP-AC08H-MDY9__0 | 4ﾄﾆ | PP-AC08H-MDY9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__PP-AB08H-MDY9__0 | 4ﾄﾆ | PP-AB08H-MDY9 | 2022-05-01 | 20.305165008
0 | 2__4ﾄﾆ__PP-AB08H-MEN9__0 | 4ﾄﾆ | PP-AB08H-MEN9 | 2022-05-01 | 8.934271524
0 | 2__4ﾄﾆ__PP-AB08H-MMS9__0 | 4ﾄﾆ | PP-AB08H-MMS9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__PP-AB08H-MMV9__0 | 4ﾄﾆ | PP-AB08H-MMV9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__PP-GA08HR-MEND__0 | 4ﾄﾆ | PP-GA08HR-MEND | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__PP-AF08H-MDY9__0 | 4ﾄﾆ | PP-AF08H-MDY9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__PP-AE08H-MDY9__0 | 4ﾄﾆ | PP-AE08H-MDY9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__PP-GA08KL-MELD__0 | 4ﾄﾆ | PP-GA08KL-MELD | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__PP-GA08KL-MEND__0 | 4ﾄﾆ | PP-GA08KL-MEND | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__PP-GC08HL-MDYD__0 | 4ﾄﾆ | PP-GC08HL-MDYD | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__PP-GD08HL-MEND__0 | 4ﾄﾆ | PP-GD08HL-MEND | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__PP-GD08HR-MEND__0 | 4ﾄﾆ | PP-GD08HR-MEND | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__WA-AA08H-MDY9__0 | 4ﾄﾆ | WA-AA08H-MDY9 | 2022-05-01 | 81.220657956
0 | 2__4ﾄﾆ__WA-AA08H-MEN9__0 | 4ﾄﾆ | WA-AA08H-MEN9 | 2022-05-01 | 56.04225318
0 | 2__4ﾄﾆ__WA-AB08H-MDY9__0 | 4ﾄﾆ | WA-AB08H-MDY9 | 2022-05-01 | 17.056337112
0 | 2__4ﾄﾆ__WA-AA08H-MMS9__0 | 4ﾄﾆ | WA-AA08H-MMS9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__WA-AA08H-MMV9__0 | 4ﾄﾆ | WA-AA08H-MMV9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__WA-AA08K-MDY9__0 | 4ﾄﾆ | WA-AA08K-MDY9 | 2022-05-01 | 22.741784892
0 | 2__4ﾄﾆ__WA-AA08K-MEL9__0 | 4ﾄﾆ | WA-AA08K-MEL9 | 2022-05-01 | 12.995305356
0 | 2__4ﾄﾆ__WA-AA08K-MEN9__0 | 4ﾄﾆ | WA-AA08K-MEN9 | 2022-05-01 | 42.234741887999995
0 | 2__4ﾄﾆ__WA-AB08K-MMS9__0 | 4ﾄﾆ | WA-AB08K-MMS9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__WA-AB08K-MMV9__0 | 4ﾄﾆ | WA-AB08K-MMV9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__WA-AA08K-MMS9__0 | 4ﾄﾆ | WA-AA08K-MMS9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__WA-AA08K-MMV9__0 | 4ﾄﾆ | WA-AA08K-MMV9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__WA-AB08H-MMS9__0 | 4ﾄﾆ | WA-AB08H-MMS9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__WA-AB08H-MMV9__0 | 4ﾄﾆ | WA-AB08H-MMV9 | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__WA-AB08K-MDY9__0 | 4ﾄﾆ | WA-AB08K-MDY9 | 2022-05-01 | 12.18309942
0 | 2__4ﾄﾆ__WA-AB08K-MEL9__0 | 4ﾄﾆ | WA-AB08K-MEL9 | 2022-05-01 | 8.934271524
0 | 2__4ﾄﾆ__WA-AB08K-MEN9__0 | 4ﾄﾆ | WA-AB08K-MEN9 | 2022-05-01 | 12.18309942
0 | 2__4ﾄﾆ__WA-AB08H-MEN9__0 | 4ﾄﾆ | WA-AB08H-MEN9 | 2022-05-01 | 3.2488258200000004
0 | 2__4ﾄﾆ__WA-AD08K-MDY9__0 | 4ﾄﾆ | WA-AD08K-MDY9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__WA-AP08H-MDY9__0 | 4ﾄﾆ | WA-AP08H-MDY9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__YY-AA08H-MDY9__0 | 4ﾄﾆ | YY-AA08H-MDY9 | 2022-05-01 | 95.840375184
0 | 2__4ﾄﾆ__WA-AP08K-MEL9__0 | 4ﾄﾆ | WA-AP08K-MEL9 | 2022-05-01 | 1.6244139479999997
0 | 2__4ﾄﾆ__WA-GB08KL-MEND__0 | 4ﾄﾆ | WA-GB08KL-MEND | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__WA-GB08K-MMVD__0 | 4ﾄﾆ | WA-GB08K-MMVD | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__WA-GB08KR-MEND__0 | 4ﾄﾆ | WA-GB08KR-MEND | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__YY-GE08HL-MDYD__0 | 4ﾄﾆ | YY-GE08HL-MDYD | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__YY-AA08H-MEN9__0 | 4ﾄﾆ | YY-AA08H-MEN9 | 2022-05-01 | 31.676056416
0 | 2__4ﾄﾆ__YY-AA08H-MMS9__0 | 4ﾄﾆ | YY-AA08H-MMS9 | 2022-05-01 | 11.370891408000002
0 | 2__4ﾄﾆ__YY-AA08H-MMV9__0 | 4ﾄﾆ | YY-AA08H-MMV9 | 2022-05-01 | 6.497651640000001
0 | 2__4ﾄﾆ__YY-AA08K-MDY9__0 | 4ﾄﾆ | YY-AA08K-MDY9 | 2022-05-01 | 47.920187592
0 | 2__4ﾄﾆ__YY-AA08K-MEL9__0 | 4ﾄﾆ | YY-AA08K-MEL9 | 2022-05-01 | 32.488262352
0 | 2__4ﾄﾆ__YY-AC08H-MEN9__0 | 4ﾄﾆ | YY-AC08H-MEN9 | 2022-05-01 | 2.4366198839999997
0 | 2__4ﾄﾆ__YY-AA08K-MEN9__0 | 4ﾄﾆ | YY-AA08K-MEN9 | 2022-05-01 | 21.117370943999997
0 | 2__4ﾄﾆ__YY-AA08K-MMS9__0 | 4ﾄﾆ | YY-AA08K-MMS9 | 2022-05-01 | 4.8732397679999995
0 | 2__4ﾄﾆ__YY-AA08K-MMV9__0 | 4ﾄﾆ | YY-AA08K-MMV9 | 2022-05-01 | 7.309859652
0 | 2__4ﾄﾆ__YY-GC08KL-MELD__0 | 4ﾄﾆ | YY-GC08KL-MELD | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__YY-GE08H-MMSD__0 | 4ﾄﾆ | YY-GE08H-MMSD | 2022-05-01 | 0.812205936
0 | 2__4ﾄﾆ__YY-GE08HR-MDYD__0 | 4ﾄﾆ | YY-GE08HR-MDYD | 2022-05-01 | 0.812205936
```
```
select
*
from
forecast.D608221_MC_CATEGORY_FORECAST_ADJUSTED
where
category_cd = '4ﾄﾆ'
and
ymd = '2022-05-01'
```
```
store_cd | category_cd | ymd | sales_forecast
----------+-------------+------------+--------------------
0 | 4ﾄﾆ | 2022-05-01 | 2077.0000166080017
(1 row)
```
### PR60010(品種別予測値出力)
#### D603010において予測対象外でない全品種の予測値が存在する
```
create table ifo0010_category_sales_2022_it_forecast_05(product_category_3 CHAR(3) NOT NULL,kick_date char(8),qty int,PRIMARY KEY(product_category_3, kick_date));
\copy ifo0010_category_sales_2022_it_forecast_05 from '/storage/mdf/interface/ifout/forecast/IFO0010_CATEGORY_SALES_FORECAST_202205_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
-------
869
(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_05
group by
category_cd
) t2
using (
category_cd
)
```
```
count
-------
869
(1 row)
```
### PR60010(機種別予測値出力)
#### D603010において予測対象外でない全機種の予測値が存在する
```
create table ifo0011_product_sales_2022_it_forecast_05(product_category_3 CHAR(3) NOT NULL,product_code CHAR(35) NOT NULL,kick_date char(8),qty double precision,PRIMARY KEY(product_category_3, product_code, kick_date));
\copy ifo0011_product_sales_2022_it_forecast_05 from '/storage/mdf/interface/ifout/forecast/IFO0011_PRODUCT_SALES_FORECAST_202205_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
-------
79035
(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_05
group by
product_code
) t2
using (
product_code
)
```
```
count
-------
79035
(1 row)
```
### PR60010(エリア別予測値出力)
#### D603010において予測対象外でない全機種×エリアの予測値が存在する
```
create table ifo0012_area_sales_forecast_05(product_category_3 CHAR(3) NOT NULL,product_code CHAR(35) NOT NULL,area_code char(1),kick_date char(8),qty double precision,PRIMARY KEY(product_category_3, product_code, area_code, kick_date));
\copy ifo0012_area_sales_forecast_05 from '/storage/mdf/interface/ifout/forecast/IFO0012_AREA_SALES_FORECAST_202205_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
--------
183840
(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_05
group by
product_code,
area_code
) t2
using (
product_code,
area_code
)
```
```
count
--------
183840
(1 row)
```
### PR60011(グループ×エリア別予測値出力)
#### D603010において予測対象外でない全グループ×エリアの予測値が存在する
```
create table ifo0070_group_area_sales_forecast_05 (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_05 from '/storage/mdf/interface/ifout/forecast/IFO0070_GROUP_AREA_SALES_FORECAST_202205.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
--------
9242
(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_05
group by
group_code,
area_code
) t2
using (
group_code,
area_code
)
```
```
count
--------
9242
(1 row)
```
### PR60031(予測対象外品リスト出力)
#### D603010のsku_cdと品種別/機種別/エリア別のIFが一致する。
```
create table IFO0020_CATEGORY_NOT_FORECAST_05 (product_category_3 CHAR(3) NOT NULL, period int, density double precision);
\copy IFO0020_CATEGORY_NOT_FORECAST_05 from '/storage/mdf/interface/ifout/na_list/IFO0020_CATEGORY_NOT_FORECAST_202205_01.csv' with csv header;
create table IFO0021_PRODUCT_NOT_FORECAST_05 (product_category_3 CHAR(3) NOT NULL, product_code CHAR(35) NOT NULL, period int, density double precision);
\copy IFO0021_PRODUCT_NOT_FORECAST_05 from '/storage/mdf/interface/ifout/na_list/IFO0021_PRODUCT_NOT_FORECAST_202205_01.csv' with csv header;
create table IFO0022_AREA_NOT_FORECAST_05 (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_05 from '/storage/mdf/interface/ifout/na_list/IFO0022_AREA_NOT_FORECAST_202205_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
--------
574425
(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_05
group by
product_category_3
union
select
'1' || '__' || product_category_3 || '__0__0' as sku_cd
from
IFO0020_CATEGORY_NOT_FORECAST_05
group by
product_category_3
union
select
'2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd
from
ifo0011_product_sales_2022_it_forecast_05
group by
product_category_3,
product_code
union
select
'2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd
from
IFO0021_PRODUCT_NOT_FORECAST_05
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_05
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_05
group by
product_category_3,
product_code,
area_code
) t2
using (
sku_cd
)
```
```
count
--------
574425
(1 row)
```
### PR60050(予測手法IF出力)
#### 品種別/機種別/エリア別予測対象のskuが存在する。
```
create table IFO0030_CATEGORY_SELECTED_METHOD_05 (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_05 from '/storage/mdf/interface/ifout/status/IFO0030_CATEGORY_SELECTED_METHOD_202205_01.csv' with csv header;
create table IFO0031_PRODUCT_SELECTED_METHOD_05 (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_05 from '/storage/mdf/interface/ifout/status/IFO0031_PRODUCT_SELECTED_METHOD_202205_01.csv' with csv header;
create table IFO0032_AREA_SELECTED_METHOD_05 (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_05 from '/storage/mdf/interface/ifout/status/IFO0032_AREA_SELECTED_METHOD_202205_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
--------
263744
(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_05
group by
product_category_3
union
select
'2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd
from
IFO0031_PRODUCT_SELECTED_METHOD_05
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_05
group by
product_category_3,
product_code,
area_code
) t2
using (
sku_cd
)
```
```
count
--------
263744
(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_05
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_05
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_05
group by
product_category_3,
product_code,
area_code,
forecast_success
) t2
using (
sku_cd
)
```
```
count | forecast_success_count
--------+------------------------
263744 | 263744
(1 row)
```
### PR60070(予測誤差IF出力)
#### 品種別/機種別/エリア別予測対象のskuが存在する。
```
create table IFO0040_CATEGORY_FORECAST_ERROR_05 (
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_05 from '/storage/mdf/interface/ifout/accuracy/IFO0040_CATEGORY_FORECAST_ERROR_202205_01.csv' with csv header;
create table IFO0041_PRODUCT_FORECAST_ERROR_05 (
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_05 from '/storage/mdf/interface/ifout/accuracy/IFO0041_PRODUCT_FORECAST_ERROR_202205_01.csv' with csv header;
create table IFO0042_AREA_FORECAST_ERROR_05 (
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_05 from '/storage/mdf/interface/ifout/accuracy/IFO0042_AREA_FORECAST_ERROR_202205_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
--------
263744
(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_05
group by
product_category_3
union
select
'2' || '__' || product_category_3 || '__' || product_code || '__' || '0' as sku_cd
from
IFO0041_PRODUCT_FORECAST_ERROR_05
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_05
group by
product_category_3,
product_code,
area_code
) t2
using (
sku_cd
)
```
```
count
--------
263744
(1 row)
```
#### 2月〜4月の実績値の合計を累積実績に格納
```
create table D602000_ACTUAL_SALES_CLEANSED_3MONTHS_second as (
select
*
from
data.D602000_ACTUAL_SALES_CLEANSED
where
ymd >= '2022-02-01'
and
ymd < '2022-05-01'
)
```
```
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,
sum(sales_units) as qty_3month_total
from
D602000_ACTUAL_SALES_CLEANSED_3MONTHS_SECOND
group by
sku_cd
) 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_05
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_05
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_05
group by
product_category_3,
product_code,
area_code,
qty_3month_total,
forecast_3month_total
) t3
using (
sku_cd,
qty_3month_total
)
```
```
count
--------
263744
(1 row)
```
#### 5月〜7月の予測値の合計を累積予測に格納
```
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-05-01' <= ymd
and
ymd < '2022-08-01'
group by
sku_cd
union
select
sku_cd,
sum(sales_forecast) as sales_forecast
from
FORECAST.D608111_PRODUCT_FORECAST_ADJUSTED
where
'2022-05-01' <= ymd
and
ymd < '2022-08-01'
group by
sku_cd
union
select
sku_cd,
sum(sales_forecast) as sales_forecast
from
FORECAST.D608112_AREA_FORECAST_ADJUSTED
where
'2022-05-01' <= ymd
and
ymd < '2022-08-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-05-01' = ymd
group by
sku_cd,
sales_forecast
union
select
sku_cd,
sales_forecast
from
FORECAST.D607011_PRODUCT_FORECAST_ARCHIVE
where
'2022-05-01' = ymd
group by
sku_cd,
sales_forecast
union
select
sku_cd,
sales_forecast
from
FORECAST.D607012_AREA_FORECAST_ARCHIVE
where
'2022-05-01' = ymd
group by
sku_cd,
sales_forecast
) t
) t3
using (
sku_cd,
sales_forecast
)
```
```
count
--------
263744
(1 row)
```
Sign in with Wallet
Connect another wallet