重要SQL记录

最新推荐文章于 2023-09-18 15:18:09 发布

Resemble_

最新推荐文章于 2023-09-18 15:18:09 发布

阅读量1.6k

点赞数

CC 4.0 BY-SA版权

分类专栏： Hive 文章标签： sql

本文链接：https://blog.youkuaiyun.com/qq_27657429/article/details/79630812

Hive 专栏收录该内容

4 篇文章

订阅专栏

同环比

select '日环比' as datekey,
       if(b.total_poi_num=0,0,round(cast(a.total_poi_num as double)/b.total_poi_num-1,4)) as total_poi_num
  from (
            select datekey,
                   'all' as biz_site,
                   total_poi_num
       ) a
left join (
            select datekey,
                  'all' as  biz_site,
                   total_poi_num
          ) b on a.biz_site = b.biz_site
UNION ALL
select '周同比' as datekey,
       if(b.total_poi_num=0,0,round(cast(a.total_poi_num as double)/b.total_poi_num-1,4)) as total_poi_num,

  from (
            select datekey,
                   'all' as  biz_site,
                   total_poi_num as total_poi_num, 

       ) a
left join (
            select datekey,
                   'all' as  biz_site,
                   total_poi_num as total_poi_num, 

          ) b on a.biz_site = b.biz_site
UNION ALL
select datekey2date('$$yesterday_compact ') as datekey,
       sum(total_poi_num) as total_poi_num,

主要是’all’ as biz_site 这步很经典

行名称转为列名称

select sum(st.name1) name1, sum(st.name4) name4 
from(
select case when user_name = 'name1' then age end name1,
case when user_name = 'name4' then age end name4 
from student_info) as st;

主要是将原来的列里面的名称变成了行名称，其实也就是case when user_name = ‘name1’ then age这步

count(distinct)优化

SELECT day,

COUNT(DISTINCT id) AS uv

FROM user_table

GROUP BY day

`可以转换成：`

SELECT day,

COUNT(id) AS uv

FROM (SELECT day,id FROM user_table GROUP BY day,id) a

GROUP BY day;

数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换：
虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的。

多重COUNT(DISTINCT)优化

SELECT day,
       COUNT(DISTINCT id) AS id_count,
       COUNT(DISTINCT name) AS name_count
  FROM user_table
 GROUP BY day

`可以转换成：`
SELECT day,
       SUM(CASE WHEN type='id_count' THEN 1 ELSE 0 END) as id_count,
       SUM(CASE WHEN type='name_count' THEN 1 ELSE 0 END) as name_count
  FROM (
        SELECT day,
               id,
               'id_count' AS type
          FROM user_table
         GROUP BY day,
                  id
     UNION ALL SELECT day,
               name,
               'name_count' AS type
          FROM user_table
         GROUP BY day,
                  name
       ) a
 GROUP BY day;