复杂SQL查询和可视化报表构建

2022-04-11 18:31:11

场景介绍AnalyticDB MySQL数据开发流程。
更多AnalyticDB MySQL相关至阿里云体验实验室

简介

目标是让云上数据仓库用户及开发者通过简单的步骤体验基于AnalyticDB MySQL版和DMS构建云原生数据仓库的主要流程，活动将通过实例的开通、结构与数据的初始化、报表的开发、报表可视化等环节，用3个具体的应用场景来体验AnalyticDB MySQL版在新零售场景下的交互查询和ETL计算速度，以及通过DMS进行数据仓库数据报表开发的流程。

场景中提供的数据集是一个零售场景的模拟数据，包括客户信息、订单记录、货物信息、国家地域信息等内容，数据总量10GB，最大数据表记录数为5999万条。

产品简介

云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库，高度兼容MySQL协议以及SQL:2003 语法标准，可以对海量数据进行即时的多维分析透视和业务探索，快速构建企业云上数据仓库。查看产品详情。

数据管理DMS是基于阿里巴巴集团十余年的数据库服务平台的云版本，提供免安装、免运维、即开即用、多种数据库类型与多种环境统一的web数据库管理终端；可以为企业用户快速复制搭建与阿里集团同等安全、高效、规范的数据库DevOps研发流程、数仓开发解决方案。查看产品详情。

场景化数据查询分析

地址：https://dms.aliyun.com/
路径：全部功能-SQLConsole-单库查询
场景化SQL脚本如下：

#### 1）统计半年内全球各地区销售金额构成。0.5秒, 用饼状图图展示。
select
    r_name   //地区
  ,sum(o_totalprice) totalprice     //交易金额
from
  (
    select
      r_name,
      o_totalprice
    from
      customer
      ,orders
      ,nation
      ,region
    where
      c_custkey = o_custkey
      and c_nationkey=n_nationkey
      and n_regionkey=r_regionkey
    and o_orderdate < date ‘1993-09-23‘  // 谓词过滤：指定时间
    and o_orderdate > date ‘1993-03-23‘
  ) a
  group by  r_name
  order by r_name ;

 #### 2）统计某类目商品每天订单量以及订单金额，按照日期排序。 1秒。用折现图展示
select  
    o_orderdate,   //下单日期
    count(distinct o_orderkey), //订单数
    sum(l_extendedprice*(1-l_discount)) as revenue  //订单金额 
from  
    customer,orders,lineitem 
where  
    c_mktsegment = ‘MACHINERY‘     // 谓词过滤
    and c_custkey = o_custkey  
    and l_orderkey = o_orderkey  
    and o_orderdate < date ‘1995-03-23‘
    and o_orderdate > date ‘1995-02-23‘ // 谓词过滤 指定时间
    and l_shipdate > date ‘1995-03-23‘  
    and l_shipdate < date ‘1996-03-23‘
group by //分组操作   
    o_orderdate //订单日期   
order by 
    o_orderdate;

#### 3）统计某个地区零件供货商收入。1秒，用柱状图展示。
select
        n_name,   //地区
        sum(l_extendedprice * (1 - l_discount)) as revenue  //收入 
from
        customer,
        orders,
        lineitem,
        supplier,
        nation,
        region   //六表连接  
where
        c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and l_suppkey = s_suppkey
        and c_nationkey = s_nationkey
        and s_nationkey = n_nationkey
        and n_regionkey = r_regionkey
        and r_name = ‘EUROPE‘  // 谓词过滤：指定地区
        and o_orderdate >= date ‘1996-01-01‘  //谓词过滤：指定时间
        and o_orderdate < date ‘1996-01-01‘ + interval ‘1‘ year
        and l_shipdate > date ‘1996-02-23‘  
          and l_shipdate < date ‘1996-03-23‘
group by
        n_name
order by   // TopN：按收入降序排序，注意分组和排序子句不同  
        revenue desc
limit 10

数据开发

