SQL_ODPS-D2-离线数仓-7-临时查询

最新推荐文章于 2023-10-11 16:20:14 发布
最新推荐文章于 2023-10-11 16:20:14 发布
阅读量500 收藏
点赞数
分类专栏： 大数据 MaxCompute DataWorks
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44345917/article/details/104935037
版权

SQL_ODPS-D2-离线数仓-7-临时查询

包括ods dwd dws ads数仓的建表语句 以及 手动导入数据语句

test

--odps sql 
--********************************************************************--
--author:i
--create time:0000-00-00 00:00:00
--********************************************************************--
SELECT  *
FROM    ods_base_log
WHERE   ds = '00000000'
;

--测试FlatEventUDTF自定义函数 一进多出
SELECT  FLATEVENTUDTF(GET_JSON_OBJECT(log_string,'$.et')) AS (event_time,event_name,event_json)    --$.et 就是事件
FROM    ods_base_log
WHERE   ds = '00000000'
;

--手动将ods层数据导入到dwd层
INSERT OVERWRITE TABLE dwd_start_log PARTITION(ds,hh,mm)
SELECT  GET_JSON_OBJECT(log_string,'$.cm.mid') mid
        ,GET_JSON_OBJECT(log_string,'$.cm.uid') user_id
        ,GET_JSON_OBJECT(log_string,'$.cm.vc' ) version_code
        ,GET_JSON_OBJECT(log_string,'$.cm.vn') version_name
        ,GET_JSON_OBJECT(log_string,'$.cm.l') lang
        ,GET_JSON_OBJECT(log_string,'$.cm.sr') source
        ,GET_JSON_OBJECT(log_string,'$.cm.os') os
        ,GET_JSON_OBJECT(log_string,'$.cm.ar') area
        ,GET_JSON_OBJECT(log_string,'$.cm.md') model
        ,GET_JSON_OBJECT(log_string,'$.cm.ba') brand
        ,GET_JSON_OBJECT(log_string,'$.cm.sv') sdk_version
        ,GET_JSON_OBJECT(log_string,'$.cm.hw') height_width
        ,GET_JSON_OBJECT(log_string,'$.cm.g') email
        ,GET_JSON_OBJECT(log_string,'$.cm.hw') sv
        ,GET_JSON_OBJECT(log_string,'$.cm.ln') ln
        ,GET_JSON_OBJECT(log_string,'$.cm.la') la
        ,GET_JSON_OBJECT(event_view.event_json,'$.entry') entry
        ,GET_JSON_OBJECT(event_view.event_json,'$.loading_time') loading_time
        ,GET_JSON_OBJECT(event_view.event_json,'$.action') action
        ,GET_JSON_OBJECT(event_view.event_json,'$.open_ad_type') open_ad_type
        ,GET_JSON_OBJECT(event_view.event_json,'$.detail') detail
        ,event_view.event_time
        ,ds
        ,hh
        ,mm
FROM    ods_base_log
LATERAL VIEW FLATEVENTUDTF(GET_JSON_OBJECT(log_string,'$.et')) event_view AS event_time,event_name,event_json
WHERE   ds = '00000000'
AND     event_view.event_name = 'start'
;

--查看导入结果
SELECT  *
FROM    dwd_start_log
WHERE   ds = '00000000'
LIMIT   3
;
-- mid	user_id	version_code	version_name	lang	source	os	area	model	brand	sdk_version	email	height_width	network	lng	lat	entry	open_ad_type	action	loading_time	detail	event_time	ds	hh	mm
-- 0	0	9	1.0.7	es	V	8.1.1	MX	HTC-0	HTC	V2.5.9	640*960	QED8OB5D@gmail.com	640*960	-112.5	29.2	3	13	1	2		1000001265269	00000000	01	15
--手动将dwd层数据导入到dws层

INSERT OVERWRITE TABLE dws_uv_detail_d PARTITION(ds,hh,mm)
SELECT  mid
        ,user_id
        ,version_code
        ,version_name
        ,lang
        ,source
        ,os
        ,area
        ,model
        ,brand
        ,sdk_version
        ,email
        ,height_width
        ,network
        ,lng
        ,lat
        ,event_time
        ,ds
        ,hh
        ,mm
FROM    (
            SELECT  *
                    ,row_number() OVER(PARTITION BY mid ORDER BY event_time ASC) rn
            FROM    dwd_start_log
            WHERE   ds = '00000000'
        ) 
WHERE   rn = 1
;

