【离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载】
离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载
- 离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载
- 一、交易域用户商品粒度订单最近1日/N日汇总表
- 1.交易域用户商品粒度订单最近1日汇总表
- 2.交易域用户商品粒度订单最近N日汇总表
- 二、交易域优惠券粒度订单最近N日汇总表
- 1.最近30天发布的优惠券的补贴率
- 三、用户域用户粒度登录历史至今汇总表
- 四、DWS层数据装载脚本
- 1.dws层所有建表语句脚本
- 2.1d表格装载脚本
- 1.首日装载脚本
- 2.每日装载脚本
- 3.nd表格装载脚本
- 4.td表格装载脚本
- 1.首日装载脚本
- 2.每日装载脚本
离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载
一、交易域用户商品粒度订单最近1日/N日汇总表
1.交易域用户商品粒度订单最近1日汇总表
- 建表DDL语句
DROP TABLE IF EXISTS dws_trade_user_sku_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_1d
(`user_id` STRING COMMENT '用户id',`sku_id` STRING COMMENT 'sku_id',`sku_name` STRING COMMENT 'sku名称',`category1_id` STRING COMMENT '一级分类id',`category1_name` STRING COMMENT '一级分类名称',`category2_id` STRING COMMENT '一级分类id',`category2_name` STRING COMMENT '一级分类名称',`category3_id` STRING COMMENT '一级分类id',`category3_name` STRING COMMENT '一级分类名称',`tm_id` STRING COMMENT '品牌id',`tm_name` STRING COMMENT '品牌名称',`order_count_1d` BIGINT COMMENT '最近1日下单次数',`order_num_1d` BIGINT COMMENT '最近1日下单件数',`order_original_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单原始金额',`activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日活动优惠金额',`coupon_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日优惠券优惠金额',`order_total_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
) COMMENT '交易域用户商品粒度订单最近1日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_1d'TBLPROPERTIES ('orc.compress' = 'snappy');
- 首日装载数据
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_user_sku_order_1d partition(dt)
selectuser_id,id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_count_1d,order_num_1d,order_original_amount_1d,activity_reduce_amount_1d,coupon_reduce_amount_1d,order_total_amount_1d,dt
from
(selectdt,user_id,sku_id,count(*) order_count_1d,sum(sku_num) order_num_1d,sum(split_original_amount) order_original_amount_1d,sum(nvl(split_activity_amount,0.0)) activity_reduce_amount_1d,sum(nvl(split_coupon_amount,0.0)) coupon_reduce_amount_1d,sum(split_total_amount) order_total_amount_1dfrom dwd_trade_order_detail_incgroup by dt,user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom dim_sku_fullwhere dt='2020-06-14'
)sku
on od.sku_id=sku.id;
- 每日装载
insert overwrite table dws_trade_user_sku_order_1d partition(dt='2020-06-15')
selectuser_id,id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_count,order_num,order_original_amount,activity_reduce_amount,coupon_reduce_amount,order_total_amount
from
(selectuser_id,sku_id,count(*) order_count,sum(sku_num) order_num,sum(split_original_amount) order_original_amount,sum(nvl(split_activity_amount,0)) activity_reduce_amount,sum(nvl(split_coupon_amount,0)) coupon_reduce_amount,sum(split_total_amount) order_total_amountfrom dwd_trade_order_detail_incwhere dt='2020-06-15'group by user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom dim_sku_fullwhere dt='2020-06-15'
)sku
on od.sku_id=sku.id;
2.交易域用户商品粒度订单最近N日汇总表
- 建表DDL语句
DROP TABLE IF EXISTS dws_trade_user_sku_order_nd;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_nd
(`user_id` STRING COMMENT '用户id',`sku_id` STRING COMMENT 'sku_id',`sku_name` STRING COMMENT 'sku名称',`category1_id` STRING COMMENT '一级分类id',`category1_name` STRING COMMENT '一级分类名称',`category2_id` STRING COMMENT '一级分类id',`category2_name` STRING COMMENT '一级分类名称',`category3_id` STRING COMMENT '一级分类id',`category3_name` STRING COMMENT '一级分类名称',`tm_id` STRING COMMENT '品牌id',`tm_name` STRING COMMENT '品牌名称',`order_count_7d` STRING COMMENT '最近7日下单次数',`order_num_7d` BIGINT COMMENT '最近7日下单件数',`order_original_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单原始金额',`activity_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日活动优惠金额',`coupon_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日优惠券优惠金额',`order_total_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单最终金额',`order_count_30d` BIGINT COMMENT '最近30日下单次数',`order_num_30d` BIGINT COMMENT '最近30日下单件数',`order_original_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单原始金额',`activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日活动优惠金额',`coupon_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日优惠券优惠金额',`order_total_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单最终金额'
) COMMENT '交易域用户商品粒度订单最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_nd'TBLPROPERTIES ('orc.compress' = 'snappy');
- 每日装载数据
- 从业务需求方面考虑，没有业务需求需要获取截止到历史某天的最近N天的相关信息。所以不需要进首日全量装载
insert overwrite table dws_trade_user_sku_order_nd partition(dt='2020-06-14')
selectuser_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,sum(if(dt>=date_add('2020-06-14',-6),order_count_1d,0)),sum(if(dt>=date_add('2020-06-14',-6),order_num_1d,0)),sum(if(dt>=date_add('2020-06-14',-6),order_original_amount_1d,0)),sum(if(dt>=date_add('2020-06-14',-6),activity_reduce_amount_1d,0)),sum(if(dt>=date_add('2020-06-14',-6),coupon_reduce_amount_1d,0)),sum(if(dt>=date_add('2020-06-14',-6),order_total_amount_1d,0)),sum(order_count_1d),sum(order_num_1d),sum(order_original_amount_1d),sum(activity_reduce_amount_1d),sum(coupon_reduce_amount_1d),sum(order_total_amount_1d)
from dws_trade_user_sku_order_1d
where dt>=date_add('2020-06-14',-29)
group by user_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name;
二、交易域优惠券粒度订单最近N日汇总表
- 此汇总表只有最近N日的汇总表，是由于业务需求层面这样设计的，只需要最近N日的优惠券信息，所以此表格数据，数据来源于DWD层明细数据，而没有1d维度的数据。
1.最近30天发布的优惠券的补贴率
补贴率：最近30天发布的优惠券相对的订单明细优惠券减免金额之和/最近30天发布的优惠券相对的订单明细的原价金额之和
-
补贴率是一个衍生指标，基于此指标进行拆分指标，形成思维导图
-
使用了最近30天发布的优惠券购买商品下单，此订单必然位于最近30天内。
-
基于思维导图的业务矩阵，上面的两个派生指标 指标的业务过程、统计周期、统计粒度一致，可以将其划分到一个汇总表中，汇总表数据落地以后，优惠券补贴率指标，直接使用此表中数据进行比对就可以了。
-
-
建表语句DDL
DROP TABLE IF EXISTS dws_trade_coupon_order_nd;
CREATE EXTERNAL TABLE dws_trade_coupon_order_nd
(`coupon_id` STRING COMMENT '优惠券id',`coupon_name` STRING COMMENT '优惠券名称',`coupon_type_code` STRING COMMENT '优惠券类型id',`coupon_type_name` STRING COMMENT '优惠券类型名称',`coupon_rule` STRING COMMENT '优惠券规则',`start_date` STRING COMMENT '发布日期',`original_amount_30d` DECIMAL(16, 2) COMMENT '使用下单原始金额',`coupon_reduce_amount_30d` DECIMAL(16, 2) COMMENT '使用下单优惠金额'
) COMMENT '交易域优惠券粒度订单最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_coupon_order_nd'TBLPROPERTIES ('orc.compress' = 'snappy');
- 数据装载，每日增量装载，nd：不需要使用全量装载
insert overwrite table dws_trade_coupon_order_nd partition(dt='2020-06-14')
selectid,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,start_date,sum(split_original_amount),sum(split_coupon_amount)
from
(selectid,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,date_format(start_time,'yyyy-MM-dd') start_datefrom dim_coupon_fullwhere dt='2020-06-14'and date_format(start_time,'yyyy-MM-dd')>=date_add('2020-06-14',-29)
)cou
left join
(selectcoupon_id,order_id,split_original_amount,split_coupon_amountfrom dwd_trade_order_detail_incwhere dt>=date_add('2020-06-14',-29)and dt<='2020-06-14'and coupon_id is not null
)od
on cou.id=od.coupon_id
group by id,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,start_date;
三、用户域用户粒度登录历史至今汇总表
- 建表语句DDL
DROP TABLE IF EXISTS dws_user_user_login_td;
CREATE EXTERNAL TABLE dws_user_user_login_td
(`user_id` STRING COMMENT '用户id',`login_date_last` STRING COMMENT '末次登录日期',`login_count_td` BIGINT COMMENT '累计登录次数'
) COMMENT '用户域用户粒度登录历史至今汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_user_user_login_td'TBLPROPERTIES ('orc.compress' = 'snappy');
- 数据装载-首日
- 装载数据的时候，默认使用1d表的数据，此处没有1d表数据，就使用dwd层dwd_user_login_inc表，但是这个表数据来自于用户行为日志数据，此数据没有历史数据，依据此表格进行历史相关维度数据计算得到的结果不准确，需要关联一下业务维度表格，关联完毕以后，依然拿不到相关维度数据，就直接给其默认值，数仓刚刚上线的时候数据不准确，之前历史数据没有，只能保证数仓上线以后的数据准确。
insert overwrite table dws_user_user_login_td partition(dt='2020-06-14')
selectu.id,nvl(login_date_last,date_format(create_time,'yyyy-MM-dd')),nvl(login_count_td,1)
from
(selectid,create_timefrom dim_user_zipwhere dt='9999-12-31'
)u
left join
(selectuser_id,max(dt) login_date_last,count(*) login_count_tdfrom dwd_user_login_incgroup by user_id
)l
on u.id=l.user_id;
- 数据装载-每日
insert overwrite table dws_user_user_login_td partition(dt='2020-06-15')
selectnvl(old.user_id,new.user_id),if(new.user_id is null,old.login_date_last,'2020-06-15'),nvl(old.login_count_td,0)+nvl(new.login_count_1d,0)
from
(selectuser_id,login_date_last,login_count_tdfrom dws_user_user_login_tdwhere dt=date_add('2020-06-15',-1)
)old
full outer join
(selectuser_id,count(*) login_count_1dfrom dwd_user_login_incwhere dt='2020-06-15'group by user_id
)new
on old.user_id=new.user_id;
四、DWS层数据装载脚本
- DWS层表格分为三部分：1d表，nd表，td表。
- 1d表被nd表和td表依赖，而nd表和td表之间没有依赖关系，需要先装载1d表，1d表装载完毕后，然后再装载td和nd表。
1.dws层所有建表语句脚本
DROP TABLE IF EXISTS dws_trade_user_sku_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_1d
(`user_id` STRING COMMENT '用户id',`sku_id` STRING COMMENT 'sku_id',`sku_name` STRING COMMENT 'sku名称',`category1_id` STRING COMMENT '一级分类id',`category1_name` STRING COMMENT '一级分类名称',`category2_id` STRING COMMENT '一级分类id',`category2_name` STRING COMMENT '一级分类名称',`category3_id` STRING COMMENT '一级分类id',`category3_name` STRING COMMENT '一级分类名称',`tm_id` STRING COMMENT '品牌id',`tm_name` STRING COMMENT '品牌名称',`order_count_1d` BIGINT COMMENT '最近1日下单次数',`order_num_1d` BIGINT COMMENT '最近1日下单件数',`order_original_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单原始金额',`activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日活动优惠金额',`coupon_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日优惠券优惠金额',`order_total_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
) COMMENT '交易域用户商品粒度订单最近1日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_1d'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_sku_order_refund_1d;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_refund_1d
(`user_id` STRING COMMENT '用户id',`sku_id` STRING COMMENT 'sku_id',`sku_name` STRING COMMENT 'sku名称',`category1_id` STRING COMMENT '一级分类id',`category1_name` STRING COMMENT '一级分类名称',`category2_id` STRING COMMENT '一级分类id',`category2_name` STRING COMMENT '一级分类名称',`category3_id` STRING COMMENT '一级分类id',`category3_name` STRING COMMENT '一级分类名称',`tm_id` STRING COMMENT '品牌id',`tm_name` STRING COMMENT '品牌名称',`order_refund_count_1d` BIGINT COMMENT '最近1日退单次数',`order_refund_num_1d` BIGINT COMMENT '最近1日退单件数',`order_refund_amount_1d` DECIMAL(16, 2) COMMENT '最近1日退单金额'
) COMMENT '交易域用户商品粒度退单最近1日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_refund_1d'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_order_1d
(`user_id` STRING COMMENT '用户id',`order_count_1d` BIGINT COMMENT '最近1日下单次数',`order_num_1d` BIGINT COMMENT '最近1日下单商品件数',`order_original_amount_1d` DECIMAL(16, 2) COMMENT '最近1日最近1日下单原始金额',`activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单活动优惠金额',`coupon_reduce_amount_1d` DECIMAL(16, 2) COMMENT '下单优惠券优惠金额',`order_total_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
) COMMENT '交易域用户粒度订单最近1日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_order_1d'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_cart_add_1d;
CREATE EXTERNAL TABLE dws_trade_user_cart_add_1d
(`user_id` STRING COMMENT '用户id',`cart_add_count_1d` BIGINT COMMENT '最近1日加购次数',`cart_add_num_1d` BIGINT COMMENT '最近1日加购商品件数'
) COMMENT '交易域用户粒度加购最近1日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_cart_add_1d'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_payment_1d;
CREATE EXTERNAL TABLE dws_trade_user_payment_1d
(`user_id` STRING COMMENT '用户id',`payment_count_1d` BIGINT COMMENT '最近1日支付次数',`payment_num_1d` BIGINT COMMENT '最近1日支付商品件数',`payment_amount_1d` DECIMAL(16, 2) COMMENT '最近1日支付金额'
) COMMENT '交易域用户粒度支付最近1日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_payment_1d'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_province_order_1d;
CREATE EXTERNAL TABLE dws_trade_province_order_1d
(`province_id` STRING COMMENT '用户id',`province_name` STRING COMMENT '省份名称',`area_code` STRING COMMENT '地区编码',`iso_code` STRING COMMENT '旧版ISO-3166-2编码',`iso_3166_2` STRING COMMENT '新版版ISO-3166-2编码',`order_count_1d` BIGINT COMMENT '最近1日下单次数',`order_original_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单原始金额',`activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单活动优惠金额',`coupon_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单优惠券优惠金额',`order_total_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
) COMMENT '交易域省份粒度订单最近1日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_province_order_1d'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_order_refund_1d;
CREATE EXTERNAL TABLE dws_trade_user_order_refund_1d
(`user_id` STRING COMMENT '用户id',`order_refund_count_1d` BIGINT COMMENT '最近1日退单次数',`order_refund_num_1d` BIGINT COMMENT '最近1日退单商品件数',`order_refund_amount_1d` DECIMAL(16, 2) COMMENT '最近1日退单金额'
) COMMENT '交易域用户粒度退单最近1日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_order_refund_1d'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_traffic_session_page_view_1d;
CREATE EXTERNAL TABLE dws_traffic_session_page_view_1d
(`session_id` STRING COMMENT '会话id',`mid_id` string comment '设备id',`brand` string comment '手机品牌',`model` string comment '手机型号',`operate_system` string comment '操作系统',`version_code` string comment 'app版本号',`channel` string comment '渠道',`during_time_1d` BIGINT COMMENT '最近1日访问时长',`page_count_1d` BIGINT COMMENT '最近1日访问页面数'
) COMMENT '流量域会话粒度页面浏览最近1日汇总表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_traffic_session_page_view_1d'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_traffic_page_visitor_page_view_1d;
CREATE EXTERNAL TABLE dws_traffic_page_visitor_page_view_1d
(`mid_id` STRING COMMENT '访客id',`brand` string comment '手机品牌',`model` string comment '手机型号',`operate_system` string comment '操作系统',`page_id` STRING COMMENT '页面id',`during_time_1d` BIGINT COMMENT '最近1日浏览时长',`view_count_1d` BIGINT COMMENT '最近1日访问次数'
) COMMENT '流量域访客页面粒度页面浏览最近1日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_traffic_page_visitor_page_view_1d'TBLPROPERTIES ('orc.compress' = 'snappy'); DROP TABLE IF EXISTS dws_trade_user_sku_order_nd;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_nd
(`user_id` STRING COMMENT '用户id',`sku_id` STRING COMMENT 'sku_id',`sku_name` STRING COMMENT 'sku名称',`category1_id` STRING COMMENT '一级分类id',`category1_name` STRING COMMENT '一级分类名称',`category2_id` STRING COMMENT '一级分类id',`category2_name` STRING COMMENT '一级分类名称',`category3_id` STRING COMMENT '一级分类id',`category3_name` STRING COMMENT '一级分类名称',`tm_id` STRING COMMENT '品牌id',`tm_name` STRING COMMENT '品牌名称',`order_count_7d` STRING COMMENT '最近7日下单次数',`order_num_7d` BIGINT COMMENT '最近7日下单件数',`order_original_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单原始金额',`activity_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日活动优惠金额',`coupon_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日优惠券优惠金额',`order_total_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单最终金额',`order_count_30d` BIGINT COMMENT '最近30日下单次数',`order_num_30d` BIGINT COMMENT '最近30日下单件数',`order_original_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单原始金额',`activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日活动优惠金额',`coupon_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日优惠券优惠金额',`order_total_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单最终金额'
) COMMENT '交易域用户商品粒度订单最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_nd'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_sku_order_refund_nd;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_refund_nd
(`user_id` STRING COMMENT '用户id',`sku_id` STRING COMMENT 'sku_id',`sku_name` STRING COMMENT 'sku名称',`category1_id` STRING COMMENT '一级分类id',`category1_name` STRING COMMENT '一级分类名称',`category2_id` STRING COMMENT '一级分类id',`category2_name` STRING COMMENT '一级分类名称',`category3_id` STRING COMMENT '一级分类id',`category3_name` STRING COMMENT '一级分类名称',`tm_id` STRING COMMENT '品牌id',`tm_name` STRING COMMENT '品牌名称',`order_refund_count_7d` BIGINT COMMENT '最近7日退单次数',`order_refund_num_7d` BIGINT COMMENT '最近7日退单件数',`order_refund_amount_7d` DECIMAL(16, 2) COMMENT '最近7日退单金额',`order_refund_count_30d` BIGINT COMMENT '最近30日退单次数',`order_refund_num_30d` BIGINT COMMENT '最近30日退单件数',`order_refund_amount_30d` DECIMAL(16, 2) COMMENT '最近30日退单金额'
) COMMENT '交易域用户商品粒度退单最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_refund_nd'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_order_nd;
CREATE EXTERNAL TABLE dws_trade_user_order_nd
(`user_id` STRING COMMENT '用户id',`order_count_7d` BIGINT COMMENT '最近7日下单次数',`order_num_7d` BIGINT COMMENT '最近7日下单商品件数',`order_original_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单原始金额',`activity_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单活动优惠金额',`coupon_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单优惠券优惠金额',`order_total_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单最终金额',`order_count_30d` BIGINT COMMENT '最近30日下单次数',`order_num_30d` BIGINT COMMENT '最近30日下单商品件数',`order_original_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单原始金额',`activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单活动优惠金额',`coupon_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单优惠券优惠金额',`order_total_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单最终金额'
) COMMENT '交易域用户粒度订单最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_order_nd'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_cart_add_nd;
CREATE EXTERNAL TABLE dws_trade_user_cart_add_nd
(`user_id` STRING COMMENT '用户id',`cart_add_count_7d` BIGINT COMMENT '最近7日加购次数',`cart_add_num_7d` BIGINT COMMENT '最近7日加购商品件数',`cart_add_count_30d` BIGINT COMMENT '最近30日加购次数',`cart_add_num_30d` BIGINT COMMENT '最近30日加购商品件数'
) COMMENT '交易域用户粒度加购最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_cart_add_nd'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_payment_nd;
CREATE EXTERNAL TABLE dws_trade_user_payment_nd
(`user_id` STRING COMMENT '用户id',`payment_count_7d` BIGINT COMMENT '最近7日支付次数',`payment_num_7d` BIGINT COMMENT '最近7日支付商品件数',`payment_amount_7d` DECIMAL(16, 2) COMMENT '最近7日支付金额',`payment_count_30d` BIGINT COMMENT '最近30日支付次数',`payment_num_30d` BIGINT COMMENT '最近30日支付商品件数',`payment_amount_30d` DECIMAL(16, 2) COMMENT '最近30日支付金额'
) COMMENT '交易域用户粒度支付最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_payment_nd'
TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_province_order_nd;
CREATE EXTERNAL TABLE dws_trade_province_order_nd
(`province_id` STRING COMMENT '用户id',`province_name` STRING COMMENT '省份名称',`area_code` STRING COMMENT '地区编码',`iso_code` STRING COMMENT '旧版ISO-3166-2编码',`iso_3166_2` STRING COMMENT '新版版ISO-3166-2编码',`order_count_7d` BIGINT COMMENT '最近7日下单次数',`order_original_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单原始金额',`activity_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单活动优惠金额',`coupon_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单优惠券优惠金额',`order_total_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单最终金额',`order_count_30d` BIGINT COMMENT '最近30日下单次数',`order_original_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单原始金额',`activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单活动优惠金额',`coupon_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单优惠券优惠金额',`order_total_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单最终金额'
) COMMENT '交易域省份粒度订单最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_province_order_nd'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_coupon_order_nd;
CREATE EXTERNAL TABLE dws_trade_coupon_order_nd
(`coupon_id` STRING COMMENT '优惠券id',`coupon_name` STRING COMMENT '优惠券名称',`coupon_type_code` STRING COMMENT '优惠券类型id',`coupon_type_name` STRING COMMENT '优惠券类型名称',`coupon_rule` STRING COMMENT '优惠券规则',`start_date` STRING COMMENT '发布日期',`original_amount_30d` DECIMAL(16, 2) COMMENT '使用下单原始金额',`coupon_reduce_amount_30d` DECIMAL(16, 2) COMMENT '使用下单优惠金额'
) COMMENT '交易域优惠券粒度订单最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_coupon_order_nd'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_activity_order_nd;
CREATE EXTERNAL TABLE dws_trade_activity_order_nd
(`activity_id` STRING COMMENT '活动id',`activity_name` STRING COMMENT '活动名称',`activity_type_code` STRING COMMENT '活动类型编码',`activity_type_name` STRING COMMENT '活动类型名称',`start_date` STRING COMMENT '发布日期',`original_amount_30d` DECIMAL(16, 2) COMMENT '参与活动订单原始金额',`activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '参与活动订单优惠金额'
) COMMENT '交易域活动粒度订单最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_activity_order_nd'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_order_refund_nd;
CREATE EXTERNAL TABLE dws_trade_user_order_refund_nd
(`user_id` STRING COMMENT '用户id',`order_refund_count_7d` BIGINT COMMENT '最近7日退单次数',`order_refund_num_7d` BIGINT COMMENT '最近7日退单商品件数',`order_refund_amount_7d` DECIMAL(16, 2) COMMENT '最近7日退单金额',`order_refund_count_30d` BIGINT COMMENT '最近30日退单次数',`order_refund_num_30d` BIGINT COMMENT '最近30日退单商品件数',`order_refund_amount_30d` DECIMAL(16, 2) COMMENT '最近30日退单金额'
) COMMENT '交易域用户粒度退单最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_order_refund_nd'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_traffic_page_visitor_page_view_nd;
CREATE EXTERNAL TABLE dws_traffic_page_visitor_page_view_nd
(`mid_id` STRING COMMENT '访客id',`brand` string comment '手机品牌',`model` string comment '手机型号',`operate_system` string comment '操作系统',`page_id` STRING COMMENT '页面id',`during_time_7d` BIGINT COMMENT '最近7日浏览时长',`view_count_7d` BIGINT COMMENT '最近7日访问次数',`during_time_30d` BIGINT COMMENT '最近30日浏览时长',`view_count_30d` BIGINT COMMENT '最近30日访问次数'
) COMMENT '流量域访客页面粒度页面浏览最近n日汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_traffic_page_visitor_page_view_nd'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_order_td;
CREATE EXTERNAL TABLE dws_trade_user_order_td
(`user_id` STRING COMMENT '用户id',`order_date_first` STRING COMMENT '首次下单日期',`order_date_last` STRING COMMENT '末次下单日期',`order_count_td` BIGINT COMMENT '下单次数',`order_num_td` BIGINT COMMENT '购买商品件数',`original_amount_td` DECIMAL(16, 2) COMMENT '原始金额',`activity_reduce_amount_td` DECIMAL(16, 2) COMMENT '活动优惠金额',`coupon_reduce_amount_td` DECIMAL(16, 2) COMMENT '优惠券优惠金额',`total_amount_td` DECIMAL(16, 2) COMMENT '最终金额'
) COMMENT '交易域用户粒度订单历史至今汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_order_td'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_trade_user_payment_td;
CREATE EXTERNAL TABLE dws_trade_user_payment_td
(`user_id` STRING COMMENT '用户id',`payment_date_first` STRING COMMENT '首次支付日期',`payment_date_last` STRING COMMENT '末次支付日期',`payment_count_td` BIGINT COMMENT '最近7日支付次数',`payment_num_td` BIGINT COMMENT '最近7日支付商品件数',`payment_amount_td` DECIMAL(16, 2) COMMENT '最近7日支付金额'
) COMMENT '交易域用户粒度支付历史至今汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_trade_user_payment_td'TBLPROPERTIES ('orc.compress' = 'snappy');DROP TABLE IF EXISTS dws_user_user_login_td;
CREATE EXTERNAL TABLE dws_user_user_login_td
(`user_id` STRING COMMENT '用户id',`login_date_last` STRING COMMENT '末次登录日期',`login_count_td` BIGINT COMMENT '累计登录次数'
) COMMENT '用户域用户粒度登录历史至今汇总事实表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dws/dws_user_user_login_td'TBLPROPERTIES ('orc.compress' = 'snappy');
2.1d表格装载脚本
1.首日装载脚本
#!/bin/bash
APP=gmallif [ -n "$2" ] ;thendo_date=$2
else echo "请传入日期参数"exit
fidws_trade_province_order_1d="
insert overwrite table ${APP}.dws_trade_province_order_1d partition(dt)
selectprovince_id,province_name,area_code,iso_code,iso_3166_2,order_count_1d,order_original_amount_1d,activity_reduce_amount_1d,coupon_reduce_amount_1d,order_total_amount_1d,dt
from
(selectprovince_id,count(distinct(order_id)) order_count_1d,sum(split_original_amount) order_original_amount_1d,sum(nvl(split_activity_amount,0)) activity_reduce_amount_1d,sum(nvl(split_coupon_amount,0)) coupon_reduce_amount_1d,sum(split_total_amount) order_total_amount_1d,dtfrom ${APP}.dwd_trade_order_detail_incgroup by province_id,dt
)o
left join
(selectid,province_name,area_code,iso_code,iso_3166_2from ${APP}.dim_province_fullwhere dt='$do_date'
)p
on o.province_id=p.id;
"
dws_trade_user_cart_add_1d="
insert overwrite table ${APP}.dws_trade_user_cart_add_1d partition(dt)
selectuser_id,count(*),sum(sku_num),dt
from ${APP}.dwd_trade_cart_add_inc
group by user_id,dt;
"
dws_trade_user_order_1d="
insert overwrite table ${APP}.dws_trade_user_order_1d partition(dt)
selectuser_id,count(distinct(order_id)),sum(sku_num),sum(split_original_amount),sum(nvl(split_activity_amount,0)),sum(nvl(split_coupon_amount,0)),sum(split_total_amount),dt
from ${APP}.dwd_trade_order_detail_inc
group by user_id,dt;
"
dws_trade_user_order_refund_1d="
insert overwrite table ${APP}.dws_trade_user_order_refund_1d partition(dt)
selectuser_id,count(*) order_refund_count,sum(refund_num) order_refund_num,sum(refund_amount) order_refund_amount,dt
from ${APP}.dwd_trade_order_refund_inc
group by user_id,dt;
"
dws_trade_user_payment_1d="
insert overwrite table ${APP}.dws_trade_user_payment_1d partition(dt)
selectuser_id,count(distinct(order_id)),sum(sku_num),sum(split_payment_amount),dt
from ${APP}.dwd_trade_pay_detail_suc_inc
group by user_id,dt;
"
dws_trade_user_sku_order_1d="
insert overwrite table ${APP}.dws_trade_user_sku_order_1d partition(dt)
selectuser_id,id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_count_1d,order_num_1d,order_original_amount_1d,activity_reduce_amount_1d,coupon_reduce_amount_1d,order_total_amount_1d,dt
from
(selectdt,user_id,sku_id,count(*) order_count_1d,sum(sku_num) order_num_1d,sum(split_original_amount) order_original_amount_1d,sum(nvl(split_activity_amount,0.0)) activity_reduce_amount_1d,sum(nvl(split_coupon_amount,0.0)) coupon_reduce_amount_1d,sum(split_total_amount) order_total_amount_1dfrom ${APP}.dwd_trade_order_detail_incgroup by dt,user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom ${APP}.dim_sku_fullwhere dt='$do_date'
)sku
on od.sku_id=sku.id;
"
dws_trade_user_sku_order_refund_1d="
insert overwrite table ${APP}.dws_trade_user_sku_order_refund_1d partition(dt)
selectuser_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_refund_count,order_refund_num,order_refund_amount,dt
from
(selectdt,user_id,sku_id,count(*) order_refund_count,sum(refund_num) order_refund_num,sum(refund_amount) order_refund_amountfrom ${APP}.dwd_trade_order_refund_incgroup by dt,user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom ${APP}.dim_sku_fullwhere dt='$do_date'
)sku
on od.sku_id=sku.id;
"
dws_traffic_page_visitor_page_view_1d="
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_1d partition(dt='$do_date')
selectmid_id,brand,model,operate_system,page_id,sum(during_time),count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by mid_id,brand,model,operate_system,page_id;
"
dws_traffic_session_page_view_1d="
insert overwrite table ${APP}.dws_traffic_session_page_view_1d partition(dt='$do_date')
selectsession_id,mid_id,brand,model,operate_system,version_code,channel,sum(during_time),count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by session_id,mid_id,brand,model,operate_system,version_code,channel;
"case $1 in"dws_trade_province_order_1d" )hive -e "$dws_trade_province_order_1d";;"dws_trade_user_cart_add_1d" )hive -e "$dws_trade_user_cart_add_1d";;"dws_trade_user_order_1d" )hive -e "$dws_trade_user_order_1d";;"dws_trade_user_order_refund_1d" )hive -e "$dws_trade_user_order_refund_1d";;"dws_trade_user_payment_1d" )hive -e "$dws_trade_user_payment_1d";;"dws_trade_user_sku_order_1d" )hive -e "$dws_trade_user_sku_order_1d";;"dws_trade_user_sku_order_refund_1d" )hive -e "$dws_trade_user_sku_order_refund_1d";;"dws_traffic_page_visitor_page_view_1d" )hive -e "$dws_traffic_page_visitor_page_view_1d";;"dws_traffic_session_page_view_1d" )hive -e "$dws_traffic_session_page_view_1d";;"all" )hive -e "$dws_trade_province_order_1d$dws_trade_user_cart_add_1d$dws_trade_user_order_1d$dws_trade_user_order_refund_1d$dws_trade_user_payment_1d$dws_trade_user_sku_order_1d$dws_trade_user_sku_order_refund_1d$dws_traffic_page_visitor_page_view_1d$dws_traffic_session_page_view_1d";;
esac
2.每日装载脚本
#!/bin/bash
APP=gmall# 如果输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;thendo_date=$2
else do_date=`date -d "-1 day" +%F`
fidws_trade_province_order_1d="
insert overwrite table ${APP}.dws_trade_province_order_1d partition(dt='$do_date')
selectprovince_id,province_name,area_code,iso_code,iso_3166_2,order_count_1d,order_original_amount_1d,activity_reduce_amount_1d,coupon_reduce_amount_1d,order_total_amount_1d
from
(selectprovince_id,count(distinct(order_id)) order_count_1d,sum(split_original_amount) order_original_amount_1d,sum(nvl(split_activity_amount,0)) activity_reduce_amount_1d,sum(nvl(split_coupon_amount,0)) coupon_reduce_amount_1d,sum(split_total_amount) order_total_amount_1dfrom ${APP}.dwd_trade_order_detail_incwhere dt='$do_date'group by province_id
)o
left join
(selectid,province_name,area_code,iso_code,iso_3166_2from ${APP}.dim_province_fullwhere dt='$do_date'
)p
on o.province_id=p.id;
"
dws_trade_user_cart_add_1d="
insert overwrite table ${APP}.dws_trade_user_cart_add_1d partition(dt='$do_date')
selectuser_id,count(*),sum(sku_num)
from ${APP}.dwd_trade_cart_add_inc
where dt='$do_date'
group by user_id;
"
dws_trade_user_order_1d="
insert overwrite table ${APP}.dws_trade_user_order_1d partition(dt='$do_date')
selectuser_id,count(distinct(order_id)),sum(sku_num),sum(split_original_amount),sum(nvl(split_activity_amount,0)),sum(nvl(split_coupon_amount,0)),sum(split_total_amount)
from ${APP}.dwd_trade_order_detail_inc
where dt='$do_date'
group by user_id;
"
dws_trade_user_order_refund_1d="
insert overwrite table ${APP}.dws_trade_user_order_refund_1d partition(dt='$do_date')
selectuser_id,count(*),sum(refund_num),sum(refund_amount)
from ${APP}.dwd_trade_order_refund_inc
where dt='$do_date'
group by user_id;
"
dws_trade_user_payment_1d="
insert overwrite table ${APP}.dws_trade_user_payment_1d partition(dt='$do_date')
selectuser_id,count(distinct(order_id)),sum(sku_num),sum(split_payment_amount)
from ${APP}.dwd_trade_pay_detail_suc_inc
where dt='$do_date'
group by user_id;
"
dws_trade_user_sku_order_1d="
insert overwrite table ${APP}.dws_trade_user_sku_order_1d partition(dt='$do_date')
selectuser_id,id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_count,order_num,order_original_amount,activity_reduce_amount,coupon_reduce_amount,order_total_amount
from
(selectuser_id,sku_id,count(*) order_count,sum(sku_num) order_num,sum(split_original_amount) order_original_amount,sum(nvl(split_activity_amount,0)) activity_reduce_amount,sum(nvl(split_coupon_amount,0)) coupon_reduce_amount,sum(split_total_amount) order_total_amountfrom ${APP}.dwd_trade_order_detail_incwhere dt='$do_date'group by user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom ${APP}.dim_sku_fullwhere dt='$do_date'
)sku
on od.sku_id=sku.id;
"
dws_trade_user_sku_order_refund_1d="
insert overwrite table ${APP}.dws_trade_user_sku_order_refund_1d partition(dt='$do_date')
selectuser_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_refund_count,order_refund_num,order_refund_amount
from
(selectuser_id,sku_id,count(*) order_refund_count,sum(refund_num) order_refund_num,sum(refund_amount) order_refund_amountfrom ${APP}.dwd_trade_order_refund_incwhere dt='$do_date'group by user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom ${APP}.dim_sku_fullwhere dt='$do_date'
)sku
on od.sku_id=sku.id;
"
dws_traffic_page_visitor_page_view_1d="
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_1d partition(dt='$do_date')
selectmid_id,brand,model,operate_system,page_id,sum(during_time),count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by mid_id,brand,model,operate_system,page_id;
"
dws_traffic_session_page_view_1d="
insert overwrite table ${APP}.dws_traffic_session_page_view_1d partition(dt='$do_date')
selectsession_id,mid_id,brand,model,operate_system,version_code,channel,sum(during_time),count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt='$do_date'
group by session_id,mid_id,brand,model,operate_system,version_code,channel;
"case $1 in"dws_trade_province_order_1d" )hive -e "$dws_trade_province_order_1d";;"dws_trade_user_cart_add_1d" )hive -e "$dws_trade_user_cart_add_1d";;"dws_trade_user_order_1d" )hive -e "$dws_trade_user_order_1d";;"dws_trade_user_order_refund_1d" )hive -e "$dws_trade_user_order_refund_1d";;"dws_trade_user_payment_1d" )hive -e "$dws_trade_user_payment_1d";;"dws_trade_user_sku_order_1d" )hive -e "$dws_trade_user_sku_order_1d";;"dws_trade_user_sku_order_refund_1d" )hive -e "$dws_trade_user_sku_order_refund_1d";;"dws_traffic_page_visitor_page_view_1d" )hive -e "$dws_traffic_page_visitor_page_view_1d";;"dws_traffic_session_page_view_1d" )hive -e "$dws_traffic_session_page_view_1d";;"all" )hive -e "$dws_trade_province_order_1d$dws_trade_user_cart_add_1d$dws_trade_user_order_1d$dws_trade_user_order_refund_1d$dws_trade_user_payment_1d$dws_trade_user_sku_order_1d$dws_trade_user_sku_order_refund_1d$dws_traffic_page_visitor_page_view_1d$dws_traffic_session_page_view_1d";;
esac
3.nd表格装载脚本
#!/bin/bash
APP=gmall# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;thendo_date=$2
else do_date=`date -d "-1 day" +%F`
fidws_trade_activity_order_nd="
insert overwrite table ${APP}.dws_trade_activity_order_nd partition(dt='$do_date')
selectact.activity_id,activity_name,activity_type_code,activity_type_name,date_format(start_time,'yyyy-MM-dd'),sum(split_original_amount),sum(split_activity_amount)
from
(selectactivity_id,activity_name,activity_type_code,activity_type_name,start_timefrom ${APP}.dim_activity_fullwhere dt='$do_date'and date_format(start_time,'yyyy-MM-dd')>=date_add('$do_date',-29)group by activity_id, activity_name, activity_type_code, activity_type_name,start_time
)act
left join
(selectactivity_id,order_id,split_original_amount,split_activity_amountfrom ${APP}.dwd_trade_order_detail_incwhere dt>=date_add('$do_date',-29)and dt<='$do_date'and activity_id is not null
)od
on act.activity_id=od.activity_id
group by act.activity_id,activity_name,activity_type_code,activity_type_name,start_time;
"
dws_trade_coupon_order_nd="
insert overwrite table ${APP}.dws_trade_coupon_order_nd partition(dt='$do_date')
selectid,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,start_date,sum(split_original_amount),sum(split_coupon_amount)
from
(selectid,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,date_format(start_time,'yyyy-MM-dd') start_datefrom ${APP}.dim_coupon_fullwhere dt='$do_date'and date_format(start_time,'yyyy-MM-dd')>=date_add('$do_date',-29)
)cou
left join
(selectcoupon_id,order_id,split_original_amount,split_coupon_amountfrom ${APP}.dwd_trade_order_detail_incwhere dt>=date_add('$do_date',-29)and dt<='$do_date'and coupon_id is not null
)od
on cou.id=od.coupon_id
group by id,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,start_date;
"
dws_trade_province_order_nd="
insert overwrite table ${APP}.dws_trade_province_order_nd partition(dt='$do_date')
selectprovince_id,province_name,area_code,iso_code,iso_3166_2,sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)),sum(if(dt>=date_add('$do_date',-6),activity_reduce_amount_1d,0)),sum(if(dt>=date_add('$do_date',-6),coupon_reduce_amount_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),sum(order_count_1d),sum(order_original_amount_1d),sum(activity_reduce_amount_1d),sum(coupon_reduce_amount_1d),sum(order_total_amount_1d)
from ${APP}.dws_trade_province_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by province_id,province_name,area_code,iso_code,iso_3166_2;
"
dws_trade_user_cart_add_nd="
insert overwrite table ${APP}.dws_trade_user_cart_add_nd partition(dt='$do_date')
selectuser_id,sum(if(dt>=date_add('$do_date',-6),cart_add_count_1d,0)),sum(if(dt>=date_add('$do_date',-6),cart_add_num_1d,0)),sum(cart_add_count_1d),sum(cart_add_num_1d)
from ${APP}.dws_trade_user_cart_add_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by user_id;
"
dws_trade_user_order_nd="
insert overwrite table ${APP}.dws_trade_user_order_nd partition(dt='$do_date')
selectuser_id,sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_num_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)),sum(if(dt>=date_add('$do_date',-6),activity_reduce_amount_1d,0)),sum(if(dt>=date_add('$do_date',-6),coupon_reduce_amount_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),sum(order_count_1d),sum(order_num_1d),sum(order_original_amount_1d),sum(activity_reduce_amount_1d),sum(coupon_reduce_amount_1d),sum(order_total_amount_1d)
from ${APP}.dws_trade_user_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by user_id;
"
dws_trade_user_order_refund_nd="
insert overwrite table ${APP}.dws_trade_user_order_refund_nd partition(dt='$do_date')
selectuser_id,sum(if(dt>=date_add('$do_date',-6),order_refund_count_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_refund_num_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_refund_amount_1d,0)),sum(order_refund_count_1d),sum(order_refund_num_1d),sum(order_refund_amount_1d)
from ${APP}.dws_trade_user_order_refund_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by user_id;
"
dws_trade_user_payment_nd="
insert overwrite table ${APP}.dws_trade_user_payment_nd partition (dt = '$do_date')
select user_id,sum(if(dt >= date_add('$do_date', -6), payment_count_1d, 0)),sum(if(dt >= date_add('$do_date', -6), payment_num_1d, 0)),sum(if(dt >= date_add('$do_date', -6), payment_amount_1d, 0)),sum(payment_count_1d),sum(payment_num_1d),sum(payment_amount_1d)
from ${APP}.dws_trade_user_payment_1d
where dt >= date_add('$do_date', -29)and dt <= '$do_date'
group by user_id;
"
dws_trade_user_sku_order_nd="
insert overwrite table ${APP}.dws_trade_user_sku_order_nd partition(dt='$do_date')
selectuser_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_num_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)),sum(if(dt>=date_add('$do_date',-6),activity_reduce_amount_1d,0)),sum(if(dt>=date_add('$do_date',-6),coupon_reduce_amount_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),sum(order_count_1d),sum(order_num_1d),sum(order_original_amount_1d),sum(activity_reduce_amount_1d),sum(coupon_reduce_amount_1d),sum(order_total_amount_1d)
from ${APP}.dws_trade_user_sku_order_1d
where dt>=date_add('$do_date',-30)
group by user_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name;
"
dws_trade_user_sku_order_refund_nd="
insert overwrite table ${APP}.dws_trade_user_sku_order_refund_nd partition(dt='$do_date')
selectuser_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,sum(if(dt>=date_add('$do_date',-6),order_refund_count_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_refund_num_1d,0)),sum(if(dt>=date_add('$do_date',-6),order_refund_amount_1d,0)),sum(order_refund_count_1d),sum(order_refund_num_1d),sum(order_refund_amount_1d)
from ${APP}.dws_trade_user_sku_order_refund_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by user_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name;
"
dws_traffic_page_visitor_page_view_nd="
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_nd partition(dt='$do_date')
selectmid_id,brand,model,operate_system,page_id,sum(if(dt>=date_add('$do_date',-6),during_time_1d,0)),sum(if(dt>=date_add('$do_date',-6),view_count_1d,0)),sum(during_time_1d),sum(view_count_1d)
from ${APP}.dws_traffic_page_visitor_page_view_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by mid_id,brand,model,operate_system,page_id;
"case $1 in"dws_trade_activity_order_nd" )hive -e "$dws_trade_activity_order_nd";;"dws_trade_coupon_order_nd" )hive -e "$dws_trade_coupon_order_nd";;"dws_trade_province_order_nd" )hive -e "$dws_trade_province_order_nd";;"dws_trade_user_cart_add_nd" )hive -e "$dws_trade_user_cart_add_nd";;"dws_trade_user_order_nd" )hive -e "$dws_trade_user_order_nd";;"dws_trade_user_order_refund_nd" )hive -e "$dws_trade_user_order_refund_nd";;"dws_trade_user_payment_nd" )hive -e "$dws_trade_user_payment_nd";;"dws_trade_user_sku_order_nd" )hive -e "$dws_trade_user_sku_order_nd";;"dws_trade_user_sku_order_refund_nd" )hive -e "$dws_trade_user_sku_order_refund_nd";;"dws_traffic_page_visitor_page_view_nd" )hive -e "$dws_traffic_page_visitor_page_view_nd";;"all" )hive -e "$dws_trade_activity_order_nd$dws_trade_coupon_order_nd$dws_trade_province_order_nd$dws_trade_user_cart_add_nd$dws_trade_user_order_nd$dws_trade_user_order_refund_nd$dws_trade_user_payment_nd$dws_trade_user_sku_order_nd$dws_trade_user_sku_order_refund_nd$dws_traffic_page_visitor_page_view_nd";;
esac
4.td表格装载脚本
1.首日装载脚本
#!/bin/bash
APP=gmallif [ -n "$2" ] ;thendo_date=$2
else echo "请传入日期参数"exit
fidws_trade_user_order_td="
insert overwrite table ${APP}.dws_trade_user_order_td partition(dt='$do_date')
selectuser_id,min(dt) login_date_first,max(dt) login_date_last,sum(order_count_1d) order_count,sum(order_num_1d) order_num,sum(order_original_amount_1d) original_amount,sum(activity_reduce_amount_1d) activity_reduce_amount,sum(coupon_reduce_amount_1d) coupon_reduce_amount,sum(order_total_amount_1d) total_amount
from ${APP}.dws_trade_user_order_1d
group by user_id;
"dws_trade_user_payment_td="
insert overwrite table ${APP}.dws_trade_user_payment_td partition(dt='$do_date')
selectuser_id,min(dt) payment_date_first,max(dt) payment_date_last,sum(payment_count_1d) payment_count,sum(payment_num_1d) payment_num,sum(payment_amount_1d) payment_amount
from ${APP}.dws_trade_user_payment_1d
group by user_id;
"dws_user_user_login_td="
insert overwrite table ${APP}.dws_user_user_login_td partition(dt='$do_date')
selectu.id,nvl(login_date_last,date_format(create_time,'yyyy-MM-dd')),nvl(login_count_td,1)
from
(selectid,create_timefrom ${APP}.dim_user_zipwhere dt='9999-12-31'
)u
left join
(selectuser_id,max(dt) login_date_last,count(*) login_count_tdfrom ${APP}.dwd_user_login_incgroup by user_id
)l
on u.id=l.user_id;
"case $1 in"dws_trade_user_order_td" )hive -e "$dws_trade_user_order_td";;"dws_trade_user_payment_td" )hive -e "$dws_trade_user_payment_td";;"dws_user_user_login_td" )hive -e "$dws_user_user_login_td";;"all" )hive -e "$dws_trade_user_order_td$dws_trade_user_payment_td$dws_user_user_login_td";;
esac
2.每日装载脚本
#!/bin/bash
APP=gmall# 如果输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;thendo_date=$2
else do_date=`date -d "-1 day" +%F`
fidws_trade_user_order_td="
insert overwrite table ${APP}.dws_trade_user_order_td partition(dt='$do_date')
selectnvl(old.user_id,new.user_id),if(new.user_id is not null and old.user_id is null,'$do_date',old.order_date_first),if(new.user_id is not null,'$do_date',old.order_date_last),nvl(old.order_count_td,0)+nvl(new.order_count_1d,0),nvl(old.order_num_td,0)+nvl(new.order_num_1d,0),nvl(old.original_amount_td,0)+nvl(new.order_original_amount_1d,0),nvl(old.activity_reduce_amount_td,0)+nvl(new.activity_reduce_amount_1d,0),nvl(old.coupon_reduce_amount_td,0)+nvl(new.coupon_reduce_amount_1d,0),nvl(old.total_amount_td,0)+nvl(new.order_total_amount_1d,0)
from
(selectuser_id,order_date_first,order_date_last,order_count_td,order_num_td,original_amount_td,activity_reduce_amount_td,coupon_reduce_amount_td,total_amount_tdfrom ${APP}.dws_trade_user_order_tdwhere dt=date_add('$do_date',-1)
)old
full outer join
(selectuser_id,order_count_1d,order_num_1d,order_original_amount_1d,activity_reduce_amount_1d,coupon_reduce_amount_1d,order_total_amount_1dfrom ${APP}.dws_trade_user_order_1dwhere dt='$do_date'
)new
on old.user_id=new.user_id;
"dws_trade_user_payment_td="
insert overwrite table ${APP}.dws_trade_user_payment_td partition(dt='$do_date')
selectnvl(old.user_id,new.user_id),if(old.user_id is null and new.user_id is not null,'$do_date',old.payment_date_first),if(new.user_id is not null,'$do_date',old.payment_date_last),nvl(old.payment_count_td,0)+nvl(new.payment_count_1d,0),nvl(old.payment_num_td,0)+nvl(new.payment_num_1d,0),nvl(old.payment_amount_td,0)+nvl(new.payment_amount_1d,0)
from
(selectuser_id,payment_date_first,payment_date_last,payment_count_td,payment_num_td,payment_amount_tdfrom ${APP}.dws_trade_user_payment_tdwhere dt=date_add('$do_date',-1)
)old
full outer join
(selectuser_id,payment_count_1d,payment_num_1d,payment_amount_1dfrom ${APP}.dws_trade_user_payment_1dwhere dt='$do_date'
)new
on old.user_id=new.user_id;
"dws_user_user_login_td="
insert overwrite table ${APP}.dws_user_user_login_td partition(dt='$do_date')
selectnvl(old.user_id,new.user_id),if(new.user_id is null,old.login_date_last,'$do_date'),nvl(old.login_count_td,0)+nvl(new.login_count_1d,0)
from
(selectuser_id,login_date_last,login_count_tdfrom ${APP}.dws_user_user_login_tdwhere dt=date_add('$do_date',-1)
)old
full outer join
(selectuser_id,count(*) login_count_1dfrom ${APP}.dwd_user_login_incwhere dt='$do_date'group by user_id
)new
on old.user_id=new.user_id;
"case $1 in"dws_trade_user_order_td" )hive -e "$dws_trade_user_order_td";;"dws_trade_user_payment_td" )hive -e "$dws_trade_user_payment_td";;"dws_user_user_login_td" )hive -e "$dws_user_user_login_td";;"all" )hive -e "$dws_trade_user_order_td$dws_trade_user_payment_td$dws_user_user_login_td";;
esac
相关文章：
【离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载】
离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载一、交易域用户商品粒度订单最近1日/N日汇总表1.交易域用户商品粒度订单最近1日汇总表2.交易域用户商品粒度订单最近N日汇总表二、交易域优惠券粒度…...
我的十年编程路 序
算起来，从决定并从事编程开始，已十年有余了。 这十年是怎么算的呢？ 我的本科是从2009年至2013年，现在回想起来，应该是从2012年下半年，也就是大四还未正式开始的时候决定从事Android开发。参加了培训班&am…...
xs 180
选择题(共180题,合计180.0分) 1. 你被任命为某项目的敏捷教练，为了更好的交付产品，你与团队召开会议，讨论项目过程中团队应该如何做到有效沟通。最有可能确定项目过程中主要以下列哪种方式沟通? A 团队成员在各自的办公室自行办公&#…...
时间序列分析 | BiLSTM双向长短期记忆神经网络时间序列预测（Matlab完整程序）
时间序列分析 | BiLSTM双向长短期记忆神经网络时间序列预测（Matlab完整程序） 目录 时间序列分析 | BiLSTM双向长短期记忆神经网络时间序列预测（Matlab完整程序）预测结果评价指标基本介绍完整程序参考资料预测结果 评价指标 训练集数据的R2为：0.99302 测试集数据的R2为&…...
0101基础-认证授权-springsecurity
文章目录1 基础概念1.1 认证1.2 会话1.3 jwt1.4 授权2 授权的数据模型3 RBAC3.1 基于角色的访问控制3.2 基于资源的访问控制4 名词解析4.1 SSO4.2 CAS4.3 联合登陆4.4 多端登录：同一账号不同终端登录4.5 OAuth1 基础概念 1.1 认证 认证是为了保护系统的隐私数据和…...
一文简单了解THD布局要求
一、什么是THD？ THD指总谐波失真。谐波失真是指输出信号比输入信号多出的谐波成分。谐波失真是系统不完全线性造成的。所有附加谐波电平之和称为总谐波失真。总谐波失真与频率有关。一般说来，1000Hz频率处的总谐波失真最小，因此不少产品均以…...
[C++]多态
🥁作者： 华丞臧 📕专栏：【C】 各位读者老爷如果觉得博主写的不错，请诸位多多支持(点赞收藏关注)。如果有错误的地方，欢迎在评论区指出。 推荐一款刷题网站 👉LeetCode 文章目录一、多态…...
中国版ChatGPT高潮即将到来，解密ChatGPT底层网络架构
2022年11月30日人工智能研究实验室OpenAI发布全新聊天机器人ChatGPT，在中国用户无法访问的前提下，上线仅两个月月活用户就突破了1亿。ChatGPT如同重磅炸弹，一时间火遍全球。面对这一万亿级市场机遇，在国内，无论是资本方…...
PingCAP 唐刘：一个咨询顾问对 TiDB Chat2Query Demo 提出的脑洞
导读 近日，TiDB Cloud 发布了 Chat2Query 功能，在 TiDB Cloud 上通过自然语言提问，即可生成相应的 SQL，通过 TiDB Cloud 对上传的任意数据集进行分析。Gartner 也在一份有关 ChatGPT 对数据分析影响研究的报告中提及了 PingCAP 的…...
力扣-销售分析III
大家好，我是空空star，本篇带大家了解一道简单的力扣sql练习题。 文章目录前言一、题目：1084. 销售分析III二、解题1.正确示范①提交SQL运行结果2.正确示范②提交SQL运行结果3.正确示范③提交SQL运行结果4.正确示范④提交SQL运行结果5.其他总结…...
U-Boot 之七 详解 Driver Model 架构、配置、命令、初始化流程
U-Boot 在 2014 年 4 月参考 Linux Kernel 的驱动模型设计并引入了自己的 Driver Model（官方简称 DM） 驱动架构。这个驱动模型（DM）为驱动的定义和访问接口提供了统一的方法，提高了驱动之间的兼容性以及访问的标准性。 …...
大数据算法重点
1 大数据亚线性空间算法 场景：用二进制存储一个数字N，需要log(N)的空间 问题：如果N特别大而且这样的N又特别的多，该怎么办呢？ 思路：减少一些准确性，从而节省更多的空间。 解决办法：使…...
【Eclipse】The import xxxx cannot be resolved 问题解决
在Eclipse使用过程中，某一个类明明存在，但是使用import导入时，却总是提示The import xxxx cannot be resolved的错误，解决办法如下： 点击Project->Clean......
LinkWeChat系统Docker版部署注意事项
具体部署手册：https://www.yuque.com/linkwechat/help/ffi7bu注意事项：启动类配置文件路径需要修改，各个模块启动类原配置如下：.properties("spring.config.name:bootstrap", "config/run/bootstrap.yml")各个…...
【高数】不定积分之有理函数的积分
文章目录前言有理函数积分的通用解法有理函数的特殊解法前言 这个专栏开始更新高等数学的解题方法，本专栏没有特别强调概念，主要是让大家熟悉考研中的一些题型以及如何求解 关键步骤用蓝色高亮提示 总结方法用红色高亮提示 注意事项用绿色高亮提示 希望…...
Java——数组
目录 前言 一、数组的定义 二、数组声明和创建 三、三种初始化及内存分析 Java内存分析 三种初始化 静态初始化 动态初始化 数组的默认初始化 数组的四个基本特点 四、下标越界及小结 五、数组的使用 For-Each循环 数组作方法入参 数组作返回值 六、二维数组 七…...
产品分析｜虎扑APP
不同于传统的体育新闻门户网站，虎扑以篮球社区起家，在经历了从体育论坛到体育新闻网站的发展后，又逐渐回归社区发展。 目前，虎扑汇聚了大量的男性用户，俨然成为了“互联网直男的自留地”。特立独行的发展方向使得虎扑不断发展壮大，同时也使得虎扑逐渐触碰到了行业天花板。…...
有限差分法-二维泊松方程及其Matlab程序实现
2.2 偏微分方程的差分解法 2.2.1 二维泊松方程 考虑区域 Ω \Omega Ω 上的二维泊松问题: { − ( ∂ 2...
【设计模式】6.代理模式
概述 代理模式:为一个对象提供一个替身,以控制对这个对象的访问。即通过代理访问目标对象 这样做的好处是：可以在目标对象实现的基础上,增强额外的功能操作，即扩展目标对象的功能。 被代理的对象可以是：远程对象、创建开销大的对象或需要安全…...
SRC挖掘之Access验证校验的漏洞挖掘
漏洞已修复，感谢某大佬的知识分享。 任意用户密码重置->可获取全校师生个人mingan信息 开局就是信息收集。 对于挖掘edu的信息收集 1.可尝试谷歌搜索语法，获取学号信息 2. 旁站的渗透获取 3. 学校的贴吧获取(大部分都是本校学生) 当然我就是闲&a…...
深入浅出Asp.Net Core MVC应用开发系列-AspNetCore中的日志记录
ASP.NET Core 是一个跨平台的开源框架，用于在 Windows、macOS 或 Linux 上生成基于云的新式 Web 应用。 ASP.NET Core 中的日志记录 .NET 通过 ILogger API 支持高性能结构化日志记录，以帮助监视应用程序行为和诊断问题。 可以通过配置不同的记录提供程…...
TDengine 快速体验（Docker 镜像方式）
简介 TDengine 可以通过安装包、Docker 镜像 及云服务快速体验 TDengine 的功能，本节首先介绍如何通过 Docker 快速体验 TDengine，然后介绍如何在 Docker 环境下体验 TDengine 的写入和查询功能。如果你不熟悉 Docker，请使用 安装包的方式快…...
1.3 VSCode安装与环境配置
进入网址Visual Studio Code - Code Editing. Redefined下载.deb文件，然后打开终端，进入下载文件夹，键入命令 sudo dpkg -i code_1.100.3-1748872405_amd64.deb 在终端键入命令code即启动vscode 需要安装插件列表 1.Chinese简化 2.ros …...
CocosCreator 之 JavaScript/TypeScript和Java的相互交互
引擎版本： 3.8.1 语言： JavaScript/TypeScript、C、Java 环境：Window 参考：Java原生反射机制 您好，我是鹤九日！ 回顾 在上篇文章中：CocosCreator Android项目接入UnityAds 广告SDK。 我们简单讲…...
DBAPI如何优雅的获取单条数据
API如何优雅的获取单条数据 案例一 对于查询类API，查询的是单条数据，比如根据主键ID查询用户信息，sql如下： select id, name, age from user where id #{id}API默认返回的数据格式是多条的，如下： {&qu…...
Spring AI 入门：Java 开发者的生成式 AI 实践之路
一、Spring AI 简介 在人工智能技术快速迭代的今天，Spring AI 作为 Spring 生态系统的新生力量，正在成为 Java 开发者拥抱生成式 AI 的最佳选择。该框架通过模块化设计实现了与主流 AI 服务（如 OpenAI、Anthropic）的无缝对接&…...
网络编程（UDP编程）
思维导图 UDP基础编程（单播） 1.流程图 服务器：短信的接收方 创建套接字 (socket)-----------------------------------------》有手机指定网络信息-----------------------------------------------》有号码绑定套接字 (bind)--------------…...
全面解析各类VPN技术：GRE、IPsec、L2TP、SSL与MPLS VPN对比
目录 引言 VPN技术概述 GRE VPN 3.1 GRE封装结构 3.2 GRE的应用场景 GRE over IPsec 4.1 GRE over IPsec封装结构 4.2 为什么使用GRE over IPsec？ IPsec VPN 5.1 IPsec传输模式（Transport Mode） 5.2 IPsec隧道模式（Tunne…...
【Oracle】分区表
个人主页：Guiat 归属专栏：Oracle 文章目录 1. 分区表基础概述1.1 分区表的概念与优势1.2 分区类型概览1.3 分区表的工作原理 2. 范围分区 (RANGE Partitioning)2.1 基础范围分区2.1.1 按日期范围分区2.1.2 按数值范围分区 2.2 间隔分区 (INTERVAL Partit…...
C# 求圆面积的程序（Program to find area of a circle）
给定半径r，求圆的面积。圆的面积应精确到小数点后5位。 例子： 输入：r 5 输出：78.53982 解释：由于面积 PI * r * r 3.14159265358979323846 * 5 * 5 78.53982，因为我们只保留小数点后 5 位数字。 输…...