一、数据库认知

（一）数据库是什么

  1. 数据库：
    1）按照数据结构来组织、存储和管理数据的仓库
    2）保存有组织数据的容器
    3）用来存放数据的一个特殊文件

（二）数据库的类型

  1. 商用数据库：Oracle，SQL Server，DB2等
  2. 开源数据库：MySQL，PostgreSQL等
  3. 桌面数据库：以微软Access为代表，适合桌面应用程序使用

（三）数据库的构成

  1. 列：表中的一个字段
  2. 行：表中的一个记录
  3. 表：表是结构化的信息；行和列组成表
  4. 数据库：多张表组成一个数据库
  5. MySQL里面可以存放多少数据：
    1）MySQL每个数据库最多可以创建20亿个表
    2）一个表允许定义1024列（即1024个字段）
    3）SQL对每个表中行的数量没有直接限制，但它受数据库存储空间的限制

（四）SQL简介

  1. SQL：一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系型数据库系统
  2. 数据分析最常用的是：数据查询语言、数据操作语言、数据定义语言，也就是增删改查
  3. 其中最常用的就是“查”，即取数

二、数据类型

（一）最常用的数据类型

  1. 数据类型概括

    数据类型 说明
    int 整数
    float 小数、浮点数
    varchar 文本
    date 日期

  2. 数值型

    数据类型 说明
    INT(size) 整数，在括号中规定最大位数
    FLOAT(size,d) 带有浮动小数点的数字，在括号中size规定最大位数 ，d表示小数点后位数
    DOUBLE(size,d) 带有浮动小数点的数字，在括号中size规定最大位数，d表示小数点后位数

  3. 日期型

    数据类型 说明
    YEAR() 2位或4位格式的年
    DATE() 日期 格式：YYYY-MM-DD
    TIME() 时间 格式：HH:MM:SS
    DATETIME() 日期和时间的组合 格式：YYYY-MM-DD HH:MM:SS
    TIMESTAMP() 时间戳

  4. 文本型

    数据类型 说明
    char(n) 保存固定长度的字符串（可包含字母、数字以及特殊字符）
    varchar() 保存可变长度的字符串（可包含字母、数字以及特殊字符）

三、SQL基础查询语言

  1. SQL常用命令及书写顺序
    SELECT *（必须）
    FROM 表
    WHERE 条件
    GROUP BY 字段
    HAVING 条件
    ORDER BY 字段
    LIMIT 限制数据条数
    注：SQL对大小写不敏感；命令一般大写，表名、字段名一般小写

  2. 检索数据（SELECT语句）
    语法结构：

    SELECT *
FROM 表名

    1）检索单列：select 列名 from 表名
    2）检索多列：用“,”隔开，select 列名1，列名2 from 表名
    3） 检索所有列：select *
    4）限制检索结果：LIMIT
    5）去重检索：DISTINCT

  3. 限定条件（WHERE语句）
    1）语法结构：

    SELECT *
FROM 表名
WHERE 条件

    2）WHERE条件中常用判断命令
    数值判断：大于(>)、小于(<)、等于(=)、不等于(<>)、大于等于(>=)、小于等于(<=)、范围(between and)
    逻辑判断：AND、OR、NOT、IN()
    模糊判断：LIKE、%、_（下划线）等，如LIKE “%财%”

    3）练习1：
    数值判断：检索出“投资表”中的“投资金额大于100000的投资信息”

    SELECT * FROM investmenttab2015
WHERE investment > 100000;

    4）练习2：
    逻辑判断：检索出“投资表”中的“投资金额大于100000且投资期限为360天的投资信息”

    SELECT * FROM investmenttab2015
WHERE investment > 100000
AND investdays = 360;

    5）案例操作：
    从“投资表”中筛选出投资期限为270天或360天，在2018年1月1日至2019年12月31日期间投资，并且 投资金额在100000以上（含100000）的投资信息

    SELECT * FROM investmenttab2015
WHERE investdays IN (270,360)
AND investment >= 100000
AND investmentdate BETWEEN '2018-01-01' AND '2019-12-31';

  4. 计算字段（加减乘除）
    1）语法结构：

    SELECT
		字段1 + 字段2 AS '合计',
		字段1 - 字段2 AS '相差',
		字段1 * 字段2 AS '乘积',
		字段1 / 字段2 AS '除以'
FROM 表名
WHERE 条件（非必须）

    2）案例操作：
    在“投资表”中加新字段：投资收益列 income

    SELECT *, investment*(investrate/360)*investdays as income 
