pandas vs SQL: SELECT, WHERE, GROUP BY, JOIN bằng pandas
Bài 1/6 · Chương 11
Trang phụ trợ
- 5 phút
- Trung cấp
- Python 3.14
- Cập nhật
Trong bài này · 14
Trang pandas vs SQL này đặt từng mệnh đề SQL quen thuộc (SELECT, WHERE, ORDER BY, LIMIT, GROUP BY, HAVING, JOIN) cạnh câu lệnh pandas làm cùng việc, chạy cả hai trên cùng một bảng. Mở trang khi bạn đã viết được câu SQL cho một việc và muốn biết viết nó bằng pandas thế nào.
Cách dùng với trang này
Mỗi câu hỏi là một mệnh đề SQL. Câu trả lời nói câu lệnh pandas tương ứng ở câu đầu, rồi đến một ví dụ chạy được. Ví dụ dựng bảng don bằng pandas, chép nó vào một cơ sở dữ liệu SQLite trong bộ nhớ bằng to_sql(), đọc kết quả của câu SQL bằng pd.read_sql(), rồi in kết quả của cả hai cách. Hai dòng in giống nhau là hai cách cùng nghĩa.
Cơ sở dữ liệu ":memory:" chỉ sống trong một lần chạy và được đóng bằng con.close() ở cuối mỗi ví dụ. Cách đọc và ghi SQLite từ pandas có ở bài read_sql(); trang này chỉ đối chiếu cú pháp, không dạy ngôn ngữ SQL.
SELECT và WHERE
SELECT vài cột viết thế nào trong pandas?
SELECT a, b FROM bang là chọn cột bằng một list tên: bang[["a", "b"]]. Thêm WHERE thì lọc dòng trước bằng một phép so sánh, gộp cả hai vào một lần loc[điều kiện, list cột]:
import sqlite3
import pandas as pd
don = pd.DataFrame({"ma_don": ["D1", "D2", "D3", "D4"],
"kenh": ["Web", "App", "Web", "Shop"],
"tien": [250000, 90000, 480000, 310000]})
con = sqlite3.connect(":memory:")
don.to_sql("don", con, index=False)
q = "SELECT ma_don, tien FROM don WHERE tien >= 250000"
print(pd.read_sql(q, con)["ma_don"].to_list())
con.close()
loc_ra = don.loc[don["tien"] >= 250000, ["ma_don", "tien"]]
print(loc_ra["ma_don"].to_list())KẾT QUẢ
['D1', 'D3', 'D4']
['D1', 'D3', 'D4']Cách lọc bằng điều kiện có ở bài lọc dữ liệu. Một khác biệt: bảng pandas giữ nhãn dòng gốc, còn kết quả SQL đánh số lại từ 0.
AND, OR, IN, LIKE đổi sang gì?
AND là &, OR là |, mỗi điều kiện trong ngoặc tròn riêng; IN (...) là isin([...]); LIKE '%chữ%' là str.contains("chữ"). query() cho viết điều kiện gần với SQL nhất, trong một chuỗi:
import pandas as pd
don = pd.DataFrame({"ma_don": ["D1", "D2", "D3", "D4"],
"kenh": ["Web", "App", "Web", "Shop"],
"tien": [250000, 90000, 480000, 310000]})
a = don[(don["kenh"] == "Web") & (don["tien"] > 300000)]
b = don.query("kenh == 'Web' and tien > 300000")
c = don[don["kenh"].isin(["App", "Shop"])]
print(a["ma_don"].to_list(), b["ma_don"].to_list())
print(c["ma_don"].to_list())KẾT QUẢ
['D3'] ['D3']
['D2', 'D4']Trong query(), tên cột viết trần và chữ đặt trong nháy đơn, như SQL; bài query() nói khi nào dùng nó.
ORDER BY và LIMIT
ORDER BY ... DESC LIMIT 3 viết thế nào?
ORDER BY tien DESC là sort_values("tien", ascending=False), LIMIT 3 là head(3). Cặp này hay gặp đến mức pandas có một lệnh gộp, nlargest(3, "tien"):
import sqlite3
import pandas as pd
don = pd.DataFrame({"ma_don": ["D1", "D2", "D3", "D4"],
"tien": [250000, 90000, 480000, 310000]})
con = sqlite3.connect(":memory:")
don.to_sql("don", con, index=False)
q = "SELECT ma_don FROM don ORDER BY tien DESC LIMIT 3"
print(pd.read_sql(q, con)["ma_don"].to_list())
con.close()
xep = don.sort_values("tien", ascending=False).head(3)
print(xep["ma_don"].to_list())
print(don.nlargest(3, "tien")["ma_don"].to_list())KẾT QUẢ
['D3', 'D4', 'D1']
['D3', 'D4', 'D1']
['D3', 'D4', 'D1']Sắp theo nhiều cột như ORDER BY kenh, tien DESC là sort_values(["kenh", "tien"], ascending=[True, False]), bài sort_values() dạy kỹ.
GROUP BY và HAVING
GROUP BY với SUM, COUNT viết thế nào?
SELECT kenh, SUM(tien) ... GROUP BY kenh là groupby("kenh")["tien"].sum(). Nhiều phép gom một lần, kèm tên cột kết quả như AS, là agg() với các cặp tên=(cột, phép):
import sqlite3
import pandas as pd
don = pd.DataFrame({"kenh": ["Web", "App", "Web", "Shop", "App"],
"tien": [250000, 90000, 480000, 310000, 60000]})
con = sqlite3.connect(":memory:")
don.to_sql("don", con, index=False)
q = ("SELECT kenh, SUM(tien) AS tong, COUNT(*) AS so_don "
"FROM don GROUP BY kenh ORDER BY kenh")
print(pd.read_sql(q, con).to_dict("list"))
con.close()
g = don.groupby("kenh").agg(tong=("tien", "sum"),
so_don=("tien", "size"))
print(g.reset_index().to_dict("list"))KẾT QUẢ
{'kenh': ['App', 'Shop', 'Web'], 'tong': [150000, 310000, 730000], 'so_don': [2, 1, 2]}
{'kenh': ['App', 'Shop', 'Web'], 'tong': [150000, 310000, 730000], 'so_don': [2, 1, 2]}groupby() đưa cột nhóm lên làm index; reset_index() đưa nó về thành cột như kết quả SQL. Bài groupby() và agg() đi từ đây.
HAVING đổi sang gì?
HAVING lọc trên kết quả đã gom, nên trong pandas nó là một lần lọc sau groupby(): gom trước, rồi giữ các nhóm thỏa điều kiện. Ví dụ HAVING SUM(tien) >= 400000:
import pandas as pd
don = pd.DataFrame({"kenh": ["Web", "App", "Web", "Shop", "App"],
"tien": [250000, 90000, 480000, 310000, 60000]})
tong = don.groupby("kenh")["tien"].sum()
print(tong[tong >= 400000].to_dict())KẾT QUẢ
{'Web': 730000}Đặt điều kiện trước groupby() thì là WHERE: lọc từng đơn trước khi gom. Trộn hai chỗ này là lỗi hay gặp khi dịch SQL sang pandas.
JOIN
INNER JOIN và LEFT JOIN viết thế nào?
JOIN ... ON a.ma_kh = b.ma_kh là merge(b, on="ma_kh"): mặc định là inner, chỉ giữ dòng khớp ở cả hai bảng. LEFT JOIN là how="left", giữ mọi dòng bảng trái và để NaN ở chỗ không khớp, như NULL của SQL:
import sqlite3
import pandas as pd
don = pd.DataFrame({"ma_don": ["D1", "D2", "D3"],
"ma_kh": ["K2", "K1", "K9"]})
kh = pd.DataFrame({"ma_kh": ["K1", "K2"], "ten": ["Lan", "Minh"]})
con = sqlite3.connect(":memory:")
don.to_sql("don", con, index=False)
kh.to_sql("kh", con, index=False)
q = "SELECT ma_don, ten FROM don LEFT JOIN kh USING (ma_kh)"
print(pd.read_sql(q, con)["ten"].to_list())
con.close()
print(don.merge(kh, on="ma_kh", how="left")["ten"].to_list())
print(don.merge(kh, on="ma_kh")["ten"].to_list())KẾT QUẢ
['Minh', 'Lan', nan]
['Minh', 'Lan', nan]
['Minh', 'Lan']Ô không khớp là NULL trong SQL; đọc vào pandas, nó thành nan như kết quả của merge(). Hai cột khóa khác tên (ON don.khach = kh.ma_kh) thì dùng left_on=, right_on=, như bài merge() chỉ.
SQL hay pandas
Nên lọc bằng SQL hay đọc hết rồi lọc bằng pandas?
Lọc và gom nhóm trên bảng lớn nên để SQL làm trong cơ sở dữ liệu, rồi chỉ đọc kết quả nhỏ vào pandas: đọc một triệu dòng chỉ để giữ trăm dòng là tốn bộ nhớ và thời gian. Pandas mạnh ở bước sau: làm sạch, đổi hình bảng, vẽ, và các phép mà SQL viết dài như pivot_table() hay so với tháng trước.
Giá trị lọc lấy từ biến thì viết câu SQL thế nào?
Đừng ghép giá trị vào chuỗi SQL bằng f-string: dấu nháy trong dữ liệu sẽ làm hỏng câu lệnh, và đó là đường cho SQL injection. Dùng dấu ? trong câu SQL và truyền giá trị qua params=, như pd.read_sql("SELECT * FROM don WHERE kenh = ?", con, params=["Web"]). Bài read_sql() có ví dụ chạy được.
Chủ đề
Bài này có hữu ích?
Hỏi đáp
Tham vấn chuyên môn
Nhà sáng lập AIDev và VietPlatform · Thạc sĩ AI MSE
- Tốt nghiệp thủ khoa xuất sắc Thạc sĩ AI MSE.
- Đồng sáng lập, CTO E-University, SVOnline: số hóa 10+ đại học, 40.000+ sinh viên.
- Xây hệ thống AI cho doanh nghiệp tại VietPlatform.