sql - SQL如何获取购买间隔为3个月的帐户ID

Question

我有下表。我需要查询至少有 3 个月的差距ACCOUNT_ID。ACCOUNT_ID

例如，ACCOUNT_ID123 具有以下内容。他们有超过3个月的差距。我怎样才能得到这个ID？

老实说，我超级卡在这里，不知道该怎么做。

WITH LAST_PURCHASED AS (
SELECT
    lp."ACCOUNT_ID",
    MAX(lp."PO_DATE") AS "LAST_PUR_DATE",
    SUM(lp."QTY") AS "TTL_QTY"
FROM A
    AS lp
GROUP BY 1
)
SELECT 
    *
FROM A 
    INNER JOIN LAST_PURCHASED lp ON (A."ACCOUNT_ID" = lp."ACCOUNT_ID")
WHERE
    lp."LAST_PURCHASED" >= DATEADD(MONTH,-3,A."PO_DATE")
LIMIT 10;

标签： sqlsnowflake-cloud-data-platform

Answer 1

您可以按如下方式使用 windows 功能：

select t.* from
(select t.*, max(diff) over  (partition by t."ACCOUNT_ID") as max_diff from
(select t.*,
       datediff(t."PO_DATE", lead(t."PO_DATE") 
                               over (partition by t."ACCOUNT_ID" 
                                     order by t."PO_DATE") ) as diff
  from A t) t ) t
where max_diff >= 3