条件运行和

cx6n0qe3 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(325)

我试图返回随时间转换的唯一用户数。
所以我有以下问题：

WITH CTE
As
(
SELECT '2020-04-01' as date,'userA' as user,1 as goals  Union all
SELECT '2020-04-01','userB',0   Union all
SELECT '2020-04-01','userC',0   Union all
SELECT '2020-04-03','userA',1   Union all
SELECT '2020-04-05','userC',1  Union all
SELECT '2020-04-06','userC',0  Union all
SELECT '2020-04-06','userB',0
)
select
  date,
  COUNT(DISTINCT
  IF
    (goals >= 1,
      user,
      NULL)) AS cad_converters
from CTE
group by date

我正在尝试计算不同的用户数，但我需要找到一种方法将不同的计数应用于整个日期。我可能需要做一些像是累积的一些。。。
预期结果是这样的

date, goals, total_unique_converted_users
'2020-04-01',1,1
'2020-04-01',0,1
'2020-04-01',0,1
'2020-04-03',1,2
'2020-04-05',1,2
'2020-04-06',0,2
'2020-04-06',0,2

sql google-bigquery

来源：https://stackoverflow.com/questions/62602096/conditional-running-sum

2条答案

按热度按时间

zbdgwd5y1#

下面是bigquery标准sql


# standardSQL

SELECT t.date, t.goals, total_unique_converted_users
FROM `project.dataset.table` t
LEFT JOIN (
  SELECT a.date, 
    COUNT(DISTINCT IF(b.goals >= 1, b.user, NULL)) AS total_unique_converted_users
  FROM `project.dataset.table` a
  CROSS JOIN `project.dataset.table` b
  WHERE a.date >= b.date
  GROUP BY a.date
)
USING(date)

赞(0）回复(0）举报 2021-07-24

23c0lvtd2#

我会通过标记每个名字的第一个进球来实现这一点。然后简单地做一个累加：

select cte.* except (seqnum), countif(seqnum = 1) over (order by date)
from (select cte.*,
             (case when goals = 1 then row_number() over (partition by user, goals order by date) end) as seqnum
      from cte
     ) cte;

我意识到这可以不用 case 在子查询中：

select cte.* except (seqnum), countif(seqnum = 1 and goals = 1) over (order by date)
from (select cte.*,
             row_number() over (partition by user, goals order by date) as seqnum
      from cte
     ) cte;

赞(0）回复(0）举报 2021-07-24

我来回答

条件运行和

2条答案

相关问题

热门标签

最新问答