如果客户的销售额超过2，则只对每名客户的最后5次销售额进行汇总。
如果客户的销售额超过2，则只对每名客户的最后5次销售额进行汇总。
提问于 2020-08-23 11:58:29
CREATE TABLE sales (
id int auto_increment primary key,
customerID VARCHAR(255),
sales_volume INT,
sales_count VARCHAR(255)
);
INSERT INTO sales
(customerID, sales_volume, sales_count
)
VALUES
("Customer_01", "650", "1"),
("Customer_01", "718", "2"),
("Customer_01", "130", "3"),
("Customer_01", "455", "4"),
("Customer_01", "910", "5"),
("Customer_01", "432", "6"),
("Customer_02", "705", "1"),
("Customer_02", "718", "2"),
("Customer_03", "560", "1"),
("Customer_03", "938", "2"),
("Customer_03", "620", "3"),
("Customer_03", "182", "4"),
("Customer_03", "167", "5"),
("Customer_03", "740", "6"),
("Customer_03", "172", "7"),
("Customer_04", "260", "1"),
("Customer_05", "812", "1");预期结果：
customerID sum(sales_volume)
Customer_01 2863 (650+718+130+455+910)
Customer_03 2467 (560+938+620+182+167)在上面的结果中，我想列出所有拥有超过2个销售的客户，但是只有从中总结 sales_volume，他们最后的5个销售。
到目前为止，我正在使用以下查询：
SELECT
customerID,
sum(sales_volume)
FROM sales
GROUP BY 1
HAVING MAX(sales_count) >= 3;一旦一个客户达到超过2个销售，就可以得到所有销售的总，但是我不知道如何将这个查询与last 5 sales的限制结合起来。
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-08-23 12:02:02
您可以使用ROW_NUMBER()向后枚举销售(因此最近的事务值为1)。然后过滤和聚合：
SELECT customerID, sum(sales_volume)
FROM (SELECT s.*,
ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY sales_count DESC) as seqnum
FROM sales s
) s
WHERE seqnum <= 5
GROUP BY customerID
HAVING MAX(sales_count) >= 3;这里是db<>fiddle。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/63546658
复制相关文章
相似问题
腾讯云开发者