--查看导入结果
SELECT  *
FROM    dws_uv_detail_d
WHERE   ds = '00000000'
LIMIT   5
;
-- mid	user_id	version_code	version_name	lang	source	os	area	model	brand	sdk_version	email	height_width	network	lng	lat	event_time	ds	hh	mm
-- 999	999	16	1.2.0	es	W	8.1.0	MX	sumsung-4	Sumsung	V2.2.9	1080*1920	W4ZM4MEQ@gmail.com	1080*1920	-86.9	12.8	1583581530558	00000000	01	15
-- 1	1	11	1.2.0	es	R	8.1.4	MX	Huawei-4	Huawei	V2.1.7	640*1136	QF4951T5@gmail.com	640*1136	-101.2	21.8	1583553705570	00000000	01	15
-- 10	10	16	1.2.2	en	E	8.0.8	MX	sumsung-10	Sumsung	V2.7.8	750*1134	K3ZL1FP0@gmail.com	750*1134	-96.7	0.8	1583514130824	00000000	01	15
-- 100	100	18	1.0.5	pt	G	8.1.6	MX	HTC-11	HTC	V2.7.3	640*960	W5920706@gmail.com	640*960	-84.8	25.9	1583564803755	00000000	01	15
-- 101	101	14	1.2.1	en	L	8.2.0	MX	Huawei-0	Huawei	V2.5.5	750*1134	1Q2V0YZ5@gmail.com	750*1134	-72.9	-4.6	1583574821599	00000000	01	15

--ods层原数据层建表语句
CREATE TABLE `ods_order_info_di` 
(
    `id` STRING COMMENT '订单编号'
    ,`total_amount` DOUBLE COMMENT '订单金额'
    ,`order_status`STRING COMMENT '订单状态'
    ,`user_id` STRING COMMENT '用户id'
    ,`payment_way` STRING COMMENT '支付方式'
    ,`out_trade_no` STRING COMMENT '支付流水号'
    ,`create_time` STRING COMMENT '创建时间'
    ,`operate_time` STRING COMMENT '操作时间'
    ,`province_id`STRING COMMENT '省份'
)
COMMENT '订单表'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_order_deatail_di` 
(
    `id` STRING COMMENT '明细id'
    ,`order_id` STRING COMMENT '订单id'
    ,`sku_id` DOUBLE COMMENT '商品id'
    ,`sku_name` STRING COMMENT '商品名称'
    ,`order_price` DOUBLE COMMENT '购买价格'
    ,`sku_num` BIGINT COMMENT '购物数量'
    ,`create_time` STRING COMMENT '创建时间'
)
COMMENT '订单明细'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_sku_info_df` 
(
    `id` STRING COMMENT 'skuid'
    ,`spu_id` STRING COMMENT '订单id'
    ,`price` DOUBLE COMMENT '价格'
    ,`sku_name` STRING COMMENT '商品名称'
    ,`sku_desc` STRING COMMENT '商品描述'
    ,`weight` DOUBLE COMMENT '重量（千克）'
    ,`tm_id` STRING COMMENT '品牌 id'
    ,`category3_id` STRING COMMENT '品类id'
    ,`create_time` STRING COMMENT '创建时间'
)
COMMENT '商品信息'
PARTITIONED BY 
(
    ds STRING
)
;

drop table `ods_user_info_df` ;

