用户使用的语言组合分析思路与例子

在分析用户使用习惯时,我们需要知道有多少用户:只使用过英文的系统语言,以此来推断英文用户的比例。

为此我们需要记录用户的访问明细,然后从中分析每个用户的语言组合。

测试环境:http://sqlfiddle.com/#!15/fea3d/7

1. 测试数据

以PostgreSQL数据库为例

CREATE TABLE user_visit
    ("uid" varchar(10), "add_date" date, "language" varchar(20))
;

INSERT INTO user_visit
    ("uid", "add_date", "language")
VALUES
    ('u01', '2016-09-01 00:00:00', 'en'),
    ('u01', '2016-09-02 00:00:00', 'en'),
    ('u01', '2016-09-06 00:00:00', 'zh-cn'),
    ('u02', '2016-09-01 00:00:00', 'zh-cn'),
    ('u03', '2016-09-03 00:00:00', 'en'),
    ('u03', '2016-09-07 00:00:00', 'en'),
    ('u04', '2016-09-01 00:00:00', 'en'),
    ('u04', '2016-09-02 00:00:00', 'en'),
    ('u05', '2016-09-01 00:00:00', 'zh-cn'),
    ('u06', '2016-09-07 00:00:00', 'zh-cn')
;

2. 按用户ID去重

select distinct uid, language 
from user_visit 
order by uid
uid language
u01 zh-cn
u01 en
u02 zh-cn
u03 en
u04 en
u05 zh-cn
u06 zh-cn

3. 获得每个用户使用的语言组合

select uid, string_agg(language, ',' order by language asc) as language_list
from (
    select distinct uid, language 
    from user_visit
) a
group by uid
uid language_list
u01 en,zh-cn
u02 zh-cn
u03 en
u04 en
u05 zh-cn
u06 zh-cn

从中可以看出:u01 使用了多种语言,其他用户都始终使用一种语言。

不同数据库的字段值合并函数:

  • PostgreSQL: string_agg
  • Redshift: listagg
  • mysql: group_concat

参考:https://www.periscopedata.com/blog/listagg-group-concat-and-string-agg.html

4. 每种语言组合的人数

select language_list, count(*) as user_count
from (
  select uid, string_agg(language, ',' order by language asc) as language_list
  from (
    select distinct uid, language 
    from user_visit
  ) a
  group by uid
) b
group by 1
order by 2 desc
language_list user_count
zh-cn 3
en 2
en,zh-cn 1

由此可以看出英文用户占了三分之一。

总体来讲,通过系统语言分析海外用户的比例要比按IP更为准确,可以排除:IP库准确度和用户使用vpn的情况。这儿的查询稍作修改,也可以用于分析用户的不同功能的使用习惯。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>