You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by GitBox <gi...@apache.org> on 2019/06/20 04:08:45 UTC
[GitHub] [incubator-druid] vogievetsky commented on issue #7928: how to
realized YOY/MOM on druid?
vogievetsky commented on issue #7928: how to realized YOY/MOM on druid?
URL: https://github.com/apache/incubator-druid/issues/7928#issuecomment-503833705
This is not Druid specific (more like general SQL) but you would do:
```sql
SELECT
COUNT(*) AS "BothMonths",
COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00') AS "MonthX",
COUNT(*) FILTER(WHERE TIMESTAMP '2019-05-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00') AS "MonthY",
(
COUNT(*) FILTER(WHERE TIMESTAMP '2019-05-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00') -
COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00')
) /
CAST(
COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00')
AS DOUBLE)
* 100 AS "MonthOverMonth"
FROM "wikiticker"
WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00'
```
Or (with a grouping):
```sql
SELECT
"channel",
COUNT(*) AS "BothMonths",
COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00') AS "MonthX",
COUNT(*) FILTER(WHERE TIMESTAMP '2019-05-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00') AS "MonthY",
(
COUNT(*) FILTER(WHERE TIMESTAMP '2019-05-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00') -
COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00')
) /
CAST(
COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00')
AS DOUBLE)
* 100 AS "MonthOverMonth"
FROM "wikiticker"
WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00'
GROUP BY "channel"
ORDER BY "MonthOverMonth" DESC
```
Obviously you do not need all those aggregates, I am just demonstrating.
The idea is that you filter the overall query to both time intervals and then filter each aggregate to a specific interval
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org