CREATE TABLE `ods_user_info_df` 
(
    `id` STRING COMMENT '用户id'
    ,`name` STRING COMMENT '姓名'
    ,`birthday` STRING COMMENT '生日'
    ,`gender` STRING COMMENT '性别'
    ,`email` STRING COMMENT '邮箱'
    ,`user_level` STRING COMMENT '用户等级'
    ,`create_time` STRING COMMENT '创建时间'
)
COMMENT '用户信息'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_base_category3_df` 
(
    `id` STRING COMMENT '三级品类id'
    ,`name` STRING COMMENT '名称'
    ,`category2_id` STRING COMMENT '二级品类id'
)
COMMENT '三级品类信息'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_base_trademark_df` 
(
    `tm_id` STRING COMMENT '品牌id'
    ,`tm_name` STRING COMMENT '名称'
)
COMMENT '品牌信息'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_base_category1_df` 
(
    `id` STRING COMMENT '一级品类id'
    ,`name` STRING COMMENT '名称'
)
COMMENT '一级品类信息'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_base_category2_df` 
(
    `id` STRING COMMENT '二级品类id'
    ,`name` STRING COMMENT '名称'
    ,`category1_id` STRING COMMENT '一级品类id'
)
COMMENT '二级品类信息'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_payment_info_di` 
(
    `id` BIGINT COMMENT '编号'
    ,`out_trade_no` STRING COMMENT '对外业务编号'
    ,`order_id` STRING COMMENT '订单编号'
    ,`user_id` STRING COMMENT '用户编号'
    ,`alipay_trade_no` STRING COMMENT '支付宝交易流水号'
    ,`total_amount` DOUBLE COMMENT '支付类型'
    ,`subject` STRING COMMENT '交易内容'
    ,`payment_type` STRING COMMENT '支付类型'
    ,`payment_time` STRING COMMENT '支付时间'
)
COMMENT '支付流水表'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_base_region_df` 
(
    `id` BIGINT COMMENT '地区id'
    ,`region_name` STRING COMMENT '地区名称'
)
COMMENT '地区'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_base_province_df` 
(
    `id` BIGINT COMMENT '品牌id'
    ,`name` STRING COMMENT '名称'
    ,`region_id` STRING COMMENT '地区id'
)
COMMENT '省份'
PARTITIONED BY 
(
    ds STRING
)
;

--dwd明细数据层：清洗，维度退化。
--如ods地区+ods省份=>dwd省份地区
--dwd明细数据层建表语句
CREATE TABLE `dwd_order_info_di` (
    `id` STRING COMMENT '订单id',
    `total_amount` STRING COMMENT '订单总额',
    `order_status` STRING COMMENT ' 1 未支付 2 已支付 3 已发货 4 已收货 5 已完成',
    `user_id` STRING COMMENT '用户id',
    `payment_way` STRING COMMENT '付款方式',
    `out_trade_no` STRING COMMENT '订单流失号',
    `province_id` STRING COMMENT '省市id',
    `create_time` STRING COMMENT '创建时间',
    `operate_time` STRING COMMENT '修改时间'
)
COMMENT '订单表'
PARTITIONED BY (ds STRING);

CREATE TABLE `dwd_order_detail_di` (
    `id` STRING COMMENT '明细id',
    `order_id` STRING COMMENT '订单id',
    `user_id` STRING COMMENT '用户id',
    `sku_id` STRING COMMENT '商品id',
    `sku_name` STRING COMMENT '商品名称',
    `order_price` STRING COMMENT '购买价格',
    `sku_num` STRING COMMENT '购物数量',
    `province_id` STRING COMMENT '省市id',
    `create_time` STRING COMMENT '创建时间'
)
COMMENT '订单明细'
PARTITIONED BY (ds STRING )
;

CREATE TABLE `dim_sku_info_df`
(
    `id` STRING COMMENT '商品id'
    ,`spu_id` STRING COMMENT 'spuid'
    ,`price` DOUBLE COMMENT '商品价格'
    ,`sku_name` STRING COMMENT '商品名称'
    ,`sku_desc` STRING COMMENT '商品描述'
    ,`weight` DOUBLE COMMENT '重量'
    ,`tm_id` STRING COMMENT '品牌id'
    ,`tm_name` STRING COMMENT '品牌名称'
    ,`category3_id` STRING COMMENT '三级分类id'
    ,`category2_id` STRING COMMENT '二级分类id'
    ,`category1_id` STRING COMMENT '一级分类id'
    ,`category3_name` STRING COMMENT '三级分类名称'
    ,`category2_name` STRING COMMENT '二级分类名称'
    ,`category1_name` STRING COMMENT '一级分类名称'
    ,`create_time` STRING COMMENT '创建时间'
)
COMMENT '商品信息'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `dim_user_info_df` 
(
    `id` STRING COMMENT 'id'
    ,`name` STRING COMMENT '用户名称'
    ,`birthday` STRING COMMENT '生日'
    ,`gender` STRING COMMENT '性别'
    ,`email` STRING COMMENT '邮箱'
    ,`user_level` STRING COMMENT '等级'
    ,`create_time` STRING COMMENT '注册时间'
)
COMMENT '用户信息表'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `dim_base_province_df` 
(
    `id` STRING COMMENT 'id'
    ,`province_name` STRING COMMENT '省市id'
    ,`region_id` STRING COMMENT '地区id'
    ,`region_name` STRING COMMENT '地区名称'
)
COMMENT '地区省市表'
PARTITIONED BY 
(
    ds STRING
)
;

--手动将数据导入dwd层  --why 有的insert后面跟的分区有加值 有的不加
INSERT OVERWRITE TABLE dwd_order_info_di PARTITION(ds)
SELECT  id
        ,total_amount
        ,order_status
        ,user_id
        ,payment_way
        ,out_trade_no
        ,province_id
        ,create_time
        ,operate_time
        ,ds
FROM    ods_order_info_di
WHERE   ds = '${bizdate}'
AND     id IS NOT NULL
;

SELECT * FROM dwd_order_info_di WHERE ds='00000000';

INSERT OVERWRITE TABLE dwd_order_detail_di PARTITION(ds)
SELECT  od.id
        ,order_id
        ,oi.user_id
        ,sku_id
        ,sku_name
        ,order_price
        ,sku_num
        ,oi.province_id
        ,od.create_time
        ,od.ds
FROM    ods_order_deatail_di od
JOIN    ods_order_info_di oi
ON      od.order_id = oi.id
AND     oi.ds = '${bizdate}'
AND     od.ds = '${bizdate}'
AND     od.id IS NOT NULL
;

SELECT * FROM dwd_order_detail_di WHERE ds='00000000' LIMIT 10;

INSERT OVERWRITE TABLE dim_sku_info_df PARTITION(ds)
SELECT  sku.id
        ,sku.spu_id
        ,sku.price
        ,sku.sku_name
        ,sku.sku_desc
        ,sku.weight
        ,sku.tm_id
        ,tm.tm_name
        ,sku.category3_id
        ,c2.id category2_id
        ,c1.id category1_id
        ,c3.name category3_name
        ,c2.name category2_name
        ,c1.name category1_name
        ,sku.create_time
        ,sku.ds
FROM    (
            SELECT  *
            FROM    ods_sku_info_df
            WHERE   ds = '${bizdate}'
            AND     id IS NOT NULL
        ) sku
JOIN    ods_base_category3_df c3
ON      sku.category3_id = c3.id
AND     c3.ds = '${bizdate}'
JOIN    ods_base_category2_df c2
ON      c3.category2_id = c2.id
AND     c2.ds = '${bizdate}'
JOIN    ods_base_category1_df c1
ON      c2.category1_id = c1.id
AND     c1.ds = '${bizdate}'
JOIN    ods_base_trademark_df tm
ON      tm.tm_id = sku.tm_id
AND     tm.ds = '${bizdate}'
;

SELECT * FROM dim_sku_info_df WHERE ds='00000000' LIMIT 10;

INSERT OVERWRITE TABLE dim_user_info_df PARTITION(ds)
SELECT  id
        ,name
        ,birthday
        ,gender
        ,email
        ,user_level
        ,create_time 
        ,ds
FROM    ods_user_info_df
WHERE   ds = '${bizdate}'
AND     id IS NOT NULL
;
SELECT * FROM dim_user_info_df WHERE ds='00000000' LIMIT 10;

INSERT OVERWRITE TABLE `dim_base_province_df` PARTITION(ds)
SELECT  P.id
        ,P.name
        ,P.region_id
        ,r.region_name
        ,P.ds
FROM    ods_base_province_df P
JOIN    ods_base_region_df r
ON      P.region_id = r.id
AND     P.ds = '${bizdate}'
AND     r.ds = '${bizdate}'
;

SELECT * FROM dim_base_province_df WHERE ds='00000000' LIMIT 10;


--dws层针对明细粒度的数据进行短周期汇总。
--如最近一天某个类目、某个地区、某类人群购买商品销售总额、购买力分布。

--dws层建表语句
DROP TABLE IF EXISTS `dws_trade_detail_di` ;
-- CREATE TABLE `dws_trade_detail_di` 
-- (
--     `user_id` STRING COMMENT '用户'
--     ,`sku_id` STRING COMMENT '商品id'
--     ,`user_gender` STRING COMMENT '用户性别'
--     ,`user_age` STRING COMMENT '用户年龄'
--     ,`user_level` STRING COMMENT '用户等级'
--     ,`sku_price` DOUBLE  COMMENT '商品当日价格'
--     ,`sku_name` STRING COMMENT '商品名称'
--     ,`sku_category3_id` STRING COMMENT '商品三级品类id'
--     ,`sku_category2_id` STRING COMMENT '商品二级品类id'
--     ,`sku_category1_id` STRING COMMENT '商品一级品类id'
--     ,`sku_category3_name` STRING COMMENT '商品三级品类名称'
--     ,`sku_category2_name` STRING COMMENT '商品二级品类名称'
--     ,`sku_category1_name` STRING COMMENT '商品一级品类名称'
--     ,`spu_id`STRING COMMENT '商品的spu'
--     ,`tm_id` STRING COMMENT '品牌id'
--     ,`tm_name` STRING COMMENT '品牌名称'
--     ,`province_id` STRING COMMENT '省市id'
--     ,`province_name` STRING COMMENT '省市名称'
--     ,`region_id` STRING COMMENT '地区id'
--     ,`region_name` STRING COMMENT '地区名称'
--     ,`sku_num` BIGINT COMMENT '购买个数'
--     ,`order_count` BIGINT COMMENT '当日下单单数'
--     ,`order_amount` DOUBLE COMMENT '当日下单金额'
-- )
-- COMMENT '用户单日交易行为宽表'
-- PARTITIONED BY 
-- (
--     ds STRING
-- )
-- ;
CREATE TABLE `dws_trade_detail_di` 
(
    `user_id` STRING COMMENT '用户 id'
    ,`sku_id` STRING COMMENT '商品 Id'
    ,`user_gender` STRING COMMENT '用户性别'
    ,`user_age` STRING COMMENT '用户年龄'
    ,`user_level` STRING COMMENT '用户等级'
    ,`sku_price` DOUBLE COMMENT '商品当日价格'
    ,`sku_name` STRING COMMENT '商品名称'
    ,`sku_category3_id` STRING COMMENT '商品三级品类 id'
    ,`sku_category2_id` STRING COMMENT '商品二级品类 id'
    ,`sku_category1_id` STRING COMMENT '商品一级品类 id'
    ,`sku_category3_name` STRING COMMENT '商品三级品类名称'
    ,`sku_category2_name` STRING COMMENT '商品二级品类名称'
    ,`sku_category1_name` STRING COMMENT '商品一级品类名称'
    ,`spu_id` STRING COMMENT '商品 spu'
    ,`tm_id` STRING COMMENT '品牌 id'
    ,`tm_name` STRING COMMENT '品牌名称'
    ,`province_id` STRING COMMENT '省市 id'
    ,`province_name` STRING COMMENT '省市名称'
    ,`region_id` STRING COMMENT '地区 id'
    ,`region_name` STRING COMMENT '地区名称'
    ,`sku_num` BIGINT COMMENT '购买个数'
    ,`order_count` BIGINT COMMENT '当日下单单数'
    ,`order_amount` DOUBLE COMMENT '当日下单金额'
)
COMMENT '用户单日交易行为宽表'
PARTITIONED BY 
(
    ds STRING
)
;


--手动将数据导入dws层
-- WITH
--     tmp_trade AS ( SELECT od.user_id ,od.sku_id ,od.province_id ,sum(sku_num) sku_num ,COUNT(*) order_count ,sum(od.order_price*sku_num) order_amount FROM dwd_order_detail_di od WHERE od.ds='${bizdate}' GROUP BY od.user_id,od.sku_id,od.province_id )
-- INSERT OVERWRITE TABLE `dws_trade_detail_di` PARTITION( ds='${bizdate}' )
-- SELECT  tmp_trade.user_id
--         ,tmp_trade.sku_id
--         ,u.gender
--         ,months_between(
--             TO_CHAR(TO_DATE('${bizdate}','yyyymmdd'),'yyyy-mm-dd')
--             ,u.birthday
--         )/12 age
--         ,u.user_level
--         ,price
--         ,sku_name
--         ,category3_id
--         ,category2_id
--         ,category1_id
--         ,category3_name
--         ,category2_name
--         ,category1_name
--         ,spu_id
--         ,tm_id
--         ,tm_name
--         ,P.id
--         ,P.province_name
--         ,P.region_id
--         ,P.region_name
--         ,tmp_trade.sku_num
--         ,tmp_trade.order_count
--         ,tmp_trade.order_amount
-- FROM    tmp_trade
-- LEFT JOIN dim_user_info_df u
-- ON      u.id = tmp_trade.user_id
-- AND     u.ds = '${bizdate}' LEFT
-- JOIN    dim_sku_info_df s
-- ON      tmp_trade.sku_id = s.id
-- AND     s.ds = '${bizdate}'
-- LEFT JOIN dim_base_province_df P
-- ON      tmp_trade.province_id = P.id
-- AND     P.ds = '${bizdate}'
-- ;

WITH
    tmp_trade AS ( SELECT od.user_id,od.sku_id,od.province_id, sum(sku_num) sku_num, COUNT(*) order_count, sum(od.order_price*sku_num) order_amount FROM dwd_order_detail_di od WHERE od.ds='${bizdate}' GROUP BY od.user_id, od.sku_id, od.province_id )
INSERT OVERWRITE TABLE dws_trade_detail_di PARTITION(ds='${bizdate}')
SELECT  tmp_trade.user_id
        ,tmp_trade.sku_id
        ,u.gender
        ,months_between(
            to_char(to_date('${bizdate}','yyyymmdd'),'yy yy-mm-dd')
            ,u.birthday
        )/12 age
        ,u.user_level
        ,price
        ,sku_name
        ,category3_id
        ,category2_id
        ,category1_id
        ,category3_name
        ,category2_name
        ,category1_name
        ,spu_id
        ,tm_id
        ,tm_name
        ,pp.id
        ,pp.province_name
        ,pp.region_id
        ,pp.region_name
        ,tmp_trade.sku_num
        ,tmp_trade.order_count
        ,tmp_trade.order_amount
FROM    tmp_trade
LEFT JOIN dim_user_info_df u ON u.id = tmp_trade.user_id AND u.ds = '${bizdate}' 
LEFT JOIN dim_sku_info_df s ON tmp_trade.sku_id = s.id AND s.ds = '${bizdate}'
LEFT JOIN dim_base_province_df pp ON tmp_trade.province_id = pp.id AND pp.ds = '${bizdate}'
;

SELECT * FROM dws_trade_detail_di WHERE ds='00000000' LIMIT 10;
desc dws_trade_detail_di;
drop table if exists tmp_trade; --临时表不能drop    
SELECT * FROM dwd_order_detail_di WHERE ds='00000000' LIMIT 10;




--ADS层：针对某个维度，进行汇总。
--如针对年龄、地区、商品进行汇总统计，统计下单数、购买商品数、销售额、平均客单价四个指标。
CREATE TABLE `ads_trade_age_d` (
    `age` BIGINT COMMENT '年龄'
    ,`sku_num` BIGINT COMMENT '购买商品个数'
    ,`order_count` BIGINT COMMENT '订单个数'
    ,`order_amount` DOUBLE COMMENT '销售额'
    ,`avg_amount` DOUBLE COMMENT '平均客单价'
)
COMMENT '年龄销售统计'
PARTITIONED BY (ds STRING )
;

CREATE TABLE `ads_trade_province_d` 
(
    `province` STRING COMMENT '省份id'
    ,`province_name` STRING COMMENT '省市名称'
    ,`region_id` STRING COMMENT '地区id'
    ,`region_name` STRING COMMENT '地区名称'
    ,`sku_num` BIGINT COMMENT '购买商品个数'
    ,`order_count` BIGINT COMMENT '订单个数'
    ,`order_amount` DOUBLE COMMENT '销售额'
    ,`avg_amount` DOUBLE COMMENT '平均客单价'
)
COMMENT '地区销售统计'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ads_trade_sku_d` 
(
    `sku_id` STRING COMMENT '商品id'
    ,`sku_name` STRING COMMENT '商品名称'
    ,`sku_num` BIGINT COMMENT '购买商品个数'
    ,`category3_id` STRING COMMENT '三级分类id'
    ,`category2_id` STRING COMMENT '二级分类id'
    ,`category1_id` STRING COMMENT '一级分类id'
    ,`category3_name` STRING COMMENT '三级分类名称'
    ,`category2_name` STRING COMMENT '二级分类名称'
    ,`category1_name` STRING COMMENT '一级分类名称'
    ,`order_count` BIGINT COMMENT '订单个数'
    ,`order_amount` DOUBLE COMMENT '销售额'
    ,`avg_amount` DOUBLE COMMENT '平均客单价'
)
COMMENT '商品销售统计'
PARTITIONED BY 
(
    ds STRING
)
;

