sql - 使用 SQLite 将组中的行相乘

Question

我有一个查询返回 token 具有特定分类的概率。

token       class       probPaired
----------  ----------  ----------
potato      A           0.5
potato      B           0.5
potato      C           1.0
potato      D           0.5
time        A           0.5
time        B           1.0
time        C           0.5

我需要汇总每个 class 的概率通过将它们相乘。

-- Imaginary MUL operator
select class, MUL(probPaired) from myTable group by class;

class       probability
----------  ----------
A           0.25
B           0.5
C           0.5
D           0.5

我怎样才能在 SQLite 中做到这一点？ SQLite 没有像 LOG 这样的功能。/ EXP或变量 - 解决方案 mentioned in other questions .

Answer 1

一般来说，如果 SQLite 做不到，你可以编写一个自定义函数。细节取决于您使用的编程语言，这里是在 Perl 中使用 DBD::SQLite .请注意，以这种方式创建的函数不是存储过程，它们存在于该连接中，并且每次连接时都必须重新创建。

对于聚合函数，您必须创建一个处理聚合的类。 MUL很简单，只是一个存储产品的对象。

{
    package My::SQLite::MUL;

    sub new {
        my $class = shift;
        my $mul = 1;
        return bless \$mul, $class;
    }

    sub step {
        my $self = shift;
        my $num = shift;

        $$self *= $num;

        return;
    }

    sub finalize {
        my $self = shift;

        return $$self;
    }
}

然后你将它安装为聚合函数 MUL它接受一个参数并使用该类。

my $dbh = ...doesn't matter how the connection is made...

$dbh->sqlite_create_aggregate("MUL", 1, "My::SQLite::MUL");

现在您可以使用 MUL在查询中。

my $rows = $dbh->selectall_arrayref(
    "select class, MUL(probPaired) from myTable group by class"
);

同样，细节会因您的特定语言而异，但基本思想是相同的。

这比获取每一行并获取聚合产品要快得多。