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 中，加一个别名，在后面用到的时候就可以直接用。

对于大批量重复使用到共同的数据，有一定优化的作用，只读取数据一次，生成中间临时表，居于临时表的数据，做后续不同的操作。

-选择语句中的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 ;