像Excel一样使用SQL进行数据分析

SQL可以通过多种语句和函数实现类似Excel的数据分析功能，包括数据清洗、预处理、分类、筛选、汇总及透视等操作。以下是具体实现方法：

一、重复数据处理

查找重复记录：通过分组统计字段出现次数，筛选重复项。

SELECT * FROM user WHERE (nick_name,password) IN ( SELECT nick_name,password FROM user GROUP BY nick_name,password HAVING COUNT(nick_name)>1);

删除重复记录：保留每组重复记录中ID最小的记录，删除其他重复项。

DELETE c1 FROM customer c1, customer c2 WHERE c1.cust_email = c2.cust_email AND c1.id > c2.id;二、缺失值处理

查找缺失值：使用IS NULL条件筛选空值记录。

SELECT * FROM customer WHERE cust_email IS NULL;

填充空值：通过UPDATE语句用固定值或计算值替换空值。

UPDATE sale SET city = "未知" WHERE city IS NULL;UPDATE orderitems SET price_new = IFNULL(price_new, 5.74);三、计算列

添加计算列：通过ALTER TABLE新增列，再用UPDATE填充计算结果。

ALTER TABLE orderitems ADD price_new DECIMAL(8,2) NOT NULL;UPDATE orderitems SET price_new = item_price * count;

查询计算列：直接在SELECT中定义计算表达式。

SELECT item_price * count AS sales FROM orderitems;四、排序与排名

多列排序：按多个字段优先级排序，支持升序（ASC）和降序（DESC）。

SELECT * FROM orderitems ORDER BY price_new DESC, quantity;

查询前N条记录：使用LIMIT限制结果数量。

SELECT * FROM orderitems ORDER BY price_new DESC LIMIT 5;

排名计算：通过子查询统计大于等于当前值的唯一值数量作为排名。

SELECT prod_price, ( SELECT COUNT(DISTINCT prod_price) FROM products WHERE prod_price >= a.prod_price) AS rank FROM products AS a ORDER BY rank;五、字符串处理

字符串替换：使用REPLACE函数替换指定内容。

UPDATE data1 SET city = REPLACE(city, 'SH', 'shanghai');

字符串截取：
按位置截取：LEFT（左侧）、RIGHT（右侧）、SUBSTRING（中间）。
SELECT LEFT('example.com', 3); -- 返回 'exa'SELECT SUBSTRING('example.com', 4, 2); -- 返回 'mp'
按分隔符截取：SUBSTRING_INDEX根据分隔符位置截取。
SELECT SUBSTRING_INDEX('
www.google.com'
, '.', 1); -- 返回 'www'

六、筛选数据

高级筛选：使用AND、OR、IN、NOT等逻辑操作符组合条件。

SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01', 'BRS01') AND prod_price > 100;

通配符筛选：LIKE配合%（任意字符）、_（单个字符）实现模糊匹配。

SELECT * FROM customers WHERE country LIKE 'CH%'; -- 匹配以'CH'开头的国家七、表联结

内联结：通过INNER JOIN关联表，类似Excel的VLOOKUP。

SELECT vend_id, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

多表联结：通过WHERE条件关联多个表。

SELECT prod_name, vend_name, prod_price, quantity FROM OrderItems, Products, Vendors WHERE Products.vend_id = Vendors.vend_id AND OrderItems.prod_id = Products.prod_id AND order_num = 20007;

自联结：在单条SELECT中多次引用同一表，实现复杂查询。

SELECT c1.cust_id, c1.cust_name FROM Customers AS c1, Customers AS c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';八、数据透视

分组汇总：通过GROUP BY分组，HAVING过滤分组结果。

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3;

交叉表：使用CASE WHEN将行转为列，实现类似Excel数据透视表的效果。

SELECT city, MAX(CASE WHEN colour = 'A' THEN price END) AS A, MAX(CASE WHEN colour = 'B' THEN price END) AS BFROM data1 GROUP BY city;

通过以上SQL操作，可以高效完成数据清洗、转换和分析任务，尤其适合处理大规模数据集，其性能远优于Excel的逐行操作。实际使用时需根据数据库类型（如MySQL、PostgreSQL）调整语法细节。

您可能感兴趣问答

Collapsible

热门标签

热点问答