ssh $i "ps -ef | grep file-flume-kafka | grep -v grep | awk ‘{print \$2}‘ | xargs kill"

<property> 
    <name>yarn.scheduler.fair.assignmultiple</name> 
    <value>true</value> 
    <discription>是否允许NodeManager一次分配多个容器</discription> 
</property> 
<property> 
    <name>yarn.scheduler.fair.max.assign</name> 
    <value>20</value> 
    <discription>如果允许一次分配多个,一次最多可分配多少个,这里按照一个最小分配yarn.scheduler.minimum-allocation-mb4gb来计算总共内存120/4=30给20即可</discription> 
</property>

/opt/module/sqoop/bin/sqoop import --connect --username --password --target-dir --delete-target-dir --num-mappers --fields-terminated-by   --query   "$2" ‘ and $CONDITIONS;‘

sqoop export 
--connect jdbc:mysql://192.168.137.10:3306/user_behavior 
--username root 
--password 123456 
--table app_cource_study_report 
--columns watch_video_cnt,complete_video_cnt,dt 
--fields-terminated-by "\t" 
--export-dir "/user/hive/warehouse/tmp.db/app_cource_study_analysis_${day}" -
-staging-table app_cource_study_report_tmp
--clear-staging-table --input-null-string ‘\N‘

--最后比较指标，若都大于即为所求的区
select t2.area_code
from (
    --先求出罗湖区2020年四季度的GDP指标
    select area_code,
           sum(case when month(‘occur_period‘) between 1 and 3 then index_value else 0 end) `one`,   --一季度GDP指标
           sum(case when month(‘occur_period‘) between 4 and 6 then index_value else 0 end) `two`,   --二季度GDP指标
           sum(case when month(‘occur_period‘) between 7 and 9 then index_value else 0 end) `three`, --三季度GDP指标
           sum(case when month(‘occur_period‘) between 10 and 12 then index_value else 0 end) `four` --四季度GDP指标
    from macro_index_data
    where area_code = ‘440305000000‘ --罗湖区
    and index_code = ‘gmjj_jjzl_01‘  --GDP指标
    and index_type = ‘TB‘            --增速
    and year(‘occur_period‘) = 2020    --2020年
    group by area_code
) t1
join (
    --再求出其它区2020年四季度的GDP指标
    select area_code,
           sum(case when month(‘occur_period‘) between 1 and 3 then index_value else 0 end) `one`,   --一季度GDP指标
           sum(case when month(‘occur_period‘) between 4 and 6 then index_value else 0 end) `two`,   --二季度GDP指标
           sum(case when month(‘occur_period‘) between 7 and 9 then index_value else 0 end) `three`, --三季度GDP指标
           sum(case when month(‘occur_period‘) between 10 and 12 then index_value else 0 end) `four` --四季度GDP指标
    from macro_index_data
    where area_code <> ‘440305000000‘ --其它区
    and index_code = ‘gmjj_jjzl_01‘  --GDP指标
    and index_type = ‘TB‘            --增速
    and year(‘occur_period‘) = 2020    --2020年
    group by area_code
) t2 on t2.one > t1.one 
and t2.two > t1.two 
and t2.three > t1.three 
and t2.four > t1.four;

select t6.jdmc `所在街道`,
       t8.jiance_person_num `已参与核酸检测总人数`,
       t7.add_num `今日新增人数`,
       t6.person_num `已检测人数占总人口数的比例`
from (
    --各街道总人口数
    select jdmc,count(*) `person_num`
    from t_syrkxxb
    group by jdmc
) t6
join (
    --各街道今日新增人数：以前没有检测过的用户
    select t4.jdmc,count(*) `add_num`
    from (
        select zjhm,zjlx,jdmc
        from t_hsjcqkb
        where date_format(‘jcsj‘,‘yyyy-MM-dd‘) = ‘2021-07-29‘
    ) t4
    left join (
        --求出检测过的用户
        select zjhm,zjlx
        from t_hsjcqkb
        group by zjhm,zjlx
    ) t5 on t4.zjhm = t5.zjhm 
    and t4.zjlx = t5.zjlx
    where t5.zjhm is null
    group by jdmc    --按照所在街道分组
) t7 on t6.jdmc = t7.jdmc
join (
    --求出各街道已参与核酸检测总人数
    select t1.jdmc,count(*) `jiance_person_num`
    from (
        --先将实有人口表按更新时间排序后过滤出最新的记录
        select t1.*
        from (
            select *,row_number() over(partition by zjhm order by gxsj desc) `rank_gxsj`
            from t_syrkxxb
            group by zjhm    --以证件号码分组
        ) t1
        where rank_gxsj = ‘1‘
    ) t2
    join (
        --求出检测过的用户
        select zjhm,zjlx
        from t_hsjcqkb
        group by zjhm,zjlx
    ) t3 on t2.zjhm = t3.zjhm and t2.zjlx = t3.zjlx
    group by t1.jdmc --以街道分组
) t8 t6.jdmc = t8.jdmc;