FROM investmenttab2015;

  5. 拼接字段（concat）

    SELECT 
	concat(字段1,字段2) AS '新字段名'
FROM
	表名

    在“用户表”中加入新拼接字段，把省份province和城市city拼接起来作为新拼接字段

    SELECT
	*,
	concat(province,city)
FROM 
	users;
SELECT
	*,
	concat(province,"-",city)
FROM 
	users;

  6. 数据分组（GROUP BY）
    1）语法结构：

    SELECT 字段,计算字段
FROM 表名
WHERE 查询条件
GROUP BY 字段

    2）案例操作：
    计算“各类投资期限投资产品”的投资金额

    SELECT investdays,sum(investment) FROM investmenttab2015
GROUP BY investdays;

  7. 数据过滤（HAVING）
    1）语法结构：

    SELECT 字段,计算字段
FROM 表名
WHERE 查询条件
GROUP BY 字段
HAVING 过滤条件

    2）案例操作：
    计算“各类投资期限”投资金额，显示投资金额大于1500000000（15亿）的投资信息

    SELECT investdays,SUM(investment) FROM investmenttab2015
GROUP BY investdays
HAVING SUM(investment) > 1500000000;

  8. 结果排序（ORDER BY）
    1）语法结构：

    SELECT 字段,计算字段
FROM 表名
WHERE 查询条件（非必须）
GROUP BY 字段
HAVING 过滤条件（非必须）
ORDER BY 字段 DESC

    2）排序：
    升序：默认 ASC
    降序：DESC

    3）案例操作：
    计算“各类投资期限”投资金额，显示投资金额大于1500000000（15亿）的投资信息，并按投资金额从高到低降序排列

    SELECT investdays,SUM(investment) FROM investmenttab2015
GROUP BY investdays
HAVING SUM(investment) > 1500000000
ORDER BY SUM(investment) DESC;

四、SQL函数

（一）数值型函数

  1. 函数及描述如下表所示：

    函数 描述
    SUM(列名) 返回某列的总和
    AVG(列名) 返回某列的平均值
    MIN(列名) 返回某列的最小值
    MAX(列名) 返回某列的最大值
    COUNT(列名) 返回某列的行数（不包括NULL值）
    COUNT(*) 返回被选行数
    COUNT(distinct 列名) 返回相异结果的数目
    ABS(列名) 返回绝对值
    QRT(列名) 返回平方根

  2. 案例操作
    计算“各类投资期限理财产品”的投资金额及投资数量

    SELECT investdays,SUM(investment),COUNT(investmentlistid)
FROM investmenttab2015
GROUP BY investdays;

（二）时间日期型函数

  1. 时间格式：
    1）DATETIME - 格式：YYYY-MM-DD HH:MM:SS
    2）DATE - 格式：YYYY-MM-DD
    3）YEAR - 格式：YYYY

  2. 函数及描述如下表所示：

    函数 说明
    NOW() 返回当前的日期和时间
    CURDATE() 返回当前的日期
    CURTIME() 返回当前的时间
    DATE() 提取日期或日期/时间表达式的日期部分
    EXTRACT() 返回日期/时间的单独部分
    DATE_ADD() 给日期添加指定的时间间隔
    DATE_SUB() 从日期减去指定的时间间隔
    DATEDIFF() 返回两个日期之间的天数
    DATE_FORMAT() 用不同的格式显示日期/时间

  3. 常见日期命令

    SELECT
NOW(), # 返回当前日期和时间
DATE(NOW()), # 日期格式
MONTH(NOW()), # 月份
YEAR(NOW()), # 年份
DATE_FORMATE(NOW(),'%Y-%m-%d'), # 年月日格式
DATE_FORMATE(NOW(),'%Y-%m'), # 年月格式，大写为英文，小写为汉字
DATE_ADD(NOW(),INTERVAL 1 DAY), # 日期相加 加一天/加一周
DATE_SUB(NOW(),INTERVAL 1 DAY) # 日期相减 减一天/减一周

SELECT DATEDIFF('2019-08-31','2019-01-01'); # 日期间隔多少天
SELECT NOW(); # 查看现在的日期和时间
SELECT CURDATE(); # 查看现在的日期

  4. 案例操作：
    1）查看“投资结束日期”

    SELECT *,DATE_ADD(investmentdate,INTERVAL investdays DAY) AS end_date
FROM investmenttab2015;

    2）查看“投资月份”

    SELECT *,DATE_FORMAT(investmentdate,"%m") AS investment_month 
FROM investmenttab2015;

