sql—如何对一列进行排序，并在第二列中显示事件的运行总数？

ykejflvf 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(287)

我尝试订购/计算用户打开移动应用程序的次数，以及他们在应用程序中购买的次数，作为用户的运行计数。
这是我正在使用的代码：

SELECT user, type,
CASE WHEN type='session_start' OR type='iap' THEN RANK() OVER (PARTITION BY user ORDER BY client_time) END AS rank,
CASE WHEN type='iap' THEN COUNT(product_id) OVER (PARTITION BY user ORDER BY client_time ROWS UNBOUNDED PRECEDING) END AS RunningIAP
FROM `dataset` 
WHERE type='session_start' OR type='iap' 
LIMIT 1000

输出为我提供了会话数的正确排名，但购买数的运行计数不起作用。
我怎样才能解决这个问题？

sql google-bigquery

来源：https://stackoverflow.com/questions/63036163/how-can-i-rank-one-column-and-have-a-running-total-of-the-event-in-a-second-colu

1条答案

按热度按时间

uqdfh47h1#

您需要条件累积计数。为此，你可以使用 COUNTIF() 作为窗口函数：

SELECT user, type,
       (CASE WHEN type IN ('session_start', 'iap')
             THEN RANK() OVER (PARTITION BY user ORDER BY client_time)
        END) AS rank,
       (CASE WHEN type IN ('session_start', 'iap')
             THEN COUNTIF(type = 'iap') OVER (PARTITION BY user ORDER BY client_time)
        END) AS RunningIAP
FROM `dataset` 
WHERE type IN ('session_start', 'iap')
LIMIT 1000;

请注意，基于外部 WHERE 子句中，您可以将其简化为：

SELECT user, type,
       RANK() OVER (PARTITION BY user ORDER BY client_time) AS rank,
       COUNTIF(type = 'iap') OVER (PARTITION BY user ORDER BY client_time) AS RunningIAP
FROM `dataset` 
WHERE type IN ('session_start', 'iap')
LIMIT 1000

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

我来回答

sql—如何对一列进行排序，并在第二列中显示事件的运行总数？

1条答案

相关问题

热门标签

最新问答