CREATE TABLE corder (cid INT, orderdate TIMESTAMP);

INSERT INTO corder(cid, orderdate) VALUES
(1, '2023-06-01'),
(1, '2023-05-01'),
(2, '2023-04-01'),
(2, '2023-03-01'),
(3, '2023-05-15'),
(3, '2023-01-01'),
(4, '2023-03-01');

您需要首先运行一个groupby查询，以查找每个客户端的最近orderDate，并根据最近的订单下达时间为客户端分配一个类别。

SELECT MAX(cid) AS cid,
  MAX(orderdate) AS lastOrder,
  CASE WHEN NOW()::DATE - MAX(orderdate)::DATE <= 14 THEN 'CHURN-1-2'
       WHEN NOW()::DATE - MAX(orderdate)::DATE <= 28 THEN 'CHURN-3-4'
       ELSE 'CHURN-MAX'
  END AS Churn
  FROM 
      corder
  GROUP BY cid

然后，您可以在公共表表达式中使用此查询，以便可以查询这些结果，将它们分组到“流失”类别中，并为每个“流失”类别选择客户端计数。

WITH cte AS(
  SELECT MAX(cid) AS cid,
  MAX(orderdate) AS lastOrder,
  CASE WHEN NOW()::DATE - MAX(orderdate)::DATE <= 14 THEN 'CHURN-1-2'
       WHEN NOW()::DATE - MAX(orderdate)::DATE <= 28 THEN 'CHURN-3-4'
       ELSE 'CHURN-MAX'
  END AS Churn
  FROM 
      corder
  GROUP BY cid)
 SELECT
    MAX(Churn) As Churn,
    COUNT(*) AS Clients
 FROM 
    cte
 GROUP BY
    Churn
 ORDER By Churn;