ActiveRecord查询难以执行百分比计算

ActiveRecord查询难以执行百分比计算

本文介绍了ActiveRecord查询难以执行百分比计算的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个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查询难以执行百分比计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-06 16:16