{"id":4588,"date":"2012-02-09T10:33:08","date_gmt":"2012-02-09T08:33:08","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4588"},"modified":"2012-02-09T10:33:08","modified_gmt":"2012-02-09T08:33:08","slug":"mysqlqueryscript-use-case-delete-all-but-top-n-records-per-group","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mysqlqueryscript-use-case-delete-all-but-top-n-records-per-group","title":{"rendered":"MySQL\/QueryScript use case: DELETE all but top N records per group"},"content":{"rendered":"

Some administrative tasks can be simplified by using common_schema\/QueryScript<\/a>. I’m collecting a bunch of these for documentation. Here’s one for example:<\/p>\n

The DBA\/developer has the task of retaining only top 3<\/strong> most populated countries per continent. That is, she has to DELETE 4th, 5th, 6th<\/strong>, … most populated counties in each continent.<\/p>\n

Is it possible to work out with a single query? Yes. But the query is not pretty. In fact, it is quite complicated, and either involves unintuitive subqueries, or unintuitive hacks<\/a>. A normal DBA would not want to write, neither maintain this kind of query, unless top-notch-geek, which is fine.<\/p>\n

Since this is a one time job, we just need to get it done. And common_schema<\/em>\/QueryScript provide with the intuitive solution: if we read our demand aloud, we realize we want to delete<\/strong> 4th, 5th, 6th<\/strong>, … populated countries for each<\/strong> continent.<\/p>\n

I present a solution made available by QueryScript, and discuss the ways in which the code overcomes limitations, or simplifies complexity:<\/p>\n

\n
var $num_countries_to_delete;\r\nforeach($continent, $num_countries: SELECT continent, COUNT(*) FROM world.Country GROUP BY continent)\r\n{\r\n  if ($num_countries > 3)\r\n  {\r\n    set $num_countries_to_delete := $num_countries - 3;\r\n    DELETE FROM world.Country WHERE Continent = $continent ORDER BY Population ASC LIMIT :$num_countries_to_delete;\r\n  }\r\n}<\/pre>\n<\/blockquote>\n
Discussion<\/h4>\n
The first thing that should be apparent from the above is that this is a programmatic<\/em> solution. Queries are declarative, which is why complex ones sometimes look incomprehensible. The above is more straightforward.<\/p>\n
The next thing to realize, which is a disclosure issue of some sorts, is that the above code is fine for a one time, or maintenance execution; but you wouldn’t want to be normally issuing this type of code against your database 10,000<\/strong> times a second.<\/p>\n
Now let’s break down the code to fragments:<\/p>\n
Discussion: variables<\/h4>\n
The $num_countries_to_delete<\/strong> is a script variable. It is local. It is reset to NULL<\/strong> upon declaration and destroyed when its visibility ends. But the real power<\/em> comes later, when it is expanded<\/a>. This is discussed last.<\/p>\n
Discussion: iteration<\/h4>\n
How would you iterate the continents using a stored routine? I personally think the syntax for server side cursors is overwhelmingly verbose. Declare a cursor, declare a continue handler, declare variables to grab values, open the cursor, start a loop, iteratively fetch the cursor (assign row values onto variables), oh, check up on the continue handler (programmatically<\/em> exit the loop if it fails), close the cursor.<\/p>\n
The foreach()<\/a> loop statement was developed to simplify all the above. Hey: just name your query, and the list of variables which should be assigned to, and do your thing in the following statement.<\/p>\n
Discussion: conditional branching<\/h4>\n
The standard SQL CASE<\/strong> statement, and the additional IF()<\/strong> statement are fine, and I use them a lot. But they are fine for SELECT<\/strong> queries, and only allow you to get<\/em> data. At best, you may invoke a function based on some condition, which can actually modify data.<\/p>\n
With QueryScript it’s as with your normal programming language: you can DELETE<\/strong> if some condition holds true, INSERT<\/strong> or SELECT<\/strong> or ALTER<\/strong> or whatever if false.<\/p>\n
In the above code there isn’t too much news. The same can be done with stored routines. However the if<\/strong> statement can also accept a query as a condition. One can ask: if (DELETE FROM … WHERE…)<\/strong>. The condition holds true only is the operation was successful (rows actually DELETEd, or INSERTed, or UPDATEed). This makes for a very tight integration between script and SQL.<\/p>\n
Discussion: variables and variable expansion<\/h4>\n
Script variables behave just as normal MySQL user defined variables (in fact, current internal implementation of script variables is by<\/em> user defined variables). Which means the set<\/strong> statement works for them just as normal.<\/p>\n
And here is where things become not-normal:<\/p>\n
Say we want to delete all but the 3 most populated countries in Europe. Wouldn’t we like to issue a DELETE FROM Country WHERE Continent = ‘Europe’ ORDER BY Population DESC LIMIT 3, 999999999<\/strong>? (The 9999999999 to resemble “infinite”, in poor man’s solution)<\/p>\n
But MySQL’s DELETE<\/strong> does not accept both limit & offset in the LIMIT<\/strong> clause. Just the limit part. Which is why we’re working the other way round: we find out the number of records we wish to purge and delete bottom up. But wait, here’s another problem:<\/p>\n
In MySQL, the LIMIT<\/strong> clause must accept a constant<\/em>. You can just DELETE FROM …. LIMIT @x<\/strong>. This makes for a syntax error. Bummer!<\/p>\n
If we don’t know ahead the number of records we wish to purge, how can we work both dynamically and correctly?<\/p>\n
\n
DELETE FROM world.Country WHERE Continent = $continent ORDER BY Population ASC LIMIT :$num_countries_to_delete;<\/pre>\n<\/blockquote>\n
The $num_countries_to_delete<\/strong> variable is expanded, via “:<\/strong>“. The :$num_countries_to_delete<\/strong> token is replaced in-place with the value contained by $num_countries_to_delete<\/strong>. MySQL never gets a variable in the LIMIT<\/strong> clause: by the time the query reaches MySQL, theres a constant<\/em> in place, and none is the wiser. But as far as we’re<\/em> concerned, we get a dynamic way of producing values to the LIMIT<\/strong> clause.<\/p>\n
LIMIT<\/strong> is not the only clause which expects constants. How about KILL<\/strong>? How about DDLs, such as CREATE TABLE<\/strong>? With variable expansion you can dynamically inject values onto such clauses, statements and commands, and get your self a dynamic script.<\/p>\n
Conclusion<\/h4>\n
This small code sample exposes much of QueryScript’s power. Throughout the months of development, I happened to use QueryScript code over and over on production, to realize how it can sometimes simplify very complex tasks into a mere 2-liner code. A code that any of my fellow programmers can understand, as well, without having to be SQL experts. And such which is executed within the server; no need for external languages, connectors, dependencies, packages etc.<\/p>\n","protected":false},"excerpt":{"rendered":"
Some administrative tasks can be simplified by using common_schema\/QueryScript. I’m collecting a bunch of these for documentation. Here’s one for example: The DBA\/developer has the task of retaining only top 3 most populated countries per continent. That is, she has to DELETE 4th, 5th, 6th, … most populated counties in each continent. Is it possible […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[67,76,50,21],"class_list":["post-4588","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-queryscript","tag-scripts","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1c0","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4588","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4588"}],"version-history":[{"count":22,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4588\/revisions"}],"predecessor-version":[{"id":4714,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4588\/revisions\/4714"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4588"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4588"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4588"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Enter variable expansion<\/a>. In the statement:<\/p>\n