SQL查询:仅从字符串值列表中选择数字

Question

我需要使用DB浏览器在sqlite数据库中创建一个查询。列有: state、measure_id、measure_name、score。所有数据都是以字符串形式输入的。我可以将分数字符串转换为十进制，但是问题是，分数列的一些值是数值，而另一些是实际的字符串值(例如“高”等)。我需要忽略输出中的实际字符串值。此外，我还需要计算每个measure_id的标准偏差(以及最小、最大、平均)。

如何忽略实际字符串值并计算标准差？

以下是一些示例数据：

sample 1: AL, ID1, Ident, 52
sample 2: TX, ID2, Foo, High
sample 3: MI, ID3, Bar, 21

(我只想选择示例1和3，然后将字符串转换为int并计算stdev)

Answer 1

如果这些值从不为0，则可以执行以下操作：

select avg(cast(value as decimal))
from t
where cast(value as decimal) > 0;

标准差的计算有点棘手。您可以使用定义公式，但SQLite甚至没有平方根函数。

如果您想要支持这些类型的操作，您可能希望移动到另一个数据库，例如Postgres或MySQL。