时间戳

首页 > 解决方案 > MySQL按范围内的日期计算各行的百分比

mysql - MySQL按范围内的日期计算各行的百分比

问题描述

我正在使用 MySQL 5.6，并且我有一个包含很多这样的记录的表......

+----+------------+-------+
| id | date       | price |
+----+------------+-------+
|  1 | 2000-01-01 |  1.56 |
|  1 | 2000-01-05 |  1.90 |
|  1 | 2000-02-02 |  1.44 |
|  2 | 2000-01-01 | 10.99 |
|  2 | 2000-01-07 |  9.88 |
|  2 | 2000-02-01 |  9.64 |
+----+------------+-------+

对于每个 ID，我想返回以下内容...

  1. ID
  2. 最短日期 >= 1 个月前
  3. 最大日期 <= 今天的日期
  4. 价格的百分比差异，基于返回的 2 个日期的价格
  5. 甚至可能显示那些日期的价格

例如，如果表格数据是（今天是 2000-02-01）......

+----+------------+-------+
| id | date       | price |
+----+------------+-------+
|  1 | 2000-01-01 |  1.00 |
|  1 | 2000-02-01 |  1.10 |
|  2 | 2000-01-04 |  1.00 |
|  2 | 2000-02-01 |  2.00 |
+----+------------+-------+

然后我希望看到...

+----+------------+------------+------------+
| id | min        | max        | percentage |
+----+------------+------------+------------+
|  1 | 2000-01-01 | 2000-02-01 |        10% |
|  2 | 2000-01-04 | 2000-02-01 |       100% |
+----+------------+------------+------------+

所以我的问题是，我该怎么做？更重要的是，解决这个问题的逻辑步骤是什么？

标签： mysqlcalculation

解决方案

你可以从这个开始，它留下了很大的改进空间：

DROP TABLE IF EXISTS price;
CREATE TABLE price (id int ,`date` date,price decimal(8,2));
INSERT INTO price VALUES
('1','2000-01-01','1.56'),
('1','2000-01-05','1.90'),
('1','2000-02-02','1.44'),
('2','2000-01-01','10.99'),
('2','2000-01-07','9.88'),
('2','2000-02-01','9.64');


select id,`min`,`max`,`price1`,`price2`,
    round((price2-price1)/price1*100,2) as `percentage`
from (
   select id,`min`,`max`,
       (select price from price p1 where p1.id=x.id and p1.`date`=x.`min`) as price1,
       (select price from price p1 where p1.id=x.id and p1.`date`=x.`max`) as price2
   from (
      select 
         id,
         min(`date`) as `min`,
         max(`date`) as `max`
      from price
      where `date` between '2000-01-01' and '2000-02-01'
      -- where `date` between date_add(current_date(), INTERVAL -1 month) and current_date()
      group by id) x
   ) x2;

注意：注释WHERE子句用于过滤当前日期之前的最后一个月。使用的 WHERE 子句只是为了使其与示例数据一起工作。

注意2：我命名了表格price，因为问题中没有给出名称。我确实需要一个更适合您当前情况的解决方案，您应该create table...为您使用的表提供（DDL）以及一些示例数据。

注意 3：您不应使用保留字作为列名，例如date,min并且max在此代码中...

推荐阅读