需求：计算当天

  • 全国所有订单信息

  • 全国、一级商品分类订单信息

  • 全国、二级商品分类订单信息

  • 大区所有订单信息

  • 大区、一级商品分类订单信息

  • 大区、二级商品分类订单信息

  • 城市所有订单信息

  • 城市、一级商品分类订单信息

  • 城市、二级商品分类订单信息

用到的表：

  • dws.dws_trade_orders_w

ADS层建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- ADS层订单分析表
DROP TABLE IF EXISTS ads.ads_trade_order_analysis;
create table if not exists ads.ads_trade_order_analysis(
  areatype string, -- 区域范围：区域类型（全国、大 区、城市）
  regionname string, -- 区域名称
  cityname string, -- 城市名称
  categorytype string, -- 商品分类类型（一级、二级）
  category1 string, -- 商品一级分类名称
  category2 string, -- 商品二级分类名称
  totalcount bigint, -- 订单数量
  total_productnum bigint, -- 商品数量
  totalmoney double -- 支付金额
)
partitioned by (dt string)
row format delimited fields terminated by ',';

ADS层加载数据

创建/data/script/trade/ads_load_trade_order_analysis.sh

备注：1笔订单，有多个商品；多个商品有不同的分类；这会导致一笔订单有多个分类，它们是分别统计的；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
#！/bin/bash
source /etc/profile

if [ -n "$1" ]
then
  do_date=$1
else
  do_date=`date -d "-1 day" +%F`
fi

sql="
with mid_orders as (
  select regionname, cityname, firstname category1, secondname category2,
  count(distinct orderid) as totalcount,
  sum(productsnum) as total_productnum,
  sum(paymoney) as totalmoney
  from dws.dws_trade_orders_w
  where dt='$do_date'
  group by regionname, cityname, firstname, secondname
)

insert overwrite table ads.ads_trade_order_analysis partition(dt='$do_date')
select '全国' as areatype, '' as regionname, '' as cityname, '' as categorytype, '' as category1, '' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders

union all

select '全国' as areatype, '' as regionname, '' as cityname, '一级' as categorytype, category1, '' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by category1

union all

select '全国' as areatype, '' as regionname, '' as cityname, '二级' as categorytype, '' as category1, category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by category2

union all

select '大区' as areatype, regionname, '' as cityname, '' as categorytype, '' as category1, '' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by regionname

union all

select '大区' as areatype, regionname, '' as cityname,'一级' as categorytype, category1, '' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by regionname, category1

union all

select '大区' as areatype, regionname, '' as cityname, '二级' as categorytype, '' as category1, category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders group by regionname, category2

union all

select '城市' as areatype, '' as regionname, cityname, '' as categorytype, '' as category1, '' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by cityname

union all

select '城市' as areatype, '' as regionname, cityname, '一级' as categorytype, category1, '' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by cityname, category1

union all

select '城市' as areatype, '' as regionname, cityname, '二级' as categorytype, '' as category1, category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by cityname, category2;
"

hive -e "$sql"

备注：由于在dws.dws_trade_orders_w中，一笔订单可能有多条记录，所以在统计订单数量的时候要用count(distinct orderid)