I present here a simplified version of the problem I encountered, I am working on Oracle 10.2:
I manage Christmas gifts for children, I have a list of children and a theoretical list of suitable gifts depending on the age and gender of the children. I also keep track of the gifts they actually received:
drop table actual_presents;
drop table kids;
drop table intended_presents;
create table kids (
name varchar2(30),
sex varchar2(1),
constraint pk_kids primary key(name),
constraint kids_sex_check check (sex in ('M', 'F'))
);
insert into kids(name, sex) values ('Alice','F');
insert into kids(name, sex) values ('Bob','M');
insert into kids(name, sex) values ('Carol','F');
insert into kids(name, sex) values ('Dave','M');
commit;
create table intended_presents (
age number,
sex varchar2(1),
present varchar2(30),
constraint pk_intended_presents primary key(age, sex, present),
constraint present_sex_check check (sex in ('M', 'F'))
);
insert into intended_presents(age, sex, present) values (5,'F', 'Doll');
insert into intended_presents(age, sex, present) values (6,'F', 'Poney');
insert into intended_presents(age, sex, present) values (5,'M', 'Car');
insert into intended_presents(age, sex, present) values (6,'M', 'Soldier');
commit;
create table actual_presents (
age number,
kid varchar2(30),
present varchar2(30),
constraint pk_actual_presents primary key(age, kid, present),
constraint fk_ap_k foreign key(kid) references kids(name)
);
insert into actual_presents(age, kid, present) values (5, 'Alice', 'Doll');
insert into actual_presents(age, kid, present) values (6, 'Alice', 'Poney');
insert into actual_presents(age, kid, present) values (5, 'Bob', 'Car');
insert into actual_presents(age, kid, present) values (6, 'Bob', 'Soldier');
insert into actual_presents(age, kid, present) values (5, 'Carol', 'Car');
insert into actual_presents(age, kid, present) values (6, 'Carol', 'Poney');
insert into actual_presents(age, kid, present) values (5, 'Dave', 'Soldier');
insert into actual_presents(age, kid, present) values (6, 'Dave', 'Car');
commit;
Now I want to draw a comparison between the expected and actual gifts for each child. While each child receives only 1 gift, it is easy:
select 'Basic query: double join, works well with 1 present per christmas' as remark from dual;
select k.name, k.sex, ip.age, ip.present as intended_present, ap.present as actual_present
from kids k, intended_presents ip, actual_presents ap
where k.name = ap.kid
and k.sex = ip.sex
and ap.age = ip.age
order by k.name, ip.age, ip.present;
But there are more complicated cases: we can plan several gifts for a given Christmas, or a child who needs to receive 2 gifts can actually receive 1 or 3.
insert into intended_presents(age, sex, present) values (5,'F', 'Drum');
insert into intended_presents(age, sex, present) values (6,'F', 'Orange');
insert into intended_presents(age, sex, present) values (5,'M', 'Itchy sweater');
insert into intended_presents(age, sex, present) values (6,'M', 'Coloring book');
insert into intended_presents(age, sex, present) values (7,'M', 'Trumpet');
insert into intended_presents(age, sex, present) values (7,'M', 'Apple');
insert into actual_presents(age, kid, present) values (5, 'Alice', 'Drum');
insert into actual_presents(age, kid, present) values (6, 'Alice', 'Orange');
insert into actual_presents(age, kid, present) values (5, 'Bob', 'Itchy sweater');
insert into actual_presents(age, kid, present) values (6, 'Bob', 'Coloring book');
insert into actual_presents(age, kid, present) values (5, 'Carol', 'Trumpet');
insert into actual_presents(age, kid, present) values (5, 'Dave', 'Coloring book');
insert into actual_presents(age, kid, present) values (6, 'Dave', 'Itchy sweater');
insert into actual_presents(age, kid, present) values (6, 'Dave', 'Puppy');
Now I find it impossible to make a query / view that returns the following expected result:
NAME S AGE ACTUAL_PRESENT INTENDED_PRESENT
------------------------------ - ---------- ------------------------------ ------------------------------
Alice F 5 Doll Doll
Alice F 5 Drum Drum
Alice F 6 Orange Orange
Alice F 6 Poney Poney
Bob M 5 Car Car
Bob M 5 Itchy sweater Itchy sweater
Bob M 6 Coloring book Coloring book
Bob M 6 Soldier Soldier
Carol F 5 Car Doll
Carol F 5 Trumpet Drum
Carol F 6 Orange
Carol F 6 Poney Poney
Dave M 5 Coloring book Car
Dave M 5 Soldier Itchy sweater
Dave M 6 Car Coloring book
Dave M 6 Itchy sweater Soldier
Dave M 6 Puppy
17 rows selected.
"" , , , (, 1 Alice 5 Doll, 1 Alice 5 Doll).
, , , , :
select 'score + partition by rank' as remark from dual;
select name, sex, age, actual_present, intended_present from (
select name, sex, age, actual_present, intended_present, row_number() over (partition by name, sex, age, actual_present order by score desc) as rank
from (
select
(case when sq.actual_present = ip.present then 1
else 0
end) as score,
sq.name as name, nvl(sq.sex, ip.sex) as sex, nvl(sq.age, ip.age) as age, sq.actual_present, ip.present as intended_present
from (
select k.name, k.sex, ap.age, ap.present as actual_present
from kids k,actual_presents ap
where k.name = ap.kid
) sq
full outer join intended_presents ip
on ip.age = sq.age
and ip.sex = sq.sex
where name is not null
)
) where rank = 1
order by name, age, actual_present;
NAME S AGE ACTUAL_PRESENT INTENDED_PRESENT
------------------------------ - ---------- ------------------------------ ------------------------------
Alice F 5 Doll Doll
Alice F 5 Drum Drum
Alice F 6 Orange Orange
Alice F 6 Poney Poney
Bob M 5 Car Car
Bob M 5 Itchy sweater Itchy sweater
Bob M 6 Coloring book Coloring book
Bob M 6 Soldier Soldier
Carol F 5 Car Doll
Carol F 5 Trumpet *Doll*
Carol F 6 Poney Poney
Dave M 5 Coloring book Car
Dave M 5 Soldier *Car*
Dave M 6 Car Coloring book
Dave M 6 Itchy sweater *Coloring book*
Dave M 6 Puppy *Coloring book*
16 rows selected.
present_index, , , :
-
- (1 - + 2 - 1 - + 2 - )
- (Dave - 6 - Puppy - NULL)
alter table intended_presents add present_index number;
update intended_presents set present_index = 1 where present in ('Doll', 'Car', 'Orange', 'Soldier', 'Apple');
update intended_presents set present_index = 2 where present_index is null;
commit;
alter table intended_presents drop constraint pk_intended_presents;
alter table intended_presents add constraint pk_intended_presents primary key(age, sex, present_index);
select * from intended_presents order by age, sex, present_index;
alter table actual_presents add present_index number;
update actual_presents set present_index = 1 where present in ('Doll', 'Car', 'Orange', 'Soldier');
update actual_presents set present_index = 3 where present in ('Puppy');
update actual_presents set present_index = 2 where present_index is null;
commit;
alter table actual_presents drop constraint pk_actual_presents;
alter table actual_presents add constraint pk_actual_presents primary key(age, kid, present_index);
select 'With present_index, we can use that in the join.' as remark from dual;
select k.name, k.sex, ip.age, ip.present as intended_present, ap.present as actual_present, ap.present_index
from kids k, intended_presents ip, actual_presents ap
where k.name = ap.kid
and k.sex = ip.sex
and ap.age = ip.age
and ap.present_index = ip.present_index
order by k.name, ip.age, ap.present_index;
NAME S AGE INTENDED_PRESENT ACTUAL_PRESENT PRESENT_INDEX
------------------------------ - ---------- ------------------------------ ------------------------------ -------------
Alice F 5 Doll Doll 1
Alice F 5 Drum Drum 2
Alice F 6 Orange Orange 1
Alice F 6 Poney Poney 2
Bob M 5 Car Car 1
Bob M 5 Itchy sweater Itchy sweater 2
Bob M 6 Soldier Soldier 1
Bob M 6 Coloring book Coloring book 2
Carol F 5 Doll Car 1
Carol F 5 Drum Trumpet 2
Carol F 6 Poney Poney 2
Dave M 5 Car Soldier 1
Dave M 5 Itchy sweater Coloring book 2
Dave M 6 Soldier Car 1
Dave M 6 Coloring book Itchy sweater 2
15 rows selected.
update actual_presents set present_index = 3 where kid = 'Alice' and present = 'Doll';
update actual_presents set present_index = 1 where kid = 'Alice' and present = 'Drum';
update actual_presents set present_index = 2 where kid = 'Alice' and present = 'Doll';
commit;
select k.name, k.sex, ip.age, ip.present as intended_present, ap.present as actual_present, ap.present_index
from kids k, intended_presents ip, actual_presents ap
where k.name = ap.kid
and k.sex = ip.sex
and ap.age = ip.age
and ap.present_index = ip.present_index
order by k.name, ip.age, ap.present_index;
NAME S AGE INTENDED_PRESENT ACTUAL_PRESENT PRESENT_INDEX
------------------------------ - ---------- ------------------------------ ------------------------------ -------------
Alice F 5 *Doll* *Drum* 1
Alice F 5 *Drum* *Doll* 2
Alice F 6 Orange Orange 1
Alice F 6 Poney Poney 2
Bob M 5 Car Car 1
Bob M 5 Itchy sweater Itchy sweater 2
Bob M 6 Soldier Soldier 1
Bob M 6 Coloring book Coloring book 2
Carol F 5 Doll Car 1
Carol F 5 Drum Trumpet 2
Carol F 6 Poney Poney 2
Dave M 5 Car Soldier 1
Dave M 5 Itchy sweater Coloring book 2
Dave M 6 Soldier Car 1
Dave M 6 Coloring book Itchy sweater 2
15 rows selected.
, , ( , ).
. - ?
***************** https://stackoverflow.com/users/3486433/serpiton *********** ******
, , , , actual = , .
WITH ActualNotIntended AS (
SELECT name, age, sex, present as actual
, Row_Number() OVER (Partition BY name, age ORDER BY present) as ID
FROM (SELECT k.name, a.age, k.sex, a.present
FROM kids k
INNER JOIN actual_presents a ON k.Name = a.kid
MINUS
SELECT k.name, i.age, k.sex, i.present
FROM kids k
INNER JOIN intended_presents i ON k.sex = i.sex) a
), IntendedNotActual AS (
SELECT name, age, sex, present as intended
, Row_Number() OVER (Partition BY name, age ORDER BY present) as ID
FROM (SELECT k.name, i.age, k.sex, i.present
FROM kids k
INNER JOIN intended_presents i ON k.sex = i.sex
INNER JOIN actual_presents a ON a.age = i.age AND k.sex = i.sex
MINUS
SELECT k.name, a.age, k.sex, a.present
FROM kids k
INNER JOIN actual_presents a ON k.Name = a.kid) i
), ActualIntended as(
SELECT k.name, a.age, k.sex, a.present as actual, i.present as intended
FROM kids k
JOIN actual_presents a ON k.name = a.kid
JOIN intended_presents i ON k.sex = i.sex AND i.age = a.age AND i.present = a.present
)
SELECT Coalesce(a.name, i.name) as name
, Coalesce(a.age, i.age) as age
, Coalesce(a.sex, i.sex) as sex
, actual
, intended
FROM ActualNotIntended a
FULL JOIN IntendedNotActual i
ON a.name = i.name AND a.age = i.age
AND a.sex = i.sex AND a.id = i.id
UNION
SELECT name, age, sex, actual, intended
FROM ActualIntended
ORDER by name, age, actual;
NAME AGE S ACTUAL INTENDED
Alice 5 F Doll Doll
Alice 5 F Drum Drum
Alice 6 F Orange Orange
Alice 6 F Poney Poney
Bob 5 M Car Car
Bob 5 M Itchy sweater Itchy sweater
Bob 6 M Coloring book Coloring book
Bob 6 M Soldier Soldier
Carol 5 F Car Doll
Carol 5 F Trumpet Drum
Carol 6 F Poney Poney
NAME AGE S ACTUAL INTENDED
Carol 6 F Orange
Dave 5 M Coloring book Car
Dave 5 M Soldier Itchy sweater
Dave 6 M Car Coloring book
Dave 6 M Itchy sweater Soldier
Dave 6 M Puppy
17 rows selected.
, , :
DROP TYPE V_RECORD_TABLE;
DROP TYPE V_RECORD;
CREATE OR REPLACE TYPE V_RECORD AS OBJECT (
NAME VARCHAR2(30),
SEX VARCHAR2(1),
AGE NUMBER,
ACTUAL_PRESENT VARCHAR2(30),
INTENDED_PRESENT VARCHAR2(30)
);
/
CREATE OR REPLACE TYPE V_RECORD_TABLE AS TABLE OF V_RECORD;
/
CREATE OR REPLACE PACKAGE INTENDED_VS_ACTUAL AS
FUNCTION GET_DATA RETURN V_RECORD_TABLE PIPELINED;
END INTENDED_VS_ACTUAL;
/
CREATE OR REPLACE PACKAGE BODY INTENDED_VS_ACTUAL AS
TYPE REF_CURSOR IS REF CURSOR;
TYPE LIST_OF_IP_REC IS TABLE OF VARCHAR2(30);
TYPE LIST_OF_IP_PER_AGE IS TABLE OF LIST_OF_IP_REC INDEX BY PLS_INTEGER;
TYPE LIST_OF_IP IS TABLE OF LIST_OF_IP_PER_AGE INDEX BY VARCHAR2(1);
TYPE EXPECTED_USED IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
FUNCTION TREAT_PENDING(PENDING_ACTUAL IN OUT LIST_OF_IP_REC, EXP_USED IN OUT EXPECTED_USED, CUR_KID IN VARCHAR2, CUR_AGE IN NUMBER, CUR_SEX VARCHAR2) RETURN V_RECORD_TABLE
IS
CUR_EXPECTED VARCHAR2(30) := '';
CUR_EXPECTED_SELECTED VARCHAR2(30) := '';
V_REC V_RECORD_TABLE := V_RECORD_TABLE();
BEGIN
IF PENDING_ACTUAL.LAST > 0 THEN
DBMS_OUTPUT.PUT_LINE('pending actual has '||PENDING_ACTUAL.LAST||' items');
FOR i IN PENDING_ACTUAL.FIRST .. PENDING_ACTUAL.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('pending actual '||PENDING_ACTUAL(i));
CUR_EXPECTED_SELECTED := NULL;
CUR_EXPECTED := EXP_USED.FIRST;
WHILE CUR_EXPECTED IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE('checking exp used item '||CUR_EXPECTED||' with value '||EXP_USED(CUR_EXPECTED));
IF EXP_USED(CUR_EXPECTED) = 0 THEN
EXP_USED(CUR_EXPECTED) := 1;
CUR_EXPECTED_SELECTED := CUR_EXPECTED;
END IF;
EXIT WHEN CUR_EXPECTED_SELECTED IS NOT NULL;
CUR_EXPECTED := EXP_USED.NEXT(CUR_EXPECTED);
END LOOP; -- loop on expected used
IF CUR_EXPECTED_SELECTED IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('returning row with actual '||PENDING_ACTUAL(i)||' exp used selected '||CUR_EXPECTED_SELECTED);
V_REC.EXTEND;
V_REC(V_REC.LAST) := V_RECORD(CUR_KID, CUR_SEX, CUR_AGE, PENDING_ACTUAL(i), CUR_EXPECTED_SELECTED);
ELSE
DBMS_OUTPUT.PUT_LINE('returning row with actual '||PENDING_ACTUAL(i)||' exp used selected NULL');
V_REC.EXTEND;
V_REC(V_REC.LAST) := V_RECORD(CUR_KID, CUR_SEX, CUR_AGE, PENDING_ACTUAL(i), NULL);
END IF;
END LOOP; -- loop on pending actual
END IF; -- if pending actual
/* remaining unused expected */
DBMS_OUTPUT.PUT_LINE('checking remaining unused expected');
CUR_EXPECTED := EXP_USED.FIRST;
WHILE CUR_EXPECTED IS NOT NULL
LOOP
-- DBMS_OUTPUT.PUT_LINE('checking exp used item '||CUR_EXPECTED||' with value '||EXP_USED(CUR_EXPECTED));
IF EXP_USED(CUR_EXPECTED) = 0 THEN
EXP_USED(CUR_EXPECTED) := 1;
V_REC.EXTEND;
V_REC(V_REC.LAST) := V_RECORD(CUR_KID, CUR_SEX, CUR_AGE, NULL, CUR_EXPECTED);
DBMS_OUTPUT.PUT_LINE('returning row with actual NULL exp used selected '||CUR_EXPECTED);
END IF;
CUR_EXPECTED := EXP_USED.NEXT(CUR_EXPECTED);
END LOOP; -- loop on expected used
RETURN V_REC;
END TREAT_PENDING;
FUNCTION GET_DATA RETURN V_RECORD_TABLE
PIPELINED
IS
V_REC V_RECORD_TABLE;
/* variables to fetch records */
CUR REF_CURSOR;
CUR_SEX VARCHAR2(1);
CUR_AGE NUMBER;
CUR_PRESENT VARCHAR2(30);
CUR_KID VARCHAR2(30);
IP_LIST LIST_OF_IP;
/* TREATMENT VARS */
PREV_KID VARCHAR2(30) := '';
PREV_AGE NUMBER := -1;
PREV_SEX VARCHAR2(1) := '';
i NUMBER;
PENDING_ACTUAL LIST_OF_IP_REC := LIST_OF_IP_REC();
EXP_USED EXPECTED_USED;
AP_FOUND BOOLEAN := FALSE;
BEGIN
/* first record intended presents by age, sex */
OPEN CUR FOR SELECT SEX, AGE, PRESENT FROM INTENDED_PRESENTS;
LOOP
FETCH cur INTO cur_sex, cur_age, cur_present;
EXIT WHEN CUR%NOTFOUND;
IF NOT IP_LIST.EXISTS(CUR_SEX) THEN
IP_LIST(CUR_SEX)(CUR_AGE) := LIST_OF_IP_REC();
END IF;
IF NOT IP_LIST(CUR_SEX).EXISTS(CUR_AGE) THEN
IP_LIST(CUR_SEX)(CUR_AGE) := LIST_OF_IP_REC();
END IF;
IP_LIST(CUR_SEX)(CUR_AGE).EXTEND;
IP_LIST(CUR_SEX)(CUR_AGE)(IP_LIST(CUR_SEX)(CUR_AGE).LAST) := CUR_PRESENT;
END LOOP;
CLOSE CUR;
DBMS_OUTPUT.PUT_LINE('intended presents recorded');
/* Treat actual presents */
OPEN CUR FOR SELECT K.NAME, K.SEX, AP.AGE, AP.PRESENT AS ACTUAL_PRESENT
FROM KIDS K, ACTUAL_PRESENTS AP
WHERE K.NAME = AP.KID ORDER BY K.NAME, AP.AGE;
LOOP
FETCH CUR INTO CUR_KID, CUR_SEX, CUR_AGE, CUR_PRESENT;
EXIT WHEN CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('********** actual present record kid '||CUR_KID||' sex '||CUR_SEX||' age '||CUR_AGE||' present '||CUR_PRESENT||' *********');
IF NOT IP_LIST.EXISTS(CUR_SEX) THEN
RAISE_APPLICATION_ERROR(-1, 'unknown sex '||CUR_SEX);
END IF;
IF NOT IP_LIST(CUR_SEX).EXISTS(CUR_AGE) THEN
RAISE_APPLICATION_ERROR(-2, 'unknown age for sex '||CUR_SEX||': '||CUR_AGE);
END IF;
IF (CUR_KID <> PREV_KID OR CUR_AGE <> PREV_AGE) THEN
DBMS_OUTPUT.PUT_LINE('switching kid or age, from '||PREV_KID||'/'||PREV_AGE||' to '||CUR_KID||'/'||CUR_AGE);
IF PREV_AGE > -1 THEN
/* here send rows from collected data */
DBMS_OUTPUT.PUT_LINE('Checking for pending records...');
V_REC := TREAT_PENDING(PENDING_ACTUAL, EXP_USED, PREV_KID, PREV_AGE, PREV_SEX);
DBMS_OUTPUT.PUT_LINE('Found records to return: '||V_REC.LAST);
IF V_REC.LAST IS NOT NULL THEN
FOR i IN V_REC.FIRST .. V_REC.LAST
LOOP
PIPE ROW ( V_REC(i));
END LOOP;
END IF;
END IF; -- if prev_age > 1 i.e. had something before, to ensure not executed on first row
/* here reinit variables */
PENDING_ACTUAL.DELETE;
EXP_USED.DELETE;
END IF; -- if switch kid / age
PREV_KID := CUR_KID;
PREV_AGE := CUR_AGE;
PREV_SEX := CUR_SEX;
/* Check whether actual present has been returned, store in pending list if not */
AP_FOUND := FALSE;
FOR i IN IP_LIST(CUR_SEX)(CUR_AGE).FIRST .. IP_LIST(CUR_SEX)(CUR_AGE).LAST
LOOP
IF CUR_PRESENT = IP_LIST(CUR_SEX)(CUR_AGE)(i) THEN
DBMS_OUTPUT.PUT_LINE('match found for '||CUR_PRESENT||' at index '||i||' marking '||IP_LIST(CUR_SEX)(CUR_AGE)(i)||' as used');
EXP_USED(IP_LIST(CUR_SEX)(CUR_AGE)(i)) := 1;
PIPE ROW ( V_RECORD( CUR_KID, CUR_SEX, CUR_AGE, CUR_PRESENT, IP_LIST(CUR_SEX)(CUR_AGE)(i) ) );
AP_FOUND := TRUE;
ELSE
IF NOT EXP_USED.EXISTS(IP_LIST(CUR_SEX)(CUR_AGE)(i)) THEN
EXP_USED(IP_LIST(CUR_SEX)(CUR_AGE)(i)) := 0;
DBMS_OUTPUT.PUT_LINE(IP_LIST(CUR_SEX)(CUR_AGE)(i)||' marked as not used');
END IF;
END IF;
END LOOP; -- loop of intended presents
IF NOT AP_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no match found for '||CUR_PRESENT||' in intended list. storing in pending_actual');
PENDING_ACTUAL.EXTEND;
PENDING_ACTUAL(PENDING_ACTUAL.LAST) := cur_present;
END IF;
END LOOP; -- loop on actual presents from query
V_REC := TREAT_PENDING(PENDING_ACTUAL, EXP_USED, CUR_KID, CUR_AGE, CUR_SEX); -- treat remaining data for last kid/age group of rows
IF V_REC.LAST IS NOT NULL THEN
FOR i IN V_REC.FIRST .. V_REC.LAST
LOOP
PIPE ROW ( V_REC(i));
END LOOP;
END IF;
RETURN;
END GET_DATA;
END INTENDED_VS_ACTUAL;
/
CREATE OR REPLACE VIEW V_INTENDED_VS_ACTUAL AS SELECT * FROM TABLE(INTENDED_VS_ACTUAL.GET_DATA());
SELECT * FROM V_INTENDED_VS_ACTUAL ORDER BY NAME, AGE, ACTUAL_PRESENT;
NAME S AGE ACTUAL_PRESENT INTENDED_PRESENT
------------------------------ - ---------- ------------------------------ ------------------------------
Alice F 5 Doll Doll
Alice F 5 Drum Drum
Alice F 6 Orange Orange
Alice F 6 Poney Poney
Bob M 5 Car Car
Bob M 5 Itchy sweater Itchy sweater
Bob M 6 Coloring book Coloring book
Bob M 6 Soldier Soldier
Carol F 5 Car Doll
Carol F 5 Trumpet Drum
Carol F 6 Poney Poney
Carol F 6 Orange
Dave M 5 Coloring book Car
Dave M 5 Soldier Itchy sweater
Dave M 6 Car Soldier
Dave M 6 Itchy sweater Coloring book
Dave M 6 Puppy