当前位置: 首页 > news >正文

高级 SQL 技巧讲解

在这里插## 标题入图片描述

大家好,我是程序员小羊!

前言:

SQL(结构化查询语言)是管理和操作数据库的核心工具。从基本的查询语句到复杂的数据处理,掌握高级 SQL 技巧不仅能显著提高数据分析的效率,还能解决业务中的复杂问题。本文将深入探讨一些高级 SQL 技巧,包括窗口函数、复杂联结、CTE(公用表表达式)、递归查询、动态 SQL、性能优化等。


一、窗口函数(Window Functions)

窗口函数是一种强大的分析工具,能够在不改变原始数据行的情况下进行聚合运算和排名操作。

1. 基本语法

窗口函数通常与关键字 OVER() 一起使用,用于指定窗口范围。

SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_department
FROM employees;

解释

  • RANK():对每个部门(department_id)内的员工按薪资从高到低排名。
  • PARTITION BY:指定分组范围。
  • ORDER BY:定义排序逻辑。
2. 常用窗口函数
  • 排名函数ROW_NUMBER()RANK()DENSE_RANK() 用于对数据分组并排序。
  • 聚合函数SUM()AVG()COUNT() 等可以按分组计算累计值。
  • 滑动窗口计算:使用 ROWS BETWEENRANGE BETWEEN 定义滑动范围,如计算滚动平均值:
    SELECT order_date, customer_id, SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_sum
    FROM orders;
    

二、复杂联结(Complex Joins)

SQL 中的联结是数据处理的核心之一,高级 SQL 技巧往往体现在复杂联结的使用上。

1. 多表联结

当涉及多个表时,联结逻辑需要更清晰,避免数据重复或遗漏:

SELECT a.customer_name, b.order_id, c.product_name
FROM customers a
JOIN orders b ON a.customer_id = b.customer_id
JOIN products c ON b.product_id = c.product_id
WHERE c.category = 'Electronics';

解释:多表联结根据外键关系查询数据,例如查询购买电子产品的客户信息。

2. 自联结(Self Join)

自联结用于将一个表与自身进行关联,常用于层级数据查询:

SELECT e1.employee_id AS employee, e2.employee_id AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

解释:查询员工及其直接经理的信息。

3. 交叉联结(Cross Join)

交叉联结生成笛卡尔积,适用于所有组合的情况。

SELECT a.customer_name, b.product_name 
FROM customers a
CROSS JOIN products b;

注意:在实际使用中,需要限制结果集以免数据过多。


三、公用表表达式(CTE)

CTE 是提升代码可读性的重要工具,尤其在需要多步查询时表现出色。

1. 基本用法
WITH CTE_sales AS (SELECT employee_id, SUM(sale_amount) AS total_salesFROM salesGROUP BY employee_id
)
SELECT * 
FROM CTE_sales 
WHERE total_sales > 10000;

解释

  • WITH 定义 CTE 子查询,为后续查询提供简化视图。
  • 适合复杂查询分步骤实现。
2. 嵌套 CTE

可以通过嵌套多个 CTE 来处理复杂逻辑:

WITH CTE_orders AS (SELECT customer_id, SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id
),
CTE_high_spenders AS (SELECT customer_id FROM CTE_orders WHERE total_amount > 10000
)
SELECT * 
FROM customers 
WHERE customer_id IN (SELECT customer_id FROM CTE_high_spenders);

四、递归查询

递归查询主要用于处理树形结构或分层数据。

