数据仓库开发 SQL 使用技巧总结

-- 辅助索引select * from t where a > '1999-08-01';

--demo1 case 搜索函数 case when xxx then xxx else endselect id, case when score < 60 then 'low' when score < 90 then 'middle'    else 'high'    end as rankfrom test
--demo2 简单 case 函数 case field when xxx then xxx else endselect id case score when 0 then 'bad' when 100 then 'good' else 'middle'    end

-- 定义临时表with tmp as (select * from test)-- 使用临时表select * from tmp
-- 定义临时表with tmp as (    select id from test where score > 60)-- 使用临时表select distinct id from tmp;

select score, count(distinct id)from testgroup by score

-- demo-- 下图结果第三行就是超级聚合行select    name, sum(score)from    studentsgroup by rollup(name)

-- union 去重, union all 不去重select column_name(s) from table_name1unionselect column_name(s) from table_name2

-- in/notin-- 略
-- exists/not exists(略)-- 子查询是相关的, 对于 scores 表中的每一行，子查询检查 class 表中是否有对应的行。 如果有匹配行，则子查询返回一个使外部查询包含 scores 表中的当前行的子查询。 如果没有相应的行，则子查询不返回导致外部查询不包括结果集中 scores 表中的当前行的行select    id, scorefrom    scoreswhere    exists(        select            1        from            class        where            class.id = scores.id    );
-- between/not between(略)select    id, scorefrom scoreswhere    score between 2500 and 3000

CREATE EXTERNAL TABLE IF NOT EXISTS test (    `quota`          STRING COMMENT '',    `package`          INT    COMMENT '',    `all_sys`         INT    COMMENT '') COMMENT 'test'PARTITIONED BY (timeline STRING COMMENT '时间分区')STORED AS PARQUET

-- 函数用于将多行数据聚合为单行，从而提供与特定值关联的数据列表，它将以逗号来分割列表-- 结果-- 1 python,c-- 2 r,java-- 3 swiftselect id,wm_concat(',',name) as language from tmp_test group by id;
-- 字符串截取-- CDEselect substring("ABCDE", 3)
-- json 解析select get_json_object({"score":3}, '$.score')

-- coalesce(expression_1, expression_2, ...,expression_n) 依次参考各参数表达式，遇到非 null 值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值select coalesce(null, null, '200')

-- 条件判断，满足第一个表达式返回 1, 否则返回 0select if(1>0, 1, 0)

-- 结果: a#b#cselect concat_ws('#', 'a', 'b', 'c', null)

-- 特性: 它们都是将分组中的某列转为一个数组返回，不同的是 collect_list 不去重而 collect_set 去重-- collect_set 去重, collect_list 不去重-- 还可以利用 collect 来突破 group by 的限制, hive 中在 group by 查询的时候要求出现在 select 后面的列都必须是出现在 group by 后面的，即 select 列必须是作为分组依据的列select username, collect_list(video_name)[0] from t_visit_video group by username;

-- 结果-- +----------+--------+-- | lastname |  name  |-- +----------+--------+-- |     a    |  aa,ab |-- |     b    |  ba,bb |-- +----------+--------+select last_name, group_concat(name) as name from test where lastname in ('a', 'b')

-- 数据准备-- id score-- 1  100,96,98,100,96,85-- 2  98,97,100,85,99,100
-- 计算-- 找到具备 100 的 idselect *from socre lateral view explode(spilt(scores, ',')) score AS each_scorewhere each_score in ("100")

-- parse_components 就是业务种自定义的 udf 函数，用来解析一个复杂得动态字段，此字段根据不同的模板可能出现得字段枚举超过百种select    test...,    parse_components(doc.components, '100', '101').test as template_fieldfrom testorder by    test

