为每个组获取前x天的收入

7z5jn7bk 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(343)

这是我的table

CREATE TABLE financials ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
CountryID VARCHAR(30) NOT NULL, 
ProductID VARCHAR(30) NOT NULL,  
Revenue INT NOT NULL, 
cost INT NOT NULL, 
reg_date TIMESTAMP 
); 

INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES 
( 1, 'Canada', 'Doe'   , 20,  5, '2010-01-31 12:01:01'), 
( 2, 'USA'   , 'Tyson' , 40, 15, '2010-02-14 12:01:01'), 
( 3, 'France', 'Keaton', 80, 25, '2010-03-25 12:01:01'),
( 4, 'France', 'Keaton',180, 45, '2010-04-24 12:01:01'),
( 5, 'France', 'Keaton', 30,  6, '2010-04-25 12:01:01'),
( 6, 'France', 'Emma'  , 15,  2, '2010-01-24 12:01:01'),
( 7, 'France', 'Emma'  , 60, 36, '2010-01-25 12:01:01'),
( 8, 'France', 'Lammy' ,130, 26, '2010-04-25 12:01:01'),
( 9, 'France', 'Louis' ,350, 12, '2010-04-25 12:01:01'), 
(10, 'France', 'Dennis',100,200, '2010-04-25 12:01:01'),
(11, 'USA'   , 'Zooey' , 70, 16, '2010-04-25 12:01:01'),
(12, 'France', 'Alex'  ,  2, 16, '2010-04-25 12:01:01');

对于每个产品和日期组合，我需要得到前5天的收入。例如，对于产品“keaton”，最后一次购买是在2010年4月25日，它只将2010年4月20日至2010年4月25日之间的收入相加，因此将为210。而对于“emma”，它将返回75，因为它将对2010-01-20到2010-01-25之间的所有内容求和。

SELECT ProductID, sum(revenue), reg_date
  FROM financials f
Where reg_date in (
    SELECT reg_date
    FROM financials as t2
    WHERE t2.ProductID = f.productID
    ORDER BY reg_date
    LIMIT 5)

不幸的是，当我使用https://sqltest.net/ 或者http://sqlfiddle.com/ 它表示不支持“limit&in/all/any/some子查询”。我的查询是否有效？

sql mysql group-by where

来源：https://stackoverflow.com/questions/48216279/get-previous-x-days-of-revenue-for-each-group

2条答案

按热度按时间

edqdpe6u1#

经过一些试验，我最终得到了一些复杂的查询，我认为它解决了您的问题

SELECT
  financials.ProductID, sum(financials.Revenue) as Revenues
FROM
  financials 
INNER JOIN (
      SELECT ProductId, GROUP_CONCAT(id ORDER BY reg_date DESC) groupedIds
      FROM financials
      group by ProductId
) group_max
  ON financials.ProductId = group_max.ProductId
     AND FIND_IN_SET(financials.id, groupedIds) BETWEEN 1 AND 5
group by financials.ProductID

首先我用 group by financials.ProductID 按产品计算收入。您面临的真正问题是为每个组消除所有不在前5位的行。为此，我使用了这个问题的解决方案，将\u concat分组，并在\u集合中找到\u，从而无限制地得到前5个结果。而不是 WHERE IN 我曾经 JOIN 但有了这个， WHERE IN 可能也有用。
小提琴在这儿

赞(0）回复(0）举报 2021-06-25

0s7z1bwu2#

您的查询是正确的，但在mysql中可能不起作用。mysql在使用 in 以及 limit 使用子查询。
取而代之的是：

SELECT f.ProductID, SUM(f.revenue)
FROM financials f JOIN
     (SELECT ProductId, MAX(reg_date) as max_reg_date
      FROM financials
      GROUP BY ProductId
     ) ff
     ON f.ProductId = ff.ProductId and
        f.reg_date >= ff.max_reg_date - interval 5 day
GROUP BY f.ProductId;

编辑：
如果要为每个产品和日期组合使用此查询，则可以使用自联接或关联子查询：

SELECT f.*,
       (SELECT SUM(f2.revenue)
        FROM financials f2
        WHERE f2.ProductId = f.ProductId AND
              f2.reg_date <= f.reg_date AND
              f2.reg_date >= f.reg_date - interval 5 day
       ) as sum_five_preceding_days
FROM financials f;

赞(0）回复(0）举报 2021-06-25

我来回答

为每个组获取前x天的收入

2条答案

相关问题

热门标签

最新问答