天天看點
傳回

複雜SQL查詢和可視化報表建構

場景介紹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
複雜SQL查詢和可視化報表建構
複雜SQL查詢和可視化報表建構

資料可視化

将資料開發的報表進行可視化報表圖形化呈現

資料可視化通路位址：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查詢和可視化報表建構

在該界面點選右上角的“配置新看闆”按鈕，即可将該圖表自動儲存并加入到儀表盤中。

複雜SQL查詢和可視化報表建構

依次制作四個圖表，放入建立中的儀表盤中即可得到下圖效果。

複雜SQL查詢和可視化報表建構
上一篇: CentOS7安裝openshift3.9叢集CentOS7安裝openshift叢集
下一篇: 在Spring生态中玩轉RocketMQ在Spring生态中玩轉RocketMQ