（三）文本型函数

  1. 函数及描述如下表所示：
    函数 描述
    LEFT(列名) 返回左边的字符
    RIGHT(列名) 返回右边的字符
    LENGTH(列名) 返回某字段的长度

（四）控制语句

  1. IF函数
    语法结构： 
    IF(condition, true, false)
  2. 案例操作：
    查询投资表，增添新列“投资额度类型”，投资金额大于等于100000的为高投入，投资金额小于100000的为低投入 
    SELECT *,IF(investment>=100000,'高投入','低投入') AS "投资额度类型"
FROM investmenttab2015;
  3. 条件函数(CASE WHEN)
    1）语法结构： 
    CASE
	WHEN 条件1 THEN '结果1'
	WHEN 条件2 THEN '结果2'
	WHEN 条件3 THEN '结果3'
	......
	ELSE '默认值'
END AS '新字段名'
    2）案例操作：
    查询投资表，增添新列“投资额度类型”，投资金额大于等于100000的为高投入，投资金额大于等于50000小于100000的为中投入，投资金额小于50000的为低投入 
    SELECT *,
			CASE WHEN investment >= 100000 THEN "高投入"
					 WHEN investment >= 50000 AND investment < 100000 THEN "中投入"
	    ELSE "低投入" 
      END AS "投资额度类型"
FROM investmenttab2015;
  4. 数据透视表
    CASE WHEN与GROUP B搭配使用，完成数据透视表功能 
    SELECT 
			investdays,
			COUNT(CASE WHEN investment >= 100000 THEN "高投入" END) AS "高投入",
			COUNT(CASE WHEN investment >= 50000 AND investment < 100000 THEN "中投入" END) AS "中投入",
	    COUNT(CASE WHEN investment < 50000 THEN "低投入" END) AS "低投入"
FROM investmenttab2015
GROUP BY investdays;
# IF实现思路
SELECT
	investdays,
  COUNT(IF(investment >= 100000,"高投入",null)) AS "高投入",
  COUNT(IF(investment >= 50000 AND investment < 100000,"中投入",null)) AS "中投入",
  COUNT(IF(investment < 100000,"低投入",null)) AS "低投入"
FROM investmenttab2015
GROUP BY investdays;

五、SQL进阶

（一）SQL语句执行顺序

  1. 执行顺序
    第一步：确定数据源
    FROM
    JOIN
    ON
    第二步：过滤数据
    WHERE
    GROUP BY
    HAVING
    第三步：查询数据
    SELECT
    第四步：显示数据
    DISTINCT
    ORDER BY
    LIMIT

（二）SQL多表联结查询

  1. UNION纵向连接
    1）为什么要连接（与数据的存储形式有关）：一张表里有一部分信息，另外一张表有其他信息，我们想要获取所有信息，所有要把两张表做关联
    2）用途：组合
    3）语法结构：
    UNION用于把来自许多SELECT语句的结果组合到一个结果集合中，也叫联合查询

    SELECT * FROM A
UNION [ALL | DISTINCT]
SELECT * FROM B
UNION [ALL | DISTINCT]
SELECT * FROM C

    a. UNION ALL 连接所有
    b. UNION(DISTINCT) 去重连接
    4）案例操作：
    将投资表（investmenttable）、投资2015年表（investmenttab2015）连接成一张大表

    # 去重连接（当数据与类型一致，认定为重复）
SELECT * FROM investmenttab
UNION 
SELECT * FROM investmenttab2015;

    注意点：
    a. 字段数量必须一致
    b. 字段的顺序必须一致

  2. 横向连接
    1）分类
    a. 内连接 JOIN：和INNER JOIN一样
    表1 JOIN 表2，返回表1和表2共同的行
    b. 左连接 LEFT JOIN：表1 LEFT JOIN 表2，以表1为基础，匹配表2的相关信息；用的最多；即使右表中没有匹配，也从左表返回所有的行
    c. 右连接 RIGHT JOIN：表1 RIGHT JOIN 表2，以表2为基础，匹配表1的相关信息；即使左表中没有匹配，也从右表返回所有的行
    d. 全连接：MySQL中无法直接实现全连接

    2）INNER JOIN 内连接
    案例操作：
    将投资表（investmenttab）、用户表（users）连接成一张大表，显示只有相同userid的数据

    SELECT * 
FROM investmenttab
INNER JOIN users 
ON investmenttab.userid = users.userid;
# using 
SELECT * 
FROM investmenttab
INNER JOIN users 
USING(userid);
# inner省略  
SELECT * 
FROM investmenttab
JOIN users 
ON investmenttab.userid = users.userid;
# on 可以选择为不同的字段，必须保证字段的内容是同一个数据

    3）LEFT JOIN 左连接
    以左边表为准：
    将投资表（investmenttab）、用户表（users）连接成一张大表，以左边表为准

    SELECT * FROM investmenttab