--各类型专利top 10申请人，以及对应的专利申请数
select t1.apply_name `申请人`,count(*) `专利申请数`,rank() over(order by count(*) desc) `专利数排名`
from (
    --先将申请人字段炸裂
    select d.*, t1.coll `apply_name`
    from t_patent_detail d
    lateral view explode(split(apply_users,‘;‘)) t1 as coll
) t1
group by t1.apply_name;    --按照申请人分组

select t1.count(*) `当日新增用户数`
from (
    select uid
    from hee
    where ds = ‘20210729‘    --当日新增
    and is_nsk_apply = 1    --已申请核额
    group by uid
) t1

select t1.count(*) `昨日新增用户数`
from (
    select uid
    from hee
    where ds = ‘20210728‘    --昨日新增
    and is_nsk_apply = 1    --已申请核额
    group by uid
) t1

select t1.count(*) `历史累计`
from (
    select uid
    from hee
    where ds <= ‘20210729‘    --历史累计
    and is_nsk_apply = 1    --已申请核额
    group by uid
) t1

select t1.count(*) `当日新增规则通过户数`
from (
    select uid
    from hee
    where ds = ‘20210729‘    --当日新增
    and is_nsk_apply = 1    --已申请核额
    and is_pass_rule = 1    --已通过规则
    group by uid
) t1

select t1.count(*) `昨日新增规则通过户数`
from (
    select uid
    from hee
    where ds = ‘20210728‘    --昨日新增
    and is_nsk_apply = 1    --已申请核额
    and is_pass_rule = 1    --已通过规则
    group by uid
) t1

select t1.count(*) `历史累计规则通过户数`
from (
    select uid
    from hee
    where ds <= ‘20210729‘    --历史累计
    and is_nsk_apply = 1    --已申请核额
    and is_pass_rule = 1    --已通过规则
    group by uid
) t1

select t1.count(*) `当日新增核额成功户数`
from (
    select uid
    from hee
    where ds = ‘20210729‘    --当日新增
    and is_nsk_apply = 1    --已申请核额
    and is_pass_rule = 1    --已通过规则
    and is_obtain_qutoa = 1 --已授信成功
    group by uid
) t1

select t1.count(*) `昨日新增核额成功户数`
from (
    select uid
    from hee
    where ds = ‘20210728‘    --昨日新增
    and is_nsk_apply = 1    --已申请核额
    and is_pass_rule = 1    --已通过规则
    and is_obtain_qutoa = 1 --已授信成功
    group by uid
) t1

select t1.count(*) `历史累计`
from (
    select uid
    from hee
    where ds <= ‘20210729‘    --历史累计
    and is_nsk_apply = 1    --已申请核额
    and is_pass_rule = 1    --已通过规则
    and is_obtain_qutoa = 1 --已授信成功
    group by uid
) t1

select sum(quota)from hee
where ds = ‘20210729‘    ----当日新增
and is_nsk_apply = 1    --已申请核额
and is_pass_rule = 1    --已通过规则
and is_obtain_qutoa = 1 --已授信成功

select sum(quota)from hee
where ds = ‘20210728‘    ----当日新增
and is_nsk_apply = 1    --已申请核额
and is_pass_rule = 1    --已通过规则
and is_obtain_qutoa = 1 --已授信成功

select sum(quota)from hee
where ds <= ‘20210729‘    ----历史累计
and is_nsk_apply = 1    --已申请核额
and is_pass_rule = 1    --已通过规则
and is_obtain_qutoa = 1 --已授信成功

select avg(quota)from hee
where ds = ‘20210729‘    --当日新增
and is_nsk_apply = 1    --已申请核额
and is_pass_rule = 1    --已通过规则
and is_obtain_qutoa = 1 --已授信成功

