Sql理解及函数总结
Common Table Expressions(CTE)
CTE介绍
公用表表达式(CTE)是一个临时结果集,该结果集是从WITH子句中指定的简单查询派生而来的,该查询紧接在SELECT或INSERT关键字之前。
CTE仅在单个语句的执行范围内定义。一个或多个CTE可以在Hive SELECT,INSERT, CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用。
WITH AS短语,也叫做子查询部分(subquery factoring),是用来定义一个SQL片断,该SQL片断会被整个SQL语句所用到。这个语句算是公用表表达式(CTE)。比如with tmp as (select * from table_name) select *from tmp ;
这个语句的意思就是,先执行select * from table_name 得到一个结果,将这个结果记录为tmp ,在执行select *from tmp 语句。tmp表只是一个别名。也就是将重复用到的大批量 的SQL语句,放到with as 中,加一个别名,在后面用到的时候就可以直接用。
对于大批量重复使用到共同的数据,有一定优化的作用,只读取数据一次,生成中间临时表,居于临时表的数据,做后续不同的操作。
1.使用with子句可以让子查询重用相同的with查询块,通过select调用(with子句只能被select查询块引用),一般在with查询用到多次情况下。在引用的select语句之前定义,同级只能定义with关键字只能使用一次,多个用逗号分割。
2.with子句的返回结果存到用户的临时表空间中,只做一次查询,反复使用,提高效率。
3.在同级select前有多个查询定义的时候,第1个用with,后面的不用with,并且用逗号隔开。
4.最后一个with 子句与下面的查询之间不能有逗号,只通过右括号分割,with 子句的查询必须用括号括起来
5.如果定义了with子句,而在查询中不使用,那么会报ora-32035 错误:未引用在with子句中定义的查询名。(至少一个with查询的name未被引用,解决方法是移除未被引用的with查询),注意:只要后面有引用的就可以,不一定非要在主查询中引用,比如后面的with查询也引用了,也是可以的。
6.前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句。
7.当一个查询块名字和一个表名或其他的对象相同时,解析器从内向外搜索,优先使用子查询块名字。
8.with查询的结果列有别名,引用的时候必须使用别名或*。
-选择语句中的CTE with q1 as (select sno,sname,sage from student where sno = 95002) select * from q1; -- from风格 with q1 as (select sno,sname,sage from student where sno = 95002) from q1 select *; -- chaining CTEs 链式 with q1 as ( select * from student where sno = 95002), q2 as ( select sno,sname,sage from q1) select * from (select sno from q2) a; -- union案例 with q1 as (select * from student where sno = 95002), q2 as (select * from student where sno = 95004) select * from q1 union all select * from q2; --视图,CTAS和插入语句中的CTE -- insert create table s1 like student; with q1 as ( select * from student where sno = 95002) from q1 insert overwrite table s1 select *; -- 统计前十城市对应发明公开量、发明授权量、有效发明量 WITH t_city AS ( SELECT chain_code, chain_name, c_f_ass_std_city as "city", cnt, ROW_NUMBER() OVER (ORDER BY cnt DESC) AS "rn" FROM ( SELECT chain_code, chain_name, c_f_ass_std_city, COUNT(distinct bi.appnum) as "cnt" FROM ( SELECT appnum, c_f_ass_std_city FROM dwd_patent_cn_baseinfo -- 中国专利著录项目数据 ) bi JOIN ( SELECT appnum FROM dwm_patent_cn_tag -- 中国专利标签数据 WHERE school_self_patent_assets = '1' ) tag ON bi.appnum = tag.appnum JOIN ( SELECT appnum, chain_code, chain_name FROM dwm_patent_cn_industry_chain -- 中国专利行业领域数据_产业链 WHERE chain_code = 'L6' ) ch ON bi.appnum = ch.appnum GROUP BY chain_code, chain_name, c_f_ass_std_city ) WHERE c_f_ass_std_city IS NOT NULL LIMIT 10 ), t_cb AS ( SELECT appnum, patent_type_cn, c_f_app_std_city, o_f_ass_std_city, c_f_ass_std_city, lawstatus_code FROM dwd_patent_cn_baseinfo ) SELECT chain_code, chain_name, city, cnt, rn ,MAX(CASE type WHEN '发明公开' THEN total_cnt END) AS "发明公开量" -- 行专列操作 ,MAX(CASE type WHEN '发明授权' THEN total_cnt END) AS "发明授权量" -- 行专列操作 ,MAX(CASE type WHEN '有效发明' THEN total_cnt END) AS "有效发明量" -- 行专列操作 FROM ( SELECT chain_code, chain_name, city, cnt, rn, '发明公开' AS type, COUNT(DISTINCT appnum) AS "total_cnt" FROM t_city LEFT JOIN ( SELECT appnum ,c_f_app_std_city FROM t_cb WHERE patent_type_cn = '发明公开' -- 发明公开 ) b1 ON t_city.city = b1.c_f_app_std_city GROUP BY chain_code, chain_name, city, cnt, rn UNION ALL SELECT chain_code, chain_name, city, cnt, rn, '发明授权' AS type, COUNT(DISTINCT appnum) AS "total_cnt" FROM t_city LEFT JOIN ( SELECT appnum ,o_f_ass_std_city FROM t_cb WHERE patent_type_cn = '发明授权' -- 发明授权 ) b2 ON t_city.city = b2.o_f_ass_std_city GROUP BY chain_code, chain_name, city, cnt, rn UNION ALL SELECT chain_code, chain_name, city, cnt, rn, '有效发明' AS type, COUNT(DISTINCT appnum) AS "total_cnt" FROM t_city LEFT JOIN ( SELECT appnum ,c_f_ass_std_city FROM t_cb WHERE patent_type_cn = '发明授权' AND lawstatus_code = '02' -- 有效发明 ) b3 ON t_city.city = b3.c_f_ass_std_city GROUP BY chain_code, chain_name, city, cnt, rn ) GROUP BY chain_code, chain_name, city, cnt, rn ;