如何使用CTE按两个表分组

诀窍是首先创建一个carthesian产品(即 condition 表只有大约30行),并且 也许吧 稍后抑制不需要的结果行:

这看起来可能是次优的,但它将避免 COALESCE() D键域,可能会表现不好。

WITH
    ctePart_Quotes AS
    (
        SELECT q.part_key, q.condition_key
            , COUNT(*) AS quotes_count
            , SUM(q.unit_price * q.qty_quoted) AS quotes_amt_total
            , SUM(q.qty_quoted) AS quotes_qty_total
        FROM quote q
        WHERE q.entry_date BETWEEN TO_DATE('01-Jan-2011', 'dd-mm-yyyy') AND TO_DATE('31-Dec-2011', 'dd-mm-yyyy')
        GROUP BY q.part_key, q.condition_key
    )
    , ctePart_Sales AS
    (
        SELECT so.part_key, so.condition_key
            , COUNT(*) AS sales_count
            , SUM(so.unit_price * so.qty_ordered) AS sales_amt_total
            , SUM(so.qty_ordered) AS sales_qty_total
            , SUM(so.qty_ordered * so.unit_cost) AS cost_total
        FROM sales_order so
        WHERE so.entry_date BETWEEN TO_DATE('01-Jan-2011', 'dd-mm-yyyy') AND TO_DATE('31-Dec-2011', 'dd-mm-yyyy')
        GROUP BY so.part_key, so.condition_key
    )
    , ctePart_Stock AS
    (
        SELECT stm.part_key, stm.condition_key
            , SUM(stm.qty_available) AS total_available
            , SUM(stm.qty_available * stm.unit_cost) AS inv_cost
        FROM stock stm
        GROUP BY stm.part_key, stm.condition_key
    )

SELECT p.part_key,
       c.condition_key,
        part_stock.total_available,
        part_stock.inv_cost,
        sales.sales_amt_total,
        sales.sales_qty_total,
        sales.sales_count,
        sales.cost_total,
        quotes.quotes_amt_total,
        quotes.quotes_qty_total,
        quotes.quotes_count
  FROM parts p
  CROSS JOIN condition c -- <<-- Here
  LEFT OUTER JOIN ctePart_Quotes quotes
    ON quotes.part_key = p.part_key
    AND quotes.condition_key = c.condition_key -- <<-- Here
  LEFT OUTER JOIN ctePart_Sales sales
    ON sales.part_key = p.part_key
    AND sales.condition_key = c.condition_key -- <<-- Here
  LEFT OUTER JOIN ctePart_Stock part_stock
    ON part_stock.part_key = p.part_key
    AND part_stock.condition_key = c.condition_key -- <<-- Here

WHERE NOT(sales_amt_total IS NULL
        AND sales_qty_total IS NULL
        AND sales_count IS NULL
        AND cost_total IS NULL
        AND quotes_amt_total IS NULL
        AND quotes_qty_total IS NULL
        AND quotes_count IS NULL) -- <<-- And maybe Here, too
    AND SALES_AMT_TOTAL > 10000
    ;

这里的主要问题似乎是您的数据模型。将问题的“描述”表转换为DDL代码,并将其转换为关系模型(使用Oracle DataModeler),我们发现如下内容:

DDL代码

create table part ( part_key number primary key ) ;
create table condition ( condition_key number primary key ) ;

create table sales_order (
  so_key number generated always as identity start with 3000 primary key 
, part_key number references part 
, condition_key number references condition
, unit_price number
, qty_ordered number
, unit_cost number
, entry_date date ) ;

create table quote (
  q_key number generated always as identity start with 4000  primary key
, part_key number references part
, condition_key number references condition
, qty_quoted number
, unit_price number
, entry_date date );

create table stock (
  stock_key number generated always as identity start with 5000  primary key
, part_key number references part
, condition_key number references condition
, qty_available number 
, unit_cost number ) ;

关系模型(Oracle SQL Developer数据建模器)

从模型来看,很明显每个部分都有几个条件。因此,可能有必要(由你)决定,你指的是哪种情况。这可能不容易。假设我们有一个部件(带部件密钥)1000。现在,我们可以记录3个不同的条件,并为查询中提到的3个表中的每个表使用特定的条件。

-- one part, 3 conditions
begin
  insert into part ( part_key ) values ( 1000 ) ;
  insert into condition( condition_key ) values ( 2001 ) ;
  insert into condition( condition_key ) values ( 2002 ) ;
  insert into condition( condition_key ) values ( 2003 ) ;
  insert into sales_order ( part_key, condition_key ) values ( 1000, 2001 ) ;
  insert into quote ( part_key, condition_key ) values ( 1000, 2002 ) ;
  insert into stock ( part_key, condition_key ) values ( 1000, 2003 ) ;
end ;
/

三个条件中的哪一个应该用于查询?很难说。

-- not using WITH (subquery factoring) here - for clarity 
select
  P.part_key
, SO.condition_key
, Q.condition_key
, ST.condition_key
from part P
  join sales_order SO on SO.part_key = P.part_key 
  join quote Q        on Q.part_key  = P.part_key 
  join stock ST       on ST.part_key = P.part_key 
