Thread: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
From
Steve Midgley
Date:
I think what you want is related to this post on how to create a FIFO queue in Postgres:<br /><br /><a eudora="autourl" href="http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html"> http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html</a><br /><br/> The major difference is that you want a FIFO queue per user_id, so the triggering code would want to bump old recordsaggregating on user_id to calculate the "limit" subquery. His original code is this:<br /><br /><br /><pre> DELETEFROM q WHERE id NOT IN (SELECT id FROM q ORDER BY id DESC LIMIT maxrows); </pre>And that subquery is where (I think!) you'd want to add "where user_id = [user_id]" - I'm not sure how you'll passuser_id into this function, maybe someone else can help with that?<br /><br /> Hopefully this is useful?<br /><br />Steve<br /><br /> At 06:24 AM 1/8/2008, pgsql-sql-owner@postgresql.org wrote:<br /><blockquote cite="" class="cite" type="cite">Date:Tue, 8 Jan 2008 14:24:22 +0000<br /> From: "Jamie Tufnell" <diesql@googlemail.com><br /> To: pgsql-sql@postgresql.org<br/> Subject: How to keep at-most N rows per group? periodic DELETEs or constraints or..?<br />Message-ID: <b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.com><br /><br /> Hi,<br /><br /> I have a tablethat stores per-user histories of recently viewed items<br /> and I'd like to limit the amount of history items to <=50 per user.<br /> I'm considering doing this with a query run from cron every so often<br /> but I'm not happy withwhat I've come up with so far, and since it's a<br /> quite active table I thought I'd ask here to see if there's a more<br/> efficient way.<br /><br /> Right now the table structure is as follows...<br /><br /> user_item_history: id (PK),user_id (FK), item_id (FK), timestamp<br /><br /> For user_ids that have more than 50 rows, I want to keep the most<br/> recent 50 and delete the rest.<br /><br /> The most obvious way of doing this for me is:<br /><br /> --<br /> --Get the user_ids with 50 or more history entries like this<br /> --<br /> SELECT user_id, count(*)<br /> FROM user_scene_history<br/> GROUP BY user_id<br /> HAVING count(*) > 50;<br /><br /> --<br /> -- Then iterate the ids above(_user_id)<br /> --<br /> DELETE FROM user_scene_history<br /> WHERE user_id = _user_id AND id NOT IN (<br /> SELECTid FROM user_scene_history<br /> WHERE user_id = _user_id<br /> ORDER BY timestamp DESC<br /> LIMIT 50);<br/><br /> I've left out the simple logic tying the above two queries together<br /> for clarity..<br /><br /> I haven'tactually tested this but while I assume it would work I<br /> imagine there is a neater and possibly more efficientway of attacking<br /> this. I'm also open to different approaches of limiting the user's<br /> history too ...perhaps with table constraints so they can simply<br /> never exceed 50 entries? But I'm not sure how to do this..<br/><br /> Any help would be greatly appreciated..<br /><br /> Thanks,<br /> Jamie</blockquote>