睡觉万岁

0%

发表于

滴滴出行

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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
-- 1.数据仓库构建
-- 1.1创建ods库
create database if not exists ods_didi;
-- 1.2创建dw数据库
create database if not exists dw_didi;
-- 1.3创建app数据库
create database if not exists app_didi;

use ods_didi;

-- 2.在ods层创建表
-- 2.1创建订单结构表
-- 创建用户订单表结构
create table if not exists ods_didi.t_user_order
(
    orderId         string comment '订单id',
    telephone       string comment '打车用户手机',
    lng             string comment '用户发起打车的经度',
    lat             string comment '用户发起打车的纬度',
    province        string comment '所在省份',
    city            string comment '所在城市',
    es_money        double comment '预估打车费用',
    gender          string comment '用户信息 - 性别',
    profession      string comment '用户信息 - 行业',
    age_range       string comment '年龄段（70后、80后、...）',
    tip             double comment '小费',
    subscribe       int comment '是否预约（0 - 非预约、1 - 预约）',
    sub_time        string comment '预约时间',
    is_agent        int comment '是否代叫（0 - 本人、1 - 代叫）',
    agent_telephone string comment '预约人手机',
    order_time      string comment '订单时间'
)
    partitioned by (dt string comment '时间分区')
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

-- ods创建取消订单表
create table if not exists ods_didi.t_user_cancel_order
(
    orderId        string comment '订单ID',
    cstm_telephone string comment '客户联系电话',
    lng            string comment '取消订单的经度',
    lat            string comment '取消订单的纬度',
    province       string comment '所在省份',
    city           string comment '所在城市',
    es_distance    double comment '预估距离',
    gender         string comment '性别',
    profession     string comment '行业',
    age_range      string comment '年龄段',
    reason         int comment '取消订单原因（1 - 选择了其他交通方式、2 - 与司机达成一致，取消订单、3 - 投诉司机没来接我、4 - 已不需要用车、5 - 无理由取消订单）',
    cancel_time    string comment '取消时间'
)
    partitioned by (dt string comment '时间分区')
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

-- ods创建订单表支付表
create table if not exists ods_didi.t_user_pay_order
(
    id                         string comment '支付订单ID',
    orderId                    string comment '订单ID',
    lng                        string comment '目的地的经度（支付地址）',
    lat                        string comment '目的地的纬度（支付地址）',
    province                   string comment '省份',
    city                       string comment '城市',
    total_money                double comment '车费总价',
    real_pay_money             double comment '实际支付总额',
    passenger_additional_money double comment '乘客额外加价',
    base_money                 double comment '车费合计',
    has_coupon                 int comment '是否使用优惠券（0 - 不使用、1 - 使用）',
    coupon_total               double comment '优惠券合计',
    pay_way                    int comment '支付方式（0 - 微信支付、1 - 支付宝支付、3 - QQ钱包支付、4 - 一网通银行卡支付）',
    mileage                    double comment '里程（单位公里）',
    pay_time                   string comment '支付时间'
)
    partitioned by (dt string comment '时间分区')
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- ods创建用户评价表
create table if not exists ods_didi.t_user_evaluate
(
    id                  string comment '评价日志唯一ID',
    orderId             string comment '订单ID',
    passenger_telephone string comment '用户电话',
    passenger_province  string comment '用户所在省份',
    passenger_city      string comment '用户所在城市',
    eva_level           int comment '评价等级（1 - 一颗星、... 5 - 五星）',
    eva_time            string comment '评价时间'
)
    partitioned by (dt string comment '时间分区')
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 创建数据仓库 导入数据
load data local inpath '/export/data/didi/order.csv' into table ods_didi.t_user_order partition (dt = '2020-04-12');

load data local inpath '/export/data/didi/cancel_order.csv' into table ods_didi.t_user_cancel_order partition (dt = '2020-04-12');

load data local inpath '/export/data/didi/pay.csv' into table ods_didi.t_user_pay_order partition (dt = '2020-04-12');

load data local inpath '/export/data/didi/evaluate.csv' into table ods_didi.t_user_evaluate partition (dt = '2020-04-12');

