场景样例 SQL

下方案例使用 LAS 的样例数据 TPC-DS 数据集作为基础数据源，构造出基本的离线数仓场景。
设定业务需求： 根据客户表、商店表、日期表以及销售表数据，进行相关的业务计算：

• 计算分城市每个城市总销售额

• 设定每个城市/每个商店顾客消费总额的 Top N

进入【数据管理-表管理-导入样例数据】，将 Schema 命名为： tpc_ds_demo。该操作会创建对应的元数据及导入数据，即直接构建本样例中的 ODS 层数据。相关的数据字典可参考：导入样例数据。

将 store_sales 数据过滤写入到销售信息表中

2.1 通过【数据管理-表管理-创建表】或者【查询分析-离线 SQL】创建 LAS 内表作为结果表。

-- 销售信息表
CREATE TABLE `tpc_ds_demo`.`dwd_trd_sales_detail_di`(
  `store_id` BIGINT COMMENT '商店id',
  `customer_id` BIGINT COMMENT '顾客id',
  `item_id` BIGINT COMMENT '商品id',
  `order_id` BIGINT COMMENT '订单id',
  `order_price` DOUBLE COMMENT '订单价格',
  `ss_promo_sk` BIGINT COMMENT '促销信息',
  `ss_quantity` DOUBLE COMMENT '销售数量',
  `ss_coupon_amt` DOUBLE COMMENT '打折券',
  `ss_net_paid` DOUBLE COMMENT '消费额',
  `ss_net_paid_inc_tax` DOUBLE COMMENT '消费税',
  `ss_net_profit` DOUBLE COMMENT '盈利',
  `ss_sold_date_sk` BIGINT COMMENT '销售日期')
PARTITIONED BY (`dt` STRING COMMENT '写入日期')

2.2 在【查询分析-离线 SQL】将 store_sales 数据分区导入，写入 LAS 内表 （目前建表语句 + 插入语句得分开两个任务）

INSERT
  OVERWRITE TABLE tpc_ds_demo.dwd_trd_sales_detail_di PARTITION (dt = '2022-06-17')
SELECT
  ss_store_sk store_id,
  ss_item_sk item_id,
  ss_customer_sk customer_id,
  ss_net_profit,
  ss_ticket_number order_id,
  ss_wholesale_cost order_price,
  ss_promo_sk,
  ss_quantity,
  ss_coupon_amt,
  ss_net_paid,
  ss_net_paid_inc_tax,
  ss_sold_date_sk
FROM
  tpc_ds_demo.store_sales;

2.3 待离线 SQL 任务成功执行后，即可通过【查询分析-离线 SQL】检查 LAS 内表中数据情况。

SELECT * FROM tpc_ds_demo.dwd_trd_sales_detail_di where dt='2022-06-17';

过滤维度表中的脏数据, 如 company 为 null 的数据

3.1 通过【数据管理-表管理-创建表】或者【查询分析-离线 SQL】创建 LAS 内表作为结果表。

-- 客户信息表
CREATE TABLE `tpc_ds_demo`.`dim_trd_customer_detail_df`(
  `customer_id` STRING COMMENT '客户 id',
  `customer_name` STRING COMMENT '客户姓名',
  `c_birth_country` STRING COMMENT '客户出生国家',
  `c_first_shipto_date_sk` BIGINT COMMENT '运输时间',
  `c_first_sales_date_sk` BIGINT COMMENT '销售时间',
  `c_preferred_cust_flag` STRING COMMENT '偏好标记',
  `c_email_address` STRING COMMENT '客户邮箱地址',
  `c_birth_year` INT COMMENT '客户出生年份')
PARTITIONED BY (`dt` STRING COMMENT '写入日期')

-- 商店信息表
CREATE TABLE `tpc_ds_demo`.`dim_trd_store_detail_df`(
  `store_id` BIGINT COMMENT '商店 id',
  `store_name` STRING COMMENT '商店名称',
  `s_rec_start_date` DATE COMMENT '商店开业时间',
  `s_rec_end_date` DATE COMMENT '商店停业时间',
  `working_time` STRING COMMENT '商店开门时间',
  `s_county` STRING COMMENT '商店所属国家',
  `s_tax_precentage` DOUBLE COMMENT '商店税率',
  `s_number_employees` INT COMMENT '商店雇员数量',
  `s_manager` STRING COMMENT '商店经理姓名',
  `s_company_id` INT COMMENT '商店所属公司 id',
  `s_company_name` STRING COMMENT '商店所属公司名称',
  `s_zip` STRING COMMENT '商店邮政编号',
  `s_city` STRING COMMENT '商店所在城市')
PARTITIONED BY (`dt` STRING COMMENT '写入日期')

