使用分区by查找期间开始和结束时的帐户状态

Question

图中显示了两个日期(1月31日和7月31日)的每个帐户及其相关帐户状态(即开放或关闭)。底部的代码生成所示的图像。如何编辑/添加代码以提取1月31日至7月31日期间有多少帐户从开放到关闭，以及有多少帐户向相反方向移动？我对窗口功能不太熟悉，所以我会感谢你的帮助。

​

​

SELECT 
date
,account_id
,account_status 
,row_number() over(partition by account_id,account_status order by date asc) as row_rank

from X
INNER JOIN Y on x.account_id=y.account_id
qualify row_rank in (1,7)

Answer 1

使用STRING_AGG聚合函数而不是使用窗口函数，

SELECT account_id, STRING_AGG(account_status, '->' ORDER BY date) AS status_change
  FROM sample GROUP BY 1;

您可以看到每个帐户的状态变化。

​

​

然后计数status_change，

SELECT status_change, COUNT(1) cnt FROM (
  SELECT account_id, STRING_AGG(account_status, '->' ORDER BY date) AS status_change
    FROM sample GROUP BY 1
) GROUP BY 1;

产出将是：

​

​

样本表：

CREATE TEMP TABLE sample AS
SELECT '2020-01-31' date, 1 account_id, 'OPEN' account_status, 1 row_rank UNION ALL
SELECT '2020-07-31', 1, 'CLOSED', 7 UNION ALL
SELECT '2020-01-31', 2, 'OPEN', 1 UNION ALL
SELECT '2020-07-31', 2, 'OPEN', 7 UNION ALL
SELECT '2020-01-31', 3, 'CLOSED', 1 UNION ALL
SELECT '2020-07-31', 3, 'OPEN', 7;

Answer 2

您可以尝试以下查询：

with sample_data as (
  select date('2020-01-31') as date, 111 as account_id, 'OPEN' as account_status,
  union all select date('2020-07-31') as date, 111 as account_id, 'OPEN' as account_status,
  union all select date('2020-01-31') as date, 222 as account_id, 'OPEN' as account_status,
  union all select date('2020-07-31') as date, 222 as account_id, 'CLOSED' as account_status,
  union all select date('2020-01-31') as date, 333 as account_id, 'OPEN' as account_status,
  union all select date('2020-07-31') as date, 333 as account_id, 'CLOSED' as account_status,
  union all select date('2020-01-31') as date, 444 as account_id, 'OPEN' as account_status,
  union all select date('2020-07-31') as date, 444 as account_id, 'OPEN' as account_status,
  union all select date('2020-01-31') as date, 555 as account_id, 'CLOSED' as account_status,
  union all select date('2020-07-31') as date, 555 as account_id, 'OPEN' as account_status,
  union all select date('2020-08-01') as date, 555 as account_id, 'CLOSED' as account_status,
),
sample_data_mod as (
  SELECT 
date
,account_id
,account_status 
,row_number() over(partition by account_id,account_status order by date asc) as row_rank,
lag(account_status) over (partition by account_id order by date asc) as prev_status
from sample_data
)


select 
date,
account_id,
account_status,
prev_status,
countif(prev_status = 'OPEN' and account_status = 'CLOSED') over (partition by account_id order by date asc) as open_to_close,
countif(prev_status = 'CLOSED' and account_status = 'OPEN') over (partition by account_id order by date asc) as close_to_open,
from sample_data_mod

输出：

​

这显示了每个account_status更改的运行更新。

Answer 3

考虑以下选项

select account_status, next_account_status, count(*) accounts_moved
from (
  select *, lead(account_status) over statuses next_account_status
  from your_table
  window statuses as (partition by account_id order by date)
)
where not next_account_status is null
group by account_status, next_account_status