阿里云大数据DataWorks开发套件的相关操作

_胡杨_

已于 2022-06-03 18:43:46 修改

阅读量2.2k

点赞数 1

分类专栏： odpscmd 文章标签：阿里云云计算大数据

于 2021-12-27 14:54:24 首次发布

本文链接：https://blog.csdn.net/m0_45097792/article/details/122171373

版权

odpscmd 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

前情提要:

实验所需文件

第一章

关于DataWorks开发套件的简介（略）

第二章

前期准备:

--新建表yxxx
CREATE TABLE IF NOT EXISTS yxxx
(
    yxbh             STRING COMMENT '院系编号',
    yxmc             STRING COMMENT '院系名称',
    professionalcode BIGINT COMMENT '专业代码',
    professional     BIGINT COMMENT '专业名称'
) 
COMMENT '储存院系信息'
LIFECYCLE 120;

--新建表bjxx
CREATE TABLE IF NOT EXISTS bjxx
(
    bjbh STRING COMMENT '班级编号',
    bjmc STRING COMMENT '班级名称',
    rxrq STRING COMMENT '入学日期',
    yxbh STRING COMMENT '所属院系中文名称'
) 
COMMENT '录入班级信息'
LIFECYCLE 120;

--新建表xsxx
CREATE TABLE IF NOT EXISTS xsxx
(
    xh   STRING COMMENT '学号',
    xm   STRING COMMENT '姓名',
    xb   STRING COMMENT '性别',
    bjbh STRING COMMENT '所属班级编号',
    rxrq STRING COMMENT '入学日期',
    rxcj BIGINT COMMENT '入学成绩',
    syd  STRING COMMENT '生源地'
) 
COMMENT '录入学生信息'
LIFECYCLE 120;

正文:

-- 1.查看数据表
SHOW TABLES ;
-- 创建班级信息临时表,临时表生命周期1天
CREATE TABLE tmp_bjxs COMMENT '班级信息表（临时表）' LIFECYCLE 1
AS SELECT * FROM bjxx;
-- 查看班级临时信息表信息
DESC tmp_bjxs;

--2. 修改（重命名）表名
ALTER TABLE tmp_bjxs RENAME TO tmp_bjxx;
-- 运用SHOW TABLES确认修改是否成功
SHOW TABLES;

-- 3.修改表的注释
ALTER TABLE tmp_bjxx SET COMMENT '班级信息表（临时表生命周期1天）';
-- 查看tmp_bjxx(班级临时信息表)定义:
DESC tmp_bjxx;

-- 4.修改表“tmp_bjxx”生命周期为2天
ALTER TABLE tmp_bjxx SET  LIFECYCLE 2;
-- 查看详细信息
DESC  tmp_bjxx;
-- 5.修改表的最后修改时间
ALTER TABLE tmp_bjxx TOUCH ;
-- 查看表的定义
DESC tmp_bjxx ;

--6.给xsxx增加两个数据列,BIGINT型的“入学成绩”（Professionalcode）和STRING型的“生源地”（Professional）
ALTER TABLE XSXX ADD COLUMNS (rxcj BIGINT COMMENT '入学成绩', syd STRING COMMENT '生源地' ) ;
DESC xsxx;

-- 7.修改列名:修改班级信息临时表（tmp_bjxx）:列bjbh重命名为bjbh_xgh
ALTER TABLE tmp_bjxx CHANGE COLUMN bjbh RENAME TO bjbh_xgh;
DESC tmp_bjxx;

-- 8.修改列的注释
ALTER TABLE tmp_bjxx CHANGE COLUMN yxbh COMMENT '院系编号';
-- 查看
DESC tmp_bjxx;

-- 9.同时修改列名和注释
ALTER TABLE tmp_bjxx CHANGE COLUMN bjbh_xgh bjbh STRING COMMENT '将第一列的列名修改为原先的值';
DESC tmp_bjxx;

-- 10.清空数据表
-- 查询tmp_bjxx表中记录数
SELECT COUNT(*) FROM tmp_bjxx ;
-- 清空数据表
TRUNCATE TABLE tmp_bjxx;
-- 查询tmp_bjxx表中记录数
SELECT COUNT(*) FROM tmp_bjxx ;
-- 11.删除表
DROP TABLE tmp_bjxx ;
-- 显示所有表
SHOW TABLES; 

