Skip site navigation (1) Skip section navigation (2)

Re: Removing redundant itemsets

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To:
Cc: Allan Kamau <allank(at)sanbi(dot)ac(dot)za>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Removing redundant itemsets
Date: 2008-03-31 11:48:34
Message-ID: 47F0CF92.5080900@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-sql
> -- Find any `a' for which `item_from_a_is_in_b' is
> -- true for all items in `a'
> SELECT a_tid AS is_redundant, b_tid AS contained_by
> FROM (
>   -- For every item in every pair of purchases,
>   -- determine whether the item in purchase `a'
>   -- was also in purchase `b'.
>   SELECT
>     a.tid AS a_tid,
>     b.tid AS b_tid,
>     a.item AS item,
>     EXISTS(
>       -- Was this item from `a' also in the `b' purchase?
>       SELECT 1 FROM togo x WHERE x.tid = b.tid AND x.item = a.item
>     ) AS item_from_a_is_in_b
>   FROM togo a INNER JOIN togo b ON (a.tid <> b.tid)
>   GROUP BY a.tid, b.tid, a.item) AS item_containment
> GROUP BY a_tid, b_tid
> HAVING every(item_from_a_is_in_b);

That really should've been written as:

SELECT
  a.tid AS is_redundant,
  b.tid AS contained_by
FROM togo a INNER JOIN togo b ON (a.tid <> b.tid)
GROUP BY a.tid, b.tid
HAVING
  EVERY(EXISTS(
    SELECT 1 FROM togo x WHERE x.tid = b.tid AND x.item = a.item
  ));

... but I'm a bit of an idiot, and couldn't figure out why the
EVERY(EXISTS(subq)) wasn't working when testing it before.

Sorry for all the noise.

--
Craig Ringer

In response to

pgsql-sql by date

Next:From: Emi LuDate: 2008-03-31 14:48:27
Subject: drop table where tableName like 'backup_2007%' ?
Previous:From: Craig RingerDate: 2008-03-31 11:29:22
Subject: Re: Removing redundant itemsets

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group