我有一个表,对于给定的(time, country, asn)
元组,存储几个指标:
+----------+---------+-------+-------+--------+--------+
| time | country | asn | rtt | rexb | reqs |
+----------+---------+-------+-------+--------+--------+
| 10000000 | US | 12345 | 40 | 0.05 | 5000 |
| 10000000 | US | 54321 | 120 | 0.15 | 500 |
| 10000000 | MX | 12345 | 300 | 0.25 | 1000 |
| 10000000 | MX | 54321 | 160 | 0.10 | 200 |
| .... | ... | ... | ... | .... | .... |
在一般使用过程中,我将这些指标中的每个指标归一化为0到100之间,然后返回最大的值,以便获得与该国ASN的连接的粗略估算:
SELECT
country,
asn,
least(
-- least(100, greatest(0, ...)) = clip value between 0 and 100
least(100, greatest(0,
-- normalize and protect against null values
-- sample normalization:
-- 0 ms RTT = "100% good"
-- 300 ms RTT = "0% good"
coalesce((300 - rtt) / 3, 0)
)),
least(100, greatest(0,
-- sample normalization:
-- 0% REXB = "100% good"
-- 50% REXB = "0% good"
coalesce((0.5 - rexb) / 0.5, 0)
)),
-- Other metrics may follow
) as quality
FROM
metrics
WHERE
time = 10000000 -- "current time"
偶尔我可能会使用reqs
(该国 ASN的请求数(进行加权:
SELECT
country,
least(
least(100, greatest(0,
coalesce((300 - sum(rtt*reqs)/sum(reqs)) / 3, 0)
)),
least(100, greatest(0,
coalesce((0.5 - sum(rexb*reqs)/sum(reqs)) / 0.5, 0)
))
) as avg_quality
FROM
metrics
WHERE
time = 10000000 -- "current time"
GROUP BY
country
此查询工作得很好。但是,当我尝试在子查询中使用它时,我只是遇到了一个问题。
我的目标是发现有多少国家的"平均质量"低于一定的门槛:
SELECT
count(*)
FROM (
SELECT
country,
least(
least(100, greatest(0,
coalesce((300 - sum(rtt*reqs)/sum(reqs)) / 3, 0)
)),
least(100, greatest(0,
coalesce((0.5 - sum(rexb*reqs)/sum(reqs)) / 0.5, 0)
))
) as avg_quality
FROM
metrics
WHERE
time = 10000000 -- "current time"
GROUP BY
time, country
) t1
WHERE t1.avg_quality < 50
这引发了错误:
ERROR 1815 (HY000): Internal error: Lost connection to ExeMgr. Please contact your administrator
我可以在没有问题的情况下执行更简单的子征服。为什么要失败,我该如何修复?
我正在使用MariadB,并且metrics
表使用柱状引擎。
快速更新
当我用 WHERE country = "US"
替换 WHERE t1.avg_quality < 50
时,查询执行时没有问题。因此,它在执行子查询或过滤时毫无困难。严格来说,当我尝试在计算的列上过滤时,它失败了。
我在公司的DBA伸出援手,看看他们是否有任何答案或建议。他们无法为这种行为提供解释,但他们能够提供一个工作:
SELECT
count(*)
FROM (
SELECT
country,
least(
least(100, greatest(0,
coalesce((300 - sum(rtt*reqs)/sum(reqs)) / 3, 0)
)),
least(100, greatest(0,
coalesce((0.5 - sum(rexb*reqs)/sum(reqs)) / 0.5, 0)
))
) as avg_quality
FROM
metrics
WHERE
time = 10000000 -- "current time"
GROUP BY
time, country
HAVING
avg_quality < 50
) t1