007数据分析能力：SQL

最新推荐文章于 2022-06-21 18:03:04 发布
最新推荐文章于 2022-06-21 18:03:04 发布
阅读量614 收藏
点赞数
分类专栏： 笔试面试-数据分析
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/iven2166/article/details/101930812
版权
SQL
理论及函数之间比较
如何优化查询避免数据倾斜等问题
详细的查询数

文章目录

1 函数、之间比较

建表

auto-increment

用于 MySQL 的语法
下列 SQL 语句把 “Persons” 表中的 “P_Id” 列定义为 auto-increment 主键：

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

foreign-key

订单表格的客户id = 客户表格的客户id

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
);

删除

mysql中删除表记录delete from和truncate table的用法区别:
MySQL中有两种删除表中记录的方法:
(1)delete from语句，
(2)truncate table语句。

delete from语句可以使用where对要删除的记录进行选择。delete语句更灵活。
truncate table将删除表中的所有记录。

情况一：清空表中的所有记录，可以使用下面的两种方法：
delete from tablename
truncate table tablename
其中第二条记录中的table是可选的。

情况二：删除表中的部分记录，只能使用delete语句。
delete FROM table1 WHERE ;

顺序

书写顺序
1. select top
2. from table
3. join table_B on ...
4. join table_C on...
5. where
6. group by var1
7. having ...
8. order by ...

参考

执行顺序
1. from 
2. join
3. on
4. where
5. group by 
6. having
7. select 
8. distinct
9. order by

正则表达式：REGEXP

正则表达式参考1
案例：匹配手机号码

select phone_num from table where REGEXP_LIKE(phone_num, '^[1]{1}[35678]{1}[0-9]{9}$'); # 可替代为[[:digit:]]{9}

^、$：代表字符开始与结束
{n}：字符数字出现n次

isnull(variable_name, replace_value)

比如在计算全部产品总价格时，订单单价 乘 订单数+库存数，需要注意可能存在空值，要替换为0.

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

decode赋值（结合sign等函数）

现定义一table名为output，其中定义两个column分别为monthid（var型）和sale（number型），若sale值=1000时翻译为D，=2000时翻译为C，=3000时翻译为B，=4000时翻译为A，如是其他值则翻译为Other：

Select monthid , decode (sale,1000,'D',2000,'C',3000,'B',4000,'A',’Other’) sale from output;

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1，
用如下的SQL语句取较小值：

select monthid,decode(sign(sale-6000),-1,sale,6000) from output;

exists & in, not exists & not in

in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in。

例题：使用含有关键字exists查找未分配具体部门的员工的所有信息。

select * from employees as e
where not exists (select emp_no from dept_emp where emp_no = e.emp_no);

group_concat(var_sub separator ‘,’)

返回每个部门下面的员工，用逗号

select dept_no,group_concat(emp_no SEPARATOR ',') from dept_emp group by dept_no;

substr(var_string, start, end)

如果是从后往前，则为单参数 substr(var_string, -3)，倒数三个

having & where

相同点：

  • 都是对数据进行过滤，只保留符合条件的有效数据

不同点：

  • having是从前面筛选的字段里再筛选，where是从数据表中的字段直接筛选的
  • where是不能用字段的别名，having可以（由于where是直接筛选的，所以别名是不能用的，比如下面的where avg(price)是不合法的）
select id, avg(price) as ag from goods **where ag > 100** group by goods_category; # 不合法

类型的区别
where是一个约束声明（E.G. 比如数据库的分区字段），在查询的结果返回之前对查询条件进行约束，并且后面不能写“聚合函数”。

having是一个过滤声明，在查询的结果返回之后对查询条件进行约束，并且后面可以写“聚合函数”。

聚合函数：sum, min, max, avg, count

在from后面的执行顺序：可以协助判断是否先约束还是后过滤（where先进行条件的约束，之后再进行聚合函数的计算，得到结果返回之后用having进行筛选）：where > 聚合函数 > having

union & union all

  1. UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。
  2. 对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
  3. 对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。
  4. 从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL

窗口函数：row_number & rank

用法是一样的，都是在函数之后接上 over (已排序的字段)
row_number是对每一行数据进行序号标记（无重复），而rank是对每一行进行序号标记但是允许同个水平是同一个序号（有重复）。

