【Mysql】企业基础应用

网站不知名写手柠乐同学

于 2021-05-26 18:17:18 发布

阅读量458

点赞数

分类专栏：笔记文章标签： mysql sublime text 数据库

本文链接：https://blog.csdn.net/kyra1997/article/details/117018340

版权

笔记专栏收录该内容

69 篇文章 4 订阅

订阅专栏

数据库架构

常见的架构有ods/edw/edw_s/ddm他们各自有不同的储存特点

ods（Operational Data Store）：操作型数据仓库
    同步业务生产过程中的所有数据，比edw更加详细，冗余和复杂度也更高（同步所有表格遵从时间or某行为规则，备份过多）

edw（Enterprise Data Warehouse）：企业数据库
    已处理完成并进行过汇总的，可供业务直接使用的企业数据库（需要分配处理的规范数据库）

edw_s：安全级别更高的企业数据库
    存储安全级别较高的敏感或机密数据，例如用户的身份证号、手机号、真实姓名
    加密性更高，一般需要独立授权（设定了固定权限）

ddm（Distributed Database Middleware）：局部数据库
    专门针对某个具体的应用或需求建设的局部数据库，只关心自己需要的数据。
不会全盘考虑企业整体的数据架构和应用，每个应用都有自己的DM。所以DM可以基于仓库建设也可以独立建设。（根据实际需求生产的）

数据字典梳理

场景：刚来公司的第一天，想看一下XXX有哪些字段，用describe查看数据字典，康康表格有哪些字段以及字段的数据类型和注释。

1、 datagrip下载字段（语法：describe 表）
注：datagrip不能运行mysql，对应运行代码的是服务器

2、下载excel文件，梳理常用字段
数据导出
导出csv或xlsx数据表用于熟悉数据字段，不懂或不清晰的一定要向同事或数仓请教
mysql导出csv乱码，是csv文件本身的文本编码问题导致的
1. 鼠标右键点击选中的 csv 文件，在弹出的菜单中选择“编辑”，则系统会用文本方式（记事本）打开该 csv 文件；
2. 打开 csv 文件后，进行“另存为”操作，在弹出的界面底部位置有“编码”，修改编码方式即可：从UTF-8改成 ANSI 。保存；
3. 再用 excel 打开后，显示汉字正常。
4.多表链接统一别名
5.字段、字符型、名称、注释
6.常用sublime记录代码
7.学会查找报错（书写、语法、逻辑）

代码规范

基础语法

在这里插入图片描述

select

    SQL
        基于from、join、where、group by、having处理好的数据表格，按照select后的计算规则，计算并显示select后的字段

    Excel
        直接在表格中用函数计算，然后按顺序复制粘贴

where

    SQL（行筛选）
        基于from复制的表格，按照where后的条件对表格中的行进行筛选
        =单个数值精确匹配
            where 平台 = 'meituan'

        between A and B 匹配包含AB及AB之间的所有值
            where 日期 between '2019-12-01' and'2019-12-07'

        in(A,B,C,D)可以实现多选，not in可以反选
            where 日期 in ('2019-09-01','2019-10-01','2019-11-01','2019-12-01')
            where 日期 not in ('2019-09-01','2019-10-01','2019-11-01','2019-12-01')

        and/or可以并列多个条件
            and 且
                where 日期 between '2019-12-01' and'2019-12-07' and 平台 = 'meituan'

            or 或
                where 门店名称 = '五角场店' or 门店名称 = '大世界店'


        like配合通配符进行模糊匹配
            '%'代表任意数量字符，"_"只代表单个字符

            where 品牌名称 like '%拌客%'


    Excel
        自定义筛选

group by

     SQL
     	对数据进行分组 = 对group by后的字段进行去重合并，并作为后续聚合运算的依据
        group by其实只指定了聚合函数的计算依据

        具体的聚合运算还是要由聚合函数进行
		因为group by先运行，并且运行后，表格中的非聚合字段已经形成了，
 所以select后的非聚合字段一定要与group by后指定的字段一致，也就是说：
 group by后没有的非聚合字段select无法显示，但group by后有的非聚合字段select可以选择不显示

    为什么group by可以使用1,2等数字直接引用select后的字段？select不是最后运行吗？
        因为，select会运行两次
            第一次最先运行：按照字段原始名称和别名新建表头
                代码还没运行完毕，表头就已经生成了
            第二次最后运行：基于from、join、where、group by、having处理好的数据表格，
      按照select后的计算规则，计算并在表头后显示字段对应的数值

        所以group by可以使用1,2等数字直接引用select后的字段名称，直接用于去重合并

    顺便提一句，order by也可以直接使用数字引用字段
        但是作为筛选条件，要尽量明确

