MYSQL - 多个计数语句

发布于 2024-09-05 08:47:23 字数 655 浏览 4 评论 0原文

我正在尝试查找我们的人口统计表以显示一些统计数据。然而，由于人口统计表非常大，我想在一个查询中完成它。

有 2 个重要字段：性别、last_login

我希望能够获取不同日期范围（<1 天前、1-7 天前、7-30 天前等）的登录总数（按性别分组）

我现在知道如何在一个日期范围内执行此操作。例如，不到 1 天前：

SELECT sex, count(*) peeps 
  FROM player_account_demo 
 WHERE last_demo_update > 1275868800 
GROUP BY sex

sex       peeps
----------------
UNKNOWN   22
MALE      43
FEMALE    86

但是我必须为每个范围执行一次此操作。有没有办法将所有 3 个范围都包含在内？

我希望我的最终结果看起来像这样：

sex       peeps<1day      peeps1-7days       peeps7-30days

谢谢！

重要提示：最后一个 demo_update 是纪元时间（unix 时间戳）

原文

I'm trying to do a lookup on our demographioc table to display some stats. However, since out demographic table is quit big I want to do it in one query.

There are 2 fields that are important: sex, last_login

I want to be able to get the total number of logins for various date ranges (<1day ago, 1-7 days ago, 7-30 days ago, etc) GROUPED BY sex

I right now know how to do it for one date range. For example less than 1 day ago:

SELECT sex, count(*) peeps 
  FROM player_account_demo 
 WHERE last_demo_update > 1275868800 
GROUP BY sex

Which returns:

sex       peeps
----------------
UNKNOWN   22
MALE      43
FEMALE    86

However I'd have to do this once for each range. Is there a way to get all 3 ranges in there?

I'd want my end result to look something like this:

sex       peeps<1day      peeps1-7days       peeps7-30days

Thanks!

IMPORTANT NOTE: last demo_update is the epoch time (unix time stamp)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

￠好甜 2024-09-12 08:47:23

SELECT sex,
  SUM(IF(DATEDIFF(NOW(),last_login) < 1,1,0)),
  SUM(IF(DATEDIFF(NOW(),last_login) BETWEEN 1 AND 7,1,0)),
  SUM(IF(DATEDIFF(NOW(),last_login) BETWEEN 7 AND 30,1,0))
FROM player_account_demo 
GROUP BY sex

SELECT sex,
  SUM(IF(DATEDIFF(NOW(),last_login) < 1,1,0)),
  SUM(IF(DATEDIFF(NOW(),last_login) BETWEEN 1 AND 7,1,0)),
  SUM(IF(DATEDIFF(NOW(),last_login) BETWEEN 7 AND 30,1,0))
FROM player_account_demo 
GROUP BY sex

回复收藏 0 原文