select row_number() over(order by var_x desc) as row_num, * from table;

2 如何优化

参考1
参考2

大表并大表

PV表a(表结构为user_id,goods_id),点击表b(user_id,goods_id),数据量各为50万条，在防止数据倾斜的情况下，写一句sql找出两个表共同的user_id和相应的goods_id

个别函数的使用

  1. where：尽量少对null进行筛选，不然会放弃索引而全表扫描，应该将null设置为0，再用where取0
  2. where：避免使用!= 或者<>，否则会全表扫描
  3. where：不要在 = 的左边进行运算，比如select xxx from table where Y/2=100;
  4. 用in或者not in：尽量用between等去代替

数据倾斜

数据倾斜描述的是Map-Reduce中，由于存在空值 key 的分布严重不均匀，导致的很多数据交给了一个reduce，而剩下的少部分到了不同的reduce处理。所以在reduce方面可能卡在了99%

原因：

  1. join两个大表，存在大量的null值，这些都在一个reduce里面处理
  2. 函数 distinct 处理大表，遇到较多的特殊值
  3. group by的维度过小，某些reducer处理速度较慢
  4. 合并的表格的字段，存在着不同的类型——int或者字符，hash会自动地将int作为若干个group，而字符汇成一个group

s

如何调优：

  1. join 操作中，使用 map join 在 map 端就先进行 join
  2. 让小表 join 大表，使得小表先进内存
  3. 大表 join 大表的过程中，把空值的 key 用随机数附在字符串后面，把倾斜的数据分到不同的reducer上。
  4. 在用count distinct 的过程中，用group by 来代替

3 其他（比如面试题里面sql的难点）

你觉得的mysql中的难点

hadoop原理和mapreduce原理

1）Hadoop原理：采用HDFS(Hadoop Distributed File System)分布式存储文件，MapReduce分解计算，其它先略
2）MapReduce原理：

a. map阶段：读取HDFS中的文件，解析成<k,v>的形式，并对<k,v>进行分区（默认一个区），将相同k的value放在一个集合中
b. reduce阶段：将map的输出copy到不同的reduce节点上，节点对map的输出进行合并、排序

————————————————
版权声明：本文为CSDN博主「稻蛙」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u013382288/article/details/80450360

SQL的数据类型

1）字符串：char、varchar、text
2）二进制串：binary、varbinary
3）布尔类型：boolean
4）数值类型：integer、smallint、bigint、decimal、numeric、float、real、double
5）时间类型：date、time、timestamp、interval

4. 详细查询示例

更新

有两个表A 和B ，均有key 和value 两个字段，如果B 的key 在A 中也有，就把B 的value 换为A 中对应的value
这道题的SQL 语句怎么写？

update b set b.value=(select a.value from a where a.key=b.key)
where b.id in(select b.id from b,a where b.key=a.key);

条件判断

(case var_0 when 1 then var_1 * 0.1 
				  when 2 then var_1 * 0.2
				  else var_1 * 0.3 end) as var_3

