从不同的表填充表

Question

日安。

我有以下表格：

• Order_Header(Order_id {pk}，customer_id {fk}，agent_id {fk}，Order_date(日期格式))
• Invoice_Header (Invoice_ID {pk}，Customer_ID {fk}，Agent_ID{fk}，invoice_Date{日期格式})
• 股票( Product_ID {pk}，Product_description)

我创建了一个名为AVG_COMPLETION_TIME_FACT的表，并希望使用与前三个表有关的以下值填充它：

• Product_ID
• Invoice_month
• Invoice_Year
• AVG_Completion_Time (Invoice_date - Order_date)

我有以下代码不起作用：

    INSERT INTO AVG_COMPLETION_TIME_FACT(

SELECT PRODUCT_ID, EXTRACT (YEAR FROM INVOICE_DATE), EXTRACT (MONTH FROM INVOICE_DATE), (INVOICE_DATE - ORDER_DATE)
FROM STOCK, INVOICE_HEADER, ORDER_HEADER
GROUP BY PRODUCT_ID, EXTRACT (YEAR FROM INVOICE_DATE), EXTRACT (MONTH FROM INVOICE_DATE)
);

我想按product_id，发票年份和发票月份分组。

这个是可能的吗？

如有任何建议，将不胜感激。

问候

Answer 1

简短的回答:如果您的数据库包含了编写正确查询所需的更多列，则可能是可能的。

除了句法问题外，还有几个问题。当我们创建一些测试表时，您可以看到您正在寻找的答案不能从您在问题中提供的列中导出。示例表(Oracle 12c)，省略了所有PK/FK约束：

-- 3 tables, similar to the ones described in your question,
-- including some test data
create table order_header (id, customer_id, agent_id, order_date )
as
select 1000, 100, 1, date'2018-01-01' from dual union all
select 1001, 100, 2, date'2018-01-02' from dual union all
select 1002, 100, 3, date'2018-01-03' from dual
;

create table invoice_header ( id, customer_id, agent_id, invoice_date )
as
select 2000, 100, 1, date'2018-02-01' from dual union all
select 2001, 100, 2, date'2018-03-11' from dual union all
select 2002, 100, 3, date'2018-04-21' from dual
;

create table stock( product_id, product_description) 
as
select 3000, 'product3000' from dual union all
select 3001, 'product3001' from dual union all
select 3002, 'product3002' from dual
;

如果您按照已经完成的方式加入表(使用交叉连接)，您将看到得到的行数比预期的多.但是: invoice_header表和order_header表都不包含任何PRODUCT_ID数据。因此，我们无法判断哪个product_ids与存储的order_ids或invoice_ids相关联。

select 
  product_id
, extract( year from invoice_date )
, extract( month from invoice_date )
, invoice_date - order_date
from stock, invoice_header, order_header -- cross join -> too many rows in the resultset!
-- group by ...
;
...
27 rows selected.

为了使查询正确，您可能应该编写内部联接和条件(关键字: ON)。如果我们尝试使用原始表定义(如您的问题中所提供的)来实现这一点，您将看到我们无法连接所有3个表，因为它们不包含所需的所有列: PRODUCT_ID (表库存)不能与ORDER_HEADER或INVOICE_HEADER相关联。

这两个表(ORDER_HEADER和INVOICE_HEADER)共有的一列是: customer_id，但这不足以回答您的问题。但是，我们可以使用它来演示如何对联接进行编码。

select 
  -- product_id
  IH.customer_id  as cust_id
, OH.id           as OH_id
, IH.id           as IH_id
, extract( year from invoice_date )  as year_
, extract( month from invoice_date ) as month_
, invoice_date - order_date          as completion_time
from invoice_header IH
  join order_header OH on IH.customer_id = OH.customer_id
-- the stock table cannot be joined at this stage
;

缺少栏:请将以下内容视为“概念证明”代码。假设在数据库中的某个位置，表中有{1} link和ORDER_HEADER (此处名称: STOCK_ORDER)和{2} link ORDER_HEADER和INVOICE_HEADER (名称此处: ORDER_INVOICE)的列，则实际上可以获得所需的信息。

-- each ORDER_HEADER is mapped to multiple product_ids
create table stock_order
as
select S.product_id, OH.id as oh_id  -- STOCK and ORDER_HEADER
from stock S, order_header OH ; -- cross join, we use all possible combinations here


select oh_id, product_id 
from stock_order 
order by OH_id 
;

PRODUCT_ID      OH_ID
---------- ----------
      3000       1000
      3000       1001
      3000       1002
      3001       1000
      3001       1001
      3001       1002
      3002       1000
      3002       1001
      3002       1002

9 rows selected.

-- each INVOICE_HEADER mapped to a single ORDER_HEADER
create table order_invoice ( order_id, invoice_id )
as
select 1000, 2000 from dual union all
select 1001, 2001 from dual union all
select 1002, 2002 from dual
; 

要查询，请确保编写了正确的联接条件(ON .)例如

-- example query. NOTICE: conditions in ON ...
select 
  S.product_id
, IH.customer_id  as cust_id
, OH.id           as OH_id
, IH.id           as IH_id
, extract( year from invoice_date )  as year_
, extract( month from invoice_date ) as month_
, invoice_date - order_date          as completion_time
from invoice_header IH
  join order_invoice OI on IH.id = OI.invoice_id     -- <- new "link" table
  join order_header OH  on OI.order_id = OH.id  
  join stock_order SO   on OH.id = SO.OH_id          -- <- new "link" table
  join stock S          on S.product_id = SO.product_id 
;

现在您可以添加组BY，并且只选择所需的列。再加上插入，你应该写些类似.

-- example avg_completion_time_fact table.  
create table avg_completion_time_fact (
  product_id number
, year_ number
, month_ number
, avg_completion_time number
) ;


insert into avg_completion_time_fact ( product_id, year_, month_, avg_completion_time )
select 
  S.product_id
, extract( year from invoice_date )  as year_
, extract( month from invoice_date ) as month_
, avg( invoice_date - order_date )   as avg_completion_time
from invoice_header IH
  join order_invoice OI on IH.id = OI.invoice_id
  join order_header OH  on OI.order_id = OH.id  
  join stock_order SO   on OH.id = SO.OH_id
  join stock S          on S.product_id = SO.product_id 
group by S.product_id, extract( year from invoice_date ), extract( month from invoice_date )  
;

AVG_COMPLETION_TIME_FACT表现在包含：

SQL> select * from avg_completion_time_fact order by product_id ;

PRODUCT_ID      YEAR_     MONTH_ AVG_COMPLETION_TIME
---------- ---------- ---------- -------------------
      3000       2018          3                  68
      3000       2018          4                 108
      3000       2018          2                  31
      3001       2018          3                  68
      3001       2018          2                  31
      3001       2018          4                 108
      3002       2018          3                  68
      3002       2018          4                 108
      3002       2018          2                  31

还不完全清楚数据库(或模式)的最终查询将是什么样子，因为我们不知道它包含的所有表的定义。但是，如果您应用这些技术并坚持示例的语法，您应该能够获得所需的结果。祝你好运！