1. 递归 CTE
WITH RECURSIVE employee_hierarchy AS (SELECT employee_id, manager_id, 1 AS levelFROM employeesWHERE manager_id IS NULLUNION ALLSELECT e.employee_id, e.manager_id, h.level + 1FROM employees eJOIN employee_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * 
FROM employee_hierarchy;

解释

  • RECURSIVE 允许 CTE 自身引用。
  • 此例从顶级经理开始,递归查找所有下属及其层级关系。

五、动态 SQL

动态 SQL 允许根据输入动态生成查询,常用于存储过程或复杂查询中。

1. 动态拼接 SQL

在某些情况下需要根据用户输入生成动态 SQL:

DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM ' + @table_name + ' WHERE ' + @column_name + ' = @value';
EXEC sp_executesql @sql, N'@value NVARCHAR(100)', @value = 'example';

注意:确保输入经过验证,避免 SQL 注入风险。


六、性能优化技巧

SQL 性能优化对于处理大规模数据尤为重要。以下是一些常用的优化技巧:

1. 索引的使用
  • 单列索引与复合索引:根据查询条件创建合适的索引。例如:
    CREATE INDEX idx_customer_name ON customers (customer_name);
    
  • 避免全表扫描:通过索引提高过滤和排序的效率。
2. 分析执行计划

通过 EXPLAIN 或类似工具分析查询执行计划,定位瓶颈:

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
3. 避免不必要的复杂查询
  • 减少嵌套查询:使用联结替代子查询,提高效率。
  • 选择性查询:仅查询必要的列,避免使用 SELECT *
4. 分区与分片

对于大表,可以使用分区优化查询性能:

CREATE TABLE orders_partitioned (order_id INT,customer_id INT,order_date DATE
)
PARTITION BY RANGE (order_date) (PARTITION p1 VALUES LESS THAN ('2024-01-01'),PARTITION p2 VALUES LESS THAN ('2025-01-01')
);
5. 缓存查询结果

对于频繁使用的查询结果,可以缓存到中间表或物化视图中:

CREATE MATERIALIZED VIEW recent_orders AS 
SELECT * 
FROM orders 
WHERE order_date > CURRENT_DATE - INTERVAL '30 days';

七、事务管理

1. 事务的基本操作

SQL 中的事务保证数据操作的一致性:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

注意:在多步操作中加入 ROLLBACK 逻辑处理失败场景。

2. 隔离级别的选择

根据业务需求选择合适的隔离级别:

  • READ UNCOMMITTED:允许脏读,性能最高。
  • READ COMMITTED:禁止脏读,默认级别。
  • REPEATABLE READ:避免不可重复读。
  • SERIALIZABLE:完全隔离,性能最低。

总结

高级 SQL 技巧涵盖了从分析、建模到性能优化的方方面面。通过窗口函数进行复杂分析、使用 CTE 提高可读性、递归查询处理层级数据、动态
SQL
提高灵活性,开发者能够高效解决业务中的各种复杂需求。同时,关注性能优化和事务管理是处理大规模数据时不可忽视的关键环节。掌握这些高级技巧,能让你在数据处理中更加得心应手,充分发挥
SQL 的强大能力。

结尾

今天这篇文章就到这里了,大厦之成,非一木之材也;大海之阔,非一流之归也。感谢大家观看本文

在这里插入图片描述

在这里插入图片描述

相关文章:

高级 SQL 技巧讲解

​ 大家好,我是程序员小羊! 前言: SQL(结构化查询语言)是管理和操作数据库的核心工具。从基本的查询语句到复杂的数据处理,掌握高级 SQL 技巧不仅能显著提高数据分析的效率,还能解决业务中的复…...

浅论AI大模型在电商行业的发展未来

随着人工智能(AI)技术的快速发展,AI大模型在电商行业中扮演着越来越重要的角色。本文旨在探讨AI大模型如何赋能电商行业,包括提升销售效率、优化用户体验、增强供应链管理等方面。通过分析AI大模型在电商领域的应用案例和技术进展…...

【python笔记03】《类》

文章目录 面向对象基本概念对象的概念类的概念 类的定义类的创建(实例的模板)类的实例化--获取对象对象方法中的self关键字面试题请描述什么是对象,什么是类。请观阅读如下代码,判断是否能正常运行,如果不能正常运行&a…...

Flutter 应用在真机上调试的流程

在真机上调试 Flutter 应用的方法有很多,可以使用 USB 数据线连接设备到电脑进行调试,也可以通过无线方式进行 Flutter 真机调试。 1. 有线调试 设备准备 启用开发者模式: Android:进入 设置 > 关于手机,连续点击…...

以太坊基础知识结构详解

以太坊的历史和发展 初创阶段 2013年:Vitalik Buterin 发表了以太坊白皮书,提出了一个通用的区块链平台,不仅支持比特币的货币功能,还能支持更复杂的智能合约。2014年:以太坊项目启动,进行了首次ICO&…...

安全见闻(完整版)

目录 安全见闻1 编程语言和程序 编程语言 函数式编程语言: 数据科学和机器学习领域: Web 全栈开发: 移动开发: 嵌入式系统开发: 其他: 编程语言的方向: 软件程序 操作系统 硬件设备…...

LeetCode100之反转链表(206)--Java

1.问题描述 给你单链表的头节点 head ,请你反转链表,并返回反转后的链表 示例1 输入:head [1,2,3,4,5] 输出:[5,4,3,2,1] 示例2 输入:head [1,2] 输出:[2,1] 示例3 输入:head [] 输…...

牛客周赛第一题2024/11/17日

链接:登录—专业IT笔试面试备考平台_牛客网 来源:牛客网 时间限制:C/C/Rust/Pascal 1秒,其他语言2秒 空间限制:C/C/Rust/Pascal 256 M,其他语言512 M 64bit IO Format: %lld 题目描述 小红这天来到了三…...

麒麟Server下安装东方通TongLINK/Q

环境 系统:麒麟Server SP3 2403 应用:TLQ8.1(Install_TLQ_Standard_Linux2.6.32_x86_64_8.1.17.0.tar.gz) 安装Server 将文件解压到/usr/local/tlq。 cd /opt/tlq/ mkdir /usr/local/tlq/ tar -zxvf Install_TLQ_Standard_Linux2.6.32_x86_64_8.1.1…...

BERT的中文问答系统33

我们在现有的代码基础上增加网络搜索的功能。我们使用 requests 和 BeautifulSoup 来从百度搜索结果中提取信息。以下是完整的代码,包括项目结构、README.md 文件以及所有必要的代码。 项目结构 xihe241117/ ├── data/ │ └── train_data.jsonl ├── lo…...

Ubuntu下的Eigen库的安装及基本使用教程

一、Eigen库介绍 简介 Eigen [1]目前最新的版本是3.4,除了C标准库以外,不需要任何其他的依赖包。Eigen使用的CMake建立配置文件和单元测试,并自动安装。如果使用Eigen库,只需包特定模块的的头文件即可。 基本功能 Eigen适用范…...

【spring 】Spring Cloud Gateway 的Filter学习

介绍和使用场景 Spring Cloud Gateway 是一个基于 Spring Framework 5 和 Project Reactor 的 API 网关,它旨在为微服务架构提供一种简单而有效的方式来处理请求路由、过滤、限流等功能。在 Spring Cloud Gateway 中,Filter 扮演着非常重要的角色&#…...

每秒交易数(Transactions Per Second:TPS)详细拆解

每秒交易数(TPS)是指计算机网络每秒可以处理的交易数量。TPS是衡量不同区块链和其他计算机系统速度的关键指标。然而,TPS并不是用来衡量区块链速度的唯一指标。许多人认为,虽然TPS很重要,但最终性实际上是一个更重要的…...

【初阶数据结构与算法】链表刷题之链表分割、相交链表、环形链表1、环形链表I、环形链表II

文章目录 一、链表分割二、相交链表三、环形链表I四、环形链表|| 一、链表分割 题目链接:https://www.nowcoder.com/practice/0e27e0b064de4eacac178676ef9c9d70 我们来看看链表分割的题目描述和它给出的函数:    这个题虽然是以C形式来做&#xff0…...

【STL】set,multiset,map,multimap的介绍以及使用

关联式容器 在C的STL中包含序列式容器和关联式容器 1.关联式容器:它里面存储的是元素本身,其底层是线性序列的数据结构,比如:vector,list,deque,forward_list(C11)等 2.关联式容器里面储存的…...

新能源二手车交易量有望破百万,二手车市场回暖了吗?

这些年,伴随着新能源汽车市场的高速发展,各种新能源车的二手车也在逐渐增加,不过之前的二手车市场相对比较冷清,就在最近一则新闻传出新能源二手车交易量有望破百万,二手车市场这是回暖了吗? 一、新能源二手…...

哈佛商业评论 | 项目经济的到来:组织变革与管理革新的关键

在21世纪,项目经济(Project Economy)逐步取代传统运营,成为全球经济增长的核心动力。项目已不再是辅助工具,而是推动创新和变革的重要载体。然而,只有35%的项目能够成功,显示出项目管理领域存在巨大的改进空间。本文将详细探讨项目经济的背景、项目管理的挑战,以及适应…...

web浏览器环境下使用window.open()打开PDF文件不是预览,而是下载文件?

如果你使用 window.open() 方法打开 PDF 文件,但浏览器不是预览而是下载文件,这可能是由于以下几个原因: 服务器配置:服务器可能将 PDF 文件配置为下载而不是预览。例如,服务器可能设置了 Content-Disposition 响应头…...

【GeekBand】C++设计模式笔记12_Singleton_单件模式

1. “对象性能” 模式 面向对象很好地解决了 “抽象” 的问题, 但是必不可免地要付出一定的代价。对于通常情况来讲,面向对象的成本大都可以忽略不计。但是某些情况,面向对象所带来的成本必须谨慎处理。典型模式 SingletonFlyweight 2. Si…...

Pyhon基础数据结构(列表)【蓝桥杯】

a [1,2,3,4,5] a.reverse() print("a ",a) a.reverse() print("a ",a)# 列表 列表(list)有由一系列按照特定顺序排序的元素组成 列表是有顺序的,访问任何元素需要通过“下标访问” 所谓“下标”就是指元素在列表从左…...

无法与IP建立连接,未能下载VSCode服务器

如题,在远程连接服务器的时候突然遇到了这个提示。 查阅了一圈,发现是VSCode版本自动更新惹的祸!!! 在VSCode的帮助->关于这里发现前几天VSCode自动更新了,我的版本号变成了1.100.3 才导致了远程连接出…...

1688商品列表API与其他数据源的对接思路

将1688商品列表API与其他数据源对接时,需结合业务场景设计数据流转链路,重点关注数据格式兼容性、接口调用频率控制及数据一致性维护。以下是具体对接思路及关键技术点: 一、核心对接场景与目标 商品数据同步 场景:将1688商品信息…...

Java - Mysql数据类型对应

Mysql数据类型java数据类型备注整型INT/INTEGERint / java.lang.Integer–BIGINTlong/java.lang.Long–––浮点型FLOATfloat/java.lang.FloatDOUBLEdouble/java.lang.Double–DECIMAL/NUMERICjava.math.BigDecimal字符串型CHARjava.lang.String固定长度字符串VARCHARjava.lang…...

相机从app启动流程

一、流程框架图 二、具体流程分析 1、得到cameralist和对应的静态信息 目录如下: 重点代码分析: 启动相机前,先要通过getCameraIdList获取camera的个数以及id,然后可以通过getCameraCharacteristics获取对应id camera的capabilities(静态信息)进行一些openCamera前的…...

Springcloud:Eureka 高可用集群搭建实战(服务注册与发现的底层原理与避坑指南)

引言:为什么 Eureka 依然是存量系统的核心? 尽管 Nacos 等新注册中心崛起,但金融、电力等保守行业仍有大量系统运行在 Eureka 上。理解其高可用设计与自我保护机制,是保障分布式系统稳定的必修课。本文将手把手带你搭建生产级 Eur…...

解读《网络安全法》最新修订,把握网络安全新趋势

《网络安全法》自2017年施行以来,在维护网络空间安全方面发挥了重要作用。但随着网络环境的日益复杂,网络攻击、数据泄露等事件频发,现行法律已难以完全适应新的风险挑战。 2025年3月28日,国家网信办会同相关部门起草了《网络安全…...

Caliper 配置文件解析:fisco-bcos.json

config.yaml 文件 config.yaml 是 Caliper 的主配置文件,通常包含以下内容: test:name: fisco-bcos-test # 测试名称description: Performance test of FISCO-BCOS # 测试描述workers:type: local # 工作进程类型number: 5 # 工作进程数量monitor:type: - docker- pro…...

Kafka主题运维全指南:从基础配置到故障处理

#作者:张桐瑞 文章目录 主题日常管理1. 修改主题分区。2. 修改主题级别参数。3. 变更副本数。4. 修改主题限速。5.主题分区迁移。6. 常见主题错误处理常见错误1:主题删除失败。常见错误2:__consumer_offsets占用太多的磁盘。 主题日常管理 …...

DeepSeek源码深度解析 × 华为仓颉语言编程精粹——从MoE架构到全场景开发生态

前言 在人工智能技术飞速发展的今天,深度学习与大模型技术已成为推动行业变革的核心驱动力,而高效、灵活的开发工具与编程语言则为技术创新提供了重要支撑。本书以两大前沿技术领域为核心,系统性地呈现了两部深度技术著作的精华:…...

webpack面试题

面试题:webpack介绍和简单使用 一、webpack(模块化打包工具)1. webpack是把项目当作一个整体,通过给定的一个主文件,webpack将从这个主文件开始找到你项目当中的所有依赖文件,使用loaders来处理它们&#x…...