--手动将数据导入ADS层
INSERT OVERWRITE TABLE `ads_trade_age_d` PARTITION(ds='${bizdate}')
SELECT  round(td.user_age) age
        ,sum(sku_num) sku_num
        ,sum(order_count) order_count
        ,sum(order_amount) order_amount
        ,round(avg(order_amount),2) avg_amount
FROM    dws_trade_detail_di td
WHERE   ds = '${bizdate}'
GROUP BY ROUND(td.user_age)
;

SELECT *  FROM ads_trade_age_d WHERE ds='00000000' LIMIT 10;

INSERT OVERWRITE TABLE ads_trade_province_d PARTITION(ds='${bizdate}')
SELECT  td.province_id
        ,td.province_name
        ,td.region_id
        ,td.region_name
        ,sum(sku_num) sku_num
        ,sum(order_count) order_count
        ,sum(order_amount) order_amount
        ,round(AVG(order_amount),2) avg_amount
FROM    dws_trade_detail_di td
WHERE ds='${bizdate}'
GROUP BY td.province_id
         ,td.province_name
         ,td.region_id
         ,td.region_name
;
SELECT *  FROM ads_trade_province_d WHERE ds='00000000' LIMIT 10;


INSERT OVERWRITE TABLE ads_trade_sku_d PARTITION (ds='${bizdate}')
SELECT 
    td.sku_id
    ,td.sku_name
    ,td.sku_category3_id
    ,td.sku_category2_id
    ,td.sku_category1_id
    ,td.sku_category3_name
    ,td.sku_category2_name
    ,td.sku_category1_name
    ,SUM(sku_num) sku_num
    ,SUM(order_count) order_count
    ,SUM(order_amount) order_amount
    ,ROUND(AVG(order_amount),2) avg_amount
