从 1 到 10，您的数据仓库技能有多好？ 想要超过 7/10？那么这篇文章就是为你准备的。 你的 SQL 有多好？想尽快准备好面试？ 这篇博文详细解释了最复杂的数据仓库 SQL 技术。我将使用 BigQuery 标准 SQL 方言写下关于这个主题的一些想法。 1.增量表和MERGE 更新表很重要。这确实很重要。理想情况是当您拥有主键、唯一整数和自动递增的事务时。这种情况下的表更新很简单： https://gist.github.com/mshakhomirov/18775cbbe8288af864ad79247c0de63d?embedable=true#file-1-1-sql 在现代数据仓库中处理非规范化星型模式数据集时，情况并非总是如此。您的任务可能是使用 SQL 创建 和/或仅使用一部分数据增量更新数据集。 可能不存在，但您必须处理唯一键取决于已知的最新 （或时间戳）的数据模型。例如， 数据集中的 取决于最新的已知连接时间戳。在这种情况下，您可能希望 现有用户并 新用户。 会话 transaction_id transaction_id last_online user_id update insert MERGE 和增量更新 您可以使用 ，也可以将操作拆分为两个操作。一种用新记录更新现有记录，另一种插入不存在的全新记录（LEFT JOIN 情况）。 MERGE 是关系数据库中常用的语句。 Google BigQuery MERGE 命令是一种数据操作语言 (DML) 语句。它通常用于在一条语句中以原子方式执行三个主要功能。这些函数是 UPDATE、INSERT 和 DELETE。 MERGE 当两个或多个数据匹配时，可以使用 UPDATE 或 DELETE 子句。 当两个或多个数据不同且不匹配时，可以使用 INSERT 子句。 当给定数据与源不匹配时，也可以使用 UPDATE 或 DELETE 子句。 这意味着 Google BigQuery MERGE 命令使您能够通过更新、插入和删除 Google BigQuery 表中的数据来合并 Google BigQuery 数据。 考虑这个 SQL： https://gist.github.com/mshakhomirov/5ad1a7518c54bc030d1c78b56fe3cf82?embedable=true#file-1-2-sql 2. 数词 执行 UNNEST() 并检查您需要的词是否在您需要的列表中可能在许多情况下很有用，即数据仓库情绪分析： https://gist.github.com/mshakhomirov/694e040539b0d1b556f8e053d315a3bf?embedable=true#file-2-sql 3. 在 SELECT 语句之外使用 IF() 语句 这使我们有机会节省一些代码行并在代码方面更加雄辩。通常你会想把它放到一个子查询中，并在 子句中添加一个过滤器，但你可以 做： where 这样 https://gist.github.com/mshakhomirov/933e6a358e49dcccd4e547a5509c8fda?embedable=true#file-3-sql 另一个示例如何 将其用于 表。 。这是一个不好的例子，因为匹配的表后缀可能是动态确定的（基于表中的某些内容），您 不 分区 不要这样做 将被收取全表扫描费用。 https://gist.github.com/mshakhomirov/1c62d79cd9690140c569cd047b9d491f?embedable=true#file-3-2-sql 您还可以在 子句和 函数中使用它。 HAVING AGGREGATE 4. 使用 GROUP BY ROLLUP ROLLUP 函数用于在多个级别执行聚合。当您必须使用维度图时，这很有用。 以下查询按 子句中指定的交易类型 (is_gift) 返回每天的总积分支出，它还显示每天的总支出以及所有可用日期的总支出。 where https://gist.github.com/mshakhomirov/4cf738aaad967fe92c4fb7192874fadf?embedable=true#file-4-sql 5.将表转换为JSON 假设您需要将表转换为 JSON 对象，其中每条记录都是嵌套数组的一个元素。这是 函数有用的地方： to_json_string() https://gist.github.com/mshakhomirov/aac1f93312ae305ba80c915fe4a2a386?embedable=true#file-5-sql 然后您可以在任何地方使用它：日期、营销渠道、指数、直方图等。 6.使用分区 给定 、 和 列。对于每个日期，您如何在保留所有行的同时显示每个客户的总收入值？你可以这样实现： user_id date total_cost https://gist.github.com/mshakhomirov/e4f11721eb5a3182150df08f25b70d64?embedable=true#file-6-sql 7.移动平均线 BI 开发人员的任务通常是在他们的报告和出色的仪表盘中添加移动平均线。这可能是 7、14、30 天/月甚至年 MA 线图。那么我们该怎么做呢？ https://gist.github.com/mshakhomirov/ebf5488d0036bc9b84ae05889346d986?embedable=true#file-7-sql 8. 日期数组 当您处理 或想要检查某些数据集是否存在缺失值（即日期）时，它会变得非常方便。 BigQuery 有一个名为 函数： 用户保留 GENERATE_DATE_ARRAY https://gist.github.com/mshakhomirov/2ba5a67053f85794462dab98e56ad74d?embedable=true#file-8-sql 9.行号() 这对于从您的数据中获取最新信息很有用，即最新更新的记录等，甚至可以删除重复项： https://gist.github.com/mshakhomirov/05d0c04c5975207d98552ffd436add8b?embedable=true#file-9-sql 10.NTILE() 另一个编号功能。如果您有移动应用程序，这对于监控诸如 之类的事情非常有用。例如，我将我的应用程序连接到 Firebase，当用户 时，我可以看到他们花了多长时间。 Login duration in seconds login 此函数根据行顺序将行划分为 存储桶，并返回分配给每行的从 1 开始的存储桶编号。桶中的行数最多可以相差 1。余数值（行数除以桶的余数）从桶 1 开始分配给每个桶。如果 计算结果为 NULL、0 或负数，提供了一个错误。 constant_integer_expression constant_integer_expression https://gist.github.com/mshakhomirov/16fe941aa8c4ed79e4aad8b7049b307a?embedable=true#file-10-sql 11.排名/dense_rank 它们也称为 函数。我倾向于使用 ，因为它不会跳过下一个可用排名，而 会。它返回连续的排名值。您可以将它与将结果分成不同的桶的分区一起使用。如果每个分区中的行具有相同的值，它们将获得相同的排名。 编号 DENSE_RANK 作为默认排名函数 RANK 例子： https://gist.github.com/mshakhomirov/459b68c5f3d1e8284c01e516db1d8dcb?embedable=true#file-11-1-sql 产品价格的另一个例子： https://gist.github.com/mshakhomirov/4c90a6fc8516d8264e172676a83a1048?embedable=true#file-11-2-sql 12. 旋转/旋转 Pivot 将行更改为列。这就是它所做的一切。 Unpivot 做 事情。 相反的 https://gist.github.com/mshakhomirov/f90b035ba259e672d4d51a669e0cd1fc?embedable=true#file-12-sql 13. 首值/末值 这是另一个有用的函数，它有助于获取每一行相对于该特定分区中第一个/最后一个值的增量。 https://gist.github.com/mshakhomirov/ea4de9144b97bf8c196cab07609c309e?embedable=true#file-13-sql 14. 将表转换为结构数组并将它们传递给 UDF 当您需要将具有某些复杂逻辑的用户定义函数 (UDF) 应用于每一行或一个表时，这非常有用。您始终可以将您的表视为一组 TYPE STRUCT 对象，然后将它们中的每一个传递给 UDF。这取决于你的逻辑。例如，我用它来计算购买过期时间： https://gist.github.com/mshakhomirov/35d956fa9db86b12b44ab62c00f42a40?embedable=true#file-14-sql 以类似的方式，您可以创建表而无需使用 。例如，我用它来模拟单元测试的一些测试数据。这样，您只需在编辑器中使用 + + 即可非常快速地完成此操作。 UNION ALL Alt Shift Down https://gist.github.com/mshakhomirov/6ea226c1b5b789d4a31691ce065c20d7?embedable=true#file-14-2-sql 15. 使用 FOLLOWING 和 UNBOUNDED FOLLOWING 创建事件漏斗 营销渠道就是一个很好的例子。您的数据集可能包含不断重复的相同类型的事件，但理想情况下您希望将每个事件与下一个不同类型的事件链接起来。当您需要获取某项列表（即事件、购买等）以构建渠道数据集时，这可能很有用。使用 PARTITION BY 它可以让您有机会对所有以下事件进行分组，而不管每个分区中存在多少事件。 https://gist.github.com/mshakhomirov/05fd7d79d8acf3b173181a5d950ab6e7?embedable=true#file-15-sql 16.正则表达式 如果您需要从非结构化数据中提取某些内容，例如外汇汇率、自定义分组等，您会使用它。 使用正则表达式处理货币汇率 考虑这个带有汇率数据的例子： https://gist.github.com/mshakhomirov/9ca6e153da19c491034bd57995875308?embedable=true#file-16-1-sql 使用正则表达式处理应用程序版本 有时您可能想使用 为您的应用程序获取 版本、 或 版本，并创建自定义报告： regexp 主要 发布版本 修改 https://gist.github.com/mshakhomirov/b1f442a296ffef52c7baa1245e1dc316?embedable=true#file-16-2-sql 结论 SQL 是一种有助于操作数据的强大工具。希望这些来自数字营销的 SQL 用例对您有用。这确实是一项方便的技能，可以帮助您完成许多项目。这些 SQL 片段让我的生活变得轻松多了，我几乎每天都在工作中使用它们。此外，SQL 和现代数据仓库是数据科学的必备工具。其强大的方言功能允许轻松建模和可视化数据。因为 SQL 是数据仓库和商业智能专业人员使用的语言，所以如果您想与他们共享数据，它是一个很好的选择。这是与市场上几乎所有数据仓库/湖解决方案进行通信的最常见方式。 最初由 发表于 datamike mydataschool.com Mike 是一个充满激情且专注于数字化的人，他充满干劲和热情，热爱数字营销的全面组合所带来的挑战。住在英国，2015 年获得纽卡斯尔大学 MBA 学位。

Read me on Medium.com

該音頻是用故事的原始語言製作的！

太長; 讀書

Making security fun one episode at a time

每个初学者都需要了解的 16 种 SQL 技术

太長; 讀書

Mike Shakhomirov

About Author

標籤

Languages

这篇文章刊登在...

每个初学者都需要了解的 16 种 SQL 技术

太長; 讀書

Mike Shakhomirov

About Author

標籤

Languages

这篇文章刊登在...

相關故事