Public Event Sentiment Analysis in PostgreSQL


Sometimes a business may want to know what will happen following a specific event. For example, will publishing house price regulations lead to large investment in the stock market?

Let’s take a look at how PostgreSQL databases can meet this type of requirement.

Simulate a Realistic Scenario

1. Construct the structure of a table holding events

2. Insert 10 million event records, which are of 5,000 different event types

3. Create indexes by event type


Query a specific number of events following a certain event

Simply use the following function to meet the preceding requirement

Query example

Query two events following an event of type 1 and generate analysis results ten times.

With the preceding results, we can know what events will happen after the occurrence of event 1.

To use the preceding results for further analysis, use hotspot analysis similar to keywords, use the MADlib machine learning database in PostgreSQL or use plR.

You always have a method to find the correlation between events.

Correlation Analysis of Non-Independent Events

Because the previous analysis targets independent events, which bear no correlation themselves, it is required to use functions to generate results.

Users’ mouse clicks on Taobao (for example, what other items a user clicks after clicking a specific item) is an example of non-independent events.

For non-independent events, the structure design includes associated fields like USERID, so that we can easily establish association.

Trajectory analysis can be performed on non-independent events by using recursive calls in PostgreSQL.


Recursive syntax in PostgreSQL and PL/pgSQL can perfectly meet the requirements of analyzing events before and after a specific event in public opinion analysis scenarios.

Then hotspot analysis, cluster analysis, MADlib in PostgreSQL or plR can be used to analyze events to find event correlation.

Original Source

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store