mysql - 计算另一个表中的时间戳小于/大于主表中的时间间隔的位置

Question

我正在尝试统计一个月/一年内未发布任何交易的用户

但是一个用户的交易有很多条目，这是一个粗略的例子:

+---------+
| user_id |
+---------+
|       1 |
|       2 |
|       3 |
+---------+
+----------------+---------+---------------------+
| transaction_id | user_id |      recorded       |
+----------------+---------+---------------------+
| t1             |       1 | 2019-01-01 hh:mm:yy |
| t2             |       1 | 2019-01-02 hh:mm:yy |
| t3             |       2 | 2018-01-01 hh:mm:yy |
| t4             |       2 | 2018-01-02 hh:mm:yy |
| t5             |       2 | 2018-01-02 hh:mm:yy |
| t6             |       3 | 2018-01-03 hh:mm:yy |
+----------------+---------+---------------------+

如何获取上述2018-01-03中未添加任何交易的用户数量？

Answer 1

-- Creating temp table that stores YYYYMM as int
declare @DateTo datetime =GetDate()
declare @DateFrom datetime= DATEADD(Year, -2, GetDate())
create Table #DateScope1(AllDates int )
-- loop to fill the temp table to fill it with date range of YYYYMM
while @DateFrom<@DateTo
begin
insert into #DateScope1(AllDates) 
select convert(nvarchar(4), DatePart(Year, @DateFrom))+right('00'+convert(nvarchar(2),DatePart(Month, @DateFrom)),2)
set @DateFrom=DATEADD(Month, 1, @DateFrom)
end


-- 1- Get All Distinct users
-- 2- cross join with the temp table to get all users with all months
-- 3- Get Distinct users again but now with their YYYYMM Months
-- 4- Now make left outer join with 2 and 3 then filter with 3 dates nulls
SELECT          AllXAll.user_id, AllDates
FROM            (SELECT        tUsers.user_id, [#DateScope1].AllDates
                          FROM            (SELECT DISTINCT user_id
                                                    FROM            yourtable) AS tUsers CROSS JOIN
                                                    [#DateScope1]) AS AllXAll LEFT OUTER JOIN
                             (SELECT        user_id, recorded
                               FROM            (SELECT DISTINCT user_id, CONVERT(nvarchar(4), DATEPART(Year, recorded)) + RIGHT('00' + CONVERT(nvarchar(2), DATEPART(Month, recorded)), 2) AS recorded
                                                         FROM            (SELECT        user_id, recorded
                                                                                   FROM            yourtable AS yourtable_1) AS t1_1) AS t1_2) AS t1 ON AllXAll.user_id = t1.user_id AND AllXAll.AllDates = t1.recorded 
where recorded is null

drop table #DateScope1