-- 12.创建视图
CREATE VIEW IF NOT EXISTS view_bjxx_xsxx(
bjbh,
bjmc,
rxrq,
yxbh,
xh,
xm,
xb
)COMMENT '班级、学生信息关联'
AS SELECT 
b.bjbh,
b.bjmc,
b.rxrq,
b.yxbh,
x.xh,
x.xm,
x.xb
FROM bjxx b
LEFT OUTER JOIN xsxx x
ON b.bjbh = x.bjbh
;

-- 13.对前面的视图进行替换操作,为每个组成列增加注释信息
CREATE OR REPLACE VIEW view_bjxx_xsxx(
bjbh COMMENT '班级编号',
bjmc COMMENT '班级名称',
rxrq COMMENT '入学日期',
yxbh COMMENT '所属院系编号',
xh COMMENT '学号',
xm COMMENT '姓名',
xb COMMENT '性别'
)COMMENT '班级`学生信息关联视图'
AS SELECT 
b.bjbh,
b.bjmc,
b.rxrq,
b.yxbh,
x.xh,
x.xm,
x.xb
FROM bjxx b
LEFT OUTER JOIN xsxx x
ON b.bjbh = x.bjbh
;

-- 14.重命名视图
ALTER VIEW view_bjxx_xsxx RENAME TO view_bjxx_xsxx_xgh;

-- 15.删除视图
DROP VIEW IF EXISTS  view_bjxx_xsxx_xgh;
-- 查看视图,报错找不到,即成功
DESC view_bjxx_xsxx_xgh;
-- 查看报错即删除成功
DESC view_bjxx_xsxx_xgh;

第三章

-- 1.插入数据
-- 创建表样例
-- 先进行了删除表
DROP TABLE temp_test_xsxx_30;
CREATE TABLE IF  NOT EXISTS temp_test_xsxx_30 (
xh STRING COMMENT '学号',
xm STRING COMMENT '姓名',
xb STRING COMMENT '性别',
bjbh STRING  COMMENT '所属班级编号',
rxrq STRING COMMENT '入学日期',
rxcj BIGINT  COMMENT '入学日期',
syd STRING COMMENT '生源地'
)COMMENT '测试插入非分区表'LIFECYCLE 30;
-- 向非分区表中插入2条测试数据：INSERT INTO 模式
INSERT INTO TABLE temp_test_xsxx_30 
VALUES ('9211271028','孙德邻','女','2013101050703415','20131003',372,'沧州师范学院');
INSERT INTO TABLE temp_test_xsxx_30 
VALUES ('9002211017','田普','男','2013101050703925','20131003',370,'衡水学院');
--查看表中数据结果
SELECT * FROM temp_test_xsxx_30;
--查看表的详细信息
DESC temp_test_xsxx_30;
-- INSERT OVERWRITE模式插入数据
INSERT overwrite TABLE temp_test_xsxx_30 
VALUES ('8901203047','吴丹','女','2013101050703121','20131004',369,'邯郸学院');
-- 查询表数据
SELECT * FROM temp_test_xsxx_30;

-- 2.查询数据
-- （1）查询全表数据
-- INSERT INTO插入数据
INSERT INTO TABLE temp_test_xsxx_30 
VALUES('9108081022','单英杰','女','2013101050702923','20131005',366,'唐山师范学院');
INSERT INTO TABLE temp_test_xsxx_30 
VALUES('9008216422','刘岩','男','2013101050703903','20131007',365,'河北农业大学');
INSERT INTO TABLE temp_test_xsxx_30 
VALUES('9105250021','魏浩璇','女','2013101050703713','20131007',374,'唐山师范学院');
INSERT INTO TABLE temp_test_xsxx_30 
VALUES('9303260027','刘海宽','男','2013101050703623','20131004',364,'沧州师范学院');
INSERT INTO TABLE temp_test_xsxx_30
VALUES('9206247041','李艳玲','女','2013101050702901','20131004',363,'沧州师范学院');
INSERT INTO TABLE temp_test_xsxx_30
VALUES('9107231023','崔娟','女','2013101050703419','20131008',383,'廊坊师范学院');
INSERT INTO TABLE temp_test_xsxx_30
VALUES('9011133827','董莉娜','女','2013101050702815','20131008',353,'廊坊师范学院');
--读取表数据
READ temp_test_xsxx_30;