LEFT JOIN users 
ON investmenttab.userid = users.userid;
# 连接表中的部分字段 
SELECT a.userid,b.age FROM investmenttab AS a 
LEFT JOIN users AS b 
ON a.userid = b.userid;

    案例操作：
    案例操作：查询各地区的投资人数及投资金额

    SELECT COUNT(DISTINCT a.userid),b.area,SUM(a.investment)
FROM investmenttab AS a 
LEFT JOIN users AS b 
ON a.userid = b.userid
GROUP BY b.area;

    4）RIGHT JOIN 右连接
    以右边表为准：
    将投资表（investmenttab）、用户表（users）连接成一张大表，以右边表为准

    SELECT * FROM investmenttab
RIGHT JOIN users 
ON investmenttab.userid = users.userid
WHERE investmenttab.investmentdate='2016-12-31';

（三）子查询（嵌套查询）

  1. 子查询
    1）当一个查询是另一个查询的一部分时，我们把内层的查询称为子查询（内查询），外层的查询称为父查询（主查询）
    2）通过子查询可以实现多表查询，该查询语句可能包括in、any、all和exists等关键字。除此之外还可能包含比较运算符

  2. 子查询分类
    1）WHERE 子查询
    2）FROM 子查询

  3. WHERE 子查询
    语法结构：

    SELECT * FROM t WHERE col > (SELECT XXX FROM t)

    1）使用运算符
    案例操作1：
    查询投资表（investmenttab）投资金额比单号’ph-000000001’还要低的投资单信息

    SELECT * 
FROM investmenttab
WHERE investment < (
	SELECT investment
	FROM investmenttab
	WHERE investmentlistid = 'ph-000000001'
);

    案例操作2：
    查询投资表（investmenttab）投资金额与投资周期与单号’ph-000000001’一致的投资单信息

    SELECT * 
FROM investmenttab
WHERE (investment,investdays)  = (
	SELECT investment,investdays 
	FROM investmenttab
	WHERE investmentlistid = 'ph-000000001'
);

    注意点：
    a. WHERE后面的字段名一定要和子查询中的字段名顺序一致
    b. WHERE后面的字段数目要与子查询中的字段数目一致

    2）使用IN关键字
    当主查询的条件是子查询中的结果时，就可以通过关键字in来进行判断，相反，如果想实现主查询的条件不是子查询中的结果时，就可以通过关键字not in来进行判断
    案例操作：
    查询华北地区用户的投资信息

    SELECT * 
FROM investmenttab
WHERE userid IN (
	SELECT userid 
	FROM users 
	WHERE area='华北'
);

  4. FROM子查询
    语法结构：

    SELECT t1.*
FROM (SELECT 字段 FROM 表名) AS t1
WHERE 条件

    案例操作：
    查询各城市的投资总额，根据投资总额进行排名，找到前10名的城市

    SELECT * 
FROM (
SELECT b.city,SUM(investment),ROW_NUMBER() over(ORDER BY SUM(investment) DESC) AS rank_num
FROM investmenttab AS a 
INNER JOIN users AS b 
ON a.userid = b.userid
GROUP BY b.city
) AS t 
WHERE t.rank_num <= 10;

  5. WITH临时表
    1）在MySQL8.0之后，增加了WITH表功能，可以相应的子查询
    2）WITH临时表，也叫子查询部分，是用来定义一个SQL片段，该SQL片段会被整个SQL语句所用到
    3）WITH子句的返回结果存到用户的临时表空间中，只做一次查询，反复使用，提高效率
    4）语法结构

    WITH t1 AS (SELECT 字段 FROM 表名)
SELECT * FROM t1

    案例操作：
    查询各城市的投资总额，根据投资总额进行排名，找到前10名的城市

    WITH t AS (
	SELECT b.city,SUM(investment),ROW_NUMBER() over(ORDER BY SUM(investment) DESC) AS rank_num
	FROM investmenttab AS a 
	INNER JOIN users AS b 
	ON a.userid = b.userid
	GROUP BY b.city
)
SELECT * 
FROM t 
WHERE t.rank_num <= 10;

  6. 排序列问题
    1）MySQL中有order by语句实现排序，但是实际工作中，我们需要在排序的结果上，增加新的一列为排序列，也就是将排序的结果名次需要一同显示出来
    2）在MySQL5.7中，没有提供相应的开窗函数，需要通过采用编程的逻辑进行比对，也就是引入变量的思想处理
    3）而在MySQL8.0中，提供了相应的开窗函数，直接使用即可
    4）MySQL8.0排序
    语法结构：

    用法1：无分组排序
