创建视图来简化美观查询

一、创建视图

对于数据分析师来说，创建视图可以实现对数据的预处理，后续的查询可以基于视图来完成而不是在原始的数据表上做查询。

1.建表语句

create table sales_2020
(
sale_id bigint not null auto_increment,
sale_date date not null,
sale_area varchar(255) not null,
sale_channel varchar(255) not null,
order_no varchar(255) not null,
brand varchar(255) not null,
unit_price int not null,
sale_amount int not null,
primary key (sale_id)
);

2.创建视图

create or replace view v_sales_2020
as
select sale_date,
       quarter(sale_date) as sale_quarter,
       month(sale_date) as sale_month,
       (weekday(sale_date) + 1) % 7 as week_day,
       sale_area,
       sale_channel,
       brand,
       unit_price,
       sale_amount,
       (unit_price * sale_amount) as total,
       case when unit_price < 300 then '低端'
            when unit_price < 800 then '中端'
            else '高端'
        end as tag
    from sales_2020;

查询案例

1.查询2020年月度销售额

select sale_month as 月份, sum(total) as 销售额
from v_sales_2020
group by sale_month;

2.计算月环比

select 月份,
       本月销售额,
       上月销售额,
       concat(round((本月销售额 - 上月销售额) / 上月销售额 * 100, 2), '%') as 环比
from (select 月份,
             销售额 as 本月销售额,
             lag(销售额) over (order by 月份 asc) as 上月销售额
                from (select sale_month as 月份,
                             sum(total) as 销售额
                from v_sales_2020
                 group by sale_month) as t1) as t2;

3.查询各品牌销售贡献占比

select t2.brand, round(品牌数 / 总订单数, 2) as 占比
from (select sum(unit_price * sale_amount) as 总订单数 from sales_2020) as t1,
(select brand, sum(unit_price * sale_amount) as 品牌数
from sales_2020
group by brand) as t2;

4.查询各地区的月度销售额

select sale_area, month(sale_date), sum(unit_price * sale_amount)
from sales_2020
group by sale_area, month(sale_date)
order by sale_area;

5.窄表变宽表

select 月份
     , sum(case 销售区域 when '北京' then 销售额 else 0 end) as 北京
     , sum(case 销售区域 when '上海' then 销售额 else 0 end) as 上海
     , sum(case 销售区域 when '福建' then 销售额 else 0 end) as 福建
     , sum(case 销售区域 when '广东' then 销售额 else 0 end) as 广东
     , sum(case 销售区域 when '浙江' then 销售额 else 0 end) as 浙江
     , sum(case 销售区域 when '安徽' then 销售额 else 0 end) as 安徽
     , sum(case 销售区域 when '江苏' then 销售额 else 0 end) as 江苏
  from (select sale_month as 月份
             , sale_area as 销售区域
             , sum(total) as 销售额
          from v_sales_2020
         group by sale_month, sale_area) as temp
 group by 月份;

6.查询各渠道的各品牌销量

select sale_channel, brand, count(*)
from sales_2020
group by sale_channel, brand
order by sale_channel;

7.查询不同售价区间商品销量占比

select tag, 数量 / 总数 as 占比
from
(select tag, count(tag) as 数量
from v_sales_2020
group by tag) as t1,
(select count(*) as 总数 from v_sales_2020) as t2;