--（2）GROUP BY分组查询
SELECT syd,AVG(rxcj) AS avg_score FROM temp_test_xsxx_30 GROUP BY syd ;

--（3）ORDER BY排序查询
SELECT * FROM temp_test_xsxx_30 ORDER BY rxcj DESC LIMIT 5;

--（4）join查询
SELECT x.xh, x.xm, x.xb, x.bjbh, x.rxrq, b.bjmc
FROM xsxx x 
INNER JOIN bjxx b
ON x.bjbh = b.bjbh;

第四章

-- 1.创建表xsxw
CREATE TABLE IF NOT EXISTS xsxw
(
    xwsj STRING COMMENT '行为时间',
    xh   STRING COMMENT '学号',
    xwdd STRING COMMENT '行为地点',
    xwdm STRING COMMENT '行为代码',
    bzsm STRING COMMENT '备注说明'
) 
COMMENT '学生行为信息'
PARTITIONED BY
(
    xwrq STRING COMMENT '行为日期',
    yxbh STRING COMMENT '院系编号'
)LIFECYCLE 120;

-- 2.查看学生行为表定义
DESC xsxw ;
-- 插入分区
ALTER TABLE xsxw ADD IF NOT EXISTS PARTITION (xwrq='20120717',yxbh='SMGL');
ALTER TABLE xsxw ADD IF NOT EXISTS PARTITION (xwrq='20120718',yxbh='JGXY');

-- 3.查看当前表有多少分区
SHOW PARTITIONS xsxw;

-- 4.查看单个分区信息
DESC xsxw PARTITION (xwrq='20120718',yxbh='JGXY') ;

-- 5.查看分区
ALTER TABLE xsxw ADD IF NOT EXISTS PARTITION (xwrq='20120716',yxbh='JSJXY');
READ xsxw;
-- 查看导入到分区xwrq='20120718',yxbh='JGXY'的数据
SELECT * FROM xsxw WHERE xwrq='20120718';

-- 6.删除分区
-- 查看分区 （IF EXISTS存在执行，不存在跳过）
SHOW PARTITIONS xsxw;
ALTER TABLE xsxw DROP IF EXISTS PARTITION (xwrq='20120717',yxbh='SMGL');

-- 7.修改分区值
ALTER TABLE xsxw PARTITION (xwrq='20120716',yxbh='JSJXY')
RENAME TO PARTITION (xwrq='20120718',yxbh='JSJXY');
-- 查看
SHOW PARTITIONS xsxw;

-- 8.修改分区数据最后修改时间
DESC xsxw PARTITION (xwrq='20120718',yxbh='JSJXY');
-- 修改该学生行为表的属性值
ALTER TABLE xsxw TOUCH PARTITION (xwrq='20120718',yxbh='JSJXY');
-- 查看分区详细信息
DESC xsxw PARTITION (xwrq='20120718',yxbh='JSJXY');

第五章

-- 1.创建分区表
CREATE TABLE csxx_ffq (
rq STRING COMMENT '日期',
xh STRING COMMENT '序号',
sj STRING COMMENT '数据'
) COMMENT '测试数据（分区表）'
LIFECYCLE 30;
-- 这里向csxx_ffq导入一波数据，文件名为：06 非分区表插入数据

-- 创建分区表
CREATE TABLE csxx_fq (
xh STRING COMMENT '序号',
sj STRING COMMENT '数据'
) COMMENT '测试数据（分区表）'
PARTITIONED BY (rq STRING COMMENT '日期')
LIFECYCLE 30;

-- 2.插入数据：多路分区方式
FROM csxx_ffq 
INSERT OVERWRITE TABLE csxx_fq PARTITION (rq='20180101')
SELECT xh,sj WHERE rq='20180101'
INSERT OVERWRITE TABLE csxx_fq PARTITION (rq='20180102')
SELECT xh,sj WHERE rq='20180102'
INSERT OVERWRITE TABLE csxx_fq PARTITION (rq='20180103')
SELECT xh,sj WHERE rq='20180103'
;
-- 查看分区表（csxx_fq）的数据
READ csxx_fq;

