mysql - MySQL 的线性方程函数？

Question

我想为我在 MySQL 数据库中的一些数据计算分数。分数将按如下方式计算:

score = COUNT(purchases MADE BETWEEN NOW() AND (NOW() - 1 WEEK))
  + 0.7 * COUNT(purchases MADE BETWEEN (NOW() - 1 WEEK) AND (NOW() - 2 WEEKS))
  + 0.4 * COUNT(purchases OLDER THAN (NOW() - 2 WEEKS))

我在带有 purchase_time 列的表中进行了购买。

是否可以在 MySQL 中执行此操作并获得类似于以下的输出？

ORDER_ID    SCORE
   3          8
   4          3
   5          15

谢谢

--- 编辑 ---表结构为:

tblOrder - table
id - primary key
created - time stamp

Answer 1

SELECT  orderId,
        SUM
        (
        CASE
        WHEN purchase_date > NOW() - INTERVAL 1 WEEK AND purchase_date <= NOW() THEN
                1
        WHEN purchase_date > NOW() - INTERVAL 2 WEEK AND purchase_date <= NOW() - INTERVAL 1 WEEK THEN
                0.7
        ELSE
                0.3
        END
        )
FROM    mytable
GROUP BY
        orderId