时间戳

首页 > 解决方案 > MySQL 销售报告

mysql - MySQL 销售报告

问题描述

我有以下表格：

users
+-------+-----------+-----------+
| id    | source    | age_group |
+-------+-----------+-----------+
| 1     | google    | 18 - 22   |
+-------+-----------+-----------+
| 2     | facebook  | 22 - 25   |
+-------+-----------+-----------+
| 3     | site      | 25 - 35   |
+-------+-----------+-----------+
| 4     | google    | 25 - 35   |
+-------+-----------+-----------+
| 5     | google    | 18 - 22   |
+-------+-----------+-----------+

orders
+-------+-----------+
| id    | userId    |
+-------+-----------+
| 1     | 3         |
+-------+-----------+
| 2     | 1         |
+-------+-----------+
| 3     | 3         |
+-------+-----------+
| 4     | 2         |
+-------+-----------+
| 5     | 4         |
+-------+-----------+
| 6     | 5         |
+-------+-----------+

orders_payments:
+-------+-----------+-----------+
| id    | orderId   | amount    |
+-------+-----------+-----------+
| 1     | 1         | 10        |
+-------+-----------+-----------+
| 2     | 2         | 0         |
+-------+-----------+-----------+
| 3     | 3         | 40        |
+-------+-----------+-----------+
| 4     | 5         | 0         |
+-------+-----------+-----------+
| 5     | 6         | 0         |
+-------+-----------+-----------+

和 3 种可能性：

  1. 用户下单并付款（orders_payments 金额 > 0）
  2. 用户免费试用了一项服务（orders_payments 金额 = 0）
  3. 用户下订单但没有出现（orders_payments 中没有记录）

我需要一个看起来像这样的销售报告：

+-----------+-----------+-----------+-----------+-----------+
| source    | age_group | paid      | tried     | no_show   |
+-----------+-----------+-----------+-----------+-----------+
| google    | 18 - 22   | 0         | 2         | 0         |
+-----------+-----------+-----------+-----------+-----------+
| google    | 25 - 35   | 0         | 1         | 0         |
+-----------+-----------+-----------+-----------+-----------+
| facebook  | 22 - 25   | 0         | 0         | 1         |
+-----------+-----------+-----------+-----------+-----------+
| site      | 25 - 35   | 1         | 0         | 0         |
+-----------+-----------+-----------+-----------+-----------+

最后 3 列代表至少付费一次、免费试用服务或未出席预约的用户数量。

报告按来源分组，然后按年龄组。所以像这样

SELECT source, age_group, ... FROM users GROUP BY source, age_group

对于我的一生，我无法弄清楚。

任何想法都非常感谢。

谢谢！

LE：我只想统计一次用户。因此，如果他们有两个已付款订单，我将它们计为已付款一次，如果尝试但随后已付款，我也想将它们计为已付款一次，依此类推。

LE2：我认为这是一个可能的解决方案：

select
    u.source,
    u.age_group,
    sum(op.amount > 0) paid,
    sum(op.amount = 0) tried,
    sum(op.orderId is null) no_show
from users u
inner join orders o on o.userId = u.id
left join (
    select max(amount) as amount, orderId
    from orders_payments
    left join orders on orders_payments.orderId = orders.id
    group by orders.userId
) op on op.orderId = o.id
group by u.source, u.age_group
order by u.source, u.age_group

标签： mysqlsqlgroup-bypivotreport

解决方案

我会left join在支付表上选择一个，然后是条件聚合。

select
    u.source,
    u.age_group,
    sum(op.amount > 0) paid,
    sum(op.amount = 0) tried,
    sum(op.orderId is null) no_show
from users u
inner join orders o on o.userId = u.id
left join orders_payments op on op.orderId = o.id
group by u.source, u.age_group
order by u.source, u.age_group

推荐阅读