ROW_NUMBER() OVER(ORDER BY 字段 DESC)

例如：ROW_NUMBER() OVER(ORDER BY 学生成绩 DESC)
表示不分班级，所有学生的成绩从高到低排序

用法2：分组排序
ROW_NUMBER() OVER(PARTITION BY 字段1 ORDER BY 字段2 DESC)
表示根据字段1分组，在分组内部根据字段2排序，这个函数计算的值就表示每组内部排序后的顺序编号

例如：ROW_NUMBER() OVER(PARTITION BY 班级 ORDER BY 学生成绩 DESC)
表示根据“班级”分组，在每个“班级”内部根据“学生成绩”排序，这个函数计算的值就表示每个“班级”内部排序后的顺序编号

    5）解释
    ROW_NUMBER() 起到了编号的功能
    PARTITION BY 将相同数据进行分区
    ORDER BY 使得数据按一定顺序排序
    6）三种排序
    将根据用户投资金额进行排序，从大到小，并且将排序的结果同时显示在查询结果中

    # ROW_NUMBER() 简单排序 
# 将根据用户投资金额进行排序，从大到小，并且将排序的结果同时显示在查询结果中
SELECT investment,ROW_NUMBER() over(ORDER BY investment DESC) AS 'row_number' 
FROM investmenttab;

# DENSE_RANK() 相同排名不占位 
SELECT investment,DENSE_RANK() over(ORDER BY investment DESC) AS 'dense_rank' 
FROM investmenttab;

# RANK() 相同排名占位 
SELECT investment,RANK() over(ORDER BY investment DESC) AS 'rank' 
FROM investmenttab;

    7）PARTITION BY 分组排序
    案例操作：查找每个区域投资金额最高的十个城市

    SELECT * 
FROM (
	SELECT b.area,b.city,ROW_NUMBER() over(PARTITION BY b.area ORDER BY SUM(a.investment) DESC) AS 'rank'
	FROM investmenttab AS a 
	INNER JOIN users AS b 
	ON a.userid = b.userid
	GROUP BY b.area,b.city
) AS t 
WHERE t.rank <= 10;

六、Hive进阶

（一）大数据分析简介

  1. 大数据
    大数据（big data）：是指无法在一定时间范围内用常规软件工具进行捕捉、管理和处理的数据集合，是需要新处理模式才能具有更强的决策力、洞察发现力和流程优化的海量、高增长率和多样化的信息资产

  2. 大数据的5V特征
    速度（Velocity）、种类（Variety）、数量（Volume）、价值（Value）、真实性（Veracity）

（二）走进Hadoop

  1. 快速认知Hadoop
    1）Hadoop是Apache基金组织下的一套开源软件平台
    2）Hadoop提供的功能：利用服务器集群对海量数据进行分布式处理
    3）Hadoop的核心组件：HDFS、MapReduce、YARN、Hadoop基础功能库

  2. HDFS：块级别的分布式文件存储系统
    1）Hadoop中自带的分布式文件存储系统，并且是Hadoop工具的核心基础组件之一
    2）分布式存储系统可以理解为多台单机存储系统的各司其职、协同合作，统一的对外提供存储的服务

  3. MapReduce：分布式计算框架
    MapReduce是采用一种分而治之的思想设计出来的分布式计算框架，如复杂的计算任务，单台服务器无法胜任时，可将此大任务切分成一个个小的任务，小任务分别在不同的服务器上并行的执行，最终再汇总每个小任务的结果，MapReduce由两个阶段组成：Map阶段（切分成一个个小的任务）、Reduce阶段（汇总小任务的结果）

  4. YARN：作业调度和资源管理器
    1）Hadoop资源管理器，通用资源管理系统和调度平台
    2）为上层应用提供统一的资源管理和调度
    3）它的引入为集群在利用率、资源统一管理和数据共享等方面带来了巨大好处

