ActiveRecord查询难以执行百分比计算 [英] Difficulty with ActiveRecord query that does percentage calculation

问题描述

我有一个ActiveRecord架构PurchaseSummary，它是我继承的并且无法更改，看起来像这样：

I have an ActiveRecord schema, PurchaseSummary, that I inherited and THAT I CAN NOT CHANGE that looks like this:

purchase_summaries
    date date_of_purchase
    integer number_of_purchases
    boolean coupon_used

例如11月1日，有800次购买，有500张没有优惠券，有300张有优惠券，11月2日，有600次购买没有优惠券，有100张有优惠券，因此数据库中的行如下所示：

example, for November 1st, there were 800 purchases, 500 without a coupon, 300 with a coupon, and on Nov 2nd, 600 purchases without a coupon, and 100 with, so the rows in the database look like this:

2011-11-01, 500, false
2011-11-01, 300, true 
2011-11-02, 600, false
2011-11-02, 100, true

例如， 11/1的购买次数为800，而11/2的购买次数为11/2。

In example, total number of purchases for 11/1 are 800 and 11/2 700.

问题：我想构造一个查询，该查询每天返回一次，返回使用的购买百分比优惠券，或

The problem: I would like to construct a query that returns for each day, the percentage of purchases that used a coupon, or

2011-11-01, 0.375
2011-11-02, 0.142

推荐答案

不是最优化的方法，但是您可以执行以下操作：

Not th most optimized way, but you can do something like :

select date_of_purchase
      ,sum(number_of_purchases) / (select sum(subquery.number_of_purchases) 
                                     from purchase_summaries subquery 
                                    where subquery.date_of_purchase = query.date_of_purchase
                                      and subquery.coupon_used = 1) * 100 as percentage
  from purchase_summaries query
 group by date_of_purchase

这篇关于ActiveRecord查询难以执行百分比计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！