select avg(quota)from hee
where ds = ‘20210728‘    --昨日新增
and is_nsk_apply = 1    --已申请核额
and is_pass_rule = 1    --已通过规则
and is_obtain_qutoa = 1 --已授信成功

select avg(quota)from hee
where ds < ‘20210729‘    --历史累计
and is_nsk_apply = 1    --已申请核额
and is_pass_rule = 1    --已通过规则
and is_obtain_qutoa = 1 --已授信成功

select sum(putout_amt)from jieju
where ds = ‘20210729‘    --当日新增

select sum(putout_amt)from jieju
where ds = ‘20210728‘    --昨日新增

select sum(putout_amt)from jieju
where ds <= ‘20210728‘    --历史累计

select avg(putout_amt) `当日户均发放金额`
from jieju
where ds = ‘20210729‘

select avg(putout_amt) `昨日户均发放金额`
from jieju
where ds = ‘20210728‘

select avg(putout_amt) `历史累计户均发放金额`
from jieju
where ds <= ‘20210729‘

select count(*) `在贷客户数`
from (
    select uid
    from jieju
    where ds = ‘20210729‘ 
    and prod_type = ‘XX贷‘
    group by uid
) t1

select sum(balance) `在贷余额`
from jieju
where ds = ‘20210729‘ 
and prod_type = ‘XX贷‘

select sum(balance) `不良余额`
from jieju
where ds = ‘20210729‘ 
and prod_type = ‘XX贷‘
and is_buding = 1

余额不良率=不良余额/在贷余额

select count(*) `不良客户数`
from (
    select uid
    from jieju
    where ds = ‘20210729‘ 
    and prod_type = ‘XX贷‘
    and is_buding = 1
    group by uid
) t1

客户不良率=不良客户数/在贷客户数

select count(*) `在贷客户数`
from (
    select uid
    from jieju
    where ds = ‘20210729‘ 
    and prod_type = ‘YY贷‘
    group by uid
) t1

select sum(balance) over(partition by ds) `在贷余额`
from jieju
where ds = ‘20210729‘ 
and prod_type = ‘YY贷‘

select sum(balance) over(partition by ds) `不良余额`
from jieju
where ds = ‘20210729‘ 
and prod_type = ‘YY贷‘
and is_buding = 1

余额不良率=不良余额/在贷余额

select count(*) `不良客户数`
from (
    select uid
    from jieju
    where ds = ‘20210729‘ 
    and prod_type = ‘YY贷‘
    and is_buding = 1
    group by uid
) t1

客户不良率=不良客户数/在贷客户数

select count(*) `在贷客户数`
from (
    select uid
    from jieju
    where ds = ‘20210729‘ 
    and prod_type = ‘ZZ贷‘
    group by uid
) t1

select sum(balance) over(partition by ds) `在贷余额`
from jieju
where ds = ‘20210729‘ 
and prod_type = ‘ZZ贷‘

select sum(balance) over(partition by ds) `不良余额`
from jieju
where ds = ‘20210729‘ 
and prod_type = ‘ZZ贷‘
and is_buding = 1

余额不良率=不良余额/在贷余额

select count(*) `不良客户数`
from (
    select uid
    from jieju
    where ds = ‘20210729‘ 
    and prod_type = ‘ZZ贷‘
    and is_buding = 1
    group by uid
) t1

客户不良率=不良客户数/在贷客户数

select count(distinct uid) `户数`,sum(balance) `余额`,round((sum(10to30) / count(*)),2) `逾期率`
from (
    select uid,
           balance,
           case when owerduedays between 1 and 30 then 1 else 0    `10to30`
    from jieju
) t1

select count(distinct uid) `户数`,sum(balance) `余额`,round((sum(30to90) / count(*)),2) `逾期率`
from (
    select uid,
           balance,
           case when owerduedays between 30 and 90 then 1 else 0    `30to90`
    from jieju
) t1

select count(distinct uid) `户数`,sum(balance) `余额`,round((sum(90day) / count(*)),2) `逾期率`
from (
    select uid,
           balance,
           case when owerduedays > 90 then 1 else 0    `90day`
    from jieju
) t1

