每种类型客户的销售百分比

2024-05-18 • 问答

我想找到每种客户类型每周的销售额百分比。我可以看到数学运算，但无法弄清楚如何编写查询。

SELECT 
    c.customerType as 'Customer Type',DATEPART(WEEK,o.orderDate) as 'Week of the year',COUNT(c.customerType)  as 'Number of sales' 
FROM
    [dbo].[Order] o
JOIN 
    Customer c ON c.id = o.customerId
GROUP BY 
    c.customerType,o.orderDate)

此查询输出按客户类型分组的每个销售的计数。

CustomerType  Week   Number of Sales
------------------------------------
Cash          36      248
Corporate     36       10
Personal      36        5
Cash          37      113
Corporate     37        3
Personal      37        2
Cash          38      136
Corporate     38        7
Personal      38        2
Cash          39      138
Corporate     39        4
Personal      39        3

您可以包装查询并使用窗口函数：

select 
    t.*,(100.0 * [Number of sales])
        /(sum([Number of sales]) over(partition by [Week of the year]) 
        [Percent of Total Sales]
from (
    select 
        c.customerType as [Customer Type],datepart(week,o.orderDate) as [Week of the year],count(c.customerType) as [Number of sales],from [dbo].[Order] o
    join Customer c ON c.id = o.customerId
    group by c.customerType,o.orderDate),datepart(year,o.orderDate)
) t

注意：

在SQLServer中，比起引号，最好使用方括号来定义标识符（引号通常保留给字符串）
.0中的100.0很重要：它强制SQLServer执行十进制除法（默认情况下，它将进行整数除法，这不是您想要的）
我将年份添加到组的定义中；如果您的数据分散了几年，您可能不希望将不同年份的同一周一起计算

侧面说明：SQLServer在混合窗口函数和聚合方面非常灵活。所以可能也可以：

select 
    c.customerType as [Customer Type],(100.0 * count(c.customerType))
        / (sum(count(c.customerType)) over(partition by datepart(week,o.orderDate)))
        as [Percent of Total Sales]
from [dbo].[Order] o
join Customer c ON c.id = o.customerId
group by c.customerType,o.orderDate)

每种类型客户的销售百分比

liuqingshuai113 回答：每种类型客户的销售百分比

大家都在问