-- partition by 用于给结果集分组，另外如果不指定那么会默认把整个结果集作为分组-- partition by 需要分组的列名-- order by 需要排序的列名-- rows between 参与计算的行起始位置 and 参与计算的行终止位置-- over括号中的那些如果不需要可以省略<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名> rows between <起始位置> and <终止位置>)-- rows between 指定计算起始行和结束行的方法-- rows between 3 preceding and current row 包括本行和前三行-- rows between current row and 3 following 包括本行和后三行-- rows between unbounded preceding and current row 包括本行和该分组内之前所有的行-- rows between current row and unbounded following 包括本行和该分组内之后所有行-- rows between 5 preceding and 1 following 包括前五行和后一行
-- over (order by x) 相当于 over(order by x rows between unbounded preceding and current now) 从前方无界到当前行
-- over () 相当于从前方无界到后方无界，整组内容
-- 另, partition 子句可省略省略就是不指定分组-- 例:select *, rank() over (order by scores desc) as ranking from students

select a.id, a.a, a.b, a.c, a.d  from (select t.id,               t.a,               t.b,               t.c,               t.d,               rank() over(partition by t.a order by t.b desc) rk          from test t) awhere rk < 4;

--distinct_org_idselect *from ( select  org.*,  row_number() over (partition by org.id, org.name order by org.creat_time asc) rk from  org_test as org) as tempwhere rk = 1

-- 求成绩再前百分之 20 的分数select score, ntile (5) over (order by score) bucketsfrom scores.ntile_demowhere buckets = 1;

-- 语法-- lag(field, num, defaultvalue)-- 函数可以在一次查询中取出当前行的同一字段 field 的前面第 num 行的数据，如果没有用 defaultvalue 代替-- lead(field, num, defaultvalue)-- 函数可以在一次查询中取出当前行的同一字段 field 的后面第 num 行的数据，如果没有用 defaultvalue 代替
-- 计算今日材料消耗(需计算今日材料损耗和昨日差值)select name,       data,    --今日抄表值       use as use_today,    --前一天抄表值       lag(use,1,0) over(partition BY name order by data desc) as use_yesdayfrom test

-- 数据准备-- 计算每个月销售额，上一月销售额，下月销售额，相邻月销售额平均值insert into dc_test_order values(100, 1), (200, 2),(300, 3), (400, 4),(500, 5), (600, 6),(700, 7), (800, 8),(900, 9)
-- 计算select month,  first_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) prev_month,  sum(tot_sales) monthly_sales,  last_value(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) next_month,  avg(sum(tot_sales)) over (order by month rows between 1 preceding and 1 following) rolling_avgfrom dc_test_ordergroup by monthorder by month;-- 结果|month|prev_month|monthly_sales|next_month|rolling_avg|+-----+----------+-------------+----------+-----------+|    1|     100.0|        100.0|     200.0|      150.0||    2|     100.0|        200.0|     300.0|      200.0||    3|     200.0|        300.0|     400.0|      300.0||    4|     300.0|        400.0|     500.0|      400.0||    5|     400.0|        500.0|     600.0|      500.0||    6|     500.0|        600.0|     700.0|      600.0||    7|     600.0|        700.0|     800.0|      700.0||    8|     700.0|        800.0|     900.0|      800.0||    9|     800.0|        900.0|     900.0|      850.0|+-----+----------+-------------+----------+-----------+

-- 数据准备-- 计算小于等于当前成绩的人数占总人数占比insert into dc_test_scores values(1, 100), (2, 200),(3, 300), (4, 400),(5, 500)
-- 计算select    id,    score,    cume_dist() over(order by score) as rn1from test
-- 结果|id|score|rn1|+--+---------+| 1|100.0|0.2|| 2|200.0|0.4|| 3|300.0|0.6|| 4|400.0|0.8|| 5|500.0|1.0|+--+-----+---+

-- 数据准备-- 给定行的百分比排名insert into dc_test_scores values(1, 100), (2, 200),(3, 300), (4, 400),(5, 500)
--计算select id, score, percent_rank() over (partition by id order by score) as rn1from test
--结果|id|score|rn1 |+--+----------+| 1|100.0|0.0 || 2|200.0|0.25|| 3|300.0|0.5 || 4|400.0|0.75|| 5|500.0|1.0 |+--+-----+----+

with RECURSIVE T as(select '1' as id,'10' as parent_id union allselect '2', '20' union allselect '3', '30' union allselect '20', '200' union allselect '10', '100' union allselect '100', '1000' union allselect '200', '2000' union allselect '1000', '10000'), A as (select id, parent_id from T where id = '1'union allselect T.id,T.parent_id from Tinner join A on T.id=A.parent_id)
select * from A;-- 结果-- id parent_id-- 1 10-- 10 100-- 100 1000

