google-bigquery - 如何在不指定列名的情况下使用 bigquery 在表中的每一列上调用内置函数？
问题描述
使用看起来像这样的表
WITH
table_one AS(
SELECT 0.123 feat_1, 0.645 feat_2 , 1 label_bc, 'a' dontcare UNION ALL
SELECT 0.567, 0.456, 0, 'v' UNION ALL
SELECT 0.243, 0.734, 1, 'x' UNION ALL
SELECT 0.456, 0.888, 0, 'c' UNION ALL
SELECT 0.645, 0.222, 1, 'x' UNION ALL
SELECT 0.321, 0.123, 0, 'z'
)
SELECT * from table_one
我想计算特征和 label_bc 之间的相关性。我可以手动指定功能以获得所需的结果，如下所示：
选择 corr(label_bc, feat_1) as feat1_corr, corr(label_bc, feat_2) as feat2_corr from table_one
虽然这正是我正在寻找的，但由于我必须输入列名，所以不需要它。使用我的真实世界数据，我有大量的特征，它们可能会不时变化。所以这种方法是不实用的。
我可以做这样的事情来获得所需的列。请注意，这仅适用于真实表，而不适用于此示例 table_one 中的虚拟表。
all_columns AS (
SELECT column_name
FROM `my-proj`.my_dataset.INFORMATION_SCHEMA.COLUMNS where column_name not in ('dontcare', 'label_bc') AND
TABLE_NAME = 'table_one' -- will work if real table
)
这将返回一个表，其中有一列称为“column_name”，每一行都有一个字符串值，即列名。在这种情况下，它将是两行中的“feat_1”和“feat_2”。
我也可以做这样的事情
column_name_array as (
select ARRAY_AGG(all_columns) from all_columns
)
这将返回一个一列一行的表格。该列将是“all_columns”，值将是一个列名数组。['feat_1'，'feat_2']
话虽如此，我不能将这些原语结合起来为任何给定的具有不同列的表提供工作解决方案，以计算所有特征列和 label_bc 列之间的 corr() 。任何指导表示赞赏。
这不是一个答案，而是解决方案的一些额外努力。
-- Assume a table like this
----------------------------------------------------
-- feat_1 feat_2 feat_3 label_bc dontcare
----------------------------------------------------
-- 0.123 0.645 0.656 1.0 a
-- 0.567 0.456 -0.056 0.0 b
-- 0.243 0.734 0.754 1.0 c
-- 0.456 0.888 -0.858 0.0 i
-- 0.645 0.222 0.252 1.0 j
-- 0.321 0.123 -0.153 0.0 c
----------------------------------------------------
WITH
-- my_colnames is currently not used
my_colnames (colname) as (
SELECT 'feat_1' colname UNION ALL
SELECT 'feat_2' UNION ALL
SELECT 'feat_3' UNION ALL
SELECT 'dontcare' UNION ALL
SELECT 'label_bc'
),
-- the static sample table
table_one AS (
SELECT 0.123 feat_1,
0.645 feat_2,
0.656 feat_3,
1.0 label_bc,
'a' dontcare
UNION ALL
SELECT 0.567,
0.456,
-0.056,
0.0,
'b'
UNION ALL
SELECT 0.243,
0.734,
0.754,
1.0,
'c'
UNION ALL
SELECT 0.456,
0.888,
-0.858,
0.0,
'i'
UNION ALL
SELECT 0.645,
0.222,
0.252,
1.0,
'j'
UNION ALL
SELECT 0.321,
0.123,
-0.153,
0.0,
'c'
),
-- transform of table_one where feat values are moved
-- from column based to row based such as the table_one is
-- is represented like this:
----------------------------------------------------
-- feat_id feat_val label_bc dontcare
----------------------------------------------------
-- 1 0.123 1.0 a
-- 1 0.567 0.0 b
-- 1 0.243 1.0 c
-- 1 0.456 0.0 i
-- 1 0.645 1.0 j
-- 1 0.321 0.0 c
-- 2 0.645 1.0 a
-- 2 0.456 0.0 b
-- 2 0.734 1.0 c
-- 2 0.888 0.0 i
-- 2 0.222 1.0 j
-- 2 0.123 0.0 c
-- 3 0.656 1.0 a
-- 3 -0.056 0.0 b
-- 3 0.754 1.0 c
-- 3 -0.858 0.0 i
-- 3 0.252 1.0 j
-- 3 -0.153 0.0 c
----------------------------------------------------
--
-- Is it possible to create this table given the my_colnames table
-- and not have to manually specify the field names below?
table_two AS (
select 1 as feat_id,
feat_1 as feat_val,
label_bc,
dontcare
from table_one union all
select 2,
feat_2,
label_bc,
dontcare
from table_one union all
select 3,
feat_3,
label_bc,
dontcare
from table_one
)
-- works
--select * from my_colnames
--select * from table_one
select * from table_two
--select corr(label_bc, feat_1) as feat1_corr, corr(label_bc, feat_2) as feat2_corr, corr(label_bc, feat_3) as feat3_corr from table_one
--select feat_id, corr(label_bc, feat_val) as feat_corr from table_two GROUP BY feat_id;
这是 Mikhail 为我这样的 SQL 新手准备的答案。我不得不用中间结果来理解他的出色方法。我认为我们需要这些特征作为一个数字，比如一个 id。在这里，我修改他的正则表达式以简单地使用名称。
#standardSQL
-- Assume a table like this
----------------------------------------------------
-- feat_1 feat_2 feat_3 label_bc dontcare
----------------------------------------------------
-- 0.123 0.645 0.656 1.0 a
-- 0.567 0.456 -0.056 0.0 b
-- 0.243 0.734 0.754 1.0 c
-- 0.456 0.888 -0.858 0.0 i
-- 0.645 0.222 0.252 1.0 j
-- 0.321 0.123 -0.153 0.0 c
----------------------------------------------------
WITH table_one AS (
SELECT 0.123 feat_1, 0.645 feat_2, 0.656 feat_3, 1.0 label_bc, 'a' dontcare UNION ALL
SELECT 0.567, 0.456, -0.056, 0.0, 'b' UNION ALL
SELECT 0.243, 0.734, 0.754, 1.0, 'c' UNION ALL
SELECT 0.456, 0.888, -0.858, 0.0, 'i' UNION ALL
SELECT 0.645, 0.222, 0.252, 1.0, 'j' UNION ALL
SELECT 0.321, 0.123, -0.153, 0.0, 'c'
),
-- transform of table_one where feat values are moved
-- from column based to row based such as the table_one is
-- represented like this:
----------------------------------------------------
-- feat_id feat_val label_bc dontcare
----------------------------------------------------
-- 1 0.123 1.0 a
-- 1 0.567 0.0 b
-- 1 0.243 1.0 c
-- 1 0.456 0.0 i
-- 1 0.645 1.0 j
-- 1 0.321 0.0 c
-- 2 0.645 1.0 a
-- 2 0.456 0.0 b
-- 2 0.734 1.0 c
-- 2 0.888 0.0 i
-- 2 0.222 1.0 j
-- 2 0.123 0.0 c
-- 3 0.656 1.0 a
-- 3 -0.056 0.0 b
-- 3 0.754 1.0 c
-- 3 -0.858 0.0 i
-- 3 0.252 1.0 j
-- 3 -0.153 0.0 c
----------------------------------------------------
-- regular expression looks for a quotation mark, feat_, a number one or more times, a quotation mark,
-- a colon, a negative sign zero or more times, a number one or more times, a decimal point, a number one or more times
-- SELECT
-- REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"feat_\d+":-?\d+.\d+')
-- from table_one as t;
-- Shows how to create a table of coefficients without specifying
-- the table column names for features using regular expressions
-- by examine each row as a json key:value pair string format.
table_two AS (
SELECT
CAST(REGEXP_EXTRACT(SPLIT(kv, ':')[OFFSET(0)], r'feat_(\d+)') AS INT64) AS feat_id,
CAST(SPLIT(kv, ':')[SAFE_OFFSET(1)] AS FLOAT64) AS feat_val,
label_bc,
dontcare
FROM table_one t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"feat_\d+":-?\d+.\d+')) kv
),
-- table_2b is first step in building table_2
-- this shows how the table_one table is cross joined with the unnested
-- array of the json for each row of table_one.
-- REGEXP_EXTRACT_ALL does not return a table. It returns an array. One
-- element for each row in table since it uses to_JSON_STRING
-- kv column looks like
-- "feat_1:0.123
-- The table looks like this
-- Row feat_1 feat_2 feat_3 label_bc dontcare kv
-- 1 0.123 0.645 0.656 1.0 a "feat_1":0.123
-- 2 0.123 0.645 0.656 1.0 a "feat_2":0.645
-- 3 0.123 0.645 0.656 1.0 a "feat_3":0.656
-- 4 0.567 0.456 -0.056 0.0 b "feat_1":0.567
table_two_b AS (
SELECT
*
FROM table_one t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"feat_\d+":-?\d+.\d+')) kv
),
table_two_c AS (
SELECT
-- split returns an array of strings. offset(0) is the first one
-- corresponding to the key name which is the feature_name
REGEXP_EXTRACT(SPLIT(kv, ':')[OFFSET(0)], r'(.+)') AS feat_name,
CAST(SPLIT(kv, ':')[SAFE_OFFSET(1)] AS FLOAT64) AS feat_val,
label_bc
FROM table_one t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"feat_\d+":-?\d+.\d+')) kv
)
-- select * from table_two_c
-- Shows what I want from table_two
SELECT feat_name, CORR(label_bc, feat_val) AS feat_corr
FROM table_two_c
GROUP BY feat_name
-- Shows how the table is now t.feat_1, and an extra column json_row which is
-- a second copy of the table in one row using {"key":value, "key":value ...} syntax.
--SELECT
-- t,
-- TO_JSON_STRING(t) AS json_row
-- FROM table_one AS t;
解决方案
以下是 BigQuery 标准 SQL
#standardSQL
WITH table_two AS (
SELECT
CAST(REGEXP_EXTRACT(SPLIT(kv, ':')[OFFSET(0)], r'feat_(\d+)') AS INT64) AS feat_id,
CAST(SPLIT(kv, ':')[SAFE_OFFSET(1)] AS FLOAT64) AS feat_val,
label_bc, dontcare
FROM `project.dataset.table_one` t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"feat_\d+":-?\d+.\d+')) kv
)
SELECT feat_id, CORR(label_bc, feat_val) AS feat_corr
FROM table_two
GROUP BY feat_id
您可以使用示例/虚拟数据进行测试，使用上面的示例，如下例所示
#standardSQL
WITH table_one AS (
SELECT 0.123 feat_1, 0.645 feat_2, 0.656 feat_3, 1.0 label_bc, 'a' dontcare UNION ALL
SELECT 0.567, 0.456, -0.056, 0.0, 'b' UNION ALL
SELECT 0.243, 0.734, 0.754, 1.0, 'c' UNION ALL
SELECT 0.456, 0.888, -0.858, 0.0, 'i' UNION ALL
SELECT 0.645, 0.222, 0.252, 1.0, 'j' UNION ALL
SELECT 0.321, 0.123, -0.153, 0.0, 'c'
), table_two AS (
SELECT
CAST(REGEXP_EXTRACT(SPLIT(kv, ':')[OFFSET(0)], r'feat_(\d+)') AS INT64) AS feat_id,
CAST(SPLIT(kv, ':')[SAFE_OFFSET(1)] AS FLOAT64) AS feat_val,
label_bc, dontcare
FROM table_one t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"feat_\d+":-?\d+.\d+')) kv
)
SELECT feat_id, CORR(label_bc, feat_val) AS feat_corr
FROM table_two
GROUP BY feat_id
结果
Row feat_id feat_corr
1 1 -0.30526201038849277
2 2 0.0818318512559385
3 3 0.838349444539397
推荐阅读
- android - Flutter：Geolocator返回方法'compareTo'在null上被调用
- javascript - 如何在 jquery 中附加 php 和脚本变量？
- angular - 是否可以在Angular6中的多个输入字段中计算字符
- reactjs - 放宽 React 类中的子项约束
- python - 超出python最大递归深度
- apache - 如何将语言功能的htaccess重定向与www合并到非www和http到https？浏览器返回：err_too_many_redirects
- python - Word To Vec with Spacy，词归类
- php - 在 laravel 异常处理程序中添加更多属性
- python-3.x - 我想在谷歌云中使用 python 函数列出组织中的所有资源
- python - 在 Tkinter 中是否有线程的替代方法？或者有没有办法在没有线程的情况下运行后台计时器？