Check out my general blog entry on Advent of Code 2021 for background and resources. In short, I’m working through the Advent of Code 2021 programming challenge using Db2 LUW SQL. This is the detailed entry for day 2.
AOC Day 2
My day 2 solutions are available in my GitHub repo.
Part 1 Problem Statement
— Day 2: Dive! —
Now, you need to figure out how to pilot this thing.It seems like the submarine can take a series of commands like forward 1, down 2, or up 3:
forward X increases the horizontal position by X units.
down X increases the depth by X units.
up X decreases the depth by X units.
Note that since you’re on a submarine, down and up affect your depth, and so they have the opposite result of what you might expect.The submarine seems to already have a planned course (your puzzle input). You should probably figure out where it’s going. For example:
forward 5
down 5
forward 8
up 3
down 8
forward 2
Your horizontal position and depth both start at 0. The steps above would then modify them as follows:forward 5 adds 5 to your horizontal position, a total of 5.
down 5 adds 5 to your depth, resulting in a value of 5.
forward 8 adds 8 to your horizontal position, a total of 13.
up 3 decreases your depth by 3, resulting in a value of 2.
down 8 adds 8 to your depth, resulting in a value of 10.
forward 2 adds 2 to your horizontal position, a total of 15.
After following these instructions, you would have a horizontal position of 15 and a depth of 10. (Multiplying these together produces 150.)Calculate the horizontal position and depth you would have after following the planned course. What do you get if you multiply your final horizontal position by your final depth?
General Commentary on the Problem
I first read part one while I was getting ready in the morning. Before I had really started my day, I realized that this was a pretty simple SQL statement, and that for this problem, I didn’t even need to know the sequence of operations. Experience from last year told me that part 2 was bound to be different and harder, so I made sure when I loaded the data to include a sequence so I would know the order of the rows. Remember that Db2 and most RDBMSes do not guarantee the order of the rows, so if you want to know the order in which they were, you need to note that order as you load them into the table
My Solution
My day 2 solutions are available in my GitHub repo.
First, I need to get the data into a table in the database. To do that, I create a table with this syntax:
create table aoc_day02 (
seq int generated by default as identity primary key
, direction varchar(18) not null
, units int not null);
create index ix_day02_direction on aoc_day02 (direction, units) allow reverse scans;I really wanted to do everything in Db2/SQL, but I learned that I could not use a space as the separator for a delimited file. This means that doing it purely in SQL would be some kind of cheat like loading it into the table, selecting it out and then parsing it to another table. I could do that, but instead, I decided to just use python’s easy parsing for csvs. This is the code I used to take the data as preseneted and write it out to a properly formatted csv:
with open("data/day02_input.del") as fin, open("data/day02_input.csv", 'w') as fout:
o=csv.writer(fout)
for line in fin:
o.writerow(line.split())
I then used this to import the data into the table:
delete from aoc_day02;
call admin_cmd ('import from /database/day02_input.csv of del
insert into aoc_day02 (direction, units)');Different way of Loading Data
My brain just wouldn’t let it go, so I coded a way to load the data without using python to convert the input to a csv first:
create table aoc_day02_stage (
seq int generated by default as identity primary key
, dir_and_units varchar(40) not null);
call admin_cmd ('import from /database/day02_input.del of del
insert into aoc_day02_stage (dir_and_units)');
delete from aoc_day02;
insert into aoc_day02 (
select seq
, SUBSTR(dir_and_units,1,INSTR(dir_and_units,' ')-1)
, SUBSTR(dir_and_units,INSTR(dir_and_units,' ')+1)
from aoc_day02_stage );
This uses an ELT methodology with a staging table on the space-separated, non-quoted input. There, now I can say I did the whole thing in SQL.
Query to Answer Part 1
Once I’ve done a quick verification that the data was inserted the way I intended, this is the SQL I came up with to generate the answer to part 1:
with m as (select direction, sum(units) as sum_units
from aoc_day02 group by direction)
select (select sum_units from m where direction='forward')
* ((select sum_units from m where direction='down') - (select sum_units from m where direction='up')) as answer
from sysibm.sysdummy1;I technically didn’t have to use a CTE here, but it made it a bit clearer in my mind
Part 2 Problem Statement
AOC Day 2 – you can only see part 2 after you’ve completed part 1
— Part Two —
Based on your calculations, the planned course doesn’t seem to make any sense. You find the submarine manual and discover that the process is actually slightly more complicated.In addition to horizontal position and depth, you’ll also need to track a third value, aim, which also starts at 0. The commands also mean something entirely different than you first thought:
down X increases your aim by X units.
up X decreases your aim by X units.
forward X does two things:
It increases your horizontal position by X units.
It increases your depth by your aim multiplied by X.
Again note that since you’re on a submarine, down and up do the opposite of what you might expect: “down” means aiming in the positive direction.Now, the above example does something different:
forward 5 adds 5 to your horizontal position, a total of 5. Because your aim is 0, your depth does not change.
down 5 adds 5 to your aim, resulting in a value of 5.
forward 8 adds 8 to your horizontal position, a total of 13. Because your aim is 5, your depth increases by 8*5=40.
up 3 decreases your aim by 3, resulting in a value of 2.
down 8 adds 8 to your aim, resulting in a value of 10.
forward 2 adds 2 to your horizontal position, a total of 15. Because your aim is 10, your depth increases by 2*10=20 to a total of 60.
After following these new instructions, you would have a horizontal position of 15 and a depth of 60. (Multiplying these produces 900.)Using this new interpretation of the commands, calculate the horizontal position and depth you would have after following the planned course. What do you get if you multiply your final horizontal position by your final depth?
General Commentary on the Problem
My first thought was that I was right that I would need that sequence to know the proper order of rows. Next, I thought that I’d have to use a stored procedure for this one because it is just too procedural. I wrote that stored proc in less than an hour on my lunch break. Later in the day, while working on totally unrelated things at work, I came up with an idea on how to do it without the stored procedure, so I have two solutions to this problem. It’s amazing to me how useful time away from the issue is for me on problems like this one.
My Solution – Stored Procedure
The reason that stored procedures are so attractive for this kind of work is that they’re procedural. While SQL tends to look at data as a whole and in groups, a procedural approach tends to handle each bit of data in order. Most programmers are used to a procedural approach, and as a result, don’t always think in the sets that give SQL its true power. Here’s the procedure I came up with, which gives the correct answer.
create or replace procedure db2inst1.sp_aoc_day02_part2 ( INOUT v_position INT
, INOUT v_depth INT
, INOUT v_aim INT
, OUT v_ans INT )
not deterministic
language sql
reads sql data
BEGIN
-- declare variables
declare v_aim int;
-- set isolation level
set current isolation = UR;
--set staring values
set v_position = coalesce(v_position,0);
set v_aim = coalesce(v_aim,0);
set v_depth = coalesce(v_depth,0);
-- query table and process row by row
for for_routine as c_aoc_day02_data cursor for
select direction, units from aoc_day02 order by seq
do
if direction = 'forward' THEN
set v_position = v_position + units;
set v_depth = v_depth + (v_aim * units);
end if;
if direction = 'down' THEN
set v_aim = v_aim + units;
end if;
if direction = 'up' THEN
set v_aim = v_aim - units;
end if;
end for;
set v_ans = v_position * v_depth;
END @The code here is very straight forward, looping through the rows in the table one at a time. Working with stored procedures in a Jupyter Noteboook is a special kind of hell, and I ended up resorting to using the very handy Db2 Extensions to make it easier.
To call that stored procedure, using the Db2 Extensions in my Jupyter Notebook, I used:
pos=0
dep=0
aim=0
ans=0
pos,dep,aim,ans=%sql -r call db2inst1.sp_aoc_day02_part2 (:pos,:dep,:aim,:ans)
display("The horizontal position is: "+pos)
display("The depth is: "+dep)
display("The answer is: "+ans)At a command line, that simplifies to just:
call db2inst1.sp_aoc_day02_part2(0,0,0,null)One of the nice things about how I coded this is that I can specify different starting positions, depths, or aims than simply 0. In the real world, that would be useful.
My Solution – Plain SQL
Running around the back of my head was the idea that I really could do this without a stored procedure. It feels so procedural that SQL PL (or PL/SQL or T-SQL) makes a lot of sense. But I decided to sink a good 4 hours into writing it without the stored procedure instead. The approach here was not obvious, but I did come up with the right answer using this query.
with moves as ( select seq
, direction
, units
, case when direction = 'forward' then units
else 0
end as horiz_move
, case when direction='down' then units
when direction='up' then -1*units
else 0
end as vert_move
from aoc_day02)
, pos_aim as (select horiz_move
, case when direction='forward' then units * sum(vert_move) over (order by seq) end as depth
from moves)
select sum(horiz_move) * sum(depth)
from pos_aim;Check out the Jupyter Notebook in my repo for another query that goes through it line by line instead of just spitting out the final answer.
Note that dividing the data into “sections” based on the “forward” rows was critical in better understanding the process, but when I coded the final solution, I realized that I didn’t need them for my solution, and eliminated it from this final answer. Again CTEs made it easier for me to walk through the work in chunks and logically separate the work being done.
Summary
Both parts of today’s challenge weren’t too difficult, though the second part was challenging to do without procedural SQL. It was fun to rise to the challenge, but that was a lot of time to devote on a weekday. I’m sure I won’t be keeping up the one-a-day pace.
