Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
This is day 5 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1. This is going to be a crazy looping item, since it will move through the list, relative to the current spot, and incrementing items, I know this won’t be good in SQL.
Still. Worth solving.
Let’s load the data. I’ll use a table, but first, I’ll also add an identity. This will help me number instructions and figure out what the next one is.
CREATE TABLE Day5 ( InstructionKey INT IDENTITY(1,1) , Instruction INT) GO
There are issues with identities, but this is a great trick:
CREATE VIEW Day5V AS SELECT d.Instruction FROM dbo.Day5 AS d GO -- reusable code BULK INSERT Day5V FROM 'e:\Documents\GitHub\AdventofCode\2017\Day5\Input.txt' WITH (ROWTERMINATOR='\n') GO
Now I can get to work. Here’s the logic I used.
I wanted to first set some starting points. I have a counter (0 based, increment first). This determines how many times I jump around. I also need to track the current instruction key and the next key. And, of course, I need the instruction value.
The identity is the array index, or the instruction key (which place am I in). In this case, I’ll try to follow this logic.
Get the end (out of bounds, which is the max + 1). I loop until I get an jump outside of the end range. The loop does these items:
- Get the current instruction jump
- Set the next location to be the current key + the current jump
- Update the current jump to increment by 1
- Set the current instruction key to the next key
- loop
This seems to be what I need. On the test set, this worked fine. When I first set this up, I used this code:
DECLARE @end INT , @CurrentInstructionKey INT = 1 , @Instruction INT , @NextInstructionKey INT , @counter INT = 0; SELECT @end = MAX(InstructionKey) + 1 FROM dbo.Day5 AS d; -- SELECT [end] = @end; WHILE @CurrentInstructionKey < @end BEGIN SET @counter = @counter + 1; SELECT @Instruction = Instruction FROM Day5 WHERE InstructionKey = @CurrentInstructionKey; SELECT @NextInstructionKey = @CurrentInstructionKey + @Instruction; UPDATE dbo.Day5 SET Instruction = Instruction + 1 WHERE InstructionKey = @CurrentInstructionKey; SET @CurrentInstructionKey = @NextInstructionKey; --PRINT @CurrentInstruction END; SELECT Counter = @counter , [current] = @CurrentInstruction;
When I ran this, it chugged for some time. I bet in Python or C#, which would solve quickly with arrays. With updates, it’s slow. Like minutes slow for 1074 rows.
However, it worked.
Part II
In this part, this instructions are almost the same, but based on the current instruction value, we either increase or decrease the value. Not a big change. Our new update looks like:
UPDATE dbo.Day5 SET Instruction = Instruction + CASE WHEN @Instruction >= 3 THEN -1 ELSE 1 END WHERE InstructionKey = @CurrentInstructionKey;
This also works, albeit slowly. I left this around 5:30 and went to the gym.
One of the easier puzzles.