Iterating over arbitrary values in Mysql-CodePudding

Based on this answer, I have a query that generates a list of dates in Mysql, for example:

 ----------------------------------------------- 
| Month beginning                               |
 ----------------------------------------------- 
| 2022-10-01                                    |
| 2022-09-01                                    |
| 2022-08-01                                    |
| 2022-07-01                                    |
| 2022-06-01                                    |
| 2022-05-01                                    |
| 2022-04-01                                    |
| 2022-03-01                                    |
| 2022-02-01                                    |
 -----------------------------------------------

I also have a query for calculating the number of users that have not logged in up to the end of September:

SET @range_end = "2022-10-01"; SELECT (SELECT COUNT(DISTINCT id) FROM users WHERE timestamp < @range_end) - (SELECT COUNT(DISTINCT user_id) FROM logins WHERE timestamp < @range_end) AS "Not logged in";

This is just an example of the problem I'm trying to solve - I appreciate there are simpler approaches to this calculation.

How can I combine these queries, so the dates generated by my Month Beginning query are used in my user count query? Example below:

 ----------------- --------------- 
| Month beginning | Not logged in |
 ----------------- --------------- 
| 2022-10-01      |           110 |
| 2022-09-01      |           120 |
| 2022-08-01      |           130 |
| 2022-07-01      |           140 |
| 2022-06-01      |           150 |
| 2022-05-01      |           160 |
| 2022-04-01      |           170 |
| 2022-03-01      |           180 |
| 2022-02-01      |           200 |
 ----------------- ---------------

I've tried all sorts of joins, unions, groupings and sub queries, but can't get it to stick.

Or does what I'm trying to do fall foul of Restrictions on Subqueries.

The month generation query is:

select DATE_FORMAT(date_range.timestamp, "%Y-%m-01") AS "Month beginning"
from (
    select curdate() - INTERVAL (a.a   (10 * b.a)   (100 * c.a)   (1000 * d.a) ) DAY as timestamp
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) date_range
where date_range.timestamp between '2022-02-28' and NOW() GROUP BY DATE_FORMAT(date_range.timestamp, "%Y-%m-01") ORDER BY timestamp DESC;

CodePudding user response：

Use your subquery as the table to select from

SELECT range_end AS "Month beginning", 
    (SELECT COUNT(DISTINCT id) FROM users WHERE timestamp < range_end) - 
    (SELECT COUNT(DISTINCT user_id) FROM logins WHERE timestamp < range_end)    
        AS "Not logged in"
FROM (
    select DATE_FORMAT(date_range.timestamp, "%Y-%m-01") AS range_end
    from (
        select curdate() - INTERVAL (a.a   (10 * b.a)   (100 * c.a)   (1000 * d.a) ) DAY as timestamp
        from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
    ) date_range
    where date_range.timestamp between '2022-02-28' and NOW() GROUP BY DATE_FORMAT(date_range.timestamp, "%Y-%m-01") ORDER BY timestamp DESC
) AS date_range