-- 3.插入数据：【动态分区】方式
INSERT OVERWRITE TABLE csxx_fq PARTITION(rq)
SELECT xh,sj,rq FROM csxx_ffq;
-- 查询数据
READ  csxx_fq;

-- 4. 查询数据
--创建学生信息表
-- CREATE TABLE IF NOT EXISTS xsxx(
--  xh STRING COMMENT '学号',
--  xm STRING COMMENT '姓名',
--  xb STRING COMMENT '性别',
--  bjbh STRING COMMENT '所属班级编号',
--  rxrq STRING COMMENT '入学日期'
-- ) COMMENT '学生信息表' LIFECYCLE 120;
-- 有些情况,需要多个查询语句返回的结果集进行合并处理
SELECT * FROM (
 SELECT * FROM xsxx WHERE bjbh= '0101'
 UNION ALL
 SELECT * FROM xsxx WHERE bjbh= '0104') a ;

-- 5.子查询
--创建商品信息表
CREATE TABLE IF NOT EXISTS spxx (
cbbh STRING COMMENT '出版编号',
spmc STRING COMMENT '商品名称',
splb STRING COMMENT '商品类别',
spjg DOUBLE COMMENT '商品价格',
pjdj STRING COMMENT '评价等级'
)COMMENT '商品信息表'LIFECYCLE 120;
--创建出版社信息表
CREATE TABLE IF NOT EXISTS cbsxx (
cbsbh STRING COMMENT '出版社编号',
cbbh STRING COMMENT '出版编号',
cbsmc STRING COMMENT '出版社名称',
cbsj STRING COMMENT '出版时间',
spmc STRING COMMENT '商品名称'
)COMMENT '出版社信息表'LIFECYCLE 120;
--创建电商信息表
CREATE TABLE IF NOT EXISTS dsxx (
dsptbh STRING COMMENT '电商平台编号',
dsptmc STRING COMMENT '电商平台名称',
cbsmc STRING COMMENT '出版社名称',
spmc STRING COMMENT '商品名称'
)COMMENT '电商信息表'LIFECYCLE 120;
-- 这里导入三个文件，出版社信息；电商信息；商品信息。
-- 通过子查询，关联三张数据表,商品信息(xpxx),出版社信息(xbsxx),电子信息表(dzxxb),进行查询。
SELECT a.dsptmc, a.cbsmc, s.*
FROM spxx s
LEFT OUTER JOIN (
    SELECT d.dsptmc, c.cbsmc, c.cbbh
    FROM dsxx d
    LEFT OUTER JOIN cbsxx c
    ON d.cbsmc = c.cbsmc
) a
ON s.cbbh = a.cbbh;

-- 6.JOIN操作
-- （1）左查询
SELECT a.dsptmc, a.cbsmc,s.*
FROM spxx s
LEFT OUTER JOIN (
SELECT d.dsptmc, d.dsptbh ,c.cbsmc, c.cbbh
FROM dsxx d
-- LEFT 左
LEFT OUTER JOIN cbsxx c
ON d.cbsmc = c.cbsmc
) a
ON s.cbbh = a.cbbh;
-- 用下面RIGHT OUTER JOIN可实现一样的效果，
-- （2）右查询
SELECT a.dsptmc, a.cbsmc,s.*
FROM spxx s
 LEFT OUTER JOIN (
 SELECT d.dsptmc,c.cbsmc,c.cbbh 
 FROM cbsxx c
--  RIGHT 右
 RIGHT OUTER JOIN dsxx d  
 ON d.cbsmc = c.cbsmc
 ) a 
 ON s.cbbh = a.cbbh;

--  7.MAPJOIN HINT 一个大表和一个或者多个小表进行join操作，这时建议使用MAPJOIN,连接的性能将会得到显著提升。
-- 注意，MAPJOIN必须是小表：LEFT OUTER JOIN右面必须是小表；RIGHT OUTER JOIN左面必须是小表；INNERJOIN左右表均可以作为小表。
SELECT /* + MAPJOIN(c) */
 c.cbsmc,s.*
 FROM spxx s 
 LEFT OUTER JOIN cbsxx c 
 ON s.cbbh = c.cbbh ;

