TL;DR at the bottom.
I have inherited a rather poorly-designed table which holds a log of requests against a third-party webservice. This table contains a timestamp, two fields named metadata_1 and metadata_2 which allow you to identify the user, and a very long VARCHAR2 field named TEXT.
This TEXT field holds the actual request information, represented as a comma-separated list of values. Somewhere buried in this list are two relevant bits of information, which I'll call "request type" and "request key." I am trying to figure out, for each user, the ratio of unsuccessful requests to total requests per day and request key. This value is then to be compared against the global error rate for that day, to identify devices that have trouble talking to the webservice. (The metadata only identifies the user, so getting to their devices involves a little additional effort.) A non-successful request has a field I'll call "error type" which is inserted before the request type.
As you probably guessed from that last sentence, the format of this comma-separated list of values is not consistent. In particular, the position of the KEY field in the comma-separated list depends on the request type, whose position in turn depends on whether the request was successful. All of these fields are variable-length.
So a TEXT field could look like this:
"2017-04-05T07:21:00.569Z,html_error:403,get_status,80,asdf2k,1,0,KEY_123,hunter2"
"2017-04-05T07:21:01.529Z,html_error:403,get_status,80,asdf2k,1,0,KEY_123,hunter2"
But it could also look like this:
"2017-04-05T07:23:46.459Z,send_events,80,qwert-8,2,1,KEY_123,foobar,1,1,false,114,11838"
My question is: How would you tackle this problem? There is a query that works in the test environment, reproduced below, but its performance is extremely poor. There has got to be a better way to do this. Assume that this all has to be done in a single query and that I have no influence on either the environment or the database design. (Note that there's some additional filtering going on here. Anything else that seems odd is likely an error I made during the anonymization. This is more about the strategy I'm using - I'm not asking you to write the query for me.)
Desired result: For each user, day and request key, the number of successful and unsuccessful requests, compared to the ratio of successful requests for that day.
Current result: As described, but with unacceptable performance.
select a.*, b.success_rate_day from (select device.serial_id, rql.date, rql.KEY, rql.requests_ok, rql.requests_error
from device, user,
(select request_log.meta_1, request_log.meta_2, to_char(request_log.created_timestamp, 'DDD') AS date,
(select count(b.TEXT)
FROM request_log b
where b.meta_1 = request_log.meta_1
and b.meta_2 = request_log.meta_2
and b.text NOT LIKE '%error%'
and to_char(request_log.created_timestamp, 'DDD') = to_char(b.created_timestamp, 'DDD')
and (CASE
WHEN b.TEXT LIKE '%html_error%' THEN SUBSTR(b.TEXT, INSTR(b.TEXT, ';', -1, 2)+1, (INSTR(b.TEXT, ';', -1, 1)-INSTR(b.TEXT, ';', -1, 2)-1))
WHEN b.TEXT LIKE '%get_status%' THEN SUBSTR(b.TEXT, INSTR(b.TEXT, ';', -1, 4)+1, (INSTR(b.TEXT, ';', -1, 3)-INSTR(b.TEXT, ';', -1, 4)-1))
WHEN b.TEXT LIKE '%send_events%' THEN SUBSTR(b.TEXT, INSTR(b.TEXT, ';', 1, 6)+1, INSTR(b.TEXT, ';', 1, 7)-INSTR(b.TEXT, ';', 1, 6)-1)
ELSE 'Error'
END) = (CASE
WHEN request_log.TEXT LIKE '%html_error%' THEN SUBSTR(request_log.TEXT, INSTR(request_log.TEXT, ';', -1, 2)+1, (INSTR(request_log.TEXT, ';', -1, 1)-INSTR(request_log.TEXT, ';', -1, 2)-1))
WHEN request_log.TEXT LIKE '%get_status%' THEN SUBSTR(request_log.TEXT, INSTR(request_log.TEXT, ';', -1, 4)+1, (INSTR(request_log.TEXT, ';', -1, 3)-INSTR(request_log.TEXT, ';', -1, 4)-1))
WHEN request_log.TEXT LIKE '%send_events%' THEN SUBSTR(request_log.TEXT, INSTR(request_log.TEXT, ';', 1, 6)+1, INSTR(request_log.TEXT, ';', 1, 7)-INSTR(request_log.TEXT, ';', 1, 6)-1)
ELSE 'Error'
END)
) AS requests_ok,
(select count(b.TEXT)
FROM request_log b
where b.meta_1 = request_log.meta_1
and b.meta_2 = request_log.meta_2
and b.text LIKE '%error%'
and to_char(request_log.created_timestamp, 'DDD') = to_char(b.created_timestamp, 'DDD')
and (CASE
WHEN b.TEXT LIKE '%html_error%' THEN SUBSTR(b.TEXT, INSTR(b.TEXT, ';', -1, 2)+1, (INSTR(b.TEXT, ';', -1, 1)-INSTR(b.TEXT, ';', -1, 2)-1))
WHEN b.TEXT LIKE '%get_status%' THEN SUBSTR(b.TEXT, INSTR(b.TEXT, ';', -1, 4)+1, (INSTR(b.TEXT, ';', -1, 3)-INSTR(b.TEXT, ';', -1, 4)-1))
WHEN b.TEXT LIKE '%send_events%' THEN SUBSTR(b.TEXT, INSTR(b.TEXT, ';', 1, 6)+1, INSTR(b.TEXT, ';', 1, 7)-INSTR(b.TEXT, ';', 1, 6)-1)
ELSE 'Error'
END) = (CASE
WHEN request_log.TEXT LIKE '%html_error%' THEN SUBSTR(request_log.TEXT, INSTR(request_log.TEXT, ';', -1, 2)+1, (INSTR(request_log.TEXT, ';', -1, 1)-INSTR(request_log.TEXT, ';', -1, 2)-1))
WHEN request_log.TEXT LIKE '%get_status%' THEN SUBSTR(request_log.TEXT, INSTR(request_log.TEXT, ';', -1, 4)+1, (INSTR(request_log.TEXT, ';', -1, 3)-INSTR(request_log.TEXT, ';', -1, 4)-1))
WHEN request_log.TEXT LIKE '%send_events%' THEN SUBSTR(request_log.TEXT, INSTR(request_log.TEXT, ';', 1, 6)+1, INSTR(request_log.TEXT, ';', 1, 7)-INSTR(request_log.TEXT, ';', 1, 6)-1)
ELSE 'Error'
END)
) AS requests_error,
(CASE
WHEN TEXT LIKE '%html_error%' THEN SUBSTR(TEXT, INSTR(TEXT, ';', -1, 2)+1, (INSTR(TEXT, ';', -1, 1)-INSTR(TEXT, ';', -1, 2)-1))
WHEN TEXT LIKE '%get_status%' THEN SUBSTR(TEXT, INSTR(TEXT, ';', -1, 4)+1, (INSTR(TEXT, ';', -1, 3)-INSTR(TEXT, ';', -1, 4)-1))
WHEN TEXT LIKE '%send_events%' THEN SUBSTR(TEXT, INSTR(TEXT, ';', 1, 6)+1, INSTR(TEXT, ';', 1, 7)-INSTR(TEXT, ';', 1, 6)-1)
ELSE 'Error'
END) AS KEY
from request_log
where request_log.meta_1 <= 99999
and extract(hour from request_log.created_timestamp) BETWEEN 5 AND 23
group by request_log.meta_1,
request_log.meta_2,
to_char(request_log.created_timestamp, 'DDD'),
(CASE
WHEN TEXT LIKE '%html_error%' THEN SUBSTR(TEXT, INSTR(TEXT, ';', -1, 2)+1, (INSTR(TEXT, ';', -1, 1)-INSTR(TEXT, ';', -1, 2)-1))
WHEN TEXT LIKE '%get_status%' THEN SUBSTR(TEXT, INSTR(TEXT, ';', -1, 4)+1, (INSTR(TEXT, ';', -1, 3)-INSTR(TEXT, ';', -1, 4)-1))
WHEN TEXT LIKE '%send_events%' THEN SUBSTR(TEXT, INSTR(TEXT, ';', 1, 6)+1, INSTR(TEXT, ';', 1, 7)-INSTR(TEXT, ';', 1, 6)-1)
ELSE 'Error'
END)
) rql
where (device.user_ID = user.id)
and (user.meta_1 = rql.meta_1)
and (user.meta_2 = rql.meta_2)) a,
(select rql_global.date, rql_global.success_rate_day
from (select to_char(request_log.created_timestamp, 'DDD') AS date, ROUND(
(select count(b.TEXT) FROM request_log b where b.meta_1 <= 99999 and extract(hour from b.created_timestamp) BETWEEN 5 AND 23 and b.text NOT LIKE '%error%' and to_char(request_log.created_timestamp, 'DDD') = to_char(b.created_timestamp, 'DDD'))
/
GREATEST((select count(c.TEXT) FROM request_log c where c.meta_1 <= 99999 and extract(hour from c.created_timestamp) BETWEEN 5 AND 23 and to_char(request_log.created_timestamp, 'DDD') = to_char(c.created_timestamp, 'DDD')), 1), 4) *100
AS success_rate_day
from request_log
group by to_char(request_log.created_timestamp, 'DDD')
) rql_global) b
where a.date = b.date
order by serial_id ASC, a.date ASC, KEY ASC;