SQL_ODPS-D2-离线数仓-5-开窗函数在sql中的实际应用.md
--手动将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