FROM dws_trade_detail_di td 
WHERE ds='${bizdate}'
GROUP BY td.sku_id
    ,td.sku_name
    ,td.sku_category3_id
    ,td.sku_category2_id
    ,td.sku_category1_id
    ,td.sku_category3_name
    ,td.sku_category2_name
    ,td.sku_category1_name
;
SELECT *  FROM ads_trade_age_d WHERE ds='00000000' LIMIT 10; --这里有\n原因在上


--全流程调度完成后 查询一下
SELECT * FROM ads_trade_age_d WHERE ds='00000000' LIMIT 10;
SELECT * FROM ads_trade_sku_d WHERE ds='00000000' LIMIT 10; --因为上面的原因一直\n但调度上没有问题
SELECT * FROM ads_trade_province_d WHERE ds='00000000' LIMIT 10;

离线同步test

--odps sql 
--********************************************************************--
--author:i
--create time:0000-00-00 00:00:00
--********************************************************************--

--每日全量表同步测试
SELECT * FROM ods_user_info_df WHERE ds='00000000';
SELECT * FROM ods_base_category1_df WHERE ds='00000000';
SELECT * FROM ods_base_category2_df WHERE ds='00000000';
SELECT * FROM ods_base_category3_df WHERE ds='00000000';
SELECT * FROM ods_base_province_df WHERE ds='00000000';
SELECT * FROM ods_base_region_df WHERE ds='00000000';
SELECT * FROM ods_base_trademark_df WHERE ds='00000000';
SELECT * FROM ods_sku_info_df WHERE ds='00000000';
--每日增量表同步测试
-- rename ods_order_deatail_di TO ods_order_detail_di;
SELECT * FROM ods_order_deatail_di WHERE ds='00000000';
SELECT * FROM ods_order_info_di WHERE ds='00000000';

