mysql查询抓取多个日期范围 [英] mysql query for grabbing multiple date ranges

问题描述

我似乎遇到了一些麻烦，想要实现我想要的功能。我有一张表，如下所示。

  |日期（TIMESTAMP）| Count | 
 | --------------------- | ------- | 
 | 2016-02-01 01:00:00 | 52 | 
 | 2016-01-05 11:30:00 | 14 | 
 | 2016-02-01 04:20:00 | 36 | 
 | ... | ... | 
  

表格大约有40,000行。我想要做的是抓住多个日期范围的总计，所以我最终得到以下结果：...

  |期间|总计| 
 | ------------ | ------- | 
 |全部| 10245 | 
 |过去一年| 1401 | 
 |过去一个月| 104 | 
 |过去一周| 26 | 
  

目前我正在运行PHP脚本中的一个循环，并为每个日期范围I正在寻找。实际上，每个循环中大约有10次查询来获取不同的统计数据，但是我正在简化它。这需要永远，我希望有一个更优雅的方式来做到这一点，但是我花了相当多的时间尝试不同的事情和研究，并没有得到任何好处。我知道如何使用CASE进行分组，但是当记录可能需要在多个分箱中时才能使用。任何帮助？

解决方案

1st。知道功能得到第一个日期，第一个日期和第一个日期。
然后使用count和filter来构造你的sql，并且使用不同时期的第一个和最后一个日期进行过滤。

ref：

MySQL选择年份和月份的第一天

p>

月份
https://stackoverflow.com/a/19259159/ 1258492

星期 https://stackoverflow.com/a / 11831133/1258492

select'All'as period， count（1）from tblunionselect'Past Year'as period，count（1）from tbl where timestamp between MAKEDATE（year（now（）） - 1,1）andlast_day（MAKEDATE（year（now（）） - 1,1）+间隔11个月）工会选择上个月作为时期，计数（1）fr om tbl where LAST_DAY（NOW（） - INTERVAL 2 MONTH）之间的时间戳+ INTERVAL 1 DAY和LAST_DAY（NOW（） - INTERVAL 1 MONTH）union select'Past Week'as period，count（1）from tbl where addstamp between adddate（curdate ），INTERVAL 1-DAYOFWEEK（curdate（）） - 7 DAY）andadddate（curdate（），INTERVAL 7-DAYOFWEEK（curdate（）） - 7 DAY）;

I seem to be having a bit of trouble coming up a query to achieve what I want. I have a table like the following..

| Date(TIMESTAMP)     | Count |
|---------------------|-------|
| 2016-02-01 01:00:00 | 52    |
| 2016-01-05 11:30:00 | 14    |
| 2016-02-01 04:20:00 | 36    |
| ...                 | ...   |

The table has about 40,000 rows. What I would like to do is grab the totals for multiple date ranges so I end up with the following...

| Period     | Total |
|------------|-------|
| All        | 10245 |
| Past year  | 1401  |
| Past month | 104   |
| Past week  | 26    |

Currently I am running through a loop in my PHP script and doing an individual query for each date range I'm looking for. Actually there are about 10 queries I'm doing per loop to grab different stats but for the example I'm simplifying it. This takes forever and I am hoping there is a more elegant way to do this, however I've spent quite a bit of time now trying different things and researching and have gotten nowhere. I understand how to use CASE to group but not when a record may need to be in multiple bins. Any help?

解决方案

1st. get known to function getting first date of year, first date of month and first date of week. Then compose your sql using count and filter with first and last date of different period.

ref:

MySQL Select First Day of Year and Month

month https://stackoverflow.com/a/19259159/1258492

week https://stackoverflow.com/a/11831133/1258492

select 'All' as period, count(1) from 
tbl
union

select 'Past Year' as period, count(1) from 
tbl
where timestamp between 
MAKEDATE(year(now())-1,1) and
last_day(MAKEDATE(year(now())-1,1) + interval 11 month)

union 

select 'Past Month' as period, count(1) from 
tbl
where timestamp between 
LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY and
LAST_DAY(NOW() - INTERVAL 1 MONTH)

union 
select 'Past Week' as period, count(1) from 
tbl 
where timestamp between 
adddate(curdate(), INTERVAL 1-DAYOFWEEK(curdate())-7 DAY)  and
adddate(curdate(), INTERVAL 7-DAYOFWEEK(curdate())-7 DAY) ;

这篇关于mysql查询抓取多个日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！