SQL根据激活/停用记录回答: which customers were active in a given month,

Question

给定一个表格

custid | date       | action
1      | 2011-04-01 | activate
1      | 2011-04-10 | deactivate
1      | 2011-05-02 | activate
2      | 2011-04-01 | activate
3      | 2011-03-01 | activate
3      | 2011-04-01 | deactivate

数据库是PostgreSQL。

我想要一个 SQL 查询来显示在 5 月的任何阶段活跃的客户。

因此，在上面，这将是 1 和 2。

我只是无法理解如何处理这个问题。有什么指点吗？

更新

客户 2 在 5 月活跃，因为他在 5 月之前被激活，并且自激活后没有被停用。比如，我这个月还活着，但不是这个月出生的，也没有死。

select distinct custid
from MyTable
where action = 'active' and date >= '20110501' and date < '20110601'

这种方法行不通，因为它只显示五月期间的激活，而不是“事件”。

Answer 1

注意:这将是一个起点，仅适用于 2011 年。

忽略任何挥之不去的错误，此代码(针对每个客户)查看 1) 客户在 5 月之前的最新状态更新以及 2) 客户是否在 5 月期间变得活跃？

SELECT
  Distinct CustId

FROM
 MyTable -- Start with the Main table

 -- So, was this customer active at the start of may?
 LEFT JOIN  -- Find this customer's latest entry before May of This Year
   (select 
     max(Date) 
   from
     MyTable
   where
     Date < '2011-05-01') as CustMaxDate_PreMay on CustMaxDate_PreMay.CustID = MyTable.CustID

 -- Return a record "1" here if the Customer was Active on this Date
 LEFT JOIN 
   (select
      1 as Bool,
      date
    from
      MyTable
   ) as CustPreMay_Activated on CustPreMay_Activated.Date = CustMaxDate_PreMay.Date and CustPreMay_Activated.CustID = MyTable.CustID and CustPreMay_Activated = 'activated'

 -- Fallback plan: If the user wasn't already active at the start of may, did they turn active during may? If so, return a record here "1"
 LEFT JOIN  
   (select 
     1 as Bool 
   from
     MyTable
   where
     Date <= '2011-05-01' and Date < '2011-06-01' and action = 'activated') as TurnedActiveInMay on TurnedActiveInMay .CustID = MyTable.CustID

 -- The Magic: If CustPreMay_Activated is Null, then they were not active before May
 --            If TurnedActiveInMay is also Null, they did not turn active in May either
 WHERE
   ISNULL(CustPreMay_Activated.Bool, ISNULL(TurnedActiveInMay.Bool, 0)) = 1

注意:

您可能需要将“FROM MyTable”替换为

From (Select distinct CustID from MyTable) as Customers

仅查看这段代码，我不清楚它是否会 A) 太慢或 B) 由于启动 FROM 子句 @MYTable 而导致重复或问题，每个客户可能包含许多记录。 DISTINCT 子句可能会解决这个问题，但我想我会提到这个解决方法。

最后，我将留给您让这项工作跨越不同的年份。