having

由于SQL的单向执行的，而where已经在group by之前执行过了，如果要在group by后再对局和预算的结果进行筛选，需要使用新语句having
并且，having是在分组（去重合并）后运行的，因此having只可以对作为分组依据的非聚合字段和任意分组后聚合运算的结果进行筛选，除了对象，having具体的语法和where完全一致

只可以对作为分组依据的group by 后的非聚合字段进行筛选
在这里插入图片描述

select
品牌名称
,门店名称
,sum(GMV)
,sum(下单人数)
from ddm.shop
where 日期 between '2019-12-01' and '2019-12-07'
group by 1,2
having 门店名称 in ('五角场店','真如店','宝山店')
order by sum(GMV) desc
limit 10

对任意分组后聚合运算的结果进行筛选
在这里插入图片描述

select
品牌名称
,门店名称
,sum(GMV)
,sum(下单人数)
from ddm.shop
where 日期 between '2019-12-01' and '2019-12-07'
group by 1,2
having 门店名称 in ('五角场店','真如店','宝山店') and sum(曝光人数) > 100
order by sum(GMV) desc
limit 10

Excel
    数据透视表-拖拽字段到行

order by

    SQL（去重合并）
        按照字段的顺序对表格的行进行排序，默认升序，字段后加DESC为降序
        order by在select前运行，因此可以依据select后没有，但表格中有的字段进行排序


    Excel
        升序、降序
        自定义排序

limit（和order by连用）

    SQL
        按照order by排序后的表格，限制最终显示表格的行数
        limit n 则显示前n行，如：limit 10 显示前10行
        limit x,y 则从第x行后显示y行，如limit 10,2则会显示第11和12行
        常用来取固定名次的数据

    Excel
        只保留指定行数

聚合函数

聚合函数也可以不和group by一起用，这样就可以求整个表格的数值
    select sum(GMV) from ddm.shop

sum() - 返回总和
count() - 返回行数
distinct去重计数
distinct还可对单独字段去重查询

select distinct 门店名称 from ddm.shop

如果同时对两个字段去重查询会怎样？

eg：select distinct 门店名称,distinct 品牌名称 from ddm.shop
       SQL会直接报错
       Excel会删除两个字段都重复的
       Tableau会合并两个字段都重复的数据

可以用group by实现多个字段去重合并

count(distinct 字段)

 count(*)可以快速查看表格有多少行，又不占用太多资源

avg() - 返回平均值
max() - 返回最大值
min() - 返回最小值

eg：查询所有门店每天的GMV和CPC消耗【表连接】，GMV和CPC在两个表格里，如何把他们放到一个表格里呢？

select
门店名称
,日期
,GMV
,cpc总费用
from ddm.shop shop 
join ddm.cpc cpc 
on shop.门店ID = cpc.门店ID and shop.日期 = cpc.日期

join

    表面上，join是各种逻辑的表连接
    实际上，join是一种运算逻辑（算法）
        遍历匹配
        向下增添

inner/left/right/full

在这里插入图片描述

只是基于连接键是否为空值的筛选方式

join = inner join
left join
right join
full join

mysql不支持完全外部连接，需要union两个左右连接

union 和 union all 的区别

            1、对重复结果的处理不同
                union all是直接连接，取到得是所有值，记录可能有重复；union 是取唯一值，记录没有重复。
                所以union在进行表链接后会筛选掉重复的记录，union all不会去除重复记录。

            2、对排序的处理不同
                union将会按照字段的顺序进行排序；union all只是简单的将两个结果合并后就返回。
                从效率上说，union all 要比union快很多。
                如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用union all。
            注意
                1、union 和 union all都可以将多个结果集合并，而不仅仅是两个，所以可将多个结果集串起来。 
                2、使用union和union all必须保证各个select 集合的结果有相同个数的列，
          并且每个列的类型是一样的。但列名则不一定需要相同，oracle会将第一个结果的列名作为结果集的列名。
                on后面的筛选条件和where的筛选条件有什么不同？
                on比where运行得早，数据库会根据on的条件对数据进行筛选再进行连接
        而where则是连接完成后，再进行筛选