（三）走进Hive

  1. Hive是什么？
    1）基于Hadoop的开源数据仓库工具，用于处理海量结构化数据
    2）Hive把HDFS中结构化的数据文件映射成数据表
    3）Hive通过把HiveSQL进行解析和转化，最终生成一系列在Hadoop上运行的MapReduce任务，通过执行这些任务完成数据分析与处理
    4）HiveSQL和MySQL一样，都遵循着SQL的标准，因此它们很多语句都是一样的

  2. Hive与传统数据库的比较

    – Hive RDBMS
    查询语言 HQL SQL
    数据存储位置 HDFS 块设备或本地
    数据更新 不建议 经常修改
    索引 0.8版本后加入位图索引 有复杂的索引
    处理数据规模 大 小
    执行 MapReduce 数据库自身引擎
    执行延迟 较高 很低
    可拓展性 较好 非常有限

（四）基础语法

  1. 语法
    语法结构：

    SELECT A # A为列名
FROM B # B为表名
WHERE C # C为筛选条件
GROUP BY ...
HAVING ...
ORDER BY ...

    参数含义：
    ASC：升序（默认为升序排列）
    DESC：降序
    对多个字段进行排序：ORDER BY A ASC， B DESC 每个字段都要指定升序还是降序

  2. 需求练习
    1）选出城市在北京，性别为女的10个用户

    select user_name
from user_info 
where city = 'beijing'
and sex = 'female'
limit 10;

    2）选出在2018年12月31日，购买的商品品类是food的用户名、购买数量、支付金额

    select	user_name,piece,pay_amount
from user_trade
where goods_category = 'food'
and dt = '2018-12-31';

    3）计算出在2019年4月支付金额最多的TOP5用户

    select user_name,sum(pay_amount)
from user_trade
where dt between '2019-04-01' and '2019-04-30'
group by user_name
order by sum(pay_amount) desc
limit 5;

    注意：在Hive中分区就相当于是把表中的数据按照不同的条件分成不同的文件夹，如果没有对分区表进行分区字段的限制，会报错

（六）常用函数

  1. 如何把时间戳转化为日期时间

    select pay_time,
from_unixtime(pay_time,'yyyy-MM-dd hh:mm:ss')
from user_trade
where dt = '2019-04-09';

    注意：from_unixtime(bigint unixtime,string format)，将时间戳转化为指定格式的日期
    常用格式：
    1）yyyy-MM-dd hh:mm:ss
    2）yyyy-MM-dd hh
    3）yyyy-MM-dd hh:mm
    4）yyyy-MM-dd
    拓展：把日期转化成时间戳函数：unix_timestamp(string date)

  2. 如何计算日期间隔？
    需求练习：计算出用户的首次激活时间与2019年5月1日的日期间隔

    select user_name,
datediff('2019-05-01',to_date(firstactivetime))
from user_info
limit 10;

select datediff('2019-12-30','2019-12-20');
# 注意：datediff(string enddate,string startdate)：结束日期减去开始日期的天数
拓展：日期增加、减少函数 
date_add(string date,int days)
date_sub(string date,int days)

  3. 需求练习
    1）统计以下年龄段 20岁以下、20-30岁、30-40岁、40岁以上的用户数

    select case when age < 20 then '20岁以下'
            when age >= 20 and age < 30 then '20-30岁'
            when age >= 30 and age < 40 then '30-40岁'
            else '40岁以上' end,
            count(distinct user_id)
from user_info
group by case when age < 20 then '20岁以下'
              when age >= 20 and age < 30 then '20-30岁'
              when age >= 30 and age < 40 then '30-40岁'
              else '40岁以上' end ;

    2）统计不同性别用户的等级高低分布情况（假设level大于5为高级）

    select sex,if(level>5,'高','低'),count(distinct user_id)
from user_info
group by sex,if(level>5,'高','低');

    3）计算出每个月的拉新情况

    select substr(firstactivetime,1,7),count(distinct user_id)
from user_info
group by substr(firstactivetime,1,7);

    4）重点：统计不同手机品牌的用户数
    数据类型一：string

    select get_json_object(extra1,'$.phonebrand'),count(distinct user_id)
from user_info
group by get_json_object(extra1,'$.phonebrand');

    数据类型二：map<string,string>

    select extra2['phonebrand'],count(distinct user_id)
