I enjoy when the Avent of Code comes around each year. I seem to make this a December (or sometimes New Year’s) resolution to get through them all, but life usually gets in the way. In any case, I decided to at least start this year and see how far I get.
Day 1 – First Puzzle
This is a simple one, and one that seems to lend itself to T-SQL. We have an input file that looks like:
+11
+9
-10
-5
etc.
This asks us to walk through the file, summing the values together and getting a new value. So the first row ends with 11. The next ends with 20 (11+9). The next is 10 (20-10), and so on. This feels like a simple calc, so let’s get it.
I wanted to load this with BULK LOAD, so I started with a table:
CREATE TABLE Day1(rawdata VARCHAR(20))
I know I’ll need to change this, but let’s make this easy. I use this command to now load my data.
BULK INSERT dbo.Day1 FROM 'C:\Users\way0u\Source\Repos\AdventofCode\2018\Day1\input.txt'
Once this is done, I’ll move on. Since I need to get this into some numeric values (this is a math problem), I’ll make another table.
CREATE TABLE Day1_a(frequency INT)
Now I move the data.
INSERT dbo.Day1_a
(
frequency
)
SELECT CAST(rawdata AS int)
FROM dbo.Day1
GO
That seems to work fine. How do I get the end result? Well, addition doesn’t matter here, so I can do this:
SELECT SUM(frequency) FROM dbo.Day1_a
GO
I get an answer, plug it in, and viola, I’m right. That feels good.
Day 1 – Second Puzzle
This one is a little harder. I’m supposed to find out the first time that the end result repeats it’s value. The test cases show this working as follows:
Value New result0 0
1 1
-1 0
If I walk through this, the 0 repeats. The other test cases show this, but with the large input set, I need to change a few things.
- I need to preserve ordering
- I need to process this row by row.
The second item doesn’t mean that I’m looping necessarily, but I need to calculate out the sums as I go and potentially repeat the list.
To get started, let me modify my Bulk Insert and table to keep the ordering. I created this table.
CREATE TABLE Day1b(datakey INT IDENTITY(1,1), rawdata VARCHAR(20))
I then ran BULK INSERT. I got this error:
I tried a number of items, but nothing really worked. This was a very, very annoying error, and the main solution I saw on Stack Overflow was to add a column to the input file, which I don’t want to do. I initially thought this was a problem with the encoding, but it’s really the identity.
The best solution was a lower down answer, which was to create a view without the identity.
CREATE VIEW vDay1b
AS
SELECT rawdata
FROM dbo.Day1b
GO
If I run the BULK INSERT to this view, it works fine.
OK. We’re moving and I have the data in order. Let’s move it to get the integer results we need.
CREATE TABLE Day1_2
( n INT, frequency INT)
GO
INSERT Day1_2
SELECT datakey,
CAST(rawdata AS INT)
FROM dbo.Day1b
If I run a quick query that does a SUM() OVER(), I get a series of results. I can see there are no duplicates here.
OK, this means I need to repeat the data. I can re-insert data into the table, but that feels inefficient. I ought to be able to group data together.
Let’s do this by selecting the data as a group, but adding a value to it. I can do that with a cross join. Here’s a short example that illustrates this. Suppose I have a table with the values “Broncos”, “Chiefs”, “Raiders”, “Chargers”, I get select data like this in groups.
With that in mind, let’s create a tally table and start to duplicate data. I have no idea how many times, but having done the Advent of Code before, I’m guessing 5 groups isn’t enough. Let’s start with 100 repeats.
One note, I do need to start with 0, so we’ll use a UNION to add the 0 row. We don’t want the 0 row repeated, so we don’t add that to the table.
