数仓SQL如何做code review?

2024-06-08 01:52
文章标签 sql code 数仓 database review

本文主要是介绍数仓SQL如何做code review?,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

第一步应该是先明确需求,明确完需求以后在进行开发,接着code review

在明确HiveSQL、SparkSQL的编写需求后,接下来将详细介绍代码审查(Code Review)时的一些关键注意点:

1. 关联关系

left join 和 join

在进行表关联时,需判断是使用外连接(outer join)还是内连接(join),这主要取决于是否需要包含那些在原表中存在但在关联表中没有匹配项的记录。

场景:我们有两个表,一个是用户订单表(orders),一个是用户信息表(users)。我们想要查询所有订单及其对应的用户信息,包括那些没有用户信息的订单。

外连接(outer join):如果我们想要包含那些在原表(orders)中存在但在关联表(users)中没有匹配项的记录,我们应该使用左外连接(LEFT OUTER JOIN)。

SELECT * FROM orders o LEFT OUTER JOIN users u ON o.user_id = u.id;

这样,即使某个订单没有对应的用户信息,它也会出现在结果集中,但用户信息相关字段将为NULL。

内连接(join):如果我们只关心那些既有订单又有用户信息的记录,我们应该使用内连接(INNER JOIN)。

SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

这样,只有那些在两个表中都有匹配项的记录才会出现在结果集中。

关联条件是否匹配

检查关联条件(on子句)中,两边的数据类型是否匹配,以避免类型不匹配导致的数据错误。

场景:我们有两个表,一个是商品表(products),包含商品ID(product_id,整数类型)和商品价格(price),另一个是订单明细表(order_details),包含商品ID(product_id,字符串类型)和购买数量(quantity)。

  • 数据类型不匹配:如果直接关联这两个表,可能会因为数据类型不匹配而导致错误或不正确的结果。

– 假设products.product_id是INT类型,而order_details.product_id是VARCHAR类型

SELECT * FROM products p JOIN order_details od ON p.product_id = od.product_id; 
-- 可能导致错误
    • 为避免这种情况,需要确保关联条件中两边的数据类型一致,或者在关联时进行显式类型转换。

关联是否发散

  • 当关联关系为一对一时,必须验证两个表的关联键是否具有唯一性。如果不唯一,关联操作可能会产生笛卡尔积,从而导致数据膨胀。

  • 场景:我们有两个表,员工表(employees)和部门表(departments),想要通过部门ID(department_id)关联这两个表,以查询每个员工所属的部门名称。

关联键不唯一:如果departments表中的department_id不是唯一的,那么与employees表关联时可能会产生笛卡尔积,导致数据膨胀。

-- 假设departments.department_id不是唯一的  
SELECT * FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id; -- 可能产生笛卡尔积
    • 为避免这种情况,需要确保关联键在两个表中都是唯一的。

2. 过滤条件

  • 验证WHERE子句中的过滤条件是否正确。

and 与 or

场景:我们有一个订单表(orders),包含订单状态(status)和订单金额(amount)。我们想要查询状态为“已完成”(status=‘completed’)且金额大于100的订单。

过滤条件不正确:如果WHERE子句的过滤条件设置不正确,可能会导致查询结果不符合预期。

-- 假设我们想要查询状态为“已完成”且金额大于100的订单  
SELECT * FROM orders WHERE status = 'completed' OR amount > 100; 
-- 这里的OR应该是AND

上述查询中使用了OR而不是AND,导致查询结果包含了所有状态为“已完成”的订单以及所有金额大于100的订单,而不是同时满足这两个条件的订单。为了得到正确的结果,应该将OR改为AND。

not in

当使用 NOT IN 子句时,需要注意的是,如果子查询中的列包含 NULL 值,那么整个 NOT IN 条件可能不会按预期工作,因为 NULL 与任何值的比较都会返回 NULL,而不是 TRUEFALSE。这可能导致查询结果中排除了一些本应包含的记录。

下面举两个例子来说明这个问题:

**例子 1:子查询中没有 NULL

假设我们有两个表:orders(订单表)和 cancelled_orders(已取消订单表)。我们想要查询所有未取消的订单。

SELECT order_id FROM orders WHERE order_id NOT IN (SELECT order_id FROM cancelled_orders);

如果 cancelled_orders 表中没有 NULL 值,这个查询将正常工作,返回所有未在 cancelled_orders 表中出现的 order_id

例子 2:子查询中包含 NULL

现在,假设 cancelled_orders 表中有一个 order_idNULL 的记录。如果我们再次运行上面的查询,结果可能不是我们所期望的。

SELECT order_id FROM orders 
WHERE order_id NOT IN (SELECT order_id FROM cancelled_orders);

在这个情况下,如果 cancelled_orders 表中有一个或多个 order_idNULL,那么整个 NOT IN 子句可能会返回空集,因为任何值与 NULL 的比较都会返回 NULL,导致 NOT IN 条件不成立。

为了解决这个问题,可以在子查询中排除 NULL 值,或者改用其他方法,如 NOT EXISTSLEFT JOIN

SELECT order_id FROM orders o 
WHERE NOT EXISTS ( SELECT 1 FROM cancelled_orders co WHERE co.order_id = o.order_id );

或者使用 LEFT JOIN

SELECT o.order_id FROM orders o LEFT JOIN cancelled_orders co ON o.order_id = co.order_id WHERE co.order_id IS NULL;

不过我经常用的方式是

SELECT column_name FROM table_name WHERE column_name NOT IN ( SELECT COALESCE(nullable_column, 'some_default_value') FROM another_table );

