google-bigquery - 如何使用基于多列的bigquery关联？

Question

给定 100k 行和 100 列的数据集，如何使用 bigquery CORR() 来查找行之间的相关性？

架构是:

id:integer, feature1:float, feature2:float, ..., feature100:float

编辑 这不是滚动窗口时间序列相关问题。每行是对 100 个特征的观察，我想使用 bigquery 查找每行的前 N ​​个相似观察。

Answer 1

您想找到每列与其他列之间的相关性吗？

那将是这样的:

SELECT CORR(col1, col2), CORR(col1, col3), CORR(col1, col4),..., CORR(col99, col100)
FROM [mytable]

这可能需要很长时间才能编写(除非您将其自动化)。作为替代方案，请考虑一个不同的模式，其中所有内容都位于 3 列中。转换将像这样运行:

SELECT colname, value, rowid FROM
(SELECT 'col1' AS colname, col1, rowid AS value FROM [mytable]),
(SELECT 'col2' AS colname, col2, rowid AS value FROM [mytable]),
(SELECT 'col3' AS colname, col3, rowid AS value FROM [mytable]),
...
(SELECT 'col100' AS colname, col100 AS value FROM [mytable])

使用此模式，您可以使用更简单的查询运行所有组合列相关性:

SELECT CORR(a.value, b.value) corr, a.colname, b.colname
FROM [my_new_table] a
JOIN EACH [my_new_table] b
ON a.rowid=b.rowid
WHERE a.colname>b.colname
GROUP BY a.colname, b.colname

(这就是我在@Tjorriemorrie - http://googlecloudplatform.blogspot.mx/2013/09/introducing-corr-to-google-bigquery.html 链接的文章中所做的)

请注意，第一个查询可能比最后一个更复杂，但我怀疑运行时间会更短，因为不需要改组。

由于此问题询问行，因此初始转换将类似，但略有不同:

SELECT column, value, rowid FROM
  (SELECT 'c1' column, c1 AS value, rowid FROM [mytable]),
  (SELECT 'c2' column, c2 AS value, rowid FROM [mytable]),
  (SELECT 'c3' column, c3 AS value, rowid FROM [mytable]) 

然后将计算行之间的相关性，如下所示:

SELECT CORR(a.value, b.value), a.rowid, b.rowid
FROM [my_new_table] a
JOIN EACH [my_new_table] b
ON a.column=b.column
WHERE a.rowid < b.rowid
GROUP BY a.rowid, b.rowid