Mam trzy stoły (Oracle):

sales_order
-------------
int so_key (pk)
int part_key (fk)
int condition_key (fk)
number unit_price
int qty_ordered
number unit_cost
date entry_date

quote
-------------
int q_key (pk) 
int part_key (fk)
int condition_key (fk)
number unit_price
int qty_quoted
date entry_date

stock
-------------
int stock_key (pk)
int part_key (fk)
int condition (fk)
int qty_available
number unit_cost

Wszystkie trzy mają kluczowe odniesienia do tych dwóch tabel:

part
-------------
int part_key (pk) 

condition
-------------
int condition_key (pk)

Piszę zapytanie, które będą agregować dane w wiersze zgrupowane przez part i condition. Nie mogę jednak wymyślić, jak grupować zarówno przez part, jak i condition. Oto kwerenda (funkcjonalna), którą mam tylko grupy part:

WITH
    ctePart_Quotes AS
    (
        SELECT q.part_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
    )
    , ctePart_Sales AS
    (
        SELECT so.part_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
    )
    , ctePart_Stock AS
    (
        SELECT stm.part_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
    )
SELECT p.part_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
  LEFT OUTER JOIN ctePart_Quotes quotes
    ON quotes.part_key = p.part_key
  LEFT OUTER JOIN ctePart_Sales sales
    ON sales.part_key = p.part_key
  LEFT OUTER JOIN ctePart_Stock part_stock
    ON part_stock.part_key = p.part_key

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 SALES_AMT_TOTAL > 10000

Ta zapytanie produkuje to wyjście (sumy zgrupowane przez Part_key):

part_key | total_available | inv_cost | sales_amt_total | ...
---------|-----------------|----------|-----------------| ...
     234 |              59 |  4923.90 |        29403.48 | ...
     185 |              21 |   192.64 |         9034.95 | ...
     102 |             102 |  8738.34 |        50382.20 | ...
...

Ale staram się zmodyfikować zapytanie, aby produkować to (sumy zgrupowane przez Part_key i Condion_key):

part_key | condition_key | total_available | inv_cost | sales_amt_total | ...
---------|---------------|-----------------|----------|-----------------| ...
     234 |             3 |              24 |  2360.50 |        16947.18 | ...
     234 |             7 |              35 |  2563.40 |        12456.30 | ...
...

Jak Ty to robisz?

Edytuj: W celu wyjaśnienia: Złożoność leży w: Jak jesteś warunkami w finale Wybierz? Ponieważ wybierasz FROM part, ale relacja condition przechodzi przez inne tabele (sales_order itp.). Więc trzeba będzie join przez każdą ze stolików ({x4}} itd.) Ale te dołączenia byłyby oddzielne kolumny.

Edytuj # 2: Ktoś podał dobry obraz modelu danych, który ilustruje (właściwy, uzasadniony) związek między częścią / warunkami, ale także subtelną złożonością w tym problemie:

enter image description here

0
ryvantage 3 czerwiec 2018, 05:43

3 odpowiedzi

Najlepsza odpowiedź

Sztuką jest najpierw utworzyć produkt kartezjański (tabela condition ma tylko ~ 30 rzędów), a może tłumić niechciane wiersze wyników później:

Może to wyglądać podgokalne, ale uniknie połączenia na COALESCE() d Keyfields, co może działać źle.


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
    ;
1
wildplasser 3 czerwiec 2018, 16:49

Głównym problemem wydaje się być twoim modelem danych. Konwertowanie tabeli "Opisy" twojego pytania do kodu DDL i odwracanie tego do modelu relacyjnego (za pomocą Oracle Datamodeler), znajdziemy coś takiego:

kod 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 ) ;

model relacyjny (Oracle SQL Developer Data Modeler)

enter image description here

Patrząc na model, staje się jasne, że każda część może mieć kilka warunków. W ten sposób może być konieczne (dla Ciebie), aby zdecydować, który stan się odwołujesz. To może nie być łatwe. Załóżmy, że mamy część (z Part_ey) 1000. Teraz możemy nagrywać 3 różne warunki i użyć określonego stanu dla każdej z 3 tabel wymienionych w zapytaniu.

-- 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 ;
/

Który z 3 warunku ma być używany do zapytania? Ciężko powiedzieć.

-- 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   

Cóż - moglibyśmy wybrać jeden z warunków, nie możemy. Jednak jeszcze więcej warunków może istnieć dla jednej i tej samej części ...

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 

Wniosek: Napraw model danych. (Wiemy, że czasami łatwiej powiedzieć niż zrobione ...) Wtedy ma sens, aby zrobić więcej pracy na zapytaniu.

__ Update __

Teraz, że wiemy, że nic nie można zrobić o tabelach i ograniczeniach, może następujące zapytania da ci punkt wyjścia. Nie mamy odpowiednich danych testowych, więc dodajmy kilka losowych wartości do tabel ...

-- 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 ;
/

cytat tabeli

-- 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 

stół sprzedawca_order

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

zapas tabeli

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     

Zakładając, że zarejestrowano tylko poprawne kombinacje części / warunku, możemy użyć pełnych połączeń zewnętrznych, aby uzyskać pierwsze zdjęcie.

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

Następnie możemy użyć funkcji analitycznych dla różnych obliczeń. Należy pamiętać, że nie używamy tutaj grupy, grupowanie jest wykonywane przez ... Partycje Q.Part_Key, Q.Condition_key ... (więcej o funkcjach analitycznych: Oracle Dokumentacja i przykłady Oto).

-- 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 ...
; 

Wynik

-- 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 
2
stefan 3 czerwiec 2018, 15:37

Może to działa dla ciebie:

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;

To także grupy condition_key w CTE. Następnie FULL JOIN s łącze razem za pomocą coalesce, aby skompensować wartości null part_key lub condition_key w pierwszych stołach (ale może nie ma żadnego Każda kombinacja part_key i condition_key, która jest obecna w jednym z tabel jest również obecna w odpowiednich dwóch innych tabelach.). Wynik jest następnie LEFT JOIN ed do part przy użyciu {{x10}}.

0
sticky bit 3 czerwiec 2018, 15:11