-- 日期信息表
CREATE TABLE `tpc_ds_demo`.`dim_trd_date_less_df`(
  `d_date_sk` BIGINT COMMENT '日期 id',
  `d_date` DATE COMMENT '日期',
  `d_week_seq` INT COMMENT '该年第几周',
  `d_quarter_seq` INT COMMENT '该年第几季度',
  `d_day_name` STRING COMMENT '哪年第几天',
  `d_quarter_name` STRING COMMENT '哪年第几季度',
  `d_holiday` STRING COMMENT '节日',
  `d_weekend` STRING COMMENT '周末',
  `d_year` INT COMMENT '年份')
PARTITIONED BY (`dt` STRING COMMENT '写入日期')

3.2 在【查询分析-离线 SQL】将维表数据过滤后分区导入，写入 LAS 内表。

INSERT
  OVERWRITE TABLE tpc_ds_demo.dim_trd_customer_detail_df PARTITION (dt = '2022-06-17')
SELECT
  c_customer_sk customer_id,
  concat(c_salutation, c_first_name, c_last_name) customer_name,
  c_first_shipto_date_sk,
  c_first_sales_date_sk,
  c_preferred_cust_flag,
  c_email_address,
  c_birth_country,
  c_birth_year
FROM
  tpc_ds_demo.customer
WHERE
  c_customer_sk IS NOT NULL;
  
INSERT
  OVERWRITE TABLE tpc_ds_demo.dim_trd_store_detail_df PARTITION (dt = '2022-06-17')
SELECT
  s_store_sk store_id,
  s_store_name store_name,
  s_rec_start_date,
  s_rec_end_date,
  s_hours working_time,
  s_county,
  s_tax_precentage,
  s_number_employees,
  s_manager,
  s_company_id,
  s_company_name,
  s_zip,
  s_city
FROM
  tpc_ds_demo.store
WHERE
  s_company_id IS NOT NULL;
  
INSERT
  OVERWRITE TABLE tpc_ds_demo.dim_trd_date_less_df PARTITION (dt = '2022-06-17')
SELECT
  d_date_sk,
  d_date,
  d_week_seq,
  d_quarter_seq,
  d_holiday,
  d_weekend,
  d_quarter_name,
  d_day_name,
  d_year
FROM
  tpc_ds_demo.date_dim
WHERE
  d_year <= 2020
  AND d_year >= 1920;

3.3 待离线 SQL 任务成功执行后，即可通过【查询分析-离线 SQL】检查 LAS 内表中数据情况。

SELECT * FROM tpc_ds_demo.dim_trd_customer_detail_df where dt='2022-06-17';
SELECT * FROM tpc_ds_demo.dim_trd_store_detail_df where dt='2022-06-17';
SELECT * FROM tpc_ds_demo.dim_trd_date_less_df where dt='2022-06-17';

按日期等维度聚合后汇总，并将日期信息补齐

4.1 通过【数据管理-表管理-创建表】或者【查询分析-离线 SQL】创建 LAS 内表作为结果表。

-- 订单维度轻度汇总表
CREATE TABLE `tpc_ds_demo`.`dwm_trd_order_metrics_di`(
  `store_id` BIGINT COMMENT '商店 id',
  `customer_id` BIGINT COMMENT '客户 id',
  `order_id` BIGINT COMMENT '订单 id',
  `d_date` STRING COMMENT '销售日期',
  `ss_promo_sk` BIGINT COMMENT '促销信息',
  `order_price` DOUBLE COMMENT '订单金额',
  `ss_quantity` DOUBLE COMMENT '订单数量',
  `ss_coupon_amt` DOUBLE COMMENT '折扣券',
  `ss_net_paid` DOUBLE COMMENT '消费额',
  `ss_net_paid_inc_tax` DOUBLE COMMENT '消费税',
  `ss_net_profit` DOUBLE COMMENT '盈利')
PARTITIONED BY (`dt` STRING COMMENT '写入日期')

4.2 在【查询分析-离线 SQL】将聚合数据数据导入，写入 LAS 内表。

INSERT
  OVERWRITE TABLE tpc_ds_demo.dwm_trd_order_metrics_di PARTITION (dt = '2022-06-17')
SELECT
  store_id,
  customer_id,
  order_id,
  d_date,
  ss_promo_sk,
  order_price,
  ss_quantity,
  ss_coupon_amt,
  ss_net_paid,
  ss_net_paid_inc_tax,
  ss_net_profit
 FROM(
    SELECT
      store_id,
      customer_id,
      order_id,
      ss_sold_date_sk,
      ss_promo_sk,
      sum(nvl(order_price, 0)) as order_price,
      sum(ss_quantity) as ss_quantity,
      sum(ss_coupon_amt) as ss_coupon_amt,
      sum(ss_net_paid) as ss_net_paid,
      sum(ss_net_paid_inc_tax) as ss_net_paid_inc_tax,
      sum(ss_net_profit) as ss_net_profit
    FROM
      tpc_ds_demo.dwd_trd_sales_detail_di
    WHERE
      dt = '2022-06-17'
    GROUP BY
      store_id,
      customer_id,
      order_id,
      ss_sold_date_sk,
      ss_promo_sk
  ) t1
  JOIN (
    SELECT
      d_date_sk,
      d_date
    FROM
      tpc_ds_demo.dim_trd_date_less_df
    WHERE
      dt = '2022-06-17'
  ) t2 ON t1.ss_sold_date_sk = t2.d_date_sk;

