mysql - 尝试使用 MySQL 获取唯一记录

Question

我有一个正在查看“page_views”表的查询。到目前为止，我有能力获得每天的总浏览量。但是，我在白天获取独特的 View 时遇到了问题。

我正在测试的列是 user_id...这是我对总观看次数的查询。

SELECT 
  site_id, 
  CONCAT(  month(created_at) 
         , '-'
         ,   day(created_at)
         , '-' 
         , year(created_at)
        ) as created_at_date, 
  COUNT(*) as total_results 
FROM 
  page_views 
GROUP BY day(created_at)

我无法弄清楚仅根据 user_id 列获取每日唯一的总唯一观看次数的逻辑。

Schema:

id:int
user_id:int
site_id:int
request_uri:varchar
referer:varchar
user_agent:varchar
created_at:datetime

Answer 1

首先，您原来的查询不正确。 (不同月份的同一天或者不同的网站会返回无效的结果)应该如下

SELECT 
  site_id, 
  CONCAT(month(created_at) , '-',   day(created_at), '-' , year(created_at)) 
                                                         as created_at_date, 
  COUNT(*) as total_results 
FROM 
  page_views 
GROUP BY 
  site_id, 
  CONCAT(month(created_at) , '-',   day(created_at), '-' , year(created_at))

对于最初的问题，以下查询应该有效:

SELECT 
  site_id, 
  CONCAT(month(created_at) , '-',   day(created_at), '-' , year(created_at))
                                                           as created_at_date, 
  COUNT(distinct user_id) as unique_users
FROM 
  page_views 
GROUP BY 
  site_id, 
  CONCAT(month(created_at) , '-',   day(created_at), '-' , year(created_at))