1

I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed.

I also have a function that returns me the next 20.000 (order by priority, last_started_execution limit 20000) tokens to search.

My problem is that sometimes the function returns me too much results from the same location and i need this 20.000 balanced by location.

For example: If i have 100.000 tokens in line to process, 5.000 from each location (20 locations in this case), I'll need the function get_next to return me 1.000 from each location, returning the 20.000 limit balanced.

The total line of tokens to process can be huge like millions, but I only need the next 20.000 as balanced as possible.

Also consider that I can have in line few tokens like 30.000 from location A and 2.000 from location B, in this case I will need 18.000 from location A and 2.000 from location B in a total of 20.000 being this the best possible result.

CREATE TABLE search_token (
    id SERIAL,
    search_token varchar(255),
    fk_location integer,
    priority integer,
    last_started_execution timestamp
);
  • Edit the question and: 1) add the full CREATE TABLE statement, 2) add a tag with the DBMS you are using, 3) clarify what you mean with "next". – ypercubeᵀᴹ Jun 10 '14 at 17:47
  • @ypercube Done. – Ivan De Sousa Paz Jun 10 '14 at 18:19
1

Something like this, then?

SELECT  fk_location, 
        search_token,
        priority,
        ROW_NUMBER() OVER ( 
            PARTITION BY fk_location 
            ORDER BY priority, last_started_execution )
          AS Ranking
FROM    search_token
ORDER BY Ranking, fk_location 
LIMIT 20000;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.