COALESCE 函数返回其参数列表中的第一个非 NULL 值。在处理 NOT IN 子句时,如果担心子查询可能返回 NULL 值,可以使用 COALESCE 来确保 NULL 被替换为一个不会与实际数据冲突的默认值。

3. 指标的统计口径处理

在统计数据指标时,需要明确两个基本概念:

  • 可累加指标,如支付金额、浏览量等,这些指标可以通过简单的数值相加来统计。在SQL中,通常使用SUM函数来处理这类指标。

  • 不可累加指标,如访客数,这类指标不能通过简单相加来统计,而需要先进行去重处理,然后再求和。在SQL中,这类指标通常使用COUNT(DISTINCT)函数来统计。

可累加指标的例子

  1. 支付金额

假设我们有一个订单表(orders),其中包含每笔订单的支付金额(payment_amount)。要统计总支付金额,我们可以使用SUM函数:

SELECT SUM(payment_amount) AS total_payment_amount FROM orders;
  1. 浏览量

如果我们有一个页面浏览记录表(page_views),其中包含每次页面浏览的记录,我们可以统计总浏览量如下:

SELECT SUM(views) AS total_views FROM page_views;

或者,如果每条记录代表一次浏览,我们可以这样统计:

SELECT COUNT(*) AS total_views FROM page_views;

不可累加指标的例子

  1. 访客数

假设我们有一个用户访问记录表(user_visits),其中包含用户的访问信息,包括用户ID(user_id)和访问时间(visit_time)。由于同一个用户可能在一天内多次访问,因此我们需要先对用户ID进行去重,然后再统计唯一的访客数量:

SELECT COUNT(DISTINCT user_id) AS unique_visitors FROM user_visits;
  1. 独立IP数

如果我们想要统计访问网站的独立IP数量,这也是一个不可累加的指标。假设我们有一个记录每个访问请求的IP地址的表(access_logs),我们可以这样统计:

SELECT COUNT(DISTINCT ip_address) AS unique_ips FROM access_logs;

在处理不可累加指标时,使用COUNT(DISTINCT column_name)是确保每个项只被计数一次的关键。这种方法在数据库查询中非常有用,特别是当需要准确统计唯一实体(如唯一用户、唯一IP等)的数量时。

4. insert插入数据

这个出现在hivesql、sparksql中

  • 是否支持重跑。等价于看插入时是否有overwrite关键字,如果没有该关键字,重跑数据(多次执行该工作流)时不会覆盖脏数据,而是增量往表插入数据,进而可能会导致最终数据统计翻倍。

这个主要出现在hivesql中

  • 插入的数据顺序和被插入表结构顺序是否完全一致。我们要保证数据字段写入顺序没有出错,否则会导致插入值错乱。

这篇关于数仓SQL如何做code review?的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1040914

相关文章

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更

MySQL深分页进行性能优化的常见方法

《MySQL深分页进行性能优化的常见方法》在Web应用中,分页查询是数据库操作中的常见需求,然而,在面对大型数据集时,深分页(deeppagination)却成为了性能优化的一个挑战,在本文中,我们将... 目录引言:深分页,真的只是“翻页慢”那么简单吗?一、背景介绍二、深分页的性能问题三、业务场景分析四、

MySQL 迁移至 Doris 最佳实践方案(最新整理)

《MySQL迁移至Doris最佳实践方案(最新整理)》本文将深入剖析三种经过实践验证的MySQL迁移至Doris的最佳方案,涵盖全量迁移、增量同步、混合迁移以及基于CDC(ChangeData... 目录一、China编程JDBC Catalog 联邦查询方案(适合跨库实时查询)1. 方案概述2. 环境要求3.

SQL server数据库如何下载和安装

《SQLserver数据库如何下载和安装》本文指导如何下载安装SQLServer2022评估版及SSMS工具,涵盖安装配置、连接字符串设置、C#连接数据库方法和安全注意事项,如混合验证、参数化查... 目录第一步:打开官网下载对应文件第二步:程序安装配置第三部:安装工具SQL Server Manageme

C#连接SQL server数据库命令的基本步骤

《C#连接SQLserver数据库命令的基本步骤》文章讲解了连接SQLServer数据库的步骤,包括引入命名空间、构建连接字符串、使用SqlConnection和SqlCommand执行SQL操作,... 目录建议配合使用:如何下载和安装SQL server数据库-CSDN博客1. 引入必要的命名空间2.

全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

《全面掌握SQL中的DATEDIFF函数及用法最佳实践》本文解析DATEDIFF在不同数据库中的差异,强调其边界计算原理,探讨应用场景及陷阱,推荐根据需求选择TIMESTAMPDIFF或inte... 目录1. 核心概念:DATEDIFF 究竟在计算什么?2. 主流数据库中的 DATEDIFF 实现2.1

MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

《MySQL多列IN查询之语法、性能与实战技巧(最新整理)》本文详解MySQL多列IN查询,对比传统OR写法,强调其简洁高效,适合批量匹配复合键,通过联合索引、分批次优化提升性能,兼容多种数据库... 目录一、基础语法:多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析

MySQL中的LENGTH()函数用法详解与实例分析

《MySQL中的LENGTH()函数用法详解与实例分析》MySQLLENGTH()函数用于计算字符串的字节长度,区别于CHAR_LENGTH()的字符长度,适用于多字节字符集(如UTF-8)的数据验证... 目录1. LENGTH()函数的基本语法2. LENGTH()函数的返回值2.1 示例1:计算字符串