At least two ways are popular to acheve Relational Division.
1) Relational Division allowing residue.
devidend(x,y) / divisor(y) = x
Example 1-1: Nested NOT EXISTS
Code:
SELECT DISTINCT
x
FROM devidend a
WHERE NOT EXISTS
(SELECT 0
FROM divisor b
WHERE NOT EXISTS
(SELECT 0
FROM devidend c
WHERE c.x = a.x
AND c.y = b.y
)
)
;
Example 1-2: JOIN - HAVING - COUNT
Code:
SELECT a.x
FROM devidend a
INNER JOIN
divisor b
ON b.y = a.y
GROUP BY
a.x
HAVING
COUNT(*) = (SELECT COUNT(*) FROM divisor)
;
Here is another way by using LISTAGG
(and column names in a pattern string of a LIKE predicate which was newly supported in DB2 9.7 PF4 for LUW).
Example 1-3: LISTAGG
Code:
------------------------------ Commands Entered ------------------------------
WITH
/**************************************************
********** Start of test data 1. **********
**************************************************/
divisor(y) AS (
VALUES
('B') , ('C') , ('D') , ('F') , ('G')
)
, devidend(x , y) AS (
VALUES
-- x = 1: lack(D)
(1 , 'B') , (1 , 'C') , (1 , 'F') , (1 , 'G')
-- x = 2: Exact match
, (2 , 'B') , (2 , 'C') , (2 , 'D') , (2 , 'F') , (2 , 'G')
-- x = 3: surplus(A,E)
, (3 , 'B') , (3 , 'C') , (3 , 'D') , (3 , 'F') , (3 , 'G')
, (3 , 'A') , (3 , 'E')
-- x = 4: lack(G) and surplus(Go), same number of rows
, (4 , 'B') , (4 , 'C') , (4 , 'D') , (4 , 'F') , (4 , 'Go')
)
/**************************************************
********** End of test data 1. **********
**************************************************/
SELECT x
FROM devidend
GROUP BY
x
HAVING
LISTAGG( y || ':' )
WITHIN GROUP( ORDER BY y )
LIKE
(SELECT '%' ||
CAST(
LISTAGG( y || ':%' )
WITHIN GROUP( ORDER BY y )
AS VARCHAR(3999)
)
FROM divisor
)
;
------------------------------------------------------------------------------
X
-----------
2
3
2 record(s) selected.
2) Exact Division.
devidend(x,y) / divisor(y) = x
Example 2-1: LISTAGG
Code:
WITH
/**************************************************
********** Start of test data 1. **********
**************************************************/
...
...
/**************************************************
********** End of test data 1. **********
**************************************************/
SELECT x
FROM devidend
GROUP BY
x
HAVING
LISTAGG( y , ':' )
WITHIN GROUP( ORDER BY y )
=
(SELECT LISTAGG( y , ':' )
WITHIN GROUP( ORDER BY y )
FROM divisor
)
;
------------------------------------------------------------------------------
X
-----------
2
1 record(s) selected.
3) Relational Division allowing residue.
Return with y in devidend.
devidend(x,y) / divisor(y) = x,y
Example 3-1: Nested NOT EXISTS
Code:
------------------------------ Commands Entered ------------------------------
WITH
/**************************************************
********** Start of test data 1. **********
**************************************************/
...
...
/**************************************************
********** End of test data 1. **********
**************************************************/
SELECT x , y
FROM devidend a
WHERE NOT EXISTS
(SELECT 0
FROM divisor b
WHERE NOT EXISTS
(SELECT 0
FROM devidend c
WHERE c.x = a.x
AND c.y = b.y
)
)
ORDER BY
x , y
;
------------------------------------------------------------------------------
X Y
----------- --
2 B
2 C
2 D
2 F
2 G
3 A
3 B
3 C
3 D
3 E
3 F
3 G
12 record(s) selected.
Example 3-2: LISTAGG (Return with y in devidend and mark column('*': not in divisor)).
Code:
------------------------------ Commands Entered ------------------------------
WITH
/**************************************************
********** Start of test data 2. **********
**************************************************/
divisor(y) AS (
VALUES
('Base') , ('C') , ('D') , ('F') , ('G')
)
, devidend(x , y) AS (
VALUES
-- x = 1: lack(D)
(1 , 'Base') , (1 , 'C') , (1 , 'F') , (1 , 'G')
-- x = 2: Exact match
, (2 , 'Base') , (2 , 'C') , (2 , 'D') , (2 , 'F') , (2 , 'G')
-- x = 3: surplus(A,Bas,E)
, (3 , 'Base') , (3 , 'C') , (3 , 'D') , (3 , 'F') , (3 , 'G')
, (3 , 'A') , (3 , 'Bas') , (3 , 'E')
-- x = 4: lack(G) and surplus(Go), same number of rows
, (4 , 'Base') , (4 , 'C') , (4 , 'D') , (4 , 'F') , (4 , 'Go')
)
/**************************************************
********** End of test data 2. **********
**************************************************/
SELECT x , y
, LEFT('*' , 1 - SIGN( LOCATE(y || ':' , pattern) ) ) AS mark
FROM (SELECT x , y
, LISTAGG( y || ':' )
OVER( PARTITION BY x
ORDER BY y
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS list_y
FROM devidend
) a
INNER JOIN
(SELECT '%' ||
CAST(
LISTAGG( y || ':%' )
WITHIN GROUP( ORDER BY y )
AS VARCHAR(3999)
)
FROM divisor
) b(pattern)
ON a.list_y LIKE b.pattern
ORDER BY
x , y
;
------------------------------------------------------------------------------
X Y MARK
----------- ---- ----
2 Base
2 C
2 D
2 F
2 G
3 A *
3 Bas *
3 Base
3 C
3 D
3 E *
3 F
3 G
13 record(s) selected.
dBforums
>
Relational Division using LISTAGG
06-14-11, 07:52
Linear Mode
