aoc_2022_day15p2.sql

DROP TABLE IF EXISTS day15;
CREATE TABLE `day15` (
  `signalX` int DEFAULT NULL,
  `signalY` int DEFAULT NULL,
  `beaconX` int DEFAULT NULL,
  `beaconY` int DEFAULT NULL,
  `radius` int GENERATED ALWAYS AS (ABS(`signalX` - `beaconX`) + ABS(`signalY` - `beaconY`))
);

LOAD DATA INFILE 'c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/day15.txt' INTO TABLE day15 
LINES STARTING BY 'Sensor at x=' (@line) SET 
    signalX = SUBSTRING_INDEX(@line, ',', 1), 
    signalY = substring_index(SUBSTRING_INDEX(@line, ':', 1), 'y=', -1), 
    beaconX = substring_index(substring_index(@line, 'x=', -1), ',', 1), 
    beaconY = substring_index(@line, 'y=', -1);

SELECT x, y, 4000000*x + y as part2
FROM (
    WITH 
        toggle AS (VALUES ROW(-1), ROW(1)), 
        diag (val) AS (SELECT signalX + signalY + column_0*radius FROM day15 JOIN toggle), 
        diagInv (val) AS (SELECT signalX - signalY + column_0*radius FROM day15 JOIN toggle) 
    SELECT 
        FLOOR((ds + dsi) / 2) AS x, 
        FLOOR(ds - (ds + dsi) / 2) AS y
    FROM 
        (SELECT 
            (SELECT SUM(diag.val) / 2 FROM diag JOIN diag AS partner ON partner.val IN (diag.val - 2, diag.val + 2)) AS ds, 
            (SELECT SUM(diagInv.val) / 2 FROM diagInv JOIN diagInv AS partner ON partner.val IN (diagINv.val - 2, diagInv.val + 2)) AS dsi
        ) dt
) dt;