将查询分析的数据存储到新的表中，后续对于相关数据的查询分析就可以用一个简单的SQL来完成

地址：https://dms.aliyun.com/?#to=data-analysis
路径：全部功能-数据工厂-任务编排，新建任务流实例，选择“单实例SQL”节点来创建任务。
任务配置好之后，点击试运行即可运行。
同时我们可以选择点击空白处进行周期调度运行的配置。
任务节点SQL脚本如下：

#### 1）统计半年内全球各地区销售金额构成。0.5秒, 用饼状图图展示。

create table sum_region_price as 
select
    r_name   //地区
  ,sum(o_totalprice) totalprice     //交易金额
from
  (
    select
      r_name,
      o_totalprice
    from
      customer
      ,orders
      ,nation
      ,region
    where
      c_custkey = o_custkey
      and c_nationkey=n_nationkey
      and n_regionkey=r_regionkey
    and o_orderdate < date ‘1993-09-23‘  // 谓词过滤：指定时间
    and o_orderdate > date ‘1993-03-23‘
  ) a
  group by  r_name
  order by r_name ;

 #### 2）统计某类目商品每天订单量以及订单金额，按照日期排序。 1秒。用折现图展示

 create table items_revenue as 
select  
    o_orderdate,   //下单日期
    count(distinct o_orderkey) as order_num, //订单数
    sum(l_extendedprice*(1-l_discount)) as revenue  //订单金额 
from  
    customer,orders,lineitem 
where  
    c_mktsegment = ‘MACHINERY‘     // 谓词过滤
    and c_custkey = o_custkey  
    and l_orderkey = o_orderkey  
    and o_orderdate < date ‘1995-03-23‘
    and o_orderdate > date ‘1995-02-23‘ // 谓词过滤 指定时间
    and l_shipdate > date ‘1995-03-23‘  
    and l_shipdate < date ‘1996-03-23‘
group by 
    o_orderdate 
order by 
    o_orderdate;

#### 3）统计某个地区零件供货商收入。1秒，用柱状图展示。

create table region_seller_cash as 
select
        n_name,   //地区
        sum(l_extendedprice * (1 - l_discount)) as revenue  //收入 
from
        customer,
        orders,
        lineitem,
        supplier,
        nation,
        region   //六表连接  
where
        c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and l_suppkey = s_suppkey
        and c_nationkey = s_nationkey
        and s_nationkey = n_nationkey
        and n_regionkey = r_regionkey
        and r_name = ‘EUROPE‘  // 谓词过滤：指定地区
        and o_orderdate >= date ‘1996-01-01‘  //谓词过滤：指定时间
        and o_orderdate < date ‘1996-01-01‘ + interval ‘1‘ year
        and l_shipdate > date ‘1996-02-23‘  
          and l_shipdate < date ‘1996-03-23‘
group by
        n_name
order by   // TopN：按收入降序排序，注意分组和排序子句不同  
        revenue desc

数据可视化

将数据开发的报表进行可视化报表图形化呈现
数据可视化访问地址：https://dms.aliyun.com/?#to=data-visual
推荐步骤：

进入dms：https://dms.aliyun.com
通过SQLConsole将下列sql逐条执行查询后，点击数据可视化即可进入图表编辑界面。
SQL脚本如下：

#### 1）统计半年内全球各地区销售金额构成。0.5秒, 用饼状图图展示。

select * from sum_region_price;

 #### 2）统计某类目商品每天订单量以及订单金额，按照日期排序。 1秒。用折现图展示

 select * from items_revenue;

#### 3）统计某个地区零件供货商收入。1秒，用柱状图展示。

select * from  region_seller_cash;

在该界面点击右上角的“配置新看板”按钮，即可将该图表自动保存并加入到仪表盘中。

依次制作四个图表，放入新建中的仪表盘中即可得到下图效果。

复杂SQL查询和可视化报表构建

码农公寓

简介

产品简介

场景化数据查询分析

数据开发

数据可视化

相关文章