select count(distinct uid) `户数`,sum(balance) `余额`,round((sum(1day) / count(*)),2) `逾期率`
from (
    select uid,
           balance,
           case when owerduedays > 1 then 1 else 0 `1day`
    from jieju
) t1

select count(distinct uid) `户数`,sum(balance) `余额`,round((sum(1day) / count(*)),2) `逾期率`
from (
    select uid,
           balance,
           case when owerduedays > 1 then 1 else 0    `1day`
    from jieju
    where is_buding = 1
) t1

select count(*) `user_num`,sum(mingzhon) `mingzhon_num`,round(mingzhon_num / user_num,2) `命中率`
from (
    select uid,(case when get_json_object(‘content‘,‘$.V01‘) == 1 then 1 else 0 end) `mingzhon`
    from moxin
    where ds = ‘20201010‘
) t1

select count(*) `user_num`,sum(mingzhon) `mingzhon_num`,round(mingzhon_num / user_num,2) `命中率`
from (
    select uid,(case when get_json_object(‘content‘,‘$.V02‘) == 1 then 1 else 0 end) `mingzhon`
    from moxin
    where ds = ‘20201010‘
) t1

--MOB1，一个月后再来查询
select sum(bad_monery) `不良余额`,sum(putout_amt) `发放月金额`
from (
    select (case when is_buding = 1 then balance else 0 end) `bad_monery`,putout_amt
    from jieju
    where date_format(‘ds‘,‘yyyy-MM‘) = ‘2019-10‘    --数据都按天存放，过滤出当月的即可
    and date_format(‘putout_date‘,‘yyyy-MM‘) = ‘2019-10‘
) t1

--MOB2，两个月后再来查询
select sum(bad_monery) `不良余额`,sum(putout_amt) `发放月金额`
from (
    select (case when is_buding = 1 then balance else 0 end) `bad_monery`,putout_amt
    from jieju
    where date_format(‘ds‘,‘yyyy-MM‘) = ‘2019-10‘    --数据都按天存放，过滤出当月的即可
    and date_format(‘putout_date‘,‘yyyy-MM‘) = ‘2019-10‘
) t1

--MOB1，一个月后再来查询
select sum(bad_monery) `不良余额`,sum(putout_amt) `发放月金额`
from (
    select (case when is_buding = 1 then balance else 0 end) `bad_monery`,putout_amt
    from jieju
    where date_format(‘ds‘,‘yyyy-MM‘) = ‘2019-11‘    --数据都按天存放，过滤出当月的即可
    and date_format(‘putout_date‘,‘yyyy-MM‘) = ‘2019-11‘
) t1

--MOB2，两个月后再来查询
select sum(bad_monery) `不良余额`,sum(putout_amt) `发放月金额`
from (
    select (case when is_buding = 1 then balance else 0 end) `bad_monery`,putout_amt
    from jieju
    where date_format(‘ds‘,‘yyyy-MM‘) = ‘2019-11‘    --数据都按天存放，过滤出当月的即可
    and date_format(‘putout_date‘,‘yyyy-MM‘) = ‘2019-11‘
) t1

select a.key,a.value
from a
where a.key not in (select b.key from b)

select t1.key,t1.value
from (
    select a.key,a.value
    from a
) t1
right join (
    select b.key
    from b
) t2 on t1.key = t2 .key
where t1.key is null;

select a.key,a.value
from a
where a.key in (select b.key from b)

select t1.key,t1.value
from (
    select a.key,a.value
    from a
) t1
join (
    select b.key
    from b
) t2 on t1.key = t2 .key;

select avg(score) `班级为2020的所有学生科目"PE"的平均分`
from (
    select *
    from student
    where class = ‘2020‘
) t1
join (
    select student_num,score
    from course_score
    where courese = ‘PE‘
) t2 on t1.num = t2.student_num

select num,name
from student s
join (
    select student_num
    from course_score
    group by student_num
    having score > 80
) t1 on s.num = t1.student_num;

select s.num,s.name
from student s
left join (
    select student_num
    from course_score
    where score <= 80
    group by student_num
) t1 on s.num = t1.student_num
where t1.student_num is null;

select student_num,
       sum(case when score >= 60 then 1 else 0 end) `passed`,
       sum(case when score < 60 then 1 else 0 end) `failed`
from course_score
group by student_num

select user,
       order_id,
       time,
       row_number() over(partition by user order by time asc) `order_id`
from A

