用SQL bigquery转置一个列并从其他列获取值

Question

目前，我已经将GA4数据集扁平化为单独的行。但是现在我看到一个记录有相同的日期，事件和时间戳:

what I have:

<表类> 日期 事件 键 int_value str_value tbody><<tr>2022-04-01firstopen系统1空2022-04-01firstopenengagementtime0空2022-04-01firstopen设备空android2022-04-02screenview系统0空2022-04-02screenviewengagementtime1空2022-04-02screenview设备空iphone

一个简单的方法是:

-- sample data
WITH sample_table AS (
-- put your table here.
)
-- query starts here
SELECT date, event,
MAX(IF(key = 'system', int_value, NULL)) system,
MAX(IF(key = 'engagementtime', int_value, NULL)) engagementtime,
MAX(IF(key = 'device', str_value, NULL)) device,
FROM sample_table
GROUP BY 1, 2;
-- query result
+------------+------------+--------+----------------+---------+
|    date    |   event    | system | engagementtime | device  |
+------------+------------+--------+----------------+---------+
| 2022-04-01 | firstopen  |      1 |              0 | android |
| 2022-04-02 | screenview |      0 |              1 | iphone  |
+------------+------------+--------+----------------+---------+

您也可以使用PIVOT查询。

WITH sample_table AS (
-- sample data
)
-- pivot query starts here
SELECT * FROM sample_table
PIVOT (
ANY_VALUE(COALESCE('' || int_value, str_value)) 
FOR key IN ('system', 'engagementtime', 'device')
);

• '' || int_value与CAST(int_value AS STRING)相同
• 的结果几乎与上面相同，但是system和engagementtime列在这种情况下是字符串类型而不是整数类型。