;

-- output
PART_KEY  CONDITION_KEY  CONDITION_KEY  CONDITION_KEY  
1000      2001           2002           2003   

好吧-我们可以选择其中一个条件,不是吗?然而,对于同一个零件,可能存在更多的条件…

begin
  insert into condition( condition_key ) values ( 2004 ) ;
  insert into condition( condition_key ) values ( 2005 ) ;
  insert into condition( condition_key ) values ( 2006 ) ;
  insert into sales_order ( part_key, condition_key ) values ( 1000, 2004 ) ;
  insert into quote ( part_key, condition_key ) values ( 1000, 2005 ) ;
  insert into stock ( part_key, condition_key ) values ( 1000, 2006 ) ;
end ;
/

-- Same query as above now gives us:
PART_KEY  CONDITION_KEY  CONDITION_KEY  CONDITION_KEY  
1000      2001           2005           2006           
1000      2001           2005           2003           
1000      2001           2002           2006           
1000      2001           2002           2003           
1000      2004           2005           2006           
1000      2004           2005           2003           
1000      2004           2002           2006           
1000      2004           2002           2003 

结论:修正你的数据模型。(我们知道这有时说起来容易做起来难……)那么,对您的查询做更多的工作是有意义的。

_更新__

既然我们知道不能对表和约束做任何事情,也许下面的查询将为您提供一个起点。我们没有正确的测试数据,所以我们只需向表中添加一些随机值…

-- PART and CONDITION -> 1000 integers each
begin
  for i in 1 .. 1000
  loop
    insert into part ( part_key ) values ( i ) ;
    insert into condition( condition_key ) values ( i ) ;
  end loop;
end ;
/

表格引用

-- 2 12s, 2 18s
SQL> select * from quote ;
Q_KEY  PART_KEY  CONDITION_KEY  QTY_QUOTED  UNIT_PRICE  ENTRY_DATE  
4000   10        100            55          500         01-MAY-11   
4001   12        120            55          500         01-MAY-11   
4002   12        37             56          501         01-MAY-11   
4003   14        140            55          500         01-MAY-11   
4004   15        46             56          501         01-MAY-11   
4005   16        160            55          500         01-MAY-11   
4006   18        180            55          500         01-MAY-11   
4007   18        55             56          501         01-MAY-11   
4008   20        200            55          500         01-MAY-11 

表销售订单

SQL> select * from sales_order ;

SO_KEY  PART_KEY  CONDITION_KEY  UNIT_PRICE  QTY_ORDERED  UNIT_COST  ENTRY_DATE  
3000    10        100            500         55           400        05-MAY-11   
3001    12        120            500         55           400        05-MAY-11   
3002    14        140            500         55           400        05-MAY-11   
3003    16        160            500         55           400        05-MAY-11   
3004    18        180            500         55           400        05-MAY-11   
3005    20        200            500         55           400        05-MAY-11

餐桌存货

SQL> select * from stock ;
STOCK_KEY  PART_KEY  CONDITION_KEY  QTY_AVAILABLE  UNIT_COST  
5000       10        100            10             400        
5001       12        120            10             400        
5002       14        140            10             400        
5003       14        100            12             402        
5004       16        160            10             400        
5005       18        180            10             400        
5006       20        200            10             400     

假设只记录了有效的部分/条件组合,我们可以使用完整的外部连接来获得第一张图片。

SQL> select 
  2    Q.part_key  q_part , Q.condition_key  q_cond
  3  , SO.part_key so_part, SO.condition_key so_cond
  4  , ST.part_key st_part, ST.condition_key st_cond
  5  from quote Q 
  6    full join sales_order SO
  7      on SO.part_key = Q.part_key and SO.condition_key = Q.condition_key
  8    full join stock ST  
  9      on ST.part_key = SO.part_key and ST.condition_key = SO.condition_key
 10  ;

-- result
Q_PART  Q_COND  SO_PART  SO_COND  ST_PART  ST_COND  
10      100     10       100      10       100      
12      120     12       120      12       120      
12      37      NULL     NULL     NULL     NULL     
14      140     14       140      14       140      
15      46      NULL     NULL     NULL     NULL     
16      160     16       160      16       160      
18      180     18       180      18       180      
18      55      NULL     NULL     NULL     NULL     
20      200     20       200      20       200      
NULL    NULL    NULL     NULL     14       100

然后,我们可以使用解析函数进行各种计算。注意,我们这里不使用GROUPBY,分组是通过…按q.part_键、q.condition_键进行分区…(有关分析函数的详细信息:Oracle documentation ,和示例 here )

-- Skeleton query ...
-- Note that you will have need to write over(...) several times.
-- Add a WHERE clause and conditions as required.
select
  Q.part_key as q_part, Q.condition_key as q_cond, 
  count( Q.part_key ) over ( partition by Q.part_key, Q.condition_key ) as q_count
