Charles Hooper schrieb:
> On Jun 9, 9:09 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>> Charles Hooper schrieb:
>>> On Jun 8, 5:56 pm, Albright.Br..._at_gmail.com wrote:
>>>> I need to write a query that returns only the students that have read
>>>> all books by an author. I have these tables set up so far....
>>> Now, we need a way to first eliminate all rows where AUTHOR_NUM_BOOKS
>>> is not equal to MEMBER_NUM_BOOKS, and then return a list of names
>>> without duplicates. This can be accomplished by sliding the above SQL
>>> statement into an inline view:
>>> SELECT DISTINCT
>>> MEMBERNAME
>>> FROM
>>> (SELECT
>>> BC.MEMBERNAME,
>>> COUNT(DISTINCT B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR)
>>> AUTHOR_NUM_BOOKS,
>>> COUNT(BC.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME, B.AUTHOR)
>>> MEMBER_NUM_BOOKS
>>> FROM
>>> BOOKS B,
>>> BOOKCAMP BC
>>> WHERE
>>> B.AUTHOR='J.K Rowling'
>>> AND B.BOOKTITLE=BC.BOOKTITLE(+))
>>> WHERE
>>> AUTHOR_NUM_BOOKS=MEMBER_NUM_BOOKS;
>>> MEMBERNAME
>>> --------------------
>>> Chuck
>>> Mike
>>> Charles Hooper
>>> IT Manager/Oracle DBA
>>> K&M Machine-Fabricating, Inc.
>> Nice examples.
>> Just for fun, yet another one:
>>
>> SELECT MEMBERNAME
>> FROM (SELECT B.MEMBERNAME,CAST(COLLECT(booktitle) AS
>> SYS.dbms_debug_vc2coll) BOOKLIST
>> FROM BOOKCAMP B
>> GROUP BY MEMBERNAME) M,
>> (SELECT AUTHOR,CAST(COLLECT(booktitle) AS
>> SYS.dbms_debug_vc2coll) BOOKLIST
>> FROM BOOKS B
>> GROUP BY AUTHOR) A
>> WHERE A.BOOKLIST SUBMULTISET OF M.BOOKLIST
>> AND AUTHOR = 'J.K Rowling'
>> /
>>
>> Best regards
>>
>> Maxim
>
> Interesting, I am fairly certain that I would not have found that
> solution. The above solution seems to run a little slow on my system,
> but it is still interesting.
>
> Let's see if there is another way - caution, this might be
> inefficient:
> The starting point:
> SELECT
> B.AUTHOR,
> B.BOOKTITLE,
> ROW_NUMBER() OVER (PARTITION BY B.AUTHOR ORDER BY B.BOOKTITLE)
> BOOK_NUM,
> COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) BOOK_COUNT
> FROM
> BOOKS B
> WHERE
> AUTHOR='J.K Rowling';
>
> AUTHOR BOOKTITLE BOOK_NUM BOOK_COUNT
> -------------------- -------------------- ---------- ----------
> J.K Rowling Goblet 1 2
> J.K Rowling Prisoner 2 2
>
> Now, let's put the book list into a comma separated list:
> SELECT
> SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
> FROM
> (SELECT
> B.AUTHOR,
> B.BOOKTITLE,
> ROW_NUMBER() OVER (PARTITION BY B.AUTHOR ORDER BY B.BOOKTITLE)
> BOOK_NUM,
> COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) BOOK_COUNT
> FROM
> BOOKS B
> WHERE
> B.AUTHOR='J.K Rowling')
> WHERE
> BOOK_NUM=BOOK_COUNT
> CONNECT BY PRIOR
> BOOK_NUM=BOOK_NUM-1
> START WITH
> BOOK_NUM=1;
>
> BOOK_LIST
> ---------------
> Goblet,Prisoner
>
> We are now half way done. Prepare to do the same with the BOOKCAMP
> table:
> SELECT
> BC.MEMBERNAME,
> BC.BOOKTITLE,
> ROW_NUMBER() OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR ORDER BY
> B.BOOKTITLE) BOOK_NUM,
> COUNT(B.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR)
> BOOK_COUNT
> FROM
> BOOKS B,
> BOOKCAMP BC
> WHERE
> B.AUTHOR='J.K Rowling'
> AND B.BOOKTITLE=BC.BOOKTITLE;
>
> MEMBERNAME BOOKTITLE BOOK_NUM BOOK_COUNT
> -------------------- -------------------- ---------- ----------
> Chuck Goblet 1 2
> Chuck Prisoner 2 2
> Mary Goblet 1 1
> Mike Goblet 1 2
> Mike Prisoner 2 2
>
> Generate a comma separated list for each MEMBERNAME:
> SELECT
> MEMBERNAME,
> SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
> FROM
> (SELECT
> BC.MEMBERNAME,
> BC.BOOKTITLE,
> ROW_NUMBER() OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR ORDER BY
> B.BOOKTITLE) BOOK_NUM,
> COUNT(B.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR)
> BOOK_COUNT
> FROM
> BOOKS B,
> BOOKCAMP BC
> WHERE
> B.AUTHOR='J.K Rowling'
> AND B.BOOKTITLE=BC.BOOKTITLE)
> WHERE
> BOOK_NUM=BOOK_COUNT
> CONNECT BY PRIOR
> (MEMBERNAME||TO_CHAR(BOOK_NUM))=(MEMBERNAME||TO_CHAR(BOOK_NUM-1))
> START WITH
> BOOK_NUM=1;
>
> MEMBERNAME BOOK_LIST
> --------------------
> Chuck Goblet,Prisoner
> Mary Goblet
> Mike Goblet,Prisoner
>
> Now, let's put it all together to see where the author book list
> matches the MEMBERNAME book lists:
> SELECT
> BC.MEMBERNAME
> FROM
> (SELECT
> SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
> FROM
> (SELECT
> B.AUTHOR,
> B.BOOKTITLE,
> ROW_NUMBER() OVER (PARTITION BY B.AUTHOR ORDER BY B.BOOKTITLE)
> BOOK_NUM,
> COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) BOOK_COUNT
> FROM
> BOOKS B
> WHERE
> B.AUTHOR='J.K Rowling')
> WHERE
> BOOK_NUM=BOOK_COUNT
> CONNECT BY PRIOR
> BOOK_NUM=BOOK_NUM-1
> START WITH
> BOOK_NUM=1) B,
> (SELECT
> MEMBERNAME,
> SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
> FROM
> (SELECT
> BC.MEMBERNAME,
> BC.BOOKTITLE,
> ROW_NUMBER() OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR ORDER BY
> B.BOOKTITLE) BOOK_NUM,
> COUNT(B.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR)
> BOOK_COUNT
> FROM
> BOOKS B,
> BOOKCAMP BC
> WHERE
> B.AUTHOR='J.K Rowling'
> AND B.BOOKTITLE=BC.BOOKTITLE)
> WHERE
> BOOK_NUM=BOOK_COUNT
> CONNECT BY PRIOR
> (MEMBERNAME||TO_CHAR(BOOK_NUM))=(MEMBERNAME||TO_CHAR(BOOK_NUM-1))
> START WITH
> BOOK_NUM=1) BC
> WHERE
> B.BOOK_LIST=BC.BOOK_LIST;
>
> MEMBERNAME
> --------------------
> Chuck
> Mike
>
> Oddly, the above executes much faster than the CAST(COLLECT(booktitle)
> AS SYS.dbms_debug_vc2coll) solution. Maybe the dataset size should be
> increased, and the OP should post the performance results of each
> method to see how the first two solutions compare with the others. I
> think that it would be interesting to see if the
> CAST(COLLECT(booktitle) AS SYS.dbms_debug_vc2coll) method scales
> better than the other methods.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
>
This ties to the problem of set comparisons in sql, which i believe ( i
don't mean multiset operations) can't be effectively solved in pure sql.
Yet one approach (borrowed from
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12864646978683#166097700346936189)
SELECT DISTINCT MEMBERNAME
FROM (SELECT BC.*,
B.*,
SUM(B_RNK) OVER(PARTITION BY MEMBERNAME, AUTHOR) M_RNK
FROM BOOKCAMP BC,
(SELECT B.*, SUM(B_RNK) OVER(PARTITION BY AUTHOR) A_RNK
FROM (SELECT B.*,
POWER(2,
DENSE_RANK() OVER(ORDER BY
BOOKTITLE) - 1) B_RNK
FROM BOOKS B) B) B
WHERE BC.BOOKTITLE = B.BOOKTITLE)
WHERE AUTHOR = 'J.K Rowling'
AND A_RNK = M_RNK
/
however, it'll have its limitations too ( and on really big sets -
bigger than 1000 members) - i think, all suggested solutions will not
perform very well. For middle sized sets ( where the complete resultsets
will fit into pga) - the best performance i saw until now ( for similar
tasks) - has the model clause.
Best regards
Maxim
Received on Sat Jun 09 2007 - 10:24:04 CDT
