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屋！