每日总结2023/9/25

create database test;

use test;

create table test_jichang
(
    day_id   varchar(255),
    sale_nbr varchar(255),
    cnt      varchar(255),
    round    varchar(255)
);
create table test_dailishang
(
    day_id   varchar(255),
    sale_nbr varchar(255),
    cnt      varchar(255),
    round    varchar(255)
);
create table test_dailishang_h
(
    day_id      varchar(255),
    sale_nbr    varchar(255),
    Sale_number int
);
create table test_daili
(
    day_id   varchar(255),
    sale_nbr varchar(255),
    incnt    int,
    inround  int,
    outcnt   int,
    outround int,
    lirun    int
);

-- 创建初始表
show databases ;
create database if not exists ttes;
use ttes;
drop table  testSales;
create table testSales
(
    day_id   string,
    sale_nbr string,
    buy_nbr  string,
    cnt      string,
    round    string
)
    row format delimited fields terminated by ',';
-- 将本地数据导入初始表中(先将数据文件上传到虚拟机里)
load data local inpath '/shuju/sales.csv' into table testSales;
use ttes;
select * from testSales;

--创建表格存放清洗后的数据

create table test_sales_wash
(
    day_id   string,
    sale_nbr string,
    buy_nbr  string,
    cnt      string,
    round    string
)
    row format delimited fields terminated by ',';
-- 插入清洗后的数据
insert overwrite table test_sales_wash
select date_add('2023-09-00', cast(day_id as int)) as day_id,
       sale_nbr                                    as sale_nbr,
       buy_nbr                                     as buy_nbr,
       cnt                                         as cnt,
       round                                       as round
from testSales;
select * from test_sales_wash;

-- 1.统计每天各个机场的销售数量和销售金额。
-- 创建机场表存放数据
create table test_jichang
(
    day_id   string,
    sale_nbr string,
    cnt      string,
    round    string
) row format delimited fields terminated by ',';
--向机场表插入数据
insert into table test_jichang
select day_id, sale_nbr, sum(cnt) as cnt, sum(round) as round
from test_sales_wash
group by sale_nbr, day_id
having sale_nbr like 'C%';
select * from test_jichang;