4.3 待离线 SQL 任务成功执行后，即可通过【查询分析-离线 SQL】检查 LAS 内表中数据情况。

SELECT * FROM tpc_ds_demo.dwm_trd_order_metrics_di where dt='2022-06-17';

计算分城市每个城市总销售额

5.1 通过【数据管理-表管理-创建表】或者【查询分析-离线 SQL】创建 LAS 内表作为结果表。

-- 城市核心交易指标表
CREATE TABLE `tpc_ds_demo`.`dm_trd_city_core_metrics_di`(
  `s_city` STRING COMMENT '城市名',
  `order_price` DOUBLE COMMENT '订单金额',
  `ss_net_profit` DOUBLE COMMENT '利润',
  `profit_ratio` DOUBLE COMMENT '利润率')
PARTITIONED BY (`dt` STRING COMMENT '写入日期')

5.2 在【查询分析-离线 SQL】将维表数据过滤后分区导入，写入 LAS 内表。

INSERT
  OVERWRITE TABLE tpc_ds_demo.dm_trd_city_core_metrics_di PARTITION (dt = '2022-06-17')
SELECT
  s_city,
  sum(order_price) as order_price,
  sum(ss_net_profit) as ss_net_profit,
  sum(ss_net_profit) / sum(order_price) as profit_ratio
FROM
  (
    SELECT
      store_id,
      sum(order_price) as order_price,
      sum(ss_net_profit) as ss_net_profit
    FROM
      tpc_ds_demo.dwm_trd_order_metrics_di
    WHERE
      dt = '2022-06-17'
    GROUP BY
      store_id
  ) t1
  JOIN (
    SELECT
      store_id,
      s_city
    FROM
      tpc_ds_demo.dim_trd_store_detail_df
    WHERE
      dt = '2022-06-17'
  ) t2 ON t1.store_id = t2.store_id
GROUP BY
  s_city;

5.3 待离线 SQL 任务成功执行后，即可通过【查询分析-离线 SQL】检查 LAS 内表中数据情况。

SELECT * FROM tpc_ds_demo.dm_trd_city_core_metrics_di where dt='2022-06-17';

得到每个城市消费总额的 top 100 作为 vip 客户进行展示

6.1 通过【数据管理-表管理-创建表】或者【查询分析-离线 SQL】创建 LAS 内表作为结果表。

-- 头部用户画像表
CREATE TABLE `tpc_ds_demo`.`app_mkt_top_customer_feature_di`(
  `customer_id` STRING COMMENT '客户 id',
  `s_city` STRING COMMENT '城市',
  `order_price` DOUBLE COMMENT '订单金额',
  `customer_name` STRING COMMENT '客户姓名',
  `rank_num` INT COMMENT '排名',
  `c_email_address` STRING COMMENT 'email',
  `c_birth_country` STRING COMMENT '出生国家',
  `age` INT COMMENT '年龄')
COMMENT ''
PARTITIONED BY (`dt` STRING COMMENT '写入日期')

6.2 在【查询分析-离线 SQL】将维表数据过滤后分区导入，写入 LAS 内表。

insert
  OVERWRITE TABLE tpc_ds_demo.app_mkt_top_customer_feature_di PARTITION (dt = '2022-06-17')
select
  *
from
  (
    select
      t3.customer_id,
      s_city,
      order_price_cos as order_price,
      customer_name,
      dense_rank() over (
        partition by s_city
        order by
          order_price_cos desc
      ) as rank_num,
      c_email_address,
      c_birth_country,
      age
    from
      (
        select
          customer_id,
          s_city,
          sum(order_price_cos) order_price_cos
        from
          (
            select
              store_id,
              CAST(customer_id AS string) as customer_id,
              sum(order_price) as order_price_cos
            from
              tpc_ds_demo.dwm_trd_order_metrics_di
            where
              dt = '2022-06-17'
            group by
              store_id,
              customer_id
          ) t1
          JOIN (
            select
              store_id,
              s_city
            from
              tpc_ds_demo.dim_trd_store_detail_df
            where
              dt = '2022-06-17'
          ) t2 ON t1.store_id = t2.store_id
        group by
          customer_id,
          s_city
      ) t3
      JOIN (
        select
          customer_id,
          customer_name,
          c_email_address,
          c_birth_country,
          2022 - c_birth_year as age
        from
          tpc_ds_demo.dim_trd_customer_detail_df
        where
          dt = '2022-06-17'
      ) t4 ON t3.customer_id = t4.customer_id
  ) t5
where
  rank_num <= 100

6.3 待离线 SQL 任务成功执行后，即可通过【查询分析-离线 SQL】检查 LAS 内表中数据情况。

SELECT * FROM tpc_ds_demo.app_mkt_top_customer_feature_di where dt='2022-06-17';