--  8.HAVING子句:WHERE关键字无法与合计函数一起使用，可以采用having字句，查询将商品按名称分组，求各商品平均价格，查看平均价格在100元以上的商品。
SELECT spmc ,AVG(spjg) FROM spxx GROUP BY spmc HAVING AVG(spjg) > 100;
-- SELECT spmc ,AVG(spjg) FROM spxx GROUP BY spmc HAVING AVG(spjg) > 100 DESC LIMIT 0,10;进行降序排列，在第0排开始。

第六章

-- 1.创建测试数据表
CREATE TABLE IF NOT EXISTS temp_test（
id BIGINT COMMENT '序号'
）
COMMENT '测试数据表'
LIFECYCLE 120;

-- 2.插入一条数据记录到测试数据表中
INSERT OVERWRITE TABLE temp_test VALUES (001);
DESC temp_test ;

-- 3.日期函数
-- （1）GETDATE:可通过这个命令获得系统当前时间。
SELECT GETDATE() FROM temp_test ;
-- （2）TO_CHAR:可通过这个命令将时间类型的数据转换为指定格式字符串。
-- 日期转换为字符串
SELECT TO_CHAR(GETDATE(),'日期函数 yyyy-mm-dd hh:mi:ss') FROM temp_test ;
-- 获取当前系统时时间
SELECT TO_CHAR(GETDATE(),'yyyymmddhhmiss') FROM temp_test ;
-- （3）TO_DATE:可通过这个命令将字符串类型的数据转换为日期时间型数据。
SELECT TO_DATE('日期函数 2005-05-28 01:38:06','日期函数 yyyy-mm-dd hh:mi:ss') FROM temp_test ;
-- 字符串类型的数据转换为日期时间型数据
SELECT TO_DATE('20050528013806','yyyymmddhhmiss') FROM temp_test ;
-- （4）DATEADD:进行日期加减运算。
SELECT DATEADD(GETDATE(),-1,'yyyy') FROM temp_test ;
--SQL1:
SELECT DATEADD(DATETIME'2017-05-28 01:38:06',-1,'yyyy') FROM temp_test;
--SQL2:
SELECT DATEADD(DATETIME'2017-05-31 01:38:06',8,'mm') FROM temp_test;
--SQL3:
SELECT DATEADD(DATETIME'2017-05-31 01:38:06',-31,'dd') FROM temp_test;
--SQL4:
SELECT DATEADD(DATETIME'2017-05-31 01:38:06',23,'hh') FROM temp_test;
--SQL5:
SELECT DATEADD(DATETIME'2017-05-31 01:38:06',-39,'mi') FROM temp_test;
--SQL6:
SELECT DATEADD(DATETIME'2017-05-31 01:38:06',54,'ss') FROM temp_test;

-- 4.DATEDIFF:进行两个日期差值计算：
--SQL1:
SELECT DATEDIFF(DATETIME'2017-05-28 01:38:06',DATETIME'2016-05-28 01:38:06','yyyy') FROM temp_test;
--SQL2：
SELECT DATEDIFF(DATETIME'2017-05-28 01:38:06',DATETIME'2018-01-31 01:38:06','mm') FROM temp_test;
--SQL3：
SELECT DATEDIFF(DATETIME'2017-05-31 01:38:06',DATETIME'2017-04-30 01:38:06','dd') FROM temp_test;
--SQL4：
SELECT DATEDIFF(DATETIME'2017-05-31 01:38:06',DATETIME'2017-06-01 00:38:06','hh') FROM temp_test;
--SQL5：
SELECT DATEDIFF(DATETIME'2017-05-31 01:38:06',DATETIME'2017-05-31 00:59:06','mi') FROM temp_test;
--SQL6：
SELECT DATEDIFF(DATETIME'2017-05-31 01:38:06',DATETIME'2017-05-31 01:39:00','ss') FROM temp_test;

