Python/SQL-实习整理

Python

df.query()函数是对数据框进行行挑选的操作

df.query("expression")

例
df.query(“column==‘A’”)
等同于
df[df[“column”]==“A”]

df2是对df1的复制

df2=df1.copy()

apply应用公式

pd.df.apply(func)

例
f=lambda x: x.max()-x.min()
指定一周中的某一天作为一周的第一天,0:周一…6:周日

import calendar as cal
cal.setfirstday(firstweekday=6)

获取指定日期为周几

print(cal.weekday(year,month,day)

修改列名

df.rename(columns={"column_name":"new column_name"})

代码输出结果保留两位小数

pd.set_option('display.float_format',lambda x: '%.2f' % x)

分组求和
a=data.groupby(by=[“column”])[“coulum”].sum()
生成透视表
pt=pd.pivot_table(data,index=[“ecolumn”],aggfunc=[np.sum], fill_value=None, dropna=False)

SQL

DBeaver：数据库管理工具
数据语言DDL:Data Definition Language
可以create/drop/alter，可作用于数据库/表
数据操纵DML:Data Manipulation Language
可以select/insert/update/delete，主要作用于表
数据控制DCL:Data Control Language
可以grant（赋予权限）/revoke（取消权限），主要作用于表和列
可以赋予或者取消某个用户对表和列的DML权限

SQL查询主要包含4个处理步骤：
1.查询分析 2.查询检查 3.查询优化 4.查询执行

1.查询分析
扫描全部的SQL语句—进行语法词法分析—判断查询语句是否符合SQL语法规则
2.查询检查
对经过查询分析后符合SQL语法规则的查询语句进行语义检查，即根据数据字典判断查询语句中涉及的数据库、数据表、列等是否存在
3.查询优化
根据具体情况选择一个效率最高的执行策略
4.查询执行
对符合前三个步骤的SQL语句进行执行，并且返回查询结果

给选出的结果加一列固定值作为标签：

select id,name,"age<20" as label from demo.chapter5 where age<20;

JSON列解析：
json_extract(列名，想提取的具体的key)：对JSON列中的数据进行提取
字典：
dict，是Python提供的一种常用的数据结构，由键（key）和值（value)成对组成，键和值之间用：隔开，项之间用逗号隔开，整个字典用{}括起来。
dic={key1:value1,key2:value2}
提取两个列表中对应的元素

zip(list1, list2)：
#以字典形式dict(zip(list1, list2))或者列表形式list(zip(list1,list2))储存

不等于空格/null/空值：

!=""/is not null/!=""

删除缺失值一般情况下会删除整行/整列，造成数据的浪费，所以可以采用填充的方式填补缺失值
coalesce(null,null,…,null,value)，填充的是null，不能填充空格和空值
select order_id,member_id,coalesce(profession,“其他”) from demo.chapter6

NULL和空值的区别
NULL在字段中储存NULL值，占用空间；空值在字段中储存空字符，长度为0，不占用空间
COUNT()基数时会自动过滤null值，但是不会过滤空值
IFNULL(列名，设定值)，不是null，返回真值，是null，返回设定值

删除重复值
distinct
重命名
as
更改某一列的数据类型
cast(列名 as type)/convert(列名 as type)
数学运算：
abs绝对值/ceil最小整数/floor最大整数/rand随机数
小数点位数调整：round(列名/数值，小数点个数)
数值正负判断：
sign(列名)
字符串替换：
replace(列名/字符串，“需要被替换的字符”，“用来替换的字符”)
字符串合并：
concat(列名，列名)
添加符号的字符串合并：
concat_ws(“符号”，列名，列名)
字符串截取：
左边截取：left(列名/字符串，从左边数需要截取的字符个数)
右边截取：right(列名/字符串，从右边数需要截取的字符个数)
中间截取：substring(列名/字符串，第一个字符截取位置，想截取的字符个数)
字符串匹配：
like有两种匹配符号：%匹配任意长度的字符；匹配单个长度的字符
like “a%” 第一个字符是a的所有字符串
like “%a%” 包含字符a的所有字符串
like "a" 第一个字符是a，且长度为2的字符串
想查询不包含某字符的信息，可以用not like
去除字符串空格：
去除字符串左边空格：ltrim(列名/字符串)
去除字符串右边空格：rtrim(列名/字符串)
去除字符串两边空格：trim(列名/字符串)
去除中间的空格：replace(列名/字符串，需要去除的空格情况" “等，”")
字符串重复：将同一个字符串重复若干次后合并成一个字符串，repeat(字符串，次数)
聚合运算：
将多个值聚合在一起进行某种运算
总体方差var_pop(列名)
样本方差var_samp(列名)
标准差具有实际意义，和原始数据单位一致
总体标准差std(列名)
样本标准差stddev_samp()

