sql - 使用 postgres 和 chart.io 构建客户生命周期值(value)折线图

Question

我需要构建一个客户生命周期值(value)图表，但一直纠结于如何在普通 psql 中执行此操作。

因此，我们的想法是每天都有一条线，显示当天签约的客户在一段时间内花费的平均金额。

我的表格是这样的:

用户

id | created_at
1  | 2013-11-01
2  | 2013-11-02
3  | 2013-11-03
4  | 2013-11-03

费用

id | created_at | total_amount | user_id
1  | 2013-11-01 | 49.00        | 1
2  | 2013-11-01 | 22.00        | 1
3  | 2013-11-04 | 10.00        | 1
4  | 2013-11-02 | 49.00        | 2
5  | 2013-11-03 | 66.00        | 3
6  | 2013-11-03 | 49.00        | 4
7  | 2013-11-06 | 14.00        | 4
8  | 2013-11-12 | 33.00        | 4

我们需要使用 chart.io 将其呈现为折线图。因此结果表必须如下所示:

created_at | Layer      | avg_amount
2013-11-01 | 2013-11-01 | 35.50
2013-11-04 | 2013-11-01 | 10.00
2013-11-02 | 2013-11-02 | 49.00
2013-11-03 | 2013-11-03 | 57.50
2013-11-06 | 2013-11-03 | 14.00
2013-11-12 | 2013-11-03 | 33.00

“创建于”是指产生费用的时间

'Layer'指的是用户创建的时间

'avg_amount' 是在 'created_at' 注册的用户当天的平均费用 total_amount

谢谢:)

编辑:不幸的是，随着市场营销人员看到图表，规范发生了变化……他们现在希望 Y 轴永远不会下降。它需要是[同类群组用户创造的累计收入]/[同类群组中的所有用户]。因此，提名人可以增加，但分母永远不会减少。

所以结果表应该是这样的:

created_at | Layer      | cumulative_amount
2013-11-01 | 2013-11-01 | 71.00
2013-11-02 | 2013-11-02 | 49.00
2013-11-03 | 2013-11-03 | 66.00
2013-11-03 | 2013-11-03 | 49.00
2013-11-04 | 2013-11-01 | 81.00
2013-11-06 | 2013-11-03 | 63.00
2013-11-12 | 2013-11-03 | 96.00

非常感谢

Answer 1

我相信您可以解决一些格式问题 (here's the SQLFiddle)，但这是您需要的基本查询。

WITH daily_charges AS (
  SELECT
    sum(total_amount) AS daily_total,
    user_id,
    date_trunc('day', created_at) AS charge_date
  FROM charges
  GROUP BY user_id, created_at
  )
SELECT
  d.charge_date,
  date_trunc('day', u.created_at) AS layer,
  avg(d.daily_total) AS avg_amount
FROM
  daily_charges d
  JOIN users u ON
    (d.user_id=u.id)
GROUP BY charge_date, layer;