Cumulative row count over date ranges

I recently helped a coworker (Mr. M) with his SQL query that gets a cumulative count over a set of ranges of dates. He wrote a query that functionally works just fine, but he didn't like it because there was a lot of repetition happening. It looked something like:

SELECT [date1], COUNT(*) FROM MYTABLE WHERE CREATED_ON <= [date1] UNION ALL
SELECT [date2], COUNT(*) FROM MYTABLE WHERE CREATED_ON <= [date2] UNION ALL
SELECT [date3], COUNT(*) FROM MYTABLE WHERE CREATED_ON <= [date3] UNION ALL
SELECT [date4], COUNT(*) FROM MYTABLE WHERE CREATED_ON <= [date4] UNION ALL
SELECT [date5], COUNT(*) FROM MYTABLE WHERE CREATED_ON <= [date5]

Imagine in place of [datex] there is some mathy date expression -- I'm leaving it out for now (because I'm lazy) but the idea was subtract a week from today for date1, subtract 14 days from today for date2, etc.

So Mr. M did not like that his copy/pasted query parts violated DRY - Don't Repeat Yourself. I think that's less bad than all this UNIONing and repeated checking against CREATED_ON. It's running 5 different queries that are kind of redundant as they are looking up by the same column repeatedly. How can we improve this query? My first instinct was to use windowing functions, but alas, this is MySQL which lacks them (remember, MySQL is a hair band). I'm sure we can do better, but here's what I came up with as a first hack:

SELECT SUM(bucket1) AS week1,
 SUM(bucket2) AS week2,
 SUM(bucket3) AS week3,
 SUM(bucket4) AS week4,
 SUM(bucket5) as week5
FROM (
 SELECT CASE WHEN CREATED_ON <= ADDDATE(current_date(), INTERVAL -1-(0*7) DAY) THEN 1 ELSE 0 END AS bucket1,
 CASE WHEN CREATED_ON <= ADDDATE(current_date(), INTERVAL -1-(1*7) DAY) THEN 1 ELSE 0 END AS bucket2,
 CASE WHEN CREATED_ON <= ADDDATE(current_date(), INTERVAL -1-(2*7) DAY) THEN 1 ELSE 0 END AS bucket3,
 CASE WHEN CREATED_ON <= ADDDATE(current_date(), INTERVAL -1-(3*7) DAY) THEN 1 ELSE 0 END AS bucket4,
 CASE WHEN CREATED_ON <= ADDDATE(current_date(), INTERVAL -1-(4*7) DAY) THEN 1 ELSE 0 END AS bucket5
FROM 
MYTABLE) sub

Well, I wasn't able to remove copy/pasted repetition, but I do think removing the UNION ALLs is a positive. What does this query do?

Step 1) The subquery. This is the bulk of the query, which passes over the whole table just once, and puts each row in an bucket from 1 through 5. It will perform 1 scan per row and 5 comparisons per row, vs. scanning each row 5 scans per row and 5 comparison per row.

Step 2) Sum them up!

Now in this specific instance the performance gain was pretty negligible, and we didn't really neaten the query all that much. I wouldn't blame Mr. M if he decides to stick with old query. As far as performance goes, the issue is this table was very small with only about 50k rows, therefore the cost of repeating 5 queries was negligible compared to all the added logic of those CASE WHEN expressions that I added.

Is there a way to avoid the repetition? 
The answer is, for MySQL, I don't know! But I wonder if I can figure something out with Postgres...

So first I made a sample table MYTABLE with 100,001 rows of randomly generated data.

To write the query, I decided to define the 5 dates using a common table expression, this way we can reference it as an imaginary table, so to speak:

mwrynn=# WITH weeks AS (SELECT now() - i * interval '1 week' AS date_point from generate_series(0,4) i)
SELECT * FROM weeks;

          date_point           

-------------------------------

 2016-06-04 13:14:00.560272-04

 2016-05-28 13:14:00.560272-04

 2016-05-21 13:14:00.560272-04

 2016-05-14 13:14:00.560272-04

 2016-05-07 13:14:00.560272-04

(5 rows)

Next, let's join that to our table so that for each date_point, get the rows where created_on is less than or equal to the date_point...

mwrynn=# WITH weeks AS (SELECT now() - i * interval '1 week' AS date_point from generate_series(0,4) i)

SELECT * FROM weeks JOIN mytable ON mytable.created_on <= weeks.date_point;

          date_point           | created_on 

-------------------------------+------------

 2016-06-04 13:15:40.869402-04 | 2015-12-12

 2016-06-04 13:15:40.869402-04 | 2016-02-19

 2016-06-04 13:15:40.869402-04 | 2016-04-20

 2016-06-04 13:15:40.869402-04 | 2016-02-01

[snipping tons of rows]

Final step: get the counts per date_point:

mwrynn=# WITH weeks AS (SELECT now() - i * interval '1 week' AS date_point from generate_series(0,4) i)

SELECT COUNT(*), date_point

FROM weeks JOIN

     mytable ON mytable.created_on <= weeks.date_point

GROUP BY date_point

ORDER BY date_point DESC;

 count  |          date_point          

