Sql 在当前行之后和之前创建附加日期并基于它创建新列

请try 以下操作，并在查询注释中提供解释:

/* use generate_series to generate a series of dates
   starting from the min date of datetime up to the 
   max datetime with one-month intervals, then do a 
   cross join with the distinct cust_id to map each cust_id 
   to each generated date.*/
WITH cust_dates AS
(
  SELECT EX.cust_id, to_char(dts, 'YYYY-mm') dts
  FROM generate_series 
        (
         (SELECT MIN(datetime)::timestamp FROM example), 
         (SELECT MAX(datetime)::timestamp + '2 month'::interval  FROM example),
         '1 month'::interval
        ) dts
  CROSS JOIN (SELECT DISTINCT cust_id FROM example) EX
),
/* do a left join with your table to find prices
   for each cust_id/ month, and aggregate for cust_id, month_date
   to find the sum of prices for each cust_id, month_date.
*/
monthly_price AS
(
  SELECT CD.cust_id,
       CD.dts AS month_date,
       COALESCE(SUM(price), 0) total_price
  FROM cust_dates CD LEFT JOIN example EX
  ON CD.cust_id = EX.cust_id AND 
     CD.dts = to_char(EX.datetime, 'YYYY-mm')
  GROUP BY CD.cust_id, CD.dts
)
/* Now, we have the sum of monthly prices for each cust_id,
   we can use the max window function with "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW"
   to check if one of the (current month or the previous two months) has a sum of prices > 0.
*/
SELECT cust_id, month_date, total_price,
  CASE MAX(total_price) OVER 
      (PARTITION BY cust_id ORDER BY month_date 
       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    WHEN 0 THEN 'inactive' 
    ELSE 'active'
  END AS is_active
FROM monthly_price
ORDER BY cust_id, month_date

See demo个