-- 3.在dw层进行数据预处理
use dw_didi;
-- 创建宽表语句
create table if not exists dw_didi.t_user_order_wide
(
    orderId          string comment '订单id',
    telephone        string comment '打车用户手机',
    lng              string comment '用户发起打车的经度',
    lat              string comment '用户发起打车的纬度',
    province         string comment '所在省份',
    city             string comment '所在城市',
    es_money         double comment '预估打车费用',
    gender           string comment '用户信息 - 性别',
    profession       string comment '用户信息 - 行业',
    age_range        string comment '年龄段（70后、80后、...）',
    tip              double comment '小费',
    subscribe        int comment '是否预约（0 - 非预约、1 - 预约）',
    subscribe_name   string comment '是否预约名称',
    sub_time         string comment '预约时间',
    is_agent         int comment '是否代叫（0 - 本人、1 - 代叫）',
    is_agent_name    string comment '是否代缴名称',
    agent_telephone  string comment '预约人手机',
    order_time       string comment '订单时间',
    order_date       string comment '订单时间，yyyy-MM-dd',
    order_year       string comment '年',
    order_month      string comment '月',
    order_day        string comment '日',
    order_hour       string comment '小时',
    order_time_range string comment '时间段'

)
    partitioned by (dt string comment '2020-04-12')
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

-- 预处理sql语句 用户订单处理
insert overwrite  table  dw_didi.t_user_order_wide partition (dt='2020-04-12')
select orderid,
       telephone,
       lng,
       lat,
       province,
       city,
       es_money,
       gender,
       profession,
       age_range,
       tip,
       subscribe,
--        if(nvl(subscribe, 0) = 0, '非预约', '预约')                   as subscribe_name,
       case
           when subscribe = 0 or (subscribe is null) then '非预约'
           when subscribe = 1 then '预约'
           end                                                             as subscribe_name,
       date_format(sub_time, 'yyyy-MM-dd')                                 as sub_time,
       is_agent,
       case
           when is_agent = 0 or (subscribe is null) then '本人'
           when is_agent = 1 then '代叫'
           end                                                             as is_agent_name,
       agent_telephone,
--        substr(order_time, 1, 4)                                      as year,
       date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')       as order_time,
       date_format(order_time, 'yyyy-MM-dd')                               as order_data,
       year(date_format(order_time, 'yyyy-MM-dd'))                         as order_year,
       month(date_format(order_time, 'yyyy-MM-dd'))                        as order_month,
       day(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss'))  as order_day,
       hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) as order_hour,
       case
           when hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) >= 1 and
                hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) < 5 then '凌晨'
           when hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) >= 5 and
                hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) < 8 then '早上'
           when hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) >= 8 and
                hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) < 11 then '上午'
           when hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) >= 11 and
                hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) < 13 then '中午'
           when hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) >= 13 and
                hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) < 17 then '下午'
           when hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) >= 17 and
                hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) < 19 then '晚上'
           when hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) >= 19 and
                hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) < 20 then '半夜'
           when hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) >= 20 and
                hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) < 24 then '深夜'
           when hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) >= 0 and
                hour(date_format(concat(order_time, ':00'), 'yyyy-MM-dd HH:mm:ss')) < 1 then '凌晨'
           end
--        date_format(order_time, 'yyyy-MM-dd HH:mm:ss'),
from ods_didi.t_user_order
where length(order_time) >= 8
and dt='2020-04-12';


create table if not exists dw_didi.t_user_cancel_order(
    orderId string,
    Profession string,
    age_range string,
    Reason string,
    cancel_time string
)
partitioned by (dt string comment '2020-04-12')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

insert overwrite table dw_didi.t_user_cancel_order partition(dt='2020-04-12')
select
    orderId,
    profession,
    age_range,
    reason,
    cancel_time
from
    ods_didi.t_user_cancel_order
where dt='2020-04-12'

create table if not exists dw_didi.t_user_pay_order(
   id string comment '支付订单ID',
   orderId string comment '订单ID',
   real_pay_money double comment '实际支付总额',
   has_coupon int comment '是否使用优惠券（0 - 不使用、1 - 使用）',
   pay_way int comment '支付方式（0-微信支付、1-支付宝支付、3-QQ钱包支付、4- 一网通银行卡支付）',
   mileage double comment '里程（单位公里）',
   pay_time string comment '支付时间'
)
    partitioned by (dt string comment '2020-04-12')
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

insert overwrite table dw_didi.t_user_pay_order partition(dt='2020-04-12')
select
    id  ,
    orderId ,
    real_pay_money ,
    has_coupon ,
    pay_way  ,
    mileage ,
    pay_time
from
    ods_didi.t_user_pay_order
where dt='2020-04-12'

create table if not exists dw_didi.t_user_evaluate(
     id string comment '评价日志唯一ID',
     orderId string comment '订单ID',
     eva_level int comment '评价等级（1 - 一颗星、... 5 - 五星）',
     eva_time string comment '评价时间'
)
    partitioned by (dt string comment '时间分区')
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

insert overwrite table dw_didi.t_user_evaluate partition(dt='2020-04-12')
select
    id,
    orderId,
    eva_level,
    eva_time
from
    ods_didi.t_user_evaluate
where dt='2020-04-12'

-------------本文结束感谢您的阅读-------------
老板你好，讨口饭吃