MySQL:按日期分组RANGE? [英] MySQL: group by date RANGE?
问题描述
确定我有这个查询,它将2列组合在一起非常好:
SELECT search_query_keyword,search_query_date,COUNT(1)as count
from search_queries
WHERE search_query_date> ='。$ from。'AND search_query_date< ='。$ to。'
GROUP BY search_query_keyword,search_query_date
ORDER BY count DESC
LIMIT 10
但是如果我想按日期RANGE进行分组而不仅仅是一个日期?有没有办法做到这一点?
谢谢!
编辑:确定这些答案相当复杂,我认为我想要的东西可以变得更容易,所以让我重新解释一下。例如,我想在> = 20090601 AND <= 20090604时间段内选择关键字。但是,不要重复关键字,我宁愿只是获得关键字盎司和它发生的次数。例如,而不是这样:
关键字:foo
关键字:foo
关键字:foo
关键字:bar
关键字:bar
我会得到:
关键字:foo,count:3
关键字:bar,count:2
我不太确定日期范围分组 - 您必须定义日期范围想要,然后也许你可以联合这些查询:
pre $ SELECT
'范围1'AS'date_range',
search_query_keyword
FROM search_queries
WHERE search_query_date> ='。$ fromRange1。'AND search_query_date< ='。$ toRange1。'
UNION
SELECT
'Range 2'AS'date_range',
search_query_keyword
FROM search_queries
WHERE search_query_date> ='。$ fromRange2。'AND search_query_date< ='。$ toRange2。'
GROUP BY 1,2
或者,如果您想将它们放在30天,60天等多少天的分组中,您可以这样做:
SELECT
(DATEDIFF(search_query_date,NOW())/ 30)AS date_group,
search_query_keyword
FROM search_queries
GROUP BY date_group, search_query_keyword
编辑:根据您提供的更多信息,产生你想要的:
SELECT
search_query_keyword,
COUNT(search_query_keyword)AS keyword_count
FROM search_queries
WHERE search_query_date> ='。$ from。'AND search_query_date< ='。$ to。'
GROUP BY search_query_keyword
OK I have this query that groups 2 columns together quite nicely:
SELECT search_query_keyword, search_query_date, COUNT(1) as count
FROM search_queries
WHERE search_query_date >= '.$from.' AND search_query_date <= '.$to.'
GROUP BY search_query_keyword, search_query_date
ORDER BY count DESC
LIMIT 10
But what if I want to group by a date RANGE instead of just a date? Is there a way to do that?
Thanks!
EDIT: OK these answers are pretty complicated and I think what I want can be acheived a lot easier so let me re-explain. I want to select keywords over a time period ">= 20090601 AND <= 20090604" for example. But instead of getting repeated keywords I would rather just get the keyword ounce and how many times it occured. So for example instead of this:
keyword: foo
keyword: foo
keyword: foo
keyword: bar
keyword: bar
I would get:
keyword: foo, count: 3
keyword: bar, count: 2
I'm not exactly sure about the date range grouping -- you'd have to define the date ranging that you would want and then maybe you could UNION those queries:
SELECT
'Range 1' AS 'date_range',
search_query_keyword
FROM search_queries
WHERE search_query_date >= '.$fromRange1.' AND search_query_date <= '.$toRange1.'
UNION
SELECT
'Range 2' AS 'date_range',
search_query_keyword
FROM search_queries
WHERE search_query_date >= '.$fromRange2.' AND search_query_date <= '.$toRange2.'
GROUP BY 1,2
Or if you wanted to put them within a grouping of how many days old like "30 days, 60 days, etc" you could do this:
SELECT
(DATEDIFF(search_query_date, NOW()) / 30) AS date_group,
search_query_keyword
FROM search_queries
GROUP BY date_group, search_query_keyword
EDIT: Based on the further information you provided, this query should produce what you want:
SELECT
search_query_keyword,
COUNT(search_query_keyword) AS keyword_count
FROM search_queries
WHERE search_query_date >= '.$from.' AND search_query_date <= '.$to.'
GROUP BY search_query_keyword
这篇关于MySQL:按日期分组RANGE?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!