精华内容
下载资源
问答
  • 原标题:MySQL 8.0窗口函数优化SQL一例8.0的窗口函数真香1. 问题描述最近在折腾把所有mysql slow query log写入到数据库中,再集中展示,向业务部门开放,也方便业务部门的同学自行查看并优化各自业务内的慢SQL。...

    原标题:MySQL 8.0窗口函数优化SQL一例

    8.0的窗口函数真香1. 问题描述

    最近在折腾把所有mysql slow query log写入到数据库中,再集中展示,向业务部门开放,也方便业务部门的同学自行查看并优化各自业务内的慢SQL。增加了定期生成报表的功能,统计最近1~2周内的慢查询数量变化情况,给业务方同学更直观的数据对比,了解最近这段时间的慢查询数量变化情况,是多了还是少了。于是有了下面这一坨SQL:

    select hostname_max , db_max, sum(ts_cnt) as 1W

    (select ifnull(sum(t1.ts_cnt),0) as ts_cnt from global_query_review_history t1 where

    t1.hostname_max=t2.hostname_max and t1.ts_min>= date_sub(now, interval 14 day) and

    t1.ts_max<= date_sub(now, interval 7 day)) AS 2W

    from global_query_review_history t2 where

    ts_min>= date_sub(now, interval 7 day)

    group by hostname_max, db_max

    order by 1W desc limit20;

    当前 global_query_review_history 表约有2.5万条记录,这条SQL耗时 1.16秒,显然太慢了。下面是SQL执行计划:

    *************************** 1. row ***************************

    id: 1

    select_type: PRIMARY

    table: t2

    partitions: NULL

    type: ALL

    possible_keys: ts_min

    key: NULL

    key_len: NULL

    ref: NULL

    rows: 25198

    filtered: 41.09

    Extra: Using where; Using temporary; Using filesort

    *************************** 2. row ***************************

    id: 2

    select_type: DEPENDENT SUBQUERY

    table: t1

    partitions: NULL

    type: ref

    possible_keys: hostname_max,ts_min

    key: hostname_max

    key_len: 258

    ref: func

    rows: 20

    filtered: 14.90

    Extra: Using where

    可以看到需要进行一次子查询(无法自动优化成JOIN)。

    SQL执行后的status统计值:

    +-----------------------+--------+

    | Variable_name | Value |

    +-----------------------+--------+

    | Handler_read_first | 0 |

    | Handler_read_key | 17328 |

    | Handler_read_last | 0 |

    | Handler_read_next | 809121 |

    | Handler_read_prev | 0 |

    | Handler_read_rnd | 0 |

    | Handler_read_rnd_next | 25380 |

    +-----------------------+--------+

    2. SQL优化

    上面的SQL主要瓶颈在于嵌套子查询,去掉子查询,即便是全表扫描也还是很快的。

    [root@yejr.run]> select ...

    ...

    20 rows inset(0.08 sec)

    [root@yejr.run]> show status like 'handler%read%';

    +-----------------------+-------+

    | Variable_name | Value |

    +-----------------------+-------+

    | Handler_read_first | 0 |

    | Handler_read_key | 16910 |

    | Handler_read_last | 0 |

    | Handler_read_next | 0 |

    | Handler_read_prev | 0 |

    | Handler_read_rnd | 0 |

    | Handler_read_rnd_next | 25380 |

    +-----------------------+-------+

    SQL优化有困难自然先想到了松华老师,在得知我用的MySQL 8.0之后,他帮忙给改造成了基于窗口函数的写法:

    select hostname_max , db_max,

    sum( casewhen ts_min>= date_sub(now, interval 7 day) thents_cnt end ) as 1W,

    ifnull(sum( casewhen ts_min>= date_sub(now, interval 14 day)

    and ts_max<= date_sub(now, interval 7 day) thents_cnt end ) over(partition by hostname_max),0) 2W

    from global_query_review_history t2

    wherets_min>= date_sub(now, interval 14 day)

    group by hostname_max, db_max

    order by 1W desc limit20;

    再看下执行计划:

    *************************** 1. row ***************************

    id: 1

    select_type: SIMPLE

    table: t2

    partitions: NULL

    type: ALL

    possible_keys: ts_min

    key: NULL

    key_len: NULL

    ref: NULL

    rows: 25198

    filtered: 44.88

    Extra: Using where; Using temporary; Using filesort

    新SQL比较取巧,只需要读取一次数据,利用窗口函数直接计算出需要的统计值。虽然有可用索引,但因为要扫描的数据量比较大,所以最后还是变成全表扫描。新SQL耗时和status统计值见下:

    20 rows inset(0.08 sec)

    [root@yejr.run]> show status like 'handler%read%';

    +-----------------------+-------+

    | Variable_name | Value |

    +-----------------------+-------+

    | Handler_read_first | 0 |

    | Handler_read_key | 24396 |

    | Handler_read_last | 0 |

    | Handler_read_next | 0 |

    | Handler_read_prev | 0 |

    | Handler_read_rnd | 886 |

    | Handler_read_rnd_next | 26703 |

    +-----------------------+-------+

    和之前那个SQL差距太大了,优化效果杠杠滴。

    Enjoy MySQL 8.0 :)

    延伸阅读

    扫码关注松华老师「深入SQL编程开发与优化」课程 返回搜狐,查看更多

    责任编辑:

    展开全文
  • 问题描述最近在折腾把所有mysql slow query log写入到数据库中,再集中展示,向业务部门开放,也方便业务部门的同学自行查看并优化各自业务内的慢SQL。增加了定期生成报表的功能,统计最近1~2周内的慢查询数量变化...

    8.0的窗口函数真香

    1. 问题描述

    最近在折腾把所有mysql slow query log写入到数据库中,再集中展示,向业务部门开放,也方便业务部门的同学自行查看并优化各自业务内的慢SQL。增加了定期生成报表的功能,统计最近1~2周内的慢查询数量变化情况,给业务方同学更直观的数据对比,了解最近这段时间的慢查询数量变化情况,是多了还是少了。于是有了下面这一坨SQL:

    select hostname_max , db_max, sum(ts_cnt) as 1W

    (select ifnull(sum(t1.ts_cnt),0) as ts_cnt from global_query_review_history t1 where

    t1.hostname_max=t2.hostname_max and t1.ts_min>= date_sub(now(), interval 14 day) and

    t1.ts_max<= date_sub(now(), interval 7 day)) AS 2W

    from global_query_review_history t2 where

    ts_min>= date_sub(now(), interval 7 day)

    group by hostname_max, db_max

    order by 1W desc limit 20;

    当前 global_query_review_history 表约有2.5万条记录,这条SQL耗时 1.16秒,显然太慢了。下面是SQL执行计划:

    *************************** 1. row ***************************

    id: 1

    select_type: PRIMARY

    table: t2

    partitions: NULL

    type: ALL

    possible_keys: ts_min

    key: NULL

    key_len: NULL

    ref: NULL

    rows: 25198

    filtered: 41.09

    Extra: Using where; Using temporary; Using filesort

    *************************** 2. row ***************************

    id: 2

    select_type: DEPENDENT SUBQUERY

    table: t1

    partitions: NULL

    type: ref

    possible_keys: hostname_max,ts_min

    key: hostname_max

    key_len: 258

    ref: func

    rows: 20

    filtered: 14.90

    Extra: Using where

    可以看到需要进行一次子查询(无法自动优化成JOIN)。

    SQL执行后的status统计值:

    +-----------------------+--------+

    | Variable_name         | Value  |

    +-----------------------+--------+

    | Handler_read_first    | 0      |

    | Handler_read_key      | 17328  |

    | Handler_read_last     | 0      |

    | Handler_read_next     | 809121 |

    | Handler_read_prev     | 0      |

    | Handler_read_rnd      | 0      |

    | Handler_read_rnd_next | 25380  |

    +-----------------------+--------+

    可以看到除了有全表扫描外,还要根据索引的多次逐行扫描(Handler_read_next = 809121,子查询引起的)。

    2. SQL优化

    上面的SQL主要瓶颈在于嵌套子查询,去掉子查询,即便是全表扫描也还是很快的。

    [root@yejr.run]> select ...

    ...

    20 rows in set (0.08 sec)

    [root@yejr.run]> show status like 'handler%read%';

    +-----------------------+-------+

    | Variable_name         | Value |

    +-----------------------+-------+

    | Handler_read_first    | 0     |

    | Handler_read_key      | 16910 |

    | Handler_read_last     | 0     |

    | Handler_read_next     | 0     |

    | Handler_read_prev     | 0     |

    | Handler_read_rnd      | 0     |

    | Handler_read_rnd_next | 25380 |

    +-----------------------+-------+

    SQL优化有困难自然先想到了松华老师,在得知我用的MySQL 8.0之后,他帮忙给改造成了基于窗口函数的写法:

    select hostname_max , db_max,

    sum( case when ts_min>= date_sub(now(), interval 7 day)  then ts_cnt end ) as 1W,

    ifnull(sum(case when  ts_min>= date_sub(now(), interval 14 day)

    and ts_max<= date_sub(now(), interval 7 day) then ts_cnt end ) over(partition by hostname_max),0) 2W

    from global_query_review_history t2

    where ts_min>= date_sub(now(), interval 14 day)

    group by hostname_max, db_max

    order by 1W desc limit 20;

    再看下执行计划:

    *************************** 1. row ***************************

    id: 1

    select_type: SIMPLE

    table: t2

    partitions: NULL

    type: ALL

    possible_keys: ts_min

    key: NULL

    key_len: NULL

    ref: NULL

    rows: 25198

    filtered: 44.88

    Extra: Using where; Using temporary; Using filesort

    新SQL比较取巧,只需要读取一次数据,利用窗口函数直接计算出需要的统计值。虽然有可用索引,但因为要扫描的数据量比较大,所以最后还是变成全表扫描。新SQL耗时和status统计值见下:

    20 rows in set (0.08 sec)

    [root@yejr.run]> show status like 'handler%read%';

    +-----------------------+-------+

    | Variable_name         | Value |

    +-----------------------+-------+

    | Handler_read_first    | 0     |

    | Handler_read_key      | 24396 |

    | Handler_read_last     | 0     |

    | Handler_read_next     | 0     |

    | Handler_read_prev     | 0     |

    | Handler_read_rnd      | 886   |

    | Handler_read_rnd_next | 26703 |

    +-----------------------+-------+

    和之前那个SQL差距太大了,优化效果杠杠滴。

    全文完。

    Enjoy MySQL 8.0 :)

    延伸阅读

    扫码关注松华老师「深入SQL编程开发与优化」课程

    或者点击文末“阅读原文”直达

    展开全文
  • 问题描述最近在折腾把所有mysql slow query log写入到数据库中,再集中展示,向业务部门开放,也方便业务部门的同学自行查看并优化各自业务内的慢SQL。增加了定期生成报表的功能,统计最近1~2周内的慢查询数量变化...

    8.0的窗口函数真香

    1. 问题描述

    最近在折腾把所有mysql slow query log写入到数据库中,再集中展示,向业务部门开放,也方便业务部门的同学自行查看并优化各自业务内的慢SQL。增加了定期生成报表的功能,统计最近1~2周内的慢查询数量变化情况,给业务方同学更直观的数据对比,了解最近这段时间的慢查询数量变化情况,是多了还是少了。于是有了下面这一坨SQL:

    select hostname_max , db_max, sum(ts_cnt) as 1W
    (select ifnull(sum(t1.ts_cnt),0) as ts_cnt from global_query_review_history t1 where 
    t1.hostname_max=t2.hostname_max and t1.ts_min>= date_sub(now(), interval 14 day) and 
    t1.ts_max<= date_sub(now(), interval 7 day)) AS 2W 
    from global_query_review_history t2 where 
    ts_min>= date_sub(now(), interval 7 day) 
    group by hostname_max, db_max 
    order by 1W desc limit 20;

    当前 global_query_review_history 表约有2.5万条记录,这条SQL耗时 1.16秒,显然太慢了。下面是SQL执行计划:

    *************************** 1. row ***************************
               id: 1
      select_type: PRIMARY
            table: t2
       partitions: NULL
             type: ALL
    possible_keys: ts_min
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 25198
         filtered: 41.09
            Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
               id: 2
      select_type: DEPENDENT SUBQUERY
            table: t1
       partitions: NULL
             type: ref
    possible_keys: hostname_max,ts_min
              key: hostname_max
          key_len: 258
              ref: func
             rows: 20
         filtered: 14.90
            Extra: Using where

    可以看到需要进行一次子查询(无法自动优化成JOIN)。

    SQL执行后的status统计值:

    +-----------------------+--------+
    | Variable_name         | Value  |
    +-----------------------+--------+
    | Handler_read_first    | 0      |
    | Handler_read_key      | 17328  |
    | Handler_read_last     | 0      |
    | Handler_read_next     | 809121 |
    | Handler_read_prev     | 0      |
    | Handler_read_rnd      | 0      |
    | Handler_read_rnd_next | 25380  |
    +-----------------------+--------+

    可以看到除了有全表扫描外,还要根据索引的多次逐行扫描(Handler_read_next = 809121,子查询引起的)。

    2. SQL优化

    上面的SQL主要瓶颈在于嵌套子查询,去掉子查询,即便是全表扫描也还是很快的。

    [root@yejr.run]> select ...
    ...
    20 rows in set (0.08 sec)

    [root@yejr.run]> show status like 'handler%read%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Handler_read_first    | 0     |
    | Handler_read_key      | 16910 |
    | Handler_read_last     | 0     |
    | Handler_read_next     | 0     |
    | Handler_read_prev     | 0     |
    | Handler_read_rnd      | 0     |
    | Handler_read_rnd_next | 25380 |
    +-----------------------+-------+

    SQL优化有困难自然先想到了松华老师,在得知我用的MySQL 8.0之后,他帮忙给改造成了基于窗口函数的写法:

    select hostname_max , db_max,
    sum( case when ts_min>= date_sub(now(), interval 7 day)  then ts_cnt end ) as 1W,
    ifnull(sum(case when  ts_min>= date_sub(now(), interval 14 day)
       and ts_max<= date_sub(now(), interval 7 day) then ts_cnt end ) over(partition by hostname_max),0) 2W
    from global_query_review_history t2
     where ts_min>= date_sub(now(), interval 14 day)
    group by hostname_max, db_max
    order by 1W desc limit 20;

    再看下执行计划:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t2
       partitions: NULL
             type: ALL
    possible_keys: ts_min
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 25198
         filtered: 44.88
            Extra: Using where; Using temporary; Using filesort

    新SQL比较取巧,只需要读取一次数据,利用窗口函数直接计算出需要的统计值。虽然有可用索引,但因为要扫描的数据量比较大,所以最后还是变成全表扫描。新SQL耗时和status统计值见下:

    20 rows in set (0.08 sec)

    [root@yejr.run]> show status like 'handler%read%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Handler_read_first    | 0     |
    | Handler_read_key      | 24396 |
    | Handler_read_last     | 0     |
    | Handler_read_next     | 0     |
    | Handler_read_prev     | 0     |
    | Handler_read_rnd      | 886   |
    | Handler_read_rnd_next | 26703 |
    +-----------------------+-------+

    和之前那个SQL差距太大了,优化效果杠杠滴。

    全文完。

    Enjoy MySQL 8.0 :)

    延伸阅读

    • MySQL 8.0中对EXISTS、NOT EXISTS的持续优化
    • MySQL 8 Anti-Join 几点总结
    • SQL优化很难怎么办?给你一个简单暴力的办法
    • SQL 优化案例一则
    • MySQL Join竟然可以这么玩?根据条件进行复制
    扫码关注松华老师「深入SQL编程开发与优化」课程

    4f6dd0af771b328a763136f195f30d07.png

    或者点击文末“阅读原文”直达

    展开全文
  • 文章目录1 HiveSQL核心技能1-常用函数1.1 基础语法① ... if④ 字符串函数⑤ 聚合统计函数1.3 重点练习1.4 常见错误及处理办法2 HiveSQL核心技能2-表连接2.1 inner join2.2 left join & right join 1 HiveSQ.


    本博文中HQL将用于shell模板中的sql语句替换:【数据库笔记】hive查询结果导出到本地
    同理也有Mysql的shell模板:【DA】shell脚本+SQL应用

    1 HiveSQL核心技能1-常用函数

    1. 掌握HIVE基础语法、常用函数及其组合使用
    2. 掌握一些基本业务指标的分析思路与实现技巧

    1.1 基础语法

    ① SELECT …A… FROM …B… WHERE …C…

    • A:列名
    • B:表名
    • C:筛选条件

    在这里插入图片描述

    SELECT user_name 
    FROM user_info 
    WHERE city='beijing' and sex='female'
    limit 10;
    

    注意:如果该表是一个分区表,则WHERE条件中必须对分区字段进行限制

    显示表分区:
    hive> show partitions table_name;
    
    根据表分区查询数据:
    hive> select * from table_name where partition_date='2018-04-10' ; 
    

    ② GROUP BY(分类汇总)

    在这里插入图片描述

    select  goods_category as category,
    		count(distinct buyer_id) as user_num,
    		sum(pay_amount) as total_amount
    from user_trade
    where sale_date between '2019-01-01' and '2019-03-31'
    group by goods_category
    

    在这里插入图片描述
    GROUP BY …… HAVING
    在这里插入图片描述

    SELECT user_name,
           sum(pay_amount) as total_amount
    FROM user_trade 
    WHERE year(dt)=2019 and month(dt)=4
    GROUP BY user_name 
    HAVING sum(pay_amount)>50000;
    

    ③ ORDER BY

    一开始在终端写:

    SELECT user_name,
           sum(pay_amount) as total_amount
    FROM user_trade 
    WHERE year(dt)=2019 and month(dt)=4
    GROUP BY user_name 
    HAVING sum(pay_amount)>50000
    order by total_amount desc;
    

    报错:
    在这里插入图片描述

    SELECT user_name,
           sum(pay_amount) as total_amount
    FROM user_trade 
    WHERE year(dt)=2019 and month(dt)=4
    GROUP BY user_name 
    HAVING sum(pay_amount)>50000
    order by total_amount desc
    limit 5

    在这里插入图片描述

    ④ HiveSQL执行顺序

    FROMWHEREGROUP BYHAVINGSELECTORDER BY
    

    在这里插入图片描述

    1.2 常用函数

    查看Hive中的函数:

    show functions;
    

    查看具体函数的用法:

    desc function 函数名;
    desc function extended 函数名;
    

    在这里插入图片描述
    在这里插入图片描述

    ① 时间戳转化为日期

    SELECT pay_time,
           from_unixtime(pay_time,'yyyy-MM-dd hh:mm:ss'),
           from_unixtime(pay_time,'yyyy-MM'),
           from_unixtime(pay_time,'hh')
    FROM user_trade 
    WHERE dt='2019-04-09';
    

    在这里插入图片描述

    注意:月份MM是大写的,这里大小写不通用

    from_unixtime(bigint unixtime, string format):将时间戳转化为指定格式的日期

    1. yyyy-MM-dd hh:mm:ss
    2. yyyy-MM-dd hh
    3. yyyy-MM-dd hh:mm
    4. yyyyMMdd

    拓展把日期转化为时间戳——unix_timestamp(string date)

    ② 计算日期间隔

    在这里插入图片描述

    SELECT user_name,
           datediff('2019-05-01',to_date(firstactivetime))
    FROM user_info 
    limit 10;
    

    在这里插入图片描述
    datediff(string enddate, string startdate):结束日期减去开始日期的天数

    拓展:日期增加函数、减少函数——date_adddate_sub(类型要是string类型的)

    • date_add(string startdate, int days)
    • date_sub (string startdate, int days)
    date_add("2019-01-01",10)
    

    在这里插入图片描述

    只能写10,不能写interval 10 day(s) 或者 10 day(s),这里和mysql不同
    在这里插入图片描述

    ③ 条件函数 case & if

    在这里插入图片描述

    SELECT case when age<20 then '20岁以下'
               	when age>=20 and age<30 then '20-30岁'
                when age>=30 and age<40 then '30-40岁'
                else '40岁以上' end as age_type,
           count(distinct user_id) user_num
    FROM user_info 
    GROUP BY case when age<20 then '20岁以下'
                  when age>=20 and age<30 then '20-30岁'
    	          when age>=30 and age<40 then '30-40岁'
    	          else '40岁以上' end;
    

    end as age_type 不能写成 ‘age_type’

    在这里插入图片描述

    SELECT sex,
           if(level>5,'高','低') as level_type,
           count(distinct user_id) user_num
    FROM user_info 
    GROUP BY sex,
             if(level>5,'高','低');
    

    在这里插入图片描述

    注意,select中的if(level>5,'高','低')必须和group by中一致,不能混合用casewhen和if

    ④ 字符串函数

    在这里插入图片描述

    SELECT substr(firstactivetime,1,7) as month,
           count(distinct user_id) user_num
    FROM user_info 
    GROUP BY substr(firstactivetime,1,7);
    

    在这里插入图片描述
    substr(string A, int start, int len):如果不指定截取长度,则从起始位一直截取到最后。

    在这里插入图片描述

    ##第一种情况
    SELECT get_json_object(extra1, '$.phonebrand') as phone_brand,
           count(distinct user_id) user_num
    FROM user_info 
    GROUP BY get_json_object(extra1, '$.phonebrand');
    
    ##第二种情况
    SELECT extra2['phonebrand'] as phone_brand,
           count(distinct user_id) user_num
    FROM user_info 
    GROUP BY extra2['phonebrand'];
    

    在这里插入图片描述
    get_json_object(string json_string, string path)

    • param1:需要解析的json字段
    • param2:用$.key取出想要获取的value
      在这里插入图片描述

    ⑤ 聚合统计函数

    在这里插入图片描述

    SELECT avg(pay_amount) as avg_amount,
           datediff(max(from_unixtime(pay_time,'yyyy-MM-dd')),min(from_unixtime(pay_time,'yyyy-MM-dd')))
    FROM user_trade 
    WHERE year(dt)='2018' and user_name='ELLA';
    
    max(from_unixtime(pay_time,'yyyy-MM-dd'))= from_unixtime(max(pay_time),'yyyy-MM-dd'))
    

    1.3 重点练习

    在这里插入图片描述
    hive需要写成两层

    SELECT count(a.user_name)
    FROM
       (SELECT user_name,
               count(distinct goods_category) as category_num
        FROM user_trade 
        WHERE year(dt)='2018'
        GROUP BY user_name 
        HAVING count(distinct goods_category)>2) a;
    

    mysql仅需一层:

    SELECT count(user_name),count(distinct goods_category)
    FROM user_trade 
    WHERE year(dt)='2018'
    GROUP BY user_name 
    HAVING count(distinct goods_category)>2;
    

    在hive中如此写会报错,也就是hive要求having里出现的字段,也需要出现在同层的select
    在这里插入图片描述
    在这里插入图片描述

    select case when age>=20 and age<30 then '20-30'
                when age>=30 and age<40 then '30-40'
                end as age_bins,
                marr,
                count(distinct user_id) as num
    from
           (
              select user_id,
                     age,
                     get_json_object(extra1,'$.marriage_status') as marr
              from user_info
              where substring(firstactivetime,1,4)='2018' and age>=20 and age<40
           ) t
    group by case when age>=20 and age<30 then '20-30'
                  when age>=30 and age<40 then '30-40'
                  end,marr;
    

    在这里插入图片描述

    SELECT a.age_type,
           if(a.marriage_status=1,'已婚','未婚'),
           count(distinct a.user_id)
    FROM
       (SELECT case when age<20 then '20岁以下'
                   when age>=20 and age<30 then '20-30岁'
                   when age>=30 and age<40 then '30-40岁'
                   else '40岁以上' end  as age_type,
               get_json_object(extra1, '$.marriage_status') as
    marriage_status,
               user_id
        FROM user_info 
        WHERE to_date(firstactivetime) between '2018-01-01'
    and '2018-12-31') a 
    WHERE a.age_type in ('20-30岁','30-40岁')
    GROUP BY a.age_type,
             if(a.marriage_status=1,'已婚','未婚');
    

    在这里插入图片描述

    1.4 常见错误及处理办法

    1. 可以先在Notepad++中编辑好sql,Ctrl+A,Ctrl+C复制,进入Xshell,Shift+Insert粘贴代码。非常需要注意的是,在Notepad++中只能使用空格键排版,切记不能用TAB,不然粘贴过去的代码不能运行,并且最后hsql以;结尾。

    2. hsql和mysql的一点不同:在having的使用上,hsql强制在having中出现的计算字段必须出现在同层的select语句中,详见 1.3 重点练习

    3. 如果查询表是一个分区表,则WHERE条件中必须对分区字段进行限制,详见 1.1 ①

    4. hsql的order by一定要和limit联用,详见 1.1 ③

    5. 月份是MM,详见1.2 ①

    6. date_add(string startdate, int days)date_sub中最后一个参数只接受数字,不用整interval和day,这也是hsql和mysql的不同之处,详见1.2 ②

    7. hsql中使用case when的结尾 end as age_type 不能写成 ‘age_type’,不同于mysql,详见1.2 ③

    8. hsql中select中的if(level>5,'高','低')必须和group by中一致,不能混合用casewhen和if,详见1.2 ③

    9. 标点符号错误
      在这里插入图片描述

    10. 没有对子查询的表进行重命名
      在这里插入图片描述

    11. 使用错误的字段名

    12. 丢了逗号分隔符

    2 HiveSQL核心技能2-表连接

    • 掌握HQL中的各种连接及其组合使用
    • 掌握数据分析中业务指标思路转换的技巧

    2.1 基础语法

    ① inner join

    在这里插入图片描述

    • 表连接时,必须进行重命名
    • on后面使用的连接条件必须起到唯一键值的作用
    • inner可省略不写,效果一样

    在这里插入图片描述
    解法1

    select distinct t.user_name
    from 
        trade_2019 t
    inner join
        (
            select distinct user_name
            from user_refund
            where year(dt)=2019
        ) u
    on t.user_name=u.user_name;
    

    解法2

    SELECT a.user_name
    FROM
        (SELECT distinct user_name
         FROM user_trade
         WHERE year(dt)=2019)a
    JOIN
        (SELECT distinct user_name
        FROM user_refund
        WHERE year(dt)=2019)b 
    on a.user_name=b.user_name;
    

    在这里插入图片描述
    在这里插入图片描述
    解法1

    select distinct a1.user_name
    from trade_2017 a1,trade_2018 a2
    where a1.user_name=a2.user_name;
    

    解法2

    select a1.user_name
    from
        (
            select distinct user_name
            from user_trade
            where year(dt)=2017
        ) a1
    inner join
        (
            select distinct user_name
            from user_trade
            where year(dt)=2018
        ) a2
    on a1.user_name=a2.user_name;
    

    在这里插入图片描述
    解法一

    select distinct a1.user_name
    from trade_2017 a1,trade_2018 a2,trade_2018 a3
    where a1.user_name=a2.user_name 
    and a2.user_name=a3.user_name
    

    解法二,最后一行也可以是 JOIN trade_2019 c on a.user_name=c.user_name;

    SELECT distinct a.user_name
    FROM trade_2017 a 
    JOIN trade_2018 b on a.user_name=b.user_name
    JOIN trade_2019 c on b.user_name=c.user_name;
    

    解法三,最后一行也可以是 )c on b.user_name=c.user_name;

    SELECT a.user_name
    FROM
        (
            SELECT distinct user_name
            FROM trade_2017
        )a
    JOIN
        (
             SELECT distinct user_name
            FROM trade_2018
        )b on a.user_name=b.user_name
    JOIN
        (
            SELECT distinct user_name
            FROM trade_2019
        )c on a.user_name=c.user_name;
    

    在表的数据量级很大时,推荐第三种写法,为什么呢? 先distinct缩小表,减少工作量

    ② left join & right join

    进行左连接后,以左边的表1为全集,返回能够匹配上的右边表2的匹配结果,没有匹配上的则显示NULL,如果左表的键值可以在右表中找到多个对应,那么会多出几行

    在这里插入图片描述
    在这里插入图片描述

    SELECT a.user_id,
           a.user_name
    FROM user_list_1 a
    LEFT JOIN user_list_2 b
    ON a.user_id=b.user_id
    WHERE b.user_id is null;
    

    在这里插入图片描述
    解法一

    select t.user_name
    from 
        (
            select distinct user_name
            from trade_2019
        ) t 
    left join
        (
            select distinct user_name
            from user_refund
            where year(dt)=2019
        ) u 
    on t.user_name=u.user_name
    where u.user_name is null;
    

    解法二

    SELECT a.user_name 
    FROM
        (SELECT distinct user_name
        FROM user_trade
        WHERE year(dt)=2019)a
    LEFT JOIN
        (SELECT distinct user_name
        FROM user_refund
        WHERE year(dt)=2019)b on a.user_name=b.user_name
    WHERE b.user_name is null;
    

    在这里插入图片描述
    解法一

    select u.master,
           count(distinct u.user_name) as num
    from 
        (
            select distinct user_name
            from trade_2019
        ) t 
    inner join 
        (
            select user_name,
                   get_json_object(extra1,'$.education') as master
            from user_info
        ) u 
    on t.user_name=u.user_name
    group by u.master;
    

    在这里插入图片描述

    解法二

    SELECT b.education,
            count(distinct a.user_name)
    FROM
            (SELECT distinct user_name
            FROM user_trade
            WHERE year(dt)=2019)a
    LEFT JOIN
            (SELECT user_name,
                    get_json_object(extra1, '$.education') as education
            FROM user_info)b 
            on a.user_name=b.user_name
    GROUP BY b.education;
    

    在这里插入图片描述

    注意:get_json_object(extra1, '$.education')可以换成extra2['education']

    在这里插入图片描述
    解法一

    select t1.user_name
    from
        (
            select t1.user_name
            from 
                (select distinct user_name from trade_2017) t1,
                (select distinct user_name from trade_2018) t2
            where t1.user_name=t2.user_name
        ) t1
    left join 
        (
            select distinct user_name from trade_2019
        ) t2
    on t1.user_name=t2.user_name
    where t2.user_name is null;
    

    在这里插入图片描述

    解法一需要加distinct,如果表比较小的时候,加不加distinct影响不大。但是有分区的大表,不加distinct写执行速度很慢。

    解法二

    SELECT a.user_name
    FROM
        (SELECT distinct user_name
        FROM trade_2017)a
    JOIN
        (SELECT distinct user_name
        FROM trade_2018)b on a.user_name=b.user_name
    LEFT JOIN
        (SELECT distinct user_name
        FROM trade_2019)c on b.user_name=c.user_name
    WHERE c.user_name is null;
    

    在这里插入图片描述

    ③ full join

    SELECT *
    FROM user_list_1 a 
    FULL JOIN user_list_2 b 
    ON a.user_id=b.user_id;
    

    在这里插入图片描述
    在这里插入图片描述

    SELECT coalesce(a.user_name,b.user_name)
    FROM user_list_1 a 
    FULL JOIN user_list_2 b 
    on a.user_id=b.user_id;
    

    在这里插入图片描述
    coalesce (expression_1, expression_2, ...,expression_n)依次参考各参数表达式,遇到非null值即停止并返回该值。如果所有的表达式都是空值,最终将返回一个空值。

    ④ union all

    select user_id,
           user_name
    from user_list_1
    union all
    select user_id,
           user_name
    from user_list_3;
    

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    SELECT count(distinct a.user_name),
           count(a.user_name)
    FROM
        (
            SELECT user_name
            FROM trade_2017
            UNION ALL
            SELECT user_name
            FROM trade_2018
            UNION ALL
            SELECT user_name
            FROM trade_2019)a;
    

    在这里插入图片描述

    SELECT count(distinct a.user_name),
           count(a.user_name)
    FROM
        (
            SELECT user_name
            FROM trade_2017
            UNION
            SELECT user_name
            FROM trade_2018
            UNION
            SELECT user_name
            FROM trade_2019)a;
    

    在这里插入图片描述
    UNION ALL和UNION的区别:
    在这里插入图片描述
    在这里插入图片描述
    解法一

    select  user_name,
            sum(pay_amount),
            sum(refund_amount)
    from
       (
            select user_name,
                   sum(amount) as pay_amount,
                   0 as refund_amount
            from trade_2019
            group by user_name
            
            union all
        
            select  user_name,
                    0 as pay_amount,
                    sum(refund_amount) as refund_amount
            from user_refund
            where year(dt)=2019
            group by user_name
            
        ) u
    group by user_name;
    

    在这里插入图片描述

    解法二:full join

    select coalesce(u.user_name,r.user_name) as user_name,
           u.pay_amount,
           r.refund_amount
    from
        (
            select  user_name,
                    sum(amount) as pay_amount
            from trade_2019
            group by user_name
        ) u 
    full join
        (
            select  user_name,
                    sum(refund_amount) as refund_amount
            from user_refund
            where year(dt)=2019
            group by user_name
        ) r 
    on u.user_name=r.user_name;
    

    在这里插入图片描述

    在这里插入图片描述
    如何把NULL都变成0呢?

    select coalesce(u.user_name,r.user_name) as user_name,
           if(u.pay_amount is null,0,u.pay_amount),
           if(r.refund_amount is null,0,r.refund_amount)
    from
        (
            select  user_name,
                    sum(amount) as pay_amount
            from trade_2019
            group by user_name
        ) u 
    full join
        (
            select  user_name,
                    sum(refund_amount) as refund_amount
            from user_refund
            where year(dt)=2019
            group by user_name
        ) r 
    on u.user_name=r.user_name;
    

    在这里插入图片描述
    在这里插入图片描述

    select u.user_name,
           u.pay_amount,
           r.refund_amount
    from
        (
            select  user_name,
                    sum(amount) as pay_amount
            from trade_2019
            group by user_name
        ) u 
    left join
        (
            select  user_name,
                    sum(refund_amount) as refund_amount
            from user_refund
            where year(dt)=2019
            group by user_name
        ) r 
    on u.user_name=r.user_name;
    

    在这里插入图片描述

    2.2 重点练习

    在这里插入图片描述
    解法一

    select case when u.age<20 then '20以下'
                when u.age>=20 and u.age<30 then '20-30岁'
                when u.age>=30 and u.age<40 then '30-40岁'
                else '40以上'
                end as age_bins,
            count(distinct u.user_name) as num
    from
       (
            select user_name,
                   avg(age) as age
            from user_info
            where substring(firstactivetime,1,4)='2017'
            group by user_name
        ) u
    left join
        (
            select user_name
            from user_trade
            where year(dt)>=2017
            group by user_name
        ) t
    on u.user_name=t.user_name
    where t.user_name is null
    group by case when u.age<20 then '20以下'
                  when u.age>=20 and u.age<30 then '20-30岁'
                  when u.age>=30 and u.age<40 then '30-40岁'
                  else '40以上'
                  end;
    

    在这里插入图片描述

    解法二

    SELECT a.age_level,
           count(a.user_name)
    FROM
            (SELECT user_name,
                    case when age<20 then '20岁以下'
                         when age>=20 and age<30 then '20-30岁'
                         when age>=30 and age<40 then '30-40岁'
                         else '40岁以上' end as age_level
            FROM user_info
            WHERE year(firstactivetime)=2017)a
    LEFT JOIN
            (SELECT distinct user_name
            FROM user_trade
            WHERE dt>'0')b 
    on a.user_name=b.user_name
    WHERE b.user_name is null
    GROUP BY a.age_level;
    

    在这里插入图片描述

    在这里插入图片描述

    select  year(firstactivetime),
            month(firstactivetime),
            day(firstactivetime),
            hour(firstactivetime),
            minute(firstactivetime),
            second(firstactivetime)
    from user_info
    limit 5;
    

    在这里插入图片描述

    select  u1.h,
            count(u1.user_name) as num
    from 
        (
            select user_name,
                   hour(firstactivetime) as h
            from user_info
        ) u1
    inner join
        (
            select user_name
            from user_trade
            where year(dt)=2018 or year(dt)=2019
            group by user_name
        ) u2
    on u1.user_name=u2.user_name
    group by u1.h;
    

    在这里插入图片描述

    3 HiveSQL核心技能3-窗口函数

    1. 掌握sum()、avg()用于累计计算的函数
    2. 掌握row_number()、rank()用于排序的函数
    3. 掌握ntile()用于分组查询的函数
    4. 掌握lag()、lead()偏移分析函数

    3.1 累计计算窗口函数

    ① sum(…) over(……)

    大家在做报表的时候,经常会遇到计算截止某月的累计数值,通常在EXCEL里可以通过函数来实现。Excel怎么累计求和

    在这里插入图片描述

    那么在HiveSQL里,该如何实现这种累计数值的计算呢?利用窗口函数!

    关于窗口函数的几点说明

    1. Window Function又称为窗口函数、分析函数。
    2. 窗口函数与聚合函数类似,但是它是每一行数据都生成一个结果。
    3. 聚合函数(比如sum、avg、max等)可以将多行数据按照规定聚合为一行,一般来讲聚集后的行数要少于聚集前的行数。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,便引入了窗口函数。
    4. 窗口函数是在select时执行的,位于order by之前

    在这里插入图片描述

    select mon,
           pay_amount,
           sum(pay_amount) over(order by mon) as pay_amount
    from
        (
            select month(dt) as mon,
                   sum(pay_amount) as pay_amount
            from user_trade
            where year(dt)=2018
            group by month(dt)
        ) a;
    

    在这里插入图片描述
    在这里插入图片描述

    select y,m,
           pay_amount,
           sum(pay_amount) over (partition by y order by m) as total_pay
    from
        (
            select year(dt) as y,
                   month(dt) as m,
                   sum(pay_amount) as pay_amount
            from user_trade
            where year(dt) in (2017,2018)
            group by year(dt),month(dt)
        ) u;
    

    在这里插入图片描述
    在这里插入图片描述

    • over中的partition by起到了分组的作用
    • order by 按照什么顺序进行累加,升序ASC、降序DESC,默认升序

    ② avg(…) over(……) ☆☆☆☆☆☆

    大家看股票的时候,经常会看到这种K线图吧,里面经常用到的就是7日、30日移动平均的趋势图,那如何使用窗口函数来计算移动平均值呢?

    在这里插入图片描述

    select mon,
           pay_amount,
           avg(pay_amount) over(order by mon rows between 2 preceding and current row) as every3monAVG
    from
       (
            select month(dt) as mon,
                   sum(pay_amount) as pay_amount
            from user_trade
            where year(dt)=2018
            group by month(dt)
        ) a;
    

    我们用rows between 2 preceding and current row来限制计算移动平均的范围,本语句含义是包含本行及前两行,这个就是我们题目中要求的近三月的写法

    ③ 语法总结

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    3.2 分区排序窗口函数

    row_number() 、rank()、dense_rank()

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    select user_name,
           count(distinct goods_category) as goods_num,
           row_number() over (order by count(distinct goods_category)) as row_number,
           rank() over (order by count(distinct goods_category)) as rank,
           dense_rank() over (order by count(distinct goods_category)) as dense_rank
    from user_trade
    where year(dt)=2019 and month(dt)=1
    group by user_name;
    

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    解法一

    select user_name
    from
        (
            select user_name,
                   dense_rank() over (order by pay_amount desc) as ranking
            from 
                (
                    select user_name,
                           sum(pay_amount) as pay_amount
                    from user_trade
                    where year(dt)=2019
                    group by user_name
                ) b
        )a
    where ranking in (10,20,30);
    

    在这里插入图片描述

    解法二

    select user_name,
           pay_amount,
           ranking
    from 
        (
            select user_name,
                   sum(pay_amount) as  pay_amount,
                   dense_rank() over(order by sum(pay_amount) desc) as ranking
            from user_trade
            where year(dt)=2019
            group by user_name
        ) t
    where ranking in (10,20,30);
    

    在这里插入图片描述

    3.3 切片排序窗口函数

    ntile(n) over(……)☆☆☆☆☆☆

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    select user_name,
           sum(pay_amount) as pay_amount,
           ntile(5) over(order by sum(pay_amount) desc) as level
    from user_trade
    where year(dt)=2019 and month(dt)=1
    group by user_name;
    

    在这里插入图片描述
    在这里插入图片描述

    select user_name,
           refund_amount,
           level
    from
        (
            select user_name,
                   sum(refund_amount) as refund_amount,
                   ntile(10) over(order by sum(refund_amount) desc) as level
            from user_refund
            where year(dt)=2019
            group by user_name
        ) a
    where level=1;
    

    在这里插入图片描述

    3.4 偏移分析窗口函数

    lag,lead ☆☆☆☆☆☆

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    select user_name,
           dt,
           lag(dt,1,'None') over(partition by user_name order by dt),
           lag(dt) over(partition by user_name order by dt),
           lag(dt,2,'None') over(partition by user_name order by dt),
           lag(dt,2) over(partition by user_name order by dt)
    from user_trade
    where dt is not null
    and user_name in ('Alice','Alexander');
    

    在这里插入图片描述

    user_name dt lag_window_0 lag_window_1 lag_window_2 lag_window_3
    Alexander 2017-04-08 None NULL None NULL
    Alexander 2017-12-02 2017-04-08 2017-04-08 None NULL
    Alexander 2017-12-02 2017-12-02 2017-12-02 2017-04-08 2017-04-08
    Alexander 2018-02-03 2017-12-02 2017-12-02 2017-12-02 2017-12-02
    Alice 2017-12-11 None NULL None NULL
    Alice 2018-07-01 2017-12-11 2017-12-11 None NULL
    Alice 2018-07-01 2018-07-01 2018-07-01 2017-12-11 2017-12-11
    Alice 2018-08-15 2018-07-01 2018-07-01 2018-07-01 2018-07-01

    在这里插入图片描述

    select user_name,
           dt,
           lead(dt,1,'None') over(partition by user_name order by dt),
           lead(dt) over(partition by user_name order by dt),
           lead(dt,2,'None') over(partition by user_name order by dt),
           lead(dt,2) over(partition by user_name order by dt)
    from user_trade
    where dt is not null
    and user_name in ('Alice','Alexander');
    
    user_name dt lead_window_0 lead_window_1 lead_window_2 lead_window_3
    Alexander 2017-04-08 2017-12-02 2017-12-02 2017-12-02 2017-12-02
    Alexander 2017-12-02 2017-12-02 2017-12-02 2018-02-03 2018-02-03
    Alexander 2017-12-02 2018-02-03 2018-02-03 None NULL
    Alexander 2018-02-03 None NULL None NULL
    Alice 2017-12-11 2018-07-01 2018-07-01 2018-07-01 2018-07-01
    Alice 2018-07-01 2018-07-01 2018-07-01 2018-08-15 2018-08-15
    Alice 2018-07-01 2018-08-15 2018-08-15 None NULL
    Alice 2018-08-15 None NULL None NULL

    在这里插入图片描述

    select distinct user_name
    from 
        (
            select user_name,
                   dt as first_pay,
                   lead(dt,1) over(partition by user_name order by dt) as next_pay  
            from user_trade
            where dt is not null
        ) a
    where datediff(next_pay,first_pay)>100;
    

    在这里插入图片描述

    3.5 重点练习

    在这里插入图片描述

    select city,
           sex,
           user_name,
           pay_amount,
           ranking
    from
        (
            select u2.city,
                   u2.sex,
                   u1.user_name,
                   u1.pay_amount,
                   dense_rank() over(partition by u2.city,u2.sex order by u1.pay_amount desc) as ranking
            from
                (
                    select user_name,
                           sum(pay_amount) as pay_amount
                    from user_trade
                    where year(dt)=2018
                    group by user_name
                ) u1
            left join 
                (
                    select user_name,
                           sex,
                           city
                    from user_info
                ) u2
            on u1.user_name=u2.user_name
        ) a
    where ranking<=3;
    

    在这里插入图片描述

    在这里插入图片描述

    select phonebrand,
           user_name,
           refund_amount,
           level
    from  
        (
            select r.user_name,
                   u.phonebrand,
                   r.refund_amount,
                   ntile(4) over(partition by u.phonebrand order by r.refund_amount desc) as level
            from
                (
                    select user_name,
                           sum(refund_amount) as refund_amount
                    from user_refund
                    where dt is not null
                    group by user_name
                ) r
            left join
                (
                    select distinct user_name,
                           get_json_object(extra1,'$.phonebrand') as phonebrand
                    from user_info
                ) u
            on r.user_name=u.user_name
        ) t
    where level=1;
    

    在这里插入图片描述

    4 常用优化技巧

    1. 学会利用各种技巧提升Hive执行效率
    2. 学会思路变换,多方法解决问题

    Hive作为大数据领域常用的数据仓库组件,在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大,而是数据倾斜、数据冗余、job或I/O过多、MapReduce分配不合理等等。

    对Hive的调优既包含对HiveSQL语句本身的优化,也包含Hive配置项和MR方面的调整。

    4.1 列裁剪和分区裁剪

    1. 列裁剪:在查询时只读取需要的列
      当列很多或者数据量很大时,如果select 所有的列或者不指定分区,导致的全列扫描和全表扫描效率都很低。Hive中与列裁剪优化相关的配置项是hive.optimize.cp,默认是true
    2. 分区裁剪:在查询时只读需要的分区。Hive中与分区裁剪优化相关的则是hive.optimize.pruner,默认是true

    4.2 排序技巧:sort by代替order by

    HiveSQL中的order by与其他SQL语言中的功能一样,就是将结果按某个字段**全局排序,**这会导致所有map端数据都进入一个reduce中,在数据量大时可能会长时间计算不完

    如果使用sort by,那么就会视情况启动多个reducer进行排序,并且保证每个reducer内局部有序。为了控制map端数据分配到reducekey,往往还要配合distribute by一同使用。

    如果不加distribute by的话,map端数据就会随机分配给reducer

    hive中的distribute by

    --未优化写法
    select a,b,c
    from table
    where xxx
    order by a
    limit 10;
    
    --优化写法
    select a,b,c
    from table
    where xxx
    distribute by user_id
    sort by a
    limit 10;
    

    4.3 去重技巧:用group by来替换distinct

    在这里插入图片描述

    -- 取出user_trade表中全部支付用户
    ##原有写法
    SELECT distinct user_name
    FROM user_trade
    WHERE dt>'0';
    
    ##优化写法
    SELECT user_name
    FROM user_trade
    WHERE dt>'0'
    GROUP BY user_name;
    

    在这里插入图片描述
    注意:在极大的数据量(且很多重复值)时,可以先group by去重,再count()计数,效率高于直接count(distinct **)

    4.4 聚合技巧:grouping sets、cube、rollup

    grouping sets

    在这里插入图片描述
    通常写法:

    --性别分布--
    SELECT sex,
           count(distinct user_id)
    FROM user_info
    GROUP BY sex;
    
    --城市分布--
    SELECT city,
           count(distinct user_id)
    FROM user_info
    GROUP BY city;
    
    --等级分布--
    SELECT level,
           count(distinct user_id)
    FROM user_info
    GROUP BY level;
    

    缺点:要分别写三次SQL,需要执行三次,重复工作,且费时

    优化写法:

    -- 性别、城市、等级用户分布
    SELECT sex,
           city,
           level,
           count(distinct user_id)
    FROM user_info
    GROUP BY sex,city,level
    GROUPING SETS (sex,city,level);
    

    在这里插入图片描述
    在这里插入图片描述

    --性别分布--
    SELECT sex,
           count(distinct user_id)
    FROM user_info
    GROUP BY sex;
    
    --每个性别的城市分布--
    SELECT sex,
           city,
           count(distinct user_id)
    FROM user_info
    GROUP BY sex,
             city;
    

    优化写法:

    SELECT sex,
           city,
           count(distinct user_id)
    FROM user_info
    GROUP BY sex,city
    GROUPING SETS (sex,(sex,city));
    

    在这里插入图片描述
    注意:第二列为NULL的,即是性别的用户分布,其余有城市的均为每个性别的城市分布

    cube

    cube:根据group by维度的所有组合进行聚合

    -- 性别、城市、等级的各种组合的用户分布
    SELECT sex,
           city,
           level,
           count(distinct user_id)
    FROM user_info
    GROUP BY sex,city,level
    GROUPING SETS (sex,city,level,(sex,city),(sex,level),
    (city,level),(sex,city,level));
    
    -- 优化写法
    --性别、城市、等级的各种组合的用户分布--
    SELECT sex,
           city,
           level,
           count(distinct user_id)
    FROM user_info
    GROUP BY sex,city,level
    with cube;
    

    注意:跑完数据后,整理很关键!!

    rollup

    rollup:以最左侧的维度为主,进行层级聚合,是cube的子集。
    在这里插入图片描述

    SELECT a.dt,
           sum(a.year_amount),
           sum(a.month_amount)
    FROM
        (SELECT substr(dt,1,4) as dt,
                sum(pay_amount) year_amount,
                0 as month_amount
        FROM user_trade
        WHERE dt>'0'
        GROUP BY substr(dt,1,4)
        UNION ALL 
        SELECT substr(dt,1,7) as dt,
                0 as year_amount,
                sum(pay_amount) as month_amount
        FROM user_trade
        WHERE dt>'0'
        GROUP BY substr(dt,1,7)
        )a
    GROUP BY a.dt;
    

    在这里插入图片描述

    优化写法:

    -- 优化写法
    SELECT year(dt) as year,
           month(dt) as month,
           sum(pay_amount) 
    FROM user_trade
    WHERE dt>'0'
    GROUP BY year(dt),
             month(dt)
    with rollup;
    

    在这里插入图片描述

    4.5 换个思路解题

    条条大路通罗马,写SQL亦是如此,能达到同样效果的SQL有很多种,要学会思路转换,灵活应用。

    解法一

    --在2017年和2018年都购买的用户--
    SELECT a.user_name  
    FROM
        (SELECT distinct user_name
        FROM user_trade
        WHERE year(dt)=2017)a
        JOIN
        (SELECT distinct user_name
        FROM user_trade
        WHERE year(dt)=2018)b on a.user_name=b.user_name;
    

    解法二

    SELECT a.user_name
    FROM
        (SELECT user_name,
                count(distinct year(dt)) as year_num
        FROM user_trade
        WHERE year(dt) in (2017,2018)
        GROUP BY user_name)a 
    WHERE a.year_num=2;
    
    
    SELECT user_name,
           count(distinct year(dt)) as year_num
    FROM user_trade
    WHERE year(dt) in (2017,2018)
    GROUP BY user_name 
    having count(distinct year(dt))=2;
    

    4.6 union all时可以开启并发执行

    Hive中互相没有依赖关系的job间是可以并行执行的,最典型的就是多个子查询union all在集群资源相对充足的情况下,可以开启并发执行。参数设置:set hive.exec.parallel=true;

    --每个用户的支付和退款金额汇总--
    SELECT a.user_name,
           sum(a.pay_amount),
           sum(a.refund_amount)
    FROM
        (
            SELECT user_name,
                   sum(pay_amount) as pay_amount,
                   0 as refund_amount
            FROM user_trade
            WHERE dt>'0'
            GROUP BY user_name
            
            UNION ALL
    
            SELECT user_name,
                   0 as pay_amount,
                   sum(refund_amount) as refund_amount
            FROM user_refund
            WHERE dt>'0'
            GROUP BY user_name
        )a
    GROUP BY a.user_name;
    

    在这里插入图片描述

    4.7 表连接优化

    • 小表在前,大表在后
      Hive假定查询中最后的一个表是大表,它会将其它表缓存起来,然后扫描最后那个表

    • 使用相同的连接键
      当对3个或者更多个表进行join连接时,如果每个on子句都使用相同的连接键的话,那么只会产生一个MapReduce job

    • 尽早的过滤数据
      减少每个阶段的数据量,对于分区表要加分区,同时只选择需要使用到的字段。

    4.8 遵循严格模式

    严格模式:强制不允许用户执行3种有风险的HiveSQL语句,一旦执行会直接报错。

    • 查询分区表时不限定分区列的语句
    • 两表join产生了笛卡尔积的语句
    • order by来排序但没有指定limit的语句。

    要开启严格模式,需要设置参数hive.mapred.mode=strict;。开启严格模式,避免造成不必要的集群资源的浪费。

    4.9 阿里手册 の SQL语句

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    展开全文
  • 此文章主要向大家描述的是MySQL统计函数 GROUP_CONCAT 使用的具体操作步骤,以及对其实际操作步骤中我们大家要用到的代码的描述,望你在浏览之后会对其实际操作有更好的了解。sql:casewhen,distinct,groupby,group_...
  • SQL优化

    2018-09-20 17:01:47
    3.检查执行过程中优化器的统计信息 4.分析相关表的记录数、索引情况 5.改写SQL语句、表分析 6.有些SQL语句不具备优化的可能,需要优化处理方式 7.达到最佳执行计划 常用SQL优化 1.优化查询SQL字段的选择,避免用* 2....
  • SQL常见雷区,函数优化,执行顺序,使用技巧汇总 一、sql使用技巧 1、去重统计 select count(distinct 去重字段) from 表 2、字段别名和 group by 踩这个坑还是很难受的,亏得公司的学长看出来了,希望下次不要再...
  • sql优化

    2018-03-06 22:13:31
    对于max()和count()这样的统计函数,将统计的字段设置索引可以实现sql优化2.子查询编程连接查询,可以加快查询的速度3.优化limit,方法一:记录上次查询返回的主键,在下次查询时,利用主键进行过滤 。 方法二:...
  • sql 优化

    2016-05-06 11:18:59
    近日做的项目,数据量很大,在统计方面,sql语句的优化成为了必然,这是我的一些想法(oracle),先记下: 1、在查询等检索数据时,where 和order by 条件所涉及到的列加上索引。 2、在进行时间比较的时候,尽力将...
  • 常用函数 提高性能——调试 1.指定需要返回的字段 2.合理使用GROUP BY 当过滤条件作用在分区字段上面时,可以减少数据扫描的范围,有效提升查询性能; 当group by的数值型字段将比字符型更节省内存空间; 还应考虑...
  • 1.使用over子句与rows_number()以及聚合函数进行使用,可以进行编号以及各种操作。而且利用over子句的分组效率比group by子句的效率更高。 2.在订单表(order)中统计中,生成这么每一条记录都包含如下信息:...
  • 收获不止SQL优化

    2018-08-07 16:59:13
    第13章 动手,过程函数优化SQL飞 352 13.1 PL/SQL优化重点 353 13.1.1 定义类型的优化 353 13.1.2 PL/SQL的集合优化 355 13.1.3 PL/SQL的游标合并 361 13.1.4 动态SQL 364 13.1.5 使用10046 trace跟踪PL/...
  • 定期使用ANALYZE TABLE使表统计信息保持最新 ,为优化器提供构造有效执行计划所需的信息。 阅读EXPLAIN计划并调整索引。 调整MySQL用于缓存的内存区域的大小和属性。 避免锁导致的查询性能问题。 1. WHERE子句优化 ...
  • SQL优化禁用SELECT *使用SELECT COUNT(*) 统计行数尽量少运算尽量避免全表扫描,如果可以,在过滤列建立索引尽量避免在WHERE子句对字段进行NULL判断尽量避免在WHERE子句使用!= 或者<>尽量避免在WHERE子句使用...
  • SQL优化禁用SELECT *使用SELECT COUNT(*) 统计行数尽量少运算尽量避免全表扫描,如果可以,在过滤列建立索引尽量避免在WHERE子句对字段进行NULL判断尽量避免在WHERE子句使用!= 或者<>尽量避免在WHERE子句使用...
  • 第五部分 SQL优化 优化SQL语句执行过程 SQL优化术语 第一部分 SQL基础 SQL简介 发展历史 SQL语句分类 DDL[Data Definition Language] DML[Data Manipulation Language] DQL [Data Query Language] DCL [Data...
  • SQL性能优化

    2014-12-20 10:21:26
    一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。  NOT IN  此操作是强列推荐不使用的,因为不能应用表的索引。  推荐方案:用NOT EXISTS 或(Outer-Join+判断为空)方案代替 例如: ...
  • 在Spark 1.5.x版本,增加了一系列内置函数到DataFrame API中,并且实现了code-generation的优化。与普通的函数不同,DataFrame的函数并不会执行后立即返回一个结果值,而是返回一个Column对象,用于在并行作业中进行...
  • 第13章 动手,过程函数优化SQL飞 352 13.1 PL/SQL优化重点 353 13.1.1 定义类型的优化 353 13.1.2 PL/SQL的集合优化 355 13.1.3 PL/SQL的游标合并 361 13.1.4 动态SQL 364 13.1.5 使用10046trace 跟踪PL/...
  • 在对一个dataframe的多个列实现应用同一个函数时,是否能动态的指定? 例如: 对A,B,C三列实现分组统计 1.初始化spark,构建DF val spark = SparkSession.builder() .appName("name") .master("local[2]") ...
  • SQL语句优化

    2018-07-18 15:57:00
    人数登记率的sql优化(TBL_ZBXT_013_00):  思路:开始的思路是想把每一个符合条件的人员所在区县,转换成地市来进行统计,但是上万条数据逐条进行函数转换极度影响查询效率 因此,先将各区县的人数进行个数...
  • SQL高级优化

    2018-12-04 15:00:53
     COUNT()是一个特殊的函数,有两种不同的作用,它可以统计某个列值的数量,也可以统计行数。  在统计列值的时候要求列值是非空的,也就是不统计null。  当我们统计行的时候,常见的是COUNT(*),这种情况下,...
  • 其实我们可以使用oracle提供的olap函数对此进行优化,通过伪列:count(*) over()即可获得当前sql的总条数。  比如select t.*,count(*) over() from dual  会返回总条数为1。    olap...
  • 很简单,就是为了统计记录数 由SELECT返回 为了理解这个函数,让我们祭出 employee_tbl 表 注意:由于 SQL 查询对大小写不敏感,所以在 WHERE 条件中,无论是写成 ZARA 还是 Zara,结果都是一样的 count(1),count...
  • oracle-sql优化

    2017-12-06 11:16:58
    一、SQL语句准备执行阶段 当SQL 语句进入Oracle ...4) 生成执行计划:使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划 5) 建立二进制代码--基于HASH函数的HASH值:基于执行计划 一旦为执行准备好了,
  • Mysql Sql优化

    2019-04-02 10:10:21
    使用select count(*) 统计行数 尽量少运算 尽量避免全表扫描,如果可以,在过滤列建立索引 尽量避免在where子句对字段进行null判断 尽量避免在where子句使用!= 或者<> 尽量避免在where子句使用or连接 尽量避免...
  • SQL优化方法

    2018-12-07 18:05:35
    SQL优化方法主要包含两大类 1、表的优化,2、语句的优化  1.1、纯数字的的字段尽量存储为整型,在排序或比较大小时,相对字符串有很大的速度优势  1.2、在根据需求设计表的时候,可以先具体的去了解每一个...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 444
精华内容 177
关键字:

统计函数优化sql