from user_info
group by extra2[phonebrand];

  4. 重难点解析
    1）统计在2018年购买的商品品类在两个以上的用户数

    /*
1. 先求出每个人购买的商品品类的数量
2. 筛选出购买商品品类数量大于2的用户
3. 统计符合条件的用户有多少个
*/
select count(distinct user_name)
from(
select user_name
from user_trade
where year(dt) = '2018'
group by user_name
having count(distinct goods_category)>2
) as t;

    2）用户激活时间在2018年，年龄段在20-30岁和30-40岁之间的婚姻状况

    /*
1. 先选好激活时间在2018年的用户，并把他们的所有年龄段计算好，提取出婚姻状态
2. 取出20-30和30-40这两个年龄段的用户，并且把他们的婚姻状态进行转换为可以理解的说明 
3. 进行聚合计算，针对年龄和婚姻状态分别进行计算 
*/
select t.age_range,if(t.ms=1,'已婚','未婚'),count(user_id)
from 
(select user_id,
case when age < 20 then '20岁以下'
            when age >= 20 and age < 30 then '20-30岁'
            when age >= 30 and age < 40 then '30-40岁'
            else '40岁以上' end as age_range,
            get_json_object(extra1,'$.marriage_status') as ms
from user_info
where to_date(firstactivetime) between '2018-01-01' and '2018-12-31') as t 
where t.age_range in ('20-30岁','30-40岁')
group by t.age_range,if(t.ms=1,'已婚','未婚');

（七）HiveSQL核心技能——窗口函数

  1. 累计求和窗口函数：sum(…) over(…)
    1）语法结构

    sum(A) over(partition by B order by C rows between D1 and D2)
