SQL中的CASE WHEN语句：从基础到高级应用指南

news/2024/7/24 3:53:25 标签: sql, 数据库, mysql, olap, 数据分析

SQL中的CASE WHEN语句：从基础到高级应用指南

准备工作 - 表1: products 示例数据：

我们使用一个名为"Products"的表，包含以下列：ProductID、ProductName、CategoryID、UnitPrice、StockQuantity。

sql">-- 建表
CREATE TABLE `products` (
  `productID` int(11) NOT NULL,
  `productName` varchar(255) DEFAULT NULL,
  `categoryID` int(11) DEFAULT NULL,
  `unitPrice` int(11) DEFAULT NULL,
  `stockQuantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 样例数据
INSERT INTO `products` VALUES (1, 'Laptop', 1, 800, 50);
INSERT INTO `products` VALUES (2, 'Smartphone', 1, 500, 100);
INSERT INTO `products` VALUES (3, 'T-shirt', 2, 20, 200);
INSERT INTO `products` VALUES (4, 'Jeans', 2, 40, 150);
INSERT INTO `products` VALUES (5, 'Headphones', 1, 100, 75);
  • 示例展示
productIDproductNamecategoryIDunitPricestockQuantity
1Laptop180050
2Smartphone1500100
3T-shirt220200
4Jeans240150
5Headphones110075

一. CASE WHEN 基础使用

1. CASE WHEN-基本使用

sql">SELECT
	ProductName,
	UnitPrice,
CASE
		
		WHEN UnitPrice > 100 THEN
		'Expensive' ELSE 'Affordable' 
	END AS PriceCategory 
FROM
	Products;

查询结果：

ProductNameUnitPricePriceCategory
Laptop800Expensive
Smartphone500Expensive
T-shirt20Affordable
Jeans40Affordable
Headphones100Affordable

2. CASE WHEN-多条件

sql">SELECT
	productName,
	stockQuantity,
CASE
		
		WHEN stockQuantity > 100 THEN
		'In Stock' 
		WHEN stockQuantity > 50 THEN
		'Limited Stock' ELSE 'Out of Stock' 
	END AS StockStatus 
FROM
	products;

查询结果：

productNamestockQuantityStockStatus
Laptop50Out of Stock
Smartphone100Limited Stock
T-shirt200In Stock
Jeans150In Stock
Headphones75Limited Stock

3. CASE WHEN-聚合函数

sql">SELECT
	categoryID,
	AVG( unitPrice ) AS AvgPrice,
CASE
		
		WHEN AVG( unitPrice ) > 50 THEN
		'High Price' ELSE 'Low Price' 
	END AS PriceCategory 
FROM
	products 
GROUP BY
	categoryID;
  • 查询结果
categoryIDAvgPricePriceCategory
1466.6667Hign Price
230low Price

4. CASE WHEN-日期条件

sql">SELECT
	productName,
CASE
		
		WHEN EXTRACT( MONTH FROM CURRENT_DATE ) = 8 THEN
		( SELECT NOW() ) ELSE 'Other Month' 
	END AS CurrentTime 
FROM
	products;
  • 查询结果
productNameCurrentTime
Laptop2023/8/30 19:14
Smartphone2023/8/30 19:14
T-shirt2023/8/30 19:14
Jeans2023/8/30 19:14
Headphones2023/8/30 19:14

5. CASE WHEN-用于排序

sql">SELECT
    ProductName,
    UnitPrice,
    CASE
        WHEN UnitPrice > 50 THEN 'Expensive'
        ELSE 'Affordable'
    END AS PriceCategory
FROM Products
ORDER BY UnitPrice DESC;
  • 查询结果
productNameunitPricePriceCategory
Laptop1902/3/10 0:00Expensive
Smartphone1901/5/14 0:00Expensive
Headphones1900/4/9 0:00Expensive
Jeans1900/2/9 0:00Affordable
T-shirt1900/1/20 0:00Affordable

6. CASE WHEN-子查询

sql">SELECT
	productName,
	unitPrice,
	( CASE WHEN unitPrice > ( SELECT AVG( unitPrice ) FROM products ) THEN 'Above Avg' ELSE 'Below Avg' END ) AS PriceComparison 
FROM
	products;
  • 查询结果
productNameunitPricePriceComparison
Laptop800Above Avg
Smartphone500Above Avg
T-shirt20Below Avg
Jeans40Below Avg
Headphones100Below Avg

7. CASE WHEN-计算字段

sql">SELECT
    ProductName,
    UnitPrice,
    StockQuantity,
    CASE
        WHEN StockQuantity > 0 THEN UnitPrice / StockQuantity
        ELSE 0
    END AS PricePerUnit
FROM Products;
  • 查询结果
productNameunitPricestockQuantityPricePerUnit
Laptop8005016
Smartphone5001005
T-shirt202000.1
Jeans401500.2667
Headphones100751.3333

8. CASE WHEN-动态列名

sql">SELECT
	productName,
	unitPrice,
	stockQuantity,
CASE
		
		WHEN stockQuantity > 150 THEN
		'High' 
		WHEN stockQuantity > 100 THEN
		'Medium' ELSE 'Low' 
	END AS StockCategory,
CASE
		
		WHEN stockQuantity > 100 THEN
		stockQuantity * 1.1 ELSE stockQuantity * 1.05 
	END AS AdjustedStock 
FROM
	products;
  • 查询结果
productNameunitPricestockQuantityStockCategoryAdjustedStock
Laptop80050Low52.5
Smartphone500100Low105
T-shirt20200High220
Jeans40150Medium165
Headphones10075Low78.75

9. CASE WHEN-带有嵌套逻辑

sql">SELECT
    ProductName,
    UnitPrice,
    CASE
        WHEN StockQuantity > 100 THEN
            CASE
                WHEN UnitPrice > 50 THEN 'High Demand, High Price'
                ELSE 'High Demand, Affordable'
            END
        ELSE 'Low Demand'
    END AS ProductStatus
FROM Products;
  • 查询结果
productNameunitPriceProductStatus
Laptop800Low Demand
Smartphone500Low Demand
T-shirt20High Demand, Affordable
Jeans40High Demand, Affordable
Headphones100Low Demand

10. CASE WHEN-处理字符串匹配

sql">SELECT
    ProductName,
    CASE
        WHEN ProductName LIKE '%Laptop%' THEN 'Electronics'
        WHEN ProductName LIKE '%T-shirt%' THEN 'Clothing'
        ELSE 'Other'
    END AS Category
FROM Products;
  • 查询结果
productNameCategory
LaptopElectronics
SmartphoneOther
T-shirtClothing
JeansOther
HeadphonesOther

11. CASE WHEN-用于条件合并

sql">SELECT
    ProductName,
    UnitPrice,
    CASE
        WHEN UnitPrice > 50 AND StockQuantity > 50 THEN 'High Price, High Stock'
        WHEN UnitPrice > 50 OR StockQuantity > 50 THEN 'High Price or High Stock'
        ELSE 'Low Price and Low Stock'
    END AS ProductStatus
FROM Products;
  • 查询结果
productNameunitPriceProductStatus
Laptop800High Price or High Stock
Smartphone500High Price, High Stock
T-shirt20High Price or High Stock
Jeans40High Price or High Stock
Headphones100High Price, High Stock

12. CASE WHEN-处理多列

sql">SELECT
    ProductName,
    UnitPrice,
    StockQuantity,
    CASE
        WHEN StockQuantity > 50 AND UnitPrice <

 30 THEN 'Popular and Affordable'
        WHEN StockQuantity <= 50 AND UnitPrice < 30 THEN 'Limited Stock, Affordable'
        WHEN StockQuantity > 50 AND UnitPrice >= 30 THEN 'Popular and Expensive'
        ELSE 'Limited Stock, Expensive'
    END AS ProductCategory
FROM Products;
  • 查询结果
productNameunitPricestockQuantityProductCategory
Laptop80050Limited Stock, Expensive
Smartphone500100Popular and Expensive
T-shirt20200Popular and Affordable
Jeans40150Popular and Expensive
Headphones10075Popular and Expensive

13. CASE WHEN-加入窗口函数

sql">SELECT
    ProductName,
    UnitPrice,
    StockQuantity,
    CASE
        WHEN StockQuantity > AVG(StockQuantity) OVER () THEN 'Above Avg Stock'
        ELSE 'Below Avg Stock'
    END AS StockComparison
FROM Products;
  • 查询结果
productNameunitPricestockQuantityStockComparison
T-shirt20200Above Avg Stock
Laptop80050Below Avg Stock
Jeans40150Above Avg Stock
Smartphone500100Below Avg Stock
Headphones10075Below Avg Stock

二. CASE WHEN 进阶使用

1. 基于历史数据的趋势预测

样例SQL：

sql">SELECT
    p.ProductID,
    p.ProductName,
    s.SaleDate,
    s.QuantitySold,
    CASE
        WHEN s.QuantitySold > LAG(s.QuantitySold) OVER (PARTITION BY p.ProductID ORDER BY s.SaleDate) THEN 'Increased'
        WHEN s.QuantitySold < LAG(s.QuantitySold) OVER (PARTITION BY p.ProductID ORDER BY s.SaleDate) THEN 'Decreased'
        ELSE 'Stable'
    END AS Trend
FROM Products p
JOIN SalesHistory s ON p.ProductID = s.ProductID;

2. 基于不同维度的复杂分析

样例SQL：

sql">SELECT
    o.OrderID,
    o.OrderDate,
    SUM(CASE WHEN p.CategoryID = 1 THEN o.Quantity ELSE 0 END) AS ElectronicsQuantity,
    SUM(CASE WHEN p.CategoryID = 2 THEN o.Quantity ELSE 0 END) AS ClothingQuantity,
    SUM(CASE WHEN p.CategoryID = 3 THEN o.Quantity ELSE 0 END) AS OtherQuantity
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY o.OrderID, o.OrderDate;

3. 多层次CASE WHEN分析

样例SQL：

sql">SELECT
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    CASE
        WHEN Salary > 70000 THEN 'High'
        WHEN Salary > 60000 THEN 'Medium'
        WHEN Salary > 50000 THEN 'Low'
        ELSE 'Very Low'
    END AS SalaryLevel,
    CASE
        WHEN Salary > 60000 THEN 'Above Average'
        ELSE 'Below Average'
    END AS SalaryComparison
FROM Employees;

4. 使用CASE WHEN进行数据分桶

样例SQL：

sql">SELECT
    CustomerID,
    Age,
    Gender,
    CASE
        WHEN Age < 30 THEN 'Young'
        WHEN Age >= 30 AND Age < 40 THEN 'Middle-aged'
        ELSE 'Senior'
    END AS AgeGroup,
    CASE
        WHEN Gender = 'Male' THEN 'Male'
        WHEN Gender = 'Female' THEN 'Female'
        ELSE 'Other'
    END AS GenderCategory
FROM Customers;

5. 基于多条件的复杂逻辑判断

样例SQL：

sql">SELECT
    OrderID,
    OrderDate,
    SUM(CASE WHEN Quantity * Price > 500 THEN Quantity ELSE 0 END) AS HighValueItems,
    SUM(CASE WHEN Quantity * Price > 100 AND Quantity * Price <= 500 THEN Quantity ELSE 0 END) AS MediumValueItems,
    SUM(CASE WHEN Quantity * Price <= 100 THEN Quantity ELSE 0 END) AS LowValueItems
FROM Orders
GROUP BY OrderID, OrderDate;

三. CASE WHEN 业务场景常用技巧

1. 数据重编码

您可以使用CASE WHEN来对现有数据进行重新编码，例如将文本值转换为数字编码或将某些字符串转换为更易于处理的标识符。

sql">SELECT
    customerName,
    CASE
        WHEN customerType = 'Individual' THEN 1
        WHEN customerType = 'Corporate' THEN 2
        ELSE 0
    END AS CustomerTypeCode
FROM Customers;

2. 条件分组

使用CASE WHEN可以在查询结果中创建不同的数据分组，而无需在实际数据中创建新的列。

sql">SELECT
    productName,
    SUM(quantity) AS totalQuantity,
    CASE
        WHEN SUM(quantity) > 100 THEN 'High'
        WHEN SUM(quantity) > 50 THEN 'Medium'
        ELSE 'Low'
    END AS QuantityGroup
FROM Sales
GROUP BY productName;

3. 动态排序规则

通过在ORDER BY子句中使用CASE WHEN，您可以根据不同条件动态调整查询结果的排序规则。

sql">SELECT
    productName,
    unitPrice
FROM Products
ORDER BY
    CASE
        WHEN category = 'Electronics' THEN unitPrice
        WHEN category = 'Clothing' THEN unitPrice * 0.9
        ELSE unitPrice * 1.1
    END;

4. 分位数分析

使用CASE WHEN可以在查询结果中对数据进行分位数分析，识别哪些数据点位于不同的分位数区间。

sql">SELECT
    productName,
    unitPrice,
    CASE
        WHEN unitPrice <= PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY unitPrice) THEN 'Q1'
        WHEN unitPrice <= PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY unitPrice) THEN 'Q2'
        WHEN unitPrice <= PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY unitPrice) THEN 'Q3'
        ELSE 'Q4'
    END AS PriceQuartile
FROM Products;

5. 缺失数据填充

使用CASE WHEN可以根据条件将缺失的数据点填充为特定值，从而更好地处理数据缺失情况。

sql">SELECT
    orderID,
    orderDate,
    CASE
        WHEN orderAmount IS NULL THEN 0
        ELSE orderAmount
    END AS FilledOrderAmount
FROM Orders;

6. 日期区间分析

使用CASE WHEN可以对日期进行区间分析，例如判断每个日期属于哪个季节、哪个月份等。

sql">SELECT
    orderDate,
    CASE
        WHEN EXTRACT(MONTH FROM orderDate) IN (12, 1, 2) THEN 'Winter'
        WHEN EXTRACT(MONTH FROM orderDate) IN (3, 4, 5) THEN 'Spring'
        WHEN EXTRACT(MONTH FROM orderDate) IN (6, 7, 8) THEN 'Summer'
        ELSE 'Fall'
    END AS Season
FROM Orders;

7. 业务阶段分析

使用CASE WHEN可以根据特定业务规则判断数据所处的不同阶段，如用户生命周期阶段、订单处理阶段等。

sql">SELECT
    userID,
    registrationDate,
    CASE
        WHEN NOW() - registrationDate < INTERVAL '30 days' THEN 'New User'
        WHEN NOW() - registrationDate < INTERVAL '90 days' THEN 'Regular User'
        ELSE 'Inactive User'
    END AS UserStage
FROM Users;

8. 动态列选择

使用CASE WHEN可以在查询结果中根据条件选择不同的列，从而根据业务需求定制查询结果。

sql">SELECT
    orderID,
    orderDate,
    CASE
        WHEN displayPrice = 'Gross' THEN grossPrice
        ELSE netPrice
    END AS SelectedPrice
FROM Orders;

9. 异常值标记

使用CASE WHEN可以根据条件识别和标记异常数据点，帮助进行数据质量分析。

sql">SELECT
    customerID,
    orderDate,
    orderAmount,
    CASE
        WHEN orderAmount < 0 THEN 'Negative'
        WHEN orderAmount > 10000 THEN 'High Amount'
        ELSE 'Normal'
    END AS DataQuality
FROM Orders;

10. 数据格式转换

使用CASE WHEN可以在不同的数据格式之间进行转换，例如将布尔值转换为文本标签。

sql">SELECT
    productID,
    productName,
    inStock,
    CASE
        WHEN inStock THEN 'Available'
        ELSE 'Out of Stock'
    END AS StockStatus
FROM Products;

http://www.niftyadmin.cn/n/4985649.html

相关文章

Java线程 - 详解(2)

Java线程 - 详解(2)

一&#xff0c;线程安全问题 有些代码在单个线程的环境下运行&#xff0c;完全正确&#xff0c;但是同样的代码&#xff0c;让多个线程去执行&#xff0c;此时就可能出现BUG&#xff0c;这就是所谓的 "线程安全问题"。举一个例子&#xff1a; public class Demo {s…
阅读更多...
数据驱动的生活：探索未来七天生活指数API的应用

数据驱动的生活：探索未来七天生活指数API的应用

前言 随着科技的不断发展&#xff0c;数据已经成为我们生活中不可或缺的一部分。从社交媒体上的点赞和分享&#xff0c;到电子邮件和搜索引擎的历史记录&#xff0c;数据正在以前所未有的速度积累。而这些数据的利用不仅仅停留在社交媒体或商业领域&#xff0c;它们还可以为我…
阅读更多...
uniapp中使用原生canvas标签绘制视频帧来模拟拍照，拍照后将图绘制在另外一个canvas上编辑画图，这样反复操作

uniapp中使用原生canvas标签绘制视频帧来模拟拍照，拍照后将图绘制在另外一个canvas上编辑画图，这样反复操作

uniapp中使用原生canvas标签绘制视频帧来模拟拍照&#xff0c;拍照后将图绘制在另外一个canvas上编辑画图&#xff0c;这样反复操作会导致ios系统上白屏&#xff0c;canvas2d上下文为null,经查阅找到相关资料 IOS 创建Canvas过多导致getContext(‘2d’) 返回null 总 Canvas 内存…
阅读更多...
拧紧燃气“安全阀”！汉威科技为城市生命线构筑安全监控网

拧紧燃气“安全阀”！汉威科技为城市生命线构筑安全监控网

防患于未“燃”&#xff0c;拧紧燃气“安全阀”&#xff0c;这是关乎亿万国人生命财产安全的大事&#xff0c;也一直是党和国家重点发力的民生工程。8月11日&#xff0c;国务院安委会正式印发《全国城镇燃气安全专项整治工作方案》&#xff0c;也让液化石油气全链条的安全问题成…
阅读更多...
哈希表+字符串

哈希表+字符串

一)知识回顾: 1)哈希表是什么?哈希表是存储数据的容器 2)哈希表有啥用?快速的查找某一个元素 3)什么时候使用哈希表?频繁的查找某一个数的时候&#xff0c;当我们快速查找某一个数的时候&#xff0c;不光要想到哈希表还需要想到二分查找&#xff0c;但是二分查找算法的局限性…
阅读更多...
【微服务部署】08-监控与告警

【微服务部署】08-监控与告警

文章目录 1. PrometheusOperator1.1 优势1.2 配置脚本1.3 部署脚本 2. Granfana实现监控看板2.1 Granfana核心特性2.2 部署文件 目前Kubernetes中最流行的监控解决方案是使用Prometheus和AlertManager 1. PrometheusOperator 1.1 优势 自动化安装将配置资源化灵活的扩展能力 …
阅读更多...
查漏补缺 - JS之一

查漏补缺 - JS之一

目录 1&#xff0c;程序流程控制2&#xff0c;变量&#xff0c;字面量&#xff0c;表达式3&#xff0c;函数4&#xff0c;数据的存储和传递5&#xff0c;函数作用域6&#xff0c;全局对象7&#xff0c;构造函数&#xff0c;原型&#xff0c;this&#xff0c;原型链&#xff0c;…
阅读更多...
ES是一个分布式全文检索框架，隐藏了复杂的处理机制，核心数据分片机制、集群发现、分片负载均衡请求路由

ES是一个分布式全文检索框架，隐藏了复杂的处理机制，核心数据分片机制、集群发现、分片负载均衡请求路由

ES是一个分布式框架&#xff0c;隐藏了复杂的处理机制&#xff0c;核心数据分片机制、集群发现、分片负载均衡请求路由。 ES的高可用架构&#xff0c;总体如下图&#xff1a; 说明&#xff1a;本文会以pdf格式持续更新&#xff0c;更多最新尼恩3高pdf笔记&#xff0c;请从下面…
阅读更多...
最新文章

Copyright @ 2022~2023