mysql - SQL 中的条件概率 p(y|x)

Question

如何在仅读取一次预计算表(直方图)时计算供应商不可知 SQL 代码中的条件概率？

假设我们有一个返回直方图 关系的查询。 直方图包含以下属性:{x、y、cnt}，其中cnt 是标称属性 x 和 y 的出现次数。并且直方图的计算非常耗时。

一旦我们有了直方图，我们就想计算条件概率p(y|x)。一种可能的方法是采用 p(y|x) = count(y,x)/count(x)，如以下查询所述:

with histogram as (
    // Long and time consuming subquery returning {x, y, cnt}
), x_count as (
    select x
         , sum(cnt) as cnt
    from histogram
    group by x
) 
select y
     , x
     , cnt/x_count.cnt as probability 
from histogram 
join x_count
using(x)

但是，公用表表达式 (CTE) 不可移植(例如 MySQL 不能使用它们)。有没有一种方法可以重写 CTE:

1. 可以在 MySQL、MSSQL 和 PostgreSQL 上执行相同的查询而无需更改？
2. 关系直方图只计算一次？

我能想到的就是将直方图具体化为表格。处理直方图。并删除直方图。

Answer 1

首先，仅仅因为您将某项声明为 CTE 并不意味着它只运行一次。例如，SQL Server 不会具体化 CTE，因此使用您的逻辑它将为每个引用运行一次直方图。它与 View 相同。

此外，using 子句并非所有数据库都支持。

因此，您可以做的与供应商无关的一件事就是使用 View 。有一个小问题，因为删除一个已经存在的 View 是特定于供应商的。但以下内容通常可以表达查询:

create view histogram as  -- you might want to give this a more unique name
    // Long and time consuming subquery returning {x, y, cnt}

select h.y, h.x, cnt / total.cnt as probability 
from histogram h join
     (select x, sum(cnt) as cnt
      from histogram
      group by x
     ) total
     on h.x = total.x;

drop view histogram;

当然，这会多次运行直方图查询。所以，您可以使用临时表解决这个问题:

create table histogram (
    x ??,  -- I don't know what the types are
    y ??,
    cnt ??
);

insert into histogram (x, y, cnt)
    select . . . ;  -- your complicated query here

select y, x, cnt * 1.0 / total.cnt as probability 
from histogram h join
     (select x, sum(cnt) as cnt
      from histogram
      group by x
     ) total
     on h.x = total.x;

drop table histogram;

不幸的是，删除现有 表是特定于数据库的。不过，这确实符合您的要求。

我的建议是将 MySQL 从要求中删除——从 ANSI 功能的角度来看，它相当退化。然后简单地做:

select h.*, cnt * 1.0 / sum(cnt) over (partition by x) as probability
from histogram h;

(* 1.0 是因为某些数据库进行整数除法，而 cnt 听起来可能是整数。)

这将是无需重新计算直方图 即可表示查询的最简单方法。而且，它可以在很多数据库中运行——SQL Server、Postgres、Oracle、Teradata、DB2、BigQuery、RedShift、Hive。事实上，我认为除了 MySQL、SQLite 和 MS Access 之外，它几乎适用于通常称为“数据库”的所有当前版本。