BigQuery:使用时间戳从行创建每月存储桶
BigQuery:使用时间戳从行创建每月存储桶
提问于 2020-08-19 11:07:51
我想从订单列表中分析滚动用户留存率。我拥有的数据如下所示：
| order_id | user_id | price | date |
-------------------------------------------
| 222 | abc | 100 | 2020-06-02 |
| 333 | abc | 140 | 2020-06-12 |
| 444 | abc | 100 | 2020-07-05 |
| 444 | abc | 100 | 2020-09-06 |我想为价格(p)和预订计数(c)创建从第一个用户订单的日期起的每个30天期间的累计总数。我希望数据看起来像这样：
| first_order_date | user_id | 30d_p | 30d_c | 60d_p | 60d_c | 90d_p | 90d_c | 120_p | 120_c
---------------------------------------------------------------------------------------------
| 2020-06-02 | 222 | 240 | 2 | 340 | 3 | 340 | 3 | 440 | 4这可以使用BigQuery实现吗？
谢谢，亚历克斯
回答 1
Stack Overflow用户
发布于 2020-08-19 14:27:11
编辑(不同的ids)：这应该做你正在寻找的事情：
WITH orders as (
SELECT * FROM UNNEST([STRUCT (222 as order_id, "abc" as user_id, 100 as price, DATE("2020-06-02") as date),
(333, "abc", 140, "2020-06-12"),
(444, "cde", 100, "2020-07-05"),
(555, "cde", 100, "2020-09-06")]))
,t2 as (SELECT t1.date, user_id, (SELECT SUM(t2.price)
FROM orders AS t2
WHERE t2.user_id = t1.user_id AND t2.date < DATE_ADD(t1.date, INTERVAL 30 DAY)) AS _30d_p,
(SELECT COUNT(t2.order_id)
FROM orders AS t2
WHERE t2.user_id = t1.user_id AND t2.date < DATE_ADD(t1.date, INTERVAL 30 DAY)) AS _30d_c,
(SELECT SUM(t2.price)
FROM orders AS t2
WHERE t2.user_id = t1.user_id AND t2.date < DATE_ADD(t1.date, INTERVAL 60 DAY)) AS _60d_p,
(SELECT COUNT(t2.order_id)
FROM orders AS t2
WHERE t2.user_id = t1.user_id AND t2.date < DATE_ADD(t1.date, INTERVAL 60 DAY)) AS _60d_c,
(SELECT SUM(t2.price)
FROM orders AS t2
WHERE t2.user_id = t1.user_id AND t2.date < DATE_ADD(t1.date, INTERVAL 90 DAY)) AS _90d_p,
(SELECT COUNT(t2.order_id)
FROM orders AS t2
WHERE t2.user_id = t1.user_id AND t2.date < DATE_ADD(t1.date, INTERVAL 90 DAY)) AS _90d_c,
(SELECT SUM(t2.price)
FROM orders AS t2
WHERE t2.user_id = t1.user_id AND t2.date < DATE_ADD(t1.date, INTERVAL 120 DAY)) AS _120d_p,
(SELECT COUNT(t2.order_id)
FROM orders AS t2
WHERE t2.user_id = t1.user_id AND t2.date < DATE_ADD(t1.date, INTERVAL 120 DAY)) AS _120d_c
FROM orders t1
ORDER by t1.order_id)
SELECT row.*
FROM (
SELECT ARRAY_AGG(t2 LIMIT 1)[OFFSET(0)] row
FROM t2
GROUP BY user_id
) 请记住，BQ列名不能以数字开头。
这将返回每个user_id中的第一个。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63485661
复制相关文章
相似问题
创建自定义时间戳存储桶bigquery
如何从云存储桶自动创建BigQuery表？
bigquery创建间隔15分钟的时间戳桶
使用时间戳主题模式的PubSub & BigQuery订阅
Laravel使用时间戳列从表中获取行
添加站长 进交流群
领取专属 10元无门槛券
AI混元助手 在线答疑
关注 腾讯云开发者公众号
洞察 腾讯核心技术
剖析业界实践案例
腾讯云开发者
