Mysql：仅在日期不同时计算每月不同的user_ids

5 回答

• 1

  获取每个user_id，日期组合的不同行并执行每月计数 .

  SELECT DATE_FORMAT(date,'%Y-%m') AS date_period, COUNT(*) AS c 
  FROM (SELECT DISTINCT date,user_id FROM stats) t
  GROUP BY DATE_FORMAT(date,'%Y-%m')
  

  回复于 2024-04-26T08:47:27+08:00
• 1

  其他答案的复杂性让我想知道我是否遗漏了一些东西，但这不应该只是你需要的吗？

  SELECT DATE_FORMAT(date, '%Y-%m') AS date_period, COUNT(DISTINCT user_id, date) AS c 
  FROM stats 
  GROUP by date_period
  

  回复于 2024-04-26T08:47:27+08:00
• 0

  这对我有用：

  SELECT 
  DATE_FORMAT(date, '%Y-%m') AS 'sum_month',
  user_id,
  COUNT(DISTINCT user_id,date) AS 'Count'
  FROM test
  GROUP BY sum_month,user_id;
  

  希望它能帮到你 .

  回复于 2024-04-26T08:47:27+08:00
• 2

  标准的SQL方法将此作为两个计数级别：

  select sum(dt_cnt) from
      (select user_id, count(distinct date_period) as dt_cnt from stats group by user_id) agg_dts
  --group by month ??
  

  我想查询性能可能会从汇总的总结中受益 . 你必须亲自尝试一下 .

  回复于 2024-04-26T08:47:27+08:00
• 0

  在SQL Server 2012上，所有这三个答案对我都没有用 . 所以我得到了以下解决方案

  select id,userid,date1 from
    (select distinct rankk,max(id) as id,max(userid) as userid,max(date1) as date1 
     from 
       (select rank() over (order by userid,date1) as rankk,* from stakflow) 
     as alias1
     group by rankk) 
  as alias2
  

  将rank函数与子查询结合使用将允许根据给定列中的repitition过滤数据

  回复于 2024-04-26T08:47:27+08:00