HIVE消费者画像

概述

• 消费者画像，是以消费者ID(通常是用户ID)作唯一标识，统计消费者的各项指标
• 通常业务系统数据库没有专门存储消费者的表，只有用户信息表
  用户注册后，并不一定会消费，消费的用户占比可能很小
• 消费者画像的构建 需要借助 用户维度表 和 子订单明细表

常见指标

累计金额、近期金额（近1天、7天、30天金额）
累计订单数、近期订单数
累计商品数、近期商品数
最近1次消费时间、最早1次消费时间
最近1次消费地址、最多次数地址
消费地区(1~n个)
曾购品牌(1~n个)
曾购类目(1~n个)
购买间隔
平均每次金额(=累计金额/累计订单数)
……

SQL

不展示全部SQL，只展示拆解后的关键部分的最简模板

• 计算 最近1次消费的收货地址 使用 窗口函数
• 如果 dwt累积消费者 每天都用 dws每日消费者 来合并，计算量就会很大
  改进：昨天分区dws消费者 full out join 前天分区dwt累计消费者

最近1次消费地址

ROW_NUMBER

WITH
-- 原始数据
t AS (
  SELECT 'u1' AS uid,'佛山' AS address,'2022-01-01' AS order_date UNION ALL
  SELECT 'u2' AS uid,'深圳' AS address,'2022-01-02' AS order_date UNION ALL
  SELECT 'u2' AS uid,'广州' AS address,'2022-01-03' AS order_date UNION ALL
  SELECT 'u3' AS uid,'佛山' AS address,'2022-01-04' AS order_date
),
-- 开窗：按用户ID分区，分区内按日期降序
t1 AS (
  SELECT
    uid
    ,address
    ,order_date
    ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY order_date desc) AS a_row_number
  FROM t
)
-- 过滤
SELECT
  uid
  ,address AS last_address
  ,order_date AS last_order_date
FROM t1
WHERE a_row_number=1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

dwt累计消费者

全外联

WITH
t1 AS (
  SELECT 'u1' AS uid,10 AS amount UNION ALL
  SELECT 'u2' AS uid,20 AS amount
),
t2 AS (
  SELECT 'u2' AS uid,30 AS amount UNION ALL
  SELECT 'u3' AS uid,40 AS amount
)
SELECT
  NVL(t1.uid,t2.uid) AS uid,
  NVL(t1.amount,0)+NVL(t2.amount,0) AS amount
FROM t1
FULL OUTER JOIN t2 ON t1.uid=t2.uid;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

最近7天

常见指标有：最近1、3、7、30天的下单数、退单数、下单金额、退单金额…

-- 日期筛选
WITH a AS (
  SELECT '2022-08-01' AS y UNION ALL
  SELECT '2022-08-02' AS y UNION ALL
  SELECT '2022-08-03' AS y UNION ALL
  SELECT '2022-08-04' AS y UNION ALL
  SELECT '2022-08-05' AS y UNION ALL
  SELECT '2022-08-06' AS y UNION ALL
  SELECT '2022-08-07' AS y UNION ALL
  SELECT '2022-08-08' AS y
)
SELECT y FROM a
WHERE y>DATE_SUB('2022-08-07',7) AND y<='2022-08-07'
ORDER BY y;
-- 查询结果：2022-08-01~2022-08-07
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 按今天算，最近7天
WHERE y>DATE_SUB(CURRENT_DATE(),7) AND y<=CURRENT_DATE()

-- 按昨天算，最近7天
WHERE y>=DATE_SUB(CURRENT_DATE(),7) AND y<CURRENT_DATE()
1
2
3
4
5

-- 脚本中
SELECT
  uid,
  COUNT(IF(y="{ymd}",order_id,NULL)),             -- 近1天下单数`,
  COUNT(IF(y>DATE_SUB("{ymd}",7),order_id,NULL)), -- 近7天下单数`,
  COUNT(order_id)                                 -- 近30天下单数`
FROM dwd_order_i
WHERE y>DATE_SUB("{ymd}",30) AND y<="{ymd}"
GROUP BY uid;
1
2
3
4
5
6
7
8
9

---

少吃零食多看书