如何获取当前日期到MySQL发布日期之间的平均点击？ [英] How to get Average hits between current date to posted date in MySQL?

问题描述

您有一个表名称图像

图像

id        hits       created
--------------------------------------
1         10       2014-11-07 11:07:57
2         8        2014-11-10 05:10:20
3         70       2014-10-04 08:04:22

在上表中，我想得到平均值最高频率记录数。

In above table i want to get the average highest number of Frequency record.

Q. What is Frequency?
A. Frequency means number-of-hits/number-of-days(current date - created date)

NB-你将获得当前日期减去创建日期。

N.B- days you will get to current-date minus created-date.

示例： - 假设我的点击是 10 创建的日期是 2014-11-07 11:07:57 然后频率将为 10/4 = 2.5 与 8/1 = 8 和 70/37 = 1.89

Example:- Suppose my hits is 10 and created date is 2014-11-07 11:07:57 then the Frequency will be 10/4=2.5 Same as 8/1=8 and 70/37=1.89

我需要通过单个MySQL查询中的频率降序获取所有记录顺序。

I need to get all records order by Frequency descending in a single MySQL query.

感谢
chinu

Thanks chinu

推荐答案

使用 DATEDIFF 函数。

查询

Use DATEDIFF function.

SELECT id,hits,created,
hits/(DATEDIFF(NOW(),created)) AS Frequency
FROM images
ORDER BY Frequency DESC;

输出

+----+------+---------------------------------+-----------+
| ID | HITS |         CREATED                 | FREQUENCY |
|----+------+---------------------------------+-----------+
|  2 |    8 | November, 10 2014 05:10:20+0000 |   8       |
|  1 |   10 | November, 07 2014 11:07:57+0000 |   2.5     |
|  3 |   70 |  October, 04 2014 08:04:22+0000 |   1.8421  |
+----+------+---------------------------------+-----------+

这篇关于如何获取当前日期到MySQL发布日期之间的平均点击？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！