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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Emi Lu | 2008-03-31 14:48:27 | drop table where tableName like 'backup_2007%' ? |
Previous Message | Craig Ringer | 2008-03-31 11:29:22 | Re: Removing redundant itemsets |