在分析用户使用习惯时，我们需要知道有多少用户：只使用过英文的系统语言，以此来推断英文用户的比例。
为此我们需要记录用户的访问明细，然后从中分析每个用户的语言组合。
测试环境：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的情况。这儿的查询稍作修改，也可以用于分析用户的不同功能的使用习惯。