从单个选择查询中提取多个数学运算输出 [英] Extracting several math operations outputs from single select query

问题描述

我需要合并三个表进行分析:活动表、学生表和账单.

I have three tables that I need to merge to analyse: active, students and bills.

'Active' 包含关于活跃学生和他们一直活跃的科目的记录，列:id(学生 ID)int、时间(他们活跃的时间)时间戳和主题(活跃的科目) - 文本

'Active' contains records on active students and the subjects they have been active on with columns: id (student id) int, time (time they have been active) timestamp, and subject (subject in which were active) - text

id   time                  subject
1    2020-04-23 06:53:30   Math
2    2020-05-13 09:51:22   Physics
2    2020-02-26 17:34:56   History

'Students' 是包含以下内容的海量数据库:id (student id) int, group (学生被分配到进行 a/b 测试的组) - text

'Students' is the mass database containing: id (student id) int, group (the group to which student was assigned for a/b test) - text

id   group     
1    A       
2    B 
3    A
4    A

'Bills' 记录学生购买的课程的所有交易:id(学生 ID)int、sale_time(学生购买课程的时间)时间戳、主题(购买课程的主题)文本、金钱(支付的金额).

'Bills' keeps record of all transactions for courses that student purchased: id (student id) int, sale_time (time when student purchased course) timestamp, subject (subject in which course purchased) text, money (amount paid).

id     sale_time              subject     money
1      2020-03-04 08:54:55    Math        4300
1      2020-04-08 20:43:56    Math        3200
2      2020-05-09 13:43:12    Law         8900

基本上，我们有一个学生数据库(Students)，其中一些购买了课程(Bills).而一些购买者仍然保持活跃(Active).

Basically, we have a student database (Students) some of which purchased courses (Bills). While some of those who purchased remain active (Active).

我需要编写 ONE SINGLE 查询，我可以在其中提取以下内容，按它们属于 A 组还是 B 组进行分组:

I need to write ONE SINGLE query where I can extract the following grouped by whether they belong to A or B group:

average revenue per user: sum (money) / count (distinct Students.id)
average revenue per active user: sum (money) / count (distinct Active.id)
conversion rate (%): count (distinct Bills.id) / count (distinct Students.id)
conversion rate (active) (%): count (distinct Bills.id) / count (distinct Active.id)
conversion rate (Math) (%) (count (distinct Bills.id) where Bills.subject = Math) / (count (distinct Active.id) where Active.subject = Math)

所有这些都在一个查询中！

All these in single query!

我用过

select sum (money)/count (distinct Students.id)
  from Students
     left join Bills using (id)
        left join Active using (id)
            group by group, Students.id

但我不知道如何在使用过滤器选择后立即完成这些数学计算.

but I don't know how to do these math calculations all in one right after select with filters.

请帮忙！

SQL 小提琴:https://www.db-fiddle.com/f/NPQR6aBf8H36XvrefJY2J/0

推荐答案

我建议在加入之前删除重复项，然后使用窗口函数:

I would recommend removing duplicates before joining and then using window functions:

select s.group, avg(b.money)as AvgPerUser,
       sum(b.money) /  nullif(count(a.id), 0) as AvgActUser,
       count(b.id) / nullif(count(s.id), 0) as CovRate,
       count(b.id) / nullif(count(a.id),0) as ConActRate,
       count(b.id) filter (where s.subject = 'Math') * 1.0 / count(*) filter (where s.subject = 'Math') as ConRateMath
from Students s left join
     (select b.id, sum(money) as money
      from bills b
      group by b.id
     ) b
     on s.id = b.id left join
     (select distinct a.id from active a
     ) a
     on s.id = a.id
group by s.group;  

注意:我不认为您需要 GROUP BY 中的 s.id.那真的不会聚合任何东西.

Note: I don't think you want s.id in the GROUP BY. That really would not be aggregating anything.

这篇关于从单个选择查询中提取多个数学运算输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！