设计简单的模式来分解需求预测 [英] Designing Simple Schema for Disaggregation of Demand Forecast
问题描述
编辑：[细节]我正在做一个简单的数据库设计任务作为一个训练练习，我必须为以下情况提出一个基本的模式设计：
我有一个父子层次结构的产品（原材料>正在进行中>结束产品）。
订单放在每个级别。
订单的数量应在未来6个月的每周桶中查看。
在未来6个月内，需求预测将针对每周桶进行。
通常在层次结构的较高级别（原材料或正在进行中的工作）完成
必须将其分解到较低级别（结束产品）
有两种方法可以将需求预测从较高级别分解为较低级别：
-
用户指定最终产品的百分比分布。说，对于正在进行中的工作，预测为1000。用户说我希望结束产品1需要40％，而在第10桶中，最终产品2需要60％。然后，从现在开始，第10周（星期日到星期六），预测值对于最终产品1将是400，对于最终产品2将是600。
-
用户说，只是按照Bucket 5中最终产品的订单分解，最终产品1和2的桶5中的订单分别为200和800，则EP1的预测值为（（200/1000）* 100）％，EP2为（（800/1000）* 100）％） 工作进行中的预测。
预测将在未来6个月的每周桶中查看，理想的格式应该是：
产品名称|桶号|周开始日期|周结束日期|预测值
这样的要求是一个基本的理想模式是什么？
< hr>
Product_Hierarchy 表格可能如下所示：
id |名称| parent_id
__________________________________________
1 |原料| （null）
2 |正在进行中| 1
3 |最终产品1 | 2
4 |最终产品2 | 2
这是存储订单的好方法吗？
订单
id | prod_id | order_date | delivery_date | deliver_date
其中，
prod_id 是引用product_hierarchy表的 id 的外键
$ 26
pre $ SELECT
COUNT（*）TOTAL_ORDERS，
WIDTH_BUCKET（
delivery_date，
SYSDATE，
ADD_MONTHS（sysdate，6），
TO_NUMBER（TO_CHAR（SYSDATE，'DD-MON-YYYY'）） - TO_CHAR（ADD_MONTHS（sysdate，6） ，'DD-MON-YYYY'））/ 7
）BUCKET_NO
FROM
orders_table
WHERE
delivery_date BETWEEN SYSDATE AND ADD_MONTHS（sysdate，6）;
但是，从今天开始，每周可以提供一周的桶。我想要他们在星期日到星期六。
请帮助设计这个数据库结构。
（Gonna使用Oracle 11g）
你最后的评论正是我的意思。很高兴看到你得到它！
自从我开始这样做以来，我完成了一个例外的代码。与您所说的不同之处在于分离将不会改变的内容（ raw_material VS raw_material_hist ）仅使用日期
CREATE TABLE raw_material
（
material_id NUMBER PRIMARY KEY，
material_blabla VARCHAR2（20）
）;
CREATE TABLE wip
（
wip_id NUMBER PRIMARY KEY，
parent_raw NUMBER REFERENCES raw_material（material_id），
wip_desc VARCHAR2（20）
）;
CREATE TABLE end_product
（
end_product_id NUMBER PRIMARY KEY，
parent_wip NUMBER REFERENCES wip（wip_id），
description VARCHAR2（20）
）;
CREATE TABLE rm_histo
（
material_id NUMBER REFERENCES raw_material（material_id），
week_start DATE CHECK（To_char（week_start，'D'）= 1），
预测NUMBER（8）CHECK（预测> 0），
CONSTRAINT pk_rm_histo PRIMARY KEY（material_id，week_start）
）;
CREATE TABLE wip_histo
（
wip_id NUMBER REFERENCES wip（wip_id），
week_start DATE CHECK（To_char（week_start，'D'）= 1），
wip_user_forecast NUMBER（8）CHECK（wip_user_forecast> 0），
CONSTRAINT pk_wip_histo PRIMARY KEY（wip_id，week_start）
）;
CREATE TABLE end_prod_histo
（
end_product_id NUMBER REFERENCES end_product（end_product_id），
week_start DATE CHECK（To_char（week_start，'D'）= 1），
end_prod_user_forecast NUMBER（8）CHECK（end_prod_user_forecast> 0）
）;
最后，确实你使用一个视图来查看预测的东西，或者一个物化的你有大量的数据。通过使用视图，您不会复制数据，因此更改/更新更安全更容易。
对于您的用例1或2，这不处理数据库模式。在一天结束时，它将只是更新一些预测值，用例1或2的逻辑可以在PL / SQL过程中或者您正在为接口使用的任何内容。
编辑：同样从您最近的评论中，您提到将预测手动设置为计算的VS。所以我添加了这样一个列，但是信用卡给你
编辑二：对于桶号，只需使用适当的日期掩码，例如 IW 或 WW 。这两个变化是一年的第一个星期。
Edit: [Details] I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (Raw Material > Work in Progress > End Product).
Orders are placed at each level.
Number of orders shall be viewable in weekly buckets for the next 6 months.
Demand forecast can be made for each product level.
Demand forecast is done for weekly buckets, for the next 6 months.
It's usually done at the higher level in hierarchy (Raw Material or Work in Progress)
It has to be disaggregated to a lower level (End Product)
There are 2 ways in which demand forecast can be disaggregated from a higher level to lower level:
User specifies percentage distribution for end product. Say, there's a forecast of 1000 for Work In Progress.. and user says I want 40% for End Product 1 and 60% for End Product 2 in bucket 10.. Then for 10th week (Sunday to Saturday) from now, forecast value for End Product 1 would be 400 and, for End Product 2 would be 600.
User says, just disaggregate according to orders placed against end products in Bucket 5, and orders in bucket 5 for End Product 1 and 2 are 200 and 800 respectively, then forecast value for EP1 would be ((200/1000) * 100)% and for EP2 would be ((800/1000) * 100)% of forecast for 'Work in Progress'.
Forecast shall be viewable in weekly buckets for the next 6 months and the ideal format should be:
product name | bucket number | week start date | week end date | forecast value
What would be the a basic ideal schema for such a requirement?
Product_Hierarchy table could look like this:
id | name | parent_id
__________________________________________
1 | raw material | (null)
2 | work in progress | 1
3 | end product 1 | 2
4 | end product 2 | 2
Is this a good way to store orders?
Orders
id | prod_id | order_date | delivery_date | delivered_date
where,
prod_id is foreign key that references id of product_hierarchy table,
The orders for 26 weekly buckets can be selected as
SELECT
COUNT(*) TOTAL_ORDERS,
WIDTH_BUCKET(
delivery_date,
SYSDATE,
ADD_MONTHS(sysdate, 6),
TO_NUMBER( TO_CHAR(SYSDATE,'DD-MON-YYYY') - TO_CHAR(ADD_MONTHS(sysdate, 6),'DD-MON-YYYY') ) / 7
) BUCKET_NO
FROM
orders_table
WHERE
delivery_date BETWEEN SYSDATE AND ADD_MONTHS(sysdate, 6);
But this will give weekly buckets starting from today irrespective of the day. I want them to be in Sunday to Saturday weeks.
Please help designing this database structure.
(Gonna be using Oracle 11g)
Your last comment is exactly what I meant. Cool to see you got it!
Since I had started doing it, I finished an exemple code. The difference with what you were saying is separating what will change from what will not (raw_material VS raw_material_hist) using only date for the week, which is monday, and various check constraints.
CREATE TABLE raw_material
(
material_id NUMBER PRIMARY KEY,
material_blabla VARCHAR2(20)
);
CREATE TABLE wip
(
wip_id NUMBER PRIMARY KEY,
parent_raw NUMBER REFERENCES raw_material(material_id),
wip_desc VARCHAR2(20)
);
CREATE TABLE end_product
(
end_product_id NUMBER PRIMARY KEY,
parent_wip NUMBER REFERENCES wip(wip_id),
description VARCHAR2(20)
);
CREATE TABLE rm_histo
(
material_id NUMBER REFERENCES raw_material(material_id),
week_start DATE CHECK (To_char(week_start, 'D')=1),
forecast NUMBER(8) CHECK (forecast >0),
CONSTRAINT pk_rm_histo PRIMARY KEY (material_id, week_start)
);
CREATE TABLE wip_histo
(
wip_id NUMBER REFERENCES wip(wip_id),
week_start DATE CHECK(To_char(week_start, 'D')=1),
wip_user_forecast NUMBER(8) CHECK (wip_user_forecast>0),
CONSTRAINT pk_wip_histo PRIMARY KEY (wip_id, week_start)
);
CREATE TABLE end_prod_histo
(
end_product_id NUMBER REFERENCES end_product(end_product_id),
week_start DATE CHECK(To_char(week_start, 'D')=1),
end_prod_user_forecast NUMBER(8) CHECK (end_prod_user_forecast >0)
);
And at the end, indeed you use a view to see the forecasted things, or a materialized one if you have tons of data. By using a view, you do not duplicate the data, so it's safer and easier to change/update.
For your use cases 1 or 2, this does not deal with the database schema. At the end of the day it'll just be updating some value for the forecast, the logic of use cases 1 or 2 could go in a PL/SQL procedure or whatever you are using for the interface.
Edit: Also from your last comment you were mentionning having the forecasted manually set VS the computed one. So I added such a column, but credits go to you
Edit bis: As for the bucket number, just use a proper mask for the date, like IW or WW. These two changes which is the first week of the year.
这篇关于设计简单的模式来分解需求预测的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
