Aggregating time series data

Time Series Analysis in PostgreSQL

Jasmin Ludolf

Content Developer, DataCamp

Measuring the length of time series

dc_news_fact : table with time series data for news articles
dc_news_dim : table with the title of the articles

SELECT
    COUNT(*) AS length,
    title
FROM dc_news_fact
JOIN dc_news_dim USING(id)
GROUP BY title
ORDER BY length DESC;

Measuring the length of time series

|length|title                                                            |
|------|-----------------------------------------------------------------|
|   144|For the Wealthiest, a Private Tax System That Saves Them Billions|
|   144|These 5 charts prove that the economy does better under ...      |
|   144|Pet surrenders on rise as Fort McMurray's economy falls          |
|   144|How Is the Economy Doing? Politics May Decide Your Answer        |
|   144|Argentina's New President Moves Swiftly to Shake Up the Economy  |

Counting number of non-null entries in a time series

SELECT COUNT(views) AS nonnull, title
FROM dc_news_fact
JOIN dc_news_dim USING(id)
GROUP BY title
ORDER BY nonnull DESC;

|nonnull |title                       |
|--------|----------------------------|
|     143|For the Wealthiest, a Pri...|
|     143|These 5 charts prove that...|
|     143|Pet surrenders on rise as...|
|     143|How Is the Economy Doing?...|
|     143|Argentina's New President...|

Counting number of non-zero entries in a time series

SELECT COUNT(views) AS nonzeros, title
FROM dc_news_fact
JOIN dc_news_dim USING(id)
WHERE views > 0
GROUP BY title
ORDER BY nonzeros DESC;

|nonzeros|title                       |
|--------|----------------------------|
|      84|Pet surrenders on rise as...|
|      82|These 5 charts prove that...|
|      79|How Is the Economy Doing?...|
|      78|Argentina's New President...|
|      64|For the Wealthiest, a Pri...|

Calculating min and max over time series data

SELECT 
    MIN(views) as min,
    MAX(views) as max,
    title
FROM dc_news_fact
JOIN dc_news_dim USING(id)
GROUP BY title
ORDER BY max DESC;

|min|max |title                       |
|---|----|----------------------------|
|  0|4161|For the Wealthiest, a Pri...|
|  0| 289|Argentina's New President...|
|  0| 141|Pet surrenders on rise as...|
|  0|  73|How Is the Economy Doing?...|
|  0|  53|These 5 charts prove that...|

Summing time series data

SELECT SUM(views) as views, title
FROM dc_news_fact
JOIN dc_news_dim USING(id)
GROUP BY title
ORDER BY views DESC;

|views|title                          |
|-----|-------------------------------|
|29565|For the Wealthiest, a Privat...|
| 1737|Pet surrenders on rise as Fo...|
| 1722|Argentina's New President Mo...|
| 1055|How Is the Economy Doing? Po...|
| 1043|These 5 charts prove that th...|

Adjusting time granularity

SELECT SUM(views) as views, DATE_TRUNC('day', ts) as date, title
FROM dc_news_fact
JOIN dc_news_dim USING(id)
GROUP BY title, date
ORDER BY title, date;

|views|date               |title                                                      |
|-----|-------------------|-----------------------------------------------------------|
|   62|2015-12-27 00:00:00|Argentina's New President Moves Swiftly to Shake Up the ...|
|  414|2015-12-28 00:00:00|Argentina's New President Moves Swiftly to Shake Up the ...|
| 1246|2015-12-29 00:00:00|Argentina's New President Moves Swiftly to Shake Up the ...|
|15265|2015-12-29 00:00:00|For the Wealthiest, a Private Tax System That Saves Them...|
...

Adjusting time granularity

SELECT SUM(views) as views, ts::date as date, title
FROM dc_news_fact
JOIN dc_news_dim USING(id)
GROUP BY title, date
ORDER BY title, date;

|views|date      |title                                                           |
|-----|----------|----------------------------------------------------------------|
|   62|2015-12-27|Argentina's New President Moves Swiftly to Shake Up the Economy |
|  414|2015-12-28|Argentina's New President Moves Swiftly to Shake Up the Economy |
| 1246|2015-12-29|Argentina's New President Moves Swiftly to Shake Up the Economy |
|15265|2015-12-29|For the Wealthiest, a Private Tax System That Saves Them Bill...|
...

Measuring the days

SELECT COUNT(DISTINCT ts::DATE) AS days, title
FROM dc_news_fact
JOIN dc_news_dim USING(id)
WHERE views > 0
GROUP BY title
ORDER BY days DESC, title;

|days|title                                                            |
|----|-----------------------------------------------------------------|
|   3|Argentina's New President Moves Swiftly to Shake Up the Economy  |
|   3|For the Wealthiest, a Private Tax System That Saves Them Billions|
|   3|How Is the Economy Doing? Politics May Decide Your Answer        |
...

Let's practice!

Time Series Analysis in PostgreSQL