Google BigQuery 主要功能概览——练习编写营销分析请求
已发表: 2022-04-12企业积累的信息越多，存储在哪里的问题就越尖锐。 如果您没有能力或不想维护自己的服务器，Google BigQuery (GBQ) 可以提供帮助。 BigQuery 为处理大数据提供了快速、经济高效且可扩展的存储，它允许您使用类似 SQL 的语法以及标准和用户定义的函数来编写查询。
在本文中，我们将了解 BigQuery 的主要功能，并通过具体示例展示它们的可能性。 您将学习如何编写基本查询并在演示数据上对其进行测试。
无需技术培训或 SQL 知识即可构建 GBQ 数据报告。
您是否经常需要有关广告活动的报告，但没有时间研究 SQL 或等待分析师的回复？ 使用 OWOX BI，您无需了解数据的结构即可创建报告。 只需选择您想在智能数据报告中看到的参数和指标。 OWOX BI Smart Data 将以您可以理解的方式立即可视化您的数据。
免费试用 OWOX BI
目录
- 什么是 SQL，BigQuery 支持哪些方言
- 从哪儿开始
- 谷歌 BigQuery 功能
- 聚合函数
- 日期函数
- 字符串函数
- 窗口函数
- 结论
什么是 SQL，BigQuery 支持哪些方言
结构化查询语言 (SQL) 允许您从大型数组中检索数据、添加数据和修改数据。 Google BigQuery 支持两种 SQL 方言：标准 SQL 和过时的旧版 SQL。
选择哪种方言取决于您的偏好，但 Google 建议使用标准 SQL 以获得以下好处：
- 嵌套和重复字段的灵活性和功能
- 支持 DML 和 DDL 语言，允许您更改表中的数据以及管理 GBQ 中的表和视图
- 与旧版 SQL 相比，处理大量数据的速度更快
- 支持所有未来的 BigQuery 更新
您可以在 BigQuery 文档中详细了解方言差异。
另请参阅：Google BigQuery 的新标准 SQL 方言与旧版 SQL 相比有哪些优势，您可以使用它解决哪些业务任务？
阅读文章
默认情况下，Google BigQuery 查询在旧版 SQL 上运行。
您可以通过多种方式切换到标准 SQL：
- 在 BigQuery 界面的查询编辑窗口中，选择Show Options并删除Use Legacy SQL旁边的复选标记：
- 在查询之前，添加 #standardSQL 行并使用新行开始查询：
从哪儿开始
因此，您可以与我们一起练习和运行查询，我们准备了一个包含演示数据的表格。 填写下面的表格，我们将通过电子邮件将其发送给您。
读者奖金
SQL 查询练习的演示数据
下载要开始使用，请下载您的演示数据表并将其上传到您的 Google BigQuery 项目。 最简单的方法是使用 OWOX BI BigQuery Reports 插件。
- 打开 Google 表格并安装 OWOX BI BigQuery Reports 插件。
- 打开您下载的包含演示数据的表，然后选择OWOX BI BigQuery Reports –> Upload Data to BigQuery ：
- 在打开的窗口中，选择您的 Google BigQuery 项目、一个数据集，然后为将存储加载数据的表命名。
- 指定加载数据的格式（如屏幕截图所示）：
如果您在 Google BigQuery 中没有项目，请创建一个。 为此，您需要在 Google Cloud Platform 中有一个有效的结算帐户。 不要害怕您需要关联银行卡：在您不知情的情况下不会向您收取任何费用。 此外，当您注册时，您将在 12 个月内收到 300 美元，可用于数据存储和处理。
OWOX BI 可帮助您将来自不同系统的数据合并到 BigQuery 中：关于您网站上的用户操作、电话、来自 CRM 的订单、电子邮件、广告费用的数据。 您可以使用 OWOX BI 自定义高级分析并自动生成任何复杂性的报告。
获取演示
在谈论 Google BigQuery 功能之前，让我们记住旧 SQL 和标准 SQL 方言中的基本查询是什么样的：
| 询问 | 旧版 SQL | 标准 SQL |
|---|---|---|
| 从表中选择字段 | 选择字段1，字段2 | 选择字段1，字段2 |
| 选择要从中选择字段的表 | FROM [projectID:dataSet.tableName] | FROM `projectID.dataSet.tableName` |
| 选择过滤值所依据的参数 | WHERE 字段1=值 | WHERE 字段1=值 |
| 选择用于对结果进行分组的字段 | GROUP BY 字段1，字段2 | GROUP BY 字段1，字段2 |
| 选择如何订购结果 | ORDER BY field1 ASC（升序）或 DESC（降序） | ORDER BY field1 ASC（升序）或 DESC（降序） |
谷歌 BigQuery 功能
在构建查询时，您最常使用聚合、日期、字符串和窗口函数。 让我们仔细看看这些函数组中的每一个。
另请参阅：如何开始使用云存储 - 创建数据集和表并配置将数据导入 Google BigQuery。
阅读文章
聚合函数
聚合函数为整个表提供汇总值。 例如，您可以使用它们来计算平均支票大小或每月总收入，或者您可以使用它们来选择购买次数最多的用户细分。
这些是最流行的聚合函数：
| 旧版 SQL | 标准 SQL | 函数的作用 |
|---|---|---|
| 平均（场） | AVG([DISTINCT] (字段)) | 返回字段列的平均值。 在标准 SQL 中，当您添加 DISTINCT 条件时，仅考虑字段列中具有唯一（非重复）值的行的平均值。 |
| 最大值（字段） | 最大值（字段） | 返回字段列的最大值。 |
| 最小值（字段） | 最小值（字段） | 返回字段列的最小值。 |
| 总和（字段） | 总和（字段） | 从字段列返回值的总和。 |
| 计数（字段） | 计数（字段） | 返回字段列中的行数。 |
| EXACT_COUNT_DISTINCT（字段） | 计数（[DISTINCT]（字段）） | 返回字段列中的唯一行数。 |
有关所有聚合函数的列表，请参阅 Legacy SQL 和 Standard SQL 文档。
让我们看一下演示数据，看看这些函数是如何工作的。 我们可以计算交易的平均收入、最高和最低金额的购买、总收入、总交易和唯一交易的数量（以检查是否重复购买）。 为此，我们将编写一个查询，在其中指定 Google BigQuery 项目、数据集和表的名称。
#旧版 SQL
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, EXACT_COUNT_DISTINCT(transactionId) as unique_transactions FROM [owox-analytics:t_kravchenko.Demo_data]
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, EXACT_COUNT_DISTINCT(transactionId) as unique_transactions FROM [owox-analytics:t_kravchenko.Demo_data]#标准 SQL
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, COUNT(DISTINCT(transactionId)) as unique_transactions FROM `owox-analytics.t_kravchenko.Demo_data`
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, COUNT(DISTINCT(transactionId)) as unique_transactions FROM `owox-analytics.t_kravchenko.Demo_data`结果，我们将得到以下信息：
您可以使用标准 Google 表格函数（SUM、AVG 等）或使用数据透视表在原始表中使用演示数据检查这些计算的结果。
从上面的截图可以看出，交易数量和唯一交易数量是不同的。 这表明我们的表中有两个事务具有相同的 transactionId：
如果您对唯一交易感兴趣，请使用计算唯一字符串的函数。 或者，您可以使用 GROUP BY 函数对数据进行分组，以在应用聚合函数之前消除重复项。
读者奖金
SQL 查询练习的演示数据
下载日期函数
这些功能允许您处理日期：更改其格式、选择必要的字段（日、月或年），或将日期移动特定间隔。
它们在以下情况下可能有用：
- 将不同来源的日期和时间转换为单一格式以设置高级分析
- 创建自动更新的报告或触发邮件（例如，当您需要过去两个小时、一周或一个月的数据时）
- 创建需要在几天、几周或几个月内获取数据的同类群组报告
这些是最常用的日期函数：
| 旧版 SQL | 标准 SQL | 功能说明 |
|---|---|---|
| 当前日期（） | 当前日期（） | 以 % YYYY -% MM-% DD 格式返回当前日期。 |
| 日期（时间戳） | 日期（时间戳） | 将日期从 % YYYY -% MM-% DD% H:% M:% C. 转换为 % YYYY -% MM-% DD 格式。 |
| DATE_ADD（时间戳，间隔，interval_units） | DATE_ADD（时间戳，INTERVAL 间隔interval_units） | 返回时间戳日期，将其增加指定的时间间隔 interval.interval_units。在旧版 SQL 中，它可以采用 YEAR、MONTH、DAY、HOUR、MINUTE 和 SECOND 值，而在标准 SQL 中，它可以采用 YEAR、QUARTER、MONTH、周和日。 |
| DATE_ADD（时间戳，- 间隔，interval_units） | DATE_SUB（时间戳，INTERVAL 间隔interval_units） | 返回时间戳日期，按指定的时间间隔递减。 |
| DATEDIFF（时间戳 1，时间戳 2） | DATE_DIFF(timestamp1, timestamp2, date_part) | 返回 timestamp1 和 timestamp2 日期之间的差异。 在旧版 SQL 中，以天为单位返回差值，而在标准 SQL 中，根据指定的 date_part 值（日、周、月、季度、年）返回差值。 |
| DAY（时间戳） | 提取（日期从时间戳） | 返回时间戳日期的日期。 取值从 1 到 31（包括 1 到 31）。 |
| 月（时间戳） | 提取（从时间戳开始的月份） | 从时间戳日期返回月份序列号。 取值从 1 到 12（包括 1 到 12）。 |
| 年（时间戳） | EXTRACT（来自时间戳的年份） | 从时间戳日期返回年份。 |
有关所有日期函数的列表，请参阅 Legacy SQL 和 Standard SQL 文档。
让我们看一下我们的演示数据，看看这些函数是如何工作的。 例如，我们将获取当前日期，将原始表格中的日期转换为 %YYYY -% MM-% DD 格式，将其取出，并在其上添加一天。 然后我们将计算当前日期与源表中的日期之间的差异，并将当前日期分解为单独的年、月和日字段。 为此，您可以复制下面的示例查询并将项目名称、数据集和数据表替换为您自己的。
#旧版 SQL
SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day, DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day, DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date, DAY( CURRENT_DATE() ) AS the_day, MONTH( CURRENT_DATE()) AS the_month, YEAR( CURRENT_DATE()) AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]
SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day, DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day, DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date, DAY( CURRENT_DATE() ) AS the_day, MONTH( CURRENT_DATE()) AS the_month, YEAR( CURRENT_DATE()) AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]#标准 SQL
SELECT today, date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day, DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day, DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date, EXTRACT(DAY FROM today ) AS the_day, EXTRACT(MONTH FROM today ) AS the_month, EXTRACT(YEAR FROM today ) AS the_year FROM ( SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD FROM `owox-analytics.t_kravchenko.Demo_data`)
SELECT today, date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day, DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day, DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date, EXTRACT(DAY FROM today ) AS the_day, EXTRACT(MONTH FROM today ) AS the_month, EXTRACT(YEAR FROM today ) AS the_year FROM ( SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD FROM `owox-analytics.t_kravchenko.Demo_data`)运行查询后，您将收到此报告：
另请参阅：可以使用 SQL 查询对 Google BigQuery 中的数据构建的报告示例，以及您可以使用 OWOX BI 补充 Google Analytics 数据的独特指标。
阅读文章
字符串函数
字符串函数允许您生成字符串、选择和替换子字符串，以及计算字符串的长度和子字符串在原始字符串中的索引序列。 例如，使用字符串函数，您可以：
- 过滤带有传递到页面 URL 的 UTM 标记的报告
- 如果源名称和活动名称写在不同的寄存器中，则将数据转换为单一格式
- 替换报告中的错误数据（例如，如果活动名称打印错误）
这些是处理字符串的最流行的函数：
| 旧版 SQL | 标准 SQL | 功能说明 |
|---|---|---|
| CONCAT('str1', 'str2') 或 'str1'+ 'str2' | 连接（'str1'，'str2'） | 将“str1”和“str2”连接成一个字符串。 |
| “str1”包含“str2” | REGEXP_CONTAINS('str1', 'str2') 或 'str1' LIKE '%str2%' | 如果字符串“str1”包含字符串“str2”，则返回 true。在标准 SQL 中，可以使用re2 库将字符串“str2”写为正则表达式。 |
| 长度（'str'） | CHAR_LENGTH('str') 或 CHARACTER_LENGTH('str') | 返回字符串 'str' 的长度（字符数）。 |
| SUBSTR('str', 索引 [, max_len]) | SUBSTR('str', 索引 [, max_len]) | 返回长度为 max_len 的子字符串，该子字符串以字符串 'str' 中的索引字符开头。 |
| 降低（'str'） | 降低（'str'） | 将字符串 'str 中的所有字符转换为小写。 |
| 上（字符串） | 上（字符串） | 将字符串 'str' 中的所有字符转换为大写。 |
| INSTR('str1', 'str2') | STRPOS('str1', 'str2') | 将字符串“str2”第一次出现的索引返回到字符串“str1”； 否则，返回 0。 |
| 替换（'str1'，'str2'，'str3'） | 替换（'str1'，'str2'，'str3'） | 将 'str1' 替换为 'str2' 和 'str3'。 |
您可以在 Legacy SQL 和 Standard SQL 文档中了解有关所有字符串函数的更多信息。
让我们看一下演示数据，看看如何使用所描述的功能。 假设我们有三个单独的列，分别包含日、月和年值：
使用这种格式的日期不是很方便，因此我们可以将这些值合并到一列中。 为此，请使用下面的 SQL 查询，并记住在 Google BigQuery 中替换您的项目、数据集和表的名称。
#旧版 SQL
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY mix_string1
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY mix_string1#标准 SQL
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM owox-analytics.t_kravchenko.Demo_data) GROUP BY mix_string1
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM owox-analytics.t_kravchenko.Demo_data) GROUP BY mix_string1运行查询后，我们在一列中收到日期：
通常，当您在网站上下载页面时，URL 会记录用户选择的变量的值。 这可以是付款或送货方式、交易编号、买家想要取货的实体店的索引等。使用 SQL 查询，您可以从页面地址中选择这些参数。 考虑两个例子，说明你如何以及为什么这样做。
示例 1 。 假设我们想知道用户从实体店取货的购买次数。 为此，我们需要计算从 URL 中包含子字符串 shop_id（实体店索引）的页面发送的交易数量。 我们可以通过以下查询来做到这一点：
#旧版 SQL
SELECT COUNT(transactionId) AS transactions, check FROM ( SELECT transactionId, page CONTAINS 'shop_id' AS check FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY check
SELECT COUNT(transactionId) AS transactions, check FROM ( SELECT transactionId, page CONTAINS 'shop_id' AS check FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY check#标准 SQL
SELECT COUNT(transactionId) AS transactions, check1, check2 FROM ( SELECT transactionId, REGEXP_CONTAINS( page, 'shop_id') AS check1, page LIKE '%shop_id%' AS check2 FROM `owox-analytics.t_kravchenko.Demo_data`) GROUP BY check1, check2
SELECT COUNT(transactionId) AS transactions, check1, check2 FROM ( SELECT transactionId, REGEXP_CONTAINS( page, 'shop_id') AS check1, page LIKE '%shop_id%' AS check2 FROM `owox-analytics.t_kravchenko.Demo_data`) GROUP BY check1, check2从结果表中，我们看到从包含 shop_id 的页面发送了 5502 笔交易（check = true）：
示例 2 。 您已为每种交付方式分配了一个 delivery_id，并在页面 URL 中指定了此参数的值。 要了解用户选择了哪种交付方式，您需要在单独的列中选择 delivery_id。
我们可以为此使用以下查询：
#旧版 SQL
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, LENGTH(page_lower_case) AS page_length, INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM [owox-analytics:t_kravchenko.Demo_data]))) ORDER BY page_lower_case ASC
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, LENGTH(page_lower_case) AS page_length, INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM [owox-analytics:t_kravchenko.Demo_data]))) ORDER BY page_lower_case ASC#标准 SQL
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, CHAR_LENGTH(page_lower_case) AS page_length, STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM `owox-analytics.t_kravchenko.Demo_data`))) ORDER BY page_lower_case ASC
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, CHAR_LENGTH(page_lower_case) AS page_length, STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM `owox-analytics.t_kravchenko.Demo_data`))) ORDER BY page_lower_case ASC结果，我们在 Google BigQuery 中得到了一个这样的表：
读者奖金
SQL 查询练习的演示数据
下载窗口函数
这些函数类似于我们上面讨论的聚合函数。 主要区别在于窗口函数不会对使用查询选择的整个数据集进行计算，而只会对部分数据（子集或窗口）进行计算。
使用窗口函数，您可以在组部分中聚合数据，而无需使用 JOIN 函数来组合多个查询。 例如，您可以计算每个广告活动的平均收入或每台设备的交易次数。 通过在报告中添加另一个字段，您可以轻松找出黑色星期五广告活动的收入份额或移动应用程序进行的交易的份额。
连同查询中的每个函数，您必须拼出定义窗口边界的 OVER 表达式。 OVER 包含三个可以使用的组件：
- PARTITION BY — 定义将原始数据划分为子集的特征，例如 clientId 或 DayTime
- ORDER BY — 定义子集中行的顺序，例如小时 DESC
- WINDOW FRAME - 允许您处理特定特征子集中的行（例如，仅当前行之前的五行）
在此表中，我们收集了最常用的窗口函数：
| 旧版 SQL | 标准 SQL | 功能说明 |
|---|---|---|
| 平均（场） 计数（字段） 计数（DISTINCT 字段） 最大限度（） 最小（） 和（） | AVG([DISTINCT] (字段)) 计数（字段） 计数（[DISTINCT]（字段）） 最大值（字段） 最小值（字段） 总和（字段） | 从所选子集中的字段列返回平均值、数字、最大值、最小值和总值。DISTINCT 仅用于计算唯一（非重复）值。 |
| DENSE_RANK() | DENSE_RANK() | 返回子集中的行号。 |
| FIRST_VALUE（字段） | FIRST_VALUE（字段[{RESPECT | IGNORE} NULLS]） | 返回子集中字段列的第一行的值。 默认情况下，计算中包括字段列中具有空值的行。 RESPECT 或 IGNORE NULLS 指定是包含还是忽略 NULL 字符串。 |
| LAST_VALUE（字段） | LAST_VALUE（字段 [{RESPECT | IGNORE} NULLS]） | 从字段列返回子集中最后一行的值。默认情况下，字段列中具有空值的行包含在计算中。 RESPECT 或 IGNORE NULLS 指定是包含还是忽略 NULL 字符串。 |
| 滞后（场） | LAG (field[, offset [, default_expression]]) | 返回相对于子集中当前字段列的前一行的值。Offset 是一个整数，指定从当前行向下偏移的行数。Default_expression 是函数在不需要时返回的值子集中的字符串。 |
| 领导（领域） | LEAD (field[, offset [, default_expression]]) | 返回相对于子集中当前字段列的下一行的值。 偏移量是一个整数，它定义了要相对于当前行向上移动的行数。Default_expression 是如果当前子集中没有必需的字符串，函数将返回的值。 |
您可以在 Legacy SQL 和 Standard SQL 的文档中查看所有聚合分析函数和导航函数的列表。
示例 1 。 假设我们要分析客户在工作时间和非工作时间的活动。 为此，我们需要将交易分为两组并计算感兴趣的指标：
- 第 1 组 — 工作时间 9:00 至 18:00 购买
- 第 2 组 — 00:00 至 9:00 和 18:00 至 23:59 下班后购买
除了工作时间和非工作时间，另一个形成窗口的变量是clientId。 也就是说，对于每个用户，我们将有两个窗口：
| 窗户 | 客户 ID | 白天 |
|---|---|---|
| 窗口 1 | 客户编号 1 | 工作时间 |
| 窗口 2 | 客户 ID 2 | 非工作时间 |
| 窗口 3 | 客户 ID 3 | 工作时间 |
| 窗口 4 | 客户 ID 4 | 非工作时间 |
| 窗口 N | 客户编号 N | 工作时间 |
| 窗口 N+1 | 客户编号 N+1 | 非工作时间 |
让我们使用演示数据来计算平均、最大、最小和总收入、交易总数以及每个用户在工作和非工作时间的唯一交易数。 下面的请求将帮助我们做到这一点。
#旧版 SQL
SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC
SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC#标准 SQL
#standardSQL SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC
#standardSQL SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC让我们以 clientId 为 102041117.1428132012 的用户为例来看看结果如何。 在这个用户的原始表中，我们有以下数据：
通过运行查询，我们会收到一份报告，其中包含来自该用户的平均、最低、最高和总收入以及用户的交易总数。 正如您在下面的屏幕截图中看到的，这两项交易都是用户在工作时间进行的：
示例 2 。 现在执行更复杂的任务：
- 根据执行时间将所有事务的序列号放在窗口中。 回想一下，我们按用户和工作/非工作时间段定义窗口。
- 报告窗口内下一个/上一个交易（相对于当前）的收入。
- 在窗口中显示第一笔和最后一笔交易的收入。
为此，我们将使用以下查询：
#旧版 SQL
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour#标准 SQL
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour我们可以使用我们已经知道的用户示例来检查计算结果：clientId 102041117.1428132012：
从上面的截图中，我们可以看到：
- 第一笔交易是在 15:00，第二笔交易是在 16:00
- 15:00交易后，16:00有一笔交易，收益为25066（列lead_revenue）
- 16:00交易前，15:00有一笔交易，收益为3699（列lag_revenue）
- 窗口内的第一笔交易是在 15:00，此笔交易的收入为 3699（first_revenue_by_hour 列）
- 查询逐行处理数据，因此对于有问题的交易，窗口中的最后一笔交易将是其本身，并且 last_revenue_by_hour 和收入列中的值将相同
关于 Google BigQuery 的有用文章：
- 6 大 BigQuery 可视化工具
- 如何将数据上传到 Google BigQuery
- 如何将原始数据从 Google Ads 上传到 Google BigQuery
- Google BigQuery Google 表格连接器
- 使用来自 Google BigQuery 的数据自动生成 Google 表格中的报告
- 根据来自 Google BigQuery 的数据在 Google Data Studio 中自动生成报告
如果您想在 Google BigQuery 中从您的网站收集非抽样数据，但不知道从哪里开始，请预订演示。 我们将告诉您使用 BigQuery 和 OWOX BI 获得的所有可能性。
我们的客户
生长 快22%
通过衡量在您的营销中最有效的方法来更快地增长
分析您的营销效率，找到增长领域，提高投资回报率
获取演示结论
在本文中，我们研究了最流行的函数组：聚合、日期、字符串和窗口。 但是，Google BigQuery 有更多有用的功能，包括：
- 允许您将数据转换为特定格式的转换函数
- 表通配符函数，允许您访问数据集中的多个表
- 允许您描述搜索查询模型而不是其确切值的正则表达式函数
我们一定会在我们的博客上写下这些功能。 同时，您可以使用我们的演示数据试用本文中描述的所有功能。
读者奖金
SQL 查询练习的演示数据
下载