如何在谷歌大查询中将字符串转换为列名?

Question

>我在Google BigQuery中有一个大型数据集，其中包含数百万行脏数据(应用程序跟踪(，我正在尝试清理这些数据。我的一个问题是，对于应用程序中触发的不同事件，相同的数据被发送到不同的列。我的意思是，对于某些事件，国家/地区可能被发送到自定义维度 1，但对于其他事件，国家/地区被发送到自定义维度 147。我无法发布实际数据，但SELECT * FROM table_with_dirty_data会产生如下内容：

date | session | eventAction | cd001 | cd002    | cd004    | cd005 
-----|---------|-------------|-------|----------|----------|-------
1    | 1       | 'event_1'   | '1'   | 'Pizza'  | null     | '21'
1    | 1       | 'event_2'   | '10'  | '25'     | 'Pizza'  | '14.56'
1    | 1       | 'event_3'   | '3.1' | null     | '15'     | 'France'
1    | 2       | 'event_1'   | '6'   | 'Burger' | null     | '21'
1    | 2       | 'event_2'   | '21'  | '25'     | 'Burger' | '12.6'

这里的最终目标是最终得到一个可以分析的干净桌子。像这样：

date | session | eventAction | country  | vendor   | product  | price
-----|---------|-------------|----------|----------|----------|-------
1    | 1       | 'event_1'   | 'France' | '1'      | 'Pizza'  | '14.56'
1    | 1       | 'event_2'   | 'France' | '1'      | 'Pizza'  | '14.56'
1    | 1       | 'event_3'   | 'France' | '1'      | 'Pizza'  | '14.56'
1    | 2       | 'event_1'   | 'Spain'  | '25'     | 'Burger' | '12.6'
1    | 2       | 'event_2'   | 'Spain'  | '25'     | 'Burger' | '12.6'

我知道有些事件需要一定程度的统计插补和数据类型转换，但现在我只关心将每个变量放入其自己的列中。所以我创建了一个辅助表(我们称之为matrix(，如下所示：

event_name | variable_1 | variable_2 | variable_3
-----------|------------|------------|----------
'event_1'  | 'cd020'    | 'cd035'    | 'cd120'
'event_2'  | 'cd020'    | 'cd146'    | 'cd056'
'event_3'  | 'cd001'    | 'cd020'    | 'cd035'

依此类推，其中列variable_#的每个单元格中的值是可以找到信息的table_with_dirty_data中的列名。也就是说，对于具有"event_1"和"even_2"的事件event_name，可以在名为cd020的列中找到variable_1，但在名为cd001的列中找到具有event_name"event_3"的事件。因此，基本上matrix所做的是将每个变量发送到每个事件的自定义维度。

我在table_with_dirty_data中有数百个不同的事件，matrix包含所有 200 个 GA 自定义维度，因此执行类似操作

SELECT 
CASE 
WHEN event_name = 'event_1' THEN cd020
WHEN event_name = 'event_2' THEN cd020
WHEN event_name = 'event_3' THEN cd001
END AS variable_1
, CASE
WHEN event_name = 'event_1' THEN cd035
WHEN event_name = 'event_2' THEN cd146
WHEN event_name = 'event_3' THEN cd020
END AS variable_2
, CASE
WHEN event_name = 'event_1' THEN cd120
WHEN event_name = 'event_2' THEN cd056
WHEN event_name = 'event_3' THEN cd035
END AS variable_3
FROM table_with_dirty_data

会花费我很长时间，而且非常容易出错。我试图做的是使用SELECT语句从存储信息的table_with_dirty_data返回列名(cd###(，并使用WHILE遍历所有不同的事件。因此，例如，对于event_name = 'event_1'，它将是这样的：

SELECT 
CASE
WHEN event_name = 'event_1'
THEN (SELECT variable_1 FROM matrix WHERE event_name = 'event_1')
END AS variable_1
, CASE
WHEN event_name = 'event_1'
THEN (SELECT variable_2 FROM matrix WHERE event_name = 'event_1')
END AS variable_2
, CASE
WHEN event_name = 'event_1'
THEN (SELECT variable_3 FROM matrix WHERE event_name = 'event_1')
END AS variable_3
FROM table_with_dirty_data
WHERE event_name = 'event_1'

这里的目标是，然后我可以遍历一个包含所有event_name的数组(这很容易实现(。最终，我需要将所有事件放在一个表中，但我可以有一个表来策划每个事件，只要它可以以编程方式完成(我个人不知道这在 GBQ 中是否可行......将不得不检查这一点(。

问题是我正在使用的SELECT语句被计算为字符串，因此CASE子句内查询的结果是字符串文字。例如，如果我为事件 = 'event_1' 运行它，

SELECT variable_1 FROM matrix WHERE event_name = 'event_1'

评估结果为

'cd020'

然后导致外部查询变为

SELECT 
CASE
WHEN event_name = 'event_1' THEN 'cd020'
END AS variable_1
, CASE
WHEN event_name = 'event_1' THEN 'cd035'
END AS variable_2
, CASE
WHEN event_name = 'event_1' THEN 'cd120'
END AS variable_3
FROM table_with_dirty_data
WHERE event_name = 'event_1'

生成这样的表

event_name | variable_1 | variable_2 | variable_3
-----------|------------|------------|----------
'event_1'  | 'cd020'    | 'cd035'    | 'cd120'
'event_1'  | 'cd020'    | 'cd035'    | 'cd120'
'event_1'  | 'cd020'    | 'cd035'    | 'cd120'

而不是返回存储在列中的值cd020，每次找到事件 'event_1' 时cd035或cd120。

有谁知道一种方法来取消引用这些内部查询的结果，以便在执行外部查询时将它们转换为列名(因此"cd020"变为cd020(？？

PS：如果有人知道的话，我也愿意接受完全不同的策略。

Answer 1

听起来像是立即执行可能会有所帮助的情况。我不确定我是否完全理解映射，但它应该允许您根据matrix表编写动态语句。

但是，根据描述，不清楚生成的查询可能有多笨拙，因为您提到有数百个此类事件，并且您可能会开始遇到其他问题，例如查询复杂性或长度限制。

Answer 2

我按照@shollyman的建议使用EXECUTE IMMEDIATE子句解决了这个问题。我只为其中一个事件做了这件事，但我相信这回答了最初的问题(将其扩展到其他事件只是编写一个WHILE循环的问题(。我将逐步进行，因为我没有使用实际的 repex。

第一步，我声明了一个名为event的变量，其中包含查询需要查找的事件的名称。

DECLARE event STRING DEFAULT 'event_1';

然后，我声明了一个变量，该变量保存查询需要查找变量的列名。

DECLARE variable_name STRING DEFAULT (SELECT variable_1 FROM matrix WHERE event_name = event);

然后我像往常一样编写查询，但使用EXECUTE IMMEDIATE子句。我使用了三重双引号，以便我可以将其分成几行以提高可读性(。

EXECUTE IMMEDIATE CONCAT("""
SELECT
CASE WHEN event_name = '""", event, "' THEN ", variable_name, """ END AS variable_1 
FROM table_with_dirty_data
WHERE event_name = '""", event, """'
""");

如果其他人要使用它，请注意我在三重双引号之前或之后使用的孤独的单引号。我这样做是因为，例如，声明的变量event，即使它是一个字符串，似乎被连接为event(周围没有单引号(，这会破坏查询执行。