高级SQL技巧
高级SQL技巧涵盖了许多方面,包括但不限于窗口函数、递归查询、公共表表达式(CTEs)、子查询、集合操作、临时函数、日期时间操作、索引优化等。以下是对这些技巧的详细讲解和示例。
窗口函数
窗口函数是一种特殊的SQL函数,能够在一组行(称为窗口)上执行计算,但不会将结果合并到单个行中,这与聚合函数有所不同。窗口函数在处理排名、累计和运行总和等场景中非常有用。
基本语法
<窗口函数> OVER ([PARTITION BY <分区列>] [ORDER BY <排序列>])
PARTITION BY:用于将数据分成不同的组。ORDER BY:用于定义计算的顺序。
常见的窗口函数
ROW_NUMBER():为每一行分配一个唯一的序号。RANK():为每一行分配一个序号,序号间可能有跳跃。DENSE_RANK():为每一行分配一个序号,序号间无跳跃。LEAD()和LAG():访问同一组中前一行或后一行的数据。
示例
SELECT employee_id,department_id,salary,ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank,LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY salary DESC) AS previous_salary
FROM employees;
递归查询
递归查询是一种自引用的查询方式,常用于处理树形结构的数据,如组织架构、目录结构等。
基本语法
WITH RECURSIVE cte_name AS (初始查询UNION ALL递归查询
)
SELECT * FROM cte_name;
示例
WITH RECURSIVE EmployeeCTE AS (SELECT employee_id, manager_id, 1 AS level FROM employees WHERE manager_id IS NULLUNION ALLSELECT e.employee_id, e.manager_id, ecte.level + 1 FROM employees e INNER JOIN EmployeeCTE ecte ON e.manager_id = ecte.employee_id
)
SELECT employee_id, employee_name, level
FROM EmployeeCTE
ORDER BY level, employee_id;
公共表表达式(CTEs)
CTE是一种临时的结果集,其定义只在单个查询的执行周期内有效。CTE能使复杂查询更易读、易维护。
基本语法
WITH cte_name AS (查询语句
)
SELECT * FROM cte_name;
示例
WITH SalesCTE AS (SELECT employee_id, SUM(amount) AS total_sales FROM sales GROUP BY employee_id
)
SELECT employee_id, total_sales
FROM SalesCTE
WHERE total_sales > 10000;
子查询
子查询是嵌套在另一个查询中的查询,可以在SELECT、WHERE、FROM、HAVING子句中使用。子查询常用于筛选条件、数据过滤等场景。
示例
SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
集合操作
集合操作用于将两个或多个查询结果集进行合并或比较。常见的集合操作符包括UNION、INTERSECT、EXCEPT。
基本语法
SELECT column_list FROM table1
UNION [ALL]
SELECT column_list FROM table2;SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2;SELECT column_list FROM table1
EXCEPT
SELECT column_list FROM table2;
示例
-- 合并两个查询结果集
SELECT name FROM customers
UNION
SELECT name FROM suppliers;-- 找出两个查询结果集的交集
SELECT name FROM customers
INTERSECT
SELECT name FROM suppliers;-- 找出只在第一个查询结果集中存在的记录
SELECT name FROM customers
EXCEPT
SELECT name FROM suppliers;
临时函数
在支持的数据库中(如PostgreSQL),可以定义临时函数(存储过程或函数)来封装复杂的逻辑,增强代码重用性。
示例(PostgreSQL)
CREATE OR REPLACE FUNCTION calculate_discount(price NUMERIC, discount_rate NUMERIC)
RETURNS NUMERIC AS $$
BEGINRETURN price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;SELECT calculate_discount(100, 0.1);
日期时间操作
日期时间操作是数据库操作中必不可少的一部分。常见操作包括日期加减、日期格式转换等。
示例
-- 日期加减
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '1 month';-- 日期格式转换
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
索引优化
创建适当的索引可以显著提高查询性能。以下是一些索引优化的技巧:
- 对查询频繁使用的列创建合适的索引(单列索引、组合索引等)。
- 避免全表扫描:使用WHERE子句进行筛选,避免不必要的全表扫描。
- 使用EXPLAIN或EXPLAIN ANALYZE来查看SQL查询的执行计划:找到性能瓶颈。
- 对组合索引的选择:把选择性高的列放在索引的前面。
示例
-- 为department_id创建索引
CREATE INDEX idx_department_id ON employees(department_id);-- 使用EXPLAIN查看查询执行计划
EXPLAIN SELECT name FROM employees WHERE department_id = 10;
其他高级技巧
- 只查询所需的列:减少返回的数据量。
- 自联结:一个表与自身进行联结,常用来处理相对数据(例如,父子关系、前后项比较等)。
- 高级过滤与排序技巧:使用正则表达式进行过滤,多条件排序等。
- 分页时使用LIMIT:对于大偏移量的查询,可以通过索引或缓存减少开销。
- 批量插入:可以显著减少IO和锁的开销。
- 视图:虽然视图会增加查询的复杂性和性能开销,但在某些情况下,它们可以提供一种简化查询的方式。然而,对于复杂查询,直接写SQL可能更为高效。
- 锁机制:使用适当的锁机制,避免全表锁(如LOCK IN SHARE MODE)。
高级SQL技巧的运用能够显著提高数据处理效率和查询性能。掌握这些技巧需要时间和实践,但一旦掌握,将对你的数据库操作和数据分析产生深远的影响。
相关文章:
高级SQL技巧
高级SQL技巧涵盖了许多方面,包括但不限于窗口函数、递归查询、公共表表达式(CTEs)、子查询、集合操作、临时函数、日期时间操作、索引优化等。以下是对这些技巧的详细讲解和示例。 窗口函数 窗口函数是一种特殊的SQL函数,能够在…...
新大话西游图文架设教程
开始架设 1. 架设条件 新大话西游架设需要准备: linux 系统服务器,建议 CentOs 7.6或以上版本游戏源码,。 2. 安装宝塔面板 宝塔是一个服务器运维管理软件,安装命令: yum install -y wget && wget -O in…...
Maven 快速入门
Maven 快速入门 一、简介1、概述2、特点3、工作原理4、常用命令5、生命周期6、优缺点🎈 面试题 二、安装和配置1、安装2、环境配置3、命令测试是否安装成功4、功能配置5、idea配置本地 maven6、maven 工程依赖包查询网站 三、基于IDEA创建Maven工程1、maven 工程中的…...
OpenCV-人脸检测
文章目录 一、人脸检测流程二、关键方法三、代码示例四、注意事项 OpenCV是一个开源的计算机视觉和机器学习软件库,它提供了多种人脸检测方法,以下是对OpenCV人脸检测的详细介绍: 一、人脸检测流程 人脸检测是识别图像中人脸位置的过程&…...
【重磅升级】基于大数据的股票量化分析与预测系统
温馨提示:文末有 CSDN 平台官方提供的学长 QQ 名片 :) 1. 项目简介 伴随全球经济一体化和我国经济的快速发展,中国股票市场对世界经济的影响力不断攀升,中国股市已成为全球第二大股票交易市场。在当今的金融市场中,股票价格的波动…...
python全栈学习记录(二十四)元类、异常处理
元类、异常处理 文章目录 元类、异常处理一、元类1.元类控制类的实例化2.属性/方法的查找顺序3.单例 二、异常处理 一、元类 1.元类控制类的实例化 类的__call__方法会在产生的对象被调用时自动触发,args和kwargs就是调用实例时传入的参数,返回值是调用…...
Golang Slice扩容机制及注意事项
Golang Slice扩容机制及注意事项: 在 Go语言中,Slice(切片)是一种非常灵活且强大的数据结构,它是对数组的抽象,提供了动态数组的功能。Slice 的扩容机制是自动的,但了解其背后的原理对于编写高…...
华为OD机试 - 猜数字 - 暴力枚举(Python/JS/C/C++ 2024 E卷 100分)
华为OD机试 2024E卷题库疯狂收录中,刷题点这里 专栏导读 本专栏收录于《华为OD机试真题(Python/JS/C/C)》。 刷的越多,抽中的概率越大,私信哪吒,备注华为OD,加入华为OD刷题交流群,…...
Flink触发器Trigger
前言 在 Flink 窗口计算模型中,数据先经过 WindowAssigner 分配窗口,然后再经过触发器 Trigger,Trigger 决定了一个窗口何时被 ProcessFunction 处理。每个 WindowAssigner 都有一个默认的 Trigger,如果默认的不满足需求…...
【操作系统的使用】Linux 系统环境变量与服务管理:设置与控制的艺术
文章目录 系统环境变量与服务管理:设置与控制的艺术一、系统环境变量的设置1.1 临时设置环境变量1.2 永久设置环境变量 二、服务启动类型的设置2.1 查看服务状态2.2 启动和停止服务2.3 设置服务的启动类型2.3.1 设置服务在启动时运行2.3.2 禁用服务在启动时运行2.3.…...
速盾:高防cdn配置中性能优化是什么?
高防CDN配置中的性能优化是指通过调整CDN配置以提升网站的加载速度、响应时间和用户体验。在进行性能优化时,需要考虑多个因素,包括CDN节点的选择和布置、缓存策略、缓存过期时间、预取和预加载、并发连接数和网络延迟等。 首先,CDN节点的选…...
Qt_软件添加版本信息
文章内容: 给生成的软件添加软件的版权等信息 #include <windows.h> //中文的话增加下面这一行 #pragma code_page(65001)VS_VERSION_INFO VERSIONINFO...
mallocfree和newdelete的区别
malloc\free和new\delete的区别 malloc/free new/delete 身份: 函数 运算符\关键字 返回值: void* 带类型的指针 参数: 字节个数(手动计算) 类型 自动计算字节数 处理数组: 手动计算数组总字节数 new 类型[数量] 扩容࿱…...
无锁队列实现(Michael Scott),伪代码与c++实现
一、Michael & Scoot 原版伪代码实现 structure pointer_t {ptr: pointer to node_t, count: unsigned integer}structure node_t {value: data type, next: pointer_t}structure queue_t {Head: pointer_t, Tail: pointer_t}initialize(Q: pointer to queue_t)node new_…...
猜数字小游戏
前言 猜数字游戏是一款经典且简单的互动游戏,常常用于提高逻辑思维能力和锻炼数学技巧。本文将深入探讨一段用 JavaScript 编写的猜数字游戏代码,帮助读者理解游戏的基本逻辑和实现方法。这段代码不仅适合初学者练习编程技巧,也是了解用户交…...
在Windows上搭建ChatTTS:从本地部署到远程AI音频生成全攻略
文章目录 前言1. 下载运行ChatTTS模型2. 安装Cpolar工具3. 实现公网访问4. 配置ChatTTS固定公网地址 前言 本篇文章主要介绍如何快速地在Windows系统电脑中本地部署ChatTTS开源文本转语音项目,并且我们还可以结合Cpolar内网穿透工具创建公网地址,随时随…...
如何用好 CloudFlare 的速率限制防御攻击
最近也不知道咋回事儿,群里好多站长都反映被CC 攻击了。有人说依旧是 PCDN 干的,但明月感觉不像,因为有几个站长被 CC 攻击都是各种动态请求(这里的动态请求指的是.php 文件的请求)。经常被攻击的站长们都知道,WordPress /Typecho 这类动态博客系统最怕的就是这种动态请求…...
Unity3D 立方体纹理与自制天空盒详解
立方体纹理和自制天空盒是游戏开发中常用的技术之一,可以为游戏增添更加丰富的视觉效果。在本文中,我们将详细介绍Unity3D中立方体纹理和自制天空盒的使用方法,并给出相应的代码实现。 对惹,这里有一个游戏开发交流小组ÿ…...
【工具】VSCODE下载,配置初次设置
打开 settings.json 文件,包含了 Visual Studio Code (VSCode) 中的各种用户配置。 {"files.associations": {"*.vue": "vue","*.wpy": "vue","*.wxml": "html","*.wxss": "…...
vue使用jquery的ajax,页面跳转
一、引入jquery依赖 打开终端更新npm npm install -g npm 更新完后引入输入npm install jquery 加载完后 在最外层的package.json文件中加入以下代码 配置好后导入jquery 设置变量用于接收服务器传输的数据 定义ajax申请数据 服务器的Controller层传输数据 (…...
铭豹扩展坞 USB转网口 突然无法识别解决方法
当 USB 转网口扩展坞在一台笔记本上无法识别,但在其他电脑上正常工作时,问题通常出在笔记本自身或其与扩展坞的兼容性上。以下是系统化的定位思路和排查步骤,帮助你快速找到故障原因: 背景: 一个M-pard(铭豹)扩展坞的网卡突然无法识别了,扩展出来的三个USB接口正常。…...
Java 语言特性(面试系列2)
一、SQL 基础 1. 复杂查询 (1)连接查询(JOIN) 内连接(INNER JOIN):返回两表匹配的记录。 SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id d.dept_id; 左…...
中南大学无人机智能体的全面评估!BEDI:用于评估无人机上具身智能体的综合性基准测试
作者:Mingning Guo, Mengwei Wu, Jiarun He, Shaoxian Li, Haifeng Li, Chao Tao单位:中南大学地球科学与信息物理学院论文标题:BEDI: A Comprehensive Benchmark for Evaluating Embodied Agents on UAVs论文链接:https://arxiv.…...
《从零掌握MIPI CSI-2: 协议精解与FPGA摄像头开发实战》-- CSI-2 协议详细解析 (一)
CSI-2 协议详细解析 (一) 1. CSI-2层定义(CSI-2 Layer Definitions) 分层结构 :CSI-2协议分为6层: 物理层(PHY Layer) : 定义电气特性、时钟机制和传输介质(导线&#…...
ios苹果系统,js 滑动屏幕、锚定无效
现象:window.addEventListener监听touch无效,划不动屏幕,但是代码逻辑都有执行到。 scrollIntoView也无效。 原因:这是因为 iOS 的触摸事件处理机制和 touch-action: none 的设置有关。ios有太多得交互动作,从而会影响…...
C++八股 —— 单例模式
文章目录 1. 基本概念2. 设计要点3. 实现方式4. 详解懒汉模式 1. 基本概念 线程安全(Thread Safety) 线程安全是指在多线程环境下,某个函数、类或代码片段能够被多个线程同时调用时,仍能保证数据的一致性和逻辑的正确性…...
什么是Ansible Jinja2
理解 Ansible Jinja2 模板 Ansible 是一款功能强大的开源自动化工具,可让您无缝地管理和配置系统。Ansible 的一大亮点是它使用 Jinja2 模板,允许您根据变量数据动态生成文件、配置设置和脚本。本文将向您介绍 Ansible 中的 Jinja2 模板,并通…...
初学 pytest 记录
安装 pip install pytest用例可以是函数也可以是类中的方法 def test_func():print()class TestAdd: # def __init__(self): 在 pytest 中不可以使用__init__方法 # self.cc 12345 pytest.mark.api def test_str(self):res add(1, 2)assert res 12def test_int(self):r…...
听写流程自动化实践,轻量级教育辅助
随着智能教育工具的发展,越来越多的传统学习方式正在被数字化、自动化所优化。听写作为语文、英语等学科中重要的基础训练形式,也迎来了更高效的解决方案。 这是一款轻量但功能强大的听写辅助工具。它是基于本地词库与可选在线语音引擎构建,…...
React---day11
14.4 react-redux第三方库 提供connect、thunk之类的函数 以获取一个banner数据为例子 store: 我们在使用异步的时候理应是要使用中间件的,但是configureStore 已经自动集成了 redux-thunk,注意action里面要返回函数 import { configureS…...
