mysql只统计最近7天的数据,最近7天的MySQL计数数据

最新推荐文章于 2024-07-30 04:00:47 发布

weixin_39683025

最新推荐文章于 2024-07-30 04:00:47 发布

阅读量235

点赞数

文章标签： mysql只统计最近7天的数据

本文介绍了如何使用MySQL查询在不支持递归的情况下，通过创建临时表（numbers）获取过去7天内每个aid每天的投票次数，特别关注了包含零投票的情况。作者提到了利用日期差和INCREMENTING numbers技巧来实现日期范围内的计数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

I have the following schema.

Table votes

+------------------+--------------+------+-----+---------------------+----------------+

+------------------+--------------+------+-----+---------------------+----------------+

| id | int(10) | NO | PRI | NULL | auto_increment |

| aid | varchar(10) | NO | | | |

| ip | varchar(100) | NO | | | |

| timestamp | varchar(20) | NO | | 0000-00-00 00:00:00 | |

| userid | int(10) | NO | | 0 | |

+------------------+--------------+------+-----+---------------------+----------------+

Here I want to get the count of each aid on a day for the last 7 days with "0"s for the dates where there a no votes for aid. timestamp is unix timestamp here.

Any help is highly appreciated.

解决方案

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

Create a table that only holds incrementing numbers - easy to do using an auto_increment:

DROP TABLE IF EXISTS `example`.`numbers`;

CREATE TABLE `example`.`numbers` (

`id` int(10) unsigned NOT NULL auto_increment,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Populate the table using:

INSERT INTO NUMBERS

(id)

VALUES

(NULL)

...for as many values as you need.

Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-01-01" and "2010-01-02" with your respective start and end dates (but use the same format, YYYY-MM-DD HH:MM:SS). In this example, I subtracted the NUMBERS.id value from the CURRENT_DATE to get a list of sequential date values for the last week -

SELECT x.dt

FROM (SELECT DATE_SUB(CURRENT_DATE, INTERVAL (n.id - 1) DAY) AS dt

FROM numbers n

WHERE n.id <= 7 ) x

LEFT JOIN onto your table of data based on the datetime portion.

SELECT x.dt,

COUNT(v.aid) AS num

FROM (SELECT DATE_SUB(CURRENT_DATE, INTERVAL (n.id - 1) DAY) AS dt

FROM numbers n

WHERE n.id <= 7 ) x

LEFT JOIN VOTES v ON DATE(FROM_UNIXTIME(v.timestamp)) = DATE(x.dt)

GROUP BY x.dt