2. 部署指南

Doris - 数据模型,事件分析适配,留存分析适配,漏斗分析适配 SQL clickhouse nosql

一. 数据模型

1.1 聚合模型

数据写入时，按照维度列对其他指标列进行聚合操作，操作结果：多条数据如果所有维度列相同，那么会对所有指标列进行聚合。

-- 1. 创建表
CREATE TABLE IF NOT EXISTS zgg.user
(
    `city` VARCHAR(20) COMMENT "城市",
    `age` SMALLINT COMMENT "年龄",
    `sex` TINYINT COMMENT "性别",
    `pv` BIGINT SUM DEFAULT "0" COMMENT "pv",
    `min_time` INT MIN DEFAULT "0" COMMENT "最小停留时间",
    `max_time` INT MAX DEFAULT "0" COMMENT "最大停留时间"
)
AGGREGATE KEY(`city`, `age`, `sex`)
DISTRIBUTED BY HASH(`city`) BUCKETS 3;
-- 2. 写入数据
INSERT INTO zgg.user VALUES('北京', 25, 1, 1, 50, 100);
INSERT INTO zgg.user VALUES('北京', 20, 0, 1, 150, 300);
-- 3. 写入相同数据
INSERT INTO zgg.user VALUES('北京', 25, 1, 1, 15, 3100);

1.2 Unique 模型

通过定义主键保证数据的唯一性，Unique 模型简化了数据导入流程，能够更好地支撑实时和频繁更新的场景。

