-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path3-2.sql
277 lines (275 loc) · 22.2 KB
/
3-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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
with
input as (
select '.........426.............985.........40..........207............................841..463................................633........17.384...
531&......+..........125....-..312..........#........895......998..945.....@......$.....-...33...................353.....*........*.........
........................#......*...........21..727..*..../..-./.............545......80...................602......@..272.......743.........
...........558.577..........486...186*925.....*....483.883.1....286...................................625..................#474.....491.....
..............*.........243.................287................*............$....245............830.........793......#..........306..*......
238.685.................*................#.........%........807.........28.947.................*.....705.....*....573...500*781...#..496....
..................989..923.......713...539......917.................115..*.....-...........662.........-......413...........................
...........=......*..........886.*.........................442......*...........398........*.............%.............636...........%......
............976.413...498..../...266........796....................87.....................969.881..&.....815...........*.....279....415.....
......728*..............*..............129..........670...890.....................760...=.......@.832........227.....632.212*...............
..........257.....712.491....-41...........970........*....*...373........742.......*....330....................*.....................814...
....................*..................415......406..441..35..../[email protected]*206........
......687.........834......448...658...*..........&..................467*..................244.......+332....*..........35..............*...
.............453.......616..*...........609.............662......672.....56........661.602*...............549.661...................141..73.
.......699...*.........*...904..........................&..........*[email protected]..........$677.......473.....*.....
........*..670.........938.........267.......684...........514...211....875.........667..171.110&.426*......../...............*........29...
.....164..........................*....@786.*..........646.=.........43.......885....+...=.............641.....996...........117....28......
.........*921....150*....635..973............587...346*......771........&3....+....................=................................@.......
......962............821....*...*...........................$....174............391+......732*177...101.....146.............555.............
..........................117.965.........327.....................*../127.54.........354.....................*...37/.......&....56....656...
.335.747*967..*469.................................-.....$.450..560...........553...+.......327*..........215.........950..............-....
......................253%...........796.........267...946..*........&...583.-...........-......727.37........@....#....-.......337.........
.............-...............-.649.........-.................316..680......*.....982..398..................961......632...718......*..%.....
..........599.............876......700..617....344*138...611..........907........-...........................................*...291...209..
.860...........................*......*......................850........*..............=....761...721*804...............%...33..............
...*...........................61./....378........268....956..#[email protected]....$...............853%......826..................
.757..=422.........................427..........+....%....=...................#.../..............908................@.......................
.........................172.................464...........................-.....................*[email protected].....
............456.....462....*.....985................801......933..464..641.874............*189..42.........................430..596...=.....
..............*....%[email protected]....*23...........*..........*.............335.........889..557#....................*...........
....810.........65......382................#.532...............704......796...192....................@..........46...527......929...........
...=....%..................*433...........................925.............................................30.....*................617.......
.....792.......386.....................575...%859................................................................993...334*..........&......
..........657.&.....&....................*.....................296.+513.............36..........267........703.............186..............
......336...........443...#.....474......897.215*346.....100.....=...........+.812../......47.%....*444..&............128........-...196....
705.............980......932.......*122..................*...........305..817..*......424.../.40.........193.210.736.*..../....&.512..*.....
...%...............*.....................................326..615.......*.......472..*...........350*201.......*[email protected]...
...................907...303...........140*112.............../........93...875......339....361=.............730.............................
..............540...........*...................498.170.....................*....................#...757........241...............201.......
...359*42.....=.......309...561....528.....444.....*........570............................125/.379......707.......*........*285.$..........
............%.........................*770.............253..*....515..926..................................=........45.946..............*...
....155..573..103.24..............................@......*...179..*........275......................*...................*................134
....*............*......963...........444......801...656.796.....524.84#......*433.......997.....122.500....711.......447...................
..80.........992..........*............*...+..........*....................................*.................*...............$.....894...61.
........183..../.......492..955....+.222....519........373....=......304........151.........691..............655......223....37......*......
.........$...................&..221................859.....929.......*...398.......*......-......591.....&.............$..........134.......
................960......264........./..............#[email protected]........*...907......=791.........................
...........239....#.....=...........248.531...................@..................................696.......741...................50..174....
.....=......*............................*.....506*809.....796.....906.....=...........17*...........51...*.......................*..*......
...520...198...781....253..............957.222.....................*.....216..............513....959*....638.312........172.318......514....
...............*.........*239...............*.....%.....923.........852..............414+............../.....%...104.......*.............936
.....221.....116.....712...........201...346...475.........*...............&..............745....109..202...........*.......................
....*............957*.....$....353...............................171....468....%..........*.........*.........906..593..592#......&316......
.........232...........251....#.......%.......................$...*...........179..904.....365.....331..541..*..............................
...................................856..........412..-....844.877..871....*.........../.................*....416...........810*685..776.969.
[email protected].#...+...428.*[email protected].............*....
....%....826......@....%.688*.......983../....723.........892...................270.....234....*......443...970...........*.................
....................314................*.................................................*..=...588......*.....%........225...496*732.......
...344+........................644......413.997....553...................................87.282..........943........866.....................
..........*563....910......267..*..546........*.....*..................60.............+..........421*739.............*...251.169.605........
938.....52...........*761.*....762............335....842.....................44/.......528..........................77......*.......*201....
..........................597......$.....................896.....543.76.........................938./707...............8........982.........
...894..195.......310*986.....=54.854...553................#.......*...*..192...127..566*768..........................*....850...*..970.....
.....*..................................=...835...874...........663...65..*.....*.................*947..539.12...947.485.....*..749..*......
......34.378........$............#.........*......*......922...............947.598..880........723.....*....*[email protected]....
..165.............388..94.......863........132.184..973.*.......58.................*......226..............618.....................=........
.....*.............................................@.....154.............240..34.297.......*...........429.....625...............672........
......619...#............641*520.340.918............................../[email protected]..../.*.....164....789........984...
.............933.....................*.....838..240..400&..........288.......196..109............*.......749...513........*.......751...*...
.....709.....................881..905.........*....*.....................643..*[email protected]..........*....993
...............109...........*........368..123....98...501.....691..410-...*.126......................92........+..546...........219........
..................*......18..70..296.....*..............&..987..*........235............8.............././...828...*........................
.151.............551.153*..........*.....883...............*.......*833........497..287*..427.377%.428...910......448.598.....334..53..619..
..........589.......................723.........832.131..705.............................#............*.................@......*...*..*.....
..........*........794*686......790...............*..........192..............................970......612.546.98............942......941...
....223.686..............................547...665.......396..*.....210......408.322..839.......*..557......*...+.................521.......
.....*.......170...........166.....390..............100.....*..921....*.........*.....+........984....*..450..........639..../....*...296...
...372..........*760...266.*...600*......999.........*...906........................#....632.......469........250.......*...422..643....@...
..............-........#...911......494...*...355...159.......................848..676.................../.....*......35....................
......81$.....544..67...............*.....159....*............209.747*29........./..........812.........430.232...................199*587...
760.................*...#........331.................%...158...................#.....................29.................596...477...........
..........%./....922...388..970...............510...68......*....590....545...621...-..977.593..889...*.........................=..879..*389
....163.618.370..............................*...............946..................332....*.......&..&..837.658......................*.......
.............................835.....129...566.....690.237..............#.............$.....386....36......#......411.....52..733...783.....
.........670...978.84...........=......*.......991*....*....632.......957...52.......548.....&....................*.........-.*.............
....920....*..*.....*...............949..............482...*......812........-...413.....201................663.286.....-......424...296*630
........992...307...634.................716..287.........811.....+........4......*...413*.........896......*...........84.52................
.........................253.............................................*.....459..........926..*.......446..............$.......175.......
.........................*.......168-.....781*......344.........85......373..........224.....*..393.545........749%.643......-..%.....968...
...*776../145...&.....140..........................*........578*........................#..45..........*............/......596..639.........
415............393..................988...........878.22..............909..........284...............173...564*48.......33.............444..
........259..........%.....207-........*631...........*......669*...................*...........282......%........%.....*...................
.130................119.....................422......440.........461...-154........575.............$.=....329...180..576.....367............
...........618...............=.../..538$.......&............493*............729..........903.........82.......................*.............
.............*............541..124.........627........806*......666.703.146*...........*.%..............-.....................142........551
...#..3......660.....................*........*.......................*......./164...498..............872.621....991........................
174....*...............683..542*.....960.....782.....755............557../.....................#282.......#.........*409.103.......943......
........231.891...........*.....782.....................*992.............492......69.......610...............+...............557......*.....
............*...964.104.289..........853......691..830.......&.....................*...870.*.............77..189.310*...............583.....
....449....435.*....../........732+.=.........*...*.........238...161.....688......675..*...934......................891....................
...............21.......................239........430..........@....*.....@..376.......205......790........................73..334...598...
......863-................474...339.....*.................765.534.116...........*..................*[email protected]....%...../....
.262..........#......785....*..$....915..622....440$.&416.*.............143...34..45....515#....448...........268........*..................
...*..+.......844......*.142..................*...........666.......266...*......*...................236..............439......338.496...285
647...393...........236.......218..........819.779...+................./.........765...................*.......292.........$......*.........
..........................257....*.................274.........247...........951........................121........391....817.../...........
........742..................&....98...................628....*.................%.168..........539............................701...........
.......................659................................*.622...868.............=...713.357....*.298....917...781.........................
.....467....382..899......*819.....952*388..............684.......*.....................*.....558...%....%...............$..*.....60..615...
.....*.....*.......*............-..........604.................85.76....%893.866.&...784.....................$423.....334....935..*....*....
..154....424......583.......8....926...$............$441.....................*...50...............=....155.......................599..115...
.............%............-...........96.506..846.................152.....985..........589.......384...@...44...............560.............
.............161...%167..906..901.........*......&....92..+610.......*247.............*...................*...........330..*....92..........
....26.........................*...........523........*......................190...711.......*..........302.690.........*.986........714@...
........154.....................763..59..............305..560...726......79...*...........521.160..159................81....................
991.339*........-.........434..........*....................-..*.........*..626....................*..............692......+................
..............448.....96./...........782............353........949.....655...............752.522....905...964#......-.683..83...............
.......=..........127../.........$....................../.......................................*......................+.........671........
.783..14...........*...........746....271.38............666..................513..37.....=984.824....@....$..463.................*...154....
.................60..892..304............*....354*20.................99.........*..%.351............367..914.*....622...........310...*..194
.../.....339............*......318..........6.................329*...*..240.....76...*...45....*..............601......+.............211....
.700...../...925......616...........*56.570.$...970...751...........556..%..958.....50..*...667........63...........793.....................
...............................8.........*........*......*854................*.........304........324.....%..................*.....213%.....
832...............%....$.........572......303.....872.=.............822.....2...434........%[email protected]*.......571..........756
.....922........908.811......792....#.967..............432..............304......+........676......314........*..................364....#...
........................=.....*.........*.......39............127.........*........27..............+.........937.172.....532*......&........
....*.............156....626...667......151..../.......................776..846...*............562.....254$......../[email protected]..
..457.213.....403*....=......................#.......656.....546%..............*.......*866......+...................954.576...........%....
.........*..........227........364....752..977.231..*...........................739.610...................................*...730..#........
......838......645........$692.................*....170...............906........................149.....578..470.........981.....310.......
959...........*....447.................33.513...412..........784.......*....................365.....%....*..................................
...../......593......*....=..965..=907...*.............................482.....................*.......915..............327...529.....425...
......613.........%.490..971.-..............&542..............................695.......803.....917........446.....53...*........*455..$....
...............258..................+..303+..........................517....7*[email protected]...*............903..#...............
........................724...+....575........312...&.........................................*....*[email protected]..............
.....343.374.......$....*....675...............%...371......409.....502.928.135...482.384....195...59.............144..982........787.......
....*....*.......289..729..........990....................../.........+.......*..*......*...............*.@...........#.............+.......
....147...613.............*534.........938....882...740.518.....994..........800.222..933...836.......260..339.=...........628.$935...../...
...............726.....308.............%........*...../.+........=..../146.................*...................509..........*........593....
930.........................823..............994.................................100.....857.......................708.220.184..............' as input
),
rows_array as (
select
regexp_split_to_array(input, '\n') as row
from
input
),
rows as (
select
row_num,
rows_array.row[row_num] as row
from
rows_array,
generate_subscripts(rows_array.row, 1) as row_num
order by row_num
),
cols as (
select
row_num,
col_num,
substring(rows.row, col_num, 1) as cell
from
rows,
generate_series(1, length(rows.row)) as col_num
order by row_num, col_num
),
gears as (
select
row_num,
col_num
from
cols
where
cell = '*'
),
group_arrays as (
select
row_num,
regexp_split_to_array(rows.row, '\b') as groups
from
rows
),
groups as (
select
row_num,
group_num,
groups[group_num] as group
from
group_arrays,
generate_subscripts(groups, 1) as group_num
),
ends as (
select
row_num,
group_num,
sum(length(groups.group)) over (partition by row_num order by group_num rows between unbounded preceding and current row) as pos
from
groups
),
positions as (
select
row_num,
group_num,
1 + lag(pos, 1, 0) over (partition by row_num order by group_num) as start,
pos as end
from
ends
),
number_ranges as (
select
groups.row_num,
groups.group_num,
groups.group::int as number,
start,
end
from
groups join positions on (
groups.row_num = positions.row_num and
groups.group_num = positions.group_num
)
where
regexp_match(groups.group, '^\d+$') is not null
),
numbers as (
select
row_num,
group_num,
generate_series(start, end) as col_num,
number
from
number_ranges
),
gears_adjacent as (
select
gears.row_num as gear_row,
gears.col_num as gear_col,
row_num.row_num,
col_num.col_num
from
gears,
generate_series(row_num - 1, row_num + 1) as row_num,
generate_series(col_num - 1, col_num + 1) as col_num
),
numbers_near_gears as (
select
numbers.row_num,
numbers.group_num,
max(numbers.col_num) as col_num,
max(numbers.number) as number,
max(gears_adjacent.gear_row) as gear_row,
max(gears_adjacent.gear_col) as gear_col
from
numbers join gears_adjacent on (
gears_adjacent.row_num = numbers.row_num and
gears_adjacent.col_num = numbers.col_num
)
group by 1, 2
),
two_adjacent_gears as (
select
gear_row,
gear_col,
round(exp(sum(ln(number)))) as gear_ratio
from
numbers_near_gears
group by 1, 2
having
count(number) = 2
)
select
sum(gear_ratio) as answer
from
two_adjacent_gears