彙整時間序列資料

PostgreSQL 的時間序列分析

Jasmin Ludolf

Content Developer, DataCamp

量測時間序列長度

dc_news_fact：包含新聞文章時間序列資料的資料表
dc_news_dim：包含文章標題的資料表

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

量測時間序列長度

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

計算時間序列中的非空值數

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...|

計算時間序列中的非零值數

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...|

計算時間序列的最小值與最大值

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...|

加總時間序列資料

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...|

調整時間粒度

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...|
...

調整時間粒度

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...|
...

量測天數

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        |
...

一起來練習吧！

PostgreSQL 的時間序列分析