sql - SQL 查询在同一个表中查找 id 但不同的时间戳事件（群组）

Question

我需要编写一个查询，用以下逻辑给我计数。下面的示例显示ACCOUNT_ID123 注册2020-02-21所以M0是 1，然后ACCOUNT_ID在连续一个月中同样有一个事件所以M1是 1。

M0 is a the signup date
M1 is signup date + 1 month
M2 is signup date + 2 consecutive months
M3 is signup date + 3 consecutive months

    WITH M_O AS (
SELECT
    parsed_data."ACCOUNT_ID" AS "parsed_data.account_id",
    MIN(TO_CHAR(TO_DATE(parsed_data."TIMESTAMP"::timestamp_ntz ), 'YYYY-MM-DD')) AS "SIGNUP",
    COUNT(DISTINCT (parsed_data."ACCOUNT_ID") ) AS "COUNT_USERS_O"
FROM "PUBLIC"."PARSED_DATA"
     AS parsed_data

WHERE (parsed_data."ACCOUNT_ID") IS NOT NULL 
    AND (((parsed_data."EVENT") = 'Started')) 
    AND (
      ((TO_CHAR(TO_DATE(parsed_data."TIMESTAMP"::timestamp_ntz ), 'YYYY-MM-DD')) >= '2020-02-21')
      AND ((parsed_data."TIMESTAMP"::timestamp_ntz ) < CURRENT_DATE())
    )
GROUP BY 1),
M_1 AS (
  SELECT
    parsed_data."ACCOUNT_ID" AS "parsed_data.account_id",
    TO_CHAR(TO_DATE(parsed_data."TIMESTAMP"::timestamp_ntz ), 'YYYY-MM-DD') AS "parsed_data.timestamp_date",
    COUNT(DISTINCT (parsed_data."ACCOUNT_ID") ) AS "COUNT_USERS_1"
  FROM "PUBLIC"."PARSED_DATA" 
    AS parsed_data INNER JOIN M_O ON parsed_data.account_id = M_O."parsed_data.account_id"
  WHERE
    (parsed_data."ACCOUNT_ID") IS NOT NULL 
    AND (((parsed_data."EVENT") = 'Started'))
    AND (
      (TO_CHAR(TO_DATE(parsed_data."TIMESTAMP"::timestamp_ntz ), 'YYYY-MM-DD')) >= DATEADD('MONTH', 1, SIGNUP)
      AND ((parsed_data."TIMESTAMP"::timestamp_ntz ) < CURRENT_DATE())
    )
GROUP BY 1,2
)

标签： sqlsnowflake-cloud-data-platform

Answer 1

看起来您想创建群组？如“为每个 id 建立创建日期，然后看看他们之后每个月如何改变他们的行为”。

此代码应该可以工作：

with events as (
    select 1 id, '2020-01-01'::date e_date
    union all select 1, '2020-02-03'
    union all select 2, '2020-03-01'
    union all select 2, '2020-05-08'
    union all select 3, '2020-08-01'
    union all select 3, '2020-09-02'
    union all select 3, '2020-09-22'
    union all select 3, '2020-09-30'
    union all select 3, '2020-10-10'
),
first_per_id as (
    select id, min(e_date) first_date
    from events
    group by id
)

select a.id
    , count_if(e_date>=dateadd(month, 0, first_date) and e_date<dateadd(month, 1, first_date)) m0
    , count_if(e_date>=dateadd(month, 1, first_date) and e_date<dateadd(month, 2, first_date)) m1
    , count_if(e_date>=dateadd(month, 2, first_date) and e_date<dateadd(month, 3, first_date)) m2
from events a
join first_per_id b
where a.id=b.id
group by 1