控制函数
if函数：

if(条件，条件为真的返回值，条件不为真的返回值)

if可以嵌套

if(条件，条件为真的返回值，if(条件,条件为真的返回值，条件不为真的返回值))

case when函数：
case 列名
when 条件1 then 返回值1
when 条件2 then 返回值2
…
when 条件n 返回值n
else 返回默认值
end

日期和时间函数
日期指年月日，时间指时分秒
当前时刻日期和时间now()
当前时刻日期curdate()/data(now())
当前时刻年/月/日 year/month/day(now())
当前时刻时间curtime()/time(now())
当前时刻小时/分/秒 hour/minute/second(now())
日期和时间的格式转换

data_format(日期时间,需要转换成的目标格式)

返回一个具体日期和时间中的单独部分，unit可以是年月日时分秒周

extract(unit from datetime)

日期向后偏移

data_add(data,interval num unit)

日期向前偏移

data_sub(data,interval num unit)
data_add(data,interval num unit)中的num写为负值

两个日期作差

datadiff(end_date,start_date)

???数据分组
分组后将同组内容连接group_concat(列名) 将该列中属于同一组的以一定形式合并起来，需要和group by联合使用
根据不同维度分组聚合后汇总rollup
将两个查询结果纵向合并 union all，放在两个查询结果中间/group by 列名 with rollup
数据透视表
group by和case when结合

窗口函数

与数据分组类似，但是比数据分组的功能丰富
数据分组是将组内多个数据聚合成一个值，窗口函数除了可以将组内数据聚合成一个值，还可以保留原始的每条数据。
聚合函数+over()函数
select id,(select avg(sales) from demo.chapter) as avg_sales from demo.chapter
可以化简为select id,avg(sales) over() as avg_sales from demo.chapter
over()函数的作用是将聚合结果单独显示在每条记录中，原始数据不改变。
聚合函数+over()函数+partition by子句，分组后再聚合
select id,avg(sales) over(partition by shopname) as avg_sales from demo.chapter
聚合函数+over()函数+partition by子句+order by子句，分组后按照顺序聚合
select id,avg(sales) over(partition by shopname order by sale_date) as avg_sales from demo.chapter

序列函数

ntile(num) over()对整张表的数据进行切片分为num组，并显示分组情况，over()中可以添加partition by/order by子句
row_number()用来生成每条记录对应的行数，即第几行，结果中不会出现重复值，与order by结合使用
lag()让数据向后移动
lead()让数据向前移动，目的是将数据放置到对应位置

多表连接

表的横向连接的四种方式：
left join/right join/inner join/outer join 表 on公共列名
表的三种连接类型：
一对一/一对多/多对多
一对一：用于连接两张表的公共列的值在左表和右表中没有重复值
一对多：用于连接两张表的公共列的值在左表和右表中其中一个表有重复值
则没有重复值的那一个表会自动复制成多条记录
多对多：用于连接两张表的公共列的值在左表和右表中都有重复值，即笛卡尔积
如果有公共列的值在左表中重复出现了m次，在右表中重复出现了n次，连接结果为m*n条
实际工作中，为避免多对多出现，一般先处理重复值
表的纵向连接的两种方式
union/union all
union删除重复值
union all不删除重复值

子查询：内层查询
主查询：外层查询

EXCEL

IF(OR(IF(AND(O2550=“电脑整机”,T2550>200),1,0),IF(AND(O2550=“办公网络”,T2550>200),1,0),IF(AND(O2550=“服务器/工作站”,T2550>60),1,0),IF(AND(O2550=“家电”,T2550>100),1,0),IF(AND(O2550=“家具”,T2550>70),1,0),IF(AND(O2550=“整车”,T2550>15),1,0),IF(AND(O2550=“回收”,T2550>50),1,0),IF(AND(O2550=“租赁”,T2550>50),1,0),IF(AND(O2550=“综合”,T2550>200),1,0),1,0))

选定单元格后，使用快捷键：ctrl+1（打开单元格设置）
设置自定义格式：0%;[红色]-0%
或者使用条件格式，设置条件，选择想应用的格式。

辛普森悖论

是一个统计学名词，1951年提出
当我们对同一组数据以分组和聚合这两种方式进行统计分析时，可以用相同的数据得出两个相反的结论。
即：在某个条件下，两组数据都满足某一个性质，但是一旦合并考虑，可能会导致相反的结论。
辛普森悖论出处：
一所高校的两个学院进行新学期招生，未完结？