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 ``` ![image](https://user-images.githubusercontent.com/177816/59817813-387ec700-92d6-11e9-852e-63e11fee6b31.png) 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: us...@infra.apache.org With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@druid.apache.org For additional commands, e-mail: commits-h...@druid.apache.org