-- 5.ISDATE：判断字符串是否可以按指定格式转换
-- 第一条语句字符串和指定日期格式可以匹配，返回true
SELECT ISDATE('2017-05-28 01:38:06','yyyy-mm-dd hh:mi:ss') FROM temp_test;
-- 第二条语句日期格式的时分秒之间去掉了冒号分隔符，导致匹配失败，返回结果为false.
SELECT ISDATE('2017-05-28 01:38:06','yyyy-mm-dd hhmiss') FROM temp_test; 

-- 6 LASTDAY：可获取当月最后一天。
SELECT LASTDAY(DATETIME'2017-05-07 01:38:06') FROM temp_test;

-- 7.DATEPART 提取日期指定部分的值
--SQL1 只提取年份:
SELECT DATEPART(DATETIME'2017-05-28 01:38:06','yyyy') FROM temp_test;
--SQL2 只提取月份:
SELECT DATEPART(DATETIME'2017-05-28 01:38:06','mm') FROM temp_test; 
--SQL3 只提取日:
SELECT DATEPART(DATETIME'2017-05-28 01:38:06','dd') FROM temp_test;
--SQL4 只提取小时:
SELECT DATEPART(DATETIME'2017-05-28 01:38:06','hh') FROM temp_test;
--SQL5 只提取分钟:
SELECT DATEPART(DATETIME'2017-05-28 01:38:06','mi') FROM temp_test;
--SQL6 只提取秒数:
SELECT DATEPART(DATETIME'2017-05-28 01:38:06','ss') FROM temp_test; 

--8.DATETRUNC() 截取日期指定部分的值
--SQL1:
SELECT DATETRUNC (DATETIME'2017-05-28 01:38:06','yyyy') FROM temp_test;
--SQL2:
SELECT DATETRUNC(DATETIME'2017-05-28 01:38:06','mm') FROM temp_test;
--SQL3:
SELECT DATETRUNC(DATETIME'2017-05-28 01:38:06','dd') FROM temp_test; 
--SQL4:
SELECT DATETRUNC(DATETIME'2017-05-28 01:38:06','hh') FROM temp_test;
--SQL5:
SELECT DATETRUNC(DATETIME'2017-05-28 01:38:06','mi') FROM temp_test; 
--SQL6:
SELECT DATETRUNC(DATETIME'2017-05-28 01:38:06','ss') FROM temp_test;

-- 9.FROM_UNIXTIME() 将 UNIX 时间转换为 MaxCompute 时间
SELECT FROM_UNIXTIME(123456789) FROM temp_test;

-- 10.TIMESTAMP() 将 MaxCompute 时间转换为 UNIX 时间
SELECT UNIX_TIMESTAMP (DATETIME'2017-11-11 05:33:09') FROM temp_test;

-- 11.WEEKDAY() 计算日期为本周第几天
SELECT WEEKDAY (DATETIME'2017-05-28 01:38:06') FROM temp_test;

-- 12.WEEKOFYEAR() 计算日期为本年第几周
SELECT WEEKOFYEAR ('2017-10-28 01:38:06') FROM temp_test;

-- 13.数学函数
-- （1）ABS()： 求-100 的绝对值
SELECT ABS (-100) FROM temp_test;
-- （2）ROUND() 四舍五入运算
SELECT ROUND (967.456,0) FROM temp_test;
--结果“967.0”
SELECT ROUND (967.456,1) FROM temp_test;
--结果“967.5”
SELECT ROUND (967.456,2) FROM temp_test;
--结果“967.46
-- （3）SQRT() 求 25 的算数平方根
SELECT SQRT (25) FROM temp_test;
--结果“5.0”
-- （4）RAND() 生成 0~1 之间的随机数
SELECT RAND () FROM temp_test; 
--结果随机“0.000471”
SELECT RAND (16) FROM temp_test;
--结果随机“0.549563”
-- （5）POW() 次方运算
SELECT POW (3,2) FROM temp_test;
--结果“9.0”
-- （6）LOG() 求对数
SELECT LOG (10,100) FROM temp_test;
--结果“2.0”

