Which kind of DBs calculate rate per minute statistics?

Question

I have a use case requirement, where I want to design a hashtag ranking system. 10 most popular hashtag should be selected. My idea is something like this:

[hashtag, rateofhitsperminute, rateofhisper5minutes]

Then I will query, find out the 10 most popular #hashtags, whose rateofhits per minute are highest.

My question is what sort of databases, can I use, to provide me statistics like 'rateofhitsperminute' ?

What is a good way to calculate such a detail and store in it db ? Do some DBs offer these features?

Answer 1

First of all, "rate of hits per minute" is calculated:

[hits during period]/[length of period]

So the rate will vary depending on how long the period is. (The last minute? The last 10 minutes? Since the hits started being recorded? Since the hashtag was first used?)

So what you really want to store is the count of hits, not the rate. It is better to either:

• Store the hashtags and their hit counts during a certain period (less memory/cpu required but less flexible)
• OR the timestamp and hashtag of each hit (more memory/cpu required but more flexible)

Now it is a matter of selecting the time period of interest, and querying the database to find the top 10 hashtags with the most hits during that period.

If you need to display the rate, use the formula above, but notice it does not change the order of the top hashtags because the period is the same for every hashtag.

---

You can apply the algorithm above to almost any DB. You can even do it without using a database (just use a programming language's builtin hashmap).

If performance is a concern and there will be many different hashtags, I suggest using an OLAP database. OLAP databases are specially designed for top-k queries (over a certain time period) like this.

Having said that, here is an example of how to accomplish your use case in Solr: Solr as an Analytics Platform. Solr is not an OLAP database, but this example uses Solr like an OLAP DB and seems to be the easiest to implement and adapt to your use case:

Your Solr schema would look like:

<fields>
 <field name="hashtag"  type="string"/>
 <field name="hit_date" type="date"/>
</fields>

An example document would be:

{
 "hashtag": "java",
 "hit_date": '2012-12-04T10:30:45Z'
}

A query you could use would be:

http://localhost:8983/solr/select?q=*:*&facet=true&facet.field=hashtag&facet.mincount=1&facet.limit=10&facet.range=hit_date&facet.range.end=2013-01-01T00:00:00Z&facet.range.start=2012-01-01T00:00:00

---

Finally, here are some advanced resources related to this question:

• Similar question: Implementing twitter and facebook like hashtags
• What is the best way to compute trending topics or tags? An interesting idea I got from these answers is to use the derivative of the hit counts over time to calculate the "instantaneous" hit rate.
• HyperLogLog can be used to estimate the hit counts if an approximate calculation is acceptable.
• Look into Sliding-Window Top-K if you want to get really academic on this topic.

Answer 2

No database has rate per minute statistics just built in, but any modern database could be used to create a database in which you could quite easily calculate rate per minute or any other calculated values you need.

Your question is like asking which kind of car can drive from New York to LA - well no car can drive itself or refuel itself along the way (I should be careful with this analogy because I guess cars are almost doing this now!), but you could drive any car you like from New York to LA, some will be more comfortable, some more fuel efficient and some faster than others, but you're going to have to do the driving and refueling.

Answer 3

You can use InfluxDB. It's well suited for your use case, since it was created to handle time series data (for example "hits per minute").

In your case, every time there is a hit, you could send a record containing the name of the hashtag and a timestamp.

The data is queryable, and there are already tools that can help you process or visualize it (like Grafana).

Answer 4

If you are happy with a large data set you could store and calculate this information yourself.

I believe Mongo is fairly fast when it comes to index based queries so you could structure something like this.

Every time a tag is "hit" or accessed you could store this information as a row

[Tag][Timestamp]

Storing it in such a fashion allows you to first of all run simple Group, Count and Sort operations which will lead you to your first desired ability of calculating the 10 most popular tags.

With the information in this format you can then perform further queries based on tag and timestamp to Count the amount of hits for a specific tag between the times X and Y which would give you your hits Per period.

Benefits of doing it this way:

• High information granularity depending on time frames supplied via query
• These queries are rather fast in mongoDB or similar databases even on large data sets

Negatives of doing it this way:

• You have to store many rows of data
• You have to perform queries to retrieve the information you need rather than returning a single data row