完整的一个数据库需求操作有详细代码及讲解（涉及：建表、查重、提重、表关联、表数据的横纵向转换展示）

本文链接：https://blog.csdn.net/Guo_Yu_tian/article/details/107564939

用到的表：

bi_req.temp_gl_20200724_hm1      --表1（数据有重复的）
bi_req.temp_gl_20200724_hm11     --表11 （数据没有重复的，hm1的提重后的）
bi_req.temp_gl_20200724_hm2      --表2 （数据没有重复的）
bi_req.temp_gl_20200724_hm3      --表3（数据没有重复的）
bi_req.temp_gl_20200724_hm4      --表4（数据没有重复的）
bi_req.temp_gl_20200724_hm       --表5数据没有重复的）
bi_req.temp_gl_20200724_fee      --表6（数据没有重复的）

第一阶段：

建表语句：

create table bi_req.temp_gl_20200724_hm2
(
  DEVICE_NUMBER   VARCHAR2(11),    --device_number是号码
  ARPU            NUMBER           --arpu  是ARPU一般指每用户平均收入。运营商用来测定其取自每个最终用户的收入的一个指标。但并不反映最终的利润率。  
)

bi_req是临时表
不写的话默认是登录账号所在的表空间
数据入库：

`select * from BI_REQ.TEMP_GL_20200724_HM2 for update  --可以`更新整个表

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值     --只能更新一行

运行后可以对表进行数据修改，开锁 → 导入数据 → 点对勾确认 → 上锁 →点提交 → 数据提交完毕

一个表的数据查重以及去重：

select 1 from dual -- 查询一个为一的字段其值固定为1

select count(1) from dual  --记录所有为1的数据的个数

查重语句：

select count(1), count(distinct t.device_number) from BI_REQ.TEMP_GL_20200724_HM1 t
count(1)  --可以查出表中总共有多少条数据
count(distinct t.decive_number)  --可以查出去重后剩余有多少条数据
--distinct  去重语句

查询是哪些数据重复：

select t.device_number
from BI_REQ.TEMP_GL_20200724_HM1 t
group by t.device_number          --以号码分组，将号码分成一组一组的，那么就会出现一个号码出现一次，一个号码出现多次的情况
having count(t.device_number) > 1  --设置条件：出现记录大于一的号码

查询重复号码对应的所有数据：

select *                                     --查询所有数据
from BI_REQ.TEMP_GL_20200724_HM1 t
where t.device_number in                     --where条件，in在什么区间
    (select t.device_number                  --以下是查询出的重复的号码
        from BI_REQ.TEMP_GL_20200724_HM1 t
        group by t.device_number
        having count(t.device_number) > 1);

保留去重后的数据：

create table BI_REQ.TEMP_GL_20200724_HM11 as           --新建一个表使用的是下面搜索到的数据
select distinct t.* from BI_REQ.TEMP_GL_20200724_HM1 t;--去重后的数据
--  distinct t.*  去重后的所有数据
--  distinct t.device_number 是去重后的只有号码的数据

select distinct t.device_number from BI_REQ.TEMP_GL_20200724_HM1 t

第二种方法：
手动去重，一条一条删

多个表之间的数据查重与去重：
使用内连接：

select t1.device_number , t3.device_number
       from bi_req.temp_gl_20200724_hm11 t1 
       inner join bi_req.temp_gl_20200724_hm3 t3
       on t1.device_number=t3.device_number      --等值连接

可以查出两张表之间重复的号码，取交集即为重复的

第二阶段：

将所有用户号码存至一张表中：

create table bi_req.temp_gl_20200724_hm as
select device_number
  from (select device_number
          from bi_req.temp_gl_20200724_hm1
        union all
        select device_number
          from bi_req.temp_gl_20200724_hm2
        union all
        select device_number
          from bi_req.temp_gl_20200724_hm3
        union all
        select device_number
          from bi_req.temp_gl_20200724_hm4)
 group by device_number;

使用左关联将号码表和账期表关联在一起，显示一月账期号码和收入

select t.device_number, nvl(t1.fee, 0) fee   --nvl()可以显示数据中的空值
  from bi_req.temp_gl_20200724_hm t
  left join (select t.device_number, sum(t.fee) fee  --通过sum()可以将多出的number对应的fee合计在一起，因为1/2/3g换4/5g会导致一个number对应两个fee
               from emp t
              where t.month_id = '202001'    --关联指标池时一定要先写账期，不能跑完指标池再写账期
              group by t.device_number) t1
    on t.device_number = t1.device_number;  --左关联

另一种写法

select t.device_number
  from bi_req.temp_gl_20200724_hm t,
       (select t.device_number, t.fee
          from emp t
         where t.month_id = '202001') t1
 where t.device_number = t1.device_number(+);

显示多个账期

select '202001' month_id, t.device_number, nvl(t1.fee, 0) fee
  from bi_req.temp_gl_20200724_hm t
  left join (select t.device_number, sum(t.fee) fee
               from emp t
              where t.month_id = '202001'
              group by t.device_number) t1
    on t.device_number = t1.device_number;

第三阶段：

显示一月账期的收入：

create table bi_req.temp_gl_20200724_fee as 
select '202001' month_id, t.device_number, nvl(t1.fee, 0) fee
  from bi_req.temp_gl_20200724_hm t
  left join (select t.device_number, sum(t.fee) fee
               from emp t
              where t.month_id = '202001'
              group by t.device_number) t1
    on t.device_number = t1.device_number;

第四阶段：

依次将二月到六月的账期数据插入到第二阶段建好的表中

insert into bi_req.temp_gl_20200724_fee       --insert into 插入数据
  select '202002' month_id, t.device_number, nvl(t1.fee, 0) fee
    from bi_req.temp_gl_20200724_hm t
    left join (select t.device_number, sum(t.fee) fee
                 from emp t
                where t.month_id = '202002'
                group by t.device_number) t1
      on t.device_number = t1.device_number;
commit;                                        --因为insert into完成后事务并未提交，所以要commit提交

第五阶段：

验证数据是否重复：

select t.month_id, count(1), count(distinct t.device_number), sum(t.fee)
  from bi_req.temp_gl_20200724_fee t
 group by t.month_id
 order by t.month_id;

第六阶段：

优化显示：将数据横向展示

select t.device_number,
       sum(case t.month_id when '202001' then t.fee else 0 end) as "202001" ,
       max(case t.month_id when '202002' then t.fee else 0 end) as "202002" ,
       max(case t.month_id when '202003' then t.fee else 0 end) as "202003" ,
       max(case t.month_id when '202004' then t.fee else 0 end) as "202004" ,
       max(case t.month_id when '202005' then t.fee else 0 end) as "202005" ,
       max(case t.month_id when '202006' then t.fee else 0 end) as "202006"
  from bi_req.temp_gl_20200724_fee t
 group by t.device_number;