-- 14.聚合函数
-- 创建学生身高表
CREATE TABLE temp_xssg(
 xh STRING COMMENT '学号',
 xm STRING COMMENT '姓名',
 xb string COMMENT '性别',
 bjbh STRING COMMENT '班级编号',
 sg double COMMENT '身高'
) COMMENT '学生身高表'
LIFECYCLE 120;
-- 查询数据条数
SELECT  COUNT(1) FROM temp_xssg;
-- （1）COUNT():进行数据的统计运算。
SELECT bjbh,COUNT(*),COUNT(sg) FROM temp_xssg GROUP BY bjbh;
-- 查询身高 sg 字段的值为 null 的记录
SELECT * FROM temp_xssg WHERE sg IS NULL;
-- （2）MAX()：就数据的最大值运算。
SELECT bjbh,MAX(sg) FROM temp_xssg GROUP BY bjbh;
-- （3）MIN()：求数据的最小值运算。
SELECT bjbh,MIN(sg) FROM temp_xssg GROUP BY bjbh;
-- （4）AVG()：求数据的平均值运算。
SELECT bjbh,AVG(sg) FROM temp_xssg GROUP BY bjbh;
-- （5）MEDIAN()：求数据的中位数运算。
SELECT bjbh,MEDIAN(sg) FROM temp_xssg GROUP BY bjbh;
-- （6）SUM()：进行求和运算。
SELECT bjbh,SUM(sg) FROM temp_xssg GROUP BY bjbh;
-- （7）STDDEV()：计算总体标准差。
SELECT bjbh,STDDEV(sg) FROM temp_xssg GROUP BY bjbh;
-- （8）STDDEV_SAMP()：计算样本标准差。
SELECT bjbh,STDDEV_SAMP(sg) FROM temp_xssg GROUP BY bjbh;
-- （9）WM_CONCAT()：进行列转行运算。
SELECT bjbh,WM_CONCAT(',',xm) FROM temp_xssg GROUP BY bjbh;

-- 15.窗口函数
-- （1）ROW_NUMBER()：进行行号统计运算。
SELECT ROW_NUMBER() OVER (PARTITION BY bjbh ORDER BY sg),bjbh,xh,xm,sg FROM temp_xssg;
-- （2）RANK()：进行排名运算。
SELECT RANK() OVER (PARTITION BY bjbh ORDER BY sg),bjbh,xh,xm,sg FROM temp_xssg;
-- （3）DENSE_RANK()：进行连续排名计算。
SELECT DENSE_RANK() OVER (PARTITION BY bjbh ORDER BY sg),bjbh,xh,xm,sg FROM temp_xssg;
-- （4）PERCENT_RANK()：进行一组数据中某行的相对排名运算。
SELECT PERCENT_RANK() OVER (PARTITION BY bjbh ORDER BY sg),bjbh,xh,xm,sg FROM temp_xssg;

-- 16.字符串函数
-- （1）ASCII() 返回字符串“123”中数字“1”的 ASCII 码（返回字符串第一个字符的ASCII码）
SELECT ASCII('1') FROM temp_test;
-- （2）将给定的ASCII码值转换为对应的ASCII字符串。
-- CHR() 获取 ASCII 码为“50”的字符串
SELECT CHR(50) FROM temp_test;
-- （3）获取字符串长度。
-- LENGTH() 获取'你好！abcd'的字符串长度
SELECT LENGTH('你好！abcd') FROM temp_test;
-- （4）将大写字符串转换为小写。
-- TOLOWER() 将('你好！aBcD')中的“BD”转化为小写
SELECT TOLOWER('你好！aBcD') FROM temp_test;
-- （5）将小写字符串转换为大写
-- TOUPPER() 将('你好！aBcD')中的“ac”转化为“AC”
SELECT TOUPPER('你好！aBcD') FROM temp_test;
-- （6）将字符串拼接起来。
-- CONCAT()将逗号分割的字符串('你好！','aBcd')进行拼接
SELECT CONCAT('你好！','aBcd') FROM temp_test;
-- （7）TO_CHAR()：将其他类型数据转换为字符串。
SELECT TO_CHAR(true) FROM temp_test;
-- （8）MD5()：获取相关参数的MD5值
SELECT MD5('123') FROM temp_test;
-- （9）去掉字符串两端的空格。
-- TRIM ()去掉（‘ 123 ’）两边的空格
SELECT TRIM(' 123 ') FROM temp_test;
-- （10）LENGTHB()：计算以字节为单位的字符串长度。
SELECT LENGTHB('你好！123') FROM temp_test;

