CODEWITHSUNDEEP
mysqlsorting
Kristian Rafteseth
Kristian Rafteseth

Reputation: 2032

Finding popular searches in database

I'm trying to find a good way of displaying popular searches in my webshop.

My table of usersearches has two columns, searchword and timestamp (time searched), like this:

searchword    timestamp
---------------------------------
bin laden     2011-12-01 11:00:00
pokemon       2013-08-01 12:00:00
doge          2014-08-01 10:00:00

A bad way of doing it is to just sort by count(searchword). If a searchword was very popular in 2011 and searched for a million times in 2011, and none in 2014, it would still come up as popular now if using that approach. So thats a bad way.

It would need to take into account timestamps somehow, maybe so that searches in the last few months is given more weight than older searches.

Anyone have a suggestion how to solve this?

I know there is no definite answer, im just looking for good ways to solve it.

Upvotes: 1

Views: 74

Answers (2)

MrSimpleMind
MrSimpleMind

Reputation: 8587

I would create my own popularity index for the different date intervals...

E.g.

select
  searchword,
  sum(
   case 
    when searchdate < date_sub(now(), interval 3 year) then 1
    when searchdate < date_sub(now(), interval 2 year) then 5
    when searchdate < date_sub(now(), interval 1 year) then 10
    when searchdate < date_sub(now(), interval 6 month) then 20
    when searchdate < date_sub(now(), interval 3 month) then 100
    when searchdate < date_sub(now(), interval 1 month) then 500
    else 1000
   end) as points
from searches
group by searchword
order by points desc
;

more details http://sqlfiddle.com/#!2/70d6f/2

Upvotes: 1

Perry
Perry

Reputation: 11700

You should count how many times a search word is used in a time.

That would make your query something like this:

SELECT searchword, count(*) AS totalsearch FROM table WHERE timestamp > '2013-01-01 00:00:00' AND timestamp < '2013-12-31 00:00:00' GROUP BY searchword ORDER BY totalsearch DES;

or make use of between:

SELECT searchword, count(*) AS totalsearch FROM table WHERE timestamp BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 00:00:00' GROUP BY searchword ORDER BY totalsearch DES;

Upvotes: 1

Related Questions