网站用户体验模型,江苏荣邦建设有限公司网站,跨平台软件开发工具,用模板做网站会被盗吗本页旨在提供一些如何使用pandas执行各种SQL操作的示例#xff0c;来帮助SQL使用者快速上手使用pandas。 目录 SQL语法一、选择SELECT1、选择2、添加计算列 二、连接JOIN ON1、内连接2、左外连接3、右外连接4、全外连接 三、过滤WHERE1、AND2、OR3、IS NULL4、IS NOT NULL5、B…本页旨在提供一些如何使用pandas执行各种SQL操作的示例来帮助SQL使用者快速上手使用pandas。 目录 SQL语法一、选择SELECT1、选择2、添加计算列 二、连接JOIN ON1、内连接2、左外连接3、右外连接4、全外连接 三、过滤WHERE1、AND2、OR3、IS NULL4、IS NOT NULL5、BETWEEN6、LIKE7、CASE WHEN 四、分组GROUP BY1、count()2、avg()3、sum()、max()、min() 五、HAVING六、排序ORDER BY七、LIMIT/OFFSET1、LIMIT2、指定列中最大的前N行3、OFFSET 八、UNION ALL/UNION1、UNION ALL2、UNION 九、开窗函数1、ROW_NUMBER()2、RANK()3、SUM() SQL语法
SELECT [DISTINCT | ALL] column1, column2, …, aggregate_function(columnN), …FROM table_name [AS alias][JOIN type JOIN table2_name [AS alias2] ON join_condition] [, JOIN type JOIN table3_name [AS alias3] ON join_condition, …][WHERE condition][GROUP BY column1, column2, …][HAVING condition][ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], …][LIMIT number [OFFSET offset]][UNION [ALL] SELECT …] – 可以链式添加多个UNION SELECT语句
DISTINCT确保结果集中的行是唯一的。ALL默认表示返回所有匹配的行包括重复的行。aggregate_function()聚合函数如**SUM(), AVG(), COUNT(), MAX(), MIN()**等用于对一组值执行计算并返回单个值。JOIN type指定连接类型如INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN等。ON join_condition定义连接条件。WHERE condition过滤结果集中的行只返回满足条件的行。GROUP BY将结果集按一个或多个列分组。通常与聚合函数一起使用。HAVING condition过滤分组后的结果集只返回满足条件的组。ORDER BY对结果集进行排序。可以指定多个列和排序方向ASC升序[默认]或DESC降序。LIMIT number [OFFSET offset]限制返回的行数并可选地指定跳过的行数。UNION [ALL]合并两个或多个SELECT语句的结果集。UNION默认去除重复行而UNION ALL保留所有行。
一、选择SELECT
在SQL中选择是使用要选择的列的逗号分隔列表或* 选择所有列
1、选择
SQL语法
SELECT total_bill, tip, smoker, time
FROM data;对应pandas实现
In :data[[total_bill, tip, smoker, time]]
Out :
total_bill tip smoker time
0 16.99 1.01 No Dinner
1 10.34 1.66 No Dinner
2 21.01 3.50 No Dinner
3 23.68 3.31 No Dinner
4 24.59 3.61 No Dinner
... ... ... ... ...
239 29.03 5.92 No Dinner
240 27.18 2.00 Yes Dinner
241 22.67 2.00 Yes Dinner
242 17.82 1.75 No Dinner
243 18.78 3.00 No Dinner2、添加计算列
SQL语法
SELECT *, tip/total_bill as tip_rate
FROM data;对应pandas实现
1可以使用DataFrame的DataFrame.assign方法来追加新列
In :data data.assign(tip_ratedata[tip] / data[total_bill])
In :dataOut :
total_bill tip sex smoker day time size tip_rate
0 16.99 1.01 Female No Sun Dinner 2 0.059447
1 10.34 1.66 Male No Sun Dinner 3 0.160542
2 21.01 3.50 Male No Sun Dinner 3 0.166587
3 23.68 3.31 Male No Sun Dinner 2 0.139780
4 24.59 3.61 Female No Sun Dinner 4 0.146808
... ... ... ... ... ... ... ... ...
239 29.03 5.92 Male No Sat Dinner 3 0.203927
240 27.18 2.00 Female Yes Sat Dinner 2 0.073584
241 22.67 2.00 Male Yes Sat Dinner 2 0.088222
242 17.82 1.75 Male No Sat Dinner 2 0.098204
243 18.78 3.00 Female No Thur Dinner 2 0.1597442也可以直接计算
In :data[tip_rate2] data[tip] / data[total_bill]
In :dataOut :
total_bill tip sex smoker day time size tip_rate tip_rate2
0 16.99 1.01 Female No Sun Dinner 2 0.059447 0.059447
1 10.34 1.66 Male No Sun Dinner 3 0.160542 0.160542
2 21.01 3.50 Male No Sun Dinner 3 0.166587 0.166587
3 23.68 3.31 Male No Sun Dinner 2 0.139780 0.139780
4 24.59 3.61 Female No Sun Dinner 4 0.146808 0.146808
... ... ... ... ... ... ... ... ... ...
239 29.03 5.92 Male No Sat Dinner 3 0.203927 0.203927
240 27.18 2.00 Female Yes Sat Dinner 2 0.073584 0.073584
241 22.67 2.00 Male Yes Sat Dinner 2 0.088222 0.088222
242 17.82 1.75 Male No Sat Dinner 2 0.098204 0.098204
243 18.78 3.00 Female No Thur Dinner 2 0.159744 0.159744二、连接JOIN ON
构造测试数据
In :df1 pd.DataFrame({key: [A, B, C, D], value: np.random.randn(4)})
In :df2 pd.DataFrame({key: [B, D, D, E], value: np.random.randn(4)})1、内连接
SQL语法
SELECT *
FROM df1
INNER JOIN df2ON df1.key df2.key; 对应pandas实现
In :pd.merge(df1, df2, onkey)
Out :
key value_x value_y
0 B 0.227232 1.011278
1 D 1.415853 -0.149207
2 D 1.415853 -0.6084302、左外连接
SQL语法
SELECT *
FROM df1
LEFT OUTER JOIN df2ON df1.key df2.key;对应pandas实现
In :pd.merge(df1, df2, onkey, howleft)
Out :
key value_x value_y
0 A 1.418532 NaN
1 B 0.227232 1.011278
2 C -0.578408 NaN
3 D 1.415853 -0.149207
4 D 1.415853 -0.6084303、右外连接
SQL语法
SELECT *
FROM df1
RIGHT OUTER JOIN df2ON df1.key df2.key;对应pandas实现
In :pd.merge(df1, df2, onkey, howright)
Out :
key value_x value_y
0 B 0.227232 1.011278
1 D 1.415853 -0.149207
2 D 1.415853 -0.608430
3 E NaN 1.4373884、全外连接
SQL语法
SELECT *
FROM df1
FULL OUTER JOIN df2ON df1.key df2.key;对应pandas实现
In :pd.merge(df1, df2, onkey, howouter)
Out :key value_x value_y
0 A 1.418532 NaN
1 B 0.227232 1.011278
2 C -0.578408 NaN
3 D 1.415853 -0.149207
4 D 1.415853 -0.608430
5 E NaN 1.437388三、过滤WHERE
SQL中的过滤是通过WHERE子句完成的。
SQL语法
SELECT *
FROM data
WHERE total_bill 10;对应pandas实现
In :data[data[total_bill] 10]
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2
0 16.99 1.01 Female No Sun Dinner 2 0.059447 0.059447
1 10.34 1.66 Male No Sun Dinner 3 0.160542 0.160542
2 21.01 3.50 Male No Sun Dinner 3 0.166587 0.166587
3 23.68 3.31 Male No Sun Dinner 2 0.139780 0.139780
4 24.59 3.61 Female No Sun Dinner 4 0.146808 0.146808
... ... ... ... ... ... ... ... ... ...
239 29.03 5.92 Male No Sat Dinner 3 0.203927 0.203927
240 27.18 2.00 Female Yes Sat Dinner 2 0.073584 0.073584
241 22.67 2.00 Male Yes Sat Dinner 2 0.088222 0.088222
242 17.82 1.75 Male No Sat Dinner 2 0.098204 0.098204
243 18.78 3.00 Female No Thur Dinner 2 0.159744 0.1597441、AND
对应pandas中的
SQL语法
# 查询晚餐小费超过5美元的数据
SELECT *
FROM data
WHERE time Dinner AND tip 5.00;对应pandas实现
In :data[(data[time] Dinner) (data[tip] 5.00)]
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2
23 39.42 7.58 Male No Sat Dinner 4 0.192288 0.192288
44 30.40 5.60 Male No Sun Dinner 4 0.184211 0.184211
47 32.40 6.00 Male No Sun Dinner 4 0.185185 0.185185
52 34.81 5.20 Female No Sun Dinner 4 0.149382 0.149382
59 48.27 6.73 Male No Sat Dinner 4 0.139424 0.139424
116 29.93 5.07 Male No Sun Dinner 4 0.169395 0.169395
155 29.85 5.14 Female No Sun Dinner 5 0.172194 0.172194
170 50.81 10.00 Male Yes Sat Dinner 3 0.196812 0.196812
172 7.25 5.15 Male Yes Sun Dinner 2 0.710345 0.710345
181 23.33 5.65 Male Yes Sun Dinner 2 0.242177 0.242177
183 23.17 6.50 Male Yes Sun Dinner 4 0.280535 0.280535
211 25.89 5.16 Male Yes Sat Dinner 4 0.199305 0.199305
212 48.33 9.00 Male No Sat Dinner 4 0.186220 0.186220
214 28.17 6.50 Female Yes Sat Dinner 3 0.230742 0.230742
239 29.03 5.92 Male No Sat Dinner 3 0.203927 0.2039272、OR
对应pandas中的|
SQL语法
# 查询至少5名用餐者的小费或账单总额超过45美元的数据
SELECT *
FROM data
WHERE size 5 OR total_bill 45;对应pandas实现
In :data[(data[size] 5) | (data[total_bill] 45)]
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2
59 48.27 6.73 Male No Sat Dinner 4 0.139424 0.139424
125 29.80 4.20 Female No Thur Lunch 6 0.140940 0.140940
141 34.30 6.70 Male No Thur Lunch 6 0.195335 0.195335
142 41.19 5.00 Male No Thur Lunch 5 0.121389 0.121389
143 27.05 5.00 Female No Thur Lunch 6 0.184843 0.184843
155 29.85 5.14 Female No Sun Dinner 5 0.172194 0.172194
156 48.17 5.00 Male No Sun Dinner 6 0.103799 0.103799
170 50.81 10.00 Male Yes Sat Dinner 3 0.196812 0.196812
182 45.35 3.50 Male Yes Sun Dinner 3 0.077178 0.077178
185 20.69 5.00 Male No Sun Dinner 5 0.241663 0.241663
187 30.46 2.00 Male Yes Sun Dinner 5 0.065660 0.065660
212 48.33 9.00 Male No Sat Dinner 4 0.186220 0.186220
216 28.15 3.00 Male Yes Sat Dinner 5 0.106572 0.1065723、IS NULL
构造测试数据
In :frame pd.DataFrame({col1: [A, B, np.nan, C, D], col2: [F, np.nan, G, H, I]}
)SQL语法
SELECT *
FROM frame
WHERE col2 IS NULL;对应pandas实现
In :frame[frame[col2].isna()]
Out :
col1 col2
1 B NaN4、IS NOT NULL
SQL语法
SELECT *
FROM frame
WHERE col1 IS NOT NULL;对应pandas实现
In :frame[frame[col1].notna()]
Out :
col1 col2
0 A F
1 B NaN
3 C H
4 D I5、BETWEEN
SQL语法
SELECT *
FROM data
WHERE tip between 5 and 7;对应pandas实现
In :data[data[tip].between(5, 7)]
Out :total_bill tip sex smoker day time size tip_rate tip_rate2
11 35.26 5.00 Female No Sun Dinner 4 0.141804 0.141804
39 31.27 5.00 Male No Sat Dinner 3 0.159898 0.159898
44 30.40 5.60 Male No Sun Dinner 4 0.184211 0.184211
46 22.23 5.00 Male No Sun Dinner 2 0.224921 0.224921
47 32.40 6.00 Male No Sun Dinner 4 0.185185 0.185185
52 34.81 5.20 Female No Sun Dinner 4 0.149382 0.149382
59 48.27 6.73 Male No Sat Dinner 4 0.139424 0.139424
73 25.28 5.00 Female Yes Sat Dinner 2 0.197785 0.197785
83 32.68 5.00 Male Yes Thur Lunch 2 0.152999 0.152999
85 34.83 5.17 Female No Thur Lunch 4 0.148435 0.148435
88 24.71 5.85 Male No Thur Lunch 2 0.236746 0.236746
116 29.93 5.07 Male No Sun Dinner 4 0.169395 0.169395
141 34.30 6.70 Male No Thur Lunch 6 0.195335 0.195335
142 41.19 5.00 Male No Thur Lunch 5 0.121389 0.121389
143 27.05 5.00 Female No Thur Lunch 6 0.184843 0.184843
155 29.85 5.14 Female No Sun Dinner 5 0.172194 0.172194
156 48.17 5.00 Male No Sun Dinner 6 0.103799 0.103799
172 7.25 5.15 Male Yes Sun Dinner 2 0.710345 0.710345
181 23.33 5.65 Male Yes Sun Dinner 2 0.242177 0.242177
183 23.17 6.50 Male Yes Sun Dinner 4 0.280535 0.280535
185 20.69 5.00 Male No Sun Dinner 5 0.241663 0.241663
197 43.11 5.00 Female Yes Thur Lunch 4 0.115982 0.115982
211 25.89 5.16 Male Yes Sat Dinner 4 0.199305 0.199305
214 28.17 6.50 Female Yes Sat Dinner 3 0.230742 0.230742
239 29.03 5.92 Male No Sat Dinner 3 0.203927 0.2039276、LIKE
开头/结尾字符匹配可以用startswith()/endswith()函数实现
SQL语法
SELECT *
FROM data
WHERE time like Di%;对应pandas实现
In :data[data[time].str.startswith(Di)]
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2
0 16.99 1.01 Female No Sun Dinner 2 0.059447 0.059447
1 10.34 1.66 Male No Sun Dinner 3 0.160542 0.160542
2 21.01 3.50 Male No Sun Dinner 3 0.166587 0.166587
3 23.68 3.31 Male No Sun Dinner 2 0.139780 0.139780
4 24.59 3.61 Female No Sun Dinner 4 0.146808 0.146808
... ... ... ... ... ... ... ... ... ...
239 29.03 5.92 Male No Sat Dinner 3 0.203927 0.203927
240 27.18 2.00 Female Yes Sat Dinner 2 0.073584 0.073584
241 22.67 2.00 Male Yes Sat Dinner 2 0.088222 0.088222
242 17.82 1.75 Male No Sat Dinner 2 0.098204 0.098204
243 18.78 3.00 Female No Thur Dinner 2 0.159744 0.159744中间字符匹配可以用contains()函数实现na参数设置为False表示在缺失值上不返回Truecase参数设置为False表示不区分大小写匹配
SQL语法
SELECT *
FROM data
WHERE time like %inne%;对应pandas实现
In :data[data[time].str.contains(inne, naFalse, caseFalse)]
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2
0 16.99 1.01 Female No Sun Dinner 2 0.059447 0.059447
1 10.34 1.66 Male No Sun Dinner 3 0.160542 0.160542
2 21.01 3.50 Male No Sun Dinner 3 0.166587 0.166587
3 23.68 3.31 Male No Sun Dinner 2 0.139780 0.139780
4 24.59 3.61 Female No Sun Dinner 4 0.146808 0.146808
... ... ... ... ... ... ... ... ... ...
239 29.03 5.92 Male No Sat Dinner 3 0.203927 0.203927
240 27.18 2.00 Female Yes Sat Dinner 2 0.073584 0.073584
241 22.67 2.00 Male Yes Sat Dinner 2 0.088222 0.088222
242 17.82 1.75 Male No Sat Dinner 2 0.098204 0.098204
243 18.78 3.00 Female No Thur Dinner 2 0.159744 0.1597447、CASE WHEN
SQL语法
SELECT tip,case when tip2 then LOWwhen 2tip3 then MIDwhen 3tip then HIGend flag
FROM data;对应pandas实现
In :data[flag] data[tip].apply(lambda x: LOW if x 2 else (MID if 2 x 3 else HIG))
In :data[[tip, flag]]
Out :tip flag
0 1.01 LOW
1 1.66 LOW
2 3.50 HIG
3 3.31 HIG
4 3.61 HIG
... ... ...
239 5.92 HIG
240 2.00 MID
241 2.00 MID
242 1.75 LOW
243 3.00 MID四、分组GROUP BY
在pandas中SQL的GROUP BY操作是使用类似名称的 groupby()方法。配合aggregate_function()使用
1、count()
SQL语法
SELECT sex, count(*)
FROM data
GROUP BY sex;对应pandas实现
In :data.groupby(sex).size()
Out :
sex
Female 87
Male 157
dtype: int642、avg()
SQL语法
SELECT day, AVG(tip), COUNT(*)
FROM tips
GROUP BY day;对应pandas实现
In :data.groupby(day).agg({tip: mean, day: size})
Out :
tip day
day
Fri 2.734737 19
Sat 2.993103 87
Sun 3.255132 76
Thur 2.771452 623、sum()、max()、min()
SQL语法
SELECT day, AVG(tip), SUM(tip), MAX(tip), MIN(tip), COUNT(tip)
FROM data
GROUP BY day;对应pandas实现
In :data.groupby(day).agg({tip: [mean, sum, max, min],day: size
}).reset_index()
Out :
day tip day
mean sum max min size
0 Fri 2.734737 51.96 4.73 1.00 19
1 Sat 2.993103 260.40 10.00 1.00 87
2 Sun 3.255132 247.39 6.50 1.01 76
3 Thur 2.771452 171.83 6.70 1.25 62五、HAVING
SQL语法
SELECT day, AVG(tip), SUM(tip), MAX(tip), MIN(tip), COUNT(*)
FROM data
GROUP BY day
HAVING SUM(tip) 200;对应pandas实现
In :result data.groupby(day).agg({tip: [mean, sum, max, min],day: size
}).reset_index()
In :result.columns [day, avg_tip, sum_tip, max_tip, min_tip, count_tips]
In :result[result[sum_tip] 200].reset_index()
Out :index day avg_tip sum_tip max_tip min_tip count_tips
0 1 Sat 2.993103 260.40 10.0 1.00 87
1 2 Sun 3.255132 247.39 6.5 1.01 76六、排序ORDER BY
SQL语法
SELECT *
FROM data
ORDER BY tip;对应pandas实现
In :data.sort_values(tip)
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2
67 3.07 1.00 Female Yes Sat Dinner 1 0.325733 0.325733
236 12.60 1.00 Male Yes Sat Dinner 2 0.079365 0.079365
92 5.75 1.00 Female Yes Fri Dinner 2 0.173913 0.173913
111 7.25 1.00 Female No Sat Dinner 1 0.137931 0.137931
0 16.99 1.01 Female No Sun Dinner 2 0.059447 0.059447
... ... ... ... ... ... ... ... ... ...
141 34.30 6.70 Male No Thur Lunch 6 0.195335 0.195335
59 48.27 6.73 Male No Sat Dinner 4 0.139424 0.139424
23 39.42 7.58 Male No Sat Dinner 4 0.192288 0.192288
212 48.33 9.00 Male No Sat Dinner 4 0.186220 0.186220
170 50.81 10.00 Male Yes Sat Dinner 3 0.196812 0.196812SQL语法
SELECT *
FROM data
ORDER BY tip,total_bill;对应pandas实现
In :data.sort_values([tip,total_bill])
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2
67 3.07 1.00 Female Yes Sat Dinner 1 0.325733 0.325733
92 5.75 1.00 Female Yes Fri Dinner 2 0.173913 0.173913
111 7.25 1.00 Female No Sat Dinner 1 0.137931 0.137931
236 12.60 1.00 Male Yes Sat Dinner 2 0.079365 0.079365
0 16.99 1.01 Female No Sun Dinner 2 0.059447 0.059447
... ... ... ... ... ... ... ... ... ...
141 34.30 6.70 Male No Thur Lunch 6 0.195335 0.195335
59 48.27 6.73 Male No Sat Dinner 4 0.139424 0.139424
23 39.42 7.58 Male No Sat Dinner 4 0.192288 0.192288
212 48.33 9.00 Male No Sat Dinner 4 0.186220 0.186220
170 50.81 10.00 Male Yes Sat Dinner 3 0.196812 0.196812SQL语法
SELECT *
FROM data
ORDER BY tip asc,total_bill desc;对应pandas实现
In :data.sort_values(by[tip, total_bill], ascending[True, False])
Out :total_bill tip sex smoker day time size tip_rate tip_rate2
236 12.60 1.00 Male Yes Sat Dinner 2 0.079365 0.079365
111 7.25 1.00 Female No Sat Dinner 1 0.137931 0.137931
92 5.75 1.00 Female Yes Fri Dinner 2 0.173913 0.173913
67 3.07 1.00 Female Yes Sat Dinner 1 0.325733 0.325733
0 16.99 1.01 Female No Sun Dinner 2 0.059447 0.059447
... ... ... ... ... ... ... ... ... ...
141 34.30 6.70 Male No Thur Lunch 6 0.195335 0.195335
59 48.27 6.73 Male No Sat Dinner 4 0.139424 0.139424
23 39.42 7.58 Male No Sat Dinner 4 0.192288 0.192288
212 48.33 9.00 Male No Sat Dinner 4 0.186220 0.186220
170 50.81 10.00 Male Yes Sat Dinner 3 0.196812 0.196812七、LIMIT/OFFSET
1、LIMIT
在pandas中使用head()实现
SQL语法
SELECT *
FROM data
LIMIT 10;对应pandas实现
In :data.head(10)
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2
0 16.99 1.01 Female No Sun Dinner 2 0.059447 0.059447
1 10.34 1.66 Male No Sun Dinner 3 0.160542 0.160542
2 21.01 3.50 Male No Sun Dinner 3 0.166587 0.166587
3 23.68 3.31 Male No Sun Dinner 2 0.139780 0.139780
4 24.59 3.61 Female No Sun Dinner 4 0.146808 0.146808
5 25.29 4.71 Male No Sun Dinner 4 0.186240 0.186240
6 8.77 2.00 Male No Sun Dinner 2 0.228050 0.228050
7 26.88 3.12 Male No Sun Dinner 4 0.116071 0.116071
8 15.04 1.96 Male No Sun Dinner 2 0.130319 0.130319
9 14.78 3.23 Male No Sun Dinner 2 0.218539 0.2185392、指定列中最大的前N行
SQL语法
SELECT *
FROM data
ORDER BY tip DESC
LIMIT 10;对应pandas实现
In :data.nlargest(10, columnstip)
或
In :data.sort_values(bytip, ascendingFalse).head(10)
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2
170 50.81 10.00 Male Yes Sat Dinner 3 0.196812 0.196812
212 48.33 9.00 Male No Sat Dinner 4 0.186220 0.186220
23 39.42 7.58 Male No Sat Dinner 4 0.192288 0.192288
59 48.27 6.73 Male No Sat Dinner 4 0.139424 0.139424
141 34.30 6.70 Male No Thur Lunch 6 0.195335 0.195335
183 23.17 6.50 Male Yes Sun Dinner 4 0.280535 0.280535
214 28.17 6.50 Female Yes Sat Dinner 3 0.230742 0.230742
47 32.40 6.00 Male No Sun Dinner 4 0.185185 0.185185
239 29.03 5.92 Male No Sat Dinner 3 0.203927 0.203927
88 24.71 5.85 Male No Thur Lunch 2 0.236746 0.2367463、OFFSET
跳过排序后的前5行选出接下来的10行
SQL语法
SELECT * FROM tips
ORDER BY tip DESC
LIMIT 10 OFFSET 5;对应pandas实现
In :data.sort_values(bytip, ascendingFalse).iloc[5:15]
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2
214 28.17 6.50 Female Yes Sat Dinner 3 0.230742 0.230742
183 23.17 6.50 Male Yes Sun Dinner 4 0.280535 0.280535
47 32.40 6.00 Male No Sun Dinner 4 0.185185 0.185185
239 29.03 5.92 Male No Sat Dinner 3 0.203927 0.203927
88 24.71 5.85 Male No Thur Lunch 2 0.236746 0.236746
181 23.33 5.65 Male Yes Sun Dinner 2 0.242177 0.242177
44 30.40 5.60 Male No Sun Dinner 4 0.184211 0.184211
52 34.81 5.20 Female No Sun Dinner 4 0.149382 0.149382
85 34.83 5.17 Female No Thur Lunch 4 0.148435 0.148435
211 25.89 5.16 Male Yes Sat Dinner 4 0.199305 0.199305八、UNION ALL/UNION
pandas中使用concat()函数实现
构造测试数据
In :df1 pd.DataFrame({city: [Chicago, San Francisco, New York City], rank: range(1, 4)}
)
In :df2 pd.DataFrame({city: [Chicago, Boston, Los Angeles], rank: [1, 4, 5]}
)1、UNION ALL
SQL语法
SELECT city, rank
FROM df1
UNION ALL
SELECT city, rank
FROM df2;对应pandas实现
In :pd.concat([df1, df2])
Out :
city rank
0 Chicago 1
1 San Francisco 2
2 New York City 3
0 Chicago 1
1 Boston 4
2 Los Angeles 52、UNION
SQL语法
SELECT city, rank
FROM df1
UNION
SELECT city, rank
FROM df2;对应pandas实现
In :pd.concat([df1, df2]).drop_duplicates()
Out :city rank
0 Chicago 1
1 San Francisco 2
2 New York City 3
1 Boston 4
2 Los Angeles 5九、开窗函数
1、ROW_NUMBER()
为结果集中的每一行分配一个唯一的数字,顺序为12345……
SQL语法
查询每天total_bill最大的两行数据
SELECT * FROM (SELECTt.*,ROW_NUMBER() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rnFROM data t
)
WHERE rn 3
ORDER BY day, rn;对应pandas实现
In :(data.assign(rndata.sort_values([total_bill], ascendingFalse).groupby([day]).cumcount() 1).query(rn 3).sort_values([day, rn])
)
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2 rn
95 40.17 4.73 Male Yes Fri Dinner 4 0.117750 0.117750 1
90 28.97 3.00 Male Yes Fri Dinner 2 0.103555 0.103555 2
170 50.81 10.00 Male Yes Sat Dinner 3 0.196812 0.196812 1
212 48.33 9.00 Male No Sat Dinner 4 0.186220 0.186220 2
156 48.17 5.00 Male No Sun Dinner 6 0.103799 0.103799 1
182 45.35 3.50 Male Yes Sun Dinner 3 0.077178 0.077178 2
197 43.11 5.00 Female Yes Thur Lunch 4 0.115982 0.115982 1
142 41.19 5.00 Male No Thur Lunch 5 0.121389 0.121389 22、RANK()
为结果集中的每一行分配一个排名相同的值会获得相同的排名但会跳过之后的排名,顺序为12245558……
SQL语法
查询每天total_bill最大的两行数据
SELECT * FROM (SELECTt.*,RANK() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rnFROM data t
)
WHERE rn 3
ORDER BY day, rn;对应pandas实现
In :(data.assign(rnkdata.groupby([day])[total_bill].rank(methodfirst, ascendingFalse)).query(rnk 3).sort_values([day, rnk])
)
Out :
total_bill tip sex smoker day time size tip_rate tip_rate2 rnk
95 40.17 4.73 Male Yes Fri Dinner 4 0.117750 0.117750 1.0
90 28.97 3.00 Male Yes Fri Dinner 2 0.103555 0.103555 2.0
170 50.81 10.00 Male Yes Sat Dinner 3 0.196812 0.196812 1.0
212 48.33 9.00 Male No Sat Dinner 4 0.186220 0.186220 2.0
156 48.17 5.00 Male No Sun Dinner 6 0.103799 0.103799 1.0
182 45.35 3.50 Male Yes Sun Dinner 3 0.077178 0.077178 2.0
197 43.11 5.00 Female Yes Thur Lunch 4 0.115982 0.115982 1.0
142 41.19 5.00 Male No Thur Lunch 5 0.121389 0.121389 2.03、SUM()
SQL语法
SELECTt.*,SUM() OVER(PARTITION BY day) AS snFROM data t;In :data[sn] data.groupby(day)[total_bill].cumsum()
In :data
Out :total_bill tip sex smoker day time size tip_rate tip_rate2 sn
0 16.99 1.01 Female No Sun Dinner 2 0.059447 0.059447 16.99
1 10.34 1.66 Male No Sun Dinner 3 0.160542 0.160542 27.33
2 21.01 3.50 Male No Sun Dinner 3 0.166587 0.166587 48.34
3 23.68 3.31 Male No Sun Dinner 2 0.139780 0.139780 72.02
4 24.59 3.61 Female No Sun Dinner 4 0.146808 0.146808 96.61
... ... ... ... ... ... ... ... ... ... ...
239 29.03 5.92 Male No Sat Dinner 3 0.203927 0.203927 1710.73
240 27.18 2.00 Female Yes Sat Dinner 2 0.073584 0.073584 1737.91
241 22.67 2.00 Male Yes Sat Dinner 2 0.088222 0.088222 1760.58
242 17.82 1.75 Male No Sat Dinner 2 0.098204 0.098204 1778.40
243 18.78 3.00 Female No Thur Dinner 2 0.159744 0.159744 1096.33
