数据平台基础记录

1、基本概念

ETL(Extraction-Transformation-Loading) 数据抽取、转换和加载

ODS —— 操作性数据
DW —— 数据仓库
DM —— 数据集市

网友整理

2、常用查询

模糊查询表名

1	show tables like 'name'

查看表结构信息

1	desc formatted 表名

3、日活数据统计

3.1、基础数据

新增用户 / 活跃用户 / 启动/访问次数 / 人均使用时长

检查数据发现当用户未登录状态时，写入的user_event.user_id为空统计结果时未作区分 20190816当天数据都是未登录状态下产生的统计结果新增和活跃用户均为：1

查询当日活跃用户数量「表名」-「user_page」

1	SELECT count() as total FROM (SELECT count() FROM datacenter.user_event where created_ymd = '20190819' GROUP BY user_id) a ;

统计当日页面停留时长

1️⃣规则：有A、B两个页面统计页面停留时长 A页面停留时长=B.开始时间 - A.开始时间 B页面停留时长=B.end_time - b.start_time

– 计算人均使用时长

– 时间不允许就用了比较麻烦的办法一共三条SQL

– ①先查询出当日所有活跃用户数 ②在查询出当日生成的所有trackID③计算每个trackID的时长（因为最后一条数据是需要end_time-start_time 所以单独计算）所有trackID相加为当日用户在线时长

– ① 统计当日活跃用户数

1	SELECT COUNT() AS '当日活跃用户数' FROM (SELECT FROM datacenter.user_page where created_ymd = '20190819' GROUP BY track_id) a;

– ②查询当日所有trackID

1	SELECT track_id FROM datacenter.user_page WHERE created_ymd = '20190922' GROUP BY track_id;

# ③计算某个trackid的时长（2结果相加为当前trackID时长）

SELECT
	sum( ( UNIX_TIMESTAMP( e.start_time ) - UNIX_TIMESTAMP( b.start_time ) ) ) AS '当前trackID时长',
	(
	SELECT
		( UNIX_TIMESTAMP( end_time ) - UNIX_TIMESTAMP( ( start_time ) ) ) AS '最后一条时长' 
	FROM
		datacenter.user_page 
	WHERE
		track_id = '135A6B1B-E35A-4CA4-953E-E670F6339AE3' 
	ORDER BY
		start_time DESC 
		LIMIT 1 
	) AS '最后一条的时长' 
FROM
	(
	SELECT
		a.start_time,
		( @i := @i + 1 ) AS ord_num 
	FROM
		user_page a,
		( SELECT @i := 1 ) d 
	WHERE
		a.track_id = '135A6B1B-E35A-4CA4-953E-E670F6339AE3' 
	ORDER BY
		 a.start_time  
	) b
	INNER JOIN (
		 SELECT
		 a.start_time,
		 ( @j := @j + 1 ) AS ord_num  
	FROM
		 user_page a,
		 ( SELECT @j := 0 ) c 
	WHERE
		 a.track_id = '135A6B1B-E35A-4CA4-953E-E670F6339AE3'  
	ORDER BY
	 a.start_time 
	) e ON b.ord_num = e.ord_num;