--------+------------------------------

 100001 | 2016-06-04 13:17:29.33812-04

  98232 | 2016-05-28 13:17:29.33812-04

  96290 | 2016-05-21 13:17:29.33812-04

  94313 | 2016-05-14 13:17:29.33812-04

  92350 | 2016-05-07 13:17:29.33812-04

(5 rows)

TA-DA!!! WE DID IT! We got the cumulative counts without copying/pasting several queries or several conditions - we just had to define the weeks we concerned about (weeks 0 through 4) in the call to generate_series(), put that in a CTE, and query that with a join... [Edit: Note, we should even be able to parameterize the number of weeks by doing something like generate_series(0,?) -- this way we could easily use any number of weeks.] My initial instinct of requiring window functions was wrong. But a big THANK YOU TO COMMON TABLE EXPRESSIONS. :) 

Question: Does anyone know how to do this in MySQL??

Day-After Edit: I guess a similar technique could be used in MySQL, only we must replace the CTE with a subquery, and replace generate_series with some way of selecting 0-4.... I guess this would work: (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) - but perhaps this would count as the type of repetition we want to avoid...Another edit: apparently there is a solution, albeit an ugly one: http://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql

Reddit post: Postgres Table inheritance and reporting queries

Hello! It's been a while. I just thought I'd share a post I wrote on reddit yesterday. A poster asked for help regarding a Postgres conundrum, and I thought that Postgres's table inheritance feature would fit the bill quite nicely. He was happy with the suggestion and ran with it. Woo!

Link to Reddit thread - my reply is below the original post. (My username is mwdb)

Postgres query using window functions, recursive query

As Tom Kyte says, "Analytics rock. Analytics roll." Among other things, Oracle's analytic queries allow a row in the resultset to reference other rows in the same resultset. This is tremendously powerful stuff. If you're not familiar with analytics, take a look at some of the magic Tom performs in that thread I just linked. Some simple use cases:
1) Get a cumulative sum - maybe you have a table of individual purchases and want to see how the sum of purchases progresses over time.
2) Perform your typical GROUP BY query using an aggregate function, BUT you don't want to roll up the data. You want to see every row in the table you're querying, with the AVG (or MAX, SUM, whatever) alongside it. See the first example in this Postgres doc.

But did you know Postgres now has Analytics too, as of 8.4? They are called Window Functions in the Postgres world. (I suppose you could call them "queries that use window functions", or even "analytic queries" as Oracle can't force you not to use the phrase.)

I had a requirement. Stripped down to the core, the requirement was to translate this:

ID | expire
---+---------
 7 |  8/20/2011
 6 | 
 5 |  8/15/2011
 4 |  8/15/2011
 3 |
 2 |
 1 |  8/10/2011

To this:

 ID | expire
---+---------
 7 |  8/20/2011
 6 |  8/15/2011
 5 |  8/15/2011
 4 |  8/15/2011
 3 |  8/10/2011
 2 |  8/10/2011
 1 |  8/10/2011

The translation is: If expire is null, look down to the next older expire. If THAT one is null too, look down again, and so on. The solution that worked very well for me was to use Window Functions, plus recursive ("WITH RECURSIVE") queries.

Edit: A helpful commenter provided this simpler solution: select
 id, max(expire) over (order by id rows between unbounded preceding and current row) as expire
 from t 
order by id desc

The windowing clause in this query says: order the rows by id ascending, and for each row, get the max value of expire looking backwards from this row to all previous rows. Pretty simple! The Oracle solution should be the same, btw.

I'll leave my older code and thought processes in this article for posterity.

Step 1 was to use Window Functions to structure a view of the table above, such that the previous and next IDs could be found in each row, almost like a linked list data structure:

ID   prev_ID  next_id  expire

---  -------  -------  --------

 7     null      6     8/20/2011

 6      7        5

 5      6        4     8/15/2011

 4      5        3     8/15/2011

 3      4        2

 2      3        1

 1      2       null   8/10/2011

(In the real world, my IDs aren't always such simple, gap-free numbers, so I cannot use any simple math tricks, such as finding prev_ID by subtracting 1 from ID.)

Step 2 was to use WITH RECURSIVE to traverse from row to row, via the pointers in our linked list, to form the end resultset we're looking for. This entailed another little trick. Coalesce two expires (the current and the parent) to make the non-null values bubble up.

I'll post the SQL when I get to it.
Creating the view, transaction, with window functions:

CREATE VIEW transaction AS
select
id,
lag(id) over (partition by account_id order by id asc) lag_id,
lead(id) over (partition by account_id order by id asc) lead_id,
expire
from trans;

Recursive query against transaction:

WITH RECURSIVE recur(
id,
expire
) AS (
select
id,
expire
from transaction where lag_id is null
union all
select
transaction.id,
coalesce(transaction.expire, recur.expire)
from transaction, recur
where transaction.lag_id=recur.id
)
select * from recur;

I'm really pleased with the advanced querying features Postgres has been putting out. Actually, WITH RECURSIVE is ANSI standard, as it turns out, and you can even find it in SQL Server, DB2 and Oracle 11g (though Oracle has had its own way of performing recursive queries using CONNECT BY, for ages).