• 10月3日



    今天做极限测试后续内容

    统计每天各个机场的销售数量和销售金额。

    通过查询贩卖编号具有C开头的数据并按日期和贩卖编号排序放入存储机场数据的表

    create table jc row format delimited fields terminated by ',' as select day_id,sale_nbr,sum(cnt),sum(round)  from saless where sale_nbr like 'C%' group by day_id,sale_nbr;

    5.

    统计每天各个代理商的销售数量和销售金额

    查询以O开头的贩卖编号按日期贩卖编号给存储代理商数据的表

    create table dlxs row format delimited fields terminated by ',' as select day_id,sale_nbr,sum(cnt),sum(round)  from salet where sale_nbr like 'O%' group by day_id,sale_nbr;

    6.

    统计每天各个代理商的销售活跃度。

    先创建一个表,存储代理商出售的次数,代理商购买的次数,

    再查询表中编号相同的sum(number)便是交易次数总和 放入新的表中

    create table dln (day_id string,sale_nbr string,number int) row format delimited fields terminated by ',';

    insert into table dln select day_id,buy_nbr,count(cnt)  from salet where  buy_nbr like 'O%' group by day_id,buy_nbr;

    insert into table dln select day_id,sale_nbr,count(cnt)  from salet where  sale_nbr like 'O%' group by day_id,sale_nbr;

    create table dlns row format delimited fields terminated by ',' as select day_id,sale_nbr,sum(number)  from dln where  sale_nbr like 'O%' group by day_id,sale_nbr;

    7.

    汇总统计 9 月 1 日到 9 月 15 日之间各个代理商的销售利润。

    创建两个表分别存储代理商买入的数据和卖出的数据

    create table  mc(day_id string,sale_nbr string,chucnt int,churound int) Row format delimited fields terminated by ',';

    create table  mr(day_id string,sale_nbr string,chucnt int,churound int) Row format delimited fields terminated by ',';

    insert into table mc select day_id,sale_nbr,sum(cnt),sum(round)  from saless where  sale_nbr like 'O%' and  day_id > '2021-09-01' and day_id < '2021-09-15' group by day_id,sale_nbr;//查询结果写入以创建表

    insert into table mr select day_id,buy_nbr,sum(cnt),sum(round)  from saless where  buy_nbr like 'O%' and  day_id > '2021-09-01' and day_id < '2021-09-15' group by day_id,buy_nbr;

    再通过连接两个表输出结果,将具有相同编号相同日期的数据整合在一起

    create table dlz Row format delimited fields terminated by ','  as select mc.day_id ,mc.sale_nbr,collect_set(mc.chucnt)[0] as sale_cnt,collect_set(mc.churound)[0] as sale_round,collect_set(mr.chucnt)[0] as buy_cnt,collect_set(mr.churound)[0]  as buy_round,(collect_set(mc.churound)[0] - collect_set(mr.churound)[0]) as profit

    from mc join mr on mc.sale_nbr=mr.sale_nbr and mc.day_id=mr.day_id group by mc.sale_nbr,mc.day_id ;

    8.将四个结果表导出到mysql

    bin/sqoop export -connect jdbc:mysql://192.168.43.244:3306/tsgl?serverTimezone=UTC --username root --password root --table jc --fields-terminated-by ',' --export-dir /user/hive/warehouse/my.db/jc

    bin/sqoop export -connect jdbc:mysql://192.168.43.244:3306/tsgl?serverTimezone=UTC --username root --password root --table dlxs --fields-terminated-by ',' --export-dir /user/hive/warehouse/my.db/dlxs

    bin/sqoop export -connect jdbc:mysql://192.168.43.244:3306/tsgl?serverTimezone=UTC --username root --password root --table dlnss --fields-terminated-by ',' --export-dir /user/hive/warehouse/my.db/dlns

    bin/sqoop export -connect jdbc:mysql://192.168.43.244:3306/tsgl?serverTimezone=UTC --username root --password root --table dlz --fields-terminated-by ',' --export-dir /user/hive/warehouse/my.db/dlz

    导入到数据库后就可以进行界面的制作。

  • 相关阅读:
    剑指offer——关于排序算法的应用(一):归并排序
    剑指offer——关于排序算法的应用:选择排序和冒泡排序
    剑指offer:将矩阵选择、螺旋输出矩阵——Python之光
    剑指offer:链表——常见的多指针协同操作:
    剑指Offer:编程习惯篇:代码鲁棒性,代码可扩展性——防御性的编程习惯,解决问题时方法分模块考虑
    剑指offer:数字二进制含1个数,快速幂运算:二进制位运算的运用
    剑指offer:斐波那契数列,跳台阶,变态跳台阶——斐波那契数列类题目:
    回溯法实现各种组合的检索:
    剑指offer:二维数组中查找
    jdk生成https证书的方法
  • 原文地址:https://www.cnblogs.com/buyaoya-pingdao/p/14623066.html
Copyright © 2020-2023  润新知