-
Notifications
You must be signed in to change notification settings - Fork 1
/
day_08_2.sql
79 lines (79 loc) · 4.26 KB
/
day_08_2.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
WITH coded_signal AS (
SELECT
src.id AS display_id,
coded_didgit,
coded_didgit_binary,
bit_count(coded_didgit_binary)::smallint AS bit_count
FROM aoc."2021_day_08" AS src
CROSS JOIN regexp_split_to_array(src.data, '\s\|\s') AS data_split
CROSS JOIN regexp_split_to_table(data_split[1], '\s') AS coded_didgit
CROSS JOIN LATERAL (
SELECT
CASE WHEN strpos(coded_didgit, 'a') > 0 THEN '1000000'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'b') > 0 THEN '0100000'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'c') > 0 THEN '0010000'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'd') > 0 THEN '0001000'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'e') > 0 THEN '0000100'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'f') > 0 THEN '0000010'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'g') > 0 THEN '0000001'::bit(7) ELSE '0000000'::bit(7) END AS coded_didgit_binary
) AS _
), decode_step_1 AS (
SELECT
display_id,
bit_and(coded_didgit_binary) FILTER (WHERE bit_count = 2) AS binary_1,
bit_and(coded_didgit_binary) FILTER (WHERE bit_count = 4) AS binary_4,
bit_and(coded_didgit_binary) FILTER (WHERE bit_count = 3) AS binary_7,
bit_and(coded_didgit_binary) FILTER (WHERE bit_count = 7) AS binary_8
FROM coded_signal
GROUP BY display_id
), decode_step_2 AS (
SELECT
decode_step_1.display_id,
ARRAY[
binary_1,
bit_and(coded_didgit_binary) FILTER (WHERE bit_count = 5 AND binary_4 | coded_didgit_binary = binary_8),
bit_and(coded_didgit_binary) FILTER (WHERE bit_count = 5 AND binary_1 | coded_didgit_binary = coded_didgit_binary),
binary_4,
bit_and(coded_didgit_binary) FILTER (WHERE bit_count = 5 AND NOT (binary_1 | coded_didgit_binary = coded_didgit_binary) AND NOT (binary_4 | coded_didgit_binary = binary_8)),
bit_and(coded_didgit_binary) FILTER (WHERE bit_count = 6 AND binary_1 | coded_didgit_binary = binary_8),
binary_7,
binary_8,
bit_and(coded_didgit_binary) FILTER (WHERE bit_count = 6 AND bit_count(binary_4 | coded_didgit_binary) = 6),
bit_and(coded_didgit_binary) FILTER (WHERE bit_count = 6 AND NOT (binary_1 | coded_didgit_binary = binary_8) AND NOT (bit_count(binary_4 | coded_didgit_binary) = 6))
] AS decodes_signals
FROM decode_step_1
INNER JOIN coded_signal
ON coded_signal.display_id = decode_step_1.display_id
AND coded_signal.bit_count IN (5, 6)
GROUP BY decode_step_1.display_id, binary_1, binary_4, binary_7, binary_8
), decode_mapping AS (
SELECT
display_id,
signal,
digit % 10 AS digit
FROM decode_step_2
CROSS JOIN unnest(decodes_signals) WITH ORDINALITY AS _(signal, digit)
), decoded_output AS (
SELECT
src.id AS display_id,
sum(digit * pow(10, 4-i)) AS value
FROM aoc."2021_day_08" AS src
CROSS JOIN regexp_split_to_array(src.data, '\s\|\s') AS data_split
CROSS JOIN regexp_split_to_table(data_split[2], '\s') WITH ORDINALITY AS s(coded_didgit, i)
CROSS JOIN LATERAL (
SELECT
CASE WHEN strpos(coded_didgit, 'a') > 0 THEN '1000000'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'b') > 0 THEN '0100000'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'c') > 0 THEN '0010000'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'd') > 0 THEN '0001000'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'e') > 0 THEN '0000100'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'f') > 0 THEN '0000010'::bit(7) ELSE '0000000'::bit(7) END |
CASE WHEN strpos(coded_didgit, 'g') > 0 THEN '0000001'::bit(7) ELSE '0000000'::bit(7) END AS coded_didgit_binary
) AS _
INNER JOIN decode_mapping
ON decode_mapping.display_id = src.id
AND decode_mapping.signal = coded_didgit_binary
GROUP BY 1
)
SELECT sum(value)
FROM decoded_output;