查询近30天活跃用户
讨论 1
常规统计
先计算日期类型。截至 2019-07-27(包含2019-07-27),这天之后的也要判断
统计符合这段时间区间的用户数。根据每日的id来统计,要去重(单个用户一天浏览多次属1个)
SELECT activity_date as day, count(DISTINCT user_id) as active_users
FROM Activity
WHERE 0 <= DATEDIFF('2019-07-27',activity_date)
AND DATEDIFF('2019-07-27',activity_date) <30
GROUP BY activity_date
讨论 2
group by
# Write your MySQL query statement below
select activity_date as day,count(distinct user_id) as active_users
from
Activity
where
activity_date between '2019-06-28' and '2019-07-27'
group by
activity_date;
每天的领导和合伙人
讨论 1
用DISTINCT
关键字过滤重复的数据
然后count
统计个数
最后group by
# group by 有一个原则:
# group by 后面的列名,只能是select后面列中没有使用过聚合函数的列
SELECT date_id,make_name,
count(DISTINCT lead_id)as unique_leads,
count(DISTINCT partner_id)as unique_partners
FROM DailySales
GROUP BY date_id,make_name
group by
的意思为分组汇总
使用了group by
后,要求Select
出的结果字段都是可汇总的,否则就会出错
group by
有一个原则,就是 select
后面的所有列中,没有使用聚合函数的列,必须出现在 group by
后面
讨论 2
select
date_id,
make_name,
count(distinct lead_id) as unique_leads,
count(distinct partner_id) as unique_partners
from dailysales
group by date_id, make_name
求关注者的数量
讨论 1
group by 分组
count() 统计数量
order by 排序
# Write your MySQL query statement below
select user_id,count(user_id) as followers_count
from Followers
group by user_id
order by user_id
#-----------------优化--------------------
SELECT
user_id, COUNT(DISTINCT follower_id) followers_count
FROM
Followers
GROUP BY
user_id;