试试这个

select t2.custid from
(
-- select the most recent entry for each customer
select custid, date, action 
from cust_table t1 
where date = (select max(date) 
    from cust_table where custid = t1.custid)
) as t2
where t2.date <  2011-06-01 
-- where the most recent entry is in May or is an activate entry
-- assumes they have to have an activate entry before they get a deactivate entry 
and (date >  2011-05-01  or [action] =  activate )

注：这将是一个起点，仅适用于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子句可能会处理这个问题，但我想我会提到这个变通方法。

最后，我将让你在不同的年份完成这项工作。

在PostgreSQL 8.4+中：

WITH ActivateDates AS (
  SELECT
    custid,
    date,
    ROW_NUMBER() OVER (PARTITION BY custid ORDER BY date) AS rownum
  FROM atable
  WHERE action =  activate 
),
DeactivateDates AS (
  SELECT
    custid,
    date,
    ROW_NUMBER() OVER (PARTITION BY custid ORDER BY date) AS rownum
  FROM atable
  WHERE action =  deactivate 
),
ActiveRanges AS (
  SELECT
    a.custid,
    a.date AS activated,
    COALESCE(b.date,  21000101 ::date) AS deactivated
  FROM ActivateDates a
    LEFT JOIN DeactivateDates d ON a.custid = d.custid AND a.rownum = d.rownum
)
SELECT DISTINCT custid
FROM ActiveRanges
WHERE deactivated >  20110501 
  AND activated   <  20110601