下面的例子是，btype表示奖金类型，如果是1，则奖金为工资乘以10%，是2，则奖金为工资乘以20%，以此类推到else，最后再接上end。

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(CASE b.btype 
 WHEN 1 THEN s.salary * 0.1
 WHEN 2 THEN s.salary * 0.2
 ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

写出中位数、众数

中位数

--没有考虑偶数的情况
set @mm = (select count(*)/2 from table);
select var_1 from table order by var_1 limit @mm, 1;

--考虑偶数的情况
set @i = -1;
select avg(t.var_1) from 
(select @i := @i + 1 as ii, var_1 from table order by var_1) as t
where t.ii in (floor(@i/2), ceiling(@i/2));

众数

select var_2, count(*) as cnt from table
group by var_2
having count(*) >= 
select max(cnt) from (select count(*) as cnt from table group by var_2);

升级版中位数
三个班级合在一起的一张成绩单，统计每个班级成绩中位数
解：设表table中字段为id，class，score

select t1.class, avg(distinct t1.score) as median
from table t1, table t2 on t1.id = t2.id
group by t1.class, t1.score
having sum(case when t1.score >= t2.score then 1else 0 end) >=
(select count(*)/2 from table where table.class = t1.class)
and
having sum(case when t1.score <= t2.score then 1else 0 end) >=
(select count(*)/2 from table where table.class = t1.class);

————————————————
版权声明：本文为CSDN博主「稻蛙」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u013382288/article/details/80450360

手动写出row_number：

SET @row_number = 0; 
SELECT (@row_number:=@row_number + 1) AS num FROM table;

次日留存率

字段及表说明：
表名：user_log
字段名：
log_day:登录日期
device_id:用户设备id
app_id:用户app的id，其中device_id和app_id确定唯一的用户

连字符：var1 || var2

select log_day, count(user_day1) / count(user_day0) as retention1
count(user_day3)/count(user_day0) as retention3
from (
	select a.log_day, a.user_day0, b.device_id || b.app_id user_day1, 
	c.device_id || c.app_id user_day3 from 
		(select log_day, device_id || app_id user_day0 from user_log tmp1 
		where device_id || app_id not in (select distinct device_id || app_id from user_log tmp2 having tmp2.log_day < tmp1.log_day)) a
		left join user_log b on a.log_day=(b.log_day - 1) and a.user_day0=b.(device_id || app_id)
		left join user_log c on a.log_day=(c.log_day - 3) and a.user_day0=c.(device_id || app_id)
)
group by log_day;

查询连续几天登录的用户

思路：如果是三天连续登录，首先把每一个用户单独判断，用到group by，并且在每个用户里进行日期的排序，row_number() over partition (order by )，或者使用datediff得到每天与最小一天的差形成新的一列。

join三个相同的表，我们查询的是第一个表，但是第一个表的日期的排序号码，可能在三天内的第一位，或者第二位，或者第三位，然后有
(s1.dayi = s2.dayi - 1 and s2.dayi = s3.dayi - 1) or
(s2.dayi = s1.dayi - 1 and s1.dayi = s3.dayi - 1) or
(s2.dayi = s3.dayi - 1 and s3.dayi = s1.dayi - 1)

查询过去一个月的最高付款量是哪几天

交易表结构为user_id,order_id,pay_time,order_amount
写sql查询过去一个月付款用户量（提示 用户量需去重）最高的3天分别是哪几天
写sql查询做昨天每个用户最后付款的订单ID及金额

1）select count(distinct user_id) as c from table group by month(pay_time) order by c desc limit 3
2）select order_id, order_amount from ((select user_id, max(pay_time) as mt from table group by user_id where DATEDIFF(pay_time, NOW()) = -1 as t1) left join table as t2 where t1.user_id = t2.user_id and t1.mt == t2.pay_time)
————————————————
版权声明：本文为CSDN博主「稻蛙」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u013382288/article/details/80450360

随机抽取用户，按照年龄段分层抽样

表结构为user_id,reg_time,age, 写一句sql按user_id随机抽样2000个用户 写一句sql取出按各年龄段（每10岁一个分段，如（0,10））分别抽样1%的用户
1）随机抽样2000个用户
select * from table order by rand() limit 2000

2）取出各年龄段抽样1%的用户
set @target = 0
set @count_user = 0
select @target:=@target+10 as age_right, *
from table as t1
where t1.age >=@target-10 and t1.age < (@target)
and t1.id in
(select floor(count(*)*0.1） from table as t2
where t1.age >=@target-10 and t1.age < (@target)
order by rand() limit ??)

查询30天内连续访问7天以上的用户数量

用户登录日志表为user_id,log_id,session_id,plat,visit_date 用sql查询近30天每天平均登录用户数量 用sql查询出近30天连续访问7天以上的用户数量

1）近三十天每天平均登录用户数量

select visit_date, count(distinct user_id)

group by visit_date

2）近30天连续访问7天以上的用户数量

select t1.date

from table t1, table t2, …, table t7

on t1.visit_date = (t2.visit_date+1) and t2.visit_date = (t3.visit_date+1)

and … and t6.visit_date = (t7.visit_date+1）

表user_id,visit_date,page_name,plat 统计近7天每天到访的新用户数，统计每个访问渠道plat7天前的新用户的3日留存率和7日留存率

1）近7天每天到访的新用户数

select day(visit_date), count(distinct user_id)

from table

where user_id not in

(select user_id from table

where day(visit_date) < date_sub(visit_date, interval 7day))

2）每个渠道7天前用户的3日留存和7日留存

三日留存
先计算每个平台7日前的新用户数量

select t1.plat, t1.c/t2.c as retention_3

(select plat, count(distinct user_id)

from table

group by plat, user_id

having day(min(visit_date)) = date_sub(now(), interval 7 day)) as t1

left join

(select plat, count(distinct user_id) as c

from table

group by user_id having count(user_id) > 0

having day(min(visit_date)) = date_sub(now(), interval 7 day)

and day(max(visit_date)) > date_sub(now(), interval 7 day)

and day(max(visit_date)) <= date_sub(now(), interval 4day)) as t2

on t1.plat = t2.plat

iven2166
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
python数据分析007——数据可视化（下）
qq_41680326的博客
02-16 551
数据可视化：股票数据分析 ''' 可通过以下指令直接安装（建议分开运行，逐个安装。） ''' # pip install pandas-datareader # pip install yfinance '\n可通过以下指令直接安装（建议分开运行，逐个安装。）\n' import pandas as pd from pandas_datareader import data as pdr im...
数据分析专栏-SQL学习数据库表
01-07
通过实践和学习，你可以更加熟练地运用SQL与SQLite，提升你在数据分析领域的专业能力。对于初学者来说，可以从简单的查询开始，逐步深入到复杂的联接和聚合函数，最终实现对数据的高效管理和分析。
数据库007
billgates_wangtiezhu的博客
01-07 898
having语句 和where一样的作用，能对聚合函数进行过滤，且只能用于聚合函数，运行在分组过后。而where 不能有聚合函数，且运行在分组过后。 sql语句执行顺序 select子句 from子句 where子句 group by子句 order by子句 having子句. 子查询  是嵌套在主查询中的查询，也称内部查询，包含子查询的语句也称外部查询。 select  colum
玩转职场数据分析
涛哥聊Python
08-21 472
经常有朋友微信问我：“如何学习数据分析？如何转行数据分析？如何快速掌握数据分析技能？高薪的大数据分析师都必须会哪些技能......“等等类似的问题。由于无法通过一两句来回复这类问题，今天...
数据分析人员需要掌握SQL到什么程度？3个常考题目刷一刷
博文视点（北京）官方博客
01-06 1090
在数据类岗位招聘过程中，经常会考察求职者的SQL能力，这里整理了3个常考的SQL数据分析题，按照由简单到复杂排序，一起来测试一下你掌握了么？ PS：以下SQL代码在MySQL8.0及其以上版本运行。 题目1：找出每个部门工资第二高的员工 现有一张公司员工信息表employee，表中包含如下4个字段。 employee_id（员工ID）：VARCHAR。 employee_name（员工姓名）：VARCHAR。 employee_salary（员工薪资）：INT。 department（员工所属部门ID）：V
大数据007——HDFS2.0
sinat_34045444的博客
01-08 317
1. Hadoop 2.0 1.1 Hadoop1.0于Hadoop2.0的区别 1）. 从整体框架来说 ​ a. Hadoop1.0即第一代Hadoop，由分布式存储系统HDFS和分布式计算框架MapReduce组成，其中HDFS由一个NameNode和多个DateNode组成，MapReduce由一个JobTracker和多个TaskTracker组成。 ​ b. Hadoop2.0即第二代...
数据分析课程：Excel透视表，SQL概念等
07-11
在数据分析领域，Excel透视表和SQL是两种非常重要的工具，它们在数据整理、探索和洞察发现中发挥着关键作用。本课程围绕这两个核心主题展开，旨在帮助学习者掌握高效的数据处理技能。 首先，我们来详细了解一下...
python 零基础学习篇-14课程：SQL数据分析及变更（下）.zip
04-27
通过学习这个课程，你将掌握如何用Python进行SQL数据分析和变更，从而提升你在大数据环境下的工作效率和分析能力。不论是进行常规的数据查询，还是进行复杂的统计分析，Python与SQL的结合都将是你手中强大的工具。
SQL-基于sql实现的数据分析项目实战.zip
03-09
综上所述，"SQL-基于sql实现的数据分析项目实战"涵盖了从基础到高级的SQL应用，通过实际案例，学习者可以掌握如何运用SQL解决实际问题，提升数据驱动决策的能力。通过实践，不断磨炼SQL技能，将使你在数据分析领域...
数据分析之SQL常问知识点&sql五十题示例.zip
最新发布
05-17
掌握SQL，解锁数据分析新境界——SQL常问知识点与五十题实战示例 在大数据时代，SQL已成为数据分析师和数据库管理员的必备技能。你是否曾因对SQL的某些知识点掌握不够深入而感到困惑？是否渴望通过实战练习来巩固和...
分班级计算中位数
07-09
很实用的班级中位数，有效发现学生成绩。直接根据已知数据，自动生成中位数，简单有效方便。
SQL窗口函数篇
YYIverson的博客
03-25 2258
转载出处：https://zhuanlan.zhihu.com/p/116012207 一.SQL中的进阶函数 1.窗口函数 窗口函数和普通聚合函数的区别： ①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。 ②聚合函数也可以用于窗口函数。 原因就在于窗口函数的执行顺序（逻辑上的）是在FROM，JOIN，WHERE，GROUP　BY，HAVI...
找出连续7天登陆，连续30天登陆的用户
weixin_44976611的博客
06-21 353
无摘要
SQL练习：查询近30天活跃用户数
全栈川川
06-14 1995
活动记录表：Activity 请写SQL查询出截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。以 任意顺序 返回结果表。查询结果示例如下： 求解 思路：为什么不用seesion_id。仔细看一下用户id，为什么一直在增加？今天毁约用户从1增加到2，明天活跃用户就是从3增加到4，说明这个是累加的。而session_id不代表这个含义。 执行：...
数据分析师基本技能——SQL
古月哲亭
05-17 7513
我们做数据分析工作时，多数数据来源于数据库，SQL非常方便我们访问和查询数据库。SQL 作为数据分析师的基本技能，那么需要掌握哪些SQL核心技能理解数据库SQL基础重点知识：查询，更新，提取，插入，删除等数据操作 典型实例sql1.理解数据库数据库基本定义：数据库（Database）基本含义为：存储数据的地方，确切地来说是一张张表格，每张表通过一定联系链接在一起，最后组成了数据库可以简单的理解为：...
SQL面经
布纸所云
09-14 4228
【秋招】拼多多数据分析岗面试题整理 SQL查询实例 SQL面试题练习 SQL面经汇总 1、交易表结构为user_id,order_id,pay_time,order_amount 写sql查询过去一个月付款用户量（提示 用户量需去重）最高的3天分别是哪几天 select date_format(pay_time,'%Y-%m-%d') as date, count(distinct u...
查询连续登陆7天以上的用户 Mysql
lipeitong333的博客
02-24 1819
查询7天连续登陆用户这个问题很经典，解决方法也有很多，这里我讲一下笔者的方法，希望对大家有帮助。 具体思路: 1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。 2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。 3、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。 4、按照id和日期分组并求和，筛选大于等于7的即为连续7天登陆的用户。 表信息如下图：
mysql题 以下1-7_Mysql 面试习题（一）深坑
weixin_42619061的博客
01-20 541
•SQL语言允许使用通配符进行字符串匹配的操作，其中‘%’可以表示：A.零个字符B.1个字符C.多个字符D.以上都是D•如果有两张表employees和departments如下：(1)写出SQL得出每个部门的平均工资。(2)查询量表得出如下结果：如上解答为一种方式，根据自身理解可以按照其他字段做筛选条件•如有有一张表player，有id、name两个列，我想知道这张表有多少行数据，id最大的前1...
拼多多面经分享：24个「数据分析师」岗位面试题和答案解析
SeizeeveryDay的博客
05-21 2201
01问：贝叶斯公式复述并解释应用场景P（A|B) = P(B|A)*P(A) / P(B)如搜索query纠错，设A为正确的词，B为输入的词，那么：P(A|B)表示输入词B实际为A的概率...
数据分析实践 使用Excel和SQL进行数据分析 数据分析教程课件 数据分析实战课程 共94页.pdf
04-29
本资源是一份详细的课程资料，旨在帮助学习者掌握数据分析实践中...通过学习这门课程，学员不仅可以掌握Excel和SQL的实用技能，还能培养解决问题和决策支持的能力，从而更好地在实际工作中应用数据分析来驱动业务发展。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值