SQL_ODPS-D2-离线数仓-5-开窗函数在sql中的实际应用

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