雪花云数据平台sql Count Distinct User id基于列字符串值

Question

我正在尝试创建一个新的数据表，它在两个单独的列中包含每周的用户总数，这是Platform = 'Web'中不同用户的计数和来自平台= 'iOS'或'Android'的不同用户计数

我的尝试下面没有工作。有人能给点建议吗?

数据来源:

DATE     USER_ID  Platform
1/1/2020  1223    Web
1/2/2020  2032    iOS
1/3/2020  2432    Android
1/4/2020  20311   iOS
1/4/2020  2443    Android

SQL尝试

SELECT DATE_TRUNC(week, d.DATE)  as DATE,
COUNT(DISTINCT d.USER_ID WHERE d.PLATFORM = 'Web') AS USER_COUNT_WEB,
COUNT(DISTINCT d.USER_ID WHERE d.PLATFORM = 'Android' OR 'iOS') AS USER_COUNT_PHONE
FROM data d
GROUP BY 1;

目标表:

DATE     User_Count_Web  User_count_Phone
1/1/2020  12              230
1/8/2020  20              442
1/15/2020 24              533

Answer 1

有一种方法:

SELECT
DATE_TRUNC(week, d.DATE) as DATE,
COUNT(DISTINCT case when d.PLATFORM = 'Web' then d.USER_ID end) AS USER_COUNT_WEB,
COUNT(DISTINCT case when d.PLATFORM in ('Android','iOS') then d.USER_ID end) AS USER_COUNT_PHONE
FROM data d
GROUP BY 1;

Answer 2

sum/case模式对于这种"透视"查询也很有用。-

SELECT
WEEK(date),
SUM(CASE WHEN platform='Web' THEN 1 ELSE 0 END) AS count_web,
SUM(CASE WHEN platform IN ('iOS','Android') THEN 1 ELSE 0 END) AS count_phone
FROM data
GROUP BY WEEK(date)

(week()函数是特定于mysql的，并且这个答案将周作为从年初开始的整数偏移量而不是作为实际日期，但思想是相同的。)