Re: Picking 25 samples of every domain

Le 09/10/2014 à 23:25, Gary Warner écrivait :

> I have a set of Postgres tables that are related to URLs found in
> email.  The tables are BIG.  40-50 million records per day.  We are
> using them for some research into tricks spammers use to confound
> blacklists.  When we parse the URLs, we pull out the "domain" portion of
> each URL and store it in a field called "top_domain".  The full URL is
> available as "link".
>
> Through various forms of randomization, customization, and wild-carding,
> a domain may have as many as 1 million URLs per day.  I am needing a
> query that would grab a sample number of URLs per domain (let's say 25
> for conversation) . . . something that in pseudo-code might look like
>
> for each top_domain in urltable do
>     select top_domain, link limit 25;

CREATE TYPE urltable_type AS (top_domain text, link text);

CREATE OR REPLACE FUNCTION urltable_sample(integer)
RETURNS SETOF urltable_type AS $$
DECLARE
    td text;
BEGIN
    FOR td IN SELECT DISTINCT top_domain FROM urltable
    LOOP
        RETURN QUERY EXECUTE
           'SELECT top_domain::text, link::text
            FROM urltable
            WHERE top_domain = ''' || td || '''
            LIMIT ' || $1;
    END LOOP;
    RETURN;
END
$$ LANGUAGE 'plpgsql' ;

SELECT top_domain, link FROM urltable_sample(25);

> Thoughts on the fastest way to do a query like that?

No but I'm not an expert.

--
Benoit Izac