sql - 有没有办法针对 SQL-CSV-Antipattern 编写 'good' 查询？

Question

TL;DR 在底部。

我继承了一个设计相当糟糕的表，其中保存了针对第三方网络服务的请求日志。该表包含一个时间戳、两个名为metadata_1 和metadata_2 的字段(允许您识别用户)以及一个名为TEXT 的超长VARCHAR2 字段。

此文本字段保存实际的请求信息，表示为逗号分隔的值列表。此列表中隐藏着两个相关的信息，我将其称为“请求类型”和“请求 key ”。我试图找出每个用户的不成功请求与每天总请求的比率以及请求 key 。然后将该值与当天的全局错误率进行比较，以识别与网络服务通信时遇到问题的设备。 (元数据仅标识用户，因此访问他们的设备需要一些额外的工作。)不成功的请求有一个我称之为“错误类型”的字段，该字段插入在请求类型之前。

正如您可能从最后一句中猜到的那样，这个以逗号分隔的值列表的格式不一致。特别是，逗号分隔列表中 KEY 字段的位置取决于请求类型，而请求类型的位置又取决于请求是否成功。所有这些字段都是可变长度的。

因此 TEXT 字段可能如下所示:

"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"

但它也可能看起来像这样:

"2017-04-05T07:23:46.459Z,send_events,80,qwert-8,2,1,KEY_123,foobar,1,1,false,114,11838"

我的问题是:您将如何解决这个问题？有一个查询可以在测试环境中运行，如下所示，但其性能极差。必须有更好的方法来做到这一点。假设这一切都必须在单个查询中完成，并且我对环境或数据库设计没有影响。 (请注意，这里进行了一些额外的过滤。任何其他看起来奇怪的事情都可能是我在匿名化过程中犯的错误。这更多的是关于我正在使用的策略 - 我不是要求您为我编写查询。 )

<小时/>

期望结果:对于每个用户、日期和请求 key ，成功和不成功请求的数量与当天成功请求的比例相比。

当前结果:如上所述，但性能 Not Acceptable 。

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;

Answer 1

我认为这可以重写如下:

WITH log_info AS (SELECT meta_1,
                         meta_2,
                         to_char(created_timestamp, 'DDD') dt,
                         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 key_val
                  FROM   request_log 
                  where  request_log.meta_1 <= 99999
                  and    extract(hour from request_log.created_timestamp) BETWEEN 5 AND 23),
           li AS (SELECT meta_1,
                         meta_2,
                         dt,
                         key_val,
                         COUNT(CASE WHEN text NOT LIKE '%error%' THEN 1 END) requests_ok,
                         COUNT(CASE WHEN text LIKE '%error%' THEN 1 END) requests_error,
                         COUNT(*) total_requests
                  FROM   log_info
                  GROUP BY meta_1,
                           meta_2,
                           dt,
                           key_val),
           rl AS (SELECT meta_1,
                         meta_2,
                         dt,
                         key_val,
                         requests_ok,
                         requests_error,
                         SUM(requests_error) OVER (PARTITION BY dt) requests_error_by_ddd,
                         SUM(total_requests) OVER (PARTITION BY dt) total_requests_by_ddd
                  FROM   li)
SELECT d.serial_id,
       rql.date,
       rql.key,
       rql.requests_ok,
       rql.requests_error,
       ROUND(100 * reqests_error_by_ddd/greatest(total_requests_by_ddd, 1), 2) success_rate_day
FROM   device d
       INNER JOIN usr u ON d.user_id = u.id
       INNER JOIN rl ON u.meta_1 = rl.meta_1
                        AND u.meta_2 = rl.meta_2;

不过，您必须检查我是否设法使逻辑正确。

首先，您在各处重复键的 case 语句，因此我将其提取到一个单独的子查询中 ( log_info )。

接下来，您似乎想要进行条件计数，因此我没有使用单独的标量子查询来获取计数，而是使用大小写来限制我想要计数的行(空值不会得到)包含在计数中)。这是在 li 子查询中完成的。

然后，您似乎需要获取每天的总请求数和总错误请求数，因此我使用了分析 sum() 函数将同一天所有行的信息汇总在一起 - 这是在 rl 子查询中完成的.

然后，在最终查询中，我执行与其他表的联接以及 success_rate_day 计算。请注意，我将连接从旧式语法转换为 ANSI 连接语法。

假设我的逻辑确实正确，这应该比您当前的查询性能更高。如果我的逻辑错误，希望您能够相应地修改我的查询。

我还加入了我的声音，建议优化表格，将信息拉到自己单独的列中 *{;-)