注：仅做笔记学习使用。

大数据小阿姨
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
离线电商数仓3.0项目即席查询复盘笔记
m0_46168848的博客
03-27 2746
离线电商数仓3.0的即席查询笔记 kylin的简介与架构 Apache Kylin 是一个开源的分布式分析引擎，提供 Hadoop/Spark 之上的 SQL 查询接口及多维分析（OLAP）能力以支持超大规模数据，最初由 eBay Inc 开发并贡献至开源社区。它能在亚秒内查询巨大的 Hive 表。 kylin架构 1）REST Server REST Server 是一套面向应用程序开发的入口点，旨在实现针对 Kylin 平台的应用开发工作。 此类应用程序可以提供查询、获取结果、触发 cube 构建任务
漫谈大数据 - 基于SparkSQL的离线数仓
热门推荐
爱意随风起，风止意难平
04-13 3万+
数据仓库，英文名称为Data Warehouse，可简写为DW或DWH。数据仓库，是为企业所有级别的决策制定过程，提供所有类型数据支持的战略集合。它是单个数据存储，出于分析性报告和决策支持目的而创建。 为需要业务智能的企业，提供指导业务流程改进、监视时间、成本、质量以及控制。
离线数仓——常见的日志主题&指标&SQL
weixin_45408791的博客
09-20 1196
离线数仓，指标，大数据，sql
表变量和临时表
kimi的博客
09-27 666
https://www.cnblogs.com/CareySon/archive/2012/06/11/TableVariableAndTempTable.html
odps嵌套子查询
人生就像一场戏
08-07 566
insert overwrite table tmp12_cdm_cs_ark_ocs_detl partition(pt = '${bizdate}000000') select dt12.wo_id, to_char(dt12.ocs_date, 'yyyy-MM') as complaint_month, case when t1.transfer_source='厦门' then t1.complaint_source when t1.transfe.
odps-eclipse-plugin-bundle-0.16.0.zip
06-17
而"odps-eclipse-plugin-bundle-0.16.0.zip"则是一个针对ODPS开发的Eclipse集成开发环境（IDE）插件，版本为0.16.0，方便开发者在Eclipse环境中进行ODPS相关的项目开发和管理。 该插件的安装和使用是其核心知识点之...
阿里云java源码-aliyun-odps-jdbc:用于ODPS的JDBC驱动程序
06-06
>odps-jdbc</ artifactId > < version >VERSION</ version > </ dependency > 入门 使用 ODPS JDBC 驱动程序就像使用其他 JDBC 驱动程序一样。 它包含以下几个步骤： 1. 使用Class.forName()显式加载 ...
aliyun-kettle-odps-plugin-1.0.0.tar.gz
10-31
kettle使用maxcompute相关插件，aliyun-kettle-odps-plugin-1.0.0.tar.gz
aliyun-kettle-odps-plugin-1.0.0包以及安装操作文件.zip
03-08
aliyun-kettle-odps-plugin-1.0.0包以及安装操作文件,适合在使用maxcompute时用kettle将数据导出，方便实用
odps-jbbc for kettle
10-17
odps-jbbc for kettle
实时计算与odps离线计算
database_zbye的专栏
09-28 1065
本文用于记录数据计算的相关学习资料，等沉淀足够，再自己撰写学习总结。 Hbase的表结构介绍       http://www.jb51.net/article/31172.htm Hbase基本操作      http://blog.csdn.net/u010967382/article/details/37878701
阿里天池odps_SQL查看更多数据
芳草碧连天
10-30 1574
一不小心就入了天池的“坑”，感觉天池比赛是你机器学习数据挖掘理论结合实际很好的平台。但是缺点就是入门比较麻烦，最好有个大神或者最起码做过几次比赛的人能带你，不然入门真的很麻烦。可惜天池比赛上有很多常年参加的大牛选手都喜欢和相同水平的人组队（可以理解），而且他们相互加过QQ，组群讨论，所以想进他们的圈子还是很难的。 　　不说那么多了，赶快进入正题。在阿里比赛的时候一般会有初赛和复赛，复赛会在阿里自己
阿里云-DataWorks- ODPS SQL开发
fyydlz的博客
01-01 6934
阿里云 数据仓库这一系列断断续续也有很久没有更新了，新年新气象，赶紧赶上开写。 ODPS；是由阿里云自主研发，提供针对TB/PB级数据、实时性要求不高的分布式处理能力，应用于数据分析、挖掘、商业智能等领域；阿里巴巴的离线数据业务都运行在ODPS上；
ODPS（MaxCompute ）
西风秋月花满楼的博客
08-06 1387
ODPS（Open Data Processing Service），是阿里巴巴通用计算平台提供的一种快速、完全托管的 GB/TB/PB 级数据仓库解决方案，现在已更名为MaxCompute，MaxCompute 向用户提供了完善的数据导入方案以及多种经典的分布式计算模型，能够更快速的解决用户海量数据计算问题，有效降低企业成本，并保障数据安全。 学习地址：http://www.aiwanba.n...
SQL中的临时表
SQL数据库开发
08-24 1万+
点击关注上方“SQL数据库开发”，设为“置顶或星标”，第一时间送达干货SQL专栏SQL基础知识第二版SQL高级知识第二版临时表定义临时表与实体表类似，只是在使用过程中，临时表是存储在系统数...
基于ODPS的SQL语句
zakexu的专栏
09-30 3万+
本博文主要是介绍阿里ODPS（Open Data Processing Service）平台上的SQL语言的入门； （一）基本概念 1.ODPS SQL适用于海量数据(TB级别)，实时性要求不高的场合，它的每个作业的准备，提交等阶段要花费较长时间， 因此要求每秒处理几千至数万笔事务的业务是不能用ODPS SQL完成的；ODPS SQL采用的是类似于SQL的语法，可以看作是标准SQL的子集，但不
大数据基础技能入门指南
淘系技术
10-11 1468
本文介绍了数据工作中数据基础和复杂数据查询两个基础技能。背景当下，不管是业务升级迭代项目，还是体验优化项目，对于数据的需求都越来越大。数据需求主要集中在以下几个方面：项目数据看板搭建：特别是一些AB实验的看板，能直观呈现项目的核心数据变化数据分析：项目启动前的探索挖掘以及项目后的效果分析但是，眼下存在的一个普遍矛盾是：日益增长的数据需求和落后的数据生产力之前的矛盾俗话说，求人不如求己，掌握基础的数...
一些 odps sql技巧
yichudu
05-24 6604
日期行为汇总 有张表为pv_daily_table, 在不同partitionds 中记录每篇内容每日的pv, 那么想汇总每篇内容近3日, 近7日的pv, 就可以用下面的sql. SELECT content_id , SUM(IF(ds = '${ds}', pv, 0)) AS today_pv -- 当日pv , SUM(IF(ds &gt; '${ds3...
Spark3+Clickhouse+Hadoop大数据实战课程
04-13
数据分析职业是一个多金的职业，数据分析职位是一个金饭碗的职位，前景美好，但是要全面掌握大数据分析技术，非常困难，大部分学员的痛点是不能快速找到入门要点，精准快速上手。本课程采用项目驱动的方式，以Spark3和Clickhouse技术为突破口，带领学员快速入门Spark3+Clickhouse数据分析，促使学员成为一名高效且优秀的大数据分析人才。学员通过本课程的学习，不仅可以掌握使用Python3进行Spark3数据分析，还会掌握利用Scala/java进行Spark数据分析，多语言并进，力求全面掌握；另外通过项目驱动，掌握Spark框架的精髓，教导Spark源码查看的技巧；会学到Spark性能优化的核心要点，成为企业急缺的数据分析人才；更会通过Clickhouse和Spark搭建OLAP引擎，使学员对大数据生态圈有一个更加全面的认识和能力的综合提升。真实的数据分析项目，学完即可拿来作为自己的项目经验，增加面试谈薪筹码。课程涉及内容：Ø  Spark内核原理（RDD、DataFrame、Dataset、Structed Stream、SparkML、SparkSQL）Ø  Spark离线数据分析(千万简历数据分析、雪花模型离线数仓构建)Ø  Spark特征处理及模型预测Ø  Spark实时数据分析(Structed Stream)原理及实战Ø  Spark+Hive构建离线数据仓库（数仓概念ODS/DWD/DWS/ADS）Ø  Clickhouse核心原理及实战Ø  Clickhouse engine详解Ø  Spark向Clickhouse导入简历数据，进行数据聚合分析Ø  catboost训练房价预测机器学习模型Ø  基于Clickhouse构建机器学习模型利用SQL进行房价预测Ø  Clickhouse集群监控，Nginx反向代理Grafana+Prometheus+Clickhouse+node_exporterØ  Spark性能优化Ø  Spark工程师面试宝典       课程组件：集群监控：福利：本课程凡是消费满359的学员，一律送出价值109元的实体书籍.
ODPS-0410031报错
最新发布
05-16
ODPS-0410031是阿里云的数据处理平台ODPS返回的错误码之一，表示某个任务在运行时发生了错误。具体来说，这个错误码通常会出现在以下几种情况下： 1. 用户没有足够的权限执行某个操作； 2. 用户提交的SQL语句存在...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值