SQL 视图，按销售最多的商品和购买最多的客户分组 [英] SQL views, grouping by most sold items and customers who purchased most

问题描述

这是我的桌子:

使用此查询，我获得了最畅销的商品:

Using this query, I am getting most sold items:

SELECT [Purchased Item], SUM([Overall Quantity purchased] )
FROM ReportDraft 
GROUP BY [Purchased Item]
ORDER BY SUM([Overall Quantity purchased] )

这将返回客户购买的商品和总数量.

This returns items and total quantity purchased by customer.

我可以以某种方式创建一个像

Can I somehow create a table like

ItemName | Total quantity purchased | Customer who purchased most | Customer quantity bought

Pie--------|---------11------------|---------------ALEX----------|--------3------------|

谢谢

推荐答案

我会使用窗口函数和条件聚合:

I would use window functions and conditional aggregation:

SELECT [Purchased Item], sum(total) as total,
       MAX(CASE WHEN seqnum = 1 THEN Customer END) as customer,
       MAX(Total) as max_quantity
FROM (SELECT [Purchased Item], Customer, SUM([Overall Quantity purchased] ) as total,
             ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY SUM([Overall Quantity purchased]) DESC) as seqnum
      FROM ReportDraft 
      GROUP BY [Purchased Item], Customer
     ) rd 
GROUP BY [Purchased Item]
ORDER BY SUM([Overall Quantity purchased] );

这篇关于SQL 视图，按销售最多的商品和购买最多的客户分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！