-- 
-- Q example sums
-- , sum( Q.unit_price * Q.qty_quoted ) 
--     over ( partition by Q.part_key, Q.condition_key ) as qat -- quotes_amt_total
-- , sum( Q.qty_quoted ) 
--    over ( partition by Q.part_key, Q.condition_key ) as qqt -- quotes_qty_total
-- 
, SO.part_key as so_part, SO.condition_key as so_cond
, count( SO.part_key ) over ( partition by SO.part_key, SO.condition_key ) as so_count
-- 
-- SO sums here
--
, ST.part_key as st_part, ST.condition_key as st_cond
, count( ST.part_key ) over ( partition by ST.part_key, ST.condition_key ) as st_count
from sales_order SO 
  full join quote Q  
    on SO.part_key = Q.part_key and SO.condition_key = Q.condition_key
  full join stock ST  
    on ST.part_key = SO.part_key and ST.condition_key = SO.condition_key
-- where ...
; 

结果

-- output
Q_PART  Q_COND  Q_COUNT  SO_PART  SO_COND  SO_COUNT  ST_PART  ST_COND  ST_COUNT  
10      100     1        10       100      1         10       100      1         
12      37      1        NULL     NULL     0         NULL     NULL     0         
12      120     1        12       120      1         12       120      1         
14      140     1        14       140      1         14       140      1         
15      46      1        NULL     NULL     0         NULL     NULL     0         
16      160     1        16       160      1         16       160      1         
18      55      1        NULL     NULL     0         NULL     NULL     0         
18      180     1        18       180      1         18       180      1         
20      200     1        20       200      1         20       200      1         
NULL    NULL    0        NULL     NULL     0         14       100      1 

也许这对你有用:

WITH
    ctePart_Quotes AS
    (
        SELECT q.part_key,
               q.condition_key
            , COUNT(*) AS quotes_count
            , SUM(q.unit_price * q.qty_quoted) AS quotes_amt_total
            , SUM(q.qty_quoted) AS quotes_qty_total
        FROM quote q
        WHERE q.entry_date BETWEEN TO_DATE('01-Jan-2011', 'dd-mm-yyyy') AND TO_DATE('31-Dec-2011', 'dd-mm-yyyy')
        GROUP BY q.part_key,
                 q.condition_key
    )
    , ctePart_Sales AS
    (
        SELECT so.part_key,
               so.condition_key
            , COUNT(*) AS sales_count
            , SUM(so.unit_price * so.qty_ordered) AS sales_amt_total
            , SUM(so.qty_ordered) AS sales_qty_total
            , SUM(so.qty_ordered * so.unit_cost) AS cost_total
        FROM sales_order so
        WHERE so.entry_date BETWEEN TO_DATE('01-Jan-2011', 'dd-mm-yyyy') AND TO_DATE('31-Dec-2011', 'dd-mm-yyyy')
        GROUP BY so.part_key,
                 so.condition_key
    )
    , ctePart_Stock AS
    (
        SELECT stm.part_key,
               stm.condition_key
            , SUM(stm.qty_available) AS total_available
            , SUM(stm.qty_available * stm.unit_cost) AS inv_cost
        FROM stock stm
        GROUP BY stm.part_key,
                 stm.condition_key
    )
SELECT p.part_key,
        cte.condition_key,
        cte.total_available,
        cte.inv_cost,
        cte.sales_amt_total,
        cte.sales_qty_total,
        cte.sales_count,
        cte.cost_total,
        cte.quotes_amt_total,
        cte.quotes_qty_total,
        cte.quotes_count
  FROM parts p
  LEFT OUTER JOIN (SELECT coalesce(quotes.part_key, sales.part_key, part_stock.part_key) part_key,
                          coalesce(quotes.condition_key, sales.condition_key, part_stock.condition_key) condition_key,
                          quotes.quotes_count,
                          quotes.quotes_amt_total,
                          quotes.quotes_qty_total,
                          sales.sales_count,
                          sales.sales_amt_total,
                          sales.sales_qty_total,
                          sales.cost_total,
                          part_stock.total_available,
                          part_stock.inv_cost
                          FROM ctePart_Quotes quotes
                               FULL JOIN ctePart_Sales sales
                                         ON sales.part_key = quotes.part_key
                                            AND sales.condition_key = quotes.condition_key
                               FULL JOIN ctePart_Stock part_stock
                                         ON part_stock.part_key = sales.part_key
                                            AND part_stock.condition_key = sales.condition_key) cte
    ON cte.part_key = p.part_key
WHERE NOT(sales_amt_total IS NULL
        AND cte.sales_qty_total IS NULL
        AND cte.sales_count IS NULL
        AND cte.cost_total IS NULL
        AND cte.quotes_amt_total IS NULL
        AND cte.quotes_qty_total IS NULL
        AND cte.quotes_count IS NULL)
    AND SALES_AMT_TOTAL > 10000;

它也按 condition_key 在CTES。那么它 FULL JOIN CTE一起使用 coalesce 补偿 null 价值观 part_key 或 条件键 在第一个表中(但可能没有,如果 部件密钥 和 条件键 ,在其中一个表中出现的也出现在相应的其他两个表中。)结果就是 LEFT JOIN 埃德 part 使用 部件密钥 .