टाइम सीरीज़ डेटा का एग्रीगेशन

PostgreSQL में Time Series Analysis

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  |

टाइम सीरीज़ में non-null प्रविष्टियाँ गिनना

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

टाइम सीरीज़ में non-zero प्रविष्टियाँ गिनना

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

टाइम सीरीज़ पर min और max निकालना

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 में Time Series Analysis