select userid,amount
from (
    select userid,amount,paytime,row_number() over(partition by userid order by paytime asc) `rank`
    from order
) t1
where t1.rank = 1;

select count(distinct userid) `user_num`, round(count(evt) / user_num,2) `平均操作次数`
from tracking_log
where date_format(‘evt_time‘,‘yyyy-MM-dd‘) = ‘2021-07-30‘

select count(userid) `A操作之后是B操作，AB操作必须相邻的用户数`
from (
    select userid,sum(case when (evt = ‘B‘ && evtOne = ‘A‘) then 1 else 0 end) `evt_sum`
    from (
        select userid,
               evt,        --用户行为
               lag(evt,1) over(partition by userid order by evt_time) `evtOne`    --往前一行的用户行为
        from tracking_log
        where date_format(‘evt_time‘,‘yyyy-MM-dd‘) = ‘2021-07-30‘
    ) t1
    group by userid
) t2
where t2.evt_sum >=1;

select count(userid) `A操作之后是B操作，AB操作必须相邻的用户数`
from (
    select userid,sum(case when (evt = ‘A‘ && evtOne = ‘B‘) then 1 else 0 end) `evt_sum`
    from (
        select userid,
               evt,        --用户行为
               lead(evt,1) over(partition by userid order by evt_time) `evtOne`    --往后一行的用户行为
        from tracking_log
        where date_format(‘evt_time‘,‘yyyy-MM-dd‘) = ‘2021-07-30‘
    ) t1
    group by userid
) t2
where t2.evt_sum >=1;

select A.*
from A
join (
    select class,max(height) `max_height`
    from A
    group by class
) t1 on A.class = t1.class
and A.height = t1.height;

select class,
       name,
       sex,
       height
from (
    select class,
           name,
           sex,
           height,
           rank() over(partition by class order by height desc) `rank`
    from A
) t1 
where t1.rank = 1;

userId    visitDate    visitCount
u01    2017/1/21    5
u02    2017/1/23    6
u03    2017/1/22    8
u04    2017/1/20    3
u01    2017/1/23    6
u01    2017/2/21    8
U02    2017/1/23    6
U01    2017/2/22    4

select userId,
       mn
       mn_count    `当前月访问次数`,
       sum(mn_count) over(partition by userId order by mn) `累计访问次数`
from (
    --统计每个用户每个月的访问次数
    select userId,
           mn,
           sum(visitCount) `mn_count`
    from (
        --先将访问时间格式化
        select userId,
               date_format(regexp_replace(visitDate,‘/‘,‘-‘),‘yyyy-MM‘) mn,
               visitCount
        from user
    ) t1
    group by userId,mn
) t2;

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

select id,score,rank() over(order by score desc) `排名`
from scores

select uid
from (
    select uid,
           if(score>avg_score,0,1) flag
    from (
        --先求出各个学科的平均成绩
        select uid,
               score,
               avg(score) over(partition by subject_id) avg_score
        from score
    )t1
)t2
group by uid
having sum(flag)=0;

select shop,count(distinct user_id) `访客数`
from Visit
group by shop

select t2.shop,
       t2.user_id,
       t2.user_count
from (
    select shop,
           user_id,
           user_count,
           rank() over(partition by shop order by user_count) `rank`
    from (
        select shop,user_id,count(user_id) `user_count`
        from Visit
        group by shop,user_id
    ) t1 
) t2
where t2.rank <= 3;

select month(‘date‘),count(order_id) `订单数`,count(distinct user_id) `用户数`,sum(amount) `总成交金额`
from order
where year(‘date‘) = ‘2017‘
group by month(‘date‘)

select count(distinct user_id) `新客数`
from (
    select *
    from order
    where date_format(‘date‘,‘yyyy-MM‘) = ‘2017-11‘
) t1
left join (
    select *
    from order
    where date_format(‘date‘,‘yyyy-MM‘) < ‘2017-11‘
) t2 on t1.user_id = t2.user_id
where t2.user_id is null;

日期     用户    年龄
2019-02-11,test_1,23
2019-02-11,test_2,19
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-12,test_2,19
2019-02-13,test_1,23
2019-02-15,test_2,19
2019-02-16,test_2,19

select sum(user_total_count),
       sum(user_total_avg_age),
       sum(twice_count),
       sum(twice_count_avg_age)