/*
A：需要被加工的字段名称
B：分组的字段名称
C：排序的字段范围
D：计算的行数范围
rows between unbounded preceding and current row（包括本行和
之前所有的行）
*/

  2. 累计求平均窗口函数：avg(…) over(…)
    1）语法结构

    avg(A) over(partition by B order by C rows between D1 and D2)
	/*
	A：需要被加工的字段名称
	B：分组的字段名称
	C：排序的字段范围
	D：计算的行数范围
	*/

  3. 语法总结
    1）rows between unbounded preceding and current row——包括本行和
    之前所有的行
    2）rows between current row and unbounded following ——包括本行和
    之后所有的行
    3）rows between 3 preceding and current row——包括本行以内和
    前3行（4行）
    4）rows between 3 preceding and 1 following——从前三行到下一行（包含本行，共5行）

  4. 分区排序窗口函数
    1）row_number() over(partition by A order by B )
    为查出的每一行记录生成一个序号，依次排序且不重复
    2）rank() over(partition by A order by B )
    在分组内，是跳跃排序，比如同时出现两个第一名，那么接下来就从第三名开始
    3）dense_rank() over(partition by A order by B )
    在分组内，是连续排名，比如同时出现两个第一名，那么接下来就从第二名开始
    注：A为分组的字段名称，B为排序的字段名称，这三个函数的括号内是不加任何字段名称的！

  5. 切片排序窗口函数
    语法结构：
    ntile(n) over(partition by A order by B )
    n：切分的片数
    A：分组的字段名称
    B：排序的字段名称
    1）NTILE(n)：用于将分组数据按照顺序切分成n片，返回当前切片值
    2）NTILE不支持ROWS BETWEEN，比如NTILE(2) OVER(PARTITION BY A ORDER BY B ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
    3）如果切片不均匀，则前面的组分得的数据多

  6. 偏移分析窗口函数
    Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行数据（Lag）和后N行数据（Lead）作为独立的列
    lag(exp_str,offset,defval) over(partition by A order by B)
    lead(exp_str,offset,defval) over(partition by A order by B)
    1）exp_str是字段名称
    2）offset表示偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2），offset默认值为1
    3）defval默认值，当这两个函数取上N/下N个值是，在表中从当前文职向前数N行已经超出了表的范围时，lag()函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回null。lead()函数同理。但是null值不能进行数学运算，故需要指定默认值。

（八）Hive核心技能——指标体系

  1. 大数据分析业务
    数据分析中最常见的业务就是用户增长和品牌增长，以下即为互联网公司通常涉及的GROW2.0增长指标20种常见的落地场景
    1）渗透力（G）：4种场景
    G1：延展目标人群，驱动品牌渗透
    G2：跨品类/品牌交叉渗透
    G3：多渠道联动渗透
    G4：趋势新品加速渗透

    2）复购力（R）：6个场景
    R1：品类消费生命周期延长
    R2：品类使用场景拓展
    R3：高频再购的触发
    R4：会员加深，加速回购
    R5：消费类目迁移，防止人群流失（适用于母婴品类）
    R6：消费体验&服务升级

    3）价格力（O）：5个场景
    O1：老客再购升级
    O2：产品包装升级
    O3：基于情感诉求实现溢价
    O4：产品功能/属性升级
    O5：高净值目标人群新品供给

    4）新品力（W）：5个场景
    W1：行业趋势分析识别新品
    W2：人群需求定位帮助开拓新品
    W3：产品价值链衍生发掘新品
    W4：全渠道洞察助力新品研发
    W5：平台能力（TMIC）孵化新品

  2. 大数据分析基本流程
    分析设计—> 数据收集—>数据处理 —>数据分析 —>数据展现 —>报告撰写
    1）分析设计：了解业务需求，明确数据分析目的
    2）数据收集：按照确定的数据分析框架，收集相关数据的过程
    3）数据处理：对采集到的数据进行加工整理，形成适合数据分析的样式
    4）数据分析：用适当的分析方法及工具，对收集来的数据进行分析
    5）数据展现：一般情况下，数据是通过表格和图形的方式来呈现的，即用图表说话
    6）报告撰写：对整个数据分析过程的一个总结与呈现

  3. 常用的大数据分析模型
    1）事件分析
    eg：最近三个月哪个渠道的用户注册量最高？变化趋势如何？
    2）漏斗分析
    eg：从浏览商品到支付的转化与流失情况如何？
    3）留存分析
    eg：分析整体用户留存现状，各维度用户留存情况
    4）分布分析
    eg： 揭示单个用户对产品的依赖程度，复购率如何？
    5）用户路径分析
    eg：用户如何浏览你的产品？最佳路径是什么？
    6）点击分析
    用户都点了哪些页面元素？哪个元素被高频点击？
    7）用户分群
    eg：90后的女性用户都有谁？定向推送优惠券
    8）用户属性分析
    eg：用户按省份分布

  4. 核心技术与指标体系
    1）技术架构图
    在这里插入图片描述

2）什么是数据分析指标？
在这里插入图片描述
3）搭建数据指标体系的目的
a. 表现现状（展现公司运营现状）
b. 反应问题（反应业务推进中存在的问题）
c. 预测趋势（预测未来发展趋势）
d. 评估目标（预期目标GAP）
e. 决策调整（调整可执行方案）

  1. 电商平台常见的指标体系
    1）核心应用1：用户留存分析
    在这里插入图片描述

    2）核心应用2：用户活跃分析
    在这里插入图片描述
    在这里插入图片描述

  2. 数据分析常用指标
    1）市场营销域
    a. 市场营销活动指标
    b. 广告投放指标

    2）市场竞争域
    a. 市场份额相关
    b. 网站排名

    3）风控域
    a. 买家评论指标
    b. 网站排名

    4）客户价值域
    a. 新客户指标
    b. 客户指标
    c. 老客户指标

    5）商品域
    a. 产品总数指标
    b. 品牌库存
    c. 上架

    6）销售转化域
    a. 购物车指标
    b. 下单类指标
    c. 支付类指标
    d. 交易类指标

    7）APP流量域
    a. 流量规模指标
    b. 流量成本类指标
    c. 会员类指标

    8）总体运营域
    a. 流量类指标
    b. 总体销售业绩指标
    c. 总体转化指标

# 数据库 # mysql
Logo
腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Elasticsearch复杂数据类型终极指南：从入门到精通

Elasticsearch作为功能强大的搜索引擎，支持多种复杂数据类型，让开发者能够灵活处理各种结构化和非结构化数据。本文将带你全面了解Elasticsearch中的复杂数据类型，从基础概念到实际应用，助你轻松掌握数据建模的核心技巧。## 内部对象：构建层级化数据结构在Elasticsearch中，对象类型（Object）是最基础的复杂数据类型之一，用于表示具有嵌套关系的数据。例如，我们可

avatar 腾讯云开发者社区

终极指南：Flink SQL连接器版本管理从混乱到有序的升级之路

Apache Flink作为流处理领域的佼佼者，其SQL连接器的版本管理一直是开发者面临的核心挑战。本文将系统讲解Flink SQL连接器版本管理的最佳实践，帮助你轻松应对版本兼容性问题，实现从混乱到有序的升级之旅。## 连接器版本管理的常见痛点 😫在Flink应用开发中，连接器版本管理常常让开发者头疼不已。不同版本的连接器可能导致各种兼容性问题，例如API变更、功能差异甚至运行时错误。

avatar 腾讯云开发者社区

如何快速搭建Neon无服务器PostgreSQL：面向初学者的完整指南

Neon是一款革命性的无服务器PostgreSQL解决方案，它通过分离存储和计算层，实现了自动扩缩容、类代码式数据库分支以及零级扩展能力。本指南将帮助你从零开始搭建Neon开发环境，体验这款创新数据库的强大功能。## 准备工作：环境要求与依赖项在开始搭建Neon环境前，请确保你的系统满足以下要求：- Linux操作系统（推荐Ubuntu 20.04+或Debian 11+）- Git

avatar 腾讯云开发者社区