SQL and the (newly discovered) OVER operator

I have a tool that takes a range of ids and processes each document with an id within that range. I wanted to run this tool in parallel with each instance processing 100,000 documents. The problem is that document ids are not contiguous. There are gaps. So a simple criteria of document id + offset is not sufficient. I needed an SQL statement that detailed contiguous range of 100,000 document ids.

As with most ostensibly complicated SQL the solution is in creating a virtual table (ie inner select) from an existing table. My virtual table needed to be sorted on document id and a sequence number given for each row. To do that I needed to use this in the column selection of the virtual table.

ROW_NUMBER() OVER( ORDER BY document_id ) - 1 row_number

I had never heard of the OVER operator. It applies a function to a row of an ordered relation. So the ROW_NUMBER() function gives the first row a value 1, the second 2, etc. Since I would be grouping using division I need the row numbers to start from 0 and so the subtraction of 1.

Now that I have row numbers and rows ordered by document id I needed to find within groups of 100,000 rows the minimum and maximum document ids. I used a simple group by row_number / 100,000 to get the row groupings. Then used MIN(document_id) to get the group's minimum document id. Same for maximum document id. The final SQL is as follows and it runs fast enough for the 50M records I needed to use it with

SELECT
  FLOOR( y.row_number / 100000 ),
  MIN( y.document_id ),
  MAX( y.document_id ),
  COUNT( * )
FROM
  (
    SELECT
      x.*,
      ROW_NUMBER() OVER( ORDER BY document_id ) - 1 row_number
    FROM
      documents x
  ) y
GROUP BY
  FLOOR( y.row_number / 100000 )
ORDER BY
  MIN( y.document_id )

Now that I have the data I can use a bash script to read the data and produce a series of commands that are feed to xargs for parallel execution.

superglue + baby powder = gap filling putty

I bought some of Games Workshop's liquid green stuff and have been waiting to use it to clean up some plastic kits I am currently working on (well, next in line). Today, however, I found out about the superglue + baby powder putty. Dries rock hard and can be filed to shape.

Viking and Anglo-Saxon army painting saga concludes

If you had any interest in my 6mm Viking and Anglo-Saxon army painting saga then, well, first, you need to get out more and meet people, but, second, I sold them to a nice chap in Australia. Who then sent them to a nice chap in Scotland to paint them! Perhaps I should have done that.

What scale are scale buildings?

In response to the question "When gaming a city fight, do you all try to match the scale/size of the scenery to your miniature troops:"

A true scale creates overpowering buildings and the combatants are very greatly diminished. So a smaller scale for buildings with regard to their volume, but not their floor plan, removes this diminishment. I have not tried this, but you could use the Disney World trick of reducing the height of each floor above the ground floor. See Forced Perspective.

No one man or woman can fix this broken country

I plan to read none of your postings about 2016 Presidential candidates. Let's be rational, no one man or woman can fix this broken country. The US has run its course. Only a revolution to replace it will work. A revolution by those with the wisdom, knowledge, principles, and diplomacy of the Founding Fathers. Not the loudest. Not the richest.