如何从第一个订单的月份创建用户队列，然后在sql中统计关于这些订单的信息？

q5iwbnjs 于 2021-07-24 发布在 Java

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

我正在尝试使用sql来：
按第一个订单的月份创建用户队列
合计该队列一直购买的所有订单金额的总和
输出群组名称（其月份）、群组大小（当月首次购买的用户总数）， total_revenue （来自该群组用户的所有订单收入），以及 avg_revenue （the） total_revenue 除以 cohort 尺寸）
请参见下面的sql fiddle，以及示例表和预期输出：
http://www.sqlfiddle.com/#!15/b5937号
谢谢！！
用户表

+-----+---------+
| id  |  name   |
+-----+---------+
|   1 | Adam    |
|   2 | Bob     |
|   3 | Charles |
|   4 | David   |
+-----+---------+

订单表

+----+--------------+-------+---------+
| id |     date     | total | user_id |
+----+--------------+-------+---------+
|  1 | '2020-01-01' |   100 |       1 |
|  2 | '2020-01-02' |   200 |       2 |
|  3 | '2020-03-01' |   300 |       3 |
|  4 | '2020-04-01' |   400 |       1 |
+----+--------------+-------+---------+

期望输出

+--------------+--------------+----------------+-------------+
|   cohort     | cohort_size  | total_revenue  | avg_revenue |
+--------------+--------------+----------------+-------------+
| '2020-01-01' |            2 |            700 |         350 |
| '2020-03-01' |            1 |            300 |         300 |
+--------------+--------------+----------------+-------------+

sql postgresql group-by aggregate-functions time-series

来源：https://stackoverflow.com/questions/62605012/how-do-i-create-cohorts-of-users-from-month-of-first-order-then-count-informati

2条答案

按热度按时间

ljo96ir51#

您可以找到每个用户的最短日期，并对其进行聚合。然后，您可以为每个此类日期汇总：

with first_orders(user_id, cohort, total) as (
  select user_id, min(ordered_at), sum(total)
  from orders
  group by user_id
)
select to_char(date_trunc('month', fo.cohort), 'YYYY-MM-DD'), count(fo.user_id), sum(fo.total), avg(fo.total)
from first_orders fo
group by date_trunc('month', fo.cohort)

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

xjreopfe2#

您可以使用窗口函数来获取第一个日期。剩下的是：

select date_trunc('month', first_date) as yyyymm,
       count(distinct user_id), sum(total), sum(total)/ count(distinct user_id)
from (select o.*, min(o.ordered_at) over (partition by o.user_id) as first_date
      from orders o
     ) o
group by date_trunc('month', first_date)
order by yyyymm;

这是sql小提琴。

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

我来回答

如何从第一个订单的月份创建用户队列，然后在sql中统计关于这些订单的信息？

2条答案

相关问题

热门标签

最新问答