--递归一个连续时间表with t as(    select        date_add( to_date('2022-04-14'), i) as timeline -- 基础日期，起始时间    from        (select 80 as days) t    lateral view    posexplode(split(repeat(',', days), ',')) pe as i, x)

 -- 查看成本show status like 'last_query_cost'

select oid from order where uid = 1 and status != 1;

-- demo1select id, content from news order by title limit 50,5--> 优化后select news.id, news.contentfrom news inner join (    select id from news order by title limit 50,5) as tmp using(id)
-- demo2select id from t limit 10000, 10--> 优化后select id from t where id > 10000 limit 10

-- 谓词下推到存储层-- demo1select * from t where a < 1;-- demo2select * from t where a < substring('123', 1, 1);
-- 谓词下推到 join 下方-- demo3select * from t join s on t.a = s.a where t.a < 1;-- 在该查询中，将谓词 t.a < 1 下推到 join 前进行过滤，可以减少 join 时的计算开销-- 这条 sql 执行的是内连接，且 on 条件是 t.a = s.a，可以由 t.a < 1 推导出谓词 s.a < 1，并将其下推至 join 运算前对 s 表进行过滤，可以进一步减少 join 时的计算开销-- 得到select * from t join s on t.a = s.a and t.a < 1 and s.a < 1

-- demo 1-- 不对函数进行支持则无法下推select * from t where substring('123', a, 1) = '1';-- demo 2-- 外连接中内表上的谓词不能下推-- 该谓词没有被下推到 join 前进行计算，这是因为外连接在不满足 on 条件时会对内表填充 NULL，而在该查询中 s.a is null 用来对 join 后的结果进行过滤，如果将其下推到 join 前在内表上进行过滤，则下推前后不等价，因此不可进行下推select * from t left join s on t.a = s.a where s.a is null;

-- 常用以下两种-- 1count(distinctcase when    conditionthen result1else    result2end)
-- 2count(condition or null)

select if(date_format('${yyyy-mm-dd}' ,"u") between '1' and '5','平日','周末')

-- 汇总数据表-日数据insert overwrite table test.test partition(timeline='${yyyy-mm-dd-1d}')select ...,    count(distinct           case when left(reg.create_time , 10) = '${yyyy-mm-dd-1d}'           then reg.id           else null           end           as create_cnt,    count(distinct reg.id) as create_cnt_alfrom       ...

-- 思路如下-- 1. 首先使用 timeline 分组, 使用窗口函数, 计算每个时间分组内的一个增量数目-- 2. 获取每个时间分组的数目的最大值, 也就是该时间段产生的一个数目-- 3. 使用窗口函数, 不指定分组(则默认就是整个表作为一个分组), 此时使用 sum 得到累加值select    timeline,    day_cnt,    sum(day_cnt) over (order by timeline) as day_cnt_addfrom (    select        timeline,        max(create_cnt) as day_cnt    from (        select            timeline,            count(id) over(partition by timeline order by timeline rows between unbounded preceding and current row) as create_cnt        from            default.test    ) as group_test    group by        timeline    order by        timeline) as group_test

with tmp2 as (    select    a.first_time,    count(a.org_id) as num    from    (        select        test.org_id as org_id,        min(left(create_date, 10)) as first_time        from        default.test as test        where        org_id != ''        group by        test.org_id    ) as a    group by a.first_time)select a.first_time, sum(b.num) as all_numfrom tmp2 a, tmp2 bwhere b.first_time <= a.first_timegroup by a.first_timeorder by a.first_time
--with tmp as (        select        A.give_day,        count(A.org_id) as num        from(                        select                        a.*,                        left(created_on, 10) as give_day                        from                        test_org as a                ) A        where                A.give_day >= '2000-01-01'        group by A.give_day)select a.give_day,        -- a.num,        sum(case when b.give_day = a.give_day then b.num else 0 end), sum(b.num) as all_numfrom tmp a, tmp bwhere b.give_day <= a.give_daygroup by a.give_dayorder by a.give_day

select ... from users where id in (...) order by field(id, 'id1', 'id2', ...)