from (
    select 0 `user_total_count`,
           0 `user_total_avg_age`,
           count(*) `ct`,
           cast(sum(age)/count(*) as decimal(10,2)) `twice_count_avg_age`
    from (
        select user_id,
               min(age) `age`
        from (
            select user_id,
                   min(age) `age`
            from (
                select user_id,
                       age,
                       date_sub(dt,rank) `flag`
                from (
                    select dt,
                       user_id,
                       min(age) `age`,
                       rank() over(partition by user_id order by dt) `rank`
                    from user_age
                    group by dt,user_id
                ) t1
            ) t2
            group by user_id,flag
            having count(*) >= 2
        ) t3 
        group by user_id
    ) t4
    union all
    select count(*) `user_count`,
           cast((sum(age)/count(*)) as decimal(10,1)),
           0 `twice_count`,
           0 `twice_count_avg_age`
    from (
        select user_id,
               min(age) `age`
        from user_age 
        group by user_id
    ) t5
) t6;

select userid,
       money,
       paymenttime,
       orderid
from (
select userid,
       money,
       paymenttime,
       orderid,
       row_number() over(partition by userid order by paymenttime desc) `rank`
from ordertable
where date_format(‘paymenttime‘,‘yyyy-MM‘) = ‘2017-10‘
) t1
where t1.rank = 1

时间                    接口                 ip地址
2016-11-09 11：22：05  /api/user/login    110.23.5.33
2016-11-09 11：23：10  /api/user/detail   57.3.2.16
.....
2016-11-09 23：59：40  /api/user/login    200.6.5.166

select address_id,count(*) `ip_count`
frrom address
where date_format(‘date‘,‘MM-dd HH‘) = ‘11-09 14‘
and url = ‘api/user/login‘
group by address_id
order by ip_count desc
limit 10

