Ah, the title already reveals what the challenge will be about – and yes, I’m two days late, because it’s been weekend and weekend is family time, usually way from any computer device.
Our task is to get seat locations and IDs from the proprietary “FBFBBFFRLR” format – which is in reality a representation of binary numbers:
FBFBBFF = 7-bit number = 0101100 = 44RLR = 3-bit number = 101 = 5
The first step is to get the binary representation of the row and column data. I used a little helper function I wrote to get the input as sys.odcivarchar2list and into the aoc_day5_input table in the same way as I did with the previous inputs (downloading from local webserver).
with
seat_data as (
select
substr(line, 1, 7) seat_row,
substr(line, 8, 3) seat_col,
line orig
from aoc_day5_input
),
binary_data as (
select
translate(seat_row, 'FB', '01') b_seat_row,
translate(seat_col, 'LR', '01') b_seat_col
from seat_data
)
select * from binary_data
Now, we need to convert these binary-like strings into decimal numbers. Unfortunately, Oracle doesn’t provide a convenient function to do this, but it’s not very difficult to do it on our own: Take each char of b_seat_row and b_seat_col and multiply it with the according power of 2. For the first bit (which is the one with the highest value) it’s power(2,6), for the second power(2,5) etc.
After that, we calculated the ID as requested and can kindly ask the database to give us the max value.
with
seat_data as (
select
substr(line, 1, 7) seat_row,
substr(line, 8, 3) seat_col,
line orig
from aoc_day5_input
),
binary_data as (
select
translate(seat_row, 'FB', '01') b_seat_row,
translate(seat_col, 'LR', '01') b_seat_col
from seat_data
),
decimal_data as (
select
substr(b_seat_row, 1, 1)*power(2,6)
+ substr(b_seat_row, 2, 1)*power(2,5)
+ substr(b_seat_row, 3, 1)*power(2,4)
+ substr(b_seat_row, 4, 1)*power(2,3)
+ substr(b_seat_row, 5, 1)*power(2,2)
+ substr(b_seat_row, 6, 1)*power(2,1)
+ substr(b_seat_row, 7, 1)
dec_seat_row,
substr(b_seat_col, 1, 1)*power(2,2)
+ substr(b_seat_col, 2, 1)*power(2,1)
+ substr(b_seat_col, 3, 1)
dec_seat_col,
b_seat_row,
b_seat_col
from binary_data
),
data_with_id as (
select
dec_seat_row * 8 + dec_seat_col id,
dec_seat_col,
b_seat_row,
b_seat_col
from decimal_data
)
select max(id)
from data_with_id;
Part 2 now requires us to find our seat-ID. It’s the only ID missing in the list, so we can use the analtic functions lag and lead to analyze the gaps between the IDs and find the one where there is a gap of 2.
with
seat_data as (
select
substr(line, 1, 7) seat_row,
substr(line, 8, 3) seat_col,
line orig
from aoc_day5_input
),
binary_data as (
select
translate(seat_row, 'FB', '01') b_seat_row,
translate(seat_col, 'LR', '01') b_seat_col
from seat_data
),
decimal_data as (
select
substr(b_seat_row, 1, 1)*power(2,6)
+ substr(b_seat_row, 2, 1)*power(2,5)
+ substr(b_seat_row, 3, 1)*power(2,4)
+ substr(b_seat_row, 4, 1)*power(2,3)
+ substr(b_seat_row, 5, 1)*power(2,2)
+ substr(b_seat_row, 6, 1)*power(2,1)
+ substr(b_seat_row, 7, 1)
dec_seat_row,
substr(b_seat_col, 1, 1)*power(2,2)
+ substr(b_seat_col, 2, 1)*power(2,1)
+ substr(b_seat_col, 3, 1)
dec_seat_col,
b_seat_row,
b_seat_col
from binary_data
),
data_with_id as (
select
dec_seat_row * 8 + dec_seat_col id,
dec_seat_col,
b_seat_row,
b_seat_col
from decimal_data
),
id_analysis as (
select
id,
id-lag(id) over (order by id) gap_prev,
lead(id) over (order by id)-id gap_next
from data_with_id
)
select
prev.id+1 my_id
from id_analysis prev, id_analysis next
where prev.gap_next > 1
and next.gap_prev > 1
and next.id - prev.id = 2;
You can find this complete example in the newly created github-repository.
To get the sample data into Oracle LiveSQL is too annoying at the moment, so I won’t do it.
0 Comments