-- 17.用户自定义函数

第七章

第八章

第九章

测验

第二次测验：

-- 1、通过脚本文件的形式创建一个视图，将班级信息（bjxx）和学生信息（xsxx）两张数据表关联起来，
-- 得到一张“大宽表”的拼接效果。 “班级学生信息关联视图”（view_bjxx_xsxx），
-- 由班级信息表（bjxx）的4个列和学生信息表（xsxx）的3个列构成；
CREATE VIEW IF NOT EXISTS view_bjxx_xsxx (
bjbh,
bjmc,
rxrq,
yxbh,
xh,
xm,
xb
)COMMENT '班级、学生信息关联'
AS SELECT 
b.bjbh,
b.bjmc,
b.rxrq,
b.yxbh,
x.xh,
x.xm,
x.xb
FROM bjxx b
LEFT OUTER JOIN xsxx x
ON b.bjbh = x.bjbh
;
DESC view_bjxx_xsxx;

-- 2、向（YXXX）增加2个数据列，BIGINT型的“专业代码”（Professionalcode）和STRING型的“专业名称”（Professional）
ALTER TABLE  yxxx  ADD COLUMNS (Professionalcode BIGINT COMMENT '专业代码',Professional BIGINT COMMENT '专业名称');
DESC yxxx ;


-- 3、（1）可视化创建分区表：
-- 创建“学生行为表”分区表
CREATE TABLE IF NOT EXISTS Student_Behavior(
xwsj STRING COMMENT '行为时间',
xh   STRING COMMENT '学号',
xwdd STRING COMMENT '行为地点',
xwdm STRING COMMENT '行为代码',
bzsm STRING COMMENT '备注说明'
) 
COMMENT '学生行为信息'
PARTITIONED BY
(
    xwrq STRING COMMENT '行为日期',
    yxbh STRING COMMENT '院系编号',
    xkbh STRING COMMENT '学科编号'
)LIFECYCLE 120;
-- 查看学生行为表定义
DESC xsxw ;


-- （2）添加分区：分区条件“行为日期+院系编号+学科编号”，
-- 其中行为日期xwrq的值为“20170718”，院系编号yxbh为“'JGXY'”
-- （经管学院），学科编号xkbh为“BigData”（学科编号）
ALTER TABLE student_behavior ADD IF NOT EXISTS PARTITION (xwrq='20120718',yxbh='JGXY',xkbh='BigData');
SHOW PARTITIONS student_behavior;

-- （3）调整分区表的生命周期从现在开始继续延续120天；
-- 查看分区详细信息：
DESC student_behavior PARTITION (xwrq='20120718',yxbh='JGXY',xkbh='BigData');
-- 修改该学生行为表的属性值，修改分区数据最后修改时间：
ALTER TABLE student_behavior TOUCH PARTITION (xwrq='20120718',yxbh='JGXY',xkbh='BigData');
-- 查看分区详细信息：
DESC student_behavior PARTITION (xwrq='20120718',yxbh='JGXY',xkbh='BigData');

最后测试

-- 从订单表ods_market_orders中，
-- 通过字段商品ID goods_id关联商品类目表ods_market_item中的商品ID goods_id，
-- 控制订单时间order_time在2019年11月1日到2019年11月15之间，
-- 按点击来源click_source分组统计订单状态pay_staus为“支付成功”的销售商品的种类数量（商品ID goods_id去重计数）、
-- 商品销售金额（商品售价goods_price之和），并取出销售商品种类数据>=10种并且商品销售金额>=800的渠道列表。
SELECT order_time
        ,COUNT(DISTINCT (kinds))
        ,SUM(goofs_price)
        ,click_source
FROM  s a
JOIN ods_market_item b
ON a.goods_id = b.goods_id
WHERE order_time BETWEEN UNIX_TIMESTAMP(DATETIME '2019-11-01 00:00:00')
AND UNIX_TIMESTAMP(DATETIME '2019-11-15 00:00:00')
AND kinds >= 10 AND xiaoshoue >= 800
GROUP BY click_source
AND pay_staus ='支付成功'
;