CREATE TABLE `account`
(   `dist_id` int（11）DEFAULT NULL COMMENT ‘区组id‘,
    `account` varchar（100）DEFAULT NULL COMMENT ‘账号‘,
    `gold` int（11）DEFAULT 0 COMMENT ‘金币‘）;

select dist_id,
       account,
       gold,
       rank
from (
select dist_id,
       account,
       gold,
       row_number() over(partition by dist_id order by gold desc) `rank`
from account
) t1
where t1.rank <= 10

insert into table member
(
    select t1.memberid,
           (mnaccount - rmnaccount) `credits`
    from (
        select memberid,
               sum(mnaccount) `mnaccount`
        from sale
        where memberid is not null
        group by memberid
    ) t1
    join (
        select memberid,
               sum(rmnaccount) `rmnaccount`
        from regoods
        where memberid is not null
        group by memberid
    ) t2 on t1.memberid = t2.memberid
);

自动编号   学号  姓名 课程编号 课程名称 分数
1     2005001 张三   0001   数学   69
2     2005002 李四   0001   数学   89
3     2005001 张三   0001   数学   69

delete from student
where 自动编号 not in (
    select min(自动编号)
    from student
    group by 学号,姓名,课程编号,课程名称,分数
)

select a.name,
       b.name
from team a, team b
where a.name < b.name

year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4

查成这样一个结果
year m1  m2  m3   m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

select year,
       (select amount from aa m where m.month = 1 and m.year = aa.year) `m1`,
       (select amount from aa m where m.month = 2 and m.year = aa.year) `m2`,
       (select amount from aa m where m.month = 3 and m.year = aa.year) `m3`,
       (select amount from aa m where m.month = 4 and m.year = aa.year) `m4`,
from aa
group by year

select * into b
from a
where 1<>1    --where 1=1 表示拷贝表结构和数据内容

courseid coursename score
1         java      70
2         oracle    90
3         xml       40
4         jsp       30
5         servlet   80

courseid coursename score  mark
1          java     70    pass
2          oracle   90    pass
3          xml      40    fail
4          jsp      30    fail
5          servlet  80    pass

select courseid,
       coursename,
       score,
       (case when score >= 60 then "pass" else "fail" end) `mark`
from score

select courseid,
       coursename,
       score,
       if(score >= 60,"pass","fail") `mark`
from score

购物人      商品名称     数量
A            甲          2
B            乙          4
C            丙          1
A            丁          2
B            丙          5

select *
from shoop s
join (
    select user
    from shoop
    group by user
    having count(*) > 2
) t1 on s.suer = t1.user;

date 　　  result
2005-05-09 win
2005-05-09 lose 
2005-05-09 lose 
2005-05-09 lose 
2005-05-10 win 
2005-05-10 lose 
2005-05-10 lose

  date   　win lose
2005-05-09  2   2 
2005-05-10  1   2

select date,
       sum(case when result = "win" then 1 else 0 end) `win`
       sum(case when result = "lose" then 1 else 0 end) `lose`
from info
group by date

create external table order(
    order_id int,
    user_id int,
    amount double,
    pay_datetime timestamp,
    channel_id int
) partitioned by (dt string)
row format delimited fields terminated by ‘\t‘;

select channel_id,
       count(order_id) `订单数`,
       count(distinct user_id) `下单人数`,
       sum(amount) `总金额`
from order
where dt = ‘2021-08-04‘
group by channel_id

select channel_id,
       order_id,
       amount,
       rank
from (
    select channel_id,
           order_id,
           amount,
           rank() over(partition by channel_id order by amount desc) `rank`
    from order
    where dt = ‘2021-08-04‘
    group by channel_id,order_id,amount
) t1
where t1.rank <= 3

order_id,//订单id
item_id, //商品id
create_time,//下单时间
amount//下单金额

item_id,//商品id
item_name,//商品名称
category//品类

item_id,//商品id
item_name,//名称
category_1,//一级品类
category_2,//二级品类

select item_id,
       item_num,
       rank() over(partition by item_id order by item_num desc) `rank`
from (
    select item_id,count(*) `item_num`
    from t_order
    where date_sub(‘2021-08-06‘,30) <= ‘create_time‘
    group by item_id
) t1
where rank <= 10;

select category,
       item_id,
       item_sum,
       rank
from (
    select category,
           item_id,
           sum(item_num) `item_sum`,
           rank() over(partition by category order by item_num desc) `rank`
    from (
        select t1.item_id,
               t1.item_num,
               t2.category
        from (
            select item_id,
                   count(*) `item_num`
            from t_order
            where date_sub(‘2021-08-06‘,30) <= ‘create_time‘
            group by item_id
        ) t1
        join (
            select item_id,
                   item_name,
                   category
            from t_item
        ) t2 on t1.item_id = t2.item_id
    ) t3
    group by category,item_id
) t4 
where rank <= 10;

select v1,max(v2)
from (
    select t1.v_id `v1`,  --版本号
           tmp.v_id `v2`  --主
    from t1
    lateral view explode(v2) tmp as v2
)

select v_id,    --版本号
       max(split(v_id,".")[0]) v1,  --主版本不会为空
       max(if(split(v_id,".")[1]="",0,split(v_id,".")[1]))v2,   --取出子版本并判断是否为空，并给默认值
       max(if(split(v_id,".")[2]="",0,split(v_id,".")[2]))v3    --取出阶段版本并判断是否为空，并给默认值
from t1

select v_id,
       rank() over(partition by v_id order by v_id) `seq`
from t1

SET hive.merge.mapfiles = true; 　　　　　　　　 -- 默认true，在map-only任务结束时合并小文件
SET hive.merge.mapredfiles = true; 　　　　　　 -- 默认false，在map-reduce任务结束时合并小文件
SET hive.merge.size.per.task = 268435456; 　　 -- 默认256M
SET hive.merge.smallfiles.avgsize = 16777216; -- 当输出文件的平均大小小于16m该值时，启动一个独立的map-reduce任务进行文件merge

set mapreduce.job.jvm.numtasks=10

set hive.map.aggr=true;

set hive.exec.compress.intermediate=true --启用中间数据压缩
set mapreduce.map.output.compress=true --启用最终数据压缩
set mapreduce.map.outout.compress.codec=…; --设置压缩方式

select * from
users a
left outer join 
logs b
on a.usr_id = cast(b.user_id as string)

hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G）
hive.exec.reducers.max（每个任务最大的reduce数，默认为999）

hive-drop-import-delims：导入到hive时删除 \n, \r, \001
hive-delims-replacement：导入到hive时用自定义的字符替换掉 \n, \r, \001

MySQL的utf8编码最多存储3个字节，当数据中存在表情号、特色符号时会占用超过3个字节数的字节，那么会出现错误 Incorrect string value: ‘\xF0\x9F\x91\x91\xE5\xB0...‘