-- 1. 创建表
CREATE TABLE IF NOT EXISTS zgg.user_unique
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `city` VARCHAR(20) COMMENT "城市",
    `age` SMALLINT COMMENT "年龄",
    `sex` TINYINT COMMENT "性别"
)
UNIQUE KEY(`user_id`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 3;
-- 2. 写入数据
INSERT INTO zgg.user_unique VALUES(1, '北京', 25, 1);
INSERT INTO zgg.user_unique VALUES(2, '上海', 35, 0);
-- 3. 写入主键相同数据
INSERT INTO zgg.user_unique VALUES(1, '沈阳', 15, 1);

1.3 Duplicate 模型

用于存储原始数据，允许重复数据

-- 1. 创建表
CREATE TABLE IF NOT EXISTS zgg.user_duplicate
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `city` VARCHAR(20) COMMENT "城市",
    `age` SMALLINT COMMENT "年龄",
    `sex` TINYINT COMMENT "性别"
)
DUPLICATE KEY(`user_id`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 3;
-- 2. 写入数据
INSERT INTO zgg.user_duplicate VALUES(1, '北京', 25, 1);
INSERT INTO zgg.user_duplicate VALUES(2, '上海', 35, 0);
-- 3. 写入主键相同数据
INSERT INTO zgg.user_duplicate VALUES(1, '沈阳', 15, 1);


二.事件分析适配：分页面 PV、分页面 UV

2.1使用 Duplicate 模型存储原始数据

CREATE TABLE IF NOT EXISTS zgg.user_all
(
    `date` DATE NOT NULL COMMENT "时间",
    `page` VARCHAR(20) NOT NULL COMMENT '页面',
    `user_id` LARGEINT NOT NULL COMMENT "用户id"
)
DUPLICATE KEY(`date`, `page`, `user_id`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;
-- 插入测试数据
INSERT INTO zgg.user_all VALUES('2022-11-18', 'login', 1);
INSERT INTO zgg.user_all VALUES('2022-11-18','login', 2);
INSERT INTO zgg.user_all VALUES('2022-11-18','order', 1);
INSERT INTO zgg.user_all VALUES('2022-11-18','order', 1);
INSERT INTO zgg.user_all VALUES('2022-11-18','order', 2);
INSERT INTO zgg.user_all VALUES('2022-11-18','pay', 1);
INSERT INTO zgg.user_all VALUES('2022-11-18','pay', 1);

2.2使用聚合模型计算分页面 PV

-- 1. 创建表
CREATE TABLE IF NOT EXISTS zgg.user_pv
(
    `date` DATE NOT NULL COMMENT "时间",
    `page` VARCHAR(20) NOT NULL COMMENT '页面',
    `pv` BIGINT SUM DEFAULT "0" COMMENT "pv"
)
AGGREGATE KEY(`date`, `page`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;
-- 2. 使用INSERT INTO table SELECT ...
INSERT INTO zgg.user_pv SELECT `date`, `page`, 1 FROM zgg.user_all;

2.3使用 Unique 模型计算分页面 UV

-- 1. 创建表
CREATE TABLE IF NOT EXISTS zgg.user_uv
(
    `date` DATE NOT NULL COMMENT "时间",
    `page` VARCHAR(20) NOT NULL COMMENT '页面',
    `user_id` LARGEINT NOT NULL COMMENT "用户id"
)
UNIQUE KEY(`date`, `page`, `user_id`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;
-- 2. 使用INSERT INTO table SELECT ...
INSERT INTO zgg.user_uv SELECT `date`, `page`, `user_id` FROM zgg.user_all;
-- 3. 使用COUNT查询UV
SELECT date, page, count(user_id) uv 
  FROM  zgg.user_uv 
 GROUP BY date, page;

三.留存分析适配：用户注册后 7 日内下订单比重

使用 Unique 模型

-- 1. 注册表
CREATE TABLE IF NOT EXISTS zgg.user_register
(
    `date` DATE NOT NULL COMMENT "时间",
    `user_id` LARGEINT NOT NULL COMMENT "用户id"
)
UNIQUE KEY(`date`, `user_id`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;
-- 2. 订单表
CREATE TABLE IF NOT EXISTS zgg.user_order
(
    `date` DATE NOT NULL COMMENT "时间",
    `user_id` LARGEINT NOT NULL COMMENT "用户id"
)
UNIQUE KEY(`date`, `user_id`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;
-- 3. 获取近7天注册总用户
SELECT COUNT(t1.user_id) cnt
  FROM  zgg.user_register t1
   AND  t1.`date` BETWEEN '2022-11-12' AND '2022-11-18';
   
-- 4. 获取近7天注册用户中下订单的用户
SELECT COUNT(t2.user_id)
  FROM  zgg.user_register t1, zgg.user_order t2
 WHERE  t1.user_id = t2.user_id
   AND  t1.`date` BETWEEN '2022-11-12' AND '2022-11-18';

四.漏斗分析适配：订单支付：用户注册 > 挑选商品 > 提交订单 > 支付

使用聚合模型 + Bitmap

-- 1. 创建表
CREATE TABLE IF NOT EXISTS zgg.user_funnel
(
    `date` DATE NOT NULL COMMENT "时间",
    `page` VARCHAR(20) NOT NULL COMMENT '页面',
    `user_id` BITMAP BITMAP_UNION NULL COMMENT "用户id"
)
AGGREGATE KEY(`date`, `page`)
DISTRIBUTED BY HASH(`date`) BUCKETS 3;
-- 2. 写入模拟数据
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'register', to_bitmap(1));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'register', to_bitmap(2));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'register', to_bitmap(3));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'register', to_bitmap(4));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'shopping', to_bitmap(1));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'shopping', to_bitmap(2));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'shopping', to_bitmap(3));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'order', to_bitmap(1));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'order', to_bitmap(2));
INSERT INTO zgg.user_funnel VALUES('2022-11-18', 'pay', to_bitmap(1));
-- 3. 通过bitmap函数：bitmap_count和bitmap_union组合以及intersect_count
SELECT 'register' page, bitmap_count(bitmap_union(user_id)) cnt   
  FROM  zgg.user_funnel
 UNION ALL
-- register -> shopping
SELECT 'register -> shopping' page, intersect_count(user_id, page, 'register', 'shopping') cnt
  FROM zgg.user_funnel 
 UNION ALL
-- register -> shopping -> order
SELECT 'register -> shopping -> order' page, intersect_count(user_id, page, 'register', 'shopping', 'order') cnt
  FROM zgg.user_funnel 
 UNION ALL
-- register -> shopping -> order -> 'pay'
SELECT 'register -> shopping -> order-> pay' page, intersect_count(user_id, page, 'register', 'shopping', 'order', 'pay') cnt
  FROM zgg.user_funnel;


签名：这个人很懒，什么也没有留下！
收藏的用户（0） X
正在加载信息~
猜你喜欢：
最新回复 (0)
返回
发新帖
作者最近主题：
热门主题：
无