窗口函数

order by和limit是全局排序无法指定一个分类取分类内排序
窗口函数是基于原有的行计算~
eg：

select
*
from
(
    select
    门店名称
    ,日期
    ,row_number() over(partition by 门店名称 order by GMV desc) r//根据门店名称分区，每个门店的gmv进行降序，目的是找每个门店最高的gmv
    ,GMV
    from ddm.shop
    where substring(日期, 1, 4) = '2020'

) a
where a.r = 1

row_number

根据分区内的排序，分配唯一且连续排名序号。顺序是基于group by之后进行运行的。

eg：over(partition by 门店名称,平台 order by 日期) //先分组在选择字段排序（门店先分区平台在分区选取日期最近的）
在这里插入图片描述
计算over()前函数的结果【窗口函数合集】

各种聚合运算都支持

根据排序分配序号

  row_number() ：根据分区内的排序，分配唯一且连续排名序号
 ,row_number() over(partition by 门店名称,平台 order by 日期) r

在这里插入图片描述

    dense_rank()：根据分区内的排序，分配不唯一且连续排名序号
    //有并列
    ,dense_rank() over(partition by 门店名称,平台 order by 日期) dense_r
    //over先运行后面后运行前面

在这里插入图片描述

    rank() ：根据分区内的排序，分配不唯一且不连续排名序号//算成绩有场景
   ,rank() over(partition by 门店名称,平台 order by 日期) rank_r

在这里插入图片描述

根据排序取具体字段数值

 first_value(字段)：根据分区内的排序，返回排在第一行的对应的字段数值（降序就可以去最后一行对应的数值）
 ,first_value(GMV) over(partition by 门店名称,平台 order by 日期) first_gmv//在分区中的选中日期范围中第一天gmv数据
 ,first_value(GMV) over(partition by 门店名称,平台 order by 日期 desc) lastgmv//在分区中的选中日期范围中最后一天gmv数据

在这里插入图片描述

    nth_value(字段,n)：返回分区内的排序第n行的字段数值。
    如果第n行还未运行到，则返回NULL。n必须是正整数，例如1,2和3。
    ,nth_value(GMV,3) over(partition by 门店名称,平台 order by 日期) nth_gmv_3//在分类日期范围内排行第3的gmv值

在这里插入图片描述

根据排序取上下几行的数值

 lag(字段,n,默认值)：返回分区内，本行前n行的字段数值，如果为空则填充默认值
 ,lag(gmv,2) over(partition by 门店名称,平台 order by 日期) lag_2
 //gmv偏移后两行字段叫lag_2

在这里插入图片描述

,lag(gmv,2,666) over(partition by 门店名称,平台 order by 日期) lag_2b

在这里插入图片描述

    lead(字段,n,默认值)：返回分区内，本行后n行的字段数值，如果为空则填充默认值
   ,lead(gmv,2) over(partition by 门店名称,平台 order by 日期) lead_2

在这里插入图片描述

   ,lead(gmv,2,666) over(partition by 门店名称,平台 order by 日期) lead_2b

根据排序统计分布位置

percent_rank()：根据分区内的排序，从0开始统计当前行所在排序中的处于百分之多少的位置（不管指定值与分组序列中某值是否重复，均将此值视为序列一部分）
percent_rank()也是0<= percent_rank() <=1，0和1都可以取到
percetn_rank()的计算公式应该是 = ((统计的列的种类)/(统计的列的总数))*100%，但是第一个总是从0开始的，不管是重复几次，要是567有4次，那就是有四个都是0，最后一个是1。
,percent_rank() over(partition by 门店名称,平台 order by 日期) perpos

 cume_dist()：根据分区内的排序，从0之后开始统计当前行所在排序中的百分比分布位置（如果指定值与分组序列中某值重复，则将二值视为一个值处理）
     cume_dist()函数统计的范围是0< cume_dist() <=1，但是取不到0
     cume_disk()的计算公式应该是 = ((统计的列的种类)/(统计的列的总数))*每个重复种类中的个数；如value值为567的两个值，公式就是(4/5)*2=0.4。而最大就是1了
     ,cume_dist() over(partition by 门店名称,平台 order by 日期) pos

在排序内分组

 ntile(n)：将排序内的行分为n组，根据分区内的排序，返回每一行是第几组
,ntile(4) over(partition by 门店名称,平台 order by 日期) ntile_r

网站不知名写手柠乐同学

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
2
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录