1 DWS层
使用用户购买商品明细宽表作为DWS数据
2 ADS层
2.1 建表语句
drop table if exists ads_goods_order_count_day; create external table ads_goods_order_count_day( dt string comment '统计日期', sku_id string comment '商品id', order_count bigint comment '下单次数' ) COMMENT '商品下单top10' stored as parquet location '/warehouse/gmall/dws/ads_goods_order_count_day/';View Code
2.2 导入数据
-----------------------------需求-商品每日下单排行Top10----------------------
-----------------------------相关表---------------------
dws_sale_detail_daycount: 每个用户每天的购买的商品明细
-----------------------------思路-----------------------
求今日的销售明细,按商品分组,统计数量,排序取前十
-----------------------------SQL------------------------
insert into TABLE ads_goods_order_count_day
select
'2020-02-16',sku_id,count(*) order_count
from dws_sale_detail